RunSQL UPDATE Error

[Guest]

Greetings.

I have designed a form to allow users to update the place and the time
of runners in a foot race. After users enter the first name, last name,
rank, place and/or time, they hit the Submit button to update the Place
and the Time of the given runner.

My problem is that I cannot get the UPDATE query to execute properly
after the user clicks on the Submit button. Everytime I hit Submit,
I get the following error:

Compile Error
Syntax Error

Here is the code that I have written again the Click event of the
submit button:

Private Sub Submit_Click()
DoCmd.RunSQL "UPDATE runners SET Place = '" & Me.Place "'," & _
" WHERE ([Last Name] = '" & Me.[Last Name] & "')" & _
" AND ([First Name] = '" & Me.[First Name] & "')" & _
" AND (Rank = '" & Me.Rank "')"
End Sub

The Last Name, First Name and Rank fields are text in both the form
and in table runners. The Place field is a number in both.

Can anybody spot the flaw here?

Regards,

Charles

 

[Brendan Reynolds]

You're missing an ampersand here ...

" AND (Rank = '" & Me.Rank "')"

Should be ...

" AND (Rank = '" & Me.Rank & "')"

 

[Matthias Klaey]

Greetings.

I have designed a form to allow users to update the place and the time
of runners in a foot race. After users enter the first name, last name,
rank, place and/or time, they hit the Submit button to update the Place
and the Time of the given runner.

My problem is that I cannot get the UPDATE query to execute properly
after the user clicks on the Submit button. Everytime I hit Submit,
I get the following error:

Compile Error
Syntax Error

Here is the code that I have written again the Click event of the
submit button:

Private Sub Submit_Click()
DoCmd.RunSQL "UPDATE runners SET Place = '" & Me.Place "'," & _
" WHERE ([Last Name] = '" & Me.[Last Name] & "')" & _
" AND ([First Name] = '" & Me.[First Name] & "')" & _
" AND (Rank = '" & Me.Rank "')"
End Sub

The Last Name, First Name and Rank fields are text in both the form
and in table runners. The Place field is a number in both.

[...]

In the first line, the comma at the end should not be there, and since
Place is numerical, do not use the single quotes around Me.Place.
Try this:

"UPDATE runners SET Place = " & Me.Place & _

HTH
Matthias Kläy

 

[Guest]

Thanks. That fixed the error with the Place. Now I'm trying
to enter the runner's time as well and am gettng the following
error:

Run-time error 3144
Syntax error in UPDATE statement

Here's the code modified to update the runner's Time:

Private Sub Submit_Click()
DoCmd.RunSQL "UPDATE runners SET Place = " & Me.Place & _
", Time = '" & Me.Time & "'" & _
" WHERE ([Last Name] = '" & Me.[Last Name] & "')" & _
" AND ([First Name] = '" & Me.[First Name] & "')" & _
" AND (Rank = '" & Me.Rank & "')"
End Sub

Both Time in table runners and Time in the form have the Long
Time format. I got a feeling that I need to be us

I tried using Me.Time without the single quotes:

", Time = " & Me.Time & _

and Me.Time enclosed by #:

", Time = #" & Me.Time & "#" & _

but these statements gave the same error.

Regards,

Charles

Greetings.

I have designed a form to allow users to update the place and the time
of runners in a foot race. After users enter the first name, last name,
rank, place and/or time, they hit the Submit button to update the Place
and the Time of the given runner.

My problem is that I cannot get the UPDATE query to execute properly
after the user clicks on the Submit button. Everytime I hit Submit,
I get the following error:

Compile Error
Syntax Error

Here is the code that I have written again the Click event of the
submit button:

Private Sub Submit_Click()
DoCmd.RunSQL "UPDATE runners SET Place = '" & Me.Place "'," & _
" WHERE ([Last Name] = '" & Me.[Last Name] & "')" & _
" AND ([First Name] = '" & Me.[First Name] & "')" & _
" AND (Rank = '" & Me.Rank "')"
End Sub

The Last Name, First Name and Rank fields are text in both the form
and in table runners. The Place field is a number in both.

[...]

In the first line, the comma at the end should not be there, and since
Place is numerical, do not use the single quotes around Me.Place.
Try this:

"UPDATE runners SET Place = " & Me.Place & _

HTH
Matthias Kläy