How can I get this SQL query to see the VBA selection?

Question

I am trying to have a query written in the Microsoft Access Query designer see the selection my VBA code is making in a table.

Essentially I have the following VBA loop, it goes down the customer column in the table TBL_ForQB and executes the query "QB_Make_Model" if the customer is different a different query runs and the loop stops.

Main Problem:

How can I make the "QB_Make_Model" see the customer the VBA code is on, currently with my code it just inserts info from all the customer.

QB_Make_Model Query:

INSERT INTO InvoiceLine ( InvoiceLineDesc, FQSaveToCache )
SELECT DISTINCT t.Make_Model, 1
FROM TBL_ForQB AS t;

VBA Loop:

Dim dbs As Database
Set dbs = CurrentDb

Dim rst As Recordset
Set rst = dbs.OpenRecordset("TBL_ForQB")

Dim cust As String
Stop

rst.MoveFirst

Dim fcust As String
fcust = rst!Customer
MsgBox rst!Customer

Do Until rst.EOF
    cust = rst!Customer
    If cust = fcust Then
        DoCmd.SetWarnings False
        MsgBox "Same Customer"
        DoCmd.OpenQuery "QB_Make_Model"
    Else
        DoCmd.OpenQuery "QB_Invoice_PK"
        MsgBox "Diffeent Customer, I have Stopped and Sent to QB!"
        'fcust = rst!Customer
        'DoCmd.OpenQuery "QB_Make_Model_PK"
        'DoCmd.OpenQuery "QB_Invoice_PK"

    End If
    rst.MoveNext
Loop

NEW ISSUE

@Parfait answer seemed to work correctly, the issue now is this:

Say there are two of the same Customer records in the table "TBL_ForQB" using the new parameter the query "QB_Make_Model" matches the customer name and VBA executes it. The problem is it sends all the rows in the Make_Model column of the "TBL_ForQB" that match the customer name. And does it as many times as there are Customers with the same name.

For ex:

I have a customer named "Customer1" with 2 records each of which include one Make_Model record (each) in the table "TBL_ForQB". The new VBA code will run and execute the Query "QB_Make_Model", which will match the Customer parameter but insert both records of "QB_Make_Model" for every time there is a customer record.

See the issue?

Answer 1

You can try to modify your query to accept a parameter, and pass that parameter using DoCmd.SetParameter.

Modified query with a parameter:

PARAMETERS pCustomer CHAR(255);
INSERT INTO InvoiceLine ( InvoiceLineDesc, FQSaveToCache )
SELECT DISTINCT t.Make_Model, 1
FROM TBL_ForQB AS t
WHERE Customer = pCustomer;

Modified VBA to pass the parameter:

Dim dbs As Database
Set dbs = CurrentDb

Dim rst As Recordset
Set rst = dbs.OpenRecordset("TBL_ForQB")

Dim cust As String
Stop

rst.MoveFirst

Dim fcust As String
fcust = rst!Customer
MsgBox rst!Customer

Do Until rst.EOF
    cust = rst!Customer
    If cust = fcust Then
        DoCmd.SetWarnings False
        MsgBox "Same Customer"
        DoCmd.SetParameter "pCustomer", cust
        DoCmd.OpenQuery "QB_Make_Model"
    Else
        DoCmd.OpenQuery "QB_Invoice_PK"
        MsgBox "Diffeent Customer, I have Stopped and Sent to QB!"
        'fcust = rst!Customer
        'DoCmd.OpenQuery "QB_Make_Model_PK"
        'DoCmd.OpenQuery "QB_Invoice_PK"

    End If
    rst.MoveNext
Loop

You can read more about using parameters in VBA in this answer. I recommend the DAO approach over an approach relying on DoCmd statements.

Answer 2

Since you cannot pass the customer as parameter to DoCmd.OpenQuery, you will need another approach, like opening a Report or a Form in DataSheet View

Dim filter AS String

filter = "Customer=""" & fcust & """" 'Assuming String type
DoCmd.OpenForm "frmMakeModel", View:=acFormDS, WhereCondition:=filter, DataMode:=acFormReadOnly

Or if Customer is a Long id:

filter = "Customer=" & fcust 'Assuming Long type

The Form's or Report's Record Source must then be an appropriate query containing a Customer column in the SELECT list.

Answer 3

Consider also the QueryDefs approach for passing the customer name parameter to action query, requiring no DoCmd calls including DoCmd.SetWarnings.

SQL (using PARAMETERS with added column for Customer)

PARAMETERS C_Param TEXT(255), P_Param LONG;
INSERT INTO InvoiceLine (InvoiceLineDesc, FQSaveToCache )
SELECT DISTINCT t.Make_Model, 1
FROM TBL_ForQB AS t
WHERE Customer = C_Param
AND ProjectID = P_Param;

Also, you may want to maintain Customer column (ideally its ID) in the InvoiceLine table which can be populated by same parameter.

VBA

Dim dbs As Database
Dim qdef As QueryDef                  ' NEW OBJECT
Dim rst As Recordset
Dim cust As String, fcust As String

Set dbs = CurrentDb
Set qdef = CurrentDb.QueryDefs("QB_Make_Model")
Set rst = dbs.OpenRecordset("TBL_ForQB")

rst.MoveFirst    
fcust = rst!Customer
MsgBox rst!Customer

Do Until rst.EOF
    cust = rst!Customer
    If cust = fcust Then        
        MsgBox "Same Customer"
        qdef!C_Param = cust 
        qdef!C_Param = rst!ProjectID 
        qdef.Execute dbFailOnError
    Else
        dbs.Execute "QB_Invoice_PK"
        MsgBox "Different Customer, I have Stopped and Sent to QB!"
    End If
    rst.MoveNext
Loop

rst.Close 
Set rst = Nothing: Set qdef = Nothing: Set dbs = Nothing