VBA code for Running Multiple Queries in Access 2010 using If elseif statement

[Hans]

Dear friends, I am fairly new to VBA coding in Access and I am trying to create a code to run multiple queries using same value.
I have 3 queries for Customer Material and I want to run these queries by passing a value of customer to find all the materials the customer can order.

On running the code, I would like to input customer number and want Query1 to run and
if the Query1 is success then display all records and stop


else run Query2 using the same parameter value (in this case Customer # so that I don't have to enter the same customer# again for the 2nd query) and if the Query2 is success then display all records and stop
else run Query3 using the same parameter value and if the Query3 is success then display all records and stop
else display records from a table tbl_material.

I tried DoCmd.OpenQuery command but the if elseif statement is not working and it run all the 3 queries and table and display results in 4 separate tabs and if I use parameter in each of the queries as criteria then the code ask me customer # every time a query execute.
I would really appreciate all your help

Thank you very much in advance!!!

[orange]

Welcome to the forum.

What exactly do you mean by
" if the Query1 is success then display all records and stop"?

A query to select records is not considered a failure if 0 records are selected. If no records match your criteria, and your query returns 0, is that not success?

Usually you would have some logic such as

Input some criteria
If criteria = X Then
DoCmd.Open "query1"
elseif criteria = Y then
DoCmd.Open "query2"
elseif criteria = Z then
DoCmd.Open "query3"
Endif

Perhaps you could tell us more.

[Hans]

Hi Orange,thank you very much for replying.
with Query success, I mean the query has records....

well, I tried the following logic but I still see that only first two queries are executing....

Const MESSAGETEXT = "No Customer Number entered."
Dim strCriteria As String
strCriteria = "[SoldTo]" = """ & Me.SoldTo &"""""


If Not IsNull(Me.SoldTo) Then
DoCmd.OpenQuery "qry_List_RGM_Exclusion", acViewNormal, acReadOnly

If DCount("SoldTo", "qry_List_RGM_Exclusion", strCriteria) = 0 Then
DoCmd.OpenQuery "qry_List_NoRGM_Exclusion", acViewNormal, acReadOnly

ElseIf DCount("SoldTo", "qry_List_NoRGM_Exclusion", strCriteria) = 0 Then
DoCmd.OpenQuery "qry_NoList_RGM_Exclusion", acViewNormal, acReadOnly

ElseIf DCount("SoldTo", "qry_NoList_RGM_Exclusion", strCriteria) = 0 Then
DoCmd.OpenTable "tbl_mat", acViewNormal, acReadOnly
End If
Else
MsgBox MESSAGETEXT, vbExclamation, "Invalid Operation"
End If

Could you please point out where I am doing wrong and provide some suggestion if possible.

Thanks

[orange]

I just reformatted your code and added code tags. What happens? What do you want to happen?

Where are you running this code? Since you are using Me. it has to be somewhere on the form or a control event, and you haven't shown the event??

Const MESSAGETEXT = "No Customer Number entered."
    Dim strCriteria As String
    strCriteria = "[SoldTo]" = """ & Me.SoldTo & """""


    If Not IsNull(Me.SoldTo) Then
        DoCmd.OpenQuery "qry_List_RGM_Exclusion", acViewNormal, acReadOnly

        If DCount("SoldTo", "qry_List_RGM_Exclusion", strCriteria) = 0 Then
            DoCmd.OpenQuery "qry_List_NoRGM_Exclusion", acViewNormal, acReadOnly

        ElseIf DCount("*", "qry_List_NoRGM_Exclusion", strCriteria) = 0 Then
            DoCmd.OpenQuery "qry_NoList_RGM_Exclusion", acViewNormal, acReadOnly

        ElseIf DCount("SoldTo", "qry_NoList_RGM_Exclusion", strCriteria) = 0 Then
            DoCmd.OpenTable "tbl_mat", acViewNormal, acReadOnly
        End If
    Else
        MsgBox MESSAGETEXT, vbExclamation, "Invalid Operation"
    End If

[Hans]

Hi Orange,

I am using this code on form where I enter the Customer number (SoldTo) in the inbound box and click on a button which runs this code.
I formatted the code as given by you and it is still not executing other 2 queries. It seems it is only running if then else if and then stopping.

I think I need some more suggestion into this, may be other way to do using macro or another code.

Thanks
Hans

[orange]

I reformatted your code for readability.
Please show all of your code related to this snippet. You must have this in some event that says
Private Sub....... and ends with End Sub

Try using this code which includes some debug statements to print messages in the immediate window.
Also, why are you opening a table?

Const MESSAGETEXT = "No Customer Number entered."
    Dim strCriteria As String
    strCriteria = "[SoldTo]" = """ & Me.SoldTo & """""

Debug.Print "strCriteria is <" & strCriteria & ">"
Debug.Print "Me.SoldTo is  <" & Me.soldTo & ">"
Debug.Print "  DCount1   is: " & DCount("SoldTo", "qry_List_RGM_Exclusion", strCriteria)
Debug.Print "  DCount2   is: " & DCount("*", "qry_List_NoRGM_Exclusion", strCriteria)
Debug.Print "  DCount3   is: " & DCount("SoldTo", "qry_NoList_RGM_Exclusion", strCriteria)

    If Not IsNull(Me.SoldTo) Then
        DoCmd.OpenQuery "qry_List_RGM_Exclusion", acViewNormal, acReadOnly

      
        If DCount("SoldTo", "qry_List_RGM_Exclusion", strCriteria) = 0 Then
            DoCmd.OpenQuery "qry_List_NoRGM_Exclusion", acViewNormal, acReadOnly
       
         ElseIf DCount("*", "qry_List_NoRGM_Exclusion", strCriteria) = 0 Then
            DoCmd.OpenQuery "qry_NoList_RGM_Exclusion", acViewNormal, acReadOnly

         ElseIf DCount("SoldTo", "qry_NoList_RGM_Exclusion", strCriteria) = 0 Then
            DoCmd.OpenTable "tbl_mat", acViewNormal, acReadOnly
        End If
    Else
        MsgBox MESSAGETEXT, vbExclamation, "Invalid Operation"
    End If