New Entry Form, Pull from Multiple Tables, Populate record table.

[schase]

I searched and read and tried over a number of days. Could never get it to work. Here in detail is what I have, I use Access 97.

I have one Form called NewHondas. This form I use to enter our new vehicles that enter our Lot. I have 5 tables with information, and one table for inventory. The information tables are:

table - Color - Columns used: ModelID, ColorName
table - Model - Columns used: ModelID, ModelName
table - ModelNumber - Columns used: ModelID, TrimLevel, ModelNumber, ModelYear, Trim, Invoice, MSRP, Logo, Transmission.
table - Picture - Columns used: ModelID, ModelName, Color, Picture.
table - Transmission - Columns used: TransID, Transmission.

My table name to store inventory: NewHondas

What I desire, is to put in ModelNumber - For example CG1652JNW - That then pulls from individual tables that it is an Accord Sedan, EX-VL (trim), Trim level G, it is a 2002, Automatic. - and puts all this information onto our NewHondas table. - by itself, not needing each individual field to be selected.

Then I need to select color using a regular combo box - and hopefully have the picture take the model name and color and match it to the picture.

All of the after event code's I have tried will only end up recording the modelnumber which I select from a pull down.

Any help greatly appreciated.

 

[gusbrunston]

Dear Schase:

Why not...

One table named "New Hondas"

   Key:  VinNumber       LongInteger
         ModelNumber     LongInteger
         ModelID         Text (?)
         ModelName       Text
         Color           LongInteger (?)
         ColorName       Text
         TrimLevel       Text (?)
         ModelYear       Date (yyyy)
         Trim            Text
         Invoice         Text
         MSRP            Currency
         Logo            Text (?)
         TransID         Text (?)
         Transmission    Text
         Picture         Ole Object
         Inventory       Yes/No

You could then create a query using the query wizard to populate your form with fields that will update the record for each car. Other queries could print inventory lists grouped by model year, etc.

Would seem easier to me than managing all those tables, to say nothing of "normalization"...

Best of luck,
Gus Brunston
An old PICKer
padregus@home.com

 

[gusbrunston]

actually...
the table should be named:

"tblNewHondas"

the form should be named:

"frmNewHondas"

Gus Brunston
An old PICKer
padregus@home.com

 

[schase]

Hi Gus, thank you for taking the time to respond. I think I understand what you are saying, the reason I have so many tables is each model number could have 5 or 6 colors available for that model. I could easily place in the same table the transmission and model name. And i could incorporate the picture with the color table, which would leave me 2 tables with source information.

The reason I need an additional table to maintain inventory (we manually add stock and vin numbers) is this table we put up on the web for our customers to view our current inventory. (link:

http://honda.elpasohonda.com/asp_pages/buyacar/ChooseyournewhondaII.asp

) Up to now I have been typing the inventory into the newhondas table - using combo boxes to pull vehicle model information from the various tables. The only reason I was trying to use a form to enter inventory is most of the posts here regarding similiar situations recommend or assume a form is used.


Does this make sense or am I talking in circles?

 

[schase]

Great suggestion on name changes, and have renamed

 

[gusbrunston]

Dear Schase:

Nice web site! I'm afraid I'm way behind you in publishing data on the web. Now how does that slogan go? "...I don't have the answer, but I certainly admire your question."

Maybe one of the gurus will pick up from here...

Best of luck.

(-: Gus Brunston
An old PICKer
padregus@home.com

 

[schase]

lol, Thanks for trying Gus. I didn't make the website, only have to maintain the database. Just trying to ease the workload and reduce human error in selecting the right information for that particular model number. Basically need to pull info from 2 tables based off of a selection and populate a 3rd table.

 

[TTThio]

Hi,

just a quick thought:

Make a query of Model and ModelID tables with join on the ModelID field.

Based the frmNewHonda on the tblNewHonda just like you do right now. Then, on the ModelNumber cbobox/ModelNumber txtBox (where you type the new data) AfterUpdate event, create a routine like:

Dim db as database (don't forget to set reference to DAO 3.6 Library)
Dim rs as recordset

Set db = currentdb
set rs = "SELECT field1, field2, etc... (whatever fields you want to retrieve) FROM NewQuery WHERE ModelNumber = '" & me.txtModelNumber & "'"

me.txt1 = rs("field1&quot
me.txt2 = rs("field2&quot etc...

As the form is based on the table, values as diplayed in the form's controls will be stored in the table. The autofill values are gained from the SQL statement.

Hopefully this will do.

 

[schase]

I'll pass that suggestion to my manager, thanks TTThio