Combo box "Number" Problem (1 Viewer)

[ansentry]

In the attached sample I have go one type of combo box to work but the one using a number I cannot get it to work.

I hope that someone can offer assistance.

I have become brain dead trying to solve this.


Regards,

 

[Travis]

Your problem was with the SQL not with how you set up your List Boxes.

Change this line

Me![RIGHTONE].RowSource = "Select[SALESPERSON].[Column](1),[Model],[CustomerName],[ChassisNumber]From [Tbl_Salesperson].[SalespersonID= " & Me![LEFTONE] & ""

To This

Me![RIGHTONE].RowSource = "Select [SALESPERSON],[Model],[CustomerName],[ChassisNumber] From [tbl_Vehicles] Where [Salesperson]= " & Me![LEFTONE]

A good way to Debug SQL code is to use the "Debug.Print" function in your code. This will spit out a clean SQL that you can copy into the SQL view of a newly created query. You can then attempt to run the query to view the results or the errors.

Example:

Dim sSQL as String

If Me![LEFTONE] = "<All>" Then
    sSQL = "Select[SALESPERSON],[Model],[CustomerName],[CHASSISNUMBER] From [tbl_Vehicles]"
Else
    sSQL = "Select[SALESPERSON].[Column](1),[Model],[CustomerName],[ChassisNumber]From [Tbl_Salesperson].[SalespersonID= " & Me![LEFTONE] & ""
End If

  Debug.Print sSQL
  Me![RIGHTONE].RowSource=sSQL

 

[ansentry]

Combo Box "Number" Problem.

Travis,

Thank you very much it works fine, however in LIST_A the Auto Number from tbl_Salesperson appears. I would like to have the SalespersonName to appear there (as in the example I have attached) , however in the SQL salesperson only appears as a number. I have tried [salesperson].[column](1) in the SQL but that does not work.

Is it possible to have both salespersonid and salespersonname appear in the Union query? If so how?

Thank you again Travis, I just started using SQL (not counting normal queries) I have just finished an option group that has SQL behind it and found that very interesting.

Regards

 

[Travis]

Change these properties on the LIST_A List box control:

Format Tab
Column Count ->2
Column Widths ->0;1.25

Data Tab
Row Source -> SELECT DISTINCT SalespersonID, SalespersonName FROM tbl_Salesperson UNION SELECT "<All>","<All>" FROM tbl_Salesperson;

 

[ansentry]

Combo Box Number Problem

Travis,

You have made my day (or night here) .

Thank you very much. I did not know that I could have used the "<ALL>" twice.

I just copied it as you said and it worked fine.


Thank you again


Regards