Update a record if it exist

[Vinu]

Hello, I've been looking through these posting for days to find a particular
code that will allow me to do the following.
I've created a data entry form "frmProvider" from a table "tbleStorage".
So, whenever a user enter a record in the form, it gets saved to the table.
Now, when the user do the entry and enters the "LastName" and "BillingTIN" on
the form, I would love to than search my table to see if that lastname and
that billingTin already exists, if it does, I than want that record from the
table to show up so that I could edit that record with new address or date.
If that record doesn't exist, than I will just enter the new record. Is it
possible to accomplish what I'm trying to do.

 

[Arvin Meyer [MVP]]

It might be better to use a combo box with the LastName and BillingTIN
columns, and with the autoexpand property (the default) on, and the
NotInList property turned off. My reasoning is that the user, if allowed to
simply type anything into a textbox, will invariably add records because of
possible spelling or typo mistakes. If they can see the entries, there will
be far fewer chances to make the mistake. The implementation is easy, since
you already have a combobox wizard to find the record. Just add a line to
the code to open a new record if nothing is found (untested):

Private Sub cboLastName_AfterUpdate()
On Error Resume Next
Dim rst As Object

Set rst = Me.Recordset.Clone
rst.FindFirst "[ID] = " & Str(NZ(Me![cboLastName], 0))
If Not rst.EOF Then
Me.Bookmark = rst.Bookmark
Else
DoCmd.GoToRecord , , acNewRecord
End If
End Sub

 

[Vinu]

I'm getting an error message on Else part of the code before the
DoCmd.GoToRecord, ,acNewRecord. Any reason why? thanks!

It might be better to use a combo box with the LastName and BillingTIN
columns, and with the autoexpand property (the default) on, and the
NotInList property turned off. My reasoning is that the user, if allowed to
simply type anything into a textbox, will invariably add records because of
possible spelling or typo mistakes. If they can see the entries, there will
be far fewer chances to make the mistake. The implementation is easy, since
you already have a combobox wizard to find the record. Just add a line to
the code to open a new record if nothing is found (untested):

Private Sub cboLastName_AfterUpdate()
On Error Resume Next
Dim rst As Object

Set rst = Me.Recordset.Clone
rst.FindFirst "[ID] = " & Str(NZ(Me![cboLastName], 0))
If Not rst.EOF Then
Me.Bookmark = rst.Bookmark
Else
DoCmd.GoToRecord , , acNewRecord
End If
End Sub
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hello, I've been looking through these posting for days to find a
particular
code that will allow me to do the following.
I've created a data entry form "frmProvider" from a table "tbleStorage".
So, whenever a user enter a record in the form, it gets saved to the
table.
Now, when the user do the entry and enters the "LastName" and "BillingTIN"
on
the form, I would love to than search my table to see if that lastname and
that billingTin already exists, if it does, I than want that record from
the
table to show up so that I could edit that record with new address or
date.
If that record doesn't exist, than I will just enter the new record. Is
it
possible to accomplish what I'm trying to do.

 

[John W. Vinson]

I'm getting an error message on Else part of the code before the
DoCmd.GoToRecord, ,acNewRecord. Any reason why? thanks!

Please post your actual code and the error message; we can see neither.

 

[Vinu]

Private Sub Combo147_AfterUpdate()
On Error Resume Next
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[BillingTIN] = '" & Me![Combo147] & "'"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Else
DoCmd.GoToRecord , , acNewRecord
End If

End Sub

I no longer am getting an error message. However, eventhough this code
allows me to see the full record if the record do exist, but it doesn't let
me select that record for editing purpose. Also, when I type in a brand new
record, i'm able to but when I try to enter the next record, my first record
last name changes to the last name of the 2nd record, but the other fields
doesn't change. BTWY, I used combo box on the last name. thanks!

 

[John Spencer]

I would try rewriting the code to the following

Private Sub Combo147_AfterUpdate()
'COMMENT OUT the next line. Bad technique
'On Error Resume Next

'Find the record that matches the control.
'Assumption: you are using DAO (MDB file)
Dim rs As DAO.RecordSet '<<<<<<<<

Set rs = Me.RecordsetClone '<<<<<<<

rs.FindFirst "[BillingTIN] = '" & Me![Combo147] & "'"

If Not rs.NoMatch Then '<<<<<<<<<<<
Me.Bookmark = rs.Bookmark
Else
DoCmd.GoToRecord , , acNewRecord
End If

End Sub


I would have an error handler in that code instead of just ignoring any
error that occurs with On Error Resume Next. At a minimum, I would
comment out that line until the code appeared to be working correctly.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Private Sub Combo147_AfterUpdate()
On Error Resume Next
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[BillingTIN] = '" & Me![Combo147] & "'"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Else
DoCmd.GoToRecord , , acNewRecord
End If

End Sub

I no longer am getting an error message. However, eventhough this code
allows me to see the full record if the record do exist, but it doesn't let
me select that record for editing purpose. Also, when I type in a brand new
record, i'm able to but when I try to enter the next record, my first record
last name changes to the last name of the 2nd record, but the other fields
doesn't change. BTWY, I used combo box on the last name. thanks!

Please post your actual code and the error message; we can see neither.

 

[Vinu]

Hey John, when I type the lastName on my combobox, I do see the record
whenever it does exist, but I still can not select that existing record, I
get an error message of "Run-time error '2105' You can't go to the specified
record" and when I debug the erroe message, "DoCmd.GoToRecord , ,
acNewRecord" gets highlighted in yellow. Also, when I type a brand new
LastName, I get an error message when I try to go to the next field. Would
you be able to tell me why that's the case? thanks!

I would try rewriting the code to the following

Private Sub Combo147_AfterUpdate()
'COMMENT OUT the next line. Bad technique
'On Error Resume Next

'Find the record that matches the control.
'Assumption: you are using DAO (MDB file)
Dim rs As DAO.RecordSet '<<<<<<<<

Set rs = Me.RecordsetClone '<<<<<<<

rs.FindFirst "[BillingTIN] = '" & Me![Combo147] & "'"

If Not rs.NoMatch Then '<<<<<<<<<<<
Me.Bookmark = rs.Bookmark
Else
DoCmd.GoToRecord , , acNewRecord
End If

End Sub


I would have an error handler in that code instead of just ignoring any
error that occurs with On Error Resume Next. At a minimum, I would
comment out that line until the code appeared to be working correctly.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Private Sub Combo147_AfterUpdate()
On Error Resume Next
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[BillingTIN] = '" & Me![Combo147] & "'"
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Else
DoCmd.GoToRecord , , acNewRecord
End If

End Sub

I no longer am getting an error message. However, eventhough this code
allows me to see the full record if the record do exist, but it doesn't let
me select that record for editing purpose. Also, when I type in a brand new
record, i'm able to but when I try to enter the next record, my first record
last name changes to the last name of the 2nd record, but the other fields
doesn't change. BTWY, I used combo box on the last name. thanks!

I'm getting an error message on Else part of the code before the
DoCmd.GoToRecord, ,acNewRecord. Any reason why? thanks!
Please post your actual code and the error message; we can see neither.