[MS_AccessPros] Re: Dependent combo boxes

 

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:

No other SQL in the form - There is no record source as the data page is blank.

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

Bob-

So, the error must not be from that SQL.  Do you have SQL anywhere else in the form?  What is the Record Source of the 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 9, 2016, at 4:02 PM, bburke@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

It goes to a frmCars : Query Builder. If I run it the CarNumber box shows up.

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

Bob-

What happens if you click in the Row Source property of the second combo box and then click the Build (…) button next to the property?

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 8, 2016, at 11:57 PM, bburke@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Here is the way the combo boxes are set up:

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

cmbCarNumber:  SELECT tblCars.CarNumber FROM tblCars WHERE (((tblCars.CarName)=[Forms]![frmCars]![cmbCarName])) ORDER BY tblCars.CarName;  (All on one line.)

I still get a 'characters found after end of SQL statement' dialog box. I created a form using Form Design, and assigned the combo boxes to that form called frmCars.

B

---In

__._,_.___

---

Posted by: bburke@swiftaz.net

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (37)

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___