Code Problem

[Jeddell]

Hi everyone,



I don't normally like to plaster a heap of code into these forums, but I have a problem with a chunk of code that I can't figure out.

Background:
I have a database that I use to write technical inspections. Each line in the inspection has a classification using two code types, and a time in hours.

I have three from open for the process, the first form is for the equipment standard data, the second is for the Inspection main page and the third is for the inspection detail. Each form used data for the other two so that the data is linked properly (ie Id numbers).

THE PROBLEM
On the close function of the third form, I have some code that searches all of the data entered into the current inspection and finds the correct classifications to place into the second form (the inspection main page). This saves me having to sift through the inspection data to determine the correct classification code and add up the times. The problem is I'm getting an error:

"Error is 3061 . Too few parameters Expected 1"

I know its somewhere in the SQL, but i don'y have the expertise to find it. I was hoping someone here could help? If the code below does not help, I can send a sample of the database if needed.

Thanks in advance

Private Sub Form_Close()
On Error GoTo Err_Close
Dim DbInsp As Database
Dim rstRecord As DAO.Recordset
Dim codeXX, codeRU, codeBY, codeNFW, BR, Zed, Ymdm, why, ex, om, nom As String
Dim ModTime, ERTFF, ERTNOM, nfw As Double
Dim code1, code2 As String
Dim SQL1 As String
Dim SumID As Integer
Dim n As Integer

SumID = [Forms]![CondSum_frm]!SumID
n = 1

codeXX = "[Code 1] = 'XX'"
codeRU = "[code 1] = 'RU'"
codeBY = "[code 1] = 'BY'"
codeNFW = "[code 1]='NFW'"
BR = "[code 2] = 'BR'"
Zed = "[code 2] = 'Z'"
Ymdm = "[code 2] = 'Ymdm'"
why = "[code 2] = 'Y'"
ex = "[code 2] = 'X'"
om = "[code 2] = 'OM'"
nom = "[code 2] = 'NOM'"

ModTime = DSum("[Estimated Hrs]", "Mods", "CraftID = CraftID")
ERTFF = Format(DSum("[ERT hrs]", "CraftInsp_qry"), "#.0")
ERTNOM = Format(DSum("[ERT hrs]", "CraftInspNOM_qry"), "#.0")
nfw = Format(DSum("[ERT hrs]", "CraftInspNFW_qry"), "#.0")

Set DbInsp = CurrentDb
Set rstRecord = DbInsp.OpenRecordset("SELECT Inspection.[Code 1], Inspection.[Code 2], Inspection.CraftID," _
& "Inspection.SummaryID FROM Inspection WHERE (Inspection.SummaryID = SumID);", dbOpenDynaset)

code = "[Code 1] = 'XX'"
rstRecord.MoveLast
rstRecord.FindFirst codeNFW

If rstRecord.NoMatch Then
 GoTo SearchXX
 Else
 code1 = "NFW"
 GoTo nextclose
 End If

SearchXX:
rstRecord.MoveLast
rstRecord.FindFirst codeXX

 If rstRecord.NoMatch Then
 GoTo SearchRU
 Else
 code1 = "XX"
 GoTo nextclose
 End If


SearchRU:
code = "[Code 1] = 'RU'"
rstRecord.MoveLast
rstRecord.FindFirst codeRU

If rstRecord.NoMatch Then
GoTo SearchBY
 Else
 code1 = "RU"
 GoTo nextclose
 End If


SearchBY:
code = "[Code 1] = 'BY'"
rstRecord.MoveLast
rstRecord.FindFirst codeBY
If rstRecord.NoMatch Then
 code1 = "FF"
 Else
 code1 = "BY"
 End If

nextclose:
rstRecord.MoveLast
rstRecord.FindFirst BR

 If rstRecord.NoMatch Then
 GoTo SearchZ
 Else
 code2 = "BR"
 GoTo class
 End If

SearchZ:
rstRecord.MoveLast
rstRecord.FindFirst Zed

 If rstRecord.NoMatch Then
 GoTo SearchYmdm
 Else
 code2 = "Z"
 GoTo class
 End If
 
SearchYmdm:
rstRecord.MoveLast
rstRecord.FindFirst Ymdm

 If rstRecord.NoMatch Then
 GoTo SearchY
 Else
 code2 = "Ymdm"
 GoTo class
 End If
 
SearchY:
rstRecord.MoveLast
rstRecord.FindFirst why

 If rstRecord.NoMatch Then
 GoTo SearchX
 Else
 code2 = "Y"
 GoTo class
 End If
 
SearchX:
rstRecord.MoveLast
rstRecord.FindFirst ex

 If rstRecord.NoMatch Then
 GoTo SearchOM
 Else
 code2 = "X"
 GoTo class
 End If
 
SearchOM:
rstRecord.MoveLast
rstRecord.FindFirst om

 If rstRecord.NoMatch Then
 code2 = "NOM"
 Else
 code2 = "OM"
 End If
 
 
class:
If nfw = 0 Then
nfw = "--"
Else
End If

Forms!CondSum_frm!txtClassification = code1 & " / " & code2 & " / " & Format(ERTNOM - ERTFF, "#.0") & " / " & Format(ERTNOM, "#.0") & " / " & nfw
Forms!CondSum_frm!nbrModTime = ModTime
Forms!CondSum_frm!nbrNFWERT = nfw
Forms!CondSum_frm!nbrTotalERT = ERTNOM

Forms!CondSum_frm.SetFocus
DoCmd.MoveSize , , , 9000
Forms!CondSum_frm.ScrollBars = 0
Forms![Craft Data].SetFocus
DoCmd.MoveSize , , , 9000
Forms!CondSum_frm.ScrollBars = 0
Forms!CondSum_frm.SetFocus

Set rstRecord = Nothing
Set DbInsp = Nothing

Exit_Sub:
Exit Sub

Err_Close:
If Err.Number = 13 Then
Resume Next
Else
MsgBox "Error is " & Err.Number & " " & Err.Description & ". Error is Close Function."
Resume Exit_Sub
End If


End Sub

[boblarson]

The SumID has to be OUTSIDE of the quotes.

Set rstRecord = DbInsp.OpenRecordset("SELECT Inspection.[Code 1], Inspection.[Code 2], Inspection.CraftID," _
& "Inspection.SummaryID FROM Inspection WHERE (Inspection.SummaryID = " & SumID & ");",dbOpenDynaset)

[Jeddell]

Damn, I should have remembered that! Shows how rusty I am with Access.

Cheers. problem solvered!