newbie: cascading combo boxes

[JoelSaltzman]

I am learing access by trial and error. I have searched to try and figure out how to make cascading combo boxes but i just cant figure it out. I attached my db and the main page i will be using is the Products form. I want to list each product seperatley and the subform shows where those products are allocated to. I want to put 3 combo boxes on the products form. The first one will select the category type the second one will then show all inventory that is in that category sorted by sku number and the third one will do the same as the second however it is sorted by name. The selected inventory items in box 2 or 3 will then put their info into corresponding fields. Take a look at my db and you will know what im talking about. any help would be greatly appreciated.

does anybody have suggestions on what book i should get to learn access?

 

[WayneRyan]

Joel,

Where's your db?

Tools --> Database utilities --> Compact/Repair
ZIP
Then Post.

Wayne

 

[JoelSaltzman]

sorry

 

[WayneRyan]

Joel,

Here goes...

Wayne

 

[JoelSaltzman]

yeah!

that is perfect! thank you so much. is it possible to have the name combo box sorted by alphabetically rather than by sku? How would i do that?

 

[JoelSaltzman]

nevermind

i got the sorting part. sorry stupid question

 

[dsomers]

I'm trying to learn how to do this cascading combo box thing and my question is how did you create the table tblcategories? For my use, I'm grouping apt #'s by building number and I can't get past this first step.

Thanks!
David Somers

 

[dsomers]

Are there any better examples or how-to websites or anything that someone can point me to to learn how to do this?

Thanks!
David Somers

 

[wizcow2]

David

This is a really good site!

Cascading Combos

Good Luck
Tom

 

[dsomers]

Thanks wizcow! But I'm having a little trouble. I followed exampled 1 from the link you provided and created 2 combo boxes: cboBuildingNumber and cboApartmentNumber. I created the tables accordingly from example 1 to fit my needs (1 for building number and 4 for each building's list of apt. #'s). I went ahead and put the following code in the form I'm using:

Private Sub cboBuildingNumber_AfterUpdate()

    On Error Resume Next
    Select Case cboBuildingNumber.Value
        Case "1219"
            cboApartmentNumber.RowSource = "1219ApartmentNumbers"
        Case "1229"
            cboApartmentNumber.RowSource = "1229ApartmentNumbers"
        Case "1231"
            cboApartmentNumber.RowSource = "1231ApartmentNumbers"
        Case "1233"
            cboApartmentNumber.RowSource = "1233ApartmentNumbers"
    End Select
        
End Sub

Now, when I go to choose my building number it has the list of 1219, 1229, 1231 and 1233. When I select one and go to choose an apartment number, there is nothing in the list. All I did for creating the combo box for the apartment numbers is create it without the wizard, changed the label name and named it cboApartmentNumber.

What am I doing wrong?

Thanks!
David Somers

 

[dsomers]

Well, I got it working. However, when I try to save the record (add new tenant), it does not save their Building Number or Apartment Number. The example from Wizcow doesn't explain how this aspect of it would work. Can someone please help me?

Thanks!
David Somers

 

[Rich]

You do not need 4 tables for Apt Numbers, again One Table for Building and one for Apts. The PK from Buildings becomes the FK in Apts. That way you can have as many Buildings with as many Rooms as you like without having to create more tables

 

[dsomers]

Here is a copy of the db if you would like to take a look at what I'm doing.

Thanks!
David Somers

 

[wizcow2]

David

You need to apply a 'Control Source' for the comboboxes.
Before Access can store a value, you have to tell it where to store it.
You may have to make two new fields in your table.
eg; BuildingNo and ApptNo

In the 'Control Source' of the cboBuildingNumber, apply BuildingNo

Good Luck
Tom

 

[KatieK]

Newbie needs guidance

Attached is my db that I have created. Tried to do cascading boxes with Company and Theatre. Now the Form creation is stalling me out. I can input fields from my tables and from my query but every time, it comes out different and incorrect.
I need to have a form to input info and have the calculations updated immediately in the form.
Then I need to create a report to show only those records with balances due, not with $0.
Please advise.

 

[RichO]

KatieK,

Your description of what you are trying to do is vague. I tried to look at the problem with the fields not updating after a calculation but you cannot input any data into form 1 or 2. The form won't let you change any of the fields. Neither the form, nor the text boxes are locked so I'm not sure what the problem is with that.

For printing a report showing only those records with a balance due, base the report on a query and set the query's criteria for the balance due field to <>0.

 

[KatieK]

KatieK,

Your description of what you are trying to do is vague. I tried to look at the problem with the fields not updating after a calculation but you cannot input any data into form 1 or 2. The form won't let you change any of the fields. Neither the form, nor the text boxes are locked so I'm not sure what the problem is with that.

For printing a report showing only those records with a balance due, base the report on a query and set the query's criteria for the balance due field to <>0.

Hi RichO,
I'll try and describe in more detail what I'm trying to accomplish. In the form, there will be entry of info such as date played, distributor, company, theatre and gross. When I try and create the form with cascading combo boxes, every time I reenter the form, I'm taken to Record 1 and my info is not being stored in the form. I'd like to open the form to the Next Record. I tried putting code in the OnOpen event procedure but it gives me an error.
I also need to be able to go back at a later date and enter in the H/A, which will calculate into the Net which will calculate by Term and give me Film Rental. I have created queries to do the calculations, I just can't seem to get the info to save to the form.
Another question, if I enter the second data (H/A, Paid Amount) directly onto the table, will this upload into my form and when I run my query, will it give me correct calculations.
Thank you for your help on this. I seem to get something accomplished and then something else doesn't work. Trying not to get frustrated.
Katie