By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,492 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

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

Seth Schrock
Expert 2.5K+
P: 2,932
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:
Expand|Select|Wrap|Line Numbers
  1. Dim dbBilling As DAO.Database
  2.     Dim rstInvoices As DAO.Recordset
  3.     Dim lngInvoiceID As Long
  4.  
  5.     Set dbBilling = CurrentDb
  6.     Set rstInvoices = dbBilling.OpenRecordset("tblInvoices")
  7.  
  8.     rstInvoices.AddNew
  9.     rstInvoices("CustomerID").Value = Forms!frmCustomer!CustomerID
  10.     rstInvoices("BeginDate").Value = Forms!frmCustomer!txtStartDate
  11.     lngInvoiceID = rstInvoices!InvoiceID
  12.     rstInvoices.Update
  13.  
  14.     DoCmd.SetWarnings False
  15.     CurrentDb.Execute "UPDATE tblFileTypes " & _
  16.     "INNER JOIN tblACHFiles ON tblFileTypes.FileTypeID = tblACHFiles.CompanyFileID " & _
  17.     "SET tblACHFiles.InvoiceID = lngInvoiceID " & _
  18.     "WHERE (((tblACHFiles.ACHCompanyID)=[Forms]![frmCustomer]![CustomerID]) " & _
  19.     "AND ((tblACHFiles.EffectiveDate)>=[Forms]![frmCustomer]![txtStartDate] " & _
  20.     "AND (tblACHFiles.EffectiveDate)<=[Forms]![frmCustomer]![txtEndDate]) " & _
  21.     "AND ((tblFileTypes.BillingNumber)=[Forms]![frmCustomer]![cmbBillingNumber]))"
  22.     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?
Jan 11 '12 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1.     Dim frmMe As Form
  2.  
  3.     With Me
  4.         ...
  5.         Set frmMe = Forms("frmCustomer")
  6.         strSQL = "UPDATE [tblFileTypes]" & _
  7.                  "       INNER JOIN" & _
  8.                  "       [tblACHFiles]" & _
  9.                  "    ON tblFileTypes.FileTypeID = tblACHFiles.CompanyFileID " & _
  10.                  "SET    tblACHFiles.InvoiceID = %I " & _
  11.                  "WHERE ((tblACHFiles.ACHCompanyID = %C)" & _
  12.                  "  AND  (tblACHFiles.EffectiveDate Between #%S# And #%E#)" & _
  13.                  "  AND  (tblFileTypes.BillingNumber = %B))"
  14.         strSQL = Replace(strSQL, "%I", rstInvoices!InvoiceID)
  15.         strSQL = Replace(strSQL, "%C", frmMe.CustomerID)
  16.         strSQL = Replace(strSQL, "%S", Format(frmMe.txtStartDate, "m\/d\/yyyy"))
  17.         strSQL = Replace(strSQL, "%E", Format(frmMe.txtEndDate, "m\/d\/yyyy"))
  18.         strSQL = Replace(strSQL, "%B", frmMe.cmbBillingNumber)
  19.         Call dbBilling.Execute(strSQL)
  20.     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 :
Expand|Select|Wrap|Line Numbers
  1. lngInvoiceID      32,719
  2. txtCustomerID     662,816
  3. txtStartDate      13 Jan 2011
  4. txtEndDate        7 Feb 2011
  5. cmbBillingNumber  329
The SQL string would be :
Expand|Select|Wrap|Line Numbers
  1. UPDATE [tblFileTypes]
  2.        INNER JOIN
  3.        [tblACHFiles]
  4.     ON tblFileTypes.FileTypeID = tblACHFiles.CompanyFileID
  5. SET    tblACHFiles.InvoiceID = 32719
  6. WHERE ((tblACHFiles.ACHCompanyID = 662816)
  7.   AND  (tblACHFiles.EffectiveDate Between #1/13/2011# And #2/7/2011#)
  8.   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.

Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,308
Seth, you need to check over When Posting (VBA or SQL) Code (again?).
Jan 11 '12 #2

Seth Schrock
Expert 2.5K+
P: 2,932
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.
Jan 11 '12 #3

NeoPa
Expert Mod 15k+
P: 31,308
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.
Jan 12 '12 #4

Seth Schrock
Expert 2.5K+
P: 2,932
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.).
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblFileTypes 
  2. INNER JOIN tblACHFiles ON tblFileTypes.FileTypeID = tblACHFiles.CompanyFileID 
  3. SET tblACHFiles.InvoiceID = DLookUp("InvoiceID","qryFindInvoiceID")
  4. WHERE (((tblACHFiles.ACHCompanyID)=[Forms]![frmCustomer]![CustomerID]) 
  5. AND ((tblACHFiles.EffectiveDate)>=[Forms]![frmCustomer]![txtStartDate] 
  6. AND (tblACHFiles.EffectiveDate)<=[Forms]![frmCustomer]![txtEndDate]) 
  7. AND ((tblFileTypes.BillingNumber)=[Forms]![frmCustomer]![cmbBillingNumber]))
  8.  
This query worked properly. I just had my logic wrong which produced the wrong results.
Jan 12 '12 #5

NeoPa
Expert Mod 15k+
P: 31,308
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?
Jan 12 '12 #6

Seth Schrock
Expert 2.5K+
P: 2,932
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
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblFileTypes INNER JOIN tblACHFiles ON tblFileTypes.FileTypeID = tblACHFiles.CompanyFileID 
  2. SET tblACHFiles.InvoiceID = lngInvoiceID 'variable to return a number such as 101
  3. WHERE (((tblACHFiles.ACHCompanyID)=[Forms]![frmCustomer]![CustomerID]) 
  4. AND ((tblACHFiles.EffectiveDate)>=[Forms]![frmCustomer]![txtStartDate] 
  5. AND (tblACHFiles.EffectiveDate)<=[Forms]![frmCustomer]![txtEndDate]) 
  6. 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
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. "SET tblACHFiles.InvoiceID = lngInoviceID " & _
but I fixed it to be
Expand|Select|Wrap|Line Numbers
  1. "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.
Expand|Select|Wrap|Line Numbers
  1.     Dim dbBilling As DAO.Database
  2.     Dim rstInvoices As DAO.Recordset
  3.     Dim lngInvoiceID As Long
  4.     Dim strSQL As String
  5.  
  6.     Set dbBilling = CurrentDb
  7.     Set rstInvoices = dbBilling.OpenRecordset("tblInvoices")
  8.  
  9.     rstInvoices.AddNew
  10.     rstInvoices("CustomerID").Value = Forms!frmCustomer!CustomerID
  11.     rstInvoices("BeginDate").Value = Forms!frmCustomer!txtStartDate
  12.     rstInvoices("EndDate").Value = Forms!frmCustomer!txtEndDate
  13.     rstInvoices("InvoiceTotal").Value = DSum("TotalCharge", "qrySpecialCountWIIF")
  14.     lngInvoiceID = rstInvoices!InvoiceID
  15.     rstInvoices.Update
  16.  
  17.  
  18.     strSQL = "UPDATE tblFileTypes " & _
  19.     "INNER JOIN tblACHFiles ON tblFileTypes.FileTypeID = tblACHFiles.CompanyFileID " & _
  20.     "SET tblACHFiles.InvoiceID = " & lngInvoiceID & _
  21.     " WHERE (((tblACHFiles.ACHCompanyID)=[Forms]![frmCustomer]![CustomerID]) " & _
  22.     "AND ((tblACHFiles.EffectiveDate)>=[Forms]![frmCustomer]![txtStartDate] " & _
  23.     "AND (tblACHFiles.EffectiveDate)<=[Forms]![frmCustomer]![txtEndDate]) " & _
  24.     "AND ((tblFileTypes.BillingNumber)=[Forms]![frmCustomer]![cmbBillingNumber]))"
  25.  
  26.  
  27.     DoCmd.SetWarnings False
  28.     CurrentDb.Execute strSQL
  29.     DoCmd.SetWarnings True
  30.  
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.
Jan 12 '12 #7

NeoPa
Expert Mod 15k+
P: 31,308
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 :
Expand|Select|Wrap|Line Numbers
  1.     Dim frmMe As Form
  2.  
  3.     With Me
  4.         ...
  5.         Set frmMe = Forms("frmCustomer")
  6.         strSQL = "UPDATE [tblFileTypes]" & _
  7.                  "       INNER JOIN" & _
  8.                  "       [tblACHFiles]" & _
  9.                  "    ON tblFileTypes.FileTypeID = tblACHFiles.CompanyFileID " & _
  10.                  "SET    tblACHFiles.InvoiceID = %I " & _
  11.                  "WHERE ((tblACHFiles.ACHCompanyID = %C)" & _
  12.                  "  AND  (tblACHFiles.EffectiveDate Between #%S# And #%E#)" & _
  13.                  "  AND  (tblFileTypes.BillingNumber = %B))"
  14.         strSQL = Replace(strSQL, "%I", rstInvoices!InvoiceID)
  15.         strSQL = Replace(strSQL, "%C", frmMe.CustomerID)
  16.         strSQL = Replace(strSQL, "%S", Format(frmMe.txtStartDate, "m\/d\/yyyy"))
  17.         strSQL = Replace(strSQL, "%E", Format(frmMe.txtEndDate, "m\/d\/yyyy"))
  18.         strSQL = Replace(strSQL, "%B", frmMe.cmbBillingNumber)
  19.         Call dbBilling.Execute(strSQL)
  20.     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 :
Expand|Select|Wrap|Line Numbers
  1. lngInvoiceID      32,719
  2. txtCustomerID     662,816
  3. txtStartDate      13 Jan 2011
  4. txtEndDate        7 Feb 2011
  5. cmbBillingNumber  329
The SQL string would be :
Expand|Select|Wrap|Line Numbers
  1. UPDATE [tblFileTypes]
  2.        INNER JOIN
  3.        [tblACHFiles]
  4.     ON tblFileTypes.FileTypeID = tblACHFiles.CompanyFileID
  5. SET    tblACHFiles.InvoiceID = 32719
  6. WHERE ((tblACHFiles.ACHCompanyID = 662816)
  7.   AND  (tblACHFiles.EffectiveDate Between #1/13/2011# And #2/7/2011#)
  8.   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.
Jan 12 '12 #8

Seth Schrock
Expert 2.5K+
P: 2,932
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
Expand|Select|Wrap|Line Numbers
  1. CustomerID    15
  2. txtStartDate        7/1/2011
  3. txtEndDate          12/31/2011
  4. 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.
Jan 12 '12 #9

NeoPa
Expert Mod 15k+
P: 31,308
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 ;-)
Jan 12 '12 #10

Post your reply

Sign in to post your reply or Sign up for a free account.