search form coding

[f430s]

hi, i have a master table with all my part information, and it has columns with part number, creationdate, defects,...
and i am trying to write a code for my search form.
i have created a search form on Access, and it has 5 combo boxes for:
part number
defects
assembled by
and the other 2 combo boxes are for "from" and "to" date range. i have made the combo boxes for the date range to display a calendar when clicked so i can choose a date.

i have been trying to write a code that only requires one of these fields to be filled out and it can filter my master table.

i have a code that is giving me a lot of problems, i can post my full code if it is necessary.

i would appreciate any help on this
Thanks

 

[dhookom]

Your code could work in many different ways including:
- building and applying a filter
- changing the record source property
- using the criteria in a saved query
- possibly others

Can you tell us at least which method you attempted and how you are handling empty controls?

Duane
Hook'D on Access
MS Access MVP

 

[randy700]

i have a code that is giving me a lot of problems, i can post my full code if it is necessary.

It would certainly be helpful.


Randy

 

[f430s]

the fields i am using from my master table are:
Part Number
CreationDate
Defect Code 1,2,3
Associate 1,2
and the combo boxes associate with these fields are:
productnum
date1-date2
Defect
associateid
respectively, and the search command button is cmdfindrecords.


Option Compare Database
Option Explicit
Dim cboOriginator As ComboBox


Private Sub cmdfindrecords_Click()
Dim strFilter As String, strOldFilter As String

strOldFilter = Me.Filter

If Me!productnum > "" Then strFilter = strFilter & " AND ([Part Number]=" & Me!productnum & ")"


strFilter = "If Me!defect > "" Then strFilter = strFilter & " And ([Defectcode] = " & Me!Defect & ") & "WHERE '" & Me.Defect & "' In([Defect Code 1], [Defect Code 2], [Defect Code 3])"


strFilter = "If Me!associateid > "" Then strFilter = strFilter & " And ([associates] = " & Me!associateid & ") & "WHERE '" & Me.associateid & "' In([associate], [associate 2])"

strFilter = "SELECT [CreationDate]" & "FROM Master Table" & "WHERE [CreationDate] Between #date1# And #date2#"



If strFilter > "" Then strFilter = Mid(strFilter, 6)
If strFilter <> strOldFilter Then
Me.Filter = strFilter
Me.FilterOn = (strFilter > "")
End If


End Sub

 

[f430s]

i was thinking of having this search form at least require one of the search criteria to be filled out. so i guess if nothing is filled out, the search should return all my data.

by the way, i am a little unsure about the codes for the defects and the associate, because i want the search to filter three or two columns. also, i dont think i defined "defectcode" or "associates" correctly

thanks

 

[dhookom]

I don't care for single line IF statements.
You are treating every field as if it is numeric. Aren't any of these values text?
="" doesn't generally work since "" is not the same as Null.

I would re-write the code using syntax like:

strFilter = "1=1 "
If Not IsNull(Me!productnum) Then
    strFilter = strFilter & " AND ([Part Number]=" & Me!productnum & ") "
End If

If [Part Number] is text, you would need to write:

strFilter = "1=1 "
If Not IsNull(Me!productnum) Then
    strFilter = strFilter & " AND ([Part Number]=""" & Me!productnum & """) "
End If

Duane
Hook'D on Access
MS Access MVP

 

[f430s]

the defect code 1,2,3 and associate 1,2 are text.
but the codes i have right now dont seem to be working, can i get some help on writing the codes for these two.

thanks

 

[dhookom]

There are quite a few issues with your code. You should really try starting very simple with one filter control and then expand as you begin to understand.

Try code like:

Private Sub cmdfindrecords_Click()
   Dim strFilter As String, strOldFilter As String

   strOldFilter = Me.Filter
   strFilter = "1=1 "
   If Me!productnum & "" > "" Then
      strFilter = strFilter & " AND [Part Number]=" & Me!productnum & " "
   End If
   IF Me!defect & "" > "" Then
      strFilter = strFilter & " And """ & Me!Defect & _
         """ In([Defect Code 1], [Defect Code 2], [Defect Code 3]) "
   End If
' etc you should be able to figure out the associate

   If Not IsNull(Me.Date1) Then
      strFilter = strFilter & " AND CreationDate >=#" & Me.Date1 & "# "
   End If
   If Not IsNull(Me.Date2) Then
      strFilter = strFilter & " AND CreationDate <=#" & Me.Date2 & "# "
   End If

     
   If strFilter <> "1=1 " Then
      Me.Filter = strFilter
      Me.FilterOn = True
   End If
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[f430s]

thank you Duane,
but whenever i search using either the product number or the date range, nothing happens and i get no data.
if i search using the associate, or the defect, i get a message asking me to enter a parameter value. i am not exactly sure why this is happening.

 

[f430s]

by the way should i explicitly format the date to always be in this form "m\/d\/yyyy".
if so do i just add it after my code?

 

[dhookom]

I would add a break point and step through the code. There are tips at faq705-7148. Then add line near the end of the code.

debug.print strFilter

Then, tell us what specific parameter value is asked for since we can't see your screen.

Duane
Hook'D on Access
MS Access MVP

 

[jrbarnett]

Following dhookom's advice, look at faq702-3619 which gives more details on the sort of thing you are attempting to do.

John

 

[f430s]

for the defect it asks to enter the parameter for defect code 3
for associate, it asks to enter for both associate and associate 2.
however for the date, and product number, nothing seems to happen.

 

[dhookom]

Did you attempt to debug? What results do you see as you step through your code?

If you are being prompted for those field names then you apparently don't have those field names in your table.

Duane
Hook'D on Access
MS Access MVP

 

[f430s]

i do have these field names in my table, but i just tried the breakpoint debugging technique, and it only prompted for these values when i made the filter run through this code:

Me.Filter = strFilter

so im guessing there is something wrong with it

 

[f430s]

i entered a defect code and an associate number into my search and used the debugging window, this is what i got:


1=1 And "12 - Assembly - Fails Drop Test" In([Defect Code 1], [Defect Code 2], [Defect Code 3])
1=1 And "12 - Assembly - Fails Drop Test" In([Defect Code 1], [Defect Code 2], [Defect Code 3])
1=1 And "12 - Assembly - Fails Drop Test" In([Defect Code 1], [Defect Code 2], [Defect Code 3])
1=1 And "5" In([associate], [associate 2])
1=1 And "13 - Assembly - Fails Load Test" In([Defect Code 1], [Defect Code 2], [Defect Code 3]) And "5" In([associate], [associate 2])
1=1 AND CreationDate >=#2/8/2010# AND CreationDate <=#8/20/2010#
1=1 AND CreationDate >=#2/8/2010# AND CreationDate <=#8/20/2010#
1=1 AND CreationDate >=#2/8/2010# AND CreationDate <=#8/20/2010#
1=1 AND CreationDate >=#2/8/2010# AND CreationDate <=#8/20/2010#
1=1 And "15 - Dropped Parts - Retainer" In([Defect Code 1], [Defect Code 2], [Defect Code 3]) AND CreationDate >=#2/8/2010# AND CreationDate <=#8/20/2010#
1=1 And "15 - Dropped Parts - Retainer" In([Defect Code 1], [Defect Code 2], [Defect Code 3]) AND CreationDate >=#2/8/2010# AND CreationDate <=#8/20/2010#
1=1 And "12 - Assembly - Fails Drop Test" In([Defect Code 1], [Defect Code 2], [Defect Code 3])
1=1 And "12 - Assembly - Fails Drop Test" In([Defect Code 1], [Defect Code 2], [Defect Code 3]) And "3" In([associate], [associate 2])
1=1 And "13 - Assembly - Fails Load Test" In([Defect Code 1], [Defect Code 2], [Defect Code 3])

 

[dhookom]

Have you looked at your table in design view to confirm the actual field names and data types? Some times developers use caption properties of fields or look up fields which I detest.

This code worked as expected for me:

Private Sub cmdFindRecords_Click()
   Dim strFilter As String, strOldFilter As String

   strOldFilter = Me.Filter
   strFilter = "1=1 "
   If Me!productnum & "" > "" Then 'Part Number is text?
      strFilter = strFilter & " AND [Part Number]='" & Me!productnum & "' "
   End If
   If Me!Defect & "" > "" Then
      strFilter = strFilter & " And """ & Me!Defect & _
         """ In([Defect Code 1], [Defect Code 2], [Defect Code 3]) "
   End If
' etc you should be able to figure out the associate
   If Me.associateid & "" > "" Then
      strFilter = strFilter & " And """ & Me!associateid & _
         """ In([Associate 1], [Associate 2]) "
   End If

   If Not IsNull(Me.Date1) Then
      strFilter = strFilter & " AND CreationDate >=#" & Me.Date1 & "# "
   End If
   If Not IsNull(Me.Date2) Then
      strFilter = strFilter & " AND CreationDate <=#" & Me.Date2 & "# "
   End If

     
   If strFilter <> "1=1 " Then
      Me.Filter = strFilter
      Me.FilterOn = True
    Else
      Me.Filter = strOldFilter
   End If

End Sub

Duane
Hook'D on Access
MS Access MVP

 

[f430s]

in design view of master table:
creationdate = date/time
part number = number
defect code 1,2,3 = text
associate 1,2 = text
i am not sure why it is not working, it keeps asking me to enter the parameter values of the defect code and the associate, when it goes through that code:
me.filter=strfilter.


and one more question, would this code still be valid if i do this:

 If Not IsNull(Me.date1) Then
      strFilter = strFilter & " AND CreationDate >=#" & Me.date1 & "# " & Format(Me!date1, "m/d/yyyy")
   End If
   If Not IsNull(Me.date2) Then
      strFilter = strFilter & " AND CreationDate <=#" & Me.date2 & "# " & Format(Me!date2, "m/d/yyyy")
   End If

Thanks

 

[dhookom]

Thank you.

 

[f430s]

i apologize about that, here is the complete master table details:

ID: AutoNumber
CreationDate: Date/Time
Part Number: Number
Defect Code 1: text
Defect Code 2: text
Defect Code 3: text
Associate: text
Associate 2: text

hopefully this clears things up, please let me know if you need any more details.

Thank You