Senin, 11 Januari 2016

Re: [MS_AccessPros] Re: Dependent combo boxes

 

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 
(Paris, France)



On Jan 10, 2016, at 10:38 PM, bburke@swiftaz.net [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 
(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 
(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 
(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 
(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 MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Bob-

It looks OK to me, too.  It's best to enter the SQL in the Row Source as a single line with no line breaks, sorta like this:

SELECT tblCars.CarNumber FROM tblCars WHERE (((tblCars.CarName)=[Forms]![frmCars]![cmbCarName])) ORDER BY tblCars.CarName;

Be careful if the display in my reply comes back with multiple lines above.

You can also click the Build (…) button next to the Row Source property to open the SQL in the query designer.  That should make it easier to spot problems.

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 
(Paris, France)



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


John,
I get a 'characters found at end of SQL statement' msg...checked everything and it seems right;
Bob

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

Ooops.  Got the second SQL wrong.  Use this:

SELECT tblCars.CarNumber
FROM tblCars
WHERE (((tblCars.CarName)=[Forms]![frmCars]![cmbCarName]))
ORDER BY tblCars.CarName;

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 
(Paris, France)



On Jan 8, 2016, at 6:01 PM, John Viescas JohnV@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Bob-

OK, but I think you have it backwards.

The first combo box should be cmbCarName, and its Row Source  needs to be:

SELECT tblCars.CarName
FROM tblCars

__._,_.___

Posted by: John Viescas <johnv@msn.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (34)

.

__,_._,___

Tidak ada komentar:

Poskan Komentar