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

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.

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

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

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



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??

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?

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

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 & "';"

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.

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:

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

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

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 [tbl_members]SET [Tutor_id] = 'John Smith' WHERE [tbl_members].Members_id = '4267'

and you want:

UPDATE [tbl_members] SET [Tutor_id] = 'John Smith' WHERE [tbl_members].Members_id = 4267


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

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 [tbl_members]SET [Tutor_id] = 'John Smith' WHERE [tbl_members].Members_id = '4267'

and you want:

UPDATE [tbl_members] SET [Tutor_id] = 'John Smith' WHERE [tbl_members].Members_id = 4267


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

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

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.

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

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

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

strSQL = "UPDATE [tbl_members]" & _
" SET [Tutor_id] = '" & strtutor & "' " & _
-^--> Add THIS Space
"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