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
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.
- Congratulations on solving the problem.
Continued success with the project.