Re: SQL statment errors

If you believe there is an error in your SQL statement, the best thing to do
is display it with Debug.Print. Something like this:

strSQL = "INSERT INTO DEM (" & myFieldName & ")" & _
"VALUES (" & Me(myFieldName).Value & ")" & _
"FROM (" & myFieldName & ")" & _
"WHERE DEM.ParticiapantID = " & Me!ParticipantID & ";"
Debug.Print strSQL

Then put a breakpoint on the line immediately following the Debug. In the
Immediate Window, you will see how the statement is being evaluated. If it
still looks good, you can copy and paste it into a query to see run-time
error messages from the query engine.

My guess is that you don't have any spaces between the parens at the end of
each line and the reserved word following it. Sometimes this confuses the
query engine. But the debug.print will tell you for sure.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


"drummergirl863" <drummergirl863@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:546DA9FC-7499-4D56-A7D5-2B56B505CA86@xxxxxxxxxxxxxxxx
Hi-- I am new to Access & VB, and don't know a whole lot yet. I'm
creating a
database for research, and the data needs to be entered twice, per the
professor's request. The db is structured so that I have identical tables
and forms, one for each round of data entry. I want to be able to catch
when
there's an error, and for the code to look up the value in the Table 1 and
change it to the value entered in Table 2 if the user chooses to do so.
I'm
trying to use an SQL statement, and I have tweaked it to the point that I
think it will work, except it's giving me an error saying I didn't put a
(;)
at the end of the statement.

Can anyone help, and explain it in layman's terms? I've spend so much
time
on this issue, and need it to be resolved!

Here's the code I have in the field's event proptery AfterUpdate (DEM is
the
form name, and ParticipantID is the unique number assigned to every
record):

Private Sub DEMgender_AfterUpdate()

Dim myValue As String
Dim myFieldName As String
Dim msg, style, title, response
Dim strSQL As String

myFieldName = Screen.ActiveControl.Name

myValue = DLookup(myFieldName, "DEM", "[ParticipantID]=" &
Me!ParticipantID)

If Me(myFieldName).Value <> myValue Then

msg = "Yo Dude! You entered conflicting data!" & vbCrLf & vbCrLf & _
"A previous value of: " & vbCrLf & vbCrLf & _
myValue & vbCrLf & vbCrLf & " was entered by the dude who did this
first." &
vbCrLf & vbCrLf & _
"Do you want to overwrite the previous value?" & vbCrLf & _
"Click YES use your new value and overwrite the previous value."
style = vbYesNo + vbQuestion + vbDefaultButton1
title = "WHOA THERE COWBOY"
response = MsgBox(msg, style, title)

If response = vbYes Then

strSQL = "INSERT INTO DEM (" & myFieldName & ")" & _
"VALUES (" & Me(myFieldName).Value & ")" & _
"FROM (" & myFieldName & ")" & _
"WHERE DEM.ParticiapantID = " & Me!ParticipantID & ";"
DoCmd.RunSQL strSQL

msg = "You have successfully overwritten the work of one of your
colleagues" & _ vbCrLf & vbCrLf & vbCrLf & _
"So, you think you're better than they are? You had better be right or
there will
be hell to pay for this!"
style = vbOK + vbDefaultButton1
title = "Overwrite complete."


Else: msg = "User has chosen not to overwrite the existing value."
style = vbOK + vbDefaultButton1
title = "No overwrite."

Me(myFieldName).SetFocus

End If

End If

End Sub


Thanks in advance for your help...



.

Loading