Search return specific record

I have a tblProduction w/ [ProductionID] which is an autonumber
associated specifically with the combination of [Date], [Shift], and
[Department]. I also have another table, tblProductOperation which has
[Employee], [Workstation], [PartNumber], [QuantityRun], etc.

I created an autoform on tblProduction (frmProduction). I also created
an autoform on tblProductOperation and drag and dropped this subform
(frmProductOperation) onto frmProduction. I call the combination of
the two forms frmDailyProdSheet - which is unique on the
[ProductionID], [Date], [Shift], and [Department].

Every time I enter a new Shift, Department, and Date a new
ProductionID is generated.

Let's say I have been collecting data for 3 shifts across 2
departments for a couple of weeks and I want to go back and REVISE not
just review a specific Department during a specific shift on a
specific date. In other words, I would like to enter all of my
criteria [Date], [Shift], and [Department] and have the recordset for
that [ProductionID] returned and editable.

I have looked at Allen Browne's Search Criteria but am wondering if
there is another way (non-programming) of accomplishing the same task.
I'm just curious.

Thanks,

Tim

Re: Search return specific record

tbrogdon@gmail.com wrote:

> I have a tblProduction w/ [ProductionID] which is an autonumber
> associated specifically with the combination of [Date], [Shift], and
> [Department]. I also have another table, tblProductOperation which has
> [Employee], [Workstation], [PartNumber], [QuantityRun], etc.
>
> I created an autoform on tblProduction (frmProduction). I also created
> an autoform on tblProductOperation and drag and dropped this subform
> (frmProductOperation) onto frmProduction. I call the combination of
> the two forms frmDailyProdSheet - which is unique on the
> [ProductionID], [Date], [Shift], and [Department].
>
> Every time I enter a new Shift, Department, and Date a new
> ProductionID is generated.
>
> Let's say I have been collecting data for 3 shifts across 2
> departments for a couple of weeks and I want to go back and REVISE not
> just review a specific Department during a specific shift on a
> specific date. In other words, I would like to enter all of my
> criteria [Date], [Shift], and [Department] and have the recordset for
> that [ProductionID] returned and editable.
>
> I have looked at Allen Browne's Search Criteria but am wondering if
> there is another way (non-programming) of accomplishing the same task.
> I'm just curious.
>
> Thanks,
>
> Tim
>
You might need some minimal code.

You could have 3 search text boxes and a command button. Let's call
them Text1, Text2, Text3, and Command1.

Command1 has the caption "New Search" and after update event of
Me.Text1 = Null
Me.Text2 = Null
Me.Text3 = Null
Me.Text1.SetFocus

Now each Textbox would have an AfterUpdate event with the code
SetFilter

Now create the routine SetFilter
Dim strF As String
If Not IsNull(Me.Text1) And Not IsNull(Me.Text2) And Not
IsNull(Me.Text3) then
'assumes date is a date field, shift numeric, dept is string
strF = "Date = #" & Me.Text1 & "# And " & _
"Shift = " & Me.Text2 & " And " & _
"Department = '" & Me.Text3 & "'"
Forms!MainForm!SubForm.Form.Filter = strF
Forms!MainForm!SubForm.Form.FilterOn = True


Europe
http://www.youtube.com/watch?v=x65k9dQScT8

Re: Search return specific record

That is precisley what I needed. Thanks for the help.

Tim

Re: Search return specific record

Color me dumb. I thought I understood this but I don't.

I created the form with 3 text boxes: Date, Department, and Shift. I
added a command button (New Search). There is also a text box for
ProductionID which, again, is determined by the 3 search criteria.
When the criteria are entered and the "New Search" button is clicked,
I want the ProductionID to reflect the ProductionID of a historical
recordset and have that recordset returned in a new form.

I opened the properties for the button but AfterUpdate was not
available to modify.

Also, should I create the SetFilter routine for the entire form or for
each instance of a text box?

Sorry for my confusion and thank you for the help.

Tim