Search Form Help Needed

[MG101]

Hi,

I recently just created a search form where users may enter 3 types of criteria, with results showing below in a list box. When a user double clicks the result it brings them to the whole record. For some odd reason when I close out of the database and reopen the search form, My cursor will not click inside the criteria text boxes and If I double click a record below (All records show when no criterias entered) it gives me a runtime error #3075 " Synatx error (Missing Operator) in query expression 'ID='. "

Here is the code that I use to load record after record is double clicked:

Code:
DoCmd.OpenForm "Part Search", , , "ID =" & [Forms]![Search]![SearchResults]

Any ideas on why it might be doing this/how to fix it? Any help is much appreciated!

Thanks!

 

[rzw0wr]

Can you upload your database.

Dale

 

[billmeye]

In the On Load or On Open or On Current etc. events for the Search form do you have any code running? It seems like something is locking it up. Also, as good practice, I always test for null before using variables just in case, i.e.,

If not isnull([Forms]![Search]![SearchResults]) Then
DoCmd.OpenForm "Part Search", , , "ID =" & [Forms]![Search]![SearchResults]
Else
Msgbox "No search terms select"
End If

 

[MG101]

Hi Dale,

Here is the database.

Hi Billmeye,

I tried using your code but it change anything, except I get a message box instead of error now when I click to load record.

I appreciate all the support!

 

[billmeye]

The Macro PartSearchButton is opening the form in read only mode and needs to be changed to open the form in edit mode.

 

[MG101]

That did the trick Billmeye! I feel stupid now. Its been a long day Haha. Thank you so much!

 

[billmeye]

Sometimes a whole days work :banghead: leads to a better day tomorrow.

 

[rzw0wr]

MG,
On your Welcome Window, The Part Search button (command22).
Open the macro and change the data mode from read only to Edit.

Bill types faster than I do.

Dale

 

[MG101]

I also noticed that the part search does not bring up records if they do not have a VIN in their record. Sometimes we get returns with no vins. The query or search results is programmed in some way in which it wont return results that do not have a vin even if you leave the vin search box empty and only search by tracking number. I think it might be the same for date code. Is it possible to change the query or whatever is causing it to allow all search results returned even if they may not contain VIN or Date code?

Thanks for all the help!

PS You can reference the above file.

 

[billmeye]

For the row source of your listbox, change it to:

SELECT [EVAP Database].ID, [EVAP Database].[Tracking Number], [EVAP Database].[Date Code], [EVAP Database].[Car No] FROM [EVAP Database] WHERE ((([EVAP Database].[Tracking Number]) Like "*" & Forms!Search!TrackingNumberSearch & "*") And (([EVAP Database].[Date Code]) Like "*" & Forms!Search!DateCodeSearch & "*" Or ([EVAP Database].[Date Code]) Is Null) And (([EVAP Database].[Car No]) Like "*" & forms!Search!VinSearch & "*" Or ([EVAP Database].[Car No]) Is Null));

This accounts for the possibility of Null values in your Car No and Date Code fields.

 

[MG101]

Hi Billmeye,

Thanks for the help! I replaced the row source with your code, but a "enter Parameter Value" box keeps popping up. The file is attached.

 

[billmeye]

You changed the name of the form from Search to Searc(ViewOnly) and Access was setup to look for Search so when it could not find the form, it prompts for manual input. That's fine, the row source for your Search listbox does not need to reference the form name, replace it with this and it will work fine.

SELECT [EVAP Database].ID, [EVAP Database].[Tracking Number], [EVAP Database].[Date Code], [EVAP Database].[Car No] FROM [EVAP Database] WHERE ((([EVAP Database].[Tracking Number]) Like "*" & [TrackingNumberSearch] & "*") AND (([EVAP Database].[Date Code]) Like "*" & [DateCodeSearch] & "*" Or ([EVAP Database].[Date Code]) Is Null) AND (([EVAP Database].[Car No]) Like "*" & [VinSearch] & "*" Or ([EVAP Database].[Car No]) Is Null));

 

[MG101]

Hi Billmeye,

Thanks for your help! That is almost exactly what I was hoping for! I also want to be able to search even if a tracking number isnt in the record. I tried adding Or ...tracking number...is null (like the other two) but for some reason it did not work. WOuld you mind editing the code to allow the search form to search through ALL records regardless of whether the search criteria is existent or null?

Again THANK YOU SO MUCH! I appreciate all of your help!

MG101

 

[billmeye]

Here ya go:

SELECT [EVAP Database].ID, [EVAP Database].[Tracking Number], [EVAP Database].[Date Code], [EVAP Database].[Car No] FROM [EVAP Database] WHERE ((([EVAP Database].[Tracking Number]) Like "*" & [TrackingNumberSearch] & "*" Or ([EVAP Database].[Tracking Number]) Is Null) AND (([EVAP Database].[Date Code]) Like "*" & [DateCodeSearch] & "*" Or ([EVAP Database].[Date Code]) Is Null) AND (([EVAP Database].[Car No]) Like "*" & [VinSearch] & "*" Or ([EVAP Database].[Car No]) Is Null));

 

[MG101]

Hi Billmeye,

I replaced it with that and it is not searching correctly. (That is what I tried before I asked you). When I replace it, It does show records without tracking numbers but for some reasonwhen i search by tracking number, it wont filter. Also filtering by date code or VIN acts up as well. You can use the file above (5-31-13) to plug that code in and see what I mean. Any Ideas?

Thanks!

 

[billmeye]

Your requirement to include all Null gives odd results. For example, if you want to find TrackingNumber 01-GM-7220-0007, you will see all that are Null AND that tracking number. You may need to scroll down a bit to find it. Then, if you also want to drill down by DateCode, you need both TrackingNumber and DateCode to match which may result in only Tracking Numbers that are Null no longer including 01-GM-7220-0007 since it did not meet your criteria. Maybe if you have it set to sort descending it will look more like what you intend.

SELECT [EVAP Database].ID, [EVAP Database].[Tracking Number], [EVAP Database].[Date Code], [EVAP Database].[Car No]
FROM [EVAP Database]
WHERE ((([EVAP Database].[Tracking Number]) Like "*" & [TrackingNumberSearch] & "*" Or ([EVAP Database].[Tracking Number]) Is Null) AND (([EVAP Database].[Date Code]) Like "*" & [DateCodeSearch] & "*" Or ([EVAP Database].[Date Code]) Is Null) AND (([EVAP Database].[Car No]) Like "*" & [VinSearch] & "*" Or ([EVAP Database].[Car No]) Is Null))
ORDER BY [EVAP Database].[Tracking Number] DESC , [EVAP Database].[Date Code] DESC , [EVAP Database].[Car No] DESC;

 

[MG101]

Hi Billmeye,

That is more like what I wanted. Although when I search by VIN for some reason my results still show up at the bottom (Maybe it needs to be assending for vin?). So it is not possible then to have it show only tracking number instead of tracking number and null? (Same with VIN and Date Code)?

Thanks for your help!

 

[MG101]

Actually I guess it was only putting results for CT VIN numbers on bottom. I tried with other vins (begining with DD and EZ ) and they seemed to work fine. I am usure why results were different for "CT" VIN's

 

[billmeye]

Give it a try. Change the DESC to ASC for Car No.

There is a lot at play here. The trackingnumber takes precedence so the listbox sorts first by trackingnumber, second by DateCode, third by VIN. If you wanted to be able to change this order you would need to recreated the SQL statement and change the orders the columns appear but then that interfere with your On Click event as the TrackingNumber may no longer be in the first column. A lot of coding would be required to keep track and make everything work as desired. At some point you just settle with a consistent result that you can work with.

 

[MG101]

Thanks for all of your help Billmeye! You sure know your stuff!