linking combo box's to a table

[DeanRowe]

Hi,

I’m using Access 2000.

I have various tables that contain the details, such as names and prices, of the different brands I stock.

For example I have a table called “Stihl Products” which contains the names and prices of that brands product. I have another table called “Husqvarna Products” containing the same information for that brand.

Here is what I would like to do on my orders form, but I currently can’t get my head around how to go about it:

I want to have a combo box with a drop down list with two options, “Stihl” & “Husqvarna”. When I come to place an order for a Stihl product I select “Stihl” from the list.
I then have another combo box underneath, which when pressed displays a drop down list of all the product codes for the Stihl products I stock.

This is the first problem:

I don’t know how to link the second combo box to react to the entry in the first. For example if I select Husqvarna in the first – how do I make the second combo box show/point to the Husqvarna codes in the “Husqvarna Products” table? Alternatively, if I selected Stihl in the first combo box, how do I make the second combo box show the Stihl Product codes?

Here’s the second problem:

After this has been done, and I have selected the correct product code (Lets say a Stihl “MS180 Chainsaw”) – I have a further two fields on my form which need to show the pricing information of that product, one is the “buying price” and the other is the “selling price” – is there any way of importing this data onto the form from the “Stihl Products” table? I was thinking of maybe a button underneath the combo boxes, which when pressed, retrieves the data from the Stihl "MS180 Chainsaw" entry in the “Stihl Products” table – but was unsure of the method of looking up this data.

Does anyone have an idea how I could do this? If anyone could point me in the right direction of what I should be doing so I can learn how to do it I would greatly appreciate it. Oh and by the way, the reason I need to display the information on the form itself is because it is linked to a series of reports, which automatically print out the data as a receipt.

Thank you for taking the time to read this.

 

[stopher]

I have various tables that contain the details, such as names and prices, of the different brands I stock.

For example I have a table called “Stihl Products” which contains the names and prices of that brands product. I have another table called “Husqvarna Products” containing the same information for that brand..

This is a bad idea. You should store all your products in one table. Consider if you had 50 products. Are you seriously going to create 50 tables and manage the reporting/coding nightmare to get it to work? Two product tables is one too many. Read up on "relational database theory" to see how to structure your tables. You will have difficulty answering your questions unless you put all your product data in one table.

I want to have a combo box with a drop down list with two options, “Stihl” & “Husqvarna”. When I come to place an order for a Stihl product I select “Stihl” from the list.
I then have another combo box underneath, which when pressed displays a drop down list of all the product codes for the Stihl products I stock.

This is the first problem:

I don’t know how to link the second combo box to react to the entry in the first. For example if I select Husqvarna in the first – how do I make the second combo box show/point to the Husqvarna codes in the “Husqvarna Products” table? Alternatively, if I selected Stihl in the first combo box, how do I make the second combo box show the Stihl Product codes?

Do a search of this site for "cascading combo boxes". There are many posts and solutions.

Here's the second problem:

After this has been done, and I have selected the correct product code (Lets say a Stihl “MS180 Chainsaw”) – I have a further two fields on my form which need to show the pricing information of that product, one is the “buying price” and the other is the “selling price” – is there any way of importing this data onto the form from the “Stihl Products” table? I was thinking of maybe a button underneath the combo boxes, which when pressed, retrieves the data from the Stihl "MS180 Chainsaw" entry in the “Stihl Products” table – but was unsure of the method of looking up this data.

There are several ways but personally I would use the After Update event of your second combo to do a DLookup based on the value of your second combo and put the looked up value in the required text box on your form.

Do a google search on "after update" for explanation/examples of how to use it. Search the Access help for how to use DLookup (or search the Forms forum for examples of how Dlookup is used to get values from tables).

Apologies if this isn't as succinct an answer as you had hoped for but there are a number of principles involved which you need to understand. Hope that gets you on your way.
Stopher

 

[DeanRowe]

Hi Stopher,

Thanks for replying! I've very briefly read through the post you made and I'm replying quickly to hope I can catch you again and briefly explain something.

I have approximately 2000 different products, but not all of them are from the same brand. For example there are 75 Stihl products, 100 Husqvarna, 300 DeWALT etc. So I keep each brands product information in one table each so its relatively readable when I look through it in the form layout, plus I couldn't keep all the information in one table as it would be too large, so I seperate them in this way.

I could have an order form for each brand, however more often than not a customer will order 1 husqvarna product, 1 Stihl product etc at the same time on the same order, so this is why I have to use the one order form.

I will look back through your post and start looking up the information you suggested - I thought I'd send this post quickly incase it changed anything and to explain why I need to do it in this way.

Thanks Stopher

 

[boblarson]

Definitely you do NOT want separate tables for different brands. ALL products should be kept in the same table, as mentioned by Stopher. To do otherwise violates the rules of normalization and will cause you no end of grief in trying to pull data together in a meaningful sense.

plus I couldn't keep all the information in one table as it would be too large,

What do you mean too large? Too large how? Access will support many millions of records in a table.

 

[stopher]

Following on from Bob's points, the way to manage how you "see" the data is through queries and forms. If you only want to see Stilh then apply a condition (filter) to your query that only shows Stihl. Or have a dropdown or search box that allows you to filter the form to show only Stihl.

Stopher

 

[DeanRowe]

Cheers for your response guys. I'm struggling to see it but I know I'm a newbie and you are experienced - so I'm pretty sure you will be right and I will be wrong. I want to do things right as I know getting the infrastructure of the database right sooner rather than later is important.

One thing that is very important is the ability for me or my team to be able to search through a brands products on a form - I would love to be able to load up a "Products" form, then select "Stihl" or "Husqvarna" from a drop down box and then click arrows to navigate through them - and this would also mean I could keep all the products in one table like you have suggested. Do you know of any tutorials or help guides that I could read to learn this, or the correct search phrase to type into google - part of the thing I find most difficult in learning Access is not knowing the correct terms to search for and read up on.

I'm presuming - tell me if I'm wrong please - that I should create a query for each brand, and have a "Products" form with a drop down box of the different brands - each brand, when selected, is linked to its own query - and it is these query results that are displayed on the form. So I now need to lookup how to display query results in a form (I know it sounds silly but I haven't done this yet but there should be lots of info out there). And to navigate through the pages I'm guessing I should lookup "creating arrows to navigate through form pages"? or would I still use the arrows that are standard on forms?

(Once again please correct me if i am wrong) Once I have done this - I then need to look into the OrdersEntryForm problem of displaying the pricing data depending upon which product is selected from the drop down/combo box - which is learning the "After Update event" and also "DLookup" - is there anything else you guys think I should look into/ read up on to solve this problem?

Thanks for all your help guys - I really do appreciate you taking the time to help me.

 

[boblarson]

Here's a sample I worked up for you. It includes a sample of how to pull reports for a single company or by all with just a single report.

 

[stopher]

Hi Dean,

I'm answering your other post here as it follows on I think.

Macros are fine for sequential operations i.e. when I click this button, open the form with the revised data, but if you want data in a form to change dynamically, you’ll need to use VBA.

Take a look at Bob’s example that he posted. Ignore everything in the Reports rectangle for what I’m about to say here.

Bob designed the form as a kind of QBF but hid the key function for some reason. Open the form in design view. At the top of the form you will see a combo + title that only appears in design view (“Go to Manufacturer”). Change the properties of this combo so it is visible (Visible=Yes). Save the form.

Now when you open the form you will see the dropdown and you can select a manufacturer and the rest of the info on the form will update to the selected manufacturer.

So what’s going on here…

Bob has designed the form as a form/subform with the manufacturer showing in the main form and the products associated with a manufacturer showing in the subform. Access handles the relationship between the form and subform so that if you change the manufacturer in the form (either by navigating to the next record or changing the combo), then the subform changes to show the corresponding data.

The other key point is the combo. Unlike most of the other fields on the form it is not related to any table or query (it is said to be “unbound”). Also, its list is derived from tblManufacturers so as you add more manufacturers to this table, they will appear in the combo.

Whenever the value of this combo changes, some VBA code is run to change the current record to the one corresponding to the new value. If you press ctrl-G, then the VBA editor will open and you will see all the code that Bob has written. Ignore the second and third sections. The first section looks like this:

Private Sub cboMfr_AfterUpdate()
    DoCmd.GoToRecord acActiveDataObject, "frmManufacturers", acGoTo, Me.cboMfr
End Sub

This is the bit of code that executes every time the value in the combo changes. It is basically going to the chosen record (GoToRecord). The key bit is Me.cboMfr which is a reference to the value of the combo on your form. That’s it !!!

There are other ways to implement a QBF. One way is to have a query set up so that its criteria is based on values entered in the form (I expect this is the method you have implemented). You can incorporate this query into your form but you will need to implement a bit of code rather than a macro (to make it dynamic). Let me know if you are interested in this method and I’ll send an example. I’ve used Bob’s example as it was already there and I didn’t want to confuse you with different ideas.

Hth
Stopher

 

[DeanRowe]

Hi Stopher,

Firstly thank you very much for taking the time to help me, I really do appreciate the help you and bob have been providing.

Looking at this hidded combo box has really excited me, because if I can get it to work then it will solve the original problem I had - being able to display results from an option selected.

What I mean by this is on Bobs form, when Husqvarna is selected, it automatically displays the information about husqvarna, ie the name, address, city, state etc.

If I put all my products into one table like you have been suggesting, I could have a combo box for my products and when one is selected, it can then automatically display it's prices underneath on my form using this AfterUpdate method.

For now I'm ignoring the subform section at the bottom of Bob's form, and concentrating on the Manufacturers results. I can't see how bob has linked the manfucturers fields to the relative fields in the manufacturers table.

I've attached my stihl products table with this post, I've made a form and have linked the combo box to the "Product Code" field in the "Stihlproducts" table, and I've entered the AfterUpdate code - but I cant see how to make the fields themselves respond to the combo box.

Would you mind helping me please one last time and showing me how to make them respond and link them together? I just can't see how Bobs done it.

Thank you ever so much for your help, if I can crack this I can then put all my products in one table and I wont have to search through forms every time to look for prices - it'll save me an unbelievable amount of time.

The other thing that got my attention in your post was when you said "Macros are fine for sequential operations i.e. when I click this button, open the form with the revised data" - some of my staff aren't amazing on computers so i don't think they could use the subform properly - but they are used to the format of loading up a form that is linked to a table and using the arrows at the bottom to navigate through the records - so what I would like to be able to do is put all my products in one table, then select a brand (either from a switchboard or from within a form using a combo box) and still have this visual format of navigating using the arrows, displaying one products information at a time on the screen - can a Macro be programmed to display a querys results in this manner?

Thank you very much indeed for all your help, I really do appreciate it very much.

 

[boblarson]

For now I'm ignoring the subform section at the bottom of Bob's form, and concentrating on the Manufacturers results. I can't see how bob has linked the manfucturers fields to the relative fields in the manufacturers table.

It's called binding and the main form has the recordsource of a query that is just the "SELECT * FROM tblManufacturers" (in other words select all records from that table) and then each field is "bound" to a field in that table/query by setting the recordsource to the field in question. So, the main form is just like a form that is created by Access when you use the wizard (with the exception that Access will put the table name in there instead of a query, but by using a query I can set the sort order whereas with a table records are not sorted in any particular order and therefore when I display them in my form, if not using a query, they will potentially display out of order).

I've attached my stihl products table with this post,

Nothing shows here as an attachment, you may want to try again.

I've made a form and have linked the combo box to the "Product Code" field in the "Stihlproducts" table, and I've entered the AfterUpdate code - but I cant see how to make the fields themselves respond to the combo box.

The afterupdate code (make sure it's in the Combo Box's After Update event and not the form's After Update event) is what searches through the recordset that we have opened with the form (the "SELECT * FROM tblManufacturers). That is how it responds to the combo box.

The other thing that got my attention in your post was when you said "Macros are fine for sequential operations i.e. when I click this button, open the form with the revised data" - some of my staff aren't amazing on computers so i don't think they could use the subform properly

If you don't like the data sheet method that I've used, you can still use the subform format by setting the subform to SINGLE FORM as the default and setting the Navigation Buttons to YES and it will then show you a single form within a form (and if you use that then the links are automatically supplied via the Child/Master links in the subform control). But, if you would rather just open a form based on the manufacturer data, you can open one by using:

DoCmd.OpenForm "YourFormNameHere", acNormal,, "[MfrID]=" & Me.YourComboBoxNameHere

 

[DeanRowe]

Sorry, it appeared too large so i cut it down further, here it is...

 

[DeanRowe]

Hi Bob,

I can't seem to get the Control Source property to offer the same drop down box of fields from the table as you do, do you know what I'm doing wrong?

 

[boblarson]

I would suggest using the Form wizard to create your initial form, then you can modify it as needed (i.e. to add an unbound combo box to select the records, etc.). With the form you have there's a bit of work (adding the recordsource, binding the controls, etc.) that you need to do before worrying about the combo box and the form wizard will make it very easy.

Also, noting your table structure, you are definitely in need of normalization as you have repeating fields (all of the different VAT fields for different entities). If you do not normalize you will find getting data out in a meaninful way difficult, to say the least, when you start wanting reports.

 

[boblarson]

Hi Bob,

I can't seem to get the Control Source property to offer the same drop down box of fields from the table as you do, do you know what I'm doing wrong?

You have to set the form's Recordsource first. You can just type

SELECT * FROM stihlproducts

in the Form's recordsource and then the fields will become available to you.