Coding Issues

  • Thread starter Thread starter Michael Johnson
  • Start date Start date
M

Michael Johnson

I have created a program with a custom search form and I
have figured out that in order for this form to work using
code I will have around 4096 different sections of code,
this is one for every possible combination. Is there an
easier way to do this then coding every section. Now that
I have actually got all the coding done I am getting a
procedure to large error message. Any help would would be
appreciated.

Thanks
Michael

Here is one section of my code

'Start Date Option 1
If start_date_option = True And carrier_option = True And
state_option = True And agent_id_option = True And _
sent_option = True And violation_option = True And
complete_option = True And title_option = True And _
description_option = True And error_option = True And
charged_option = True And employee_id_option = True And
word_option = True Then

strsearchresults = "select * from
[qry_letter_of_indemnity_tables] where [start_date] >= #"
& Format(strbegindate, "mm/dd/yyyy") & "# " _
& " and [start_date] <= #" & Format
(strenddate, "mm/dd/yyyy") & "# and [carrier] = '" &
strcarrier & "' and [annuity_signed_in_state] = '" &
strannuitystate & "'" _
& " and [agent_id] = '" & stragentid & "' and [loi_sent]
= '" & strloisent & "' and [compliance_violation] = '" &
strcomplianceviolation & "'" _
& " and [loi_complete] = '" & strloicomplete & "' and
[loi_summary_title] = '" & strsummarytitle & "' and
[loi_summary_description] = '" & strsummarydescription
& "'" _
& " and (([source_of_error] = '" & strerrorsource & "')
or ([second_source_of_error] = '" & strerrorsource & "')
or ([other_source_of_error] = '" & strerrorsource & "'))" _
& " and (([group_charged] = '" & strgroupcharged & "') or
([second_group_charged] = '" & strgroupcharged & "') or
([other_group_charged] = '" & strgroupcharged & "'))" _
& " and (([employee_id_1] = '" & stremployeeid & "') or
([employee_id_2] = '" & stremployeeid & "') or
([employee_id_3] = '" & stremployeeid & "') or
([employee_id_4] = '" & stremployeeid & "') or
([employee_id_5] = '" & stremployeeid & "'))" _
& " and (([loi_explanation] LIKE '*" & strwordsearch
& "*') or ([corrective_action] LIKE '*" & strwordsearch
& "*'))"

If rcs.RecordCount = 0 Then
strnorecord = MsgBox("No Records Found for this
Criteria", , "No Records Found")
Exit Sub

End If
End If

DoCmd.OpenForm "frm_search_option_results", acNormal
Forms!frm_search_option_results.RecordSource = stroption1
Call make_visible
Forms!frm_search_option_results!employee_id = strloggedinid
Exit Sub
 
It is not clear at all what, or which options change.

However, if I am guessing, I assume that for each option that is true, you
have need to put in a condition.

Normally, for a given search form, I simply test if the field value is NOT
NULL. If the field value is NOT null, then the user entered something into
the filed, and thus you add it to your criteria

So, I world use something like:

dim strSql as string
dim strWhere as string

if isnull(strAgentid) = false then
strWhere = "(agent_id = " & strAgentId & ")"
endif

if isnull(strLoiSent) = false then
if strWhere <> "" then
strWhere = strWhere & " and "
endif
strWhere = strWhere & "(loi_sent = '" & strLoiSent & "')"
end if

As you can see, you can continue the above process for as many fields as you
want. The result strWhere will have a VALID sql where clause. For fields
that have no entry by the user, then they are simply skipped, and NOT added
to the condition list. Thus, you built up ONLY conditions that are entered
on your search form. You can then direclity feed that "where" clause to a to
a report, and not even have to build the rest of the sql. (you use the
"where" clase feature of the report).

Or, after you got the above where built, you could build a whole correct sql
string as follows:

strSql = "select * from tblCustomer where " & strWhere

It is not clear at all where all your "flags" come from, but I think your
desing should dump them. However, if you do have flags for each field that
has a entry, then in place of using the isnull() function above, you would
simply test your flags

if agent_option = True then
bla bla bla as above.

However, for the many search forms I build, why use/set a flag for each text
box on the form when you can simply skip them when nothing is entered?

In general, the above apporch means that VERY LITTLE code is needed. And,
for fields that are NOT to be searched, I useally have commetns beside, or
under that state:
(blank = all).

For some nice screen shots of the above code example, take a look at:

http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html

Those screens all use the above sample code snip..and not much code is
needed at all. Notice how many of the screens go "blank = all"
 
It depends what you mean by "custom search form'. On my website (see sig
below) are several small sample databases which illustrate how to create
queries programmatically. Perhaps they will help. They all start with
"CreateQueries"

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Michael Johnson said:
I have created a program with a custom search form and I
have figured out that in order for this form to work using
code I will have around 4096 different sections of code,
this is one for every possible combination. Is there an
easier way to do this then coding every section. Now that
I have actually got all the coding done I am getting a
procedure to large error message. Any help would would be
appreciated.

Thanks
Michael

Here is one section of my code

'Start Date Option 1
If start_date_option = True And carrier_option = True And
state_option = True And agent_id_option = True And _
sent_option = True And violation_option = True And
complete_option = True And title_option = True And _
description_option = True And error_option = True And
charged_option = True And employee_id_option = True And
word_option = True Then

strsearchresults = "select * from
[qry_letter_of_indemnity_tables] where [start_date] >= #"
& Format(strbegindate, "mm/dd/yyyy") & "# " _
& " and [start_date] <= #" & Format
(strenddate, "mm/dd/yyyy") & "# and [carrier] = '" &
strcarrier & "' and [annuity_signed_in_state] = '" &
strannuitystate & "'" _
& " and [agent_id] = '" & stragentid & "' and [loi_sent]
= '" & strloisent & "' and [compliance_violation] = '" &
strcomplianceviolation & "'" _
& " and [loi_complete] = '" & strloicomplete & "' and
[loi_summary_title] = '" & strsummarytitle & "' and
[loi_summary_description] = '" & strsummarydescription
& "'" _
& " and (([source_of_error] = '" & strerrorsource & "')
or ([second_source_of_error] = '" & strerrorsource & "')
or ([other_source_of_error] = '" & strerrorsource & "'))" _
& " and (([group_charged] = '" & strgroupcharged & "') or
([second_group_charged] = '" & strgroupcharged & "') or
([other_group_charged] = '" & strgroupcharged & "'))" _
& " and (([employee_id_1] = '" & stremployeeid & "') or
([employee_id_2] = '" & stremployeeid & "') or
([employee_id_3] = '" & stremployeeid & "') or
([employee_id_4] = '" & stremployeeid & "') or
([employee_id_5] = '" & stremployeeid & "'))" _
& " and (([loi_explanation] LIKE '*" & strwordsearch
& "*') or ([corrective_action] LIKE '*" & strwordsearch
& "*'))"

If rcs.RecordCount = 0 Then
strnorecord = MsgBox("No Records Found for this
Criteria", , "No Records Found")
Exit Sub

End If
End If

DoCmd.OpenForm "frm_search_option_results", acNormal
Forms!frm_search_option_results.RecordSource = stroption1
Call make_visible
Forms!frm_search_option_results!employee_id = strloggedinid
Exit Sub
Click to expand...
 
Back
Top