Can any one help - Restriction

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

Guest

Hi
I've the following code for restricting the duplication of OrdRef. It's
working fine, but i have one more requirement.The OrdRef format is
"PO/1700-R.0/2004". The "R.0" stands for the revision of the order.

BeforeUpdate Event:
------------------
Dim OrdNo As Integer
OrdNo = DCount("[OrdRef]", "OrdTBL", "[OrdRef] = '" &
[Forms]![Ordentry]![Text29] & "'")

If OrdNo <> 0 Then
MsgBox "DUPLICATION !"
Cancel = True
End If

Table name = OrdTBL
Field in table = OrdRef
Form Name = Ordentry
Control on form = Text29

What i am looking for is the following
When I add a new record with the same number(1700) it should warn me that i
am duplicating, however it should allow me to modify the existing number as
PO/1700-R.1/2004 if required.

In other words when a new record is add the system should check the number
in between the two slashes if the number (eg:1700) exist then it should not
allow, else continue...

Please help.
 
Ok, I see two requirements. 1) Warn you if you are reusing a number, such as
1700, so that you can make the entry with a different revision number, such
as R.1. 2) Don't allow duplicates where the entire string would be a
duplicate.

For #2, set an index on the field in the table to Yes (No Duplicates).
Access will NOT allow duplicate entries. If you attempt to make one, you
will get an error that can be trapped in the form's Error event.

For #1, there are a couple of options. 1) You could break the entry into
more than one field and concatenate the fields together to get the entire
entry. You could still use the suggestion above by placing a Unique index on
the multiple fields. 2) You could use the Mid() function in your DLookup to
check for the number.

Dim OrdNo As Integer, strMsg As String, intRevNum, strNewOrdRef As String
OrdNo = DCount("[OrdRef]", "OrdTBL", "Val(Mid([OrdRef], 4)) = '" &
Val(Mid([Forms]![Ordentry]![Text29], 4)) & "'")

If OrdNo <> 0 Then
strMsg = "Possible Duplication!" & vbCrLf & "Continue with new revision
number?"
If MsgBox(strMsg, vbYesNo + vbQuestion, "Possible Duplicate") = vbYes
Then
intRevNum = DMax("Val(Mid([OrdRef], 11))", "OrdTBL",
"Val(Mid([OrdRef], 4)) = '" &
Val(Mid([Forms]![Ordentry]![Text29], 4)) & "'")
intRevNum = intRevNum + 1
strNewOrdRef = Replace([Forms]![Ordentry]![Text29], "R." &
intRevNum-1, "R." & intRevNum)
[Forms]![Ordentry]![Text29] = strNewRevNum
Else
Cancel = True
End If
End If

This will check for the 1700, if it is there, it will ask if you want to
update the R number. If so, add one and continue. If not, then cancel. The
Mid function will get a specified number of characters from the middle of a
string starting at the indicated point. If the number of characters to
retrieve is missing, then Mid will start at the indicated point and go to
the end of the string. The Val function will return the number portion of a
string, ending at the first non numeric character. The Replace function will
replace the indicated text with the new text in where ever the indicated
text is found in the string. Since the letter R only appears once in the
string, this will pin down where to make the replacement.

This is untested, but should be close.

--
Wayne Morgan
Microsoft Access MVP


AN said:
Hi
I've the following code for restricting the duplication of OrdRef. It's
working fine, but i have one more requirement.The OrdRef format is
"PO/1700-R.0/2004". The "R.0" stands for the revision of the order.

BeforeUpdate Event:
------------------
Dim OrdNo As Integer
OrdNo = DCount("[OrdRef]", "OrdTBL", "[OrdRef] = '" &
[Forms]![Ordentry]![Text29] & "'")

If OrdNo <> 0 Then
MsgBox "DUPLICATION !"
Cancel = True
End If

Table name = OrdTBL
Field in table = OrdRef
Form Name = Ordentry
Control on form = Text29

What i am looking for is the following
When I add a new record with the same number(1700) it should warn me that
i
am duplicating, however it should allow me to modify the existing number
as
PO/1700-R.1/2004 if required.

In other words when a new record is add the system should check the number
in between the two slashes if the number (eg:1700) exist then it should
not
allow, else continue...

Please help.
Click to expand...
 
Back
Top