VBA Access creating query from combo boxes

[dprayner]

Hi everyone.

I am trying to create a query based on responses from a user on a Access form. What I am trying to do exactly is there is a create the query from a table named WCNVACCT which has about 12 fields name, account number, etc. The first combo23 will ask form the item (ie. name) and the second combo25 will ask for the detail of that item (ie "John Smith"). I was trying to do something like this in the event procedure of a command button: SELECT combo23.Value FROM WCNVACCT WHERE = Combo25.Value.

Any suggestions? DAVE

 

[xlbo]

yup - so what issues are you having with that ?

mySQL = "SELECT " & combo23.Value & "FROM WCNVACCT WHERE " & combo23.Value & "= " & Combo25.Value

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

... and remember to insert spaces before and after appropriately...

mySQL = "SELECT " & combo23.Value & " FROM WCNVACCT WHERE " & combo23.Value & "= " & Combo25.Value

and if combo23.value is a TEXT field, don't forget the tics...

mySQL = "SELECT " & combo23.Value & " FROM WCNVACCT WHERE " & combo23.Value & "= '" & Combo25.Value & "' "

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[dprayner]

Hi guys.

I am very new to Access. How would passing the criteria string to the variable mySQL invoke a query? Do I have to do something further with the mySQL variable? DAVE

 

[xlbo]

yup
...maybe a little look in the help files.....DoCmd & RunSQL would be keywords to search on..

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[dprayner]

Hi Geoff.

I am getting an error when I run the following code:

Private Sub cmdQuery_Click()
Dim mySQL As String
On Error GoTo Err_cmdQuery_Click

mySQL = "SELECT " & Combo23.Value & " FROM WCNVACCT WHERE " & Combo23.Value & "= '" & txtQuery.Value & "' "
DoCmd.RunSQL mySQL
Exit_cmdQuery_Click:
Exit Sub

Err_cmdQuery_Click:
MsgBox Err.Description
Resume Exit_cmdQuery_Click

End Sub

I decided to go with a ComboBox for the fields (column names) and a textbox for the items in the associated column.

When I click on the command button I get: A RunSQL action requires an argument consisting of an SQL statement.

I'm not sure I am passing the variables to the SELECT statement corrently. The comboBox holds for example Names and the textBox the user would type in Geoff to Query for all the Geoff entries. Can you help? DAVE

 

[PHV]

The RunSQL method don't admit a SELECT statement.
You may consider having a Listbox in your form, and then:
Private Sub cmdQuery_Click()
Dim mySQL As String
mySQL = "SELECT [" & Me!Combo23 & "] FROM WCNVACCT WHERE [" & Me!Combo23 & "]='" & Me!txtQuery & "'"
Me!ListBox.RowSource = mySQL
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

Apologies - have a look at the OpenQuery method of DoCmd - it will accept a query name or SQL string

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question