Ken, are you out there? Still having table trouble..

  • Thread starter Thread starter bpuppygrrl
  • Start date Start date
B

bpuppygrrl

Ken, I used the code you gave me, but I'm getting a "Compile Error Unexpected
Sub, Function or Property" error for this line:

strSQL "INSERT INTO Locations(Locations) VALUES (""" & NewData & """)"

What have I done to it???

Thanks!
 
Sorry, I'm not Ken.

If you are defining strSql then you need
strSQL = "your string"

I'm assuming that you already have a

Dim strSql As String
at the top of the functions

Evi
 
Without seeing what Ken gave you, I can't indicate any other errors, but
you're missing an equal sign between the variable name and the SQL string:

strSQL = "INSERT INTO Locations(Locations) VALUES (""" & NewData & """)"
 
Without checking back to see what I sent you I'm not sure whether the fault
was mine or not, but the line is missing an equals sign:

strSQL = "INSERT INTO Locations(Locations) VALUES (""" & NewData & """)"

Just to be sure this is what you want it will insert the value typed into
the combo box into a field called Locations in a table, also called
Locations. I'd have expected the field name (that's the one in parentheses)
to be Location (singular). I tend to use plural or collective nouns for
tables and singular nouns for fields wherever possible; it seems more logical
to me that way.

Ken Sheridan
Stafford, England
 
Hi - Thank you so much, I'm so grateful for all this help. I've put in that
errant =, but it still opens the debugger and highlights that line, although
it no longer gives me an error message. This is what the whole thing looks
like now:

Private Sub Location_NotInList(NewData As String, Response As Integer)

Dim cmd As ADODB.Command
Dim ctrl As Control
Dim strSQL As String, strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add new Location to list?"

strSQL = "INSERT INTO Locations(Locations) VALUES (""" & NewData & """)"

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If MsgBox(strMessage, vbYesNo = vbQuestion) = vbYes Then
cmd.CommandText = strSQL
cmd.Execute
Response = acDataErrAdded
Else
Response = acDataErrContinue
ctrl.Undo

End If

Set cmd = Nothing

Any ideas? Thank you!!

- Em
 
Do you have a watchpoint set on the line?

You also have an equals sign in place of a plus sign later in the code:

If MsgBox(strMessage, vbYesNo = vbQuestion) = vbYes Then

should be:

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then

Ken Sheridan
Stafford, England
 
Hi Ken, Thanks, I have fixed the impending disaster with the +. Forgive me,
but what is a watchpoint?

- Em
 
Back
Top