Delete all instances of a records in table based on value in list

[Guest]

Example: Have a listbox populated full of persons names such as John, Jim,
Mary, Sally etc. from a table. for example how would on an event button for
say if Jim was highlighted in the textbox Del every record in the table with
Jims Name. The table name is called TNames, and field1=Persons Name field
2= job field 3= salary, and etc. The listbox is bound to Persons name
however the display form is unbound.

Thanks

 

[John Vinson]

Example: Have a listbox populated full of persons names such as John, Jim,
Mary, Sally etc. from a table. for example how would on an event button for
say if Jim was highlighted in the textbox Del every record in the table with
Jims Name. The table name is called TNames, and field1=Persons Name field
2= job field 3= salary, and etc. The listbox is bound to Persons name
however the display form is unbound.

Thanks

I hope you have more than just first names - or even full names - in
the listbox. Surely you could have Jim Smith and Jim Jenkins - or even
two people both named Jim Jones? I once worked with Dr. Lawrence David
Wise, Ph.D. and his colleague, Dr. Lawrence David Wise, Ph.D.

That said: a Query

DELETE * FROM Tnames
WHERE [Persons Name] = [Forms]![YourFormName]![ListBoxName];

could be executed from the Click event of a button, or even the
AfterUpdate event of the listbox.

John W. Vinson[MVP]

 

[Guest]

Sorry, Yep I do have them as you suggested, I probebly should have added them
to my post. If I had 2 Bob's such as bob smith , bob jones. then I would wipe
them both out. I am only on page 235 of Beginning Access VBA by Denise
Gosnell. So I was kind of just wanting a general example. I probebly
guessing instead of a Listbox it could be a text box cbobox.

Question: Since Im in this learning VBA phase. If I had 2 tables linked.
table 1 = Customers and table 2 = Customer transactions. If I selected Bob
Jones in your example and there was customer transactions, I set a msgbox "
warning there is customer transactions for [Listbox, combo, txt] you want to
procede to delete". Primary key customerID set autonumber. Table Customer
transactions linking by customerid set to number. I know one example can
complicate into another. I bet using the IF statement. However I am guising
there is a procedure that goes out and checks the customer transaction and
searches for customerID, if a match is returned then the msg box pops up.
then if you precede then it would delete all records matching the customerid
in customer transactions and also the Bob Jones record in customers. Am I
way off base and hopeless are am I actually learning VBA.

Thanks John

Example: Have a listbox populated full of persons names such as John, Jim,
Mary, Sally etc. from a table. for example how would on an event button for
say if Jim was highlighted in the textbox Del every record in the table with
Jims Name. The table name is called TNames, and field1=Persons Name field
2= job field 3= salary, and etc. The listbox is bound to Persons name
however the display form is unbound.

Thanks

I hope you have more than just first names - or even full names - in
the listbox. Surely you could have Jim Smith and Jim Jenkins - or even
two people both named Jim Jones? I once worked with Dr. Lawrence David
Wise, Ph.D. and his colleague, Dr. Lawrence David Wise, Ph.D.

That said: a Query

DELETE * FROM Tnames
WHERE [Persons Name] = [Forms]![YourFormName]![ListBoxName];

could be executed from the Click event of a button, or even the
AfterUpdate event of the listbox.

John W. Vinson[MVP]

 

[John Vinson]

Sorry, Yep I do have them as you suggested, I probebly should have added them
to my post. If I had 2 Bob's such as bob smith , bob jones. then I would wipe
them both out.

If you're dealing with a table of People, then you really must use
something - typically a numeric PersonID - as a Primary Key. Duplicate
names are NOT necessarily duplicate people!

I am only on page 235 of Beginning Access VBA by Denise
Gosnell. So I was kind of just wanting a general example. I probebly
guessing instead of a Listbox it could be a text box cbobox.

A listbox, a textbox, a combo box - they'll all let you do the same
thing. They differ in appearance and user interaction, but not in the
way they would be used (for this purpose).

Question: Since Im in this learning VBA phase. If I had 2 tables linked.
table 1 = Customers and table 2 = Customer transactions. If I selected Bob
Jones in your example and there was customer transactions, I set a msgbox "
warning there is customer transactions for [Listbox, combo, txt] you want to
procede to delete". Primary key customerID set autonumber. Table Customer
transactions linking by customerid set to number. I know one example can
complicate into another. I bet using the IF statement. However I am guising
there is a procedure that goes out and checks the customer transaction and
searches for customerID, if a match is returned then the msg box pops up.
then if you precede then it would delete all records matching the customerid
in customer transactions and also the Bob Jones record in customers. Am I
way off base and hopeless are am I actually learning VBA.

I'm sorry, this has me baffled. I simply do not know what you are
asking. You made a number of statements but you have not asked a
question.

If you have an enforced Relationship between the tables, then you will
get an error when you attempt to delete the Customer record. You can
use the Form's Error event to trap this error. Or, you could use
DLookUp in the click event of the Delete command button you're
creating to see if that employeeID exists in the transaction table.
Or, you could set Cascade Deletes on the relationship, and it will
delete the customer record and all related transactions - probably NOT
what you want to happen!

John W. Vinson[MVP]