event procedure with docmd.runsql

[Derek Wittman]

Here's my code so far. Seems simple enough. But I cannot figure out why,
even though I'm defining lngoper up front, the code is asking me for it in
another input box WITHOUT any text other than the name of the variable.

Private Sub DoStuff_Click()
Dim lngoper As Long
lngoper = InputBox("Enter Operation ID", "Entry required to continue")
DoCmd.RunSQL ("delete * from tbloperid")
DoCmd.RunSQL ("insert into tbloperid (lngopernumber) values (lngoper)")

End Sub

Once I get it working so I'm only asking the user once, I'd like to output
two queries via transferspreadsheet to one workbook. I think I got that
part. But I cannot make this work. A colleague suggested for the variable's
value, I have a 1 record, 1 field table and store it there. Delete it,
inputbox the new value and insert it via SQL. Link that to the criteria
field in the 2 queries.

The other option, whichever is easier, is an UPDATE SQL command.

Any and all insight will be greately appreciated.

 

[Dale Fye]

Try:

DoCmd.RunSQL ("insert into tbloperid (lngopernumber) values (" & lngoper &
")")

Personally, I prefer using the execute command. In not only blocks the
warning messages you get when using RunSQL, but also gives you a way to
handle errors if they occur

On Error goto ErrHandler

strSQL = "insert into tbloperid (lngopernumber) values (" & lngoper & ")"
currentdb.execute strsql, dbfailonerror

Exit Sub

ErrHandler:
'do something here
Exit Sub

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Derek Wittman]

Got nabbed by the quote-happy SQL again... that fixed it, Dale. Thank you!

 

[Dale Fye]

In this case, it isn't about "quote happy", it's about formatting the sql so
that your variable gets evaluated as the string is built, rather than passing
the insert query a reference to a variable, like you had it.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.