Search Form Code

[f430]

hi, i have a master table with all my part information, and it has columns with part number, date, 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 and 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 would appreciate any help on this
Thanks

[NeoPa]

Check out Example Filtering on a Form.

When you've had a play, let us know if there is anything you still feel unsure about. The more clear and specific you can be about what you're struggling with, the more quickly and easily we we be able to help you.

[f430]

Thanks NeoPa for the info, the article provides some helpful hints. however i do have some questions about my code.
1) in my search form i have a date range, which is made up of a combo box at each "from" and "to" when they are clicked a calendar pops up and a date can be picked. my question in regards to this is, how can i do a search of my master table of any entry in between these 2 dates? because i believe that the code u provided above was only meant to pick the exact date entered.

2)in my second combo box, im trying to filter my master table according to the defect entered into the search. however in my master table i have 3 columns for defects (labeled defect 1, 2, 3)since some parts have more than one defect. how can i make my code search all 3 columns for the defect entered into the search field?
would it be something like this:

If Me!Defect > "" Then _
     strFilter = strFilter & _
     " AND ([Defect Code 1, defect code 2, defect code 3]=" & _
     Me!Defect & ")"

thanks

[f430]

one more thing. i have tried manipulating your code to fit mine, but since im using a command button for my search,nothing really seemed to work out. after i tried to fix everything to on click and saved, exited and ran the code. but it didnt work

[NeoPa]

f430: 1) in my search form i have a date range, which is made up of a combo box at each "from" and "to" when they are clicked a calendar pops up and a date can be picked. my question in regards to this is, how can i do a search of my master table of any entry in between these 2 dates? because i believe that the code u provided above was only meant to pick the exact date entered.

There is a Between X And Y construct in a SQL WHERE clause or filter. EG :

...
WHERE [DateField] Between #1/1/2009# And #12/31/2009#

f430: 2)in my second combo box, im trying to filter my master table according to the defect entered into the search. however in my master table i have 3 columns for defects (labeled defect 1, 2, 3)since some parts have more than one defect. how can i make my code search all 3 columns for the defect entered into the search field?
would it be something like this:

If Me!Defect > "" Then _
strFilter = strFilter & _
" AND ([Defect Code 1, defect code 2, defect code 3]=" & _
Me!Defect & ")"

This is a good reason for not storing your data that way of course, but you could use (if exact matches were being looked for) :

strSQL = "SELECT ..." & _
         "WHERE '" & Me.DefectCode & "' In([defect code 1], _
                                           [defect code 2], _
                                           [defect code 3])

[NeoPa]

f430: one more thing. i have tried manipulating your code to fit mine, but since im using a command button for my search,nothing really seemed to work out. after i tried to fix everything to on click and saved, exited and ran the code. but it didnt work

I can't really comment or advise on where you went wrong unless you show me what you've done. I'm clever I know, but not that clever :D

[f430]

i keep getting an error when using this

strSQL = "SELECT ..." & _ "WHERE '" & Me.Defect & "'In([defect code 1], _ [defect code 2], _ [defect code 3])

once i write this statement and move to another line, it highlights [defect code 2] and says compile error:expected: end of statement.

also, im not sure what comes before the code you provided for the date range. and i am also assuming that this would look something like this:

WHERE [DateField] Between #fromcombobox# And #tocombobox#

i will post my complete code after i solve the current issues.

Thank you for your help.

[NeoPa]

As the underline character (_) indicates line continuation, it is not valid to use it as you have within code on a line.

As for your Between code :

WHERE [DateField] Between #fromcombobox# And #tocombobox#

This is not quite correct as dates should be formatted properly for use in SQL (See Literal DateTimes and Their Delimiters (#)). It is pretty close otherwise though. It may even be that you have explicit formatting for the two ComboBoxes which is compatible with SQL. In that case it would not be so wrong to use the code you've posted (Personally I'd always reformat it explicitly, but that's a choice if the code is already working).

[f430]

i have defined my from box as "date1" and to box as "date2"
i am not really sure what you mean by explicitly formatting these two boxes.
i was also wondering what kind of code i should use before the where statement.

Thanks

[f430]

this is the code that i currently have, but it is giving me a lot of problems. i also have not included the code for my date range, because i am not quite sure how to build it.

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 = "SELECT ..." & _
             "WHERE '" & Me.Defect & "' In([Defect Code 1], [Defect Code 2], _ [Defect Code 3])"
 
 
strFilter = "SELECT ..." & _
             "WHERE '" & Me.associateid & "' In([associate], [associate 2])"
 
 
If strFilter > "" Then strFilter = Mid(strFilter, 6)
If strFilter <> strOldFilter Then
      Me.Filter = strFilter
      Me.FilterOn = (strFilter > "")
End If
 
 
End Sub

when i search using the part number, nothing happens.
when i search using either associate number or defect, i get an error msg: u cant assign a value for this object, and when i go to debug it highlights this code:

Me.Filter = strFilter

[NeoPa]

f430: i am not really sure what you mean by explicitly formatting these two boxes.

I suggest you look at the article I linked you to in post #8 then. It is explained fully there.

f430: i was also wondering what kind of code i should use before the where statement.

That would be a standard SQL query string. If this is something you're not familiar with then we may need a crash course on the very basics.

Queries in Access are actually QueryDef objects. These can be saved as you know, and run, and edited etc.

At the heart of every QueryDef there is the fundamental query instructions. These are written in SQL code and can be found, when editing a QueryDef, by selecting SQL View from the View menu. Such SQL code can form the basis of a QueryDef object, but there are also ways of executing SQL code from a simple string variable (or literal) from within VBA.

To learn more about what SQL can do for you look in the Help system (Finding Jet SQL Help).

The (almost) most basic form of a SQL instruction would be to display the contents of a table and goes like :

SELECT *
FROM   [TableName]

The SQL of a QueryDef is updatable. This can prove important.

[NeoPa]

Having seen your code some things become clearer to me.

Firstly, you are working on a filter string. Filter strings are essentially the WHERE clause of a full SQL statement, but without the word "WHERE ". The SELECT clause is equally unnecessary when dealing with filters (By the way, SELECT ... was just my use of the ellipsis (...) to indicate whatever you had before here. I wasn't suggesting the literal use of the dots).

You still have an underline character (_) on your line #13 which is an error (See post #8).

When building a filter string, it is advisable (generally) to start from scratch unless you know what you can expect it to contain already. It appears that your code would go around and around making the filter string longer and longer re-applying filtering on the same set of fields. Not good for you.

[f430]

NeoPa, i appreciate all the help.
i apologize for not keeping up with you, but my skills in access/vba are very basic. i am having some problems building a query string, would you be able to provide more information about building a standard sql string.

i fixed the minor errors and the here is what it looks like

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])"

Thanks

[NeoPa]

I must rush for now, but try looking more carefully at the code in the article linked in post #2. I can go into more detail later, but trust me, your understanding will benefit from a bit more looking at it. Even if you still need further explanation, at least when that comes you'll be able to put what you read into context. Those "aaaaaah" moments are really quite precious. I'd hate you to miss out ;)

PS. I know it probably all looks like a foreign language at the moment, but I expect it will become clearer soon.

[f430]

my date1 and date2 comboboxes are formatted as short dates in the properties of the combo box, so i guess they are explicitly formatted, and the code im trying to use is :

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

however, i am a little unsure about the codes on line 7 and 10 above, because i have not really defined what "defectcode" and "associates" are. and i keep getting them highlighted when i debug

[NeoPa]

f430: my date1 and date2 comboboxes are formatted as short dates in the properties of the combo box, so i guess they are explicitly formatted, and the code im trying to use is :

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

Absolutely not! That is not only implicitly formatted but it will certainly start to cause problems if you ever try to run it on a machine set up other than for North America, which almost coincidentally has a short date format similar to the SQL standard. I can't see how you could say that if you'd read the article I linked you to.

f430: however, i am a little unsure about the codes on line 7 and 10 above, because i have not really defined what "defectcode" and "associates" are. and i keep getting them highlighted when i debug

This sounds like a joke. Why would you think it could possibly work if you have references to non-existent items?

Have you even looked at the articles I linked you to? There is no indication of it in your comments. I feel like a man trying to help someone drowning but every time I throw the rope in the water you throw it back. You want to be rescued by a boat.

I'm happy to continue trying if you are, but we need to get past asking questions that are this obvious. References to non-existent items are going to cause a problem. Don't ask me to save you from that. Create the items before doing your test. Dates need to be formatted explicitly for use within SQL. This is a SQL standard. You can write code that doesn't do this. It will probably work for you if North America is your whole world, but it is not what I suggest for reasons of portability and the difficulty of finding the problem when it does happen.

[NeoPa]

Try this. It depends on the items being correctly named and, above all, existing, but the basic format should be about right :

strFilter = ""
If Not IsNull(Me.ProductNum) Then _
    strFilter = strFilter & " AND " & _
                "([Part Number]=" & Me.ProductNum & ")"
 
If Not IsNull(Me.Defect) Then _
    strFilter = strFilter & " AND " & _
                "(" & Me.Defect & " In([Defect Code 1]," & _
                                      "[Defect Code 2]," & _
                                      "[Defect Code 3])"
 
If Not IsNull(Me.AssociateID) Then _
    strFilter = strFilter & " AND " & _
                "(" & Me.AssociateID & " In([Associate]," & _
                                           "[Associate 2])"
 
If strFilter > "" Then strFilter = Mid(strFilter, 6)
Me.Filter = strFilter
Me.FilterOn = (strFilter > "")

PS. It assumes that Me.ProductNum, Me.Defect and Me.AssociateID are numeric (as indicated by your code). If this is not the case then we need to know.

[f430]

i did read your articles, i just didn't know how to define these variables since they were not in any of my fields in my master table. i apologize for the trouble.
i did make some modifications to my code, because some were text fields and i accidentally put in a code for a number. i have also changed the date code to something hopefully better.
however i still wasn't able to get it to work.
here is my code for your review:

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
 
   If Me!associateid & "" > "" Then
      strFilter = strFilter & " And """ & Me!associateid & _
         """ In([Associate], [Associate 2]) "
    End If
 
    If Not IsNull(Me.date1) Then
      strFilter = strFilter & " AND CreationDate >=#" & Format(Me!date1, "m/d/yyyy") & "# "
   End If
   If Not IsNull(Me.date2) Then
      strFilter = strFilter & " AND CreationDate <=#" & Format(Me!date2, "m/d/yyyy") & "# "
   End If
 
   If strFilter <> "1=1 " Then
      Me.Filter = strFilter
      Me.FilterOn = True
    Else
      Me.Filter = strOldFilter
   End If
 
Debug.Print strFilter
End Sub 

now whenever i try to search using the part number or the date range, nothing really happens.
but if i use the defect or the associate number, i get a message that asks me to enter a parameter value for defect code 3 and associate 1,2.
i tried debugging and only when the filter runs through the code below, that the search form asks me to enter the parameter values

 Me.Filter = strFilter

Thanks

[NeoPa]

It's hard to work with you because you don't respond to what I post. You just keep starting again from where you are and I can never tell what you're talking about or where you're at in your head. I never know how much of what I've already said I need to repeat. Basically, it's very frustrating trying to communicate with someone who doesn't respond to you.

I get the feeling that you're doing your best, but you don't seem to understand simple communication.

Your latest code is wrong in so many ways. I do not wish to spend five minutes on every point as that will take up so much of my time. I spent time earlier providing a good basic set of code that would work for you assuming all you'd told me so far was reliable, but instead of going from there and explaining what is still not working, you ignore that and go back to your code which is so full of holes I can't even comment on them all.

Please stop ignoring what I post. It makes me feel like I'm wasting my time. I don't post it without reason. This can help you a long way forward if you'll let it. It won't if you ignore it though.

PS.

NeoPa: PS. It assumes that Me.ProductNum, Me.Defect and Me.AssociateID are numeric (as indicated by your code). If this is not the case then we need to know.

Posting a whole set of different and non-workable code is not letting me know.

[NeoPa]

As I now have some information about another bit of the filtering I can add that into my suggested code :

strFilter = ""
If Not IsNull(Me.ProductNum) Then _
    strFilter = strFilter & " AND " & _
                "([Part Number]=" & Me.ProductNum & ")"
 
If Not IsNull(Me.Defect) Then _
    strFilter = strFilter & " AND " & _
                "('" & Me.Defect & "' In([Defect Code 1]," & _
                                        "[Defect Code 2]," & _
                                        "[Defect Code 3])"
 
If Not IsNull(Me.AssociateID) Then _
    strFilter = strFilter & " AND " & _
                "('" & Me.AssociateID & "' In([Associate]," & _
                                             "[Associate 2])"
 
If Not IsNull(Me.Date1) Then
    strFilter = strFilter & " AND ([CreationDate]"
    If Not IsNull(Me.Date2) Then
        strFilter = strFilter & _
                    " Between " & Format(Me.Date1, "\#m/d/yyyy\#") & _
                    " And " & Format(Me.Date2, "\#m/d/yyyy\#") & ")"
    Else
        strFilter = strFilter & _
                    "=" & Format(Me.Date1, "\#m/d/yyyy\#") & ")"
    End If
End If
 
If strFilter > "" Then strFilter = Mid(strFilter, 6)
Me.Filter = strFilter
Me.FilterOn = (strFilter > "")

I have changed my assumption about the Defect Code and Associate ID fields from looking at your latest code posted. I'm guessing you have some reason for doing it that way. Again. I'm only guessing as the information I'm working with is so poor. Please let me know if what the actual types of the fields are. At least I can be reasonably sure that [CreationDate] is DateTime.

If you want help on how to deal with literal strings in VBA and SQL check out Quotes (') and Double-Quotes (") - Where and When to use them.

[Michael Adams]

If you two do not mind my input, here it is.
I have a "Search Form" that asks the user for given information, either user input or selection from a combo box. I then search the "Literial" string from the "Search Form" and compare it to the Master Table. If there is a match between the Search Form and the Master Table the results are populated to a new form that shows all the information in the (single/multiple) records from the table. This "Display Form" has text boxes that are controled by the master table that it is linked to.
You can follow the progression from question to answer in the following post:
http://bytes.com/topic/access/answer...ed#post3594080

As to your getting the "Parameter" box, as you will note in the post we also recieved those but the final code eliminated the pop up and returned the correct records.

Thanks for letting me post to this post, and I hope it helps.

I am working on the searching the from date and the to date also, should be fairly easy now that I have this code.
I will be posting that code in the other post tomorrow, but will also post here if you like.

Thanks, MJA

[NeoPa]

You're welcome to jump in of course Michael. I wouldn't recommend linking across to that thread though. The OP is at a very basic level and the thread rambles on for ever with very little progress.

You, on the other hand, seem to have a bit more experience, so I'd point you to the article I linked to in post #2 of this thread. You may find it answers some of your questions.

[f430]

I apologize about that again. i just thought starting a new code would maybe eliminate some of the errors i had. but i will just stick with the old code.
i will provide the master table detail below
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

i will try out my code and let you know.
Thank you for your help

[Michael Adams]

Might I suggest that you do one piece at a time? For instance just create a real quick form with a single text box for your part number and put a command button on that same form. Use the command button wizard to open your final form with the information you want diplayed after the search.

Thanks,
MJA

[NeoPa]

Thank you for this post. I now know I'm not working in a vacuum. I feel better already.

This confirms my guesses, so I would be interested in hearing if the code I suggested in post #20 works for you. It should do.

If it does fail, can you say what you see, giving line numbers and/or error codes. That would be a great help.

[f430]

i got a error message saying that i was missing a ), or ].
but i spotted the missing one and added it for both the associate and the defect code

 If Not IsNull(Me.associateid) Then _
       strFilter = strFilter & " AND " & _
          "('" & Me.associateid & "') In([Associate]," & "[Associate 2])"

but now whenever i try to search for the defect code, or the associate, it asks me to enter the parameter value for defect code 3, associate, and associate 2. if i don't enter in any data and cancel, i get run-time error '2001' "you canceled the previous operation". i then go to debugging and it highlights

 Me.Filter = strFilter

i tried debugging and whenever i move below the code above, it gives me an error msg saying run-time errot '3075' syntax error (missing operator) and it refers to the defect and associate code.

also whenever i try to search using either the date or the part number, nothing seems to happen.

[NeoPa]

Good attempt :) You had the right idea, but the wrong place to put it.

Here is a fixed version of one of them. I'm sure you can fix the other yourself.

 If Not IsNull(Me.associateid) Then _
       strFilter = strFilter & " AND " & _
          "('" & Me.associateid & "' In([Associate],[Associate 2]))"

NB. You don't need to concatenate two simple strings together if they're on the same line. You can just make it a longer string.

Let me know how you get on with this after you've fixed both in your code.

[f430]

after i fixed the code, and try to search, it keeps asking me to enter parameter values for the following:
defect code 3
associate
associate 2
and i went into my code and created a breakpoint at the code provided in post #26 (the second one)
and when i run the filter through it it gives me run-time error '3075' syntax error (missing operator) for both the associate and the defect code.
now im really confused.

Thank You

[Michael Adams]

Try this, create a variable for each. Then use this code after your dim statements.

If Not isnull(Me.your text/combo box that holds your literial string) Then 
 your variable here = "[field on table]=""" & Me!your text/combo box that holds your literial string & """"

This should match the literal string from your filter and the string your table holds.

Now after this statement your variable will now hold the exact match on your table, you will need to call this variable in the correct place.

If this is confusing maybe NeoPa can help straighten it out. What I think is happening is that you have more than one unique entry in each field you are searching, that is why it is asking you for parameters. I believe you will find it working if you input the same string into the parameters that you input into each text box on your filter.

This is why I always work on one piece of code first. It makes debugging problems much easier and also makes the coding process less confussing(less thing to go wrong).

Just a thought.
MJA

[NeoPa]

Why don't you post the whole, exact (Copy / Paste) procedure in here and I'll look at it for you. I need to see what you're using to work out what might be wrong as I know you're not using my code as I posted it from your other posts (though why that is, at this stage, is beyond me I must admit).

[Michael Adams]

This is what I have.'

Private Sub Bank()
'This sub is to search the table workordertracking for the criteria that is in the drop down box
 
'This is the variable for the form to be opened and populated
Dim BnkDocName As String
'This is the variable for the matching strings
Dim BnkLinkCriteria As String
 
'This is to assign the form "SubSearch" to the variable stdocName
'This will be the form which will be populated with the information in the form
BnkDocName = "SubSearch"
 
'me.combo5 is the combo box that is controled by the table "Bank" in the properties for the combo5 box
If Not IsNull(Me.Combo5) Then
    'This is how the literial strings are assigned to the variable which are matched from
    'the field in the table and the combo box on the search form
    BnkLinkCriteria = "[bank]=""" & Me!Combo5 & """"
End If
'This is the message box if the combo box is left empty
If IsNull(Me!Combo5) Then
    MsgBox ("Please select a Bank from the drop down...")
Else
    'This is the opening of the form and the criteria match
 
    'When the form is opened it will be populated with the information from
    'the controled source in each text box on the form
    DoCmd.OpenForm BnkDocName, , , BnkLinkCriteria
End If
End Sub

I call this in a click() event.

[f430]

here is my full code

Option Compare Database
Option Explicit
Dim cboOriginator As ComboBox
 
 
Private Sub cmdfindrecords_Click()
   Dim strFilter As String, strOldFilter As String
 
 
   strFilter = ""
    If Not IsNull(Me.productnum) Then _
       strFilter = strFilter & " AND " & _
          "([Part Number]=" & Me.productnum & ")"
 
   If Not IsNull(Me.Defect) Then _
       strFilter = strFilter & " AND " & "('" & Me.Defect & "' In([Defect Code 1]," & "[Defect Code 2]," & "[Defect Code 3]))"
 
   If Not IsNull(Me.associateid) Then _
       strFilter = strFilter & " AND " & "('" & Me.associateid & "' In([Associate]," & "[Associate 2]))"
 
 
 
  If Not IsNull(Me.date1) Then
       strFilter = strFilter & " AND ([CreationDate]"
  If Not IsNull(Me.date2) Then
       strFilter = strFilter & _
                " Between " & Format(Me.date1, "\#m/d/yyyy\#") & _
                " And " & Format(Me.date2, "\#m/d/yyyy\#") & ")"
  Else
       strFilter = strFilter & _
                "=" & Format(Me.date1, "\#m/d/yyyy\#") & ")"
  End If
  End If
 
  If strFilter > "" Then strFilter = Mid(strFilter, 6)
      Me.Filter = strFilter
      Me.FilterOn = (strFilter > "")
 
 
 
 
Debug.Print strFilter
End Sub
 
 
Private Sub date1_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
 
Set cboOriginator = date1
calendar.Visible = True
calendar.SetFocus
 
If Not IsNull(cboOriginator) Then
   calendar.Value = cboOriginator.Value
Else
   calendar.Value = "DATE"
End If
 
 
End Sub
 
 
Private Sub date2_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
 
Set cboOriginator = date2
calendar.Visible = True
calendar.SetFocus
 
If Not IsNull(cboOriginator) Then
   calendar.Value = cboOriginator.Value
Else
   calendar.Value = "DATE"
End If
 
End Sub
 
Private Sub calendar_Click()
 
cboOriginator.Value = calendar.Value
cboOriginator.SetFocus
calendar.Visible = False
Set cboOriginator = Nothing
 
End Sub
Private Sub exit_Click()
On Error GoTo Err_exit_Click
 
 
    DoCmd.Close
 
Exit_exit_Click:
    Exit Sub
 
Err_exit_Click:
    MsgBox Err.Description
    Resume Exit_exit_Click
 
End Sub
 
 

[NeoPa]

Sorry Michael. I should have been clearer that I was addressing f430. I'm more than happy to help you on your quest if you would like me to, but not in this thread. This thread is owned by f430's question I'm afraid.

[Michael Adams]

No questions, My code works for comparing literal strings against the table. Before I used this code I was getting the same parameter thing f430 is getting. I believe it is looking at what the user is inputting and notices that there are different values in the field and is asking for wich one the user wants returned.

[NeoPa]

Sorry if there was any misunderstanding Michael. There is no reason at all why you should not post anything you feel may be helpful (I'd be horrified if I thought, that you thought, that I was saying otherwise).

As it happens, I'm very grateful for your latest post as it brough f430's post to my attention that I'd missed when last I'd posted :)

@f430:
Nice. I'm leaving for home shortly, but that is more than I asked for and will do very nicely.

[Michael Adams]

I was not offended at all, I was just clarifying. I just hope that I could be of help cause I was running into the same problems that f430 has been running into.

[NeoPa]

f432: after i fixed the code, and try to search, it keeps asking me to enter parameter values for the following:
defect code 3
associate
associate 2

That indicates to me that none of those fields exist, or are actually called what you've called them. Check the names of the fields in whatever is the record source of your form.

f432: and i went into my code and created a breakpoint at the code provided in post #26 (the second one)

That would be post #20 line #30 I assume.

f432: and when i run the filter through it it gives me run-time error '3075' syntax error (missing operator) for both the associate and the defect code.

This is consistent with your first point about the three missing fields.

A breakpoint is a good idea, but in this case what would be more helpful (if the earlier instruction doesn't help you find the problem anyway) would be to move line #42 before line #36 (from your post #32), then post a copy of the data printed off when this line runs. I will look at it for you if we need to go that far.

[f430]

1) i went into my master table details and checked that everything had the same field name, i also checked the relationships between the tables (master table and products table, master and defects, master and associate), and everything was looking ok. then i went into my search form and checked that all the combo boxes were referring to the right field.
so im pretty sure everything was defined correctly.

2) you are correct, it is post#20 line #30, here is the code:

Me.Filter = strFilter

3) i moved the debugging code before the code above, and ran the filter.
by saying "copy the data printed off" i am assuming you mean the statement that comes up in the immediate window.
anyways, i ran the code and after i passed the debugging code, this message was displayed on my immediate window:

('1 - Rusty Retainers' In([Defect Code 1],[Defect Code 2],[Defect Code 3])) AND ('6' In([Associate],[Associate 2]))

where 1-Rusty Retainers is referring to one of the defects, and im guessing 6 is referring to one of the associate id for an associate number.

i tried to be as descriptive as possible, if you need more information please let me know.

Thank you for your help

[NeoPa]

f430: i tried to be as descriptive as possible, if you need more information please let me know.

You're certainly learning. This is much more pleasant to work with :)

The string is exactly what I was hoping to see. Not only is that precisely what I meant, but now I see it I'm confirmed on it being formatted perfectly.

If this is causing "Enter parameter Values" prompts still, then something somewhere is wrong with your database, and it's not the code.

With this in mind (and assuming that is still the case) it seems as if it might be time to attach a working (or in this case not working) copy of your database for me to look at. I'll include below some instructions I usually post to help you to do this correctly with the fewest possible problems.

When attaching your work please follow the following steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
4. If you've done anything in steps 1 to 3 then make sure that the problem you're experiencing is still evident in the updated version.
5. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
6. Compact the database (Tools / Database Utilities / Compact and Repair Database...).
7. Compress the database into a ZIP file.
8. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.

[f430]

for some reason whenever i try to put my access file into the zipped folder, the search form does not show in the regular view. however if the search form is in the design view it has everything.
i followed all the steps, but thats what happens when i try to compress it.
any ideas??

[NeoPa]

f430:
for some reason whenever i try to put my access file into the zipped folder, the search form does not show in the regular view. however if the search form is in the design view it has everything.

I do not understand what you're talking about here. How does Zipping a file have any bearing whatsoever on what happens when you run it? How can you be running it while you are zipping it? This makes no sense to me at all.

Try to explain, step-by-step, what you are trying and what goes wrong at which point. The following questions may help.

Which points of my post have you completed successfully? Which point do you get stuck on? What do you notice is wrong at that point?

[f430]

i was able to complete all the steps correctly, except step #5 because when i went into my visual basic editor, that option was not available(i wasn't able to select it).
and what i mean about the form is, i checked the access file before i compress it, and everything seems to open and work correctly. however when i compress it in a zipped folder, i open the file and my search form is blank in regular view, but it has all the boxes in the design view.

[f430]

i think i figured out what is wrong with my database, and it definitely isn't my code.
the problem is that my search form is based on a search query. i went into my search query, and it didnt include defect code 3, associate, and associate 2. and that is why every time i searched, it asked me to enter parameter values. and i believe if i can get the correct sql into my search query, my code will work. here is what i have in my search query now, pls let me know how i can fix it.

SELECT [Master Table].ID, [Master Table].[Defect Code 1], [Master Table].CreationDate, [Master Table].[Defect Code 2], [Master Table].[Quantity Defective 2], [Master Table].[Part Number], [Master Table].[Defect Code 3], [Master Table].Associate, [Master Table].[Associate 2]
FROM [Master Table]
WHERE ((([Master Table].[Defect Code 1])=Forms!search!Defect1) And (([Master Table].CreationDate) Between Forms!Search!date1 And Forms!Search!date2) And (([Master Table].[Part Number])=Forms!Search![Product name])) Or ((([Master Table].[Defect Code 2])=Forms!Search!Defect1));
 

as you can see i have added the missing search criteria (defect code 3, associate and associate 2) and now when i search, i do not get any errors but my search form doesnt return any data.

Thanks

[NeoPa]

f430:
i was able to complete all the steps correctly, except step #5 because when i went into my visual basic editor, that option was not available(i wasn't able to select it).

Unless you're using 2007 (which would be pointless anyway after point #2) I cannot see why you might be having difficulty compiling the project. Was the option simply hidden due to not having been used recently. Office menus do that.

f430:
and what i mean about the form is, i checked the access file before i compress it, and everything seems to open and work correctly. however when i compress it in a zipped folder, i open the file and my search form is blank in regular view, but it has all the boxes in the design view.

I'm not sure why you would do that. No-one is likely to worry if it doesn't work from a zipped folder. As long as it works when unzipped, that is all that matters.

[NeoPa]

f430:
i think i figured out what is wrong with my database, and it definitely isn't my code.
the problem is that my search form is based on a search query. i went into my search query, and it didnt include defect code 3, associate, and associate 2. and that is why every time i searched, it asked me to enter parameter values. and i believe if i can get the correct sql into my search query, my code will work.

That is exactly right. Very much as I suspected, but I would have needed the database itself to say exactly where etc. It's a very good sign that you worked this out yourself. Well done.

f430:
here is what i have in my search query now, pls let me know how i can fix it.

as you can see i have added the missing search criteria (defect code 3, associate and associate 2) and now when i search, i do not get any errors but my search form doesnt return any data.

You seem to have changed direction with the SQL. I gave you detailed code on how to handle that dynamically, but now you want to use fixed code. That approach is much less flexible and also much more difficult to get right.

Is there a good reason why you have decided to start again on a different track? Essentially throwing away all the time and effort already expended on this.

[f430]

compiling the project was greyed out, but that was just because i was into the search section, and not the whole code. i was able to get it to work.
please let me know if you still need me to attach the file based on my last response.

[NeoPa]

f430:
please let me know if you still need me to attach the file based on my last response.

I assume you are now reading my latest post. I think you've solved the main confusion, but I'm awaiting your response to know if we're still working together on this.

[f430]

i wasn't trying to not use what i learned, i just had this sql before i fixed my code, and i was not sure if this query should have the same format as the code i used.
should i have if statements like in post# 32 line#15??

[NeoPa]

Basically yes.

That is to say the query should be unfiltered, and the filter applied as it seems to be in post #32. Frankly I can only guarantee it should be like post #20, probably including any subsequent fixes if errors were found (it was all a long time ago now), but if you feel confident your changed code does the same job, then go with that if you prefer.

You should understand that when a filter is applied retrospectively (as we plan to do here from the code in the form), then the item being filtered should fundamentally include all that we may be interested in looking at. If it is already filtered then it is likely that aplying a second, separate filter, will leave nothing displayed.

[f430]

so this is gonna be a copy of the code, but instead of "if" we use "SELECT" or "procedure", because it seems like it doesnt accept if or any quotation marks or at least not in the beginning of the code. it keeps saying that it is expecting "select", "update" , " insert" or "procedure"