By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,409 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Multiple List Box form to build dynamic query feedback wanted

P: n/a
I have designed a form that has 5 different list boxes where the
selections within each are used as criteria in building a dynamic
query. Some boxes are set for multiple selections but these list boxes
do not necessarily need to have a selection made to be used in the
dynamic query.

In essence the form can have selections made in all or none of its list
boxes to form the dynamic query

I am looking to get some feedback in reference to my code. I am in no
way an expert and am always looking for some helpful hints to improve.

The following code is lenghty and complex and am sure can be condensed
and improved. If you got nothing else to do or are good natured enough
to help an inquiring mind I would appreciate some feedback.

Thanks in advance

*********begin code*********

'variable declaration
Dim varItem As Variant
Dim Query As String
Dim CriteriaProf As String
Dim CriteriaState As String
Dim CriteriaRPC As String
Dim CriteriaRegion As String
Dim CriteriaLanger As String
Dim chkEMail As String
' Set Criteria for Each ListBox
'1st set criteria with 'or' statement between each selection
'2nd trim the last 'or' statement off
'3rd put criteria in parentheses

For Each varItem In Listbox_Prof.ItemsSelected
CriteriaProf = CriteriaProf & " Profession='" &
Listbox_Prof.ItemData(varItem) & "' OR "
Next varItem

If Len(Nz(CriteriaProf)) <> 0 Then
CriteriaProf = Left(CriteriaProf, Len(CriteriaProf) - 3)
CriteriaProf = " ( " & CriteriaProf & " ) "
End If

For Each varItem In Listbox_State.ItemsSelected
CriteriaState = CriteriaState & " State='" &
Listbox_State.ItemData(varItem) & "' OR "
Next varItem

If Len(Nz(CriteriaState)) <> 0 Then
CriteriaState = Left(CriteriaState, Len(CriteriaState) - 3)
CriteriaState = " ( " & CriteriaState & " ) "
End If

For Each varItem In Listbox_RPC.ItemsSelected
CriteriaRPC = CriteriaRPC & " ReturnedPostCard=" &
Listbox_RPC.ItemData(varItem) & " OR "
Next varItem

If Len(Nz(CriteriaRPC)) <> 0 Then
CriteriaRPC = Left(CriteriaRPC, Len(CriteriaRPC) - 3)
CriteriaRPC = " ( " & CriteriaRPC & " ) "
End If

For Each varItem In Listbox_Region.ItemsSelected
CriteriaRegion = CriteriaRegion & " Region='" &
Listbox_Region.ItemData(varItem) & "' OR "
Next varItem

If Len(Nz(CriteriaRegion)) <> 0 Then
CriteriaRegion = Left(CriteriaRegion, Len(CriteriaRegion) - 3)
CriteriaRegion = " ( " & CriteriaRegion & " ) "
End If

'This ListBox holds three selection (N/A, Yes, No) for each slection
'specific criteria is set

For Each varItem In Listbox_Langer.ItemsSelected
CriteriaLanger = Listbox_Langer.ItemData(varItem)
Next varItem

If CriteriaLanger = "No" Then
CriteriaLanger = "( Heard<>'Langerman Lists' ) "
End If

If CriteriaLanger = "Yes" Then
CriteriaLanger = "( Heard='Langerman Lists' ) "
End If

If CriteriaLanger = "N/A" Then
CriteriaLanger = ""
End If

'Checkbox used to select a null value for criteria

chkEMail = Me.chkEmailOnly

If chkEMail = -1 Then
chkEMail = "( Email<>null ) "
End If

'Start to build dynamic SQL statement
'1st set SELECT statement
'2nd add WHERE clause for by adding criteria one at a time
'If Criteria for ListBox is not empty add it to SQL followed by
'AND' statement
'3rd Trim last 'And' statement
'qryDetailExport pre-existing with predefined fields needed for
output

Query = "SELECT * FROM qryDetailExport WHERE "

If Len(Nz(CriteriaProf)) > 0 Then
Query = Query & CriteriaProf & "AND "
End If

If Len(Nz(CriteriaState)) > 0 Then
Query = Query & CriteriaState & "AND "
End If

If Len(Nz(CriteriaRPC)) > 0 Then
Query = Query & CriteriaRPC & "AND "
End If

If Len(Nz(CriteriaRegion)) > 0 Then
Query = Query & CriteriaRegion & "AND "
End If

If Len(Nz(CriteriaLanger)) > 0 Then
Query = Query & CriteriaLanger & "AND "
End If

If Len(chkEMail) > 1 Then
Query = Query & chkEMail & "AND "
End If

Query = Left(Query, Len(Query) - 4)

'Create temp query with dynamic SQL statement to use for various output
reasons

Dim qryExport1 As New QueryDef

On Error Resume Next
CurrentDb.QueryDefs.delete ("qryExportTemp")
On Error GoTo 0 ' turn off error hand
qryExport1.Name = "qryExportTemp"
qryExport1.SQL = Query

CurrentDb.QueryDefs.Append qryExport1
CurrentDb.QueryDefs.Refresh

Me.CountTXT = DCount("*", "qryExportTemp")
*******end code***********

Nov 13 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.