Access 2010 - Search Field on the Main Menu - How do I do it?

[Parker]

Ok, so now I have in the window:


If Me.Combo197 Like "Date*" Then
Me.Filter = Me.Combo197 & "=#" & Me.Text175 & "#"
ElseIf Me.Combo197 = "Report_ID" Or Me.Combo197 = "Submitted_By" Or Me.Combo197 = "Reviewed_By" Then
Me.Filter = Me.Combo197 & "=" & Me.Text175
Else
Me.Filter = Me.Combo197 & "='" & Me.Text175 & "'"
End If
Me.FilterOn = True


Im not sure what this means (How do you have that code in the VBA window if it is not already within an event procedure?)

I would really like to have a button as pictured so once they type in a word they can physically click "search"

[June7]

No wonder this code is not working. The "Search" form does not have a RecordSource - it is unbound. Applying the filter string to that form's Filter property is meaningless.

If you want to construct the filter string and apply to another form or report. Try:

Private Sub Command164_Click()
Dim strFilter As String
If Me.Combo197 Like "Date*" Then
   strFilter = Me.Combo197 & "=#" & Me.Text175 & "#"
ElseIf Me.Combo197 = "Report_ID" Or Me.Combo197 = "Submitted_By" Or Me.Combo197 = "Reviewed_By" Or Me.Combo197 = "Approved" Then
   strFilter = Me.Combo197 & "=" & Me.Text175
Else
   strFilter = Me.Combo197 & " LIKE '*" & Me.Text175 & "*'"
End If
DoCmd.OpenForm "SearchMainF", , , strFilter
End Sub

Be sure to fix the RecordSource of SearchMainF.

The Submitted_By, Reviewed_By, and Approved fields are number type but you have alias Lookups set up on them in table (I NEVER do that - review http://access.mvps.org/access/lookupfields.htm). Even though you see text when viewing the table the actual value is the foreign key number. The search parameter will have to be the foreign key number ID. More reason for a combobox to input search parameter instead of textbox.

Are you aware that Access has a 2GB file size limit? Embedding documents in Attachment field can use up that limit fast. Also, can't really filter/search an Attachment field, at least not with this arrangement.

[Parker]

I appreciate your help, but I don't physically know how to do each step your asking. Could you either be more specific in how to do the alias lookups or just PM back the DB amended with the changes?

Furthermore, maybe I blasted it away when I sent you the non-confidential DB but in the "search" form I have the following in my record source:

SELECT NewLabReportT.Report_ID, NewLabReportT.Submitted_By, NewLabReportT.Reviewed_By, NewLabReportT.Date_Experiment_Started, NewLabReportT.Date_Experiment_Ended, NewLabReportT.Date_Report_Submitted, NewLabReportT.Experiment_Location, NewLabReportT.Report_Title, NewLabReportT.Report_Document, NewLabReportT.Notes, NewLabReportT.Abstract, NewLabReportT.Approved, NewLabReportT.Report_History FROM NewLabReportT;

And we are not worried about file size... we will only be uploading .DF smaller than 25kb... we can fit tons of files within the 2GB limit

[June7]

1. You already have the 'lookups' built in the table on those fields. My point was to advise not to do that. Did you review the link?

2. The 'search' form I was referring to is MainMenuF, the one that opens by default and has the combobox and textbox to construct filter criteria.

3. The only 'steps' you need to do are to replace the code for the command button and delete unnecessary code. Since you have already created VBA procedure you should know how to edit it.

[Parker]

All I did was copy and paste VBA stuff from these forums lol. I have never been trained or educated in VBA.

[Parker]

Private Sub Command164_Click()
Dim strFilter As String
If Me.Combo197 Like "Date*" Then
strFilter = Me.Combo197 & "=#" & Me.Text175 & "#"
ElseIf Me.Combo197 = "Report_ID" Or Me.Combo197 = "Submitted_By" Or Me.Combo197 = "Reviewed_By" Or Me.Combo197 = "Approved" Then
strFilter = Me.Combo197 & "=" & Me.Text175
Else
strFilter = Me.Combo197 & " LIKE '*" & Me.Text175 & "*'"
End If
DoCmd.OpenForm "SearchMainF", , , strFilter
End Sub

Run-time error '2501'

the OpenForm action was cancled

[June7]

Did you fix the RecordSource of SearchMainF? Right now it references a query that doesn't exist.

Or reference whatever form or report you want that has the appropriate RecordSource.

[Parker]

Your right this says it needs a reference. Do I just delete it? Do I need it?

[June7]

I don't understand. What says you need a reference? Delete what?

What form or report do you want to open?

Opening a filtered form or report is basic Access functionality. All the code is doing is automating that functionality with dynamic parameters.

[Parker]

the form SearchF has nothing to do with SearchMainF. All I have on the main screen is the Combo box, a text box and a button. I have not created a form for the search command yet. That is what I ahve been waiting on with the last code instruction. I sent you a PM earlier today.

[June7]

Build whatever form or report you want then reference it in the suggested code in place of "SearchMainF".