Search Query in Form

  • Thread starter Thread starter Eli
  • Start date Start date
E

Eli

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..
 
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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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.
 
Back
Top