Forum Discussion
Assign information from Query to a Form Field
Thanks for clarifying the context. Sometimes more information is better than less in trying understand a problem 😉
So, if your existing combo box already has a row source that returns the customer information, you can use that in the first suggested approach, I think. Add the required fields as additional columns to the rowsource for that combo box. Then, set the control sources for the other controls as, for example,
=cboCustomer.Column(1),
=cboCustomer.Column(2)
and so on.
where Column(1) is the second column, left to right. Combo and listboxes are indexed starting with 0 so column(0) is the first one on the left, and the one normally bound to the control. Other columns simply return relevant additional fields, as in this case.
That's probably the least complicated approach, assuming that you can add those fields to the rowsource for that combo box.
The Recordset approach would also work, and may be worthwhile if you want to learn a new technique in the process.
It might look something like this:
Private Sub Form_Load()
Dim strsql as String
Dim db as DAO.Database
Dim rst as DAO.Recordset
Set db = CurrentDB
strsql = "SELECT SelectionID, SalesAdministrator, Customer, Week, Commodity FROM FilterCriteria
WHERE (SelectionID)=1 ;"
x = InputBox("", "", "' & strsql.Customer & '") ' Validation only, not used in procedureSet rst = db.OpenRecordSet(strsql, dbOpenSnapShot)
With rst
Me.Combo58.Value = !Customer
Me.WeekCombo.Value = !Week
Me.CommodityCombo.Value = !Commodity
End With
Set rst = Nothing
DoCmd.Requery
End Sub
It looks to me like you are assigning the SQL String created in the first line of code as a VALUE in the combo box, where you actually want the RESULT from the execution of the SQL String. You can see that in the screenshot, which shows the & and other parts of that string.
You are also assigning a string to the variable X, but then never doing anything with X.
Also, I see that you have not Dimmed you variables, strsql and X. Good programming practice is to ALWAYS declare variables to avoid problems arising from their misuse. Unfortunately, that wouldn't help much here where "X" is never used for anything.
To resolve this, there are two possible routes.
One is to use that sqlstring, or perhaps another one more appropriate to the situation, as the ROWSOURCE for a combo box. Then your other fields Combo58, etc. can be populated by referring to the pertinent column in that combo box.
The other is to open a recordset using that SQL string. Then you could refer to the fields in that recordset where you want to assign their values to the other controls.
I'd use the former approach, but a recordset is totally viable.
- Thanks.
The x = inputbox is there just to show me what is being picked up in the strsql so I can visually see it for testing to make sure the right thing is being picked. It is not relevant to what I am trying to do. I can comment that out.
With regards to the combo box row source, it already has one in there that I use to select the customer for example.
How would I open a recordset? I have not done this before.Thanks for clarifying the context. Sometimes more information is better than less in trying understand a problem 😉
So, if your existing combo box already has a row source that returns the customer information, you can use that in the first suggested approach, I think. Add the required fields as additional columns to the rowsource for that combo box. Then, set the control sources for the other controls as, for example,
=cboCustomer.Column(1),
=cboCustomer.Column(2)
and so on.
where Column(1) is the second column, left to right. Combo and listboxes are indexed starting with 0 so column(0) is the first one on the left, and the one normally bound to the control. Other columns simply return relevant additional fields, as in this case.
That's probably the least complicated approach, assuming that you can add those fields to the rowsource for that combo box.
The Recordset approach would also work, and may be worthwhile if you want to learn a new technique in the process.
It might look something like this:
Private Sub Form_Load()
Dim strsql as String
Dim db as DAO.Database
Dim rst as DAO.Recordset
Set db = CurrentDB
strsql = "SELECT SelectionID, SalesAdministrator, Customer, Week, Commodity FROM FilterCriteria
WHERE (SelectionID)=1 ;"
x = InputBox("", "", "' & strsql.Customer & '") ' Validation only, not used in procedureSet rst = db.OpenRecordSet(strsql, dbOpenSnapShot)
With rst
Me.Combo58.Value = !Customer
Me.WeekCombo.Value = !Week
Me.CommodityCombo.Value = !Commodity
End With
Set rst = Nothing
DoCmd.Requery
End Sub- Thats Amazing... Thanks so much for your help George. It works.
