How to Clear acFormAdd Filter

[Matt via AccessMonster.com]

I have a form to capture our shop orders. However at times we people try to
enter a new order in the system that has already been entered. The database
is set to only allow duplicate order numbers, but I have other feilds that
are required and the order entry person doesn't know the order was already
entered until after they have completed the entry of all feilds and go to
save the record.

What I have done is created a AfterUpdate to the OrderNum item, but becuase
my form is open in "acFormAdd" the code fails to open the exsisting record.

Can anyone help?
ThanksPrivate Sub cmdAddNew_Click()

Dim stDocName As String

stDocName = "frmOrderEntryOnly"
DoCmd.OpenForm stDocName, , , , acFormAdd

End SubPrivate Sub OrderNum_AfterUpdate()

Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone
stLinkCriteria = "[OrderNum]=" & "'" & SID & "'"

'Check table for existing order number.
If DCount("OrderNum", "tblShopOrders", stLinkCriteria) >= 1 Then
'Go to record with matching order number
DoCmd.ShowAllRecords
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing

End Sub

 

[Allen Browne]

You will need to undo the record being entered before you can move to
another one.

This example looks to see if there is a record (any one) with the order
number, and if so, gets user confirmation that they want to discard their
entry and find the other one. If so, it ndoes the entry, shows all records,
finds other entry, and verifies that the find succeeded.

Private Sub OrderNum_AfterUpdate()
Dim stLinkCriteria As String
Dim strMsg As String
Dim rsc As DAO.Recordset

If Not IsNull(Me.OrderNum) Then
'Check table for existing order number.
stLinkCriteria = "[OrderNum]=" & "'" & SID & "'"
If Not IsNull DLookup("OrderNum", "tblShopOrders", stLinkCriteria))
Then

'Get user confirmation to dump this entry.
strMsg = "Order number already exists. Bring up that record?"
If MsgBox(strMsg, vbYesNo, "Duplicate") = vbYes Then
Me.Undo 'Undo this entry.

'Go to record with matching order number
If Me.FilterOn Then 'Show all records
Me.FilterOn = False
End If
Set rsc = Me.RecordsetClone
rsc.FindFirst stLinkCriteria
If rsc.NoMatch Then
MsgBox "Oops: it disappeared."
Else
Me.Bookmark = rsc.Bookmark
End If
End If
End If
End If
Set rsc = Nothing
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form to capture our shop orders. However at times we people try to
enter a new order in the system that has already been entered. The
database
is set to only allow duplicate order numbers, but I have other feilds that
are required and the order entry person doesn't know the order was already
entered until after they have completed the entry of all feilds and go to
save the record.

What I have done is created a AfterUpdate to the OrderNum item, but
becuase
my form is open in "acFormAdd" the code fails to open the exsisting
record.

Can anyone help?
ThanksPrivate Sub cmdAddNew_Click()

Dim stDocName As String

stDocName = "frmOrderEntryOnly"
DoCmd.OpenForm stDocName, , , , acFormAdd

End SubPrivate Sub OrderNum_AfterUpdate()

Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone
stLinkCriteria = "[OrderNum]=" & "'" & SID & "'"

'Check table for existing order number.
If DCount("OrderNum", "tblShopOrders", stLinkCriteria) >= 1 Then
'Go to record with matching order number
DoCmd.ShowAllRecords
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
Set rsc = Nothing

End Sub

 

[Matt via AccessMonster.com]

Thanks Allen,

I am closer then before thank you for the code...I had to make a few changes
and it worked okay, but I am still not able to bring up the record if it
exsists. The FilterOn is not clearing it.

Any other ideas?
See comments below '>> to changes.

Private Sub OrderNum_AfterUpdate()
Dim stLinkCriteria As String
Dim strMsg As String
Dim rsc As DAO.Recordset

'>> I don't under stand what this "if" is doing in this code, so I
disabled it.
'>> If Not IsNull(Me.OrderNum) Then
'Check table for existing order number.
'>>This is not working >>> stLinkCriteria = "[OrderNum]=" & "'" &
SID & "'"
'>> I changed it to the below and it seems to grab the OrderNum.
stLinkCriteria = Me.OrderNum
'>>Added "(" in front of (Dlookup and now that works.
If Not IsNull (DLookup("OrderNum", "tblShopOrders", stLinkCriteria))
Then

'Get user confirmation to dump this entry.
strMsg = "Order number already exists. Bring up that record?"
If MsgBox(strMsg, vbYesNo, "Duplicate") = vbYes Then
Me.Undo 'Undo this entry.
'>>The FilterOn = False is not removing the filter.
'Go to record with matching order number
If Me.FilterOn Then 'Show all records
Me.FilterOn = False
End If
Set rsc = Me.RecordsetClone
rsc.FindFirst stLinkCriteria
If rsc.NoMatch Then
MsgBox "Oops: it disappeared."
Else
Me.Bookmark = rsc.Bookmark
End If
End If
End If
'>>Removed one of the IF's.
'>>End If
Set rsc = Nothing
End Sub

You will need to undo the record being entered before you can move to
another one.

This example looks to see if there is a record (any one) with the order
number, and if so, gets user confirmation that they want to discard their
entry and find the other one. If so, it ndoes the entry, shows all records,
finds other entry, and verifies that the find succeeded.

Private Sub OrderNum_AfterUpdate()
Dim stLinkCriteria As String
Dim strMsg As String
Dim rsc As DAO.Recordset

If Not IsNull(Me.OrderNum) Then
'Check table for existing order number.
stLinkCriteria = "[OrderNum]=" & "'" & SID & "'"
If Not IsNull DLookup("OrderNum", "tblShopOrders", stLinkCriteria))
Then

'Get user confirmation to dump this entry.
strMsg = "Order number already exists. Bring up that record?"
If MsgBox(strMsg, vbYesNo, "Duplicate") = vbYes Then
Me.Undo 'Undo this entry.

'Go to record with matching order number
If Me.FilterOn Then 'Show all records
Me.FilterOn = False
End If
Set rsc = Me.RecordsetClone
rsc.FindFirst stLinkCriteria
If rsc.NoMatch Then
MsgBox "Oops: it disappeared."
Else
Me.Bookmark = rsc.Bookmark
End If
End If
End If
End If
Set rsc = Nothing
End Sub

I have a form to capture our shop orders. However at times we people try to
enter a new order in the system that has already been entered. The

[quoted text clipped - 39 lines]

End Sub

 

[Allen Browne]

Comments in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen,

I am closer then before thank you for the code...I had to make a few
changes

Great! You are meant to adapt the code to your needs.

and it worked okay, but I am still not able to bring up the record if it
exsists. The FilterOn is not clearing it.

Any other ideas?
See comments below '>> to changes.

Private Sub OrderNum_AfterUpdate()
Dim stLinkCriteria As String
Dim strMsg As String
Dim rsc As DAO.Recordset

'>> I don't under stand what this "if" is doing in this code, so I
disabled it.
'>> If Not IsNull(Me.OrderNum) Then
'Check table for existing order number.
'>>This is not working >>> stLinkCriteria = "[OrderNum]=" & "'" &
SID & "'"
'>> I changed it to the below and it seems to grab the OrderNum.
stLinkCriteria = Me.OrderNum

stLinkCriteria needs to end up looking like the WHERE clause in a query. If
you add:
Debug.Print stLinkCriteria
then when you run the code, and open the Immediate Window (Ctrl+G), you
should see something like:
[OrderNum] = 9
if OrderNum is a Number type field.
If it is a Text field, you need it to show the extra quotes:
[OrderNum] = '9'

'>>Added "(" in front of (Dlookup and now that works.
If Not IsNull (DLookup("OrderNum", "tblShopOrders", stLinkCriteria))
Good.

Then

'Get user confirmation to dump this entry.
strMsg = "Order number already exists. Bring up that record?"
If MsgBox(strMsg, vbYesNo, "Duplicate") = vbYes Then
Me.Undo 'Undo this entry.
'>>The FilterOn = False is not removing the filter.

It should. If you have filtered both a main form and a subform, there is a
bug where Access gets confused about the filters:
http://allenbrowne.com/bug-02.html

If you have actually changed the RecordSource of the form, removing the
filter won't bring in all records.

If you have added error handling that is suppressing the messages, the line
may not be executing.

If you answered No to the dialog, the entire block won't execute.

Try adding this line:
Stop
just above this section. When the code runs, it will stop and highlight that
line. Then press F8 to single-step through the code, and see what it is
doing.

'Go to record with matching order number
If Me.FilterOn Then 'Show all records
Me.FilterOn = False
End If
Set rsc = Me.RecordsetClone
rsc.FindFirst stLinkCriteria
If rsc.NoMatch Then
MsgBox "Oops: it disappeared."
Else
Me.Bookmark = rsc.Bookmark
End If
End If
End If
'>>Removed one of the IF's.
'>>End If
Set rsc = Nothing
End Sub

You will need to undo the record being entered before you can move to
another one.

This example looks to see if there is a record (any one) with the order
number, and if so, gets user confirmation that they want to discard their
entry and find the other one. If so, it ndoes the entry, shows all
records,
finds other entry, and verifies that the find succeeded.

Private Sub OrderNum_AfterUpdate()
Dim stLinkCriteria As String
Dim strMsg As String
Dim rsc As DAO.Recordset

If Not IsNull(Me.OrderNum) Then
'Check table for existing order number.
stLinkCriteria = "[OrderNum]=" & "'" & SID & "'"
If Not IsNull DLookup("OrderNum", "tblShopOrders",
stLinkCriteria))
Then

'Get user confirmation to dump this entry.
strMsg = "Order number already exists. Bring up that record?"
If MsgBox(strMsg, vbYesNo, "Duplicate") = vbYes Then
Me.Undo 'Undo this entry.

'Go to record with matching order number
If Me.FilterOn Then 'Show all records
Me.FilterOn = False
End If
Set rsc = Me.RecordsetClone
rsc.FindFirst stLinkCriteria
If rsc.NoMatch Then
MsgBox "Oops: it disappeared."
Else
Me.Bookmark = rsc.Bookmark
End If
End If
End If
End If
Set rsc = Nothing
End Sub

I have a form to capture our shop orders. However at times we people try
to
enter a new order in the system that has already been entered. The

[quoted text clipped - 39 lines]

End Sub