I know this is a dumb one but I can't find it in my refernce books :(

I have a form that the user enters a alphanumeric value into the first field. They click a control button - if the value does not exist in the corresponding table a popup prompts them to create a new record (works perfectly thanks to help from this forum).If the value does match a corresponding record in the table when they click, I want to automatically fill in the remainder of the vacant fields in the active form and move the cursor to the next field.

The form is based on a query ("QRYNewBookingV2") made up of two joined tables - 'TBLLaptop' and 'TBLUser' with the "UserID" as the joined columns.

The data needed to retrieve is in the TBLLaptop column "EncryptionStatus".

In simple terms if the record exist the Else statement I am trying to construct is as follows..

Else
Forms!FRMNewLaptopBookingV2!EncryptionStatus = Table!Laptop!EncryptionStatus

Me.UserID.SetFocus
End If
End Sub

I get no errors, it just doesn't fill the vacant fields in.

Qustion one: Is my syntax referring to the table correct?

Question two: Should I be referring to the query and if so what is the syntax? Query!QRYNewBookingV2!EncryptionStatus ?

As always, thanks for your help!Robert

Hello RobertIngles,

I saw your post and I thought I might be able to help! Make sure that:

1. The "Record Source" property for the Form is set to the "QRYNewBookingV2" (which you said it was)
2. Make sure that each control on the form has it's "Control Source" property set to the name of the corresponding table field. (Your post didn't mention this, so make sure these controls are set correctly)

Which you probably already know...but just to be super clear, once those 2 things are in place, the Access form should automatically populate the controls with whatever record you have selected. So, once you move to that record, the controls should be populated automatically. Then calling the "Me.UserID.SetFocus" should work correctly.

So, to answer each of your questions:
1. "Is my syntax referring to the table correct?"
Hmm...I may see a problem: At one point in the post, you refer to the table as "TBLLaptop", but here: "Table!Laptop!EncryptionStatus" you just use "Laptop"...so not sure if that is a typo, but it could be a code error. But really, you shouldn't have to set this manually to do what you want to accomplish (if I understand you correctly). See my note #2 above and the next point below, which is much more important...

2. "Should I be referring to the query and if so what is the syntax? Query!QRYNewBookingV2!EncryptionStatus?" Yes...this is most likely your problem. But again, you shouldn't have to manually set these fields (if I understand you correctly). Were you unable to get this to work this way?

Anyway, I hope this all makes sense! Does this help at all, or am I telling you things that you already know? Anyway, if you still have more questions, please let me know and I'll do what I can to help!

Good Luck,

Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference