How do I pass a variable from VBA to a query?

[Seth Schrock]

I'm new to defining a query in VBA (this is the first time) and I'm getting an error message: Too few parameters. Expected 5. Here is the code that I'm using:

Dim dbBilling As DAO.Database
    Dim rstInvoices As DAO.Recordset
    Dim lngInvoiceID As Long
 
    Set dbBilling = CurrentDb
    Set rstInvoices = dbBilling.OpenRecordset("tblInvoices")
 
    rstInvoices.AddNew
    rstInvoices("CustomerID").Value = Forms!frmCustomer!CustomerID
    rstInvoices("BeginDate").Value = Forms!frmCustomer!txtStartDate
    lngInvoiceID = rstInvoices!InvoiceID
    rstInvoices.Update
 
    DoCmd.SetWarnings False
    CurrentDb.Execute "UPDATE tblFileTypes " & _
    "INNER JOIN tblACHFiles ON tblFileTypes.FileTypeID = tblACHFiles.CompanyFileID " & _
    "SET tblACHFiles.InvoiceID = lngInvoiceID " & _
    "WHERE (((tblACHFiles.ACHCompanyID)=[Forms]![frmCustomer]![CustomerID]) " & _
    "AND ((tblACHFiles.EffectiveDate)>=[Forms]![frmCustomer]![txtStartDate] " & _
    "AND (tblACHFiles.EffectiveDate)<=[Forms]![frmCustomer]![txtEndDate]) " & _
    "AND ((tblFileTypes.BillingNumber)=[Forms]![frmCustomer]![cmbBillingNumber]))"
    DoCmd.SetWarnings True

In researching the error message I found that this is a common error message when a field is misspelled. Since I copied almost all of the code from a working query, I'm thinking that the problem is in line 17 where I'm trying to pass the variable lngInvoiceID to the query. I'm thinking that since the query doesn't work by itself, it is causing the error.

Is it possible to set the value of tblACHFiles.InvoiceID to the variable lngInvoiceID?

[NeoPa]

Seth, you need to check over When Posting (VBA or SQL) Code (again?).

[Seth Schrock]

Which part did I miss? I have option explicit set, it compiled just fine, I copied and pasted the code into the post and my code is within code tags.

[NeoPa]

Lol :-D

Posting the actual SQL when debugging SQL code :-)

In reality, it also makes sense to include the values of any objects referenced such as controls on a form, as these can be different every time it's run. It's data we would need to allow us to interpret what we think might be going on and thereby identify what the problem might be.

[Seth Schrock]

Okay, I'm feeling really dense. I thought that the SQL was in lines 15-21. Other than that, I don't think that there is any other SQL happening in this procedure.

Referenced objects: ACHCompanyID is a number field and is the Foreign Key for the relationship between tblACHFiles and tblCustomer, EffectiveDate is a date value as is txtStartDate and txtEndDate, BillingNumber/cmbBillingNumber is just a number (usually 1), InvoiceID is an autonumber and the PK of tblInvoices. Previously, I had a query (code following) that used a DLookup() function to pull the InvoiceID in the SET portion of the query, but I ran into a problem when I had two records that matched all the criteria (that is why I've decided to do it this way.).

UPDATE tblFileTypes 
INNER JOIN tblACHFiles ON tblFileTypes.FileTypeID = tblACHFiles.CompanyFileID 
SET tblACHFiles.InvoiceID = DLookUp("InvoiceID","qryFindInvoiceID")
WHERE (((tblACHFiles.ACHCompanyID)=[Forms]![frmCustomer]![CustomerID]) 
AND ((tblACHFiles.EffectiveDate)>=[Forms]![frmCustomer]![txtStartDate] 
AND (tblACHFiles.EffectiveDate)<=[Forms]![frmCustomer]![txtEndDate]) 
AND ((tblFileTypes.BillingNumber)=[Forms]![frmCustomer]![cmbBillingNumber]))
 

This query worked properly. I just had my logic wrong which produced the wrong results.

[NeoPa]

I'm not sure I follow exactly what you do and don't understand, so I'll make a few statements and if they're things you already understand then ignore them.

1. The SQL you just posted is the SQL required (sensibly formatted thank you). It is important though (critically important), that what you post is the exact result as produced by printing off the string created in your VBA. I notice this isn't the case here, so typically (not in this case as it's you and also the SQL itself is relatively straightforward), I would ignore it or treat it with much suspicion. Obviously, I don't want to waste my time looking carefully at a SQL string which may be the result of typos and exactly the misunderstandings that I would be there to try to discover. If the poster only posts what he expects to see then I have no way of knowing what's actually there. This is especially important if a misspelling may have occurred. If necessary assign the SQL to a string variable first and print it to the Immediate Pane before copying it and pasting it into a post. Simples (Russian accent of meerkat).
2. The resultant SQL is often complicated by literals being prepared from values taken from other objects. Hence a clear picture is only available when the final resultant SQL is seen. Experienced coders can often see these anyway, but it's also a very good idea for people to do this as they learn to see things for themselves much more easily when the actual SQL is visible.
3. The values of relevant objects I referred to earlier was about the control references (EG. [Forms]![frmCustomer]![cmbBillingNumber]) rather than field references in the SQL as such. These are often used in filters and understanding what happens is often heavily dependent on knowing these.
4. Many SQL strings (not in this case) use objects such as these, and even others available to the VBA code, to include literal references in the SQL. These are literals in the SQL but objects in the VBA - hence we have little clue what they're doing unless we see the resolved SQL.

Seth:
This query worked properly. I just had my logic wrong which produced the wrong results.

So, does this mean all is resolved? Or is there still a problem?

[Seth Schrock]

My problem of passing a variable into the SQL isn't fixed. I also think that I understand your questions now. The code in post #5 was the query that I was running before that ran, but did not produce the results that I wanted. Therefore I'm trying to create the query in VBA so that I don't have to do the DLookup() in the SET value that is in my previous query. So, the query that I'm trying to run now is

UPDATE tblFileTypes INNER JOIN tblACHFiles ON tblFileTypes.FileTypeID = tblACHFiles.CompanyFileID 
SET tblACHFiles.InvoiceID = lngInvoiceID 'variable to return a number such as 101
WHERE (((tblACHFiles.ACHCompanyID)=[Forms]![frmCustomer]![CustomerID]) 
AND ((tblACHFiles.EffectiveDate)>=[Forms]![frmCustomer]![txtStartDate] 
AND (tblACHFiles.EffectiveDate)<=[Forms]![frmCustomer]![txtEndDate]) 
AND ((tblFileTypes.BillingNumber)=[Forms]![frmCustomer]![cmbBillingNumber]))

The difference is in the SET line.

I did some troublshooting and got some interesting results. I assigned the query to a variable (strSQL) and then executed the variable. So I used the immediate window to find out what strSQL was doing. I then copied and pasted the result into a regular query and it ran fine. However, when I try to run the query in VBA via the

CurrentDb.Execute strSQL

command, the error message that I get now is Too few parameters. Expected 4. where before it expected 5. I did find one error that I had when trying to include the variable previously. Before I had the SET line be

"SET tblACHFiles.InvoiceID = lngInoviceID " & _

but I fixed it to be

"SET tblACHFiles.InvoiceID = " & lngInvoiceID & _

I think that this change is why the error message changed from expected 5 to expected 4.

So, I know that the query runs okay in a regular query. I just need to know why it isn't working in VBA.

Just for clarification, here is the code that I'm running now.

    Dim dbBilling As DAO.Database
    Dim rstInvoices As DAO.Recordset
    Dim lngInvoiceID As Long
    Dim strSQL As String
 
    Set dbBilling = CurrentDb
    Set rstInvoices = dbBilling.OpenRecordset("tblInvoices")
 
    rstInvoices.AddNew
    rstInvoices("CustomerID").Value = Forms!frmCustomer!CustomerID
    rstInvoices("BeginDate").Value = Forms!frmCustomer!txtStartDate
    rstInvoices("EndDate").Value = Forms!frmCustomer!txtEndDate
    rstInvoices("InvoiceTotal").Value = DSum("TotalCharge", "qrySpecialCountWIIF")
    lngInvoiceID = rstInvoices!InvoiceID
    rstInvoices.Update
 
 
    strSQL = "UPDATE tblFileTypes " & _
    "INNER JOIN tblACHFiles ON tblFileTypes.FileTypeID = tblACHFiles.CompanyFileID " & _
    "SET tblACHFiles.InvoiceID = " & lngInvoiceID & _
    " WHERE (((tblACHFiles.ACHCompanyID)=[Forms]![frmCustomer]![CustomerID]) " & _
    "AND ((tblACHFiles.EffectiveDate)>=[Forms]![frmCustomer]![txtStartDate] " & _
    "AND (tblACHFiles.EffectiveDate)<=[Forms]![frmCustomer]![txtEndDate]) " & _
    "AND ((tblFileTypes.BillingNumber)=[Forms]![frmCustomer]![cmbBillingNumber]))"
 
 
    DoCmd.SetWarnings False
    CurrentDb.Execute strSQL
    DoCmd.SetWarnings True
 

I hope that I have provided enough information this time :) I'm a little confused trying to have both VBA and SQL in the same space, so I think that is why I'm confused with your questions.

[NeoPa]

There appears to be quite a lot of confusion still. Some of the things I'm sending out are just not landing for some reason. This is not something to worry about. I find when people get confused and know it, they just end up getting more confused. The only way to move on is somehow to relax so understanding doesn't matter. I'll try to help with that now, and maybe later you can revisit the thread and when you read it then everything I've said earlier will start to make much more sense.

For now though, I'll work with what I have and we'll see if we can't get beyond this (Which in turn will allow 'later' to occur earlier, when you can look back over the thread and have a better chance of seeing it from a less harrassed perspective). First things first. Good spot on catching the lngInvoiceID gotcha. Your fix also was perfect. Now, it seems clear that the four errors are the references to the controls on your [frmCustomer] form. The first, and most obvious, thing to check is that the form is actually open with valid values in the four controls when you test your query. I'm guessing (for want of clear info on the matter) that when your tests worked the form was open, but whenever you tested it and it failed, the form wasn't - hence the references to those controls failed (in which case Jet SQL would treat them as parameters without values). Test this hypothesis first.

Failing that we can convert the code which creates the SQL to use literal values within the SQL string (Just as you did for lngInvoiceID in fact). The code would then look something like :

    Dim frmMe As Form
 
    With Me
        ...
        Set frmMe = Forms("frmCustomer")
        strSQL = "UPDATE [tblFileTypes]" & _
                 "       INNER JOIN" & _
                 "       [tblACHFiles]" & _
                 "    ON tblFileTypes.FileTypeID = tblACHFiles.CompanyFileID " & _
                 "SET    tblACHFiles.InvoiceID = %I " & _
                 "WHERE ((tblACHFiles.ACHCompanyID = %C)" & _
                 "  AND  (tblACHFiles.EffectiveDate Between #%S# And #%E#)" & _
                 "  AND  (tblFileTypes.BillingNumber = %B))"
        strSQL = Replace(strSQL, "%I", rstInvoices!InvoiceID)
        strSQL = Replace(strSQL, "%C", frmMe.CustomerID)
        strSQL = Replace(strSQL, "%S", Format(frmMe.txtStartDate, "m\/d\/yyyy"))
        strSQL = Replace(strSQL, "%E", Format(frmMe.txtEndDate, "m\/d\/yyyy"))
        strSQL = Replace(strSQL, "%B", frmMe.cmbBillingNumber)
        Call dbBilling.Execute(strSQL)
    End With

A number of assumptions had to be made due to lack of knowledge of exactly what you're doing where. I expect the code is running from within the module of frmCustomer. In which case frmMe is entirely unnecessary and the keyword Me can be used in its place (No preparation required as it already exists and is ready for use). I've also assumed [BillingNumber] is a numeric field. If it is textual then the relevant line (#13) needs changing to allow for the cmbBillingNumber value to be surrounded by quotes ('). I've also assumed that all the relevant controls can be guaranteed to contain valid data when this code is run.

If it's imperative that the previous approach is required - IE. references to the form controls - then I would still need the values posted of the four controls referenced as well as the value of lngInvoiceID at the point immediately prior to attempted execution of the query. In case it helps I will illustrate what you need to post by doing the same for the SQL I just created on the following assumptions (I won't need to include these values separately in the post as the posted SQL will tell me). I will also add white-space characters wherever it enhances the display, but only at points in the string where there is already white-space characters. This is OK as part of the standards of SQL is that white-space characters separate items but the length of the white-space is immaterial.

So, assuming these values :

lngInvoiceID      32,719
txtCustomerID     662,816
txtStartDate      13 Jan 2011
txtEndDate        7 Feb 2011
cmbBillingNumber  329

The SQL string would be :

UPDATE [tblFileTypes]
       INNER JOIN
       [tblACHFiles]
    ON tblFileTypes.FileTypeID = tblACHFiles.CompanyFileID
SET    tblACHFiles.InvoiceID = 32719
WHERE ((tblACHFiles.ACHCompanyID = 662816)
  AND  (tblACHFiles.EffectiveDate Between #1/13/2011# And #2/7/2011#)
  AND  (tblFileTypes.BillingNumber = 329))

This appears to be good SQL, but this could prove very illuminating if one of the values had been Null for instance. The SQL string would immediately show a discrepancy which would be invisible when approached from the VBA angle.

PS. My code doesn't require the existence of the variable lngInvoiceID.

[Seth Schrock]

Okay. The first set of code you provided worked (except you forgot to Replace "%C" which was an easy fix). The form was open (in fact the button that triggers this code is in the form so the code can't be triggered unless the form is open) so I did delete the frmMe parts and did just Me.{blank}.

Your assumed values were right on the money. Since you said that it was probably the references to the form in my code that was the problem, I tested the values in the Immediate window by copy/pasting from the code into the Immediate window and the correct values were coming back, but the query still wouldn't run. The values that I had coming back were

CustomerID    15
txtStartDate        7/1/2011
txtEndDate          12/31/2011
cmbBillingNumber    1

Just for fun, I hard coded these values into the query and it worked. I'm still not sure why the references didn't work in the code since they worked in the Immediate window. However, since I know your code does work, I will stick with that.

Thanks so very much for your patience with me and I appologize for any stress that I have put you through.

[NeoPa]

No apologies necessary from you Seth. You're in credit by plenty in my book. Keep on keeping on.

I'm afraid I don't understand either why your form control references were not accepted by Jet. I noticed that [CustomerID] was the only one without a prefix indicating the control type, but even if that were wrong it would only cause one invalid reference rather than four. No. I'm still in the dark on that one.

PS. I updated the earlier post to fix the omission you spotted. It was certainly there, but I felt the post would be more useful for reference if it made proper sense ;-)