Combo box Update

[IGBT]

Hi all sorry if this is a minor/ stupid question but i have spent all week trying to sort this; I have 2 tables

TABLE A) List of parts (Part no / Description / Type)

TABLE B) Keeps a record of where all of these parts are sent to customers with serial numbers, details etc

Now, I have created a form so that records can be entered and I want to use a combobox so the user can pick the part no (linked to table A) and it automaticall fills in the description and type fields. When complete I need TABLE B updating

This is probably very simple especially as I cannot seem to find the answer in the "Dummies" book I have

Cheers

D

 

[fneily]

First, does a part group have the same serial number? Such as a hex nut. The same size hex nuts would have the same serial numbers. I'll assume so. Then you would have a many-to-many relationship. One part can go to many customers, and one customers can have many parts. Relational database do not like this. See:
Fundamentals of Relational Database Design

http://r937.com/relational.html

So, you would have three tables:
tblCustomer
CustomerID
Cust. Info Fields

tblPart
PartID
Part Infor Fields
Price

tblTransaction
TransID Primary Key
CustID Foreign Key
PartID Foreign Key
Quantity
Date
Any Other COMMON fields.

So now you have a one-to-many relationship between tblPart and tblTransaction and
one-to-many relationship between tblCustomer and tblTransaction.

These can be connected in a query and a form based on that query. Or a form/subform created between one of the one-to-many pairs.

 

[SoCalAccessPro]

Actually, most MRP systems I've seen that have a seriaized product have a serial number table.

tblSN
-----------
SN
PartID (This will get you to part info)
Job/Sales Order/Work Order (This will get you to customer)



Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[IGBT]

Thanks for the quick reply guys(or gals)!

The parts in question would be for instance a ford fiesta and the serial number would be the build serial number (for traceability)

I can't quite see the need for 3 tables. I thought I could have a table which literally listed all of the parts (Ford Fiesta, Ford Focus etc) and an overall master inventory table that would have in it the which parts went to which customer and what serial number. I guess each customer could have lots of the same part, but they would all have seperate serial numbers

I did think that maybe Microsoft produced a template that would do this or at least give me a kickstart

D

 

[fneily]

Open Access and close the first screen. Click on File - New and then the Database tab. You'll see an Inventory Control database. Double click on it and it'll start a wizard you can follow to create your database.

Not following your serial number explanation. I'm looking at my parts list for my Porsche 928 and I see a serial number, such as the one for the water pump, which goes into many 928's of a specified year. Not all car parts for every single car in the world has a different serial number.

By the way, a database that list all the parts of a Ford Fiesta, Ford Focus, Porsche 911, etc. is called an Hierarchical Database. Access is a Relational Database. Totally differenct concepts.

 

[IGBT]

Appologies fneily, ill try a better example

If I were producing DVD players each one would have a unique serial number. I am not interested in the internal parts just the player itself. I deliver various makes of these DVD players to various customers and I need to track Model and serial number against each customer

Anyway, This inventory control wizard, is it in Access 2007?

 

[fneily]

I don't have Access 2007, it should. Or you can just do out to Microsoft.com and they have a whole bunch of pre-made databases.

From your explanation, I have a computer that's a certain model and it has a serial number assigned to me. But a certain model can go to many customers. So, you'd still have three tables. The basic concepts of normalization still apply no matter what the physical objects are.

tblModel
ModelID
Model info fields
Price

tblCustomer
CustID
Cust info fields

tblSerialNumbers
SerialNumber Primary Key
ModelID
CustID
DateBought
Any Other COMMON fields

Again, tables can be connected through a query or form/subform.

 

[IGBT]

Thanks I understand now

Just out of curiosity the first form I created is stuck in Tab mode. How do you switch to making it floating with min max corner buttons

 

[fneily]

I don't have Access 2007. Not sure what you mean by Tab mode. Taking a guess, if you have a task bar, right click the form's name and select Restore.

 

[postermmxvicom]

How do you switch to making it floating with min max corner buttons

Change Pop up from No to Yes in the form's properties.

I don't have Access 2007. Not sure what you mean by Tab mode.

In Access 2007, everything shows up in tabs like in Firefox...no floating windows. No way to resize or move or position them in this mode AFAIK.

One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[IGBT]

"Change Pop up from No to Yes in the form's properties."

Couldnt find a property for the Form called this

Anyway managed to sort this after an hour of trawling the web. You have to dive into access options and under the current database area, change document window options to 'overlapping windows' instead of tabbed documents

Why do they have to mess. If it aint broke dont fix it!

D

 

[postermmxvicom]

Couldnt find a property for the Form called this...Anyway managed to sort this after an hour of trawling the web.

Glad you got it working, but I thought I'd point out where this property was anyhoo.

Bring up the Property sheet for the form. Make sure 'form' is selected in the selection type drop down box. And 'Pop Up' is under the 'other' tab.

One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?