Search through multiple text boxes to retrieve records

[hardhitter06]

Hi All,

Access 2003.

I am trying to accomplish a particular search using three text boxes on my main form.

I have three fields: "Project", "Task" and "Award". I would like to set up three text fields on my main form with a command button so that when a user fills in these three fields and hits the "Search" command button, the proper results are displayed.

The output I am trying to receive is this:

I am trying to locate an additional field in my main table called "BoxNo" to return me the record searched (which is a combination of Project, Task and Award) and what Box this record resides in.

So on my output screen, I will have 4 fields: Project, Task, Award and BoxNo. If the record is big enough, it might reside in a combination of boxes that I need to display.

With that said, I have done something similar to this type of set up and search:

stLinkCriteria = "[DateOfTransaction] >= #" & Forms!frmRecycledDashboard.Text14 & "# And [DateOfTransaction] <= #" & Forms!frmRecycledDashboard!Text17 & "#"
DoCmd.OpenForm "frmRecycledDateRangeSearch", acNormal, , stLinkCriteria
Forms!frmRecycledDateRangeSearch.Text34 = "You have Searched by Dates " & Me.Text14 & " - " & Me.Text17

I had a date range search with Text14 and Text17 being the Search criteria inputs.

In this new scenerio, it is very similar, but I need to write the code that combines all three boxes in the search and outputs the BoxNo(s).

So that is the exact part I need help with, I know how to set up the Search Criteria Text Boxes, the Macro Search Command button and the Query n Form to display the data...I just need help with this code.

Thank you in advance.

Josh

 

[hardhitter06]

Actually,

Some of this is making a little more sense, I just need help with this first expression of the code

Private Sub Command3_Click()

[U]stLinkCriteria = "[Project] Forms!frmDashboard.Text5 And [Task] Forms!frmDashboard!Text7 And [Award] Forms!frmDashboard!Text9"[/U]
DoCmd.OpenForm "frmSearch1", acNormal, , stLinkCriteria
Forms!frmSearch1.Text4 = "You have Searched " & Me.Text5 & Me.Text7 & Me.Text9

End Sub

I need help linking those three fields together.

 

[boblarson]

If the fields are text:

stLinkCriteria = "[Project]=" & Chr(34) &  Forms!frmDashboard.Text5 & Chr(34) & " And [Task] = " & Chr(34) &  Forms!frmDashboard!Text7 & Chr(34) & " And [Award]=" & Chr(34) &  Forms!frmDashboard!Text9 & Chr(34)
DoCmd.OpenForm "frmSearch1, acNormal, , stLinkCriteria

If Numeric:

stLinkCriteria = "[Project]=" &  Forms!frmDashboard.Text5 & " And [Task] = " &  Forms!frmDashboard!Text7 & " And [Award]=" & Forms!frmDashboard!Text9 
DoCmd.OpenForm "frmSearch1, acNormal, , stLinkCriteria

 

[hardhitter06]

Thank you Bob, worked!! Minor Edit tho: DoCmd.OpenForm "frmSearch1", acNormal, , stLinkCriteria

Does anyone know how I would show the multiple box numbers returned on my frmSearch1?

If you are helping, search:
Project: 1234567
Task: 1
Award: 12345

And I would like it to return Box 1, 2 and 3

Here is my updated database to make this easier to work with.

 

[hardhitter06]

I figured it out, used a continious form. Thanks BOB