Sabtu, 09 Januari 2016

Re: [MS_AccessPros] Re: Dependent combo boxes

 


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
ORDER BY tblCars.CarName;

Set the Bound Column to 1.

The second combo box should be cmbCarNumber, and the Row Source should be:

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

The Bound Column should also be 1.

In the AfterUpdate event of the cmbCarName control, do:

Private Sub cmbCarName_AfterUpdate()
    ' Requery second combo to apply the new filter
    Me.cmbCarNumber.Requery
End Sub



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 5:22 PM, bburke@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:


Sorry John, I was sure I included previous messages. There is only one table, tblCars.  That table contains Car Name, Car Number, Type, and Length. I want to be able to return a number based on the car name I choose from CarName. Since there are multiple car names I want to filter the CarName so I can pick a particular CarNumber.  That will show up in a report listing, say, 20-30 car names with appropriate car numbers.  10 of those names may be, for example, ATSF, each will a unique CarNumber.  I'll try setting up the bound column again.

Bob

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

Bob-

You REALLY need to include the previous messages.  When you reply on the web, you MUST click the Show Message History link in the bottom of the reply box.  It's very difficult to remember what we've been talking about when you don't do that.  I've copied and pasted some of the replies below.

You need to set the Row Source for cmbCarNumber to a query that returns both the Car Number and whatever it is you want to display.  If Car Number is in the first column, set Bound Column to 1 to make that the value of the combo box.

But keep in mind that your query for the second combo is filtering on Car Name:

WHERE (((tblCars.CarName)=[Forms]![frmCars]![cmbCarNumber].[Value]))

Are you sure you want to return a number or a name?  What is the field layout of the two tables, and how are the tables related?

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

John,

I'm not sure how to set up the cmbCarNumber so that its Bound Col. is the Car Number.

Bob







__._,_.___

Posted by: bburke@swiftaz.net
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (29)

.

__,_._,___

Tidak ada komentar:

Poskan Komentar