go to specific record in form

  • Thread starter Thread starter dnr
  • Start date Start date
D

dnr

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
 
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.
Click to expand...

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 said:
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
Click to expand...
 
Tina-

Thanks for the help - it was very beneficial I kept getting errors
associated with:
Then Cancel = true
but after deleting that and reordering a couple things I got it to work.
This is very useful to the users.

I still need to have a search box for when people want to do searches for
specific lots and sublots. Do you have any advice on how to include the
sublot along with the lot for the search?

Thanks

tina said:
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.
Click to expand...

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 said:
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
Click to expand...
Click to expand...
 
Back
Top