Code Help

[RobUCSD]

The following code was plucked from a previous post. I'm trying to implement
it in my app, but I'm very much the rookie coder and could use some help.
What I'm trying to do is create a record in a table when the user dblClks on
an item in my list box. With the code below I get a "variable not defined"
msg on the OpenRecordset. I'm not sure why. Additionally, when the record is
inserted into the table, I need it to add the current fldVisitNo into the
table along with the text (or fldRNnotesLuNo). Finally, my list is not a
multi-selection list, and I think this code is for a mult-selection lst. Any
help would be greatly appreciated.

Thanks,
Rob
Dim db As Database
Dim frm As Form, ctl As Control, tbl As TableDef
Dim varItm As Variant
Dim rst As Recordset

Set db = CurrentDb()
Set rst = db(OpenRecordset, "tblRNnotes", dbOpenDnaset)
Set frm = Forms!frmqryRNnotes
Set ctl = frm!lstRNnotesLU

For Each varItm In ctl.ItemsSelected
With rst
..AddNew
!gl_code = ctl.ItemsSelected(varItm)
..Update
End With
Next varItm

rst.Close

Set db = Nothing
Set rst = Nothing
Set frm = Nothing
Set ctl = Nothing
End Sub

 

[KLeBrun]

The following code was plucked from a previous post. I'm trying to implement
it in my app, but I'm very much the rookie coder and could use some help.
What I'm trying to do is create a record in a table when the user dblClks on
an item in my list box. With the code below I get a "variable not defined"
msg on the OpenRecordset. I'm not sure why. Additionally, when the record is
inserted into the table, I need it to add the current fldVisitNo into the
table along with the text (or fldRNnotesLuNo). Finally, my list is not a
multi-selection list, and I think this code is for a mult-selection lst. Any
help would be greatly appreciated.

Thanks,
Rob
Dim db As Database
Dim frm As Form, ctl As Control, tbl As TableDef
Dim varItm As Variant
Dim rst As Recordset

Set db = CurrentDb()
Set rst = db(OpenRecordset, "tblRNnotes", dbOpenDnaset)
Set frm = Forms!frmqryRNnotes
Set ctl = frm!lstRNnotesLU

For Each varItm In ctl.ItemsSelected
With rst
.AddNew
!gl_code = ctl.ItemsSelected(varItm)
.Update
End With
Next varItm

rst.Close

Set db = Nothing
Set rst = Nothing
Set frm = Nothing
Set ctl = Nothing
End Sub

One suggestion
change Set rst = db(OpenRecordset, "tblRNnotes", dbOpenDnaset)
to
set rst = db.openrecordset("tblRNnotes", dbOpenDnaset)

 

[RobUCSD]

Thanks for your help. I tried your suggestion and I get the same error msg,
but now in the dbOpenDnaset. What do you thinks? Thanks , Rob

 

[Douglas J. Steele]

Typo. It should be dbOpenDynaset, although you should be able to get by
using just

set rst = db.openrecordset("tblRNnotes")

 

[RobUCSD]

You know what, I'm completlely f'd up here. I'm not sure if I'm using the
most effecient proccess here. What I want to do is this:
When user double clicks a single item in lstRNnotesLU I want that to create
a record in tblRNnotes for the current visit (fldVisitNo). In my tblRNnotes I
have the following fields, fldRNnotesNo(uniqueID auto#), fldVisitNo (indexed,
duplicates Y, this identifies which visit the notes are being connected to.),
fldTime, fldRNnotes(txt)

lstRNnotesLU has the following fields: fldRNnotesLUno, fldRNnotes

Your help would be greatly appreciated due to the fact that I'm clueless.

Thanks, Rob

 

[Douglas J. Steele]

That looks as good a way as any. Usually I'd run an INSERT INTO query, but
since you'd be having multiple queries to run, it may not be any better.

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

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

 

[RobUCSD]

Doug, thanks for this. A few things I need to change due to previous typos on
my part. Here's what it looks like now. I'm getting an error msg; Error0 ()
in procedure lstRNnotes_DblClick of VBA Form_frmRNnotes. Can you tell what's
wrong?

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 (fldRNnotesLU) " & _
"VALUES('" & .ItemsSelected(varItm)
db.Execute strSQL, dbFailOnError
Next varItm
End With
*************************************************************

 

[Douglas J. Steele]

You'd need to show the entire routine, but typically that error happens if
you've put error handling in your routine, and forget to exit the routine
when the program executes normally (Error 0 means "no error")

Error Handling typically takes the form of:

On Error GoTo EH

' your code

GetOut:
' Put any clean-up that needs to be done
' whether or not there's an error here
Exit Sub

EH:
MsgBox Err.Number & ": " & Err.Description
Resume GetOut

End Sub

 

[RobUCSD]

I took out the error handling and will put it back once I get this figured
out. I now get the error msg, "Run-time Error '2480' you reffered to a
property by a numeric argument that isn't one of the property numbers in the
collection."

Debug.Print gave me this; db nothing, strSql nothing, VarItem Emptly.
*******************************************************
Private Sub lstRNnotesLU_DblClick(Cancel As Integer)
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 (fldRNnotesLUno) " & _
"VALUES('" & .ItemsSelected(varItm)
db.Execute strSQL, dbFailOnError
Next varItm
End With

Debug.Print
End Sub
**********************************************************

 

[RobUCSD]

Doug, if you're still there, could you let me know? Thanks, Rob

 

[Douglas J. Steele]

Sorry about that.

"VALUES('" & .ItemsSelected(varItm)

should be

"VALUES('" & .ItemData(varItm)