> > I
> > want them to be able to type the Lot Number and Sub lot number in the
> > respective box and then click the command button which will take them to
> the
> > record they need.
>
> well, you can do that. but since the two fields are the table's primary key,
> the user will never have a choice to enter a new record "anyway"; s/he will
> always have to deal with the pre-existing record. how about automatically
> taking the user to the existing record? something along the lines of the
> following, as
>
> If DCount(1, "tblMyTable", "LotNumber = '" & Me!LotNumber _
> & "' And SubLot = '" & Me!SubLot & "'") > 0 Then
> Cancel = True
> Dim strLot As String, strSub As String
> strLot = Me!LotNumber
> strSub = Me!SubLot
> Me.Undo
> Me.RecordsetClone.FindFirst "LotNumber = '" & strLot _
> & "' And SubLot = '" & strSub & "'"
> Me.Bookmark = Me.RecordsetClone.Bookmark
> MsgBox "Here's the record that's already entered for " _
> & "this lot and sub."
> End If
>
> the above code assumes that both LotNumber and SubLot are stored in the
> table as Text data types. if they're Number data type fields, instead, just
> remove the single quotes from the code.
>
> hth
>
>
> "dnr" <dnr[ at ]discussions.microsoft.com> wrote in message
> news:F42020D7-2BC4-42B0-BB65-38F30293981A[ at ]microsoft.com...
> > Hello!
> >
> > I have a form that users use to enter inspection data. The unique index
> is
> > based on a combination of two fields, LotNumber and SubLot. If a user
> tries
> > to enter a LotNumber and SubLot number combination that already exists a
> > message box pops up telling them that. I want them to be able to find
> that
> > record if the combination already exists. I have added two text boxes and
> a
> > command button to the form, called txtLotNumber and txtSubLot and Srchcmd.
> I
> > want them to be able to type the Lot Number and Sub lot number in the
> > respective box and then click the command button which will take them to
> the
> > record they need. I found the listed below and it works but only to find
> the
> > lot number how can I include the sub lot number? Please help!!
> >
> >
> > '
http://www.databasedev.co.uk/text_search.html> >
> > '--------------------------------------------------------------
> > 'GJT 25-01-02
> > '--------------------------------------------------------------
> > Private Sub cmdSearch_Click()
> > Dim strStudentRef As String
> > Dim strSearch As String
> >
> > 'Check txtLotNumber for Null value or Nill Entry first.
> >
> > If IsNull(Me![txtLotNumber]) Or (Me![txtLotNumber]) = "" Then
> > MsgBox "Please enter a value!", vbOKOnly, "Invalid Search
> Criterion!"
> > Me![txtLotNumber].SetFocus
> > Exit Sub
> > End If
> > '---------------------------------------------------------------
> >
> > 'Performs the search using value entered into txtLotNumber
> > 'and evaluates this against values in LotNumber
> >
> > DoCmd.ShowAllRecords
> > DoCmd.GoToControl ("LotNumber")
> > DoCmd.FindRecord Me!txtLotNumber
> >
> > LotNumber.SetFocus
> > strStudentRef = LotNumber.Text
> > txtLotNumber.SetFocus
> > strSearch = txtLotNumber.Text
> >
> > 'If matching record found sets focus in LotNumber and shows msgbox
> > 'and clears search control
> >
> > If strStudentRef = strSearch Then
> > MsgBox "Match Found For: " & strSearch, , "Congratulations!"
> > LotNumber.SetFocus
> > txtLotNumber = ""
> >
> > 'If value not found sets focus back to txtLotNumber and shows msgbox
> > Else
> > MsgBox "Match Not Found For: " & strSearch & " - Please Try
> > Again.", _
> > , "Invalid Search Criterion!"
> > txtLotNumber.SetFocus
> > End If
> > End Sub
>
>
>