Update SQL in VB problem

[dwayne dibley]

All I am trying to do is allow the user to input a code into a an unbound text box, which will then be saved to a table field. After the user had retrieved the member's details via the memmber id.

As far as I can tell it must be done via SQL and I am using VB to do this.

Anyway here is my code:-

Private Sub Command7_Click()

DoCmd.RunSQL "UPDATE [Member_table] SET [Tutor_id]= """ & Me.txt_tutor.Value & """;"
WHERE Member_table.Member_id = """& me.member_id.value &""";

End Sub

VB is either returning error message " syntax error" or function not defined.

Any help??

Thanks

 

[MSAccessRookie]

All I am trying to do is allow the user to input a code into a an unbound text box, which will then be saved to a table field. After the user had retrieved the member's details via the memmber id.

As far as I can tell it must be done via SQL and I am using VB to do this.

Anyway here is my code:-

Private Sub Command7_Click()

DoCmd.RunSQL "UPDATE [Member_table] SET [Tutor_id]= """ & Me.txt_tutor.Value & """;"
WHERE Member_table.Member_id = """& me.member_id.value &""";

End Sub

VB is either returning error message " syntax error" or function not defined.

Any help??

Thanks

Wherever you have " in your formula as a string delimiter, you should change it to '. VB Accepts ' as a valid string delimiter.

[I]DoCmd.RunSQL "UPDATE [Member_table] SET [Tutor_id]= [COLOR=red][B]'[/B][/COLOR]" & Me.txt_tutor.Value & "[COLOR=red][B]'[/B][/COLOR];"[/I]
[I][COLOR=red]WHERE Member_table.Member_id = [COLOR=seagreen][B]'[/B][/COLOR]"& me.member_id.value &"[COLOR=seagreen][B]'[/B][/COLOR];"[/COLOR][/I]

 

[dwayne dibley]

Wherever you have " in your formula as a string delimiter, you should change it to '. VB Accepts ' as a valid string delimiter.

[I]DoCmd.RunSQL "UPDATE [Member_table] SET [Tutor_id]= [COLOR=red][B]'[/B][/COLOR]" & Me.txt_tutor.Value & "[COLOR=red][B]'[/B][/COLOR];"[/I]
[I][COLOR=red]WHERE Member_table.Member_id = [COLOR=seagreen][B]'[/B][/COLOR]"& me.member_id.value &"[COLOR=seagreen][B]'[/B][/COLOR];"[/COLOR][/I]

Thank for that. I have tried what you recommended but I'm still getting error messages " expression: expected" and it highlights ' just before me.member.id.

Any ideas??

 

[MSAccessRookie]

Thank for that. I have tried what you recommended but I'm still getting error messages " expression: expected" and it highlights ' just before me.member.id.

Any ideas??

What does your code look like?

 

[dwayne dibley]

What does your code look like?

Private Sub Command7_Click()

DoCmd.RunSQL "UPDATE [Member_table] SET [Tutor_id]= '" & Me.txt_tutor.Value & "';"
WHERE Member_table.Member_id ='"& me.member_id.value &"';"

End Sub

 

[MSAccessRookie]

Private Sub Command7_Click()

DoCmd.RunSQL "UPDATE [Member_table] SET [Tutor_id]= '" & Me.txt_tutor.Value & "';"
WHERE Member_table.Member_id ='"& me.member_id.value &"';"

End Sub

If this is exact, then the format is not correct. The UPDATE and WHERE are parts of the same query, and the ";" in the middle needs to be eliminated. Try this instead:

DoCmd.RunSQL "UPDATE [Member_table] SET [Tutor_id] = '" & _
    Me.txt_tutor.Value & "'" & WHERE Member_table.Member_id = '"& _
    me.member_id.value & "';"

 

[dwayne dibley]

If this is exact, then the format is not correct. The UPDATE and WHERE are parts of the same query, and the ";" in the middle needs to be eliminated. Try this instead:

DoCmd.RunSQL "UPDATE [Member_table] SET [Tutor_id] = '" & _
    Me.txt_tutor.Value & "'" & WHERE Member_table.Member_id = '"& _
    me.member_id.value & "';"

Na, still no luck mate. I have tried to change a few things in desparation, but with no luck.

My code is now:-

Private Sub cmbupdate_AfterUpdate()
Dim strSQL As String
Dim tutor As String
Dim memberid As String
strTutor = Me.cmbupdate.Column(1)
strMemberid = Me.txtmemberid.Value

strSQL = "UPDATE [tbl_members] SET [Tutor_id]= '" & strTutor & " '"
WHERE tbl_members.Members_id = '" & strMemberid & "';"

End Sub

It returns error message: "compile error: syntax error" and highlights the Where line.

 

[MSAccessRookie]

Na, still no luck mate. I have tried to change a few things in desparation, but with no luck.

My code is now:-

Private Sub cmbupdate_AfterUpdate()
Dim strSQL As String
Dim tutor As String
Dim memberid As String
strTutor = Me.cmbupdate.Column(1)
strMemberid = Me.txtmemberid.Value

strSQL = "UPDATE [tbl_members] SET [Tutor_id]= '" & strTutor & " '"
WHERE tbl_members.Members_id = '" & strMemberid & "';"

End Sub

It returns error message: "compile error: syntax error" and highlights the Where line.

When you are building a string, either all of it must be on the same line, or separated lines must each (until the last) end with a continuation marker ("_"). Try this:

[B]strSQL = "UPDATE [tbl_members] & _[/B]
[B]    SET [Tutor_id]= '" & strTutor & "' " & _[/B]
[B]    WHERE tbl_members.Members_id = '" & strMemberid & "';"[/B]

 

[dwayne dibley]

It will now compile ok, but if I try to run the code it returns a request for paremeter [tbl_members.members_id]. I have checked the names and I cant see why it would request this value??

Private Sub Command7_Click()
Dim strSQL As String
Dim strtutor As String
Dim strmemberid As String
strtutor = Me.cmbupdate.Column(0)
strmemberid = Me.txtmemberid.Value

strSQL = "UPDATE [tbl_members]" & _
"SET [Tutor_id] = '" & strtutor & "' " & _
"WHERE [tbl_members].Members_id = '" & strmemberid & "';"
DoCmd.RunSQL (strSQL)

End Sub

 

[MSAccessRookie]

It will now compile ok, but if I try to run the code it returns a request for paremeter [tbl_members.members_id]. I have checked the names and I cant see why it would request this value??

Private Sub Command7_Click()
Dim strSQL As String
Dim strtutor As String
Dim strmemberid As String
strtutor = Me.cmbupdate.Column(0)
strmemberid = Me.txtmemberid.Value

strSQL = "UPDATE [tbl_members]" & _
"SET [Tutor_id] = '" & strtutor & "' " & _
"WHERE [tbl_members].Members_id = '" & strmemberid & "';"

MsgBox strSQL

DoCmd.RunSQL (strSQL)

End Sub

Add the command in Red to display the string strSQL after it is defined. I think it is not defined correctly. I think a space is missing before the word SET and that the Members_id will be treated as a string when you want it to be a number.

When strtutor="John Smith" and Members_id=4267, I think SQL Statement will be:

UPDATE [COLOR=red][B][tbl_members]SET[/B][/COLOR] [Tutor_id] = 'John Smith' WHERE [tbl_members].Members_id = [COLOR=red][B]'4267'[/B][/COLOR]

and you want:

UPDATE [COLOR=red][B][tbl_members] SET[/B][/COLOR] [Tutor_id] = 'John Smith' WHERE [tbl_members].Members_id = [COLOR=red][B]4267[/B][/COLOR]

Make the appropriate changes if I am correct. If the Members_id is a string, you can ignore the last part of the comment

 

[dwayne dibley]

Add the command in Red to display the string strSQL after it is defined. I think it is not defined correctly. I think a space is missing before the word SET and that the Members_id will be treated as a string when you want it to be a number.

When strtutor="John Smith" and Members_id=4267, I think SQL Statement will be:

UPDATE [COLOR=red][B][tbl_members]SET[/B][/COLOR] [Tutor_id] = 'John Smith' WHERE [tbl_members].Members_id = [COLOR=red][B]'4267'[/B][/COLOR]

and you want:

UPDATE [COLOR=red][B][tbl_members] SET[/B][/COLOR] [Tutor_id] = 'John Smith' WHERE [tbl_members].Members_id = [COLOR=red][B]4267[/B][/COLOR]

Make the appropriate changes if I am correct. If the Members_id is a string, you can ignore the last part of the comment

I still cant get it to work mate and it's probably just me being a total nugget. I''m just going to leave it as I have already spent a silly amount of time trying to get it to work.

Unless ya know of another way of being able to update field in a table?

Thanks for all of your help, its very much Appreciated.

Chris

 

[MSAccessRookie]

When you added the MsgBox strSQL statement, did it read as you expect it to?

 

[dwayne dibley]

When you added the MsgBox strSQL statement, did it read as you expect it to?

It came back:-

UPDATE[tblmembers]SET [Tutor_id] = ‘30006’ WHERE [tblemembers].Members_id = ‘1234’;

The variables are correct, as selected, and there is obviously a space missing before SET(But I cant change it without making a balls of the ' " '(refs to the txt/combo fields))

Thanks Again.

 

[MSAccessRookie]

Can't you just add a space in the creation of the string strsql?

strSQL = "UPDATE [tbl_members]" & _
" SET [Tutor_id] = '" & strtutor & "' " & _
[B][COLOR=red]-^--> Add THIS Space[/COLOR]
[/B]"WHERE [tbl_members].Members_id = '" & strmemberid & "';"

 

[dwayne dibley]

Can't you just add a space in the creation of the string strsql?

strSQL = "UPDATE [tbl_members]" & _
" SET [Tutor_id] = '" & strtutor & "' " & _
[B][COLOR=red]-^--> Add THIS Space[/COLOR][/B]
"WHERE [tbl_members].Members_id = '" & strmemberid & "';"

Thanks, Ive already resolved it.

I had a space in the member_id name field which I wasnt aware of. Sorry for wasting your time.

Thanks again

Chris