odd error

[CHAOSinACT]

anyone see a problem with this:

Dim rstClaimsAndVaries As DAO.Recordset
Set rstClaimsAndVaries = CurrentDb.OpenRecordset("qryCurrentVariation")

???? i use it all over and just got an error:
"Error Type: Too few parameters. Expected 1"

its part of the code below; when i go to debug it highlights the "close" statement regarding this dao set in the exit here area....

Public Function SubmitVariance() As String
SubmitVariance = "failed"
AttentionTo = ""
EmailTo = ""

On Error GoTo handleError
Dim VariPath As String

Dim rstCivilProjectsTable As DAO.Recordset
Dim rstCivilProjectsTableFiltered As DAO.Recordset
Set rstCivilProjectsTable = CurrentDb.OpenRecordset("tblDGroupCivilMinorJobs")
rstCivilProjectsTable.Filter = "[Civil Job Number] = " & Forms!frmProjectVariancesNotInvoicedDetail!ProjectNumber
Set rstCivilProjectsTableFiltered = rstCivilProjectsTable.OpenRecordset
'get dir if varibin undefined
If IsNull(rstCivilProjectsTableFiltered!VariationsBin) Or rstCivilProjectsTableFiltered!VariationsBin = "" Then
VariPath = BrowseDirectory("Define Variations Directory for Job:")
rstCivilProjectsTableFiltered.Edit
rstCivilProjectsTableFiltered!VariationsBin = VariPath
rstCivilProjectsTableFiltered.Update
Else
VariPath = rstCivilProjectsTableFiltered!VariationsBin
End If
If VariPath = "" Then
MsgBox "Failed to Establish Variation Directory on server", vbOKOnly
GoTo handleError
End If
DoCmd.OpenForm "frmCurrentProjectContacts", acNormal, , , , acDialog
If AttentionTo = "" And EmailTo = "" Then
MsgBox "Failed to establish a client contact; submission failed", vbOKOnly
GoTo handleError
End If
'determine lodge attempts
Dim rstClaimsAndVaries As DAO.Recordset
Set rstClaimsAndVaries = CurrentDb.OpenRecordset("qryCurrentVariation")'<<<<this is where it drops out when stepped through then goes to the close statement below

If IsNull(rstClaimsAndVaries!LodgeAttempts) Then
rstClaimsAndVaries.Edit
rstClaimsAndVaries!LodgeAttempts = 1
rstClaimsAndVaries.Update
LodgeAttempts = 1
Else
rstClaimsAndVaries.Edit
rstClaimsAndVaries!LodgeAttempts = rstClaimsAndVaries!LodgeAttempts + 1
rstClaimsAndVaries.Update
LodgeAttempts = rstClaimsAndVaries!LodgeAttempts
End If
'opent the report
DoCmd.OpenReport "rptVaritionLodgement", acViewPreview
'export the pdf
DoCmd.OutputTo acOutputReport, "rptVaritionLodgement", acFormatPDF, VariPath & "\Variation No " & rstClaimsAndVaries!ClaimOrVariNumber & " - " & LodgeAttempts & ".pdf"

'send the mail
DoCmd.SendObject acSendReport, "rptVaritionLodgement", _
acFormatPDF, EmailTo, , , "D-Group Variation Approval for: " & AttentionTo, _
"This Variation to Contract has been submitted for your approval.", _
True
'update hyperlinke save
Dim rstSubmissionLinks As DAO.Recordset
Set rstSubmissionLinks = CurrentDb.OpenRecordset("tblClaimAndVariLodgeLinks")
rstSubmissionLinks.Edit
rstSubmissionLinks!JobNumber = rstCivilProjectsTableFiltered![Civil Job Number]
rstSubmissionLinks!ClaimOrVariNumber = rstClaimsAndVaries!LineID
rstSubmissionLinks!Documentname = rstClaimsAndVaries!Description
rstSubmissionLinks!DocumentType = 2
rstSubmissionLinks!DocPath = VariPath & "\Variation No " & rstClaimsAndVaries!ClaimOrVariNumber & " - " & LodgeAttempts & ".pdf"
rstSubmissionLinks!LinkToDocument = VariPath & "\Variation No " & rstClaimsAndVaries!ClaimOrVariNumber & " - " & LodgeAttempts & ".pdf" & "#" & VariPath & "\Variation No " & rstClaimsAndVaries!ClaimOrVariNumber & " - " & LodgeAttempts & ".pdf" & "#"
rstSubmissionLinks!SubmittedTo = AttentionTo
rstSubmissionLinks!SubmittedDate = Date
rstSubmissionLinks!SubmittedEmail = EmailTo
rstSubmissionLinks.Update

SubmitVariance = "sucess"


ExitHere:
rstCivilProjectsTable.Close
rstCivilProjectsTableFiltered.Close
rstClaimsAndVaries.Close '<<<<< this is where it goes in debugger

rstSubmissionLinks.Close

Set rstCivilProjectsTable = Nothing
Set rstCivilProjectsTableFiltered = Nothing
Set rstClaimsAndVaries = Nothing

Set rstSubmissionLinks = Nothing


Exit Function

handleError:
MsgBox "Error Type: " & Err.Description
SubmitVariance = "failed"
GoTo ExitHere

End Function

I'm sure this is simple and i'm just not seeing it; but it all seems normal when i look... any ideas?

thanks in advance

 

[pbaldy]

I suspect this is the cause:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209203

if so, you can also get around it by using the Eval() function around the form reference in the query.

 

[CHAOSinACT]

to make life easier i've reduced the error section down to this

Public Sub testdao()
Dim rstClaimsAndVaries As DAO.Recordset
Set rstClaimsAndVaries = CurrentDb.OpenRecordset("qryCurrentVariation")

If IsNull(rstClaimsAndVaries!LodgeAttempts) Then
rstClaimsAndVaries.Edit
rstClaimsAndVaries!LodgeAttempts = 1
rstClaimsAndVaries.Update
LodgeAttempts = 1
Else
rstClaimsAndVaries.Edit
rstClaimsAndVaries!LodgeAttempts = rstClaimsAndVaries!LodgeAttempts + 1
rstClaimsAndVaries.Update
LodgeAttempts = rstClaimsAndVaries!LodgeAttempts
End If
End Sub

Just test the query live it's fine btw

 

[CHAOSinACT]

seems to be implying i need to use a querydef statement to open it? i can't believe thats true i've never needed on just to open a recordset before....

if what i'm having trouble with is definitely:

Set rstClaimsAndVaries = CurrentDb.OpenRecordset("qryCurrentVariation")
(and this baffles me; even with the link you posted) where would i stick an eval function in that??

I'm still looking at where you sent me btw; hopefully it will make more sense.
love more of your input if possible though, thanks.

 

[CHAOSinACT]

ok it's def the query as other queries work in there; the query does load a parameter from the form on load but thats not uncommon; i've never had to worry about that before. i don't pass a parameter to the variable, it's self filtering. i've tested at the load point and it work fine, doesn't ask me for any parameters! so i'm stumped...

 

[pbaldy]

Not sure what you want me to say. The form parameter in the query can't be resolved by the recordset, as noted in the link. You can either use the method from the link or the Eval() function in the query:

Eval('Forms!FormName.ControlName')

 

[CHAOSinACT]

Not sure what you want me to say. The form parameter in the query can't be resolved by the recordset, as noted in the link. You can either use the method from the link or the Eval() function in the query:

Eval('Forms!FormName.ControlName')

LMAO. for the love of god. stop selling yourself short. u just solved it here; thanks man. i used

Eval(Forms!FormName.ControlName)
instead of
Eval('Forms!FormName.ControlName')

so thanks for that extra 2 cents input it help heaps more than u thought it would!!!

Cheers man.