Populating unbound fields in a form. 3

[jamason07]

Good Afternoon!

I'm trying to use the DLookup function to populate 2 or 3 unbound fields on a form based on the selection of a dropdown field on the same form. The information needed to populate the unbound fields is pulled from a query on which the dropdown field is based. Thus...all the info can be pulled from the same query. However, my DLookup is not working. The name of the query is "Names" and I'm trying to use the "Index" field to control the selection to account for multiple similar last names. See below:

Private Sub Form_Current()

Dim LastName As Variant
LastName = DLookup("[LastName]", "Names", "[Index] =" & Forms![Names]!Index)

End Sub

Suggestions?

JAM

 

[RoyVidar]

What is the result? Need more information than "not working", is there any errormsg, is the value it comes up with wrong...

Do you assign the value from your variable LastName to a textbox?

[tt]Me!txtLastName.Value = LastName ' or just

Me!txtLastName.Value = DLookup("[LastName]", "Names", "[Index] =" & Forms![Names]!Index)[/tt]

- all on one line

If you have the lastname and other values alredy in the combo, you could also use:

[tt]Me!txtLastName.Value = Me!cboMyCombo.Column(1)[/tt]

If the Last name is in the combos second column (zero based)

Roy-Vidar

 

[cghoga]

Try this...

Place your code in the On Change event of the combo box.

Private Sub Index_Change()

Me.LastName = DLookup("[LastName]", "Names", "[Index] =" & Forms![Names]!Index)

End Sub

Also, instead of declaring a variable, simply set the value of the unbound textbox on the form. In the above example, Me.LastName refers to the unbound textbox that will display the last name value once the combobox value is changed.

Hope this helps.

 

[GeekGirlau]

I'm with Roy - use the Column option.

Your combo box has already found the data for you, so why look it up again? In the AfterUpdate event for your combo box, add something like the following:

Me!txtLastName.Value = Me!cboMyCombo.Column(1)
Me!txtFirstName.Value = Me!cboMyCombo.Column(2)

This assumes that the data source for the combo box contains the Index, Last Name and First Name in that order.

 

[jamason07]

Mucho gracias everyone. Both solutions worked like a charm...with a little modification. Thanks!

JAM