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

[Parker]

Hello Everyone,



Background
Currently I have a basic database with the following

Table 1 = "EmployeeT" with fields "Employee_ID", "First_Name", "Last_Name" etc.
Table 2 = "NewLabReportT" with fields "Report_ID", "Report_History", "Submitted_By", "Reviewed_By", "Date_Submitted", "Report_Title", "Abstract"

Form 1 = "MainMenuF" this is where I would like to have the search
Form 2 = "NewLabReportF" with fields "Report_ID", "Report_History", "Submitted_By", "Reviewed_By", "Date_Submitted", "Report_Title", "Abstract"
Form 3 = "EmployeeF" with fields "Employee_ID", "First_Name", "Last_Name" etc.

Problem:
I would like to have on the Main Menu a drop down list with all of the fields described in NewLaBReportF ("Report_ID", "Report_History", "Submitted_By", "Reviewed_By", "Date_Submitted", "Report_Title", "Abstract") and then allow the user to click a button to the side of it to alert a pop up.

In this pop up:

If they originally clicked "Report_Id" then have the message prompt then to "Enter a Report ID"
If they originally clicked "Submitted_By" then have the message prompt them to "Enter an employees Last Name"
etc...

Further More, after they enter the respective value in this box, have a FORM not a REPORT generate that lists all of the records that match the criteria. This way then can specifically click on each record individually and have it open directly up to it.


PLEASE HELP!

[June7]

Isn't this already addressed in other post https://www.accessforums.net/access/...own-43639.html

[Parker]

Isn't this already addressed in other post https://www.accessforums.net/access/...own-43639.html

Not answered yet

[Parker]

I don't even know how to make the drop down box reflect the table names I need. In the drop down box that I have attempted to create it just has every single record in it and that is not at all what I am looking for ;/

[June7]

If you don't tell us what you don't understand we can't help. If the suggestions given in other thread were not clear enough for you, should expand on the information. Starting a new thread with same question and info is not productive and is against forum guidelines.

Combobox RowSourceType property has an option for Field List. This will list ALL the fields from table/query specified in the RowSource property. Alternative is the Value List option. You can create list in RowSource by typing the field names you want to use - example: Employee_ID;First_Name;Last_Name.

[Parker]

If you don't tell us what you don't understand we can't help. If the suggestions given in other thread were not clear enough for you, should expand on the information. Starting a new thread with same question and info is not productive and is against forum guidelines.

Combobox RowSourceType property has an option for Field List. This will list ALL the fields from table/query specified in the RowSource property. Alternative is the Value List option. You can create list in RowSource by typing the field names you want to use - example: Employee_ID;First_Name;Last_Name.

Ok I apologize.

I have on my Main Menu now a combo box with RowSourceType set to Field List and the Row Source is defined with the table I need. Additionally, I have a blank command button next to it ready for the next steps

[Parker]

Here are all the options in my list:

Report_ID
Report_History
Submitted_By
Reviewed_By
Date_Experiment_Started
Date_Experiment_Ended
Date_Report_Submitted
Experiment_Location
Report_title
Report_Document
Notes
Abstract
Aprroved

[June7]

Now you need a control for input of parameter. As explained in other thread, I use a combobox. When user selects field in first combobox, code changes the RowSource of second combobox to offer list of values from the selected field. After user selects value from second combobox, code constructs filter string. If all fields are text type, the code is simple:

Me.Filter = Me.firstcomboboxname & "='" & Me.secondcomboxname & "'"
Me.FilterOn = True

However, some of your fields are date type. Date fields require # delimiters (note the apostrophe delimiters in the example). Number type would not have any delimiters. This means you will need conditional code, like:

IIf Me.firstcombobox LIKE "Date*" Then
Me.Filter = Me.firstcomboboxname & "=#" & Me.secondcomboxname & "#"
Else
Me.Filter = Me.firstcomboboxname & "='" & Me.secondcomboxname & "'"
End If

[Parker]

Report_ID = autonumber
Report_History = text
Submitted_By = number
Reviewed_By = number
Date_Experiment_Started = date/time
Date_Experiment_Ended = date/time
Date_Report_Submitted = date/time
Experiment_Location = text
Report_title = text
Report_Document = attatchment
Notes = memo
Abstract = memo
Aprroved = number

[Parker]

Now you need a control for input of parameter. As explained in other thread, I use a combobox. When user selects field in first combobox, code changes the RowSource of second combobox to offer list of values from the selected field. After user selects value from second combobox, code constructs filter string. If all fields are text type, the code is simple:

Me.Filter = Me.firstcomboboxname & "='" & Me.secondcomboxname & "'"
Me.FilterOn = True

However, some of your fields are date type. Date fields require # delimiters (note the apostrophe delimiters in the example). Number type would not have any delimiters. This means you will need conditional code, like:

IIf Me.firstcombobox LIKE "Date*" Then
Me.Filter = Me.firstcomboboxname & "=#" & Me.secondcomboxname & "#"
Else
Me.Filter = Me.firstcomboboxname & "='" & Me.secondcomboxname & "'"
End If

I understand that a combo box might be easier for this application, but I would like the pop up messages to occur because i need it to search within the field. For example one of the categories is title... so anytime the word "Tube" is used in the title all the records would pop-up... likewise abstracts could be 250 words long, so if the word "burner" was used I would like that to populate.

I don't think it would work to just have another combo box with a predetermined series of options available to search by

[June7]

The combobox will still allow you to construct pattern match. Don't restrict combobox input to the listed items, user can type whatever they want but still have option of selecting specific parameter from list. It is the approach I use. Otherwise just use a textbox.

Me.Filter = Me.firstcomboboxname & " LIKE '*" & Me.secondcomboxname & "*'"

[Parker]

Where do I insert this "Me.Filter = Me.firstcomboboxname & " LIKE '*" & Me.secondcomboxname & "*'"

[June7]

That is example of VBA code. I use only VBA, not macros.

Are you familiar with VBA programming?

Be sure to use your combobox names.

[Parker]

Yes slightly familiar with VBA... So I placed the following in my VBA window:

Me.Filter = Me.Combo167 & " LIKE '*" & Me.Combo169 & "*'"

What would the next step be?

[Parker]

Note my second combo box "169" I did not change anything to the row source or row source type.. should i?