Using a form variable to get a list from a table 1

[willhyde]

I have a large library of catalogues (hard copy) of various products from many suppliers. Each catalogue is numbered individually. The supplier name, product type, catalogue title and ID number are entered into a table via a simple form (Access ’97).
I need a form where the user can select a product type from a list, e.g. “signs” or “bollards” or “kerbs” and then they are presented with a list of all the suppliers who offer that product, along with the appropriate catalogue name and ID number. Any Ideas?

 

[nickjar2]

Add your products and suppliers tables to a query, and select all the fields you wish to see in your form. In the product field enter criteria as follows:
forms![form Name]![Control Name]
where the control is going to be a combo box.

Save your query. Create a new form (lets say "Show Suppliers&quot based on this query. Add all your fields.

Go back to the first form (simple form Access 97) and create a combo box which will list all the products.
On the after update add this code:

docmd.openform "Show Suppliers"

Hope this makes sense,

Nick

 

[willhyde]

I'm not quite sure how to do all that. I think I could have worded the question a little better too...

I have a form called frmInput with text boxes for the company name, catalogue title and catalogue ID, and a combo box to select the product. Each record from this form is a row in a table called tblMain.

I have a form called frmSelect, with a combo box with the list of products. I can get this to run with an autoexec macro, I want any user to see this screen and select a product from the box, and the list of suppliers to appear either on the same form or on another form which pops up.

Sorry if this is what you understood in the first place.

 

[nickjar2]

Thats what I thought, but sometimes I don't explain very well, I tend to garble.
Ok, first create a query. I am assuming you have at least 2 tables, Suppliers and Products, linked by, say a Supplier_Id field. If thats is the case, goto the database window, select queries and click New. Click ok and you should see a window where you can see a list of tables. Choose the Suppliers and click Add, choose the Products then Add, and then click Close. I will post this now just to make sure that u do have 2 tables.

Nick

 

[willhyde]

As it is now I just have one table, with four fields - Company, Product, Catalogue Title, Catalogue ID. Is it better to have two or more tables?

 

[nickjar2]

Looking at your post again I will show u how to do it if u have only the one table, tblMain.
Create a new query. add the tblMain table and add the Product Name and the Supplier Name fields. In the bottom half of the query you will see options you can use like Sort, Show and Criteria. In the Criteria for the Product Name, type in:
forms!frmSelect![combo Box Name]

where combo box name is the name of your combo box on frmSelect.

Save your query.
In Databse window, select forms, click New. You will see a window with a drop down near the bottom. Locate the query you have just created and click OK. You now have a form bound to your query. In the menu at the top of the form choose View and th field list. Click and drag the Supplier field from the fild list. Save you form. (in this eg. I will call it frmSuppliers.

Goto your frmSelect form in design view. Double click the combo box so its properties appear. Click the Event tab, and locate 'After Update'. Once u click the after update an elipsis (...) should appear to the right. Click that. If a little window comes up (Choose builder) select Code Builder. Write this bit of code between the sub and End Sub of the after update procedure:

docmd.openForm "frmSuppliers"

God that is ;ong winded to explain. Sorry about that. I hope it makes sense. If not send another reply.

Cheers,

Nick

 

[nickjar2]

It would probably be better to have a suppliers and products table. If 1 Supplier can supply many products, and 1 product can be supplied by many suppliers then u would need another table to link prods and supps. (many to many relationship).
But if u only have the 1 table the above method should be ok for now. Let me know if it makes sense. As I said before, I tend to go and on and not make much sense.

Nick

 

[willhyde]

Seems to be almost there... when I select a product from the combo box on frmSelect, frmSuppliers appears, but the text box is blank with a flashing cursor in it.

 

[nickjar2]

Oh God, I am trying to think of why that is happening.
Select a prod from the combo again so u are taken to frmSuppliers. Keeping this open. go back to the database window, find your query and double click it. You should get a list of suppliers. If not, check the criteria in the query.
Let me know how u get on,

Cheers,

Nick

 

[willhyde]

Yep, there's a list of the companies who supply the product I chose in the combo box.

If I'm following this (probably not), I don't see where the textbox on frmSuppliers gets it's data from once I've selected a product and the query generates the list. If there's more than one supplier, how will I be able to see all of them in the text box - will it automatically have a scroll bar?

 

[nickjar2]

You could set the default of your form to Continuous forms or Datasheet View. Goto the properties of frmSuppliers. Click the Format tab. 2nd from top you will have default view. You will have 3 options to choose from.

"I don't see where the textbox on
frmSuppliers gets it's data from once I've selected a product"

What happens is, your query (with forms!frmSelect![combo Box Name] for its criteria in the product field) will say show every record where the product is equal to the product chosen from the combo box. Your text box on frmSuppliers should now be bound to the suppliers field in your query.

Nick

 

[willhyde]

Got it - I had the name of the text box misspelt... Doh!

Thanks a lot for your help - and patience - very much appreciated.

Cheers,

Will.

 

[nickjar2]

My pleasure.

Nick