Issue with Expression Criteria in Cascading Combos

[kiss.shoremishra]

Hi All,

I am working on large set of Cascading combos on form. I initially got Data type mismatch error and I corrected the query as the last combo has data type number & not text but now and I am again getting prompt to enter the value for variable instead of retrieving it from above Combo. Below is my code.

Private Sub Combo19_AfterUpdate()
Dim pdtgrp5 As String
If Me.Combo19.Value <> "" Then
pdtgrp5 = Me.Combo19.Value

P5 = "SELECT DISTINCT [Tbl - WRIN].[Item Prefix] FROM [Tbl - WRIN] WHERE ([Tbl - WRIN].[Product Group Level5]) = '" & pdtgrp5 & "' ORDER BY [Tbl - WRIN].[Item Prefix];"
Me.Combo21.RowSource = P5
End If
Me.Combo21 = vbNullString 'to clear the next combo
Me.Combo21.Requery
End Sub
Private Sub Combo21_AfterUpdate()
Dim ItmPfix As Integer
If Me.Combo21.Value <> "" Then
ItmPfix = Me.Combo21.Value

IP = "SELECT DISTINCT [Tbl - WRIN].[Prefix Description] FROM [Tbl - WRIN] WHERE ([Tbl - WRIN].[Item Prefix]) = ItmPfix ORDER BY [Tbl - WRIN].[Prefix Description];"
Me.Combo23.RowSource = IP
End If
Me.Combo23 = vbNullValue 'to clear the next combo
Me.Combo23.Requery
End Sub

 

[jdraw]

Put yourself in the reader's position.
We know nothing of you, your business, your project..... We need some context.
What are you trying to do? What is the business in overview?
What sort of environment are you working in --large IT shop? small business? etc.
What is your experience with database and/or Access?

 

[kiss.shoremishra]

I am a analyst who work for a small supply chain management company. I work on access DB and I am just trying to develop a form to reduce my own work load using automation and save some time. I am fully loaded and have no time for breaks, So I could automate some of my daily chores then I will breathe during break time. Hope this helps.

 

[jdraw]

I don't mean to make light of your work load, BUT it sounds much like I'm too busy chopping wood to sharpen my ax.

If you want help you're going to have to supply some sample data, and examples of what your result should be.

In plain English what is the purpose of the form? And I know you want to reduce workload...
What exactly is the work and the volume? What is your plan to reduce workload? What are the things causing the "too much work" issue? List these, and rank them according to the biggest culprit to irritant only.

Do you have any standard operating procedures for your work?
Does your boss know about your workload?
Do you need training?
Has some part of your business changed and is now affecting you and your work?

Look at this from another view-- you have chosen form and combo boxes but there may be other options. If we only knew what the underlying business/process issues were?

 

[kiss.shoremishra]

There is lot of manual search to determine proper prefix under lakhs of product group. I choose to stream line and prepared a DB on my own. Now instead of manual searching, I am aligning sub group under group & so on. So if you see the below code I am at Combo box 23 that does not give related Prefix Description when I select the prefix in Combo box 21. Prefix is a number data type and though variable exist in query it still prompts a input box to enter value for the variable ItmPfix .

Private Sub Combo19_AfterUpdate()
Dim pdtgrp5 As String
If Me.Combo19.Value <> "" Then
pdtgrp5 = Me.Combo19.Value

P5 = "SELECT DISTINCT [Tbl - WRIN].[Item Prefix] FROM [Tbl - WRIN] WHERE ([Tbl - WRIN].[Product Group Level5]) = '" & pdtgrp5 & "' ORDER BY [Tbl - WRIN].[Item Prefix];"
Me.Combo21.RowSource = P5
End If
Me.Combo21 = vbNullString 'to clear the next combo
Me.Combo21.Requery
End Sub

Private Sub Combo21_AfterUpdate()
Dim ItmPfix As Integer
If Me.Combo21.Value <> "" Then
ItmPfix = Me.Combo21.Value

IP = "SELECT DISTINCT [Tbl - WRIN].[Prefix Description] FROM [Tbl - WRIN] WHERE ([Tbl - WRIN].[Item Prefix]) = ItmPfix ORDER BY [Tbl - WRIN].[Prefix Description];"
Me.Combo23.RowSource = IP
End If
Me.Combo23 = vbNullValue 'to clear the next combo
Me.Combo23.Requery
End Sub

 

[jdraw]

Post a jpg (zip format) of your tables and relationships.

Tell us about your Products and Product Groups.

Some sample data might be useful.