Select statement built from combo box 1

[glyrocks]

I'm trying to create a simple form that builds a select statement based on two comboboxes. I was following a tutorial online, but have gotten stuck. I think I have the combobox and variables set up correctly, but I'm new to this whole coding thing and am not sure what needs to happen next. When I try to execute the code, I get an error: "Object required." Below is my code; I can produce the SQL query as I expect it to look, so I suppose the problem has to do with connecting to the database and actually executing/returning the query. Thanks for any help and suggestions!

dylan

Private Sub BtnQuery_Click()
On Error GoTo Err_BtnQuery_Click

      

Dim StrCID As String
Dim StrTable As String

Select Case Combo2.Value
    Case "Avery"
        StrCID = "37011"
    Case "Buncombe"
        StrCID = "37021"
    Case "Cleveland"
        StrCID = "37045"
    Case "Haywood"
        StrCID = "37087"
    Case "Henderson"
        StrCID = "37089"
    Case "Jackson"
        StrCID = "37099"
    Case "Madison"
        StrCID = "37113"
    Case "Mitchell"
        StrCID = "37121"
    Case "Polk"
        StrCID = "37149"
    Case "Rutherford"
        StrCID = "37161"
    Case "Swain"
        StrCID = "37173"
    Case "Transylvania"
        StrCID = "37175"
    Case "Yancey"
        StrCID = "37199"
    Case Else
        StrCID = InputBox("Enter StateFIPs with CID (e.g. 37099)", "Unknown County", "37000")
End Select
  
'I suspect the problem is below; I don't quite get the DAO.Database stuff.
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = dbs.QueryDefs("BASELOCALIMGSRC Query")
strSQL = "SELECT " & Combo7.Value & ".* " & "FROM BASEIMGGRP WHERE IMGGRPID='" & StrCID & "*'"
'MsgBox ("qdf.SQL: " & strSQL)

qdf.SQL = strSQL

DoCmd.OpenQuery "BASELOCALIMGSRC Query"
DoCmd.Close acForm, Me.Name

Dim stDocName As String

stDocName = "BASELOCALIMGSRC Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit
  


Set qdf = Nothing
Set db = Nothing

Exit_BtnQuery_Click:
    Exit Sub

Err_BtnQuery_Click:
    MsgBox Err.Description
    Resume Exit_BtnQuery_Click
    
End Sub

 

[PHV]

Replace this:
Set qdf = [!]dbs[/!].QueryDefs("BASELOCALIMGSRC Query")
with this:
Set qdf = [!]db[/!].QueryDefs("BASELOCALIMGSRC Query")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

This:
"SELECT " & Combo7.Value & ".* " & "FROM BASEIMGGRP WHERE IMGGRPID='" & StrCID & "*'"

Cannot be correct unless Combo7 always equals 'BASEIMGGRP'.
What does Combo7 equal?

In addition, you would find it easier to have two columns in Combo2, one column for the name and one for the CID. That will eliminate the whole first section of code:

[tt]strCID=Combo2.Column(1)[/tt] 'Numbering of columns starts at 0

Finally, always give you controls 'real' names, you will thank yourself later.

 

[glyrocks]

Thanks, PHV- that fixed it. I appreciate the quick response.

Can you-or anyone else--explain what the difference is? Why the 's' was wrong in my case, but didn't throw a syntax/compile error?

Remou- you are correct, that code only works for one particular situation. I was hoping to get one situation worked out, and then expand it. Thanks for the other advice, I'm already wishing I had real names, and I only have two poorly-named controls.

Thanks again,

dylan

 

[PHV]

didn't throw a syntax/compile error
Tip: use the Option Explicit instruction.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[glyrocks]

thanks, appreciate the help

dylan