Using doCmd.RunSQL

[mbamber]

Hi all, I have the following code:

Private Sub Membership_Number_AfterUpdate()

Dim SQL As String

SQL = "INSERT INTO TBL_MemberLeagues ( League_ID, Member_ID, Registered )" & Chr(10) & _
"SELECT TBL_League.League_ID, TBL_Members.Membership_Number, False AS Expr1" & Chr(10) & _
"FROM TBL_League, TBL_Members" & Chr(10) & _
"WHERE (((TBL_Members.Membership_Number)=[Forms]![FRM_Members]![Membership_Number]));"

DoCmd.RunSQL SQL

End Sub

The idea of this code is to append specific data to TBL_MemberLeagues. What I don't understand is why this code doesn't work, but

INSERT INTO TBL_MemberLeagues ( League_ID, Member_ID, Registered )
SELECT TBL_League.League_ID, TBL_Members.Membership_Number, False AS Expr1
FROM TBL_League, TBL_Members
WHERE (((TBL_Members.Membership_Number)=[Forms]![FRM_Members]![Membership_Number]));

works as a stand alone query. I know the code is running because I've put a message box after it, which displays fine.

Any ideas?

TIA

 

[madefemere]

Hello,
And like this ?

Private Sub Membership_Number_AfterUpdate()
Dim SQL As String  
SQL = "INSERT INTO TBL_MemberLeagues ( League_ID, Member_ID, Registered )" & Chr(10) & _
"SELECT TBL_League.League_ID, TBL_Members.Membership_Number, False AS Expr1" & Chr(10) & _
"FROM TBL_League, TBL_Members" & Chr(10) & _ 
"WHERE (((TBL_Members.Membership_Number)=" & [Forms]![FRM_Members]![Membership_Number] & "));" 
DoCmd.RunSQL SQL  
End Sub

 

[mbamber]

Still no luck. It's really confusing me. Thanks for the suggestion though! Keep 'em coming!!

 

[pbaldy]

This should help:

http://www.baldyweb.com/ImmediateWindow.htm

Offhand, pay attention to spaces between lines. Also, I wouldn't bother with the Chr(10).

 

[mbamber]

OK. This is what it returned:

INSERT INTO TBL_MemberLeagues ( League_ID, Member_ID, Registered ) SELECT TBL_League.League_ID, TBL_Members.Membership_Number, False AS Expr1 FROM TBL_League, TBL_Members WHERE (((TBL_Members.Membership_Number)=56545210));

I don't know much about SQL so is this a valid SQL statement?

 

[JHB]

Try by changing the False to 0 instead:

SQL = "INSERT INTO TBL_MemberLeagues ( League_ID, Member_ID, Registered )" & Chr(10) & _ "SELECT TBL_League.League_ID, TBL_Members.Membership_Number, 0 AS Expr1" & Chr(10) & _ "FROM TBL_League, TBL_Members" & Chr(10) & _ "WHERE (((TBL_Members.Membership_Number)=" & [Forms]![FRM_Members]![Membership_Number] & "));"

Is "Membership_Number" a number field in the table? Else you have to change it to:

...='" & [Forms]![FRM_Members]![Membership_Number] & "'));"

 

[namliam]

FROM TBL_League, TBL_Members WHERE

Are you sure about having these two tables in your select statement? I cannot imagine the cartesian join is intented.

 

[mbamber]

Its OK. Thanks for all your replies. I decided I was completely over complicating it and thus didn't even need the SQL.

Thanks anyway!