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

[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).