Auto Populate Skips Displaying Some Fields in the Form

[breakingme10]

I use a combo box with a row source of
SELECT Sites.Well_ID, Sites.Sec, Sites.Track, Sites.Region, Sites.County FROM Sites;
Bound to column 1 which is the PK. And is the Name of the Well Site Example= "Bert 1H-26" is one of my Well_IDs.


I use an after update event to auto populate the fields of section, track, region, county and origin.
I'm not going to go into explaining why i need the data to essentially be redundant and stored in two tables i just do.
so my code for the after update is:

Private Sub Lease_AfterUpdate()
Dim varSec, varTrack, varRegion, varCounty, varOrigin As Variant
    varSec = DLookup("Sec", "Sites", "Lease = [Well_ID]")
    varTrack = DLookup("Track", "Sites", "Lease = [Well_ID]")
    varRegion = DLookup("Region", "Sites", "Lease = [Well_ID]")
    varCounty = DLookup("County", "Sites", "Lease = [Well_ID]")
    varOrigin = DLookup("Well_ID", "Sites", "Lease = [Well_ID]")
    If (Not IsNull(varSec)) Then Me![Sec] = varSec
    If (Not IsNull(varTrack)) Then Me![Track] = varTrack
    If (Not IsNull(varRegion)) Then Me![Region] = varRegion
    If (Not IsNull(varCounty)) Then Me![County] = varCounty
    If (Not IsNull(varOrigin)) Then Me![Origin] = varOrigin
End Sub

Yes the Origin and the Well_ID are almost always the same! Every once in awhile there is an exception where the driver will write the actual location (ex. Featherston, OK) instead of the lease name on origin but mostly its the same.
The form i use is a "digital" version of our paper carbon copy water ticket forms that the drivers fill out in the field, and in my reports I use it to print the reproduced water ticket for when the ticket is damaged somehow and like the ones i'm looking at right now are covered in coffee stains (at least i hope its coffee stains).
My problem is that the "Track" and "Region" don't fill in on the form until i click inside the text box, then it fills it in. What am i doing wrong?

[pbaldy]

Try changing bang to dot:

Me.Track = varTrack

I'd use a recordset so I only made one trip to the data rather than 5.

[breakingme10]

I'm not sure how to use a recordset, i'm still new at access

[breakingme10]

Changing it to the Me.Track made no difference.

[breakingme10]

Does anyone know why it only populates the field once i click inside the text box?

[pbaldy]

I've seen it with the bang vs dot, but you say that didn't help. I've seen it happen on a db I created but it was with a 2007 db being run on the 2010 runtime. For some reason, going back to the 2007 runtime fixed several users, though not all were experiencing the problem. Many still ran the 2010 runtime and didn't experience the problem. It was a puzzler.

[breakingme10]

I ended up having worse issues than a few txt fields not showing the correct data until i clicked inside them, so i lost track of this post.
But with other issues resolved. I'm back to being absolutely stumped! I've tried turning autotab on and off for the fields but no difference. I've even added a field to it, and no difference! I use the same code on a copy of the form for a different table to store strictly the newfield tickets and i have the same issue on that form also.
Image1= After selecting the lease in the combo box
Image2=Clicking inside the "LeaseT" field to make it display the value
Image3=Clicking inside the "LeaseR" field to make it display the value
Image4=Highlighted all the fields that are auto populated by the lease combo box.

Private Sub LeaseCombo_AfterUpdate()Dim varSec, varTrack, varRegion, varCounty, varOrigin, varRC As Variant
    varSec = DLookup("SEC", "NewfieldCoalgateSites", "Lease = [WELL_ID]")
    varTrack = DLookup("TRACK", "NewfieldCoalgateSites", "Lease = [WELL_ID]")
    varRegion = DLookup("REGION", "NewfieldCoalgateSites", "Lease = [WELL_ID]")
    varCounty = DLookup("COUNTY", "NewfieldCoalgateSites", "Lease = [WELL_ID]")
    varOrigin = DLookup("WELL_ID", "NewfieldCoalgateSites", "Lease = [WELL_ID]")
    varRC = DLookup("RC_Number", "NewfieldCoalgateSites", "Lease = [WELL_ID]")
    If (Not IsNull(varSec)) Then Me.Sec = varSec
    If (Not IsNull(varTrack)) Then Me.Track = varTrack
    If (Not IsNull(varRegion)) Then Me.Region = varRegion
    If (Not IsNull(varCounty)) Then Me.County = varCounty
    If (Not IsNull(varOrigin)) Then Me.Origin = varOrigin
    If (Not IsNull(varRC)) Then Me.RC_NUMBER = varRC
End Sub

Does anyone have a clue why this is happening?

[pbaldy]

It's a shot in the dark, but try adding

Me.Repaint

at the end.

[breakingme10]

Ok I added me.Requery at the end because i read in another thread that that would work with combo boxes and i figured eh same concept for text boxes and of course it works better than ever! previously if i changed the combo box value it would not change the sec t r values to match but now it does! I came back here to share my findings and i see someone else came to the same conclusion!

It's a shot in the dark, but try adding

Me.Repaint


at the end.

[breakingme10]

well i spoke too soon, it works for one form, the one that is for newfield tickets
but not the other form, the other form it just automatically goes to the first record afterupdate of the leasecombo! i'm not sure why.

[pbaldy]

Did you try Repaint? Requery will go to the first record:

http://www.baldyweb.com/Requery.htm

[breakingme10]

I tried repaint it didn't work either. why would requery work on the newfield one and not this one? they are essentially the same table i just separate the newfield from the other companies. its not making much sense.