Forms - lookup

[CTQ3517]

I am somewhat new to Access. I am trying to set up a form which will allow me to choose my vendor and the info about that vendor comes up. I feel sure i need to rethink my tables to make sure the relationships are right, but it gets confusing. Here goes:

I have a table with vendors and vendor info
I have a table which will house the orders i place with each vendor.
I have a product table.
I placed a lookup in the order table so I would be able to choose my vendor.
I want to create a form that will allow me to choose my product and vendor info pops up and I need only key in the quantity and type of order

Can you help!

 

[GShen]

Hi,
I think I can help but I am a little confused. What do you mean by:
I placed a lookup in the order table so I would be able to choose my vendor.
Also I do not understand:
I want to create a form that will allow me to choose my product and vendor info pops up and I need only key in the quantity and type of order

Is this an MDB or ADP?

What does qty and type of order have to do with the vendor?

Can you reword and be a little more specific please?

 

[CTQ3517]

GShen - thanks for your reply - I will try to explain it better.

I have a product, vendor and order table. I want to set up a form that will allow me to choose my product and the product info fills in with the product info including the vendor name, address, contact person. Then I want to be able to fill in the quantity tha I want to order and the unit, I am ordering from. Now the unit part is pretty easy, i need only set up the radio button or option button and choose which unit is ordering. We have units all over the world and I am ordering thing from a warehouse for them. The warehouse will ship to that unit directly. Does this help? I just don't want to keep filling out the entire form.

I posted this question in another section before I found the form area and they recommended that I look at the setup in one of the sample databases. I will do that, but will look to hear from you as well. Thanks for replying.

 

[GShen]

we are getting there? Is this an MDB or ADP(Are you using SQL?)???????????
There are 1000 ways you can do this.
Here is one:
You can have a form with all your vendors on it and from that form click on a command button which will take you to another form with all the vendor info. You could put you radio buttons or whatever on that form as need be for data you wish to key in. From that form you could have another button which opens another form for all the products for that vendor and perform the necessary functions as above.
Reply back if I am confusing you.

 

[CTQ3517]

This is an MDB file. I am using Microsoft Access. I don't know much bout SQL so do my best to stay out of that area at this time. I use the forms wizard to make all forms and from time to time, take a look out back, so to speak, to view the programming.

The radio buttons seem pretty easy, my biggest proglem is that I don't know how to get started with the top portion of the form which will pull the name of the vendor, mailing address, etc. Then I can use a sub form to place my order, then choose from the radio for the type of order being place.

 

[GShen]

Are you using tables or queries as the recordsource for the form and subform?

 

[CTQ3517]

Yes, I am using tables.

(1) Table one has the vendor info
(2) Table two has the products and the vendor assigned to it
(3) Table three is the order table which is blank until I make my first order.
I want to go to my form and choose the vendor and the vendor pops up or fill in, then I want to choose the products from the product that that the vendor is responsible then I want to place the order by choosing the item(s) until I have completed my order. I then want to run a report that gives me that order and email it to my approving suppervisor in pdf format who approves it and emails it to the actual vendor.
Whew!!!!
Thanks

 

[GShen]

You can drive the 1st form using the vendor table or you can create a combo box with the vendor number and name and then open a form with all the vendor info. If you do not need to show all the vendor info. your next form can simple insert this data into the table. I would have a button on it to open up the next form (your old subform). It will need to be driven by a query. The query will have 2 tables the vendor and the products for that vendor. Have you done joins before? You can bring them up up on the grid and grab the vendor from your vendor table and drag it to your vendor on the products table. It will draw a line showing you the connection. This is an inner join. You can display all the info you want for each product in the detail section. I would keep it on 1 line so you can list all the products for the vendor. Use a continuous form with vertical scroll bars (just like on your 1st form). Use the header for the vendor info.
You an add the record a number of ways. You can simple close the form to add the record or craete another button on the form which which simple adds the record.

Hope this helps. If you need more info. or get stuck, let me know in very, very detail, what you do not understand, so I will know how much I have to explain.

Good luck.

 

[CTQ3517]

GShen - You are so kind!!!

I do understand joins. I will make my attempt today and will let you know how it comes out. I can't thank you enough for being so responsive. Talk to you soon!!!

CTQ