vba code to reference the combobox

[asv1988]

So I have a form, report and a table
What I want to do is to pool data from the table into the report using criteria in the form.
Let’s say the table has columns: “company”, “fund”, “return”, “deviation”, “variance”
On the form (I have it working) I can choose the name of the company from a first combo box and then I can choose a fund from the second combo box.


What I want to do further is to select from the third combo box one of the three variables (column headers from the table - return, deviation and variance)
I have setup the third combo box to show me the three options (return, deviation and variance), now I want to press “OK” button and have a report open that would have a textbox with the company name, another textbox with the fund name and the last textbox with the value of the variable I choose in the third combo box in my form.
I managed to make everything working except the third textbox displaying the value of the variable chosen in the third combobox.
The VBA Code I used is:

Dim table_name As String
Dim rst As DAO.Recordset
    Dim DB As DAO.Database
    Set DB = CurrentDb()
    Dim sql As String
 
sql = "Select * from [my_table] where company=" & Forms![my_form]![first_combo_box]
        Set rst = DB.OpenRecordset(sql)
     
            Third_text_box.Value = rst![forms!my_form!third_combo_box.value]
               
            rst.Update
            rst.Close

it won’t work, however if I change the line:

Third_text_box.Value = rst![forms!my_form!third_combo_box.value]

To

Third_text_box.Value = rst![variance]

It will work and display in the third textbox the value of the variance for the corresponding company and fund.

Any help would be welcome

[June7]

This makes no sense to me and don't understand how your 'working code' can work because [forms!my_form!third_combo_box.value] is not a field in rst. I presume [variance] is a field in rst.

Instead of declaring, setting, opening recordset to retrieve one value, could use a DLookup:

Me.Third_text_box = DLookup("variance","my_table","company=" & Forms![my_form]![first_combo_box])

[ieuan]

So I have a form, report and a table
What I want to do is to pool data from the table into the report using criteria in the form.
Let’s say the table has columns: “company”, “fund”, “return”, “deviation”, “variance”
On the form (I have it working) I can choose the name of the company from a first combo box and then I can choose a fund from the second combo box.
What I want to do further is to select from the third combo box one of the three variables (column headers from the table - return, deviation and variance)
I have setup the third combo box to show me the three options (return, deviation and variance), now I want to press “OK” button and have a report open that would have a textbox with the company name, another textbox with the fund name and the last textbox with the value of the variable I choose in the third combo box in my form.
I managed to make everything working except the third textbox displaying the value of the variable chosen in the third combobox.
The VBA Code I used is:

Dim table_name As String
Dim rst As DAO.Recordset
    Dim DB As DAO.Database
    Set DB = CurrentDb()
    Dim sql As String
 
sql = "Select * from [my_table] where company=" & Forms![my_form]![first_combo_box]
        Set rst = DB.OpenRecordset(sql)
     
            Third_text_box.Value = rst![forms!my_form!third_combo_box.value]
               
            rst.Update
            rst.Close

it won’t work, however if I change the line:

Third_text_box.Value = rst![forms!my_form!third_combo_box.value]

To

Third_text_box.Value = rst![variance]

It will work and display in the third textbox the value of the variance for the corresponding company and fund.

Any help would be welcome

Try:

Third_text_box.Value = Forms![my_form]![third_combo_box]

[June7]

ieuan's post made me reread the original post. The third combobox is a list of 3 fields from table? You want to allow dynamic selection of the field to retrieve data from?

Me.Third_text_box = DLookup(Forms![my_form]![third_combo_box],"my_table","company=" & Forms![my_form]![first_combo_box])

[asv1988]

thanks for the replies

[asv1988]

This makes no sense to me and don't understand how your 'working code' can work because [forms!my_form!third_combo_box.value] is not a field in rst. I presume [variance] is a field in rst.

Instead of declaring, setting, opening recordset to retrieve one value, could use a DLookup:

Me.Third_text_box = DLookup("variance","my_table","company=" & Forms![my_form]![first_combo_box])

As i wrote "it won't work" and your reasoning that [forms!my_form!third_combo_box.value] is not a field in rst is absolutely correct. and your solution Me.Third_text_box = DLookup("variance","my_table","company=" & Forms![my_form]![first_combo_box]) works, however instead of static "variance" in this line I want it to grab whatever the variable is in the third_combobox on my form and then search by that criteria.

[asv1988]

ieuan's post made me reread the original post. The third combobox is a list of 3 fields from table? You want to allow dynamic selection of the field to retrieve data from?

Me.Third_text_box = DLookup(Forms![my_form]![third_combo_box],"my_table","company=" & Forms![my_form]![first_combo_box])

That's exactly what I want, however when I use

Me.Third_text_box = DLookup(Forms![my_form]![third_combo_box],"my_table","company=" & Forms![my_form]![first_combo_box])

on the report it would return "run-time error '3075': syntax error (missing operator) in query expression"

if I put quotation marks around "Forms![my_form]![third_combo_box]" then it would return only what was in the third_combo_box i.e if there was variance then it would return "variance" and not the table value of "1.4" for the "variance".

again if I type static variable into the code, like the code below

Me.Third_text_box = DLookup("variance","my_table","company=" & Forms![my_form]![first_combo_box])

it would result in what i need, i.e the value of "1.4" for "variance", but the whole idea is to be able to choose the search criteria.

Any ideas?

Nevertheless thanks for the time you take to help me

[June7]

Seems the last DLookup I suggested should work but try this:

Me.Third_text_box = DLookup("[" & Forms![my_form]![third_combo_box] & "]","my_table","company=" & Forms![my_form]![first_combo_box])

If still need help, provide db for analysis. Follow instructions at bottom of my post.

[asv1988]

Seems the last DLookup I suggested should work but try this:

Me.Third_text_box = DLookup("[" & Forms![my_form]![third_combo_box] & "]","my_table","company=" & Forms![my_form]![first_combo_box])

If still need help, provide db for analysis. Follow instructions at bottom of my post.

June7 you are the best! It worked like a charm!!!!!!

[ssanfu]

I have been following this thread and tested what June gave you.... it should have worked.
As the control source for "Third_text_box", this worked - after I corrected all of my misspellings

=DLookUp([Forms]![my_form].[third_combo_box],"my_table","company = " & [Forms]![my_form].[first_combo_box])

No problems....???



In case you wanted to use the "OK" button, I re-wrote your code:

'Private Sub cmdOK_Click()  ' my button name
   Dim DB As DAO.Database
   Dim rst As DAO.Recordset
   Dim sSQL As String
   Dim WhatField As String

   Set DB = CurrentDb

   'get the field name
   WhatField = Forms!my_form.third_combo_box

   sSQL = "SELECT " & WhatField & " FROM my_table WHERE company = " & Forms!my_form.first_combo_box

   Set rst = DB.OpenRecordset(sSQL)

   'return the value
   Me.Third_text_box = rst(WhatField)

   rst.Close
   Set rst = Nothing
   Set DB = Nothing
'end sub

[ssanfu]

Darn!! Late again....