FindRecord after me.undo

R

rgrantz

I have code set in BeforeUpdate to check for the existence of an existing
record, tell the user the record exists, and then go to that record. The
code follows:

Dim PSNumFind As String
PSNumFind = Me.PSNum
If (DCount("*", "KitOrders", "[PSNum]=""" & Me.PSNum & """") > 0) Then
MsgBox "That Packing Slip already Entered!"
Me.Undo
DoCmd.FindRecord PSNumFind
End If

I was getting a bunch of errors about setting focus and not being able to
reference field properties/data for the findrecord function when the control
is disabled, and also not being able to leave the record because that
field's required property i set to true. I thought that setting a variable
to the control's contents would do the trick (using the variable as the
criteria string rather than the control's contents), but I now get error #
2137 ("You can't use Find/Replace Now") at the FindRecord line.

I've made msgboxes that shw the value of PSNumFind at the END of this
procedure, and it IS correct and holding the value in PSNum. What can I do
to get this thing to go to the record that exists based on the value in
PSNum?

Possibly a clue as to problem: It smetimes works, but then if the form is
closed and then reopened, I start getting the errors again when entering new
(existing) record, and I notice that in the Filter property of the form, it
is set to [PSNum] = ". When I clear this, it works again until the form
closed and opened again, and an exiusting PSNum is entered in a new record.



An addendum: I notice that when this form is entered by coming from the
previous menu (just a button that opens the form at new record), if a record
exists for the data put into PSNum as the first entry, THAT's when this
thing doesn't work. If I end the sub, go back to the form, and use the
button on the form itself to go to a new record (rather than having come
from the previous form), then this Warning and finding the existing record
thing works fine. This makes me think it's a data mode issue (button that
opens form goes to acnew as a form action, but button on form itself goes to
acnewrec as a record action). I don't know how to change this, though,
since I'm opening a form with one button and navigating records with the
other.


-- Cose from PREVIOUS menu form:

On Error GoTo Err_Command0_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmKitOrders"
DoCmd.Close
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description & Err.Number
Resume Exit_Command0_Click


-- Code on THIS form's "Add New" button:

On Error GoTo Err_Command10_Click


DoCmd.GoToRecord , , acNewRec
Me.PSNum.SetFocus

Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click


-- Code on the PSNum BeforeUpdate event (which checks for existing record):

Dim PSNumFind As String
PSNumFind = Me.PSNum
If (DCount("*", "KitOrders", "[PSNum]=""" & Me.PSNum & """") > 0) Then
MsgBox "That Packing Slip already Entered!"
Cancel = True
'Me.Undo
Cancel = True
MsgBox PSNumFind & " - " & Me.FilterOn & " Focus: " &
Screen.ActiveControl
Me.Undo
DoCmd.FindRecord PSNumFind
End If

....Obviously, I added the 2nd msgbox just to see that the values were being
saved, what the filter was set to, etc. when the error occurs


ANY help is greatly appreciated, folks...I'm almost done with this
thrice-accursed thing!!
 
G

Guest

I guess you do have some errors going on here...

The first question you have to ask is what about the record is unique to
determine if it exists in the database. If you are using record ids and
limiting your table to one unique indentifier then that would be the end of
your problem.

However, it sounds as though there is more here than that. So again the
question is what about your record is unique and make a duplicate record
undesirable?

Once you figure that out then you simply use a query method to search for
the fields of interest. If they are matched then you can display a relevant
message, cancel the update and move to that record which matched your
criteria.

rgrantz said:
I have code set in BeforeUpdate to check for the existence of an existing
record, tell the user the record exists, and then go to that record. The
code follows:

Dim PSNumFind As String
PSNumFind = Me.PSNum
If (DCount("*", "KitOrders", "[PSNum]=""" & Me.PSNum & """") > 0) Then
MsgBox "That Packing Slip already Entered!"
Me.Undo
DoCmd.FindRecord PSNumFind
End If

I was getting a bunch of errors about setting focus and not being able to
reference field properties/data for the findrecord function when the control
is disabled, and also not being able to leave the record because that
field's required property i set to true. I thought that setting a variable
to the control's contents would do the trick (using the variable as the
criteria string rather than the control's contents), but I now get error #
2137 ("You can't use Find/Replace Now") at the FindRecord line.

I've made msgboxes that shw the value of PSNumFind at the END of this
procedure, and it IS correct and holding the value in PSNum. What can I do
to get this thing to go to the record that exists based on the value in
PSNum?

Possibly a clue as to problem: It smetimes works, but then if the form is
closed and then reopened, I start getting the errors again when entering new
(existing) record, and I notice that in the Filter property of the form, it
is set to [PSNum] = ". When I clear this, it works again until the form
closed and opened again, and an exiusting PSNum is entered in a new record.



An addendum: I notice that when this form is entered by coming from the
previous menu (just a button that opens the form at new record), if a record
exists for the data put into PSNum as the first entry, THAT's when this
thing doesn't work. If I end the sub, go back to the form, and use the
button on the form itself to go to a new record (rather than having come
from the previous form), then this Warning and finding the existing record
thing works fine. This makes me think it's a data mode issue (button that
opens form goes to acnew as a form action, but button on form itself goes to
acnewrec as a record action). I don't know how to change this, though,
since I'm opening a form with one button and navigating records with the
other.


-- Cose from PREVIOUS menu form:

On Error GoTo Err_Command0_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmKitOrders"
DoCmd.Close
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description & Err.Number
Resume Exit_Command0_Click


-- Code on THIS form's "Add New" button:

On Error GoTo Err_Command10_Click


DoCmd.GoToRecord , , acNewRec
Me.PSNum.SetFocus

Exit_Command10_Click:
Exit Sub

Err_Command10_Click:
MsgBox Err.Description
Resume Exit_Command10_Click


-- Code on the PSNum BeforeUpdate event (which checks for existing record):

Dim PSNumFind As String
PSNumFind = Me.PSNum
If (DCount("*", "KitOrders", "[PSNum]=""" & Me.PSNum & """") > 0) Then
MsgBox "That Packing Slip already Entered!"
Cancel = True
'Me.Undo
Cancel = True
MsgBox PSNumFind & " - " & Me.FilterOn & " Focus: " &
Screen.ActiveControl
Me.Undo
DoCmd.FindRecord PSNumFind
End If

....Obviously, I added the 2nd msgbox just to see that the values were being
saved, what the filter was set to, etc. when the error occurs


ANY help is greatly appreciated, folks...I'm almost done with this
thrice-accursed thing!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top