Compile Error: "Method or data member not found"

[yator]

I am trying to use a List Box ("LstDefs")to add a record to a subform. The
List Box is on the Main Form.

I receive a Compile Error: "Method or data member not found" at Me.EventNo ?
If I eliminate the EventNo field I receive the error at Me.LstDefs.Columns.

Private Sub cmdAdd_Selected_Click()
On Error GoTo Err_cmdAdd_Selected_Click
Dim varItem As Variant
Dim strSQL As String

For Each varItem In Me.LstDefs.ItemsSelected

strSQL = " INSERT INTO tbl_IDQ_event_defs (EventNo, DefNo) " _
& "VALUES (" & Me.EventNo & "," _
& Me.LstDefs.Columns(0, varItem) & ")"
CurrentDb.Execute strSQL, dbFailOnError
Next
Me.frmHAI_sub_event_defs.Form.Requery

Exit_cmdAdd_Selected_Click:
Exit Sub

Err_cmdAdd_Selected_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Selected_Click

End Sub

 

[J_Goddard via AccessMonster.com]

Try using me![EventNo] and Me![LstDefs].Columns(0, varItem) to force a
reference to the form controls. The statement seems OK otherwise.

John

 

[Dirk Goldgar]

I am trying to use a List Box ("LstDefs")to add a record to a subform. The
List Box is on the Main Form.

I receive a Compile Error: "Method or data member not found" at Me.EventNo
?
If I eliminate the EventNo field I receive the error at
Me.LstDefs.Columns.

Private Sub cmdAdd_Selected_Click()
On Error GoTo Err_cmdAdd_Selected_Click
Dim varItem As Variant
Dim strSQL As String

For Each varItem In Me.LstDefs.ItemsSelected

strSQL = " INSERT INTO tbl_IDQ_event_defs (EventNo, DefNo) " _
& "VALUES (" & Me.EventNo & "," _
& Me.LstDefs.Columns(0, varItem) & ")"
CurrentDb.Execute strSQL, dbFailOnError
Next
Me.frmHAI_sub_event_defs.Form.Requery

Exit_cmdAdd_Selected_Click:
Exit Sub

Err_cmdAdd_Selected_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Selected_Click

End Sub

Double-check the name of the field/control (on the form) that you're calling
"EventNo". In addition, there is no Columns property of a list box -- the
name of the property is "Column", so your reference should be

Me.LstDefs.Column(0, varItem)

 

[yator]

I checked the control names, they are ok. I tried:
& "VALUES (" & Me![EventNo] & "," _

With this syntax, I receive the compile error at the ampersand before the
Me![EventNo], instead of at EventNo.

thanks

Try using me![EventNo] and Me![LstDefs].Columns(0, varItem) to force a
reference to the form controls. The statement seems OK otherwise.

John

I am trying to use a List Box ("LstDefs")to add a record to a subform. The
List Box is on the Main Form.

I receive a Compile Error: "Method or data member not found" at Me.EventNo ?
If I eliminate the EventNo field I receive the error at Me.LstDefs.Columns.

Private Sub cmdAdd_Selected_Click()
On Error GoTo Err_cmdAdd_Selected_Click
Dim varItem As Variant
Dim strSQL As String

For Each varItem In Me.LstDefs.ItemsSelected

strSQL = " INSERT INTO tbl_IDQ_event_defs (EventNo, DefNo) " _
& "VALUES (" & Me.EventNo & "," _
& Me.LstDefs.Columns(0, varItem) & ")"
CurrentDb.Execute strSQL, dbFailOnError
Next
Me.frmHAI_sub_event_defs.Form.Requery

Exit_cmdAdd_Selected_Click:
Exit Sub

Err_cmdAdd_Selected_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Selected_Click

End Sub

 

[Dirk Goldgar]

I checked the control names, they are ok. I tried:
& "VALUES (" & Me![EventNo] & "," _

With this syntax, I receive the compile error at the ampersand before the
Me![EventNo], instead of at EventNo.

Well, you've got me both stumped and curious. If you'd like to send me a
cut-down copy of your database, containing only the elements necessary to
demonstrate the problem, compacted and then zipped to less than 1MB in size
(preferably much smaller) -- I'll have a look at it, time permitting. You
can send it to the address derived by removing NO SPAM and ".invalid" from
the reply address of this message. If that address isn't visible to you,
you can get my address from my web site, which is listed in my sig. Do
*not* post my real address in the newsgroup -- I don't want to be buried in
spam and viruses.

 

[yator]

I appreciate the offer, will trim her down and send.
thanks!!

I checked the control names, they are ok. I tried:
& "VALUES (" & Me![EventNo] & "," _

With this syntax, I receive the compile error at the ampersand before the
Me![EventNo], instead of at EventNo.

Well, you've got me both stumped and curious. If you'd like to send me a
cut-down copy of your database, containing only the elements necessary to
demonstrate the problem, compacted and then zipped to less than 1MB in size
(preferably much smaller) -- I'll have a look at it, time permitting. You
can send it to the address derived by removing NO SPAM and ".invalid" from
the reply address of this message. If that address isn't visible to you,
you can get my address from my web site, which is listed in my sig. Do
*not* post my real address in the newsgroup -- I don't want to be buried in
spam and viruses.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

I appreciate the offer, will trim her down and send.

I checked the control names, they are ok. I tried:
& "VALUES (" & Me![EventNo] & "," _

With this syntax, I receive the compile error at the ampersand before
the
Me![EventNo], instead of at EventNo.

I've found the problem. The error message -- or, rather, the code element
it pointed to -- was misleading. The problem was not with this:

& "VALUES (" & Me![EventNo] & "," _

but with this:

& Me.lstDefs.Columns(0, varItem) & ")"

Not only is there no "Columns" property of a multiselect list box (as I
mentioned before), but there is no control on your form named "lstDefs" at
all! You have list boxes named "Level1", "Level2", and "Level3", but
nothing named "lstDefs". This seems to have confused Access so thoroughly
that it couldn't figure out where the error was coming from.

At the point in your code where the error occurs, you are looping through
the selected items in the list box named "Level2", so I guess that your
intention is to add the selected items from that list. If I change your
code to this:

strSQL = " INSERT INTO tbl_IDQ_event_defs (EventNo, DefNo) " _
& "VALUES (" & Me.EventNo & "," _
& Me.Level2.Column(0, varItem) & ")"

.... or, more simply, to this:

strSQL = " INSERT INTO tbl_IDQ_event_defs (EventNo, DefNo) " _
& "VALUES (" & Me.EventNo & "," _
& Me.Level2.ItemData(varItem) & ")"

.... then the code compiles without error and does what I expect.

Since you have three list boxes on the form, maybe you originally had this
code in a general-purpose function that would be passed the specific list
box to be processed as an argument named "lstDefs". That would make sense,
and would explain the genesis of the error.

Incidentally, the list boxes "Level2" and "Level3" have their Multiselect
properties set to "None", so you can't currently select more than one value
in them. I assume you intend to change that.