Syntax Error in Query Expression

[Hugh self taught]

Hi All,

It seems us "Wannabe's" get stuck on Error 3075 from time to time.

I realize there are differences but I'm not that up to speed to know them
correctly yet.

I have the following query:
SELECT Family.RelativeRef, Main_Table.Main_ID
FROM (AdultChild INNER JOIN Main_Table ON
AdultChild.AdultChild_ID=Main_Table.Adult_Child) INNER JOIN Family ON
Main_Table.Main_ID=Family.MaintblRef
WHERE (((Family.RelativeRef)=[Me].[Main_ID]));
which when run (Datasheet view) prompts (quite correctly) for Me.Main_ID &
then shows the correct records. I created it to get the sql for a DCount
statement in VBA. The code is as follows:

Private Sub Adult_Child_LostFocus()
Dim strWhere As String
strWhere = "SELECT Family.RelativeRef, Main_Table.Main_ID " & _
"FROM (AdultChild INNER JOIN Main_Table ON AdultChild.AdultChild_ID =
Main_Table.Adult_Child) INNER JOIN Family ON Main_Table.Main_ID =
Family.MaintblRef " & _
"WHERE (((Family.RelativeRef)=[Forms]![MainMenu].[Main_ID]));"
Debug.Print strWhere

If Me.Adult_Child <> 1 And DCount("Main_ID", "Main_Table", strWhere) = 0 Then
MsgBox "Got one"
End If

End Sub

I don't get my msgbox saying "Got one" instead I get Error 3075 Syntax Error
in Query Expression

Would some kind soul be so good as to explain what I've done wrong &
possibly what my train of thought should be between the 2 scenarios so I may
have a better understanding in future.

Cheers for now

 

[John Spencer]

StrWhere should only be a WHERE string without the word WHERE.

Perhaps you can get the following to work for you. Note the change in all
three arguments.

DCount("*","Family","RelativeRef=""" & [Forms]![MainMenu]![Main_ID] & """")

If RelativeRef is a number field then the third argument is slightly different
since you don't need the quote marks as delimiters
DCount("*","Family","RelativeRef=" & [Forms]![MainMenu]![Main_ID])

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Hugh self taught]

What can I say John. You're awesome. Thanks a stack

StrWhere should only be a WHERE string without the word WHERE.

Perhaps you can get the following to work for you. Note the change in all
three arguments.

DCount("*","Family","RelativeRef=""" & [Forms]![MainMenu]![Main_ID] & """")

If RelativeRef is a number field then the third argument is slightly different
since you don't need the quote marks as delimiters
DCount("*","Family","RelativeRef=" & [Forms]![MainMenu]![Main_ID])

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Hi All,

It seems us "Wannabe's" get stuck on Error 3075 from time to time.

I realize there are differences but I'm not that up to speed to know them
correctly yet.

I have the following query:
SELECT Family.RelativeRef, Main_Table.Main_ID
FROM (AdultChild INNER JOIN Main_Table ON
AdultChild.AdultChild_ID=Main_Table.Adult_Child) INNER JOIN Family ON
Main_Table.Main_ID=Family.MaintblRef
WHERE (((Family.RelativeRef)=[Me].[Main_ID]));
which when run (Datasheet view) prompts (quite correctly) for Me.Main_ID &
then shows the correct records. I created it to get the sql for a DCount
statement in VBA. The code is as follows:

Private Sub Adult_Child_LostFocus()
Dim strWhere As String
strWhere = "SELECT Family.RelativeRef, Main_Table.Main_ID " & _
"FROM (AdultChild INNER JOIN Main_Table ON AdultChild.AdultChild_ID =
Main_Table.Adult_Child) INNER JOIN Family ON Main_Table.Main_ID =
Family.MaintblRef " & _
"WHERE (((Family.RelativeRef)=[Forms]![MainMenu].[Main_ID]));"
Debug.Print strWhere

If Me.Adult_Child <> 1 And DCount("Main_ID", "Main_Table", strWhere) = 0 Then
MsgBox "Got one"
End If

End Sub

I don't get my msgbox saying "Got one" instead I get Error 3075 Syntax Error
in Query Expression

Would some kind soul be so good as to explain what I've done wrong &
possibly what my train of thought should be between the 2 scenarios so I may
have a better understanding in future.

Cheers for now

.