Dlookup in Textbox

[lals101]

Hi,
I have a register with a list of IDs names addresses etc stored in a table.
In a form I have a text box where an individual enters the ID number, instead of filling out name and addresses again, could i simply populate/look up the names and addresses from the ID number and enter them in the other fields.

So when an ID number is entered in one text box their Name appears in another textbox below. I looked on the net and figured I could use a dlookup function, on the afterupdate event on the text box where the ID number is entered.

would anyone know a specific solution

thanks in advance

 

[vbaInet]

The concept is to filter your records using your form's Filter and FilterOn properties. Have a look at this link:

http://allenbrowne.com/ser-62.html

 

[namliam]

Noooooooooooooooooooo, not a DLookup, DLookup is hardly ever the right solution...

In this case you can 'simply' add the required information to the combobox, if you want you can hide the information by making the column 0 wide.

Then use
Me.Yourcombo.Column(0)
To retrieve the information, where 0 is the first (ID) value of your combobox, 1 is then the second etc...

Good luck !

 

[lals101]

How come "DLookup is hardly ever the right solution", Using combo boxes still means the user would have to enter name and address. This can be negated surely is after typing their ID number the name and address fields are automatically entered. The use of a combobox is likely to induce error, ie an address for the incorrect ID.

I'm not sure how the on filter function would work in my case, could you be more specific

thanks

 

[JANR]

Then use
Me.Yourcombo.Column(0)
To retrieve the information, where 0 is the first (ID) value of your combobox, 1 is then the second etc...

Since the combobox already has retrived the info you need, it is redundant to use several data fetches for the same info.

You can include hidden fields in a combobox and by refrencing those hidden columns you acces your info/data.

JR

 

[lals101]

ah i see what you mean, so the combo box would have all the information in it, and i could customise which items of data i see. I see the use of this, but it is not what i really need.

Thank for your help anyhow

 

[JANR]

A combobox is just like a query example:

SELECT ID, fName, lName, Adress, ZipCode 
FROM TblContacts;

Here I have 5 fields. If I want to hide all but the ID field I would set the Column count of the combobox to 5
The column width to 2cm;0cm;0cm;0cm;0cm

Now i have hidden all but the ID field so to refrence the hidden fields in Unbound textboxes of the form, just use this syntax in the ControlSource.

For the FirstName:

=cboClient.Column(1)

For the Lastname

=cboClient.Column(2)

Just remember that the combobox is zero-based ie the first field has a index of 0

Hope this helps.

JR

 

[lals101]

Great thanks, but does it have to be an unbound text box?

 

[JANR]

Yes in this case, since a textbox only has 1 control source. If you are going to add the vaues to a bound control then you must use the After_Update event of the combobox and use code to set the values:

Private Sub cboContacts_AfterUpdate()
Me.txtboxFname = Me.cboContacts.Column(1)
... etc
End Sub

BUT!!!! that goes usually against normalization and you shoulden't do that.

JR

 

[namliam]

BUT!!!! that goes usually against normalization and you shoulden't do that.

BUT!!!! that goes usually against normalization and you shoulden't do that.

BUT!!!! that goes usually against normalization and you shoulden't do that.