Selection Box Question :Need Help

[MrSmith]

So ive tried to follow a few posts for this and look at other peoples code , however i cant seem to find the right info and im sure it really easy.........

I have 2 Tables

tblPO - Contains Multiple Fields - POnumber,VendorName,Address,city,state,Zip
and various other fields that dont pertain to the problem.

tblVendor - Contains Fields - VendorID,VendorName,Address,City,State,Zip

I have 1 Form

FmPO

Contains All The tblPO Data.
The Forms VendorName combo box has the row/source type set as a table/query
and row source as Vendors.

This lets me have all the vendor names in the drop down box

Now what i want to do is when I select the combo box with a company name - the address /city/state/zip auto fill out....

Its been driving me mad ...... and im sure it proberbly easy....

Thanks For the help

 

[RuralGuy]

Let me start by asking a question:

tblPO - Contains Multiple Fields - POnumber,VendorName,Address,city,state,Zip
and various other fields that dont pertain to the problem.

tblVendor - Contains Fields - VendorID,VendorName,Address,City,State,Zip

Do these two tables contain identical data? If so then is it too late to do some redesign on the table structures? This is a normalization violation and will forever cause you grief until corrected.

 

[MrSmith]

the TblPO Address Boxes are empty - the only boxes that were previously populated were the PO table Vendor Name - which im having to re enter anyway as it now looks up its info from the tblvendor.

Redesigning either tables is not a problem as long as i can get it doing what i need to.

 

[RuralGuy]

Your tblVendor seems fine as long as the VendorID is an AutoNumber. Then the tblPO just needs the VendorID in it as a ForeignKey and everything from the tblVendor can easily be joined with a query. I would suggest that the tblPO table also have an AutoNumber field called POID since your POnumber field will almost certainly be a text field and probably be controled by the vendor. You need your own PrimaryKey in this table for use later on.

 

[MrSmith]

Ok,

So VendorID is the primary Key in the tblVendors

in tblPO i have added a field called POID as an autonumber and made it the primary Key


I have deleted the vendorname/address/state/zip etc fields from the PO table as they will be coming from the tblVendors.

Now to add the VendorsID as a foreign Key - never done that b4
Do I just add the name as a field in the tblPO ?
What do i put the data type as ?

thanks for your help

 

[RuralGuy]

Convention says the field name in tblPO is the same name we used for the field where it is a PrimaryKey and make it a Long Integer (same as an AutoNumber).

 

[RuralGuy]

If you havent done so, key in the correct VendorID number in each record of the tblPO. You have to get it started by hand. Now go to Tools>Relationships... and add the two tables and join them on the VendorID field. Access will make it a one to many relationship and check the "Enforce Referential Integrety. Save and close. Now create a query with both tables in it and bring down the fields you need from both tables. Save it and switch to DataSheet view and see what you have.

 

[MrSmith]

Ok I got it working - thanks

Now one more question -

I already laid out a Form exactly how i wanted it - Formatted etc took a while -

Anyhow is there a way to change where each field is looking for its data?
Ie at the moment its looking at tblPO
however now i want it to look at the same field name in QryPO

Thanks

 

[RuralGuy]

Change the RecordSource of the form. It's on the data tab of the properties sheet. You may need to go into some of the controls and check the ControlSource and see if it is set correctly. You are going to want to change the way the ComboBox works so it stores the VendorID and not the name. It will be easier to just add a new comboBox and let the wizard set it up.

 

[MrSmith]

Ok so I added in all the vendorIDs to the table.
which was fine however when i open up relationships i can join them but i cant give it referential integrity.

If I then create the query anyway and run it ,I get my List with all the addresses per PO, which seems great.....

Now If I then use the Form wizard or even the form i had and use the data from there everything comes across fine.....
I can scroll through my 140 records and see the Vendorname and the addresses.

however:

When I change the textbox of the VendorName to a combobox (I want a combobox so that when a new record is created the vendor can be easily chosen from the list)
It all goes wrong...

 

[RuralGuy]

What goes wrong? Can you post the zipped up db?

 

[RuralGuy]

I think I understand. Leave the VendorName textbox alone. The ComboBox needs to be bound to the VendorID field and not the VendorName field. Add the VendorID to the query so the ComboBox can display the VendorName but save the VendorID to the VendorID field. You probably don't event need the VendorName textbox any longer.

 

[MrSmith]

Ok Attached File - Maybe you can tell me whats wrong with it - thanks

 

[RuralGuy]

This will get you a little further along but you have lots of work to do. Here's a link to Rules of Data Normalization that should get you started. Google can locate many more sites on the subject. With relational databases think tall and slim and not short and wide. You need to break up that Vendors table. It also means your form needs changing. I'm not exactly sure what you are after and I'm not going to do the work for you but I suspect the bottom of the form needs to be a subform.

 

[RuralGuy]

I guess it would have helped if I had attached the db huh?

 

[RuralGuy]

I didn't mean to imply I wouldn't help with your project. It is just that you will need to do most of the work and some studying time is needed. Good luck with the project. Start a new thread when you get up to the next level and get stuck.

 

[MrSmith]

thankyou for the help -

So Let me get this right so i have a general understanding - all i needed to do with create a 2nd query from the vendors table and then reference that query for the combo box?


I have a general understanding of normilization etc -

Problem is id love to have more time to learn it , however work doesnt allow me to do so - so many other things i have to know------

The database is for work - simply to caterlouge our purchases - and because of time limitations i dont get enough time to research and learn more about access....