473,394 Members | 1,526 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

Multiple List Box form to build dynamic query feedback wanted

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
0 3472

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: jason | last post by:
How does one loop through the contents of a form complicated by dynamic construction of checkboxes which are assigned a 'model' and 'listingID' to the NAME field on the fly in this syntax:...
8
by: Sans Spam | last post by:
Greetings! I have a table that contains all of the function permissions within a given application. These functions are different sections of a site and each has its own permissions (READ, WRITE,...
9
by: Ed_No_Spam_Please_Weber | last post by:
Hello All & Thanks in advance for your help! Background: 1) tblT_Documents is the primary parent transaction table that has 10 fields and about 250,000 rows 2) There are 9 child tables with...
4
by: cwwilly | last post by:
Hello, Thanks for taking a look at this! Problem: I'm trying to pass multiple dynamic values between a slaveform and a masterform. The problem I'm having is on the slaveform I loop through...
1
by: Mark | last post by:
My Category and Product tables look like: TblCategory CategoryID Category TblProduct ProductID CategoryID Product
9
by: Graham | last post by:
I have been having some fun learning and using the new Controls and methods in .Net 2.0 which will make my life in the future easier and faster. Specifically the new databinding practises and...
6
by: Dave | last post by:
On my form I have combo boxes. These combo boxes, after updating them, populate respective listboxes that are located below the combo boxes on the same form. I am trying to use a "generate...
2
by: englishman69 | last post by:
Hello, I have been banging my head against this one for a while... Searches online have revealed many different proposals for correcting my issue but none that I can follow! My basic situation...
58
by: bonneylake | last post by:
Hey Everyone, Well recently i been inserting multiple fields for a section in my form called "serial". Well now i am trying to insert multiple fields for the not only the serial section but also...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.