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

[crazyhouse]

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)

[ADezii]

You don't give me much to work on, so I'll make several assumptions. Substitute your own values where required:

Private Sub cboPN_LostFocus()
Dim varDescription As Variant
 
If Not IsNull(Me![cboPN]) Then
  'If the Part Number is a String
  varDescription = DLookup("[Description]", "PN_Table", _
                           "[Part_Num] = '" & Me![cboPN] & "'")
  'If the Part Number is Numeric
  varDescription = DLookup("[Description]", "PN_Table", _
                           "[Part_Num] = " & Me![cboPN])
    Forms!rec_form![txtDescription] = varDescription
End If
End Sub

[NeoPa]

I'd consider using the AfterUpdate event in place of the LostFocus one for better results.

[missinglinq]

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

[crazyhouse]

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.

[missinglinq]

Of course you will! Glad you got it working!

Linq ;0)>