Need help creating Auto Lookup or Autofill on a Form

[apryor]

I have a form that has a combo box, which contains contact names. Once a name is selected from the combo box, I'd like it to automatically fill the Address, City, State and Zip. I've seen solutions on this but I just can't seem to make it work. Here are the details

Table = tblNI
NIID
NIName
NIAddress
NICity
NIState
NIZip

Combo42

Private Sub Combo42_AfterUpdate()
Me.NIAddress = Me.Combo42.Column(2)
Me.NICity = Me.Combo42.Column(3)
Me.NIState = Me.Combo42.Column(4)
Me.NIZip = Me.Combo42.Column(5)
End Sub

Thanks in advance
apryor

 

[cghoga]

Check out the Dlookup function or you could probably write an SQL statement to set the controlsource of the form based on the name selected.

Since it's a combo box you might try inserting your code in the On Change event.

Hope this helps.

 

[apryor]

I checked out the Dlookup function but I'm still confused. Am I doing something wrong with the VBA code above? Or in my form design?

The fields on the form are from the table (tblNI) shown above, and I added the combo box based on values listed from the NIName value in the table.

I'm still a bit new to Access so you'll have to forgive my ignorance.

 

[RoyVidar]

Whether you are right or wrong, would depend on the rest of the setup, seing the last reply, I'm inclined to believe it's wrong;-)

I suspect you have a bound form, then what you need, is what you get if you use the combobox wizard, use the third option "find a record on my form...", which will produce some lines of code with similarities to the below:

[tt]dim rs as dao.recordset
set rs=me.recordsetclone
rs.findfirst "id = " & me!Combo42.value
if not rs.nomatch then me.bookmark=rs.bookmark
set rs=nothing[/tt]

Substitutue id with the name of your primary key field. Would need a reference to Microsoft DAO 3.# Object library (in VBE - Tools | References)

If you assign values to controls based on the columns of a combo, you'll be replacing the values (altering the records), in stead of moving to the correct record.

Roy-Vidar

 

[apryor]

It's working, thanks for all the help.

Arend