Re: Can any one help - Restriction
From: Wayne Morgan (comprev_gothroughthenewsgroup_at_hotmail.com)
Date: 11/25/04
- Next message: Orv: "Re: Display The Next Record Number on 'New Record'?"
- Previous message: Orv: "Re: Display The Next Record Number on 'New Record'?"
- In reply to: AN: "Can any one help - Restriction"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 25 Nov 2004 07:00:42 -0600
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" <AN@discussions.microsoft.com> wrote in message
news:8236E0F6-3306-47E0-B6A2-341A96C053F8@microsoft.com...
> 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.
>
- Next message: Orv: "Re: Display The Next Record Number on 'New Record'?"
- Previous message: Orv: "Re: Display The Next Record Number on 'New Record'?"
- In reply to: AN: "Can any one help - Restriction"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
