Access forms 2007- have a value from combox be passed to do a query

[jker]

i have a SALES form.

in that form i have a combox box (CbxSelectCustomer) . user selects from the combo box which is based on CUSTOMER table.

I then want an event to do a sql query of my CUSTOMER table to find the record by matching the value selected from combo box to [customer.CUST_CONCAT_LONG] in my CUSTOMER table.

Then based on that result of the SQL, i want to populate a textbox (TEXT188 FFNAME) on my form from [customer.fname]

Private Sub CbxSelectCustomer_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)

Me!Text188 = Me!CbxSelectCustomer.Column(1)

End Sub

[jker]

[Welshgasman]

Just bring that data in as extra columns of your combo

[xps35]

No need for an extra query. Make sure you select the first name in the query that is the source for the combo:

select CUST_CONCAT_LONG, fname from customer

Then make a after update event on the combo:

me.[TEXT188 FFNAME] = me.CbxSelectCustomer.Column(1)

Note that Column(1) is the second column!

[jker]

No need for an extra query. Make sure you select the first name in the query that is the source for the combo:

select CUST_CONCAT_LONG, fname from customer

Then make a after update event on the combo:

me.[TEXT188 FFNAME] = me.CbxSelectCustomer.Column(1)

Note that Column(1) is the second column!

*************************************
i made an AFTER UPDATE event for the combo box as you said, and i created a new test box with CONTROL SOURCE of FFNAME


what i dont understand is where to include the 'SELECT' statement you are referring to. Can you please explain. I am very new to access so I have a lot of questions.

[xps35]

You do not need an extra text box with a control source. The text box (text188) will get a value in the event.
The sql is the rowsource of combobox. Make sure to set the ColumnCount property to 2.

[jker]

hello,
I found the solution with your help.
1- changed my SELECT statement for the combo box to include all the fields i wanted

2- create a textbox for each field and mapped them in my EVENT. see code below



Private Sub CbxCustomerSelect_AfterUpdate()
Me.[Text202] = Me.CbxCustomerSelect.Column(0) 'customer number
Me.[Text194] = Me.CbxCustomerSelect.Column(3) 'first name
Me.[Text196] = Me.CbxCustomerSelect.Column(2) 'last name
Me.[Text198] = Me.CbxCustomerSelect.Column(4) 'house number
Me.[Text200] = Me.CbxCustomerSelect.Column(5) 'street
Me.[Text204] = Me.CbxCustomerSelect.Column(6) 'apartment
Me.[Text206] = Me.CbxCustomerSelect.Column(7) 'city
Me.[Text208] = Me.CbxCustomerSelect.Column(8) 'state
Me.[Text210] = Me.CbxCustomerSelect.Column(9) 'zipcode
Me.[Text212] = Me.CbxCustomerSelect.Column(10) 'country

[mike60smart]

Why are you doing this?

If you have a Form based on the Sales Table all you need is a Combobox to select a specific Customer.
All of the additional fields do not need to be displayed.

[Welshgasman]

Do your self a HUGE favour right now.

Give your controls meaningful names. Text202 and whatever other generic name, is going to mean absolutlely nothing to you a few days down the road, nor to any poor sod who has to take it over from you.

hello,
I found the solution with your help.
1- changed my SELECT statement for the combo box to include all the fields i wanted

2- create a textbox for each field and mapped them in my EVENT. see code below



Private Sub CbxCustomerSelect_AfterUpdate()
Me.[Text202] = Me.CbxCustomerSelect.Column(0) 'customer number
Me.[Text194] = Me.CbxCustomerSelect.Column(3) 'first name
Me.[Text196] = Me.CbxCustomerSelect.Column(2) 'last name
Me.[Text198] = Me.CbxCustomerSelect.Column(4) 'house number
Me.[Text200] = Me.CbxCustomerSelect.Column(5) 'street
Me.[Text204] = Me.CbxCustomerSelect.Column(6) 'apartment
Me.[Text206] = Me.CbxCustomerSelect.Column(7) 'city
Me.[Text208] = Me.CbxCustomerSelect.Column(8) 'state
Me.[Text210] = Me.CbxCustomerSelect.Column(9) 'zipcode
Me.[Text212] = Me.CbxCustomerSelect.Column(10) 'country

[Micron]

Then based on that result of the SQL, i want to populate a textbox

Your approach might be OK for one textbox but not for several. It makes no sense to do it that way. You create the query that returns the results you want (based on criteria if required). Your form uses that query for its recordsource and you bind (link) a control on the form for each field in the query. So 8 query fields, 8 controls. They don't all have to be textboxes. In fact, you can use a form wizard and get a basic form in seconds, using that query. I don't advocate that you leave the automated design the way it turns out, but that's another story.