checkboxes and VBA not working (1 Viewer)

[Summer123]

Hello,
i have 4 checkboxes and have differnet queries that go along with it that i want to export to Excel. So the way i written the code is as follows,

So if chkbox1 is clicked then qry1 is run and exported
if chkbox2 is clicked then qry2 is run and exported
if chkbox1 and 2 are clicked then qryall (seprate qry then qry1 and qry2) is run and exported

For Each ctl In MyForm.Controls
Select Case ctl.ControlType
Case acCheckBox
If qry1.Value = True Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry1", fileIn, True, "Export"
ElseIf qry2.Value = True Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry2", fileIn, True, "Export"
ElseIf qry1.value = True and qry2.Value = True Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryall", fileIn, True, "Export"
End If

End Select


however what happens is if i choose both checkboxes then it only reports out the results from checkbox 1... can anyone help to see what i am doing wrong?

 

[pbaldy]

An ElseIf is only checked if the preceeding If (or any preceeding ElseIf's) was false. You want

If qry1.Value = True Then
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry1", fileIn, True, "Export"
End If
If qry2.Value = True Then
  ...
End If

 

[Summer123]

hey thanks..that was really dumb of me..thank you though!

 

[pbaldy]

No problem. By the way, what's the purpose of the loop and Select/Case? They don't seem relevant to what you're doing, and could cause the queries to be run multiple times.

 

[Summer123]

thanks for pointing that out..actualy i had a different code previousaly where i was lloking at multiple checkboxes but i kept that in there..not thinking... so thank you