Search Form Help

[karmaimages]

Hi,



Unfortunately I'm a complete novice when it comes to access so any help would be much appreciated.

I'm trying to build a database to make some work easier and to keep track of some information help on paper file (why??)

I've created my table - DWP_Date_Backend

Form - DWP Trace Form (for entering data into the database)

Form- Search_Form

What I want to do, is use the search form to query the database and display the results of the all the fields in the DWP_Date_Backend perhaps in a text box under the search fields.

On the search form i several fields to be able to search the DB

I'd like for a user to be able to enter in any of the fields/leaving blank if they wish any of them or entering multiple criteria.

Then display the result either on another form/report or under the search.

I don't want the user to be able to edit any of the data just be able to search it.

I've tried but can't seem to be able to get search working in any shape or form. If someone could offer some advice? I have attached a sample database to this post.

DWP Trace.mdb

[June7]

Review: http://www.allenbrowne.com/ser-62.html

I suggest you attempt something like that and when you encounter specific issue, post question.

[karmaimages]

I managed to do it through trial and error -

I'm having an issue where data i'm entering into one of the forms is being surrounded by <div> text </div> tags and I can't work out why. Any ideas?

[June7]

Congratulations! T&E is how I get a lot accomplished.

Those are HTML code tags. Are the field and textbox set for Rich Text Format?

[karmaimages]

Congratulations! T&E is how I get a lot accomplished.

Those are HTML code tags. Are the field and textbox set for Rich Text Format?

They were set to rich text, changed and that now is all fixed. Thanks for your help.

[karmaimages]

Sorry another question..

The search button i have the following expression: [Policy_Number] Like "*" & [Forms]![Search_Query]![Text24] & "*"

I would assume if I removed the * it would search for whatever was exactly entered into the text box? Or would I need to change something else?

Also, if there a way to display a warning if no records are found?

[June7]

Use = operator instead of LIKE and wildcard.

No records found should be evident when the form displays all blank. This is what I do. User sees blank, start search all over again.

But yes, I expect you could give a notice. What do you want - a message box? Sounds annoying to me, something else user has to respond to.

[karmaimages]

Use = operator instead of LIKE and wildcard.

No records found should be evident when the form displays all blank. This is what I do. User sees blank, start search all over again.

But yes, I expect you could give a notice. What do you want - a message box? Sounds annoying to me, something else user has to respond to.

Thanks for all your help - Yes ideally a message box that said "no records found" something along those lines?

[June7]

Only approach I can see is to take the constructed filter criteria string and use it to directly interrogate the table to see if any records meet the parameters. A domain aggregate function (DLookup or DCount) could be used.

...
If DCount("*", "table or query name", strFilter) = 0 Then
MsgBox "No records"
Else
'code to apply filter to form
...
End If

[karmaimages]

Only approach I can see is to take the constructed filter criteria string and use it to directly interrogate the table to see if any records meet the parameters. A domain aggregate function (DLookup or DCount) could be used.

...
If DCount("*", "table or query name", strFilter) = 0 Then
MsgBox "No records"
Else
'code to apply filter to form
...
End If

I'm not going to lie, the above means nothing to me what so ever, but thank you for taking the time to give me the suggestion

[karmaimages]

You also mentioned using the = operator instead of like, which i changed to

[Policy_Number]="*" & [Forms]![Search_Query]![Text24] & "*"

However this didn't actually display any records, even if they existed in the database?

[June7]

I said use = instead of LIKE AND wildcards. Remove the wildcards.

[karmaimages]

I've tried removing the wildcards but this still shows nothing. Could you tell me how to write the actual query so it does work?

[June7]

What query?

Are you adapting Allen Browne code to build filter string? Post your actual code or provide revised db.

If Policy_Number is a text type field, need apostrophe delimiters, like:

Me.Recordset.FindFirst "Policy_Number='" & Me.txtPolicyNumber & "'"

[karmaimages]

Attached an updated database - The search form is the form I have the query on, In which I want it to match exact matches only not "LIKE" matches. I'm not quite sure how to chance to code to do this though. I'm trying to learn by trial and error first time I've built a proper database in access before.


DWP Trace.mdb