Syntax error

[Guest]

Can anyone tell me what's wrong with this? Thanks, Rob

Dim db As DAO.Database
Dim strSQL As String
Dim varItm As Variant

Set db = CurrentDb()
With Forms!frmRNnotes!lstRNnotesLU
For Each varItm In .ItemsSelected
strSQL = CurrentDb.Execute ("INSERT INTO tblRNnotes (fldRNnotes, fldVisitNo)
" _
& "VALUES ('" & .ItemData(varItm) & ", " & .fldVisitNo Key &
"');"),dbFailOnError

Next varItm
End With

Debug.Print

 

[John W. Vinson]

Can anyone tell me what's wrong with this? Thanks, Rob

Dim db As DAO.Database
Dim strSQL As String
Dim varItm As Variant

Set db = CurrentDb()
With Forms!frmRNnotes!lstRNnotesLU
For Each varItm In .ItemsSelected
strSQL = CurrentDb.Execute ("INSERT INTO tblRNnotes (fldRNnotes, fldVisitNo)
" _
& "VALUES ('" & .ItemData(varItm) & ", " & .fldVisitNo Key &
"');"),dbFailOnError

Next varItm
End With

Probably the fact that there is a blank in the string .fldVisitNo Key
- what's the actual object that you want to insert?

John W. Vinson [MVP]

 

[Guest]

This code is in the dblck event of a lstBox called lstRNnotes. When the user
dblclks on an item I need to store it the tblRNnotes with the fldVisitNo that
is on frmRNnotes that is linked to frmVisit by fldVisitNo.

lstVisit gets it's data from tblRNnotesLU which has canned nursing notes
that the nurse can insert into the tblRNnotes. Hope that's clear.

I am very much the rookie coder and I'm all screwed up. Any help would be
greatly appreciated. Thanks, Robert

 

[John W. Vinson]

This code is in the dblck event of a lstBox called lstRNnotes. When the user
dblclks on an item I need to store it the tblRNnotes with the fldVisitNo that
is on frmRNnotes that is linked to frmVisit by fldVisitNo.

lstVisit gets it's data from tblRNnotesLU which has canned nursing notes
that the nurse can insert into the tblRNnotes. Hope that's clear.

I am very much the rookie coder and I'm all screwed up. Any help would be
greatly appreciated. Thanks, Robert

I'm sorry, Robert, but since I can't see your database, I'm perplexed
by this. A listbox doesn't have fieldnames; you can't look up a value
from a Form, because values are stored in Tables not in Forms; and I
have no idea where fldVisitNo or frmRNnotes are.

Please post the relevant table and fieldnames in the format

tablename
fieldname <datatype> <primary key>
fieldname <datatype>
fieldname <datatype>

and the RowSource query of the listbox, and what you want retrieved
from the listbox and where you want it stored. (The latter should
ideally be in a field in the current form's Recordsource; you'll
normally have this field bound to a Textbox on the form).

John W. Vinson [MVP]

 

[Douglas J. Steele]

You don't assign the results of CurrentDb.Execute to a string. As well, the
quotes in your SQL statement are a little wonky. Try:

Dim db As DAO.Database
Dim strSQL As String
Dim varItm As Variant

Set db = CurrentDb()
With Forms!frmRNnotes!lstRNnotesLU
For Each varItm In .ItemsSelected
strSQL = "INSERT INTO tblRNnotes (fldRNnotes, fldVisitNo) " & _
"VALUES ('" & .ItemData(varItm) & "', " & .fldVisitNo Key & ")"
db.Execute strSQL, dbFailOnError
Next varItm
End With

This assumes the fldVisitNo is a numeric field.

 

[Guest]

Doug, here's how it looks. I get a syntax error on the 2 lines of sql. I
would really appreciate your help with this. Thanks.
Out of curiosity, what does "wonky" mean?

Again, Thanks, Rob
*********************************************************

Dim db As DAO.Database
Dim strSQL As String
Dim varItm As Variant

Set db = CurrentDb()
With Forms!frmRNnotes!lstRNnotesLU
For Each varItm In .ItemsSelected
strSQL = "INSERT INTO tblRNnotes (fldRNnotes, fldVisitNo) " & _
"VALUES ('" & .ItemData(varItm) & "', " & .fldVisitNo Key & ")"
db.Execute strSQL, dbFailOnError
Next varItm
End With
******************************************************

 

[John W. Vinson]

& .fldVisitNo Key &

Robert,

What is your intent in having the word "Key" in this expression?

I don't understand it, and I'm quite sure that Access doesn't
understand it either, and that it is causing the syntax error.

John W. Vinson [MVP]

 

[Guest]

John, thanks for your response. I all f'd up here because I am just learning
VBA and Sql. I took out the word "Key" and now I get the msg; Object doesn't
support this property or method (Error 438).

If you look at the posts earlier in this thread, you'll see (I think) what
I'm trying to do. I'm just not at all sure if I'm going re: it the right way.

If you have the time I would really appreciate you helping me with this
until I get it right. I've been posting for the last 3 days and am getting no
where.

Thanks, rob

 

[John W. Vinson]

This code is in the dblck event of a lstBox called lstRNnotes. When the user
dblclks on an item I need to store it the tblRNnotes with the fldVisitNo that
is on frmRNnotes that is linked to frmVisit by fldVisitNo.

lstVisit gets it's data from tblRNnotesLU which has canned nursing notes
that the nurse can insert into the tblRNnotes. Hope that's clear.

Ok... try this.

Set db = CurrentDb()
With Forms!frmRNnotes!lstRNnotesLU
For Each varItm In .ItemsSelected
strSQL = "INSERT INTO tblRNnotes (fldRNnotes, fldVisitNo) " & _
"VALUES (" & Chr(34) & .ItemData(varItm) & Chr(34) "," & _
Me.fldVisitNo & ");"
db.Execute strSQL, dbFailOnError
Next varItm
End With

Assuming that the ItemData is the text string that you want inserted,
and that you want to insert the VisitNo from the current form control
or field named fldVisitNo into tblRNnotes' fldVisitNo, this should
work. You can step through the code and view the value of strSQL prior
to the Execute statement; it might contain something like

INSERT INTO tblRNnotes (fldRNnotes, fldVisitNo) VALUES("Patient left
the OR in good condition", 3125);

indicating that the patient swept the floor, washed down the operating
table, etc.

Note that I do NOT know the datatype of fldVisitNo - if it's text,
you'll need quotes (Chr(34) is a " character) around it too; nor do I
know the bound column of lstRNnotesLU, I'm assuming that it's a Text
field that you want actually inserted into tblRNnotes. If it's a MEMO
field that you want to insert this will *not* work since a Memo in a
Listbox will be truncated to 255 bytes.

John W. Vinson [MVP]

 

[Douglas J. Steele]

It's complaining about .fldVisitNo. If fldVisitNo is a control on your
form, use Me.fldVisitNo instead.

 

[Guest]

I'm still getting a syntax error on the sql statement. fldVisitNo is a number
fld. You are correct that I want the txt I want inserted into tlbRNnotes. It
is a memo field in the tblRNnotes (as the RN may want to edit and extend the
canned note) but the canned note is a 255 txt field.

Thanks for your patients and help. Robert


Dim db As DAO.Database
Dim strSQL As String
Dim varItm As Variant

Set db = CurrentDb()
With Forms!frmRNnotes!lstRNnotesLU
For Each varItm In .ItemsSelected
strSQL = "INSERT INTO tblRNnotes (fldRNnotes, fldVisitNo) " & _
"VALUES (" & Chr(34) & .ItemData(varItm) & Chr(34) "," & _
Me.fldVisitNo & ");"
db.Execute strSQL, dbFailOnError
Next varItm
End With

 

[John W. Vinson]

I'm still getting a syntax error on the sql statement. fldVisitNo is a number
fld. You are correct that I want the txt I want inserted into tlbRNnotes. It
is a memo field in the tblRNnotes (as the RN may want to edit and extend the
canned note) but the canned note is a 255 txt field.

Please post the actual value of strSQL from the Immediate window when
you step through the code, and the error message that you're getting.
It may help to post the names and datatypes of the fields in
tblRNnotes.

Thanks for your patients and help. Robert

You've got the patients... I hope I'm still keeping my patience! <g>

John W. Vinson [MVP]

 

[Guest]

I don't see anything in the immediate window. when I try to step thru it, it
stops and highlights the entire sql statement. The error msg is Complile
Error Syntax Error.

Here's the table and structure of tblRNnotes. tblRNnotesLU's (the look up
table) note field is 255 characters. Thanks for your help. Hope I'm not
driving you too crazy. thanks, Rob



Name Type Size
fldRNnotesNo Long Integer 4
fldVisitNo Long Integer 4
fldTime Date/Time 8
fldRNnotes Memo

 

[John W. Vinson]

I don't see anything in the immediate window. when I try to step thru it, it
stops and highlights the entire sql statement. The error msg is Complile
Error Syntax Error.

aha... sounds like the syntax error is in the VBA code, not in the
Query. Try getting into the VBA editor and select Debug... Compile
<your database>. Does it highlight this line?

Doublechecking: is the code in fact

Set db = CurrentDb()
With Forms!frmRNnotes!lstRNnotesLU
For Each varItm In .ItemsSelected
strSQL = "INSERT INTO tblRNnotes (fldRNnotes, fldVisitNo) " & _
"VALUES (" & Chr(34) & .ItemData(varItm) & Chr(34) "," & _
Me.fldVisitNo & ");"
db.Execute strSQL, dbFailOnError
Next varItm
End With

and do you have db dim'd as DAO.Database, and strSQL dim'd as String?
Do you have Option Explicit set (at the very top of the form's
module)? if not - set it... and Dim all your variables appropriately!

Perhaps you could post the entire event code for this event.

John W. Vinson [MVP]

 

[Guest]

John, I actually went a different direction and it is working exactly the way
I want it. I've used this method before with paired list box's and it is
simple and works great. I'm posting the entire module of code. It be
interested in you feed back. Thanks so much for your help and effort. I
appreciate you and all the MVP's more that I can demonstrate in words.
Without you guys I'd be totally f'd. Thanks again John, Robert
***************************************************
'---------------------------------------------------------------------------------------
' Module : Form_frmRNnotes
' DateTime : 2/4/2007 15:52
' Author : Rob
' Purpose : RN notes for
'---------------------------------------------------------------------------------------
Option Compare Database
Option Explicit

Private Sub cmdRNnotesEdit_Click()
On Error GoTo cmdRNnotesEdit_Click_Error

Me.Form.AllowEdits = True
Me.lstRNnotesLU.Locked = False
Me.fsubRNnotes.Locked = False
DoCmd.GoToRecord , , acNewRec

On Error GoTo 0
Exit Sub
***************************************
cmdRNnotesEdit_Click_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
cmdRNnotesEdit_Click of VBA Document Form_frmRNnotes"
End Sub


***************************************


Private Sub Form_AfterInsert()
Me.Requery
End Sub

Private Sub Form_AfterUpdate()
Me.Refresh
DoCmd.GoToRecord , , acNewRec
End Sub
***************************************
Private Sub Form_Current()
Me.Refresh
DoCmd.GoToRecord , , acNewRec
End Sub
***************************************
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Form_Open_Error


Me.Form.AllowEdits = False
Me.lstRNnotesLU.Locked = True
Me.fsubRNnotes.Locked = True

On Error GoTo 0
Exit Sub

Form_Open_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
Form_Open of VBA Document Form_frmRNnotes"
End Sub
***************************************
Private Sub cmdRNnotesClose_Click()
On Error GoTo Err_cmdRNnotesClose_Click


DoCmd.Close

Exit_cmdRNnotesClose_Click:
Exit Sub

Err_cmdRNnotesClose_Click:
MsgBox Err.Description
Resume Exit_cmdRNnotesClose_Click

End Sub
***************************************
Private Sub lstRNnotesLU_AfterUpdate()
'DoCmd.GoToRecord , , acNewRec
End Sub
***************************************
Private Sub lstRNnotesLU_DblClick(Cancel As Integer)

Me.Refresh

lstRNnotesLU_DblClick_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
lstRNnotesLU_DblClick of VBA Document Form_frmRNnotes"

End Sub

***************************************