Search Query in Form

Hi All,

I think this question is a little bit harder then my
previous questions.

I am making a computer inventory. One table has Hardware
such as motherboard, RAM etc. Another table has Software
such as Operating System etc. And finally I have a
Location and User Table.

I have set the computer name to be the primary key in
each table. I have linked the tables via the Computer
Field, all relationships are 1 to 1 with enforce
referencial integrity.

I want to make a form that has a space to type in the
name of the computer. Then hit enter and boom all the
three tables information is dispalyed. Hardware, Software
and Location all on the same page.

I would also like to have a form for adding computers
which will automatically put the typed information into
the Database when I click a Save button.

Thankyou
Eli

One thing to think about is that if your tables all have 1 to 1 referential integrity, then perhaps you only need 1 table!, however its very hard for designers to change designs so what you can do is have a query that links all the three tables and that is what you shall use when saving new entries to your inventory.

For searching, you have to code.

Use:

Set rst = OpenRecordset ("SELECT ... variable ... FROM tablenames WHERE ....")

where the variable is of a string type and shall contain the contents of a text box that the user has typed.

PS: It may seem overwhelming at first but its really a piece of cake...

On Sun, 30 May 2004 21:51:11 -0700, "Eli"
wrote:

Hi All,

I think this question is a little bit harder then my
previous questions.

I am making a computer inventory. One table has Hardware
such as motherboard, RAM etc. Another table has Software
such as Operating System etc. And finally I have a
Location and User Table.

I have set the computer name to be the primary key in
each table. I have linked the tables via the Computer
Field, all relationships are 1 to 1 with enforce
referencial integrity.

It REALLY sounds like your table structure needs reconsideration! Are
you using a *field* for Motherboard, a field for RAM, etc. etc.?
Remember - "fields are expensive, records are cheap". You may well
want to consider a "tall thin" structure with a many to many
relationship; you'ld have a table identifying the PC, a table of all
the types of hardware that might or might not be installed in any PC,
and a third table linking these. Rather than a *field* for
Motherboard, you'ld have a record in the Hardware table with (e.g.)

HardwareID: autonumber
Type: "Motherboard"
Vendor: "ASUS"
Model: ... etc

and a HardwareInstalled table with fields for the PC's unique ID, the
HardwareID, and perhaps fields for serial number and size (the latter
used for memory and disk, for example).

This technique is even more appropriate for software. You certainly do
not want fields named [Access], [Word], [Solitare], ...!

I want to make a form that has a space to type in the
name of the computer. Then hit enter and boom all the
three tables information is dispalyed. Hardware, Software
and Location all on the same page.

A Form for the PC table with Subforms for the other tables will do
this (with either your current design or with my suggested change).

I would also like to have a form for adding computers
which will automatically put the typed information into
the Database when I click a Save button.

Access Forms provide this capability right out of the box. No Save
button is needed (though you can add one if you wish) - just enter the
data, and move to a new record and it's saved.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public