Field Population (1 Viewer)

[supercharge]

However I was wondeirng if I can select specific fields instead of the whole record.

Coping the whole record over, then clearing out fields needed for new values are more code-efficient than coping specific fields because so, you'd have to declare more dummy variables and set them to the values that you want to copy...just more work but if you want it that way, sure can.

I aslo guess I can add my own code after the cleared text box to create my own value.

Good guess!

Thanks

You're welcome!

 

[NewfieSarah]

Well I tryed the code from the sample you gave me, and the record doesnt copy. When I add the code to my program a blank form showup. So I would like to try copying specific fields can you get me started??

 

[supercharge]

Well I tryed the code from the sample you gave me, and the record doesnt copy. When I add the code to my program a blank form showup. So I would like to try copying specific fields can you get me started??

Check to make sure your form's record selector is enable (or set to yes, know how?). I think that's your problem. You got a blank record because only the AddNew code was executed, nothing was copied over.

Try it and post back here.

 

[NewfieSarah]

Yes the record selector is not enabled because I am using navation command buttons. so i have to have that there for this to work?? that seems a bit weird! ideas?

 

[supercharge]

You don't have to disable the Record Selector in order to use your navigational command buttons. It should not matter.

Or if don't want to enable, try copying each field (in case you have lots of fields, this is not a good way to do).

Here's how:
Let's say your form has 3 textboxes: LastName, FirstName and Status. Only Status will be changed when adding new record, meaning you'll keep LastName and FirstName the same. In your AddNew button OnClick sub, do the following:

Private Sub cmdAddNew_Click()
     Dim LN as String, FN as String
     LN = LastName.Value
     FN = FirstName.Value
     DoCmd.GoToRecord , , acNewRec
     LastName.Value = LN
     FirstName.Value = FN
     DoCmd.GoToControl "Status"
End Sub

 

[NewfieSarah]

Hey I tryed that and I got an error invalid use of a null! why is that? Do i have to validate to see if there is something in my active x controls. before adding? I have had to validate most eveything else like that on my program. Would it be better if you could see my add code? That is the kind of code that I was think that might work so we are on the same page mostly. Thanks

 

[supercharge]

It wouldn't hurt to see it. Also, can you capture your form's screen and attach it to this. I want to see what kind of data or activeX control is being copied and returned error messages.

 

[NewfieSarah]

Add button code

Private Sub addbtn_Click()
          
On Error GoTo Err_addbtn_Click
If PERMIT.Locked = True Then


      DoCmd.GoToRecord , , acNewRec
      enteredit
      SigPlus1.ClearTablet
      SigPlus2.ClearTablet
      
    'disable buttons
    insbtn.Enabled = False
    Command31.Enabled = False
    Command63.Enabled = False
        If Form.FilterOn = False Then
            enteredit
            PIN.Locked = False
        Else
            strpin = Form_PID.ADDRESS3.Value
            PIN.Locked = False
            PIN.SetFocus
            PIN.Text = strpin
            'PHONE.Text = phoneadd
            PIN.Locked = True
            
            If STRCLA = "" Then
                Let STRCLA = "RE"
            Else
            
                STRCLA = Form_PID.CLASS.Value
                CLASS.Locked = False
                CLASS.SetFocus
                CLASS.Text = STRCLA
                CLASS.Locked = True
            End If
            
            Dim STRDATE As String
            STRDATE = Now
            DATE.Locked = False
            DATE.SetFocus
            DATE.Text = STRDATE
            DATE.Locked = True
            
            Dim myCount As Long
            PERMIT.SetFocus
            myCount = 1 + Nz(DCount("*", "Building"), 0)
            PERMIT.Text = Format(STRDATE, "yy") & " - " & myCount
  'This is commented out code since I am unable to get it to work yet.
'BEGINNING          
          ' If OWNER = "" Then
          '      NAME = ""
           'Else
           '     NAME = OWNER.Value
          ' End If
           
          ' DoCmd.GoToControl "OWNER"
           'OWNER.Value = NAME
              
            'OWNER.Locked = False
            'OWNER.SetFocus
            'Dim OWNER2 As String
        '    OWNER = OWNER.DefaultValue
'            OWNER2.Value = OWNER
         '   OWNER.Text = OWNER
            
            'OWNER.DefaultValue = "='" & Me.OWNER & "'"
            'OWNER.Text = OWNER.DefaultValue
       '     OWNER.Locked = True
            
            
           ' Dim FIRSTN As String
           ' Dim LASTN As String
           ' Dim NAME As String
           ' FIRSTN = Form_ARLIST.FIRSTNAME.Value
           ' LASTN = Form_ARLIST.LASTNAME.Value
            
           ' If FIRSTN = " " Then
           ' NAME = LASTN
           ' Else
           ' End If
           ' If LASTN = "" Then
           ' NAME = FIRSTN
           ' Else
           ' End If
            
            'NAME = FIRSTN + " " + LASTN
            'OWNER.Locked = False
            'OWNER.SetFocus
            'OWNER.Text = NAME
            'OWNER.Locked = True
            
           ' Dim PHONEH As String
           ' PHONEH = Form_ARLIST.TELEPHONE1.Value
           ' If PHONEH = "" Then
           ' PHONEH = 0
           ' Else
            ' PHONE.Locked = False
           '  PHONE.SetFocus
            ' PHONE.Text = PHONEH
             'PHONE.Locked = True
           ' End If
            
          
            
            'Dim PHONEW As String
            'PHONEW = Form_ARLIST.TELEPHONE2.Value
            'If PHONEW = "" Then
            'PHONEW = "0-(000)000-000"
            'Else
            'End If
            
            'PHONEWORK.Locked = False
            'PHONEWORK.SetFocus
            'PHONEWORK.Text = PHONEW
            'PHONEWORK.Locked = True
'  END                      
          End If
Else
    
    
    MsgBox ("You cannot add a new record while in edit mode. Please save your changes first.")
End If
Exit_addbtn_Click:
    Exit Sub

Err_addbtn_Click:
    MsgBox Err.DESCRIPTION
    Resume Exit_addbtn_Click
    
End Sub

 

[supercharge]

WHAT IS NAME? IS IT DECLARED SOMEWHERE

          ' If OWNER = "" Then
          '      NAME = ""
           'Else
           '     NAME = OWNER.Value
          ' End If
           
          ' DoCmd.GoToControl "OWNER"

YOU'RE SETTING OWNER.Value TO ITSELF, YOU = YOU, NO CHANGES 
SETTING A VALUE TO A FIELD BEFORE IT BEING UNLOCKED

           'OWNER.Value = NAME              
            'OWNER.Locked = False
            'OWNER.SetFocus
            'Dim OWNER2 As String
        '    OWNER = OWNER.DefaultValue
'            OWNER2.Value = OWNER
         '   OWNER.Text = OWNER
            
            'OWNER.DefaultValue = "='" & Me.OWNER & "'"
            'OWNER.Text = OWNER.DefaultValue
       '     OWNER.Locked = True
            
            
           ' Dim FIRSTN As String
           ' Dim LASTN As String
           ' Dim NAME As String
           ' FIRSTN = Form_ARLIST.FIRSTNAME.Value
           ' LASTN = Form_ARLIST.LASTNAME.Value
            
           ' If FIRSTN = " " Then
           ' NAME = LASTN
           ' Else
           ' End If
           ' If LASTN = "" Then
           ' NAME = FIRSTN
           ' Else
           ' End If
            
            'NAME = FIRSTN + " " + LASTN
            'OWNER.Locked = False
            'OWNER.SetFocus
            'OWNER.Text = NAME
            'OWNER.Locked = True
            
           ' Dim PHONEH As String
           ' PHONEH = Form_ARLIST.TELEPHONE1.Value
           ' If PHONEH = "" Then

STRING MUST HAVE " " AROUND IT
AGAIN, SETTING IT BEFORE UNLOCKED

           ' PHONEH = 0
           ' Else
            ' PHONE.Locked = False
           '  PHONE.SetFocus
            ' PHONE.Text = PHONEH
             'PHONE.Locked = True
           ' End If           
          
            
            'Dim PHONEW As String
            'PHONEW = Form_ARLIST.TELEPHONE2.Value
            'If PHONEW = "" Then
            'PHONEW = "0-(000)000-000"
            'Else
            'End If
            
            'PHONEWORK.Locked = False
            'PHONEWORK.SetFocus
            'PHONEWORK.Text = PHONEW
            'PHONEWORK.Locked = True
'  END                      
          End If
Else
    
    
    MsgBox ("You cannot add a new record while in edit mode. Please save your changes first.")
End If
Exit_addbtn_Click:
    Exit Sub

Err_addbtn_Click:
    MsgBox Err.DESCRIPTION
    Resume Exit_addbtn_Click
    
End Sub

 

[Mile-O]

Having just read this I feel sorry for Pat and neil's advice. Since I agree with them and wonder what supercharge is doing. That sample database, for example, breaks rules which, NewfieSarah, you should really adhere to.

Your database, however, and if you continue going down this road you will find you spend more time working with the database on an ongoing basis than you ever should.

Sarah, I don't think you are normalised either.

 

[The Stoat]

Sarah it may help if you posted the DB scheme/relationships that you have set up, that way people could actually see what's going on. I've been designing DB's for 5 years now and i still make mistakes with normalization. Trust me if you mess it up at the start you'll spend forever trying to put it right with code and work arounds that are entirely unnecessary. A properly normalized data structure will be easier to develop into a fully functioning db, it'll be easier to manage and it will run faster and more smoothly.

TS

 

[NewfieSarah]

Okay Define "Normalised"
I Thanks Supercharge, and give him a star because he is very good to understand.
"Breaking the rules" ? What rules would that be?
Why do you say ST McAbney that if I continue down this road that I will have to spend more time on my database??

 

[supercharge]

Having just read this I feel sorry for Pat and neil's advice.

I totally understand normalization and the advices. In fact, all my databases have been done like so. I also understand the situation that Sarah's been in.

What I've been doing is to help someone's need, not to correct their way of doing things. Yes, we can always suggest but it's up to them whether they want to do it the best way.

Let's say that you want to do your given project the "A" way, but your customer or client or boss wants you to do it the "B" way, would you tell them that they will need to adhere to your way? I don't know about you but I wouldn't 'cause it could cause my job.

 

[NewfieSarah]

Thanks for the input The Stoat. However I cant at this time post my DB. Well I havent designed this whole database it is a group effort and I mostly have to deal with the things I have at this time. but I am open to suggestions on what is going on. thanks

 

[Mile-O]

Let's say that you want to do your given project the "A" way, but your customer or client or boss wants you to do it the "B" way, would you tell them that they will need to adhere to your way? I don't know about you but I wouldn't 'cause it could cause my job.

I would definitely tell them to adhere to my way on the basis that they are customers or bosses and not Access Developers. They don't care how it's done - indeed, they rarely understand how it's done - as long as it produces accurate results. More likely, I shouldn't have to tell them to adhere to any way since they are not involved in the development and are only interested in progress and the final deadline.

 

[NewfieSarah]

Let's say that you want to do your given project the "A" way, but your customer or client or boss wants you to do it the "B" way, would you tell them that they will need to adhere to your way? I don't know about you but I wouldn't 'cause it could cause my job.

Great observation Supercharge! Yes I am open to suggestions, the more learn and understand about these things the better.

 

[The Stoat]

Okay Define "Normalised"
I Thanks Supercharge, and give him a star because he is very good to understand.
"Breaking the rules" ? What rules would that be?
Why do you say ST McAbney that if I continue down this road that I will have to spend more time on my database??

http://www.datamodel.org/NormalizationRules.html

 

[supercharge]

I would definitely tell them to adhere to my way on the basis that they are customers or bosses and not Access Developers.

Lucky you for having those kinds of customers or bosses.

Some are very strict on what they want theirs to be done. Some even go into details like: I want this table and that table and blah blah blah...and this report to contain only this....it's all b...s..t, you know but hey, it's the job, right?

I'm feeling sorry for you, Sarah, for having to deal with an existing database that is a mess. If you want to normalize it, I hate to say but, you may have to start over which I don't think you'll do anyway because you have forms, reports, queries and .... depend on the tables and it'll be a pain in the butt (see my signature?).

 

[Mile-O]

Okay Define "Normalised"

"Breaking the rules" ? What rules would that be?

Normalisation: The process of efficiently organising data in a database.

First Normal Form: Each row by column entity should be atomic, meaning that for each cell of the database only one item of data should be present. Therefore, you shouldn't store - in an inventory example - 5 red widgets, 2 blue, and a green one as one piece of data. These should be broken out to three fields: Item, Quantity, Colour. The problem by not adhering is that you can't summarise data efficiently.

Second Normal Form: Remove subsets of data by creating new tables to demonstrate entities and create the relevant relationships between. Therefore, with this, it notes that you should not have repeating groups (two ways: i.e. Course1, Course2, Course3, CourseN or, with quanitites, Apples, Oranges, Pears. These are data subsets and should be broken out from the table they appear in to a new table, called tblCourses or tblFruits in this respect. The problem here is that you require ongoing editing (queries, forms, reports, macros, VBA) everytime you would want to add to the subset. This causes the database to grow wide (fields) when a database should grow down (rows).

Third Normal Form: That you should have no fields dependent upon another non-key field. This applies to calculations. You would not, for example, have three fields called Quantity, Price, and Total in your Orders table. The Total field is dependent on the values in Quantity and Price fields and so you can eliminate the Total field and, when you need totals, you can calculate it with a query or on a form/report. The problem here is that you would require extra updating or could report improperly based on bad data should you update the Price and neglect to change the Total. Reaching 3NF means you're calculations will never be incorrect.

You can't reach 2NF without first attaining 1NF; and you can't reach 3NF without first ensuring 2NF.

Why do you say ST McAbney that if I continue down this road that I will have to spend more time on my database??

Because, based on your explanations, you are stalling at 2NF since you are duplicating data over and over again when what you need is one table to store the PIN details (rather than duplicate them) and one table for the other information. In the latter table you would just include the Primary Key to the PIN table and , hey presto!, a one-to-many relationship.

If you haven't taken the query advice then I get the impression you are binding tables to your forms. Always use queries for this! Queries can select the relevant data, aggregate it, sort it, and perform calculations. Tables just provide dumps of data. The other reason is that, the more your database grows when the forms you use are bound to a table, the slower you're database will become.

 

[reclusivemonkey]

What I've been doing is to help someone's need, not to correct their way of doing things. Yes, we can always suggest but it's up to them whether they want to do it the best way.

I think you are totally wrong here. If someone is designing a database incorrectly, then to not try and correct this isn't going to help in the long term. If you were a builder, and saw an apprentice building a wall on unsuitable foundations, would you help them build the wall up regardless? There are many different ways of accomplishing things, but unless your foundations are good, its all going to collapse one day.

Let's say that you want to do your given project the "A" way, but your customer or client or boss wants you to do it the "B" way, would you tell them that they will need to adhere to your way? I don't know about you but I wouldn't 'cause it could cause my job.

There are so many things wrong with this statement I don't know where to begin. I must be very fortunate in that in my job, my manager asks me for reports/information. He doesn't tell me how to do it. Unless your boss/client can actually DO your job, then they should be listening to you and trust your competence. If they don't, I would seriously be looking for other jobs/clients. A good manager manages people, not the way people work. Admittedly a good manager is as rare as rocking horse crap.

IMHO, there are far too many people posting to this forum who really want to become proficient in Access but simply don't have the "base knowledge", or haven't put enough time into understanding what a database is or does. Just getting "an answer" isn't going to improve that situation at all.

Just my two (hundred) penneth...