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

On Thu, 31 Aug 2006 22:03:01 -0700, BrianPaul
<(E-Mail Removed)> wrote:

>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]

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

"John Vinson" wrote:

> On Thu, 31 Aug 2006 22:03:01 -0700, BrianPaul
> <(E-Mail Removed)> wrote:
>
> >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]
>

On Thu, 31 Aug 2006 23:19:01 -0700, BrianPaul
<(E-Mail Removed)> wrote:

>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]