Assign information from Query to a Form Field

[ajcbutler1120]

Hi

I am having an issue with trying to populate information from a query with a single line into a field in a form.

Can someone let me know what I am doing wrong with my Code?

Private Sub Form_Load()
strsql = "SELECT SelectionID, SalesAdministrator, Customer, Week, Commodity FROM FilterCriteria WHERE (SelectionID)=1 ;"
x = InputBox("", "", "' & strsql.Customer & '")
Me.Combo58.Value = "' & strsql.Customer & '"
Me.WeekCombo.Value = "' & strsql.Week & '"
Me.CommodityCombo.Value = "' & strsql.Commodity & '"
DoCmd.Requery
End Sub

It is to populate these fields which are blank at the top.

You can see it is not putting the value of the query field in the first field.

First field should be Text, Second field is a number and third field is text.

I would really appreciate some help.

Thanks

Aaron

[#2]

@ajcbutler1120 

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.

[#3]

I'm not even sure what happens with that Input Box, to be frank. What does it actually do when you try to use it?

[#4]

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.

[#5 · Accepted solution]

@ajcbutler1120 

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 procedure

    Set 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

[#6]

Thats Amazing... Thanks so much for your help George. It works.

[#7]

Congratulations on solving the problem.
Continued success with the project.