Access 2007 - VB If, then, else query issue

[Patricia Pines]

The front end form has the following fields across the page - all are combo boxes:

Cat# Category Description Cat Part#


The first field is sourcing a seperate table from the other two. The second and third fields are sourcing the same table, just different columns. Similar to a cascading combobox, I wish to limit the list of descriptions and parts selectable.

To make this task simpler, I created 7 seperate queries (there are 7 categories). The idea is to call the query required based on the category selected. This would then display the parts required for that particular category.

I have written the following VB code to call the required query:

Private Sub Cat___A_Click()
 If TN_IDU_Ref.[Cat#] = 1 Then
    DoCmd.OpenQuery ([Cat 1 Query])
ElseIf TN_IDU_Ref.[Cat#] = 2 Then
    DoCmd.OpenQuery ([Cat 2 Query])
ElseIf TN_IDU_Ref.[Cat#] = 3 Then
    DoCmd.OpenQuery ([Cat 3 Query])
ElseIf TN_IDU_Ref.[Cat#] = 4 Then
    DoCmd.OpenQuery ([Cat 4 Query])
ElseIf TN_IDU_Ref.[Cat#] = 5 Then
    DoCmd.OpenQuery ([Cat 5 Query])
ElseIf TN_IDU_Ref.[Cat#] = 6 Then
    DoCmd.OpenQuery ([Cat 6 Query])
ElseIf TN_IDU_Ref.[Cat#] = 7 Then
    DoCmd.OpenQuery ([Cat 7 Query])
End If
End Sub

When I run the code a box pops up asking for a macro name. Any idea why? I would appreciate code suggestions as this is a a work related project that is quite urgent. Thanking you in anticipation, Patricia

[TheSmileyCoder]

You do realise that Docmd.Openquery will open/run the query as if you had double clicked it in the object explorer window?

If this is a matter of limiting selections for the user in regards to the next combobox in the line of selections, you should modify the recordsource of that combobox.

If opening the query is truly what you want you should enclose the query name in double quotes. Example:

DoCmd.OpenQuery ("[Cat 7 Query]")

[Patricia Pines]

Thank you for the feedback. I have emended the code as suggested, now I am receiving another error message:

"Run-time error '424':
Object required

I added the following before the first "if" statement:

Me.Cat___A = TN_IDU_Ref.[Cat#]

but it makes no difference. The first if statement is highlighed and the error message pops up. What am I missing? I understand that the error on the Else condition is that TN_IDU_Ref.[Cat#] does not = 1, so the first condition is skipped. I'm just not sure how to get around it.

The problem with modifying the recordsource of the combobox is that the secondary source has repeated category numbers, eg:

1 description
1 description
1 description
1 description
etc..

whereas the first combo box lists

1
2
3
4
5
6
7

I want the related query to be called based on the number selected so the user doesn't see multiple numbers, only the second column with the related descriptions. Any suggestions on how to achieve this?

[Patricia Pines]

Ok, I have decided to take another approach. I turned the TN_IDU_Ref table into a pivot table and created a LEFT JOIN from the Cat # table. Then wrote the following code:

Private Sub Cat_Part_A_AfterUpdate()
Dim Category_Description As String

sCategoryDescriptionSource = "SELECT [TN_IDU_Ref].[Cat#]," & _
" [TN_IDU_Ref].[Category_Title]," & _
" [TN_IDU_Ref].[Category_Description] " & _
" [TN_IDU_Ref].[Part_No#] " & _
" [TN_IDU_Ref].[Quantity] " & _
"FROM TN_IDU_Ref " & _
"WHERE [Cat#] = " & Cat#.Cat#
Me.Cat_Part_A.RowSource = sCategoryDescriptionSource
Me.Cat_Part_A.Requery

End Sub

Once a gain, when I run the code a box pops up asking for a macro name. I tried placing brackets, as per the previous post, but it makes no difference.

[TheSmileyCoder]

I want the related query to be called based on the number selected so the user doesn't see multiple numbers, only the second column with the related descriptions. Any suggestions on how to achieve this?

If you have a combobox, with 2 columns where the first is a Key column and the second is the column you want to display look at the Column properties of the combobo:

Column Count=2
Column Width=0;3cm
List Width  =3cm

Regarding your code (please use code tags [code]your code here[/code]):

Private Sub Cat_Part_A_AfterUpdate()
Dim Category_Description As String
 
sCategoryDescriptionSource = "SELECT [TN_IDU_Ref].[Cat#]," & _
" [TN_IDU_Ref].[Category_Title]," & _
" [TN_IDU_Ref].[Category_Description] " & _
" [TN_IDU_Ref].[Part_No#] " & _
" [TN_IDU_Ref].[Quantity] " & _
"FROM TN_IDU_Ref " & _
"WHERE [Cat#] = " & Cat#.Cat#
Me.Cat_Part_A.RowSource = sCategoryDescriptionSource
Me.Cat_Part_A.Requery
 
End Sub

Line 6 and 7, your missing a ",".

What is Cat#.Cat#?

Asumming you want to reference the combobox which you just updated, it would look like:

WHERE [Cat#] = " & Me.Cat_Part_A

[Patricia Pines]

Thanks for the update....still no joy. The macro pop up appears when I run it (just like before). Tried adding brackets, but Access just highlighted the code in red.

All I want to do is create several caascading combo boxes, the first being Cat # (select 1-7), this would display the selected category listing (eg: Cat 1 materials if Cat#1 selected), then you would select one of the materials, which then populates the table with the associated parts and required quantities. Some category materials have only one peice of material, others have up to 10, so all materials and associated quantities need to be listed when the user selects the category and the material.

Perhaps there is another way to achieve this? That's why I decided to take another approach. I turned the TN_IDU_Ref table into a pivot table and created a LEFT JOIN from the Cat # table.....but still no success. Not sure what to do now. What would you suggest as the best approach?

[Patricia Pines]

Had another thought......what about using Case instead? I'm not sure about the syntax, something similar to:

Private Sub Cat___A_AfterUpdate()
Dim sqltext As String
Dim rst As DAO.Recordset
Dim db As DAO.Database
 
Select Case Me.Cat#
 
   Case "1"
      Set db = BOMUpdate
      [Cat#] = 1
      DoCmd.OpenQuery [Cat 1 Query]
      DoCmd.RunSQL sqltext = "INSERT INTO BOMUpdate.CatPartA"
   Case "2"
      [Cat#] = 2
      DoCmd.OpenQuery ["Cat 2 Query"]
      DoCmd.RunSQL sqltext = "INSERT INTO BOMUpdate.CatPartA"
   Case "3"
      [Cat#] = 3
      DoCmd.OpenQuery ["Cat 3 Query"]
      DoCmd.RunSQL sqltext = "INSERT INTO BOMUpdate.CatPartA"
   Case "3"
      [Cat#] = 4
      DoCmd.OpenQuery ["Cat 4 Query"]
      DoCmd.RunSQL sqltext = "INSERT INTO BOMUpdate.CatPartA"
   Case "5"
      [Cat#] = 5
      DoCmd.OpenQuery ["Cat 5 Query"]
      DoCmd.RunSQL sqltext = "INSERT INTO BOMUpdate.CatPartA"
   Case "6"
      [Cat#] = 6
      DoCmd.OpenQuery ["Cat 6 Query"]
      DoCmd.RunSQL sqltext = "INSERT INTO BOMUpdate.CatPartA"
   Case "7"
      [Cat#] = 7
      DoCmd.OpenQuery ["Cat 7 Query"]
      DoCmd.RunSQL sqltext = "INSERT INTO BOMUpdate.CatPartA"
End Select
 
  End Sub   

Maybe that would work better?

[Patricia Pines]

By the way I used your suggestion of

your code here

, hope I did it correctly.

[TheSmileyCoder]

To be clear, you have 3 comboboxes. After making a selection in combobox 1, you want to update combobox 2, and after making a selection in combobox 2, you want to update combobox 3?

Your latest piece of code seems to be complete gibberish to be honest.

What type of query are [Cat 1 Query]? Is it an action query (Update/append/delete) or a selection query?

Assuming it is an action query you want to run for some reason, it should be:

DoCmd.OpenQuery "Cat 7 Query"

This is because the Docmd.Openquery expects a string parameter detailing the name of the query to run.

You use

Set db = BOMUpdate

but only for case 1
Is that intentional? what is BOMUPDATE?

Finally your syntax for

DoCmd.RunSQL sqltext = "INSERT INTO BOMUpdate.CatPartA"

is completely off. When that code is run, access will first evaluate the sqltext = "INSERT INTO BOMUpdate.CatPartA" and since that is not the case, the comparison will return false. Then access will proceed to try to run the code:

DoCmd.RunSQL FALSE

which makes no sense.
Correct syntax would be:

sqlText="INSERT INTO BOMUpdate.CatPartA"
DoCmd.RunSQL sqltext 

That said, your SQL syntax itself gives no meaning. Insert WHAT into BOMUpdate.CatPartA?

[Patricia Pines]

To answer your questions:

Yes, I have 3 comboboxes. After making a selection in combobox 1, it updates combobox 2, and after making a selection in combobox 2, it updates combobox 3.

Cat 1 Query is a selection query (select Cat 1 displays descriptions associated with that category, including all the associated parts and quantity required for each part).

BOMUPDATE is the name of the database and the table that the selections are inserted into. Set db = BOMUpdate is for all 7 categories, I didn't realise that I had set it for the first case only.

Insert WHAT into BOMUpdate.CatPartA? Insert the part number associated with the selected Category into column CatPartA in the BOMUpdate table.

The difficulty lies in that some category descriptions have only one part associated with it, while others can have up to 10 parts for that one description. A category can have up to 100 descriptions, so it makes it easier for the user to first select a category, then choose the required description and they get to see all the parts required. This then populates a table which will later transfer the data into a pivot table which sources data from multiple tables.