Run different queries based on value of Combo Box. 1

[dtay1132]

Looking for some help running queries based on a combo box. Have looked through the forums and couldn't find anything on running different queries based on values.

I need to allow the user to switch back and forth between two different recipients of documents that are to be printed and mailed. To do this, I created one table that will store the information for the recipient which will be printed as an address. I have a form called F_MailRequest with a combo box (Combo60) that the user can select one of 2 different recipient types from . I need to be able to run one of two different update queries based on the value of the combobox. The values in the box are from a table called tbl_shipselections. There are two columns in the combo box and the bound column is #1. Values are 1 Owner, 2 LH. When the value of the combo box is ID 1 "Owner" I want to run a query called qry_UPD_MReq_RecipientShip (Yes I know that is a long name). When the value of the combo box is ID 2 "LH" I want to run a query called qry_UPD_MReq_LH.
In the AfterUpdate for the combo box I have the following code.

Private Sub Combo60_AfterUpdate()
Select Case Me.Combo60.Column(1)
Case 1
DoCmd.OpenQuery "qry_UPD_MReq_ReceipientShip"
Case 2
DoCmd.OpenQuery "qry_UPD_MReq_LHShip"
End Select
Form_F_MailRequest.Requery
End Sub

I don't get any errors, but the update queries won't run. Any help would be greatly appreciated.

 

[dhookom]

Try some of the tips for debugging at faq705-7148. Let us know what you learn or don't understand.

Duane
Hook'D on Access
MS Access MVP

 

[Prattaratt]

Use database.QueryDefs.execute instead of DoCmd.Open. Try using something like this:

Private Sub Combo60_AfterUpdate()
dim Qname as string
Dim DB as Database
Dim RowsChanged as Integer
Set DB = CurrentDB
  Select Case Me.Combo60.Column(1)
    Case 1
      QName = "qry_UPD_MReq_ReceipientShip"
    Case 2
      QName ="qry_UPD_MReq_LHShip"
  End Select
  RowsChanged = DB.QueryDefs[QName].Execute.
  Form_F_MailRequest.Requery
End Sub

 

[dtay1132]

Thanks Prattaratt, I got a syntax error using that code on the line with the RowsChanged.

 

[PHV]

Replace this:
RowsChanged = DB.QueryDefs[QName].Execute.
with this:
DB.QueryDefs(QName).Execute dbFailOnError

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

 

[MajP]

That line is jacked up

 RowsChanged = DB.QueryDefs[QName].Execute.

1. execute is a method not a function so you would have to do
DB.QueryDefs[QName].Execute.
2. Cannot use brackets for an index of a collection so
DB.QueryDefs(QName).Execute.
3. Typo with a period at the end so
DB.QueryDefs(QName).Execute

To get the recordschanged you would have to change it up some to use the rowsaffected method of the DAO querydef object

Something more like

Private Sub Combo60_AfterUpdate()
   dim Qname as string
   dim qdf as dao.querydef
   Dim RowsChanged as Integer
   Select Case Me.Combo60.Column(1)
    Case 1
      QName = "qry_UPD_MReq_ReceipientShip"
    Case 2
      QName ="qry_UPD_MReq_LHShip"
   End Select
   debug.print QName
   set qdf = currentdb.querydefs(QName)
   qdf.Execute
   rowschanged = qdf.rowsaffected
   Form_F_MailRequest.Requery
End Sub

 

[Prattaratt]

Yes, you are correct in that it should be parentheses instead of brackets. I have just been coding in C++ and Delphi instead of Basic for a long stretch and just got accustomed to their style of indexing. MajP's comments are all on target.

 

[dtay1132]

Thanks MajP. Using your proposal I received a different error. Compile Error: Method or Data member not Found.

 

[dtay1132]

Should have mentioned the portion of the code that is highlighted is .rowsaffected

 

[MajP]

Sorry the method should be
.recordsaffected

http://msdn.microsoft.com/en-us/library/office/ff192040(v=office.15).aspx

not .rowsaffected

 

[dtay1132]

Maj - Now getting a runtime error 3265 Item not found in this collection. Row highlighted is

Set qdf = CurrentDb.QueryDefs(Qname)

I am not familiar with QueryDef object so am not sure hot to troubleshoot. Here is what I have currently

Dim Qname As String
Dim qdf As dao.QueryDef
Dim RowsChanged As Integer
Select Case Me.Combo60.Column(1)
Case 1
Qname = "qry_UPD_MReq_ReceipientShip"
Case 2
Qname = "qry_UPD_MReq_LHShip"
End Select
Debug.Print Qname
Set qdf = CurrentDb.QueryDefs(Qname)
qdf.Execute
RowsChanged = qdf.RecordsAffected
Form_F_MailRequest.Requery

 

[dhookom]

Make sure the query names are spelt correctly.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

qry_UPD_MReq_ReceipientShip?

 

[dtay1132]

Ok, fixed the spelling. Actually shortened the query names completely. Getting run-time 3265 Item not found in this collection. Row affected is Set qdf = CurrentDb.QueryDefs(Qname. Is there some other object library I need to have slected? This is an Acess 2010 db.

Dim Qname As String
Dim qdf As dao.QueryDef
Dim RowsChanged As Integer
Select Case Me.Combo60.Column(1)
Case 1
Qname = "qry_UPD_RShip"
Case 2
Qname = "qry_UPD_LHShip"
End Select
Debug.Print Qname
[highlight #FCE94F]Set qdf = CurrentDb.QueryDefs(Qname)[/highlight]
qdf.Execute
RowsChanged = qdf.RecordsAffected
Form_F_MailRequest.Requery

 

[MajP]

Change this line
Debug.Print Qname
to
Debug.Print "Query Name = " & Qname

Then show what is printed. The error message is quite explicit. Whatever value you are returning from the combo box is not the name of a query.
My guess is that we really want the value of the first column not LH. The first column is index of 0

So either change to
Select Case Me.Combo60.Column(0)
or simply
Select Case Me.Combo60.value ' since bound

 

[dhookom]

You can fill the row source of the combo box with a query like:

SELECT Name
FROM msysobjects
WHERE type = 5 AND Name like "qry_UPD_*"
Order BY Name

Duane
Hook'D on Access
MS Access MVP