Subform SQL query syntax & the 3075 error

[Guest]

The code below yeilds the 3075 error. The primary key is the 'projectid'. I
have similar code that works for other searches but this keeps giving me the
syntax error. I am blind and can't see the forest through the trees. I
would be very grateful for any help. Thank you in advance.

Private Sub txtFileName_AfterUpdate()
On Error GoTo Err_ACOGIS

Dim stDocName As String
Dim stLinkCriteria As String

If IsNull(Me.txtFileName) = True Then
MsgBox "Please Enter A File Name", vbInformation
Exit Sub
End If

stDocName = "frmWorkRequest"

stLinkCriteria = "SELECT DISTINCTROW WORKREQUEST.* FROM WORKREQUEST " & _
"INNER JOIN DEVELOPEDDATADETAILS ON " & _
"WORKREQUEST.PROJECTID = DEVELOPEDDATADETAILS.PROJECTID " & _
"WHERE DEVELOPEDDATADETAILS.PROJECTLOCATION LIKE '*" & Me.txtFileName &
"*';"

If DCount("*", "WORKREQUEST", stLinkCriteria) = 0 Then
MsgBox "No Records Can Be Found" & vbNewLine & "Like File Name " &
Me.txtFileName, vbExclamation
Me.txtFileName = ""
Exit Sub
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
Me.txtFileName = ""
End If



Exit_Err_ACOGIS:
Exit Sub
Err_ACOGIS:
MsgBox "You have encountered error #" & vbNewLine & Err.Number & " " &
Err.Description, vbInformation
Resume Exit_Err_ACOGIS
End Sub

 

[Douglas J. Steele]

The problem is likely your DCount statement. Your 3rd argument
(stLinkCriteria) is invalid.

 

[Guest]

Thank you for the help. I removed the DCount statement and I still get the
syntax error. Any other thoughts?

 

[Douglas J. Steele]

Just noticed you're trying to use stLinkCriteria to open a form as well. All
you can use is the Where clause (without the keyword "Where")

 

[Guest]

Douglas, thank you for your continued discussion. I am stuck on this because
this (almost identical) bit of code works with two other searches, being used
in the same (or what I believe to be the same) way. So, are you saying that
I should break up the SQL query and assign each segment to a different
variable? Or... I'm sorry, I'm a little thick on this one... what are you
saying?

 

[Douglas J. Steele]

A valid criteria would be something like

stLinkCriteria = "PROJECTLOCATION LIKE '*" & Me.txtFileName & "*;"

You're not using the SQL statement in any way that I can see in your code.

 

[Guest]

Well, the stLinkCriteria currently looks like this:

stLinkCriteria = "SELECT DISTINCTROW WORKREQUEST.* FROM WORKREQUEST " & _
"INNER JOIN DEVELOPEDDATADETAILS ON " & _
"WORKREQUEST.PROJECTID = DEVELOPEDDATADETAILS.PROJECTID " & _
"WHERE DEVELOPEDDATADETAILS.PROJECTLOCATION LIKE '*" & Me.txtFileName &
"*';"

and that last line has the "...PROJECTLOCATION LIKE '*" & Me.txtFileName &
"*';"

When I ask for a message box report of the sql query it looks like:

"stLinkCriteria = SELECT DISTINCTROW WORKREQUEST.* FROM WORKREQUEST INNER
JOIN DEVELOPEDDATADETAILS ON WORKREQUEST.PROJECTID =
DEVELOPEDDATADETAILS.PROJECTID WHERE DEVELOPEDDATADETAILS.PROJECTLOCATION
LIKE '*mxd*';"

and then I get the syntax error. This works for two other searches on the
same form using a subform to search by with the same method. I just can't
find the syntax error. Do you know of a way I could get a report of the
error within the code? What am I missing?

Thank you for you continued conversation & help, sorry....

 

[Guest]

GOT IT!!

Thank you Douglas! Yes, I'm a dummy. Ok here's where my confusion and lack
of communication occurred...

Open Args

I was using the stLinkCriteria as open args to open the other form, so
rather than DoCmd.OpenForm stDocName, , , stLinkCriteria

it should have been

DoCmd.OpenForm stDocName, , , , , , stLinkCriteria

So, thank you for your patience and help.

 

[Douglas J. Steele]

You might find it easier to use named arguments, rather than relying on
counting the commas.

DoCmd.OpenForm stDocName, , , , , , stLinkCriteria

could be

DoCmd.OpenForm FormName:=stDocName, OpenArgs:= stLinkCriteria

or, more simply,

DoCmd.OpenForm stDocName, OpenArgs:= stLinkCriteria