ACCESS FEED: Re: [MS_AccessPros] Dependent combo boxes

OK John, I'll work on that and get back to you. Thank you for all your help - you have been very patient. I'm sure I'll have questions later on.

Bob

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Bob-

To have a correct relational database design, you should have only one table for a given subject. "Routes" is a subject that should be in one table. If you have four different routes, you simply need to add a column to indicate which route a particular row belongs.

I recommend you use a form to do data entry. You can start with that form you've been constructing - simply make the new table the Record Source and set the Control Source of each combo box to the relevant field in the table. Because you have cascading combo boxes, you'll need a Requery of the second combo box in the form's Current event.

But rather than store the individual Car fields in tblShipments, you probably should store just the CarID - that's all you need to get the relevant fields in a query by joining tblCars with tblShipments. In that case, leave your combo boxes unbound, but leave them on the form to help the user find the correct car number. In After Update of the second combo, grab the CarID and put it in the CarID field of tblShipments. You'll need to add CarID to the RowSource of the second combo box, but set its Column Width to 0" so the user doesn't see it. You would grab the value by doing:

Me.CarID = Me.cmbCarNumber.Column(1)

Column(0) is the first column that's being displayed, but you want the second column containing the CarID.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Jan 12, 2016, at 4:54 PM, bburke@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

tblIndustry1stSub and 2ndSub are different routes, have different crews, and run at different times. I will have two additional routes for a total of four. This will make setting up the switchlists for each route easier as each route have their own Stations (towns) although there may be some overlap in products and shippers.
One question I have is how to store the data needed for the report. Is that done through a query or form?
Bob

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Bob-

What is the difference between tblIndustry1stSub and tblIndustry2ndSub? Both seem to contain shipper names.

You need another table - perhaps called tblShipments - that has all the fields shown in your spreadsheet. Use that as the Record Source for your form. You would need additional combo boxes to set Kind, From / To, Consignee, Shipper, and Contents. You'll have to decide how to set the CLIC, Work, and Blk fields. Basically, your form can then be used to collect the data needed for your report. Then use the new table as the Record Source for a report.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Jan 11, 2016, at 10:43 PM, bburke@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John - I uploaded an excel file (Switchlist) in Files-Needs Assistance which is what the Report should look like. My original intention was to have a drop-down box for INIT (which is CarName). I would choose a CarName, then the CarNumber will be chosen, which will populate the Type and Length. Then I would choose a To Station which would bring up a list of industries (Consignees) in that Station. Each Station will have one or more industries, and each industry will have one or more CLICS. Further, each industry will have one or more (probably not more that 3) products they ship or receive. There will also be a drop-down for BLKS (Blocks) I can choose from.
Thanks,
Bob

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Bob-

Then it's not clear why you're using a Form. Which table has the town, industry, product, and shipper information? How will you relate this to the information in tblCars?

Basically, you need to identify where all the data you need resides and how it links together. Then you build a query using those tables and output the fields you need for your report. You can then group the report on CarName, CarNumber, or any other field. The report will match and sort the data using your design. Car Name can appear in a group header, then Car Numbers and their related type and length, then the related To / From info for each specific car.

You are spinning your wheels designing a form.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Jan 11, 2016, at 4:41 PM, bburke@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

The end result will be a Report that will be used as a switchlist for operators that list a number of cars (with car name, number, type, and length) as well as towns, industries, product, and shippers. For example the following columns will be used:

Car Name- Car Number -Type- Length- To Station - Contents - From Station - Block

So I would need to store the Car Data as there may be 20-30 rows of data, and be able to update it at a later time. In over my head??

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Bob-

Add

Me.cmbCarNumber = Null

To the AfterUpdate event.

What is it you're trying to accomplish? Are you trying to do a "drill down" based on criteria the user selects? What should happen after the user makes all the selections?

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Jan 10, 2016, at 10:38 PM, bburke@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

It works! Only thing is when I choose a different Car Name it would be nice if the Car Number combo box would go blank instead of staying on the last chosen number. I will now attempt to add the car type and length.

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Bob-

Ah, here's what I found in the Row Source for the cmbCarName combo box:

SELECT tblCars.CarName FROM tblCars ORDER BY tblCars.CarName;
FROM tblCars
ORDER BY tblCars.CarName;

You can't see the second and third line in the Row Source property because of the carriage returns. Open your form in Design View, select the first combo box, click in the Row Source property, then press Shift+F2 to bring up the Zoom box. Delete the second and third lines and click OK.

While you're at it, add the DISTINCT keyword like this:

SELECT DISTINCT tblCars.CarName FROM tblCars ORDER BY tblCars.CarName;

That will list each Car Name only once.

You also have problems with the Column Count and Column Widths properties. Set Column Count to 1 and Column Widths to 1".

You have similar problems with the cmbCarNumber combo box. Change Column Count to 1 and Column Widths to 1".

Finally, there's a blank character in the Control Source of cmbCarNumber. Get rid of that.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Jan 10, 2016, at 6:48 PM, bburke@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

OK John, I uploaded the file ATSF_SO_Sub; description Car name and number.
Bob

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Bob-

Please go to the group website, click on Files and then 2_Assistance Needed, then upload your database for me to look at. Let me know when you've done that.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Jan 10, 2016, at 4:10 AM, bburke@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote: