By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,316 Members | 617 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,316 IT Pros & Developers. It's quick & easy.

How to fill in field based on combobox selection (was VBA Access)

P: 31
I am very new at this. I have written a few functional databases, but everything i have learned has mostly come from this website.... so bear with me. I am trying to get information to automatically update. In the scenario i am using 2 tables. I have a part_number_table and a receiving_table. All of the part numbers and the descriptions are in the PN_table. The receiving_form is to assign a date, vendor, purchase order number, ect to the rec_table. What i need is .....

I have the part number on the rec_form coming from the pn_table from a combo box. When the pn is chosen i would like to have the description automatically filled in on the rec_form , from the pn_table, using on lost focus. I know it can be done, but i am extremely lost. How do i compare the number entered on the rec_form, to a number on the pn_table, and then enter the description from the pn_table on the rec_form.

Please help (the company i work for has told me to take an access class, but that has not occured yet)
Aug 17 '08 #1
Share this Question
Share on Google+
5 Replies

Expert 5K+
P: 8,740
You don't give me much to work on, so I'll make several assumptions. Substitute your own values where required:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboPN_LostFocus()
  2. Dim varDescription As Variant
  4. If Not IsNull(Me![cboPN]) Then
  5.   'If the Part Number is a String
  6.   varDescription = DLookup("[Description]", "PN_Table", _
  7.                            "[Part_Num] = '" & Me![cboPN] & "'")
  8.   'If the Part Number is Numeric
  9.   varDescription = DLookup("[Description]", "PN_Table", _
  10.                            "[Part_Num] = " & Me![cboPN])
  11.     Forms!rec_form![txtDescription] = varDescription
  12. End If
  13. End Sub
Aug 17 '08 #2

Expert Mod 15k+
P: 31,770
I'd consider using the AfterUpdate event in place of the LostFocus one for better results.
Aug 20 '08 #3

Expert 2.5K+
P: 3,532
I agree! You really don't want the code running if the user simply tabs thru the combobox, which LostFocus would do, especially if the combobox has no item selected. A Null value would pop an error, I believe.

Also, crazyhorse, in the future please take the time to come up with a thread title that gives members a general idea of your problem. Since this is an Access/VBA forum, the title "VBA Access" really doesn't say much.

Welcome to Bytes!

Linq ;0)>
Aug 20 '08 #4

P: 31
thank you for your help, i did end up using afterupdate, and i ended up using a subform on my for that is hidden. One day i will get better at this.
Aug 21 '08 #5

Expert 2.5K+
P: 3,532
Of course you will! Glad you got it working!

Linq ;0)>
Aug 21 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.