SQL statment errors

  • Thread starter Thread starter drummergirl863
  • Start date Start date
D

drummergirl863

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...
 
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 said:
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...
Click to expand...
 
There are several problems with your code.
In this line:
myFieldName = Screen.ActiveControl.Name
you are putting the name of the control in the variable.

Then in your SQL statement, you are putting then name of the control, not
the value of the control.

You said you want to update a field in the table, but your SQL is an INSERT
statement which adds rows to tables. You need an UPDATE statement.

In your DLookup, you have two errors
myValue = DLookup(myFieldName, "DEM", "[ParticipantID]=" &
Me!ParticipantID)

You are trying to look in a field that has the name of the control on your
form, not the name of the field the control is bound to, but they could but
should not have the same name.

myValue is Dimmed as a string variable. If a matching record is not found,
the DLookup will return a Null and you will get an Invalid Use of Null error.
Only variant variables can receive a Null value.

This Dim statement:
Dim msg, style, title, response

Is dimming all these variables a Variants. You should only use variant
variables when you either expect a possible Null value or you could receive
more than one data type in the variable. There are some situations where a
Variant is required, but for the most part, you should dim your varialbes
with a more explicit data type.
msg and tltle should be string stype and response should be long.

Remove the : from this line.
Else: msg = "User has chosen not to overwrite the existing value."

You can't set the focus to the current control.
Me(myFieldName).SetFocus

The code should be in the Before Update event rather than the After Update
event. The Before Update event can be canceled. The After Update is too
late. The update to the control has already been done. When you determine
you don't what the update to occur, you put a line in to cancel it:

Cancel = True

--
Dave Hargis, Microsoft Access MVP


drummergirl863 said:
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...
Click to expand...
 
Back
Top