'Delete' from ListBox command button

S

Spydii

Hello
I’m totally stuck! I’m using the code below to populate a list box
(Command36) & then to allow deletions from the list box (Command39). The
Command36 button works fine but when I click on an item in my list box & then
press the Command39 button I get this error message - ‘Compile Error: Method
or data member not found’ and the ‘.ContactID’ is highlighted.

Private Sub Command36_Click()
Dim MySet As Recordset

Set MySet = CurrentDb().OpenRecordset("tbl C")

If Me.Dirty Then Me.Dirty = False

MySet.AddNew
MySet![ContactID] = [ContactID]
MySet![Course Title] = Combo34.Column(0)
MySet![Location] = Combo34.Column(1)
MySet![Date] = Combo34.Column(2)
MySet![Time] = Combo34.Column(3)
MySet![Room] = Combo34.Column(4)
MySet![Developer] = Combo34.Column(5)
MySet![CourseID] = Combo34.Column(6)
MySet.Update

MySet.Close
List32.Requery
Combo34 = Null
Combo34.Requery
End Sub

Private Sub Command39_Click()
Dim MySet As Recordset, varItem As Variant

For Each varItem In List32.ItemsSelected
Set MySet = CurrentDb.OpenRecordset("tbl C", dbOpenDynaset)
MySet.FindFirst "[ContactID] = " & Me.ContactID & "And [Course Title] = '"
& List32.Column(0) & "'"
MySet.Delete
MySet.Close
Next varItem

List32.Requery
End Sub

Unfortunately, I don’t actually understand the code as I’ve somehow managed
to create two databases successfully by altering all sorts of code found in
the discussion pages by trial & error, hence my lack of understanding of this
particular problem. I’m even more confused because I use the code below to
populate and delete from another list box on another form and this works fine.
Please let me know if I need to provide anymore information; as I don’t
understand the code or what’s gone wrong I didn’t really know what to include
other than the code.

Thanks in advance for any help.


Private Sub Command131_Click()
Dim MySet As Recordset

Set MySet = CurrentDb().OpenRecordset("tbl PROJECT DETAILS")

If Me.Dirty Then Me.Dirty = False

MySet.AddNew
MySet![CompanyID] = [CompanyID]
MySet![Project Name] = Combo125.Column(0)
MySet![ProjectID] = Combo125.Column(1)
MySet![ERDF Date of Registration] = Text129
MySet![Company Status] = Combo133.Column(0)
MySet.Update

MySet.Close
List127.Requery
Combo125 = Null
Combo125.Requery
Combo133 = Null
Combo133.Requery

End Sub
Private Sub Command132_Click()
Dim MySet As Recordset, varItem As Variant

For Each varItem In List127.ItemsSelected
Set MySet = CurrentDb.OpenRecordset("tbl PROJECT DETAILS", dbOpenDynaset)
MySet.FindFirst "[CompanyID] = " & Me.CompanyID & "And [Project Name] = '"
& List127.Column(0) & "'"
MySet.Delete
MySet.Close
Next varItem

List127.Requery

End Sub
 
O

OldPro

Hello
I'm totally stuck! I'm using the code below to populate a list box
(Command36) & then to allow deletions from the list box (Command39). The
Command36 button works fine but when I click on an item in my list box & then
press the Command39 button I get this error message - 'Compile Error: Method
or data member not found' and the '.ContactID' is highlighted.

Private Sub Command36_Click()
Dim MySet As Recordset

Set MySet = CurrentDb().OpenRecordset("tbl C")

If Me.Dirty Then Me.Dirty = False

MySet.AddNew
MySet![ContactID] = [ContactID]
MySet![Course Title] = Combo34.Column(0)
MySet![Location] = Combo34.Column(1)
MySet![Date] = Combo34.Column(2)
MySet![Time] = Combo34.Column(3)
MySet![Room] = Combo34.Column(4)
MySet![Developer] = Combo34.Column(5)
MySet![CourseID] = Combo34.Column(6)
MySet.Update

MySet.Close
List32.Requery
Combo34 = Null
Combo34.Requery
End Sub

Private Sub Command39_Click()
Dim MySet As Recordset, varItem As Variant

For Each varItem In List32.ItemsSelected
Set MySet = CurrentDb.OpenRecordset("tbl C", dbOpenDynaset)
MySet.FindFirst "[ContactID] = " & Me.ContactID & "And [Course Title] = '"
& List32.Column(0) & "'"
MySet.Delete
MySet.Close
Next varItem

List32.Requery
End Sub

Unfortunately, I don't actually understand the code as I've somehow managed
to create two databases successfully by altering all sorts of code found in
the discussion pages by trial & error, hence my lack of understanding of this
particular problem. I'm even more confused because I use the code below to
populate and delete from another list box on another form and this works fine.
Please let me know if I need to provide anymore information; as I don't
understand the code or what's gone wrong I didn't really know what to include
other than the code.

Thanks in advance for any help.

Private Sub Command131_Click()
Dim MySet As Recordset

Set MySet = CurrentDb().OpenRecordset("tbl PROJECT DETAILS")

If Me.Dirty Then Me.Dirty = False

MySet.AddNew
MySet![CompanyID] = [CompanyID]
MySet![Project Name] = Combo125.Column(0)
MySet![ProjectID] = Combo125.Column(1)
MySet![ERDF Date of Registration] = Text129
MySet![Company Status] = Combo133.Column(0)
MySet.Update

MySet.Close
List127.Requery
Combo125 = Null
Combo125.Requery
Combo133 = Null
Combo133.Requery

End Sub
Private Sub Command132_Click()
Dim MySet As Recordset, varItem As Variant

For Each varItem In List127.ItemsSelected
Set MySet = CurrentDb.OpenRecordset("tbl PROJECT DETAILS", dbOpenDynaset)
MySet.FindFirst "[CompanyID] = " & Me.CompanyID & "And [Project Name] = '"
& List127.Column(0) & "'"
MySet.Delete
MySet.Close
Next varItem

List127.Requery

End Sub
Click to expand...

if [ContactID] is of type String, then it needs to be surrounded by
single quotes.
To get the query string: [ContactID] = 'A213'
You would need to write: "[ContactID] = ' " & Me.ContactID & " ' "
Also: there needs to be a space before the AND: " AND" not "AND".
 
S

Spydii

It's not of type String & I tried the space before the And but that didn't
change anything :(


OldPro said:
Hello
I'm totally stuck! I'm using the code below to populate a list box
(Command36) & then to allow deletions from the list box (Command39). The
Command36 button works fine but when I click on an item in my list box & then
press the Command39 button I get this error message - 'Compile Error: Method
or data member not found' and the '.ContactID' is highlighted.

Private Sub Command36_Click()
Dim MySet As Recordset

Set MySet = CurrentDb().OpenRecordset("tbl C")

If Me.Dirty Then Me.Dirty = False

MySet.AddNew
MySet![ContactID] = [ContactID]
MySet![Course Title] = Combo34.Column(0)
MySet![Location] = Combo34.Column(1)
MySet![Date] = Combo34.Column(2)
MySet![Time] = Combo34.Column(3)
MySet![Room] = Combo34.Column(4)
MySet![Developer] = Combo34.Column(5)
MySet![CourseID] = Combo34.Column(6)
MySet.Update

MySet.Close
List32.Requery
Combo34 = Null
Combo34.Requery
End Sub

Private Sub Command39_Click()
Dim MySet As Recordset, varItem As Variant

For Each varItem In List32.ItemsSelected
Set MySet = CurrentDb.OpenRecordset("tbl C", dbOpenDynaset)
MySet.FindFirst "[ContactID] = " & Me.ContactID & "And [Course Title] = '"
& List32.Column(0) & "'"
MySet.Delete
MySet.Close
Next varItem

List32.Requery
End Sub

Unfortunately, I don't actually understand the code as I've somehow managed
to create two databases successfully by altering all sorts of code found in
the discussion pages by trial & error, hence my lack of understanding of this
particular problem. I'm even more confused because I use the code below to
populate and delete from another list box on another form and this works fine.
Please let me know if I need to provide anymore information; as I don't
understand the code or what's gone wrong I didn't really know what to include
other than the code.

Thanks in advance for any help.

Private Sub Command131_Click()
Dim MySet As Recordset

Set MySet = CurrentDb().OpenRecordset("tbl PROJECT DETAILS")

If Me.Dirty Then Me.Dirty = False

MySet.AddNew
MySet![CompanyID] = [CompanyID]
MySet![Project Name] = Combo125.Column(0)
MySet![ProjectID] = Combo125.Column(1)
MySet![ERDF Date of Registration] = Text129
MySet![Company Status] = Combo133.Column(0)
MySet.Update

MySet.Close
List127.Requery
Combo125 = Null
Combo125.Requery
Combo133 = Null
Combo133.Requery

End Sub
Private Sub Command132_Click()
Dim MySet As Recordset, varItem As Variant

For Each varItem In List127.ItemsSelected
Set MySet = CurrentDb.OpenRecordset("tbl PROJECT DETAILS", dbOpenDynaset)
MySet.FindFirst "[CompanyID] = " & Me.CompanyID & "And [Project Name] = '"
& List127.Column(0) & "'"
MySet.Delete
MySet.Close
Next varItem

List127.Requery

End Sub
Click to expand...

if [ContactID] is of type String, then it needs to be surrounded by
single quotes.
To get the query string: [ContactID] = 'A213'
You would need to write: "[ContactID] = ' " & Me.ContactID & " ' "
Also: there needs to be a space before the AND: " AND" not "AND".
Click to expand...
 
S

Spydii

Please ignore my last message! I just changed Me.ContactID to Me.[ContactID]
and it works. I'm still confused as there are no spaces in ContactID and it
wasn't required for CompanyID... but it works so I'm happy!
Thanks

OldPro said:
Hello
I'm totally stuck! I'm using the code below to populate a list box
(Command36) & then to allow deletions from the list box (Command39). The
Command36 button works fine but when I click on an item in my list box & then
press the Command39 button I get this error message - 'Compile Error: Method
or data member not found' and the '.ContactID' is highlighted.

Private Sub Command36_Click()
Dim MySet As Recordset

Set MySet = CurrentDb().OpenRecordset("tbl C")

If Me.Dirty Then Me.Dirty = False

MySet.AddNew
MySet![ContactID] = [ContactID]
MySet![Course Title] = Combo34.Column(0)
MySet![Location] = Combo34.Column(1)
MySet![Date] = Combo34.Column(2)
MySet![Time] = Combo34.Column(3)
MySet![Room] = Combo34.Column(4)
MySet![Developer] = Combo34.Column(5)
MySet![CourseID] = Combo34.Column(6)
MySet.Update

MySet.Close
List32.Requery
Combo34 = Null
Combo34.Requery
End Sub

Private Sub Command39_Click()
Dim MySet As Recordset, varItem As Variant

For Each varItem In List32.ItemsSelected
Set MySet = CurrentDb.OpenRecordset("tbl C", dbOpenDynaset)
MySet.FindFirst "[ContactID] = " & Me.ContactID & "And [Course Title] = '"
& List32.Column(0) & "'"
MySet.Delete
MySet.Close
Next varItem

List32.Requery
End Sub

Unfortunately, I don't actually understand the code as I've somehow managed
to create two databases successfully by altering all sorts of code found in
the discussion pages by trial & error, hence my lack of understanding of this
particular problem. I'm even more confused because I use the code below to
populate and delete from another list box on another form and this works fine.
Please let me know if I need to provide anymore information; as I don't
understand the code or what's gone wrong I didn't really know what to include
other than the code.

Thanks in advance for any help.

Private Sub Command131_Click()
Dim MySet As Recordset

Set MySet = CurrentDb().OpenRecordset("tbl PROJECT DETAILS")

If Me.Dirty Then Me.Dirty = False

MySet.AddNew
MySet![CompanyID] = [CompanyID]
MySet![Project Name] = Combo125.Column(0)
MySet![ProjectID] = Combo125.Column(1)
MySet![ERDF Date of Registration] = Text129
MySet![Company Status] = Combo133.Column(0)
MySet.Update

MySet.Close
List127.Requery
Combo125 = Null
Combo125.Requery
Combo133 = Null
Combo133.Requery

End Sub
Private Sub Command132_Click()
Dim MySet As Recordset, varItem As Variant

For Each varItem In List127.ItemsSelected
Set MySet = CurrentDb.OpenRecordset("tbl PROJECT DETAILS", dbOpenDynaset)
MySet.FindFirst "[CompanyID] = " & Me.CompanyID & "And [Project Name] = '"
& List127.Column(0) & "'"
MySet.Delete
MySet.Close
Next varItem

List127.Requery

End Sub
Click to expand...

if [ContactID] is of type String, then it needs to be surrounded by
single quotes.
To get the query string: [ContactID] = 'A213'
You would need to write: "[ContactID] = ' " & Me.ContactID & " ' "
Also: there needs to be a space before the AND: " AND" not "AND".
Click to expand...
 
O

OldPro

Please ignore my last message! I just changed Me.ContactID to Me.[ContactID]
and it works. I'm still confused as there are no spaces in ContactID and it
wasn't required for CompanyID... but it works so I'm happy!
Thanks



OldPro said:
Hello
I'm totally stuck! I'm using the code below to populate a list box
(Command36) & then to allow deletions from the list box (Command39). The
Command36 button works fine but when I click on an item in my list box & then
press the Command39 button I get this error message - 'Compile Error: Method
or data member not found' and the '.ContactID' is highlighted.
Private Sub Command36_Click()
Dim MySet As Recordset
Set MySet = CurrentDb().OpenRecordset("tbl C")
If Me.Dirty Then Me.Dirty = False
MySet.AddNew
MySet![ContactID] = [ContactID]
MySet![Course Title] = Combo34.Column(0)
MySet![Location] = Combo34.Column(1)
MySet![Date] = Combo34.Column(2)
MySet![Time] = Combo34.Column(3)
MySet![Room] = Combo34.Column(4)
MySet![Developer] = Combo34.Column(5)
MySet![CourseID] = Combo34.Column(6)
MySet.Update
MySet.Close
List32.Requery
Combo34 = Null
Combo34.Requery
End Sub
Private Sub Command39_Click()
Dim MySet As Recordset, varItem As Variant
For Each varItem In List32.ItemsSelected
Set MySet = CurrentDb.OpenRecordset("tbl C", dbOpenDynaset)
MySet.FindFirst "[ContactID] = " & Me.ContactID & "And [Course Title] = '"
& List32.Column(0) & "'"
MySet.Delete
MySet.Close
Next varItem
List32.Requery
End Sub
Unfortunately, I don't actually understand the code as I've somehow managed
to create two databases successfully by altering all sorts of code found in
the discussion pages by trial & error, hence my lack of understanding of this
particular problem. I'm even more confused because I use the code below to
populate and delete from another list box on another form and this works fine.
Please let me know if I need to provide anymore information; as I don't
understand the code or what's gone wrong I didn't really know what to include
other than the code.
Thanks in advance for any help.
Private Sub Command131_Click()
Dim MySet As Recordset
Set MySet = CurrentDb().OpenRecordset("tbl PROJECT DETAILS")
If Me.Dirty Then Me.Dirty = False
MySet.AddNew
MySet![CompanyID] = [CompanyID]
MySet![Project Name] = Combo125.Column(0)
MySet![ProjectID] = Combo125.Column(1)
MySet![ERDF Date of Registration] = Text129
MySet![Company Status] = Combo133.Column(0)
MySet.Update
MySet.Close
List127.Requery
Combo125 = Null
Combo125.Requery
Combo133 = Null
Combo133.Requery
End Sub
Private Sub Command132_Click()
Dim MySet As Recordset, varItem As Variant
For Each varItem In List127.ItemsSelected
Set MySet = CurrentDb.OpenRecordset("tbl PROJECT DETAILS", dbOpenDynaset)
MySet.FindFirst "[CompanyID] = " & Me.CompanyID & "And [Project Name] = '"
& List127.Column(0) & "'"
MySet.Delete
MySet.Close
Next varItem
List127.Requery
End Sub
Click to expand...
Click to expand...
if [ContactID] is of type String, then it needs to be surrounded by
single quotes.
To get the query string: [ContactID] = 'A213'
You would need to write: "[ContactID] = ' " & Me.ContactID & " ' "
Also: there needs to be a space before the AND: " AND" not "AND".- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

That doesn't make sense to me. Brackets usually distinguish an object
as a field in a table, not a textbox.
I have two suggestions for the future:
1) Don't name the textboxes with the identical name as the fields. I
know Access defaults to this, but if you preceed the field name with a
"txt", then it is obvious that it refers to a textbox, and not the
field.
2) Load filters or queries into a string variable first, and then stop
the code with a break point at that line. Use Ctrl-G to bring up a
debug window, then type ? sSQL (or whatever the SQL string is called)
to see how the string is actually formatted. Hilite it and copy it
memory with a Ctrl-Insert. Then create a new query based on the same
table. Go into SQL View and overwrite all or part of it (leave the
SELECT part of the clause, if this is just a WHERE clause) with the
string in memory by doing a Shift-Insert. If the query has a fault in
it, then the compiler will find and and hilight it when you try to run
it.
Perhaps that is why it started working... Me.CompanyID refered to the
textbox value, a string, while Me.[CompanyID] referred to the field in
the underlying table, a numeric value.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Delete record 10
Delete record on list box 10
Command Button 0
Use data from subform (List Box) on Form, via Command button? 3

Top