error 80040e10 No value given for one or more required parameters in a SQL string

[Ricardo Caicedo]

Good morning

I have the error 80040e10-No value given for one or more required parameter inside a SQL string and I know that the problem is some/one of my variables passed, however I cannot found the mistake. See down here my code. I got experience in VBA but no much in ACCESS so the query have some problems.
I got the following parameters used inside of the string (I can see before open the recordset that all of them have valid values);

• TotalGj is a number result of a dsum
• Temp1 is a text
• Mketer is a text\

my code is down here

Sub Enterdata()

Dim Mketer As String, MonthBR As String
Dim HECAmount As Currency
Dim RsCustomers As ADODB.Recordset, RsFfeesMonth As ADODB.Recordset
Dim Cnx As ADODB.Connection
Dim strSQL As String, Msg As String, Temp As String, FF As String, PTX As String
Dim TotalGJ As Double, K As Integer
Dim Mes As String, temp1 As String
Dim Pos As Boolean

'Enter the HEC $ amoun billed , Marketer Group and month
DoCmd.OpenForm "frmEnterdata"
'Pass variables from the entryForm
Mketer = Forms("frmEnterdata")!ListMketer
HECAmount = Forms("frmEnterdata")!HEC
MonthBR = Forms("frmEnterdata")!MonthBR
MonthNum = Month(CDate(MonthBR & " 1," & Year(Date)))
Mes = Str(MonthNum)
temp1 = "Active"
'Setting the connections an tables
TotalGJ = DSum("[LastGJ]", "Customers", "[LastMarketer] ='" & Mketer & "'")

Set Cnx = CurrentProject.Connection
Set RsCustomers = New ADODB.Recordset
Set RsFfeesMonth = New ADODB.Recordset
'Setting the SQL STRING for Open common Records between tables Customers and Ffees - contract FFEE
strSQL = "SELECT [Customers].[Premise],[Customers].[Account],[Customers].[FranchiseContract],[Customers].[Customer]," & _
"[Customers].[LastGJ],[Customers].[LastGJMonth],[Customers].[LastMarketer],(([Customers].[lastGJ])/[TotalGJ]) AS [PHECAlloc]," & _
"[Ffees].[FranchiseContract],[Ffees].[ValidFrom1],[Ffees].[ValidFrom2], [Ffees].[ValidFrom3],[Ffees].[ValidFrom4],[Ffees].[ValidFrom5]," & _
"[Ffees].[FranFee1],[Ffees].[FranFee2],[Ffees].[FranFee3],[Ffees].[FranFee4],[Ffees].[FranFee5]," & _
"[Ffees].[PropTax1],[Ffees].[PropTax2], [Ffees].[PropTax3], [Ffees].[PropTax4],[Ffees].[PropTax5]" & _
" FROM [Customers] INNER JOIN [Ffees] ON [Customers].[FranchiseContract] = [Ffees].[FranchiseContract]" & _
" WHERE [Customers].[Status] = [" & temp1 & "] AND [customers].[LastMarketer]= [" & Mketer & "];"

RsCustomers.Open strSQL, Cnx, adOpenKeyset, adLockOptimistic

'Setting the sql STRING for Open Records for the Year/Month where FFees applied and keep the data
strSQL = "SELECT * FROM 2015Ffees ;"

RsFfeesMonth.Open strSQL, Cnx, adOpenKeyset, adLockOptimistic, adCmdTable

Do While Not RsCustomers.EOF
If (RsCustomers![LastGJMonth] <> Mes) Then 'Month pick is different of month in database
MsgBox "Please choose same month than Burns Report or Burn Reports Database had not been updated."
RsCustomers.Close
RsFfeesMonth.Close
Set RsCustomers = Nothing
Set RsFfees = Nothing
Exit Sub
Else 'Add the record as new into Year/month Ffees database
'Find the Partition time with the most recently data
K = 1


Temp = "ValidFrom" & K
Do While (IsNull([Ffees]![Temp].Value) = "True")
K = K + 1
Temp = "ValidFrom" & K
Loop
FF = "FranFee" & K
PTX = "PropTax" & K
With RsFfeesMonth
.AddNew
![FranchiseContract] = RsCustomers![ FranchiseContract]
![MonthBurn] = RsCustomers![ LastGJMonth]
![MarketerGActual] = RsCustomers![ LastMarketer]
![Customer] = RsCustomers![Customer]
![Account] = RsCustomers![ Account]
![Premise] = RsCustomers![ Premise]
![GJBurn] = RsCustomers![ LastGJ]
![PGJBurn] = RsCustomers![PHECAlloc]
![HECAlloc] = RsCustomers![PHECAlloc] * HECAmount
![FFAmount] = ![HECAlloc] * RsCustomers![FF]
![PTaxAmount] = ![HECAlloc] * RsCustomers![PTX]
End With
End If
RsCustomers.MoveNext
Loop

RsCustomers.Close
RsFfeesMonth.Close
Set RsCustomers = Nothing
Set RsFfees = Nothing


End Sub

I really appreciate your help

[pbaldy]

This is a commonly used debugging method:

http://www.baldyweb.com/ImmediateWindow.htm

[Ricardo Caicedo]

Thank you paul!

[pbaldy]

Happy to help! It helped you find the problem?

[John_G]

I think the problem is in your WHERE clause.

" WHERE [Customers].[Status] = [" & temp1 & "] AND [customers].[LastMarketer]= [" & Mketer & "];"

temp1 is a string variable and you want Status = "Active". However, in the where clause you have enclosed active in square brackets, i.e. [active]. Because you have put square brackets, MS Access thinks it is a field name which it cannot find. It should be in quotation marks (single quotes work in SQL) like this:

" WHERE [Customers].[Status] = '" & temp1 & "' AND [customers].[LastMarketer]= [" & Mketer & "];"

The same is true for Mketer - if it is text (string), it should be in quotes, not square brackets. If it is numeric, just remove the square brackets.

[Ricardo Caicedo]

Thank you very much John. It works as you post.