Creating a text box similar to a combo box (1 Viewer)

Spam808

Registered User.
Local time
Today, 14:49
Joined
Dec 3, 2018
Messages
55
Currently I have a combo box display a list of items. When you select an item it populates the other text boxes. Instead of using a combo box I would like to use a text box by typing the name of the item. How would you do this?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:49
Joined
Oct 29, 2018
Messages
21,358
Hi,

Lots of different ways but one way is to use DLookup().
 

Spam808

Registered User.
Local time
Today, 14:49
Joined
Dec 3, 2018
Messages
55
Hi theDBguy,

How should I adjust my current coding, comname2 is my combo box.

Private Sub ComName2_AfterUpdate()
Me.Name2 = Me.ComName2.Column(1)
Me.Type2 = Me.ComName2.Column(18)
Me.Class2 = Me.ComName2.Column(5)
Me.Growth2 = Me.ComName2.Column(6)
Me.Rate2 = Me.ComName2.Column(3)
Me.Unit2 = Me.ComName2.Column(4)
End Sub
 

Minty

AWF VIP
Local time
Today, 21:49
Joined
Jul 26, 2013
Messages
10,355
To be fair using a combo is by far the easiest and most reliable method.
It has numerous advantages over a normal text box for this type of operation.

Firstly all the data you want is already there - as you are doing at the moment, simply retrieve the column values.

Secondly Users can't mis-type and enter something that doesn't exist, and if they do you have a selection of events you can use to handle that.

With a textbox, you would have to do the lookup or open a recordset to find the text, handle the fact it wasn't found, or do a load of other DLookups if it was found. (all of which is significantly slower).

What is steering you away from the combo ?
 

Dangerous

Registered User.
Local time
Today, 21:49
Joined
Oct 18, 2018
Messages
73
I'm new to Access and I started using Dlookup/Lookup which worked. Then I was shown how to use combo boxes and found them so much more flexible and easier. They can be set so that the user can only select data that is in the list or by using a NotInList event you can add a new item to the list but not do so accidentally, A messagebox will pop up telling you what has been typed is not in the list and ask if you want to add it with the options of yes or no.

I'll not go back to Dlookup/Lookup.

I can't help/advise you with how to use them, only let you know that I find combo boxes so much easier.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:49
Joined
Feb 19, 2002
Messages
42,976
The advantage the combo gave you was the RowSource. When you typed in the control, behind the scenes, Access was looking up the value in the RowSource query and bringing along all the other columns. You can certainly do that yourself, you just have to code the lookup separately. dLookup() works fine when you have only a single piece of data you need to lookup up but when you have multiple, the best solution is to open a recordset based on a query. To avoid all the searching code, use a query with a where clause. The Where clause will reference your textbox.

Select ...
From ...
Where SomeField = Forms!yourform!yourcontrol;

Open the RecordSet and if a record is found, fill the other controls just as you are doing now but instead of referencing the combo, just reference the RecordSet.
 

Users who are viewing this thread

Top Bottom