Adding "ALL" option to combo box

[nic311]

Hello,



I have a form with multiple combo box whose values are based on a different tables. My rotation table is made up of two fields: Rotation ID and Rotation. There is currently only two records in there: Standard and Reverse, each with an auto generated ID number.


SELECT DISTINCTROW [RotationTbl].[RotationID], [RotationTbl].[Rotation] FROM [RotationTbl]


I also have another table, Product which contains rotation as one of the fields. It also has a product ID, product name, and a few other fields (size and material) in there. I currently have a query which links the ProductTbl with the Rotation Tbl. In the form, there is combo boxes for ProductName, Rotation, Size and Material.


That works fine, but when I add an "ALL" option I get the following error:

"The number of columns in the two selected tables or queries of a union query don't match."

I then went back and tried basing the combo box on the entire table (ID field and Rotation field):

SELECT DISTINCTROW [RotationTbl].[RotationID], [RotationTbl].[Rotation] FROM [RotationTbl] UNION SELECT "*" as RotationID, "(All)" as Rotation from RotationTbl.

However, when I select the All option and try to run the query, I get an error saying that there's a "Datatype mismatch in criteria expression."

I think the issue is that the ID field is an AutoNumber, while the Rotation field is Text therefore there's two different types of data. However, when I base my table only on the Rotation field, it tells me the Union query won't work because of the different number of columns...

Can someone please tell me what I need to do to try to fix this problem? I've tried researching it online, but nothing I've found has seemed to fix it. I'm new to access and VBA so any explanation behind any sql/ vba code would be appreciated. Thanks in advance!

[pdebaets]

The columns of the different queries in a union query need to be similar. Try this:

SELECT DISTINCTROW [RotationTbl].[RotationID], [RotationTbl].[Rotation] FROM [RotationTbl] UNION SELECT 0 as RotationID, "(All)" as Rotation from RotationTbl.

[nic311]

Although this does add an "All" option to the combo box, the query comes up with no results when this option is chosen. I'm assuming that this is because there is currently no RotationID with a value of 0... I want the query to return the results that have any of the options listed in that combo box. How can I go about accomplishing this?

Thanks

[pdebaets]

You will need code in your combo box after update event procedure to update the query SQL. something like this:

if MyComboBox = 0 then
'* All
Currentdb().querydefs("MyQueryName").sql = "Select * from RotationTbl;"
else
Currentdb().querydefs("MyQueryName").sql = "Select * from RotationTbl Where RotationID = " & MyComboBox & ";"
endif

[nic311]

I tried adding that code and I still get no results being brought up. Any other suggestions? I've browsed the internet for hours but have not yet had any success in finding something that will work...

[pdebaets]

Did the code above work? Can you open your query in design view, view the SQL and see that the changes have been made? What happens when you run the query? Is there an error message?

[nic311]

The sql for the query is below:

SELECT AttributesTbl.Assembly, ImpellerNameTbl.ImpellerName, FrameSizeTbl.FrameSize, TrimTbl.Trim, RotationTbl.Rotation,
FROM ((((((AttributesTbl INNER JOIN ImpellerNameTbl ON AttributesTbl.ImpellerName = ImpellerNameTbl.ImpellerName) INNER JOIN FrameSizeTbl ON AttributesTbl.FrameSize = FrameSizeTbl.FrameSize) INNER JOIN TrimTbl ON AttributesTbl.Trim = TrimTbl.Trim) INNER JOIN RotationTbl ON AttributesTbl.Rotation = RotationTbl.Rotation)
WHERE (((ImpellerNameTbl.ImpellerID)=[Forms]![frm_imp_search]![cbo_imp_name]) AND ((FrameSizeTbl.FrameID)=[Forms]![frm_imp_search]![cbo_frame_size]) AND ((TrimTbl.TrimID)=[Forms]![frm_imp_search]![cbo_trim]) AND ((RotationTbl.RotationID)=[Forms]![frm_imp_search]![cbo_rotation]));

I did put the code in the After Update event and I noticed that once I go to the form view, and then back to design view and view the Properties of that combo box the code that I had previously put in there (which you gave me) is no longer there... I'm not sure what this is due to

As far as an error message, I did not get any. I have a search button with the following code attached to it:


Private Sub cmd_run_qry_imp_search_Click()
On Error GoTo Err_cmd_run_qry_imp_search_Click

Dim stDocName As String
Dim intHolder As Integer

intHolder = DCount("ImpellerName", "qry_imp_search")

If intHolder > 0 Then

stDocName = "qry_imp_search"
DoCmd.OpenQuery stDocName, acNormal, acReadOnly

Else
MsgBox "There are no records that match the given criteria."

End If

Exit_cmd_run_qry_imp_search_Click:
Exit Sub

Err_cmd_run_qry_imp_search_Click:
MsgBox Err.Description
Resume Exit_cmd_run_qry_imp_search_Click
End Sub

I am getting the message saying "There are no records that match the given criteria" when I use the "ALL" option, but with the same criteria and picking one of the two rotation types (reverse or standard) I do not get this message and am able to view the results...