Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old February 5th, 2011, 12:15 AM
Registered User
Join Date: Feb 2011
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Angry Populate form fields if record exists in table

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..

Forms!FRMNewLaptopBookingV2!EncryptionStatus = Table!Laptop!EncryptionStatus

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
Old February 18th, 2011, 05:21 AM
gjgriffith's Avatar
Wrox Author
Join Date: Jul 2009
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Default Populate form fields if record exists in table

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

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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Add Record to table from form AlForum29 Access VBA 2 January 2nd, 2013 09:46 PM
Auto-populate table data into form jeff394 Access VBA 2 April 19th, 2006 02:55 PM
Auto Populate fields in a form mnemec24 Access 8 March 9th, 2005 02:11 PM
Error 3201: Cannot add record in child form/table HomeShow Access VBA 0 October 5th, 2004 09:56 AM
Record Exists [email protected] SQL Language 3 September 13th, 2004 02:36 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.