Finding and return record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with a text boxes that accept input from users. I have one text box that search to see if the records already exists. here is the code

Private Sub Medicalrecord_BeforeUpdate(Cancel As Integer
If DCount("*", "TableOne", "Medicalrecord = '" & Me!Medicalrecord & "'") > 0 The
MsgBox "Duplicate Value
Cancel = Tru
End I

End Su

My question: Can I replace the "MsgBox statement" with code to bring up the record instead of just saying the "Duplicate Value" Thanks
 
You can use the form's RecordsetClone to do that
This example is in Access' help. Be sure to set a refence to DAO, even
Access 2000 uses DAO recordset for forms.
If you have newer version of Access check help to see what type of recordset
is used for forms.

The next example uses the RecordsetClone property and the Recordset object
to synchronize a recordset's record with the form's current record. When a
company name is selected from a combo box, the FindFirst method is used to
locate the record for that company and the Recordset object's DAO Bookmark
property is assigned to the form's Bookmark property, causing the form to
display the found record.

Sub SupplierID_AfterUpdate()
Dim rst As Recordset
Dim strSearchName As String

Set rst = Me.RecordsetClone
strSearchName = Str(Me!SupplierID)
rst.Find "SupplierID = " & strSearchName
If rst.NoMatch Then
MsgBox "Record not found"
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close
End SubRagnar
 
IIRC, you should be able to. Since you are likely to use the same
RecordSource, make sure the RecordLocks Property of the current Form is
appropriately set. Also, you probably should open the second Form with the
acDialog mode so that the code in the calling Form (first Form) is paused
until the called Form is closed.

Check Access VB Help on the OpenForm Method for the arguments to use.

--
HTH
Van T. Dinh
MVP (Access)



bladelock said:
I have a form with a text boxes that accept input from users. I have one
Click to expand...
text box that search to see if the records already exists. here is the code:
Private Sub Medicalrecord_BeforeUpdate(Cancel As Integer)
If DCount("*", "TableOne", "Medicalrecord = '" & Me!Medicalrecord & "'") > 0 Then
MsgBox "Duplicate Value"
Cancel = True
End If

End Sub

My question: Can I replace the "MsgBox statement" with code to bring up
Click to expand...
the record instead of just saying the "Duplicate Value" Thanks
 
I have a form with a text boxes that accept input from users. I have one text box that search to see if the records already exists. here is the code:

Private Sub Medicalrecord_BeforeUpdate(Cancel As Integer)
If DCount("*", "TableOne", "Medicalrecord = '" & Me!Medicalrecord & "'") > 0 Then
MsgBox "Duplicate Value"
Cancel = True
End If

End Sub

My question: Can I replace the "MsgBox statement" with code to bring up the record instead of just saying the "Duplicate Value" Thanks
Click to expand...

Yes... but if that's what you want to do, you can save a step by
opening a Recordset first, since you'll need to do so eventually:

Private Sub Medicalrecord_BeforeUpdate(Cancel as Integer)
Dim rs As DAO.Recordset ' be sure Tools... References has DAO checked
Dim iAns as Integer
Set rs = Me.Recordsetclone
rs.FindFirst "[Medicalrecord] = '" & Me!Medicalrecord & "'"
If Not rs.NoMatch Then
iAns = MsgBox("This record already exists! Jump to it?", vbYesNo)
If iAns = vbYes Then
' Clear the current form, jump to the record
Me.Undo
Cancel = True
Me.Bookmark = rs.Bookmark
Else
' Just clear the medicalrecord textbox, let them try again
Cancel = True
Me!Medicalrecord.Undo
End If
End If
End Sub
 
Back
Top