Access 2003

  • Thread starter Thread starter Tay J.
  • Start date Start date
T

Tay J.

If I were to look upinformations stored in a table using a query (thru a
form) to see if I have previously been to an address, can I have the system
ask me if I want to add a new entry or if the address is not found or have it
ask if I would like to new record if the address is found?

Thanks in advance!!

Tay
 
If I were to look upinformations stored in a table using a query (thru a
form) to see if I have previously been to an address, can I have the system
ask me if I want to add a new entry or if the address is not found or have it
ask if I would like to new record if the address is found?

Thanks in advance!!

Tay
Click to expand...

Sure. You can use the BeforeUpdate event of the Form to check. What I'd
suggest is giving the user the option of opening the found record, adding a
new record, or just cancelling:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim rs As DAO.Recordset
Dim iAns As Integer
Set rs=Me.RecordsetClone ' set a recordset to the form's source
rs.FindFirst "[Address] = """ & Me!txtAddress & """" ' find entered address
If Not rs.NoMatch Then ' was a record found?
iAns = MsgBox("This address already exists." & _
"Click Yes to add it anyway, No to move to the found record, " & _
"Cancel to erase the form and start over:", vbYesNoCancel
Select Case iAns
Case vbYes
' do nothing, just add the record
Case vbNo
Cancel = True ' cancel this addition
Me.Bookmark = rs.Bookmark ' jump to the found record
Case vbCancel
Cancel = True ' cancel this additio
Me.Undo ' clear the form
End Select
End If
Set rs=Nothing 'clean up after yourself
End Sub

Air code, untested, though I've used similar constructs; adapt the table,
field and control names to your own needs.
 
Back
Top