Form for Searching Multiple Tables

  • Thread starter Thread starter Dalton
  • Start date Start date
D

Dalton

Im pretty new to designing forms and I think that would
be the best way to accomplish this task:

I have 5 different tables with contact information
(fname, lname, address, etc) and I need to have a form
that would allow me to easily remove a persons record
from the entire database. I would like to have a text
box where I can enter a last name and all possible
matches will come up. Then Id like to select the proper
record and have it deleted form the table where it
resides. A big plus would be to have a new DELETED table
that would store the deleted records once they are
removed from the active tables.

Can anyone talk me through this? Any help is appreciated!!
 
Im pretty new to designing forms and I think that would
be the best way to accomplish this task:

I have 5 different tables with contact information
(fname, lname, address, etc) and I need to have a form
that would allow me to easily remove a persons record
from the entire database.
Click to expand...

Why *FIVE TABLES*? As is often the case, the problem is not in the
query, the problem is in the structure of your tables!

If the tables all contain the same kind of contact information there
should be ONLY ONE TABLE, Contacts; with an additional field to
distinguish which of the five categories of contact this particular
person is.
I would like to have a text
box where I can enter a last name and all possible
matches will come up. Then Id like to select the proper
record and have it deleted form the table where it
resides. A big plus would be to have a new DELETED table
that would store the deleted records once they are
removed from the active tables.

Can anyone talk me through this? Any help is appreciated!!
Click to expand...

This can be done, but it will be big, clunky, and awkward. Better
would be a single table with *six* categories, one of them DELETED.
 
Dont you think that if I could have simplified this mess
into one table a long time ago I would? With the amount
of time I have alloted to spend on this project and the
resources I have to work with, I have to either search
one table at a time or figure out how to answer my
original problem.
Click to expand...

Thanks for the background. It was not obvious from the original post;
and my apologies for the Normalization Police lecture.

In this case, the VBA code solution will be required. You may want to
use an unbound Form and use code to run multiple DELETE queries. You
might have a Form with a textbox txtDeleteMe into which the user can
select the text string which identifies the record to be deleted
(since I don't know how your tables are structured, I can't say just
what that might be - but I'm sure you're aware that deleting by a
person's name is fraught with danger since you might delete Bill
Jones, the high-profile VIP customer, instead of that blankety-blank
Bill Jones who won't ever pay his bill) in a textbox txtDelete. You
could have five Delete queries stored, named del1 through del5; each
would be a Parameter query with one parameter selecting the record to
be deleted; the code could be

Dim iKill As Integer
Dim db As DAO.Database
Dim qd As DAO.Querydef
Set db = CurrentDb
For iKill = 1 to 5
Set qd = db.Querydefs("del" & iKill)
qd.Parameters(0) = Me!txtDelete
qd.Execute dbFailOnError
Set qd = Nothing
Next iKill

Add error handling, etc. as appropriate.
Unfortunately, we dont all work in that dreamworld that
Microsoft believes exists where SQL consultants and IT
wizards are flowing through all of the hallways. Many of
us work where we have to create databases, design
websites, create marketing plans, fire employees, clean
bathrooms and empty trashes.
Click to expand...

As a self-employed one-man shop, I'm aware that not everyone has such
advantages; today I've folded and stamped and mailed brochures, mopped
the floor, helped my wife bag produce for the Farmer's Market,
struggled with a client's monster query and even answered a few
newsgroup questions to the best of my ability.
 
Back
Top