Searching Using a Combo Box

J

Jim O.

I'm using a form with a combo box that has various companies listed. When I
click on the company I would like the information about the company to fill
in the text boxes I have labeled for the company name, address, etc.. Can
anyone help point me in the right direction?
 
K

kingston via AccessMonster.com

Construct the combo box so that all of the necessary information is displayed.
IOW, the query that populates the combo box will have multiple fields (and
columns). Then use the combo box's After Update event (select the control in
form design mode and in the Event tab, select [Event Procedure] in the After
Update block). In the procedure, set the control values like this:

Me.Address = Me.ComboBox.Column(1)

Replace Address with the actual name of the control (as well as ComboBox) and
choose the appropriate column number (0 is actually the first column).
 
A

amsuria

You can try this....

In your Combo Box "ON CHANGE" Event Procedure, insert this codes,

Dim xCompanyCode As String
Dim rs As ADODB.Recordset

cmbCompany.SetFocus 'assuming that your combobox name is cmbCompany
xCompanyCode = Trim(cmbCompany.Text)

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "SELECT * FROM <CompanyTable> WHERE (((CompanyTable.
Code: 
)= " & "'"
& Trim(xCompanyCode) & "'" & " ));", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

txtName.SetFocus   'txtbox for your company name field
txtName.Text = rs.Fields("CompanyName").Value

txtAddress.SetFocus   'txtbox for your company address field
txtAddress.Text = rs.Fields("CompanyAddress").Value

txtTelephone.SetFocus   'txtbox for your company phone field
txtTelephone.Text = rs.Fields("CompanyPhone").Value


rs.Close


hope i can help...

amsuria
 
M

missinglinq via AccessMonster.com

I've never figured out why people try to make this so difficult. The Combo
box Wizard is set up to do just this. It takes about 90 seconds to do! Scrap
the combo box yuou've already got and use the Wizard!

First you must have your form set up to display the record you want to
retrieve, i.e. you must have text boxes set up with the appropriate Control
Sources for the retrieved data. Then you simply:

Add a combo box to your form. The Combobox Wizard will pop up
Select "Find a record based on the value I selected in my combobox."
Hit Next.
Click on the field you're searching by (in this case Company Name) to move it
to the right side.
Hit Next.
Size the column appropriately.
Hit Next.
Name the combobox.
Hit Finish.

Now you can drop the combobox down and scroll down to the item to search by,
or you can start to enter the item, and the combobox will "autofill" as you
type.
Hit <Enter> and the company's date will be retrieved.
 
A

amsuria

MISSINGLINQ is indeed right!

now I know how to make life simplier! lol!....if not because of this, ill
never learn that this is really sooo easy!!!..

thanks...


amsuria
 
M

missinglinq via AccessMonster.com

Life is way too short to waste time "re-inventing the wheel!"
MISSINGLINQ is indeed right!

now I know how to make life simplier! lol!....if not because of this, ill
never learn that this is really sooo easy!!!..

thanks...

amsuria
I've never figured out why people try to make this so difficult. The Combo
box Wizard is set up to do just this. It takes about 90 seconds to do! Scrap
Click to expand...
[quoted text clipped - 19 lines]
type.
Hit <Enter> and the company's date will be retrieved.
Click to expand...
Click to expand...
 
M

Margo

Hi, I have mastered the combo box, but my problem is that the info that I
want displayed in the other fields are not on my form, they are in another
table. e.g. I have a combo box that looks up "Position" in the Operators
table, from this returned value, I need Access now to fill in the operators
field as the position and operator is directly linked. How can I do this?
Keeping in mind that I am very rusted in the programming area. Thanks
 
Top