Field values based on user selection in combo

[martian]

Hello,

I'm stuck! I want to create a form which has a combo box where you select
someone's name, and their address appears in another text box on the same form. The names and addresses are in a table called "cardholders".

I'm doing separate combos for first name, surname cos I think that's the only
way you can do it.

The fields in the table are credit_add, credit_name & credit_surname. The combos are called "cardholder name" "cardholder surname" etc. This is the code in the text box where I want the address to appear:-

=DLookUp("[credit_add]","cardholders","[credit_name] = '" & [cardholdername] & "' And [credit_surname] = " & [cardholdersurname])

Can anyone help?

Thanks.

 

[statsman]

Ensure that the combo box you are using has all of the fields from your table you want to place onto your form. Create a new combo box if you have to. You can adjust the fields in the existing combo box but its easier to just delete the old combo box and create a new one.

In the form’s design view, click on the combo box. Access will have assigned the combo box a number (for this demonstration we’ll say it’s Combo7 )

The columns in the combo box are numbered. They start at the left hand column with number 0 (zero) and go up one number at a time as you go to the right. The third column from the left would therefore be column 2.

Right click on the combo box and select “Build Event”, then select “Code Builder”. In the top right corner of the new screen select “AfterUpdate”. On the screen, the cursor should now be in the coding area for the “AfterUpdate” event. It will look like this:

-------------------------------------------------------------------------------------------
Private Sub Combo7_AfterUpdate()

End Sub
---------------------------------------------------------------------------------------------

In the line between
Private Sub Combo7_AfterUpdatek()
And
End Sub
enter:

Me![fieldname] = Me![Combo7].column(#)

The coding area should now look like this:

-------------------------------------------------------------------------------------
Private Sub Combo7_AfterUpdate()

Me![fieldname] = Me![Combo7].column(#)

End Sub
-----------------------------------------------------------------------------------------

You can add new blank lines just by pressing enter on your keyboard.

The [fieldname] is the nameor number of the field on the form, the column(#) is the number of the column in the combo box that has the data you want to place into that field.
Place as many lines of code in the “AfterUpdate” area as you have fields on the form you want to fill automatically when you make a selection from the Combo box. Each field requires a separate line. If you selected a field to be entered on the form when you created your combo box, that field will not require a line of code.

Save your changes by clicking on the floppy icon or by using File -> Save, then click on the “X’ in the top right corner. You should be returned to the form design screen.

If it doesn’t work properly you’ve probably misspelled something or you got the bracketing wrong.

 

[gromit]

Hi - and welcome!

The technique that you are looking for is generally referred to as cascading combo boxes. See this thread for some more explanation.
http://www.access-programmers.co.uk/forums/showthread.php?t=97680

Post back if you still have questions,

- g

 

[martian]

Thanks very much for your help. I think I am getting closer as I am now managing to get the "no results" message in the address box as a result of the code that I copied and modified from the link you suggested.

Unfortunately I am now getting a message saying "Run time error 2471: The object doesn't contain the Automation object 'paige". Paige is a value from my cardholders table which is the row source for my cardholdersurname combo on my form.

Can you think of anything?

Here is the code:-

Option Compare Database
' Set after update for Region combo
Private Sub cardholdername_AfterUpdate()
UpdateTitle
End Sub

' Set after update for Title combo
Private Sub cardholdersurname_AfterUpdate()
UpdateTitle
End Sub

Private Sub UpdateTitle()
Dim varName As Variant ' declare a variable for the name

' Check if either combo box is null
If (IsNull(Me.cardholdername) Or IsNull(Me.cardholdersurname)) Then
varName = ""
Else
' Look up the title
varName = DLookup("[credit_add]", "cardholders", "[credit_surname] = " & _
Me.cardholdersurname & " AND [credit_name] = " & _
Me.cardholdername)
End If

' if the person is not found, then indicate it
If (varName = "" Or IsNull(varName)) Then
Me.cardholder_address = "(None found)"
' Otherwise, put name into the text box
Else
Me.cardholder_address = varName
End If
End Sub


Private Sub cardholdername_BeforeUpdate(Cancel As Integer)

End Sub