Run-Time error 3141

[CementCarver]

I'm getting a run-time error 3141 regarding some select statement, but the program stops at a line that doesn't deal with a sql statement at all...?

It stops at a line where I'm passing the open arguments to a form:



DoCmd.OpenForm "frm_CREATE_PRODUCER", , , , , , MEMBER_ID

Not sure what the error message is trying to tell me.......

Any help would be appreciated......CementCarver

[Bob Fitz]

What is the Record Source of the form called "frm_CREATE_PRODUCER" that you are trying to open.

[rzw0wr]

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

FYI, nice to have sometimes.

http://www.fmsinc.com/microsoftacces...ption2007.html

Dale

[CementCarver]

The following is the record source that I am using.....

Set db = CurrentDb()
strSQL = "SELECT FULLPACKER.[Business Name] from FULLPACKER WHERE [Business Name] = " & "'" & strBUSINESS_NAME & "'"

Set rstPackerExists = db.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

If the name of business name doesn't exist, then I load that record and then try to call up the form to allow them input.

CC

[Missinglinq]

...but the program stops at a line that doesn't deal with a sql statement at all...

What is the line immediately prior to the highlighted line? The Access Gnomes have a bad habit of highlighting the line after the actual line that causes an error.

Linq ;0)>

[CementCarver]

Missinglinq,

Below are the two modules in question: the incoming rstPacker and strBusiness_Name are set up in the calling module.

.......Calling module......
Private Sub Create_New_Packer_Click()
Dim rstPacker As Recordset
Dim strBUSINESS_NAME As String
Dim db As Database

Set db = CurrentDb
Set rstPacker = db.OpenRecordset("FULLPACKER", adOpenDynamic, adLockOptimistic)

strBUSINESS_NAME = [Forms]![BUSINESS_MAIN]![BUSINESS_NAME]

'Call AddPacker Subroutine
AddPacker rstPacker, strBUSINESS_NAME

End Sub

....Receving module.....

Private Sub AddPacker(rstPacker As Recordset, _
strBUSINESS_NAME As String)

Dim Grower_lnk As Integer
Dim PackerLicence As String
Dim grow_lic As String
Dim PackName As String
Dim PkCheckBox As Long
'Dim intMEMBER_ID As Integer
Dim rstPackerExists As Recordset
Dim db As Database
Dim strSQL As String

PkCheckBox = Forms!BUSINESS_MAIN!Check_Packer

If PkCheckBox <> 0 Then

ElseIf PkCheckBox = 0 Then
MsgBox "Please Select Packer Check Box before Proceeding"
Exit Sub
End If

Set db = CurrentDb()
strSQL = "SELECT FULLPACKER.[Business Name] from FULLPACKER WHERE [Business Name] = " & "'" & strBUSINESS_NAME & "'"
Debug.Print strSQL

Set rstPackerExists = db.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

PackerLicence = DMax("[PLICENCE]", "FULLPACKER")
PackerLicence = PackerLicence + 1

Debug.Print PackerLicence

' Insert packer record

If rstPackerExists.EOF Then
With rstPacker
.AddNew
![Business Name] = strBUSINESS_NAME
![PLicence] = PackerLicence
Grower_lnk = Forms!BUSINESS_MAIN!MEMBER_ID
!MEMBER_ID = Grower_lnk
.Update
'.Bookmark = .LastModified
End With
DoCmd.Requery
Me.Refresh
rstPacker.Close
End If

If Not rstPackerExists.EOF Then
MsgBox "Packer Record for: " & strBUSINESS_NAME & " already exists, Please proceed with edit session.", vbCritical
Exit Sub
End If

PackName = strBUSINESS_NAME

'Pass arguments to form load event

DoCmd.OpenForm "frm_CREATE_PACKER", , , , , , Grower_lnk.............line that indicates the error.....

'Open the form based on where clause

'sWHERE = strBUSINESS_NAME

DoCmd.OpenForm "frm_CREATE_PACKER", , , , , , Grower_lnk

If Len(Grower_lnk) > 0 Then
Forms!frm_CREATE_PACKER![MEMBER_ID].SetFocus
DoCmd.GoToControl "MEMBER_ID_FULLPACKER"
DoCmd.FindRecord Grower_lnk, , True, , True, , True
End If


MsgBox "New Packer Record Has Been Created for: " & PackName

End Sub




CC