469,352 Members | 1,685 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,352 developers. It's quick & easy.

Search Form Code

f430
43
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
Aug 9 '10 #1
61 3403
NeoPa
32,184 Expert Mod 16PB
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.
Aug 9 '10 #2
f430
43
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:

Expand|Select|Wrap|Line Numbers
  1. If Me!Defect > "" Then _
  2.      strFilter = strFilter & _
  3.      " AND ([Defect Code 1, defect code 2, defect code 3]=" & _
  4.      Me!Defect & ")"
thanks
Aug 9 '10 #3
f430
43
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
Aug 9 '10 #4
NeoPa
32,184 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. ...
  2. 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:

Expand|Select|Wrap|Line Numbers
  1. If Me!Defect > "" Then _
  2. strFilter = strFilter & _
  3. " AND ([Defect Code 1, defect code 2, defect code 3]=" & _
  4. 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) :

Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT ..." & _
  2.          "WHERE '" & Me.DefectCode & "' In([defect code 1], _
  3.                                            [defect code 2], _
  4.                                            [defect code 3])
Aug 9 '10 #5
NeoPa
32,184 Expert Mod 16PB
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
Aug 9 '10 #6
f430
43
i keep getting an error when using this
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. WHERE [DateField] Between #fromcombobox# And #tocombobox#

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

Thank you for your help.
Aug 10 '10 #7
NeoPa
32,184 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. 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).
Aug 10 '10 #8
f430
43
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
Aug 10 '10 #9
f430
43
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdfindrecords_Click()
  2.    Dim strFilter As String, strOldFilter As String
  3.  
  4.    strOldFilter = Me.Filter
  5.  
  6. If Me!productnum > "" Then _
  7.      strFilter = strFilter & _
  8.      " AND ([Part Number]=" & _
  9.      Me!productnum & ")"
  10.  
  11.  
  12. strFilter = "SELECT ..." & _
  13.              "WHERE '" & Me.Defect & "' In([Defect Code 1], [Defect Code 2], _ [Defect Code 3])"
  14.  
  15.  
  16. strFilter = "SELECT ..." & _
  17.              "WHERE '" & Me.associateid & "' In([associate], [associate 2])"
  18.  
  19.  
  20. If strFilter > "" Then strFilter = Mid(strFilter, 6)
  21. If strFilter <> strOldFilter Then
  22.       Me.Filter = strFilter
  23.       Me.FilterOn = (strFilter > "")
  24. End If
  25.  
  26.  
  27. 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:
Expand|Select|Wrap|Line Numbers
  1. Me.Filter = strFilter
Aug 10 '10 #10
NeoPa
32,184 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM   [TableName]
The SQL of a QueryDef is updatable. This can prove important.
Aug 10 '10 #11
NeoPa
32,184 Expert Mod 16PB
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.
Aug 10 '10 #12
f430
43
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
Expand|Select|Wrap|Line Numbers
  1. If Me!productnum > "" Then _
  2.      strFilter = strFilter & _
  3.      " AND ([Part Number]=" & _
  4.      Me!productnum & ")"
  5.  
  6.  
  7. strFilter = "If Me!defect > "" Then strFilter = strFilter & " And ([Defectcode] = " & Me!Defect & ") & "WHERE '" & Me.Defect & "' In([Defect Code 1], [Defect Code 2], [Defect Code 3])"
  8.  
  9.  
  10. strFilter = "If Me!associateid > "" Then strFilter = strFilter & " And ([associates] = " & Me!associateid & ") & "WHERE '" & Me.associateid & "' In([associate], [associate 2])"
Thanks
Aug 10 '10 #13
NeoPa
32,184 Expert Mod 16PB
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.
Aug 10 '10 #14
f430
43
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 :
Expand|Select|Wrap|Line Numbers
  1. 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
Aug 11 '10 #15
NeoPa
32,184 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. 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.
Aug 11 '10 #16
NeoPa
32,184 Expert Mod 16PB
Try this. It depends on the items being correctly named and, above all, existing, but the basic format should be about right :
Expand|Select|Wrap|Line Numbers
  1. strFilter = ""
  2. If Not IsNull(Me.ProductNum) Then _
  3.     strFilter = strFilter & " AND " & _
  4.                 "([Part Number]=" & Me.ProductNum & ")"
  5.  
  6. If Not IsNull(Me.Defect) Then _
  7.     strFilter = strFilter & " AND " & _
  8.                 "(" & Me.Defect & " In([Defect Code 1]," & _
  9.                                       "[Defect Code 2]," & _
  10.                                       "[Defect Code 3])"
  11.  
  12. If Not IsNull(Me.AssociateID) Then _
  13.     strFilter = strFilter & " AND " & _
  14.                 "(" & Me.AssociateID & " In([Associate]," & _
  15.                                            "[Associate 2])"
  16.  
  17. If strFilter > "" Then strFilter = Mid(strFilter, 6)
  18. Me.Filter = strFilter
  19. 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.
Aug 11 '10 #17
f430
43
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdfindrecords_Click()
  2.    Dim strFilter As String, strOldFilter As String
  3.  
  4.    strOldFilter = Me.Filter
  5.    strFilter = "1=1 "
  6.  
  7.    If Me!productnum & "" > "" Then
  8.       strFilter = strFilter & " AND [Part Number]=" & Me!productnum & " "
  9.    End If
  10.  
  11.    If Me!Defect & "" > "" Then
  12.       strFilter = strFilter & " And """ & Me!Defect & _
  13.          """ In([Defect Code 1], [Defect Code 2], [Defect Code 3]) "
  14.    End If
  15.  
  16.    If Me!associateid & "" > "" Then
  17.       strFilter = strFilter & " And """ & Me!associateid & _
  18.          """ In([Associate], [Associate 2]) "
  19.     End If
  20.  
  21.     If Not IsNull(Me.date1) Then
  22.       strFilter = strFilter & " AND CreationDate >=#" & Format(Me!date1, "m/d/yyyy") & "# "
  23.    End If
  24.    If Not IsNull(Me.date2) Then
  25.       strFilter = strFilter & " AND CreationDate <=#" & Format(Me!date2, "m/d/yyyy") & "# "
  26.    End If
  27.  
  28.    If strFilter <> "1=1 " Then
  29.       Me.Filter = strFilter
  30.       Me.FilterOn = True
  31.     Else
  32.       Me.Filter = strOldFilter
  33.    End If
  34.  
  35. Debug.Print strFilter
  36. 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
Expand|Select|Wrap|Line Numbers
  1.  Me.Filter = strFilter
Thanks
Aug 11 '10 #18
NeoPa
32,184 Expert Mod 16PB
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.
Aug 11 '10 #19
NeoPa
32,184 Expert Mod 16PB
As I now have some information about another bit of the filtering I can add that into my suggested code :
Expand|Select|Wrap|Line Numbers
  1. strFilter = ""
  2. If Not IsNull(Me.ProductNum) Then _
  3.     strFilter = strFilter & " AND " & _
  4.                 "([Part Number]=" & Me.ProductNum & ")"
  5.  
  6. If Not IsNull(Me.Defect) Then _
  7.     strFilter = strFilter & " AND " & _
  8.                 "('" & Me.Defect & "' In([Defect Code 1]," & _
  9.                                         "[Defect Code 2]," & _
  10.                                         "[Defect Code 3])"
  11.  
  12. If Not IsNull(Me.AssociateID) Then _
  13.     strFilter = strFilter & " AND " & _
  14.                 "('" & Me.AssociateID & "' In([Associate]," & _
  15.                                              "[Associate 2])"
  16.  
  17. If Not IsNull(Me.Date1) Then
  18.     strFilter = strFilter & " AND ([CreationDate]"
  19.     If Not IsNull(Me.Date2) Then
  20.         strFilter = strFilter & _
  21.                     " Between " & Format(Me.Date1, "\#m/d/yyyy\#") & _
  22.                     " And " & Format(Me.Date2, "\#m/d/yyyy\#") & ")"
  23.     Else
  24.         strFilter = strFilter & _
  25.                     "=" & Format(Me.Date1, "\#m/d/yyyy\#") & ")"
  26.     End If
  27. End If
  28.  
  29. If strFilter > "" Then strFilter = Mid(strFilter, 6)
  30. Me.Filter = strFilter
  31. 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.
Aug 11 '10 #20
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
Aug 11 '10 #21
NeoPa
32,184 Expert Mod 16PB
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.
Aug 11 '10 #22
f430
43
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
Aug 12 '10 #23
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
Aug 12 '10 #24
NeoPa
32,184 Expert Mod 16PB
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.
Aug 12 '10 #25
f430
43
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
Expand|Select|Wrap|Line Numbers
  1.  If Not IsNull(Me.associateid) Then _
  2.        strFilter = strFilter & " AND " & _
  3.           "('" & 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
Expand|Select|Wrap|Line Numbers
  1.  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.
Aug 12 '10 #26
NeoPa
32,184 Expert Mod 16PB
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.
Expand|Select|Wrap|Line Numbers
  1.  If Not IsNull(Me.associateid) Then _
  2.        strFilter = strFilter & " AND " & _
  3.           "('" & 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.
Aug 12 '10 #27
f430
43
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
Aug 12 '10 #28
Try this, create a variable for each. Then use this code after your dim statements.

Expand|Select|Wrap|Line Numbers
  1. If Not isnull(Me.your text/combo box that holds your literial string) Then 
  2. 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
Aug 12 '10 #29
NeoPa
32,184 Expert Mod 16PB
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).
Aug 12 '10 #30
This is what I have.'
Expand|Select|Wrap|Line Numbers
  1. Private Sub Bank()
  2. 'This sub is to search the table workordertracking for the criteria that is in the drop down box
  3.  
  4. 'This is the variable for the form to be opened and populated
  5. Dim BnkDocName As String
  6. 'This is the variable for the matching strings
  7. Dim BnkLinkCriteria As String
  8.  
  9. 'This is to assign the form "SubSearch" to the variable stdocName
  10. 'This will be the form which will be populated with the information in the form
  11. BnkDocName = "SubSearch"
  12.  
  13. 'me.combo5 is the combo box that is controled by the table "Bank" in the properties for the combo5 box
  14. If Not IsNull(Me.Combo5) Then
  15.     'This is how the literial strings are assigned to the variable which are matched from
  16.     'the field in the table and the combo box on the search form
  17.     BnkLinkCriteria = "[bank]=""" & Me!Combo5 & """"
  18. End If
  19. 'This is the message box if the combo box is left empty
  20. If IsNull(Me!Combo5) Then
  21.     MsgBox ("Please select a Bank from the drop down...")
  22. Else
  23.     'This is the opening of the form and the criteria match
  24.  
  25.     'When the form is opened it will be populated with the information from
  26.     'the controled source in each text box on the form
  27.     DoCmd.OpenForm BnkDocName, , , BnkLinkCriteria
  28. End If
  29. End Sub
I call this in a click() event.
Aug 12 '10 #31
f430
43
here is my full code
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Dim cboOriginator As ComboBox
  4.  
  5.  
  6. Private Sub cmdfindrecords_Click()
  7.    Dim strFilter As String, strOldFilter As String
  8.  
  9.  
  10.    strFilter = ""
  11.     If Not IsNull(Me.productnum) Then _
  12.        strFilter = strFilter & " AND " & _
  13.           "([Part Number]=" & Me.productnum & ")"
  14.  
  15.    If Not IsNull(Me.Defect) Then _
  16.        strFilter = strFilter & " AND " & "('" & Me.Defect & "' In([Defect Code 1]," & "[Defect Code 2]," & "[Defect Code 3]))"
  17.  
  18.    If Not IsNull(Me.associateid) Then _
  19.        strFilter = strFilter & " AND " & "('" & Me.associateid & "' In([Associate]," & "[Associate 2]))"
  20.  
  21.  
  22.  
  23.   If Not IsNull(Me.date1) Then
  24.        strFilter = strFilter & " AND ([CreationDate]"
  25.   If Not IsNull(Me.date2) Then
  26.        strFilter = strFilter & _
  27.                 " Between " & Format(Me.date1, "\#m/d/yyyy\#") & _
  28.                 " And " & Format(Me.date2, "\#m/d/yyyy\#") & ")"
  29.   Else
  30.        strFilter = strFilter & _
  31.                 "=" & Format(Me.date1, "\#m/d/yyyy\#") & ")"
  32.   End If
  33.   End If
  34.  
  35.   If strFilter > "" Then strFilter = Mid(strFilter, 6)
  36.       Me.Filter = strFilter
  37.       Me.FilterOn = (strFilter > "")
  38.  
  39.  
  40.  
  41.  
  42. Debug.Print strFilter
  43. End Sub
  44.  
  45.  
  46. Private Sub date1_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  47.  
  48. Set cboOriginator = date1
  49. calendar.Visible = True
  50. calendar.SetFocus
  51.  
  52. If Not IsNull(cboOriginator) Then
  53.    calendar.Value = cboOriginator.Value
  54. Else
  55.    calendar.Value = "DATE"
  56. End If
  57.  
  58.  
  59. End Sub
  60.  
  61.  
  62. Private Sub date2_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  63.  
  64. Set cboOriginator = date2
  65. calendar.Visible = True
  66. calendar.SetFocus
  67.  
  68. If Not IsNull(cboOriginator) Then
  69.    calendar.Value = cboOriginator.Value
  70. Else
  71.    calendar.Value = "DATE"
  72. End If
  73.  
  74. End Sub
  75.  
  76. Private Sub calendar_Click()
  77.  
  78. cboOriginator.Value = calendar.Value
  79. cboOriginator.SetFocus
  80. calendar.Visible = False
  81. Set cboOriginator = Nothing
  82.  
  83. End Sub
  84. Private Sub exit_Click()
  85. On Error GoTo Err_exit_Click
  86.  
  87.  
  88.     DoCmd.Close
  89.  
  90. Exit_exit_Click:
  91.     Exit Sub
  92.  
  93. Err_exit_Click:
  94.     MsgBox Err.Description
  95.     Resume Exit_exit_Click
  96.  
  97. End Sub
  98.  
  99.  
Aug 12 '10 #32
NeoPa
32,184 Expert Mod 16PB
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.
Aug 12 '10 #33
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.
Aug 12 '10 #34
NeoPa
32,184 Expert Mod 16PB
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.
Aug 12 '10 #35
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.
Aug 12 '10 #36
NeoPa
32,184 Expert Mod 16PB
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.
Aug 12 '10 #37
f430
43
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:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. ('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
Aug 12 '10 #38
NeoPa
32,184 Expert Mod 16PB
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.
Aug 12 '10 #39
f430
43
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??
Aug 13 '10 #40
NeoPa
32,184 Expert Mod 16PB
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?
Aug 15 '10 #41
f430
43
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.
Aug 16 '10 #42
f430
43
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.
Expand|Select|Wrap|Line Numbers
  1. 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]
  2. FROM [Master Table]
  3. 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));
  4.  
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
Aug 16 '10 #43
NeoPa
32,184 Expert Mod 16PB
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.
Aug 16 '10 #44
NeoPa
32,184 Expert Mod 16PB
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.
Aug 16 '10 #45
f430
43
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.
Aug 16 '10 #46
NeoPa
32,184 Expert Mod 16PB
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.
Aug 16 '10 #47
f430
43
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??
Aug 16 '10 #48
NeoPa
32,184 Expert Mod 16PB
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.
Aug 16 '10 #49
f430
43
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"
Aug 16 '10 #50

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Mark | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.