Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Data Validation question

Status
Not open for further replies.
sparkbyte

sparkbyte

Technical User
Sep 20, 2002
879
0
0
US
MailroomTracing.ADP with backend to MS SQL 2005 STD.

On the Tracking input form I need to add some data validation rules but I am not sure where to do it.

I was thinking that it should be in the form, but would like a little advice on this one.


Table is.
Code: 
USE [MailroomTracking]
GO
/****** Object:  Table [dbo].[tblTrackingTable]    Script Date: 05/12/2010 16:37:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblTrackingTable](
	[Tracking_ID] [int] IDENTITY(1,1) NOT NULL,
	[EmployeeID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[MachineName] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[BoxNumber] [varchar](45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[FileNumber] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[TrackingDate] [datetime] NULL,
 CONSTRAINT [PK_tblTrackingTable] PRIMARY KEY CLUSTERED 
(
	[Tracking_ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

So the logic should be something like.

[BoxNumber] cannot equal [FileNumber] AND [BoxNumber] > 13 aphanumeric characters long AND [FileNumber] like [a-zA-Z]% but less than 10 characters long OR FileNumber] like [a-zA-Z][a-zA-Z][a-zA-Z]% but less than 13 characters long.



Thanks

John Fuhrman
 
Sort by date Sort by votes
not sure of your logic what are ands and what are ors
but if this a user input form chaeck in th bfore update
Code: 
if me.boxnumber= me.filenumber then
    cancel = true
    msgbox "boxnumber= filenumber"
    exit sub 
end if
if something elses= the third condision then
    cancel = true
    msgbox "no goood"
    exit sub 
end if
.......
 
Upvote 0 Downvote
PWise, that is quite close.

How would I

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo err_Handler

If Me.BoxNumber = Me.FileNumber Then
Cancel = True
MsgBox "The Tracking Number and the File Number" & _
vbCrLf & "cannot equal each other!!!", 0, "ERROR"
Exit Sub
End If

[highlight]If Me.BoxNumber < "13 characters long" Then [/highlight]
Cancel = True
MsgBox "Not a Valid Tracking Number"
Exit Sub
End If

[highlight]If Me.FileNumber Like "[a-zA-Z]*" Then[/highlight]
Cancel = True
MsgBox "Not a Valid File Number"
Exit Sub
End If


endit:
Exit Sub

err_Handler:
Select Case Err
Case 2501
'Do Nothing
Case Else
MsgBox _
"An unexpected error has been detected" & Chr(13) & _
"Error Number: " & Err.Number & " , " & Err.Description & Chr(13) & _
vbCrLf & "Please note the above details before contacting support"
End Select
Resume endit
End Sub


Thanks

John Fuhrman
 
Upvote 0 Downvote
If Me.BoxNumber < "13 characters long" Then
If len(Me.BoxNumber) < 13 Then
 
Upvote 0 Downvote
If Me.FileNumber Like "[a-zA-Z]*" Then

VBA does not suport regular expressions and i am not to fimilar with then can you please explain what this means
 
Upvote 0 Downvote
I think I have most of the logic figured out.

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo err_Handler

If Me.BoxNumber = Me.FileNumber Then
    Cancel = True
    MsgBox "ERROR" & vbCrLf & vbCrLf & _
    "The Tracking Number and the File Number cannot equal each other!!!", 0 + 48, "ERROR"
    Exit Sub
End If

If Len(Me.BoxNumber) < 13 Then
    Cancel = True
    MsgBox "Not a Valid Tracking Number." & vbCrLf & vbCrLf & _
    "The tracking Number entered must be at least 13 characters long.", 0 + 48, "ERROR"
    Exit Sub
End If

If Len(Me.FileNumber) <= 13 Then
    If IsNumeric(left(Me.FileNumber, 1)) = True Then
        Cancel = True
        MsgBox "Not a Valid File Number", 0 + 48, "ERROR"
        Exit Sub
    End If
End If

endit:
Exit Sub

err_Handler:
    Select Case Err
        Case 2501
            'Do Nothing
        Case Else
            MsgBox _
            "An unexpected error has been detected" & Chr(13) & _
            "Error Number: " & Err.Number & " , " & Err.Description & Chr(13) & _
            vbCrLf & "Please note the above details before contacting support"
    End Select
Resume endit
End Sub

The last part I am still working on is

like [a-zA-Z][a-zA-Z][a-zA-Z]% but less than 13 characters long.

These would be for reciept files numbers. The format is like this.

AAA0123456789



Thanks

John Fuhrman
 
Upvote 0 Downvote
OK, I have most of this figured out.

I am getting an error when trying to advance to a new record.

The error is "Run-time error '2105'"
"You can't go to the specified record."

The line the debugger goes to is highlighted.

Is there an alternative to the acNewRecord method in an ADP??

Just for clarification. The table has a PK and auto incrementing ID that is an identity field.

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo err_Handler

Dim strResult As String

If Me.BoxNumber = Me.FileNumber Then
    Cancel = True
    MsgBox "ERROR" & vbCrLf & vbCrLf & _
    "The Tracking Number and the File Number cannot equal each other!!!", 0 + 48, "ERROR"
    Exit Sub
End If

If Len(Me.BoxNumber) < 13 Then
    Cancel = True
    MsgBox "Not a Valid Tracking Number." & vbCrLf & vbCrLf & _
    "The tracking Number entered must be at least 13 characters long.", 0 + 48, "ERROR"
    Exit Sub
End If

If Len(Me.FileNumber) <= 10 Then
    If IsNumeric(left(Me.FileNumber, 1)) = True Then
        Cancel = True
        strResult = MsgBox("Not a Valid File Number" & vbCrLf & vbCrLf & _
                "Is the File Number entered correct?", 4 + 48, "ERROR")
        If strResult = vbYes Then
            [highlight]DoCmd.GoToRecord , , acNewRec[/highlight]
            Exit Sub
        Else
            Me.FileNumber.Undo
            Exit Sub
        End If
    End If
End If


If Len(Me.FileNumber) > 10 Then
    If IsNumeric(left(Me.FileNumber, 3)) = True Then
        Cancel = True
        strResult = MsgBox("Not a Valid Reciept Number" & vbCrLf & vbCrLf & _
                "Is the Reciept Number entered correct?", 4 + 48, "ERROR")
        If strResult = vbYes Then
            Exit Sub
        Else
            Me.FileNumber.Undo
            Exit Sub
        End If
    End If
End If

endit:
Exit Sub

err_Handler:
    Select Case Err
        Case 2501
            'Do Nothing
        Case Else
            MsgBox _
            "An unexpected error has been detected" & Chr(13) & _
            "Error Number: " & Err.Number & " , " & Err.Description & Chr(13) & _
            vbCrLf & "Please note the above details before contacting support"
    End Select
Resume endit
End Sub

Thanks

John Fuhrman
 
Upvote 0 Downvote
OK, I believe I have solved most of the issues.

Here is what seems to work.

Comment are welcome.

Code: 
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo err_Handler

Dim strResult As String

If Me.BoxNumber = Me.FileNumber Then
    Cancel = True
    MsgBox "ERROR" & vbCrLf & vbCrLf & _
    "The Tracking Number and the File Number cannot equal each other!!!", 0 + 48, "ERROR"
    Me.BoxNumber.Value = ""
    Me.FileNumber.Value = ""
    Me.TrackingDate.Value = Now
    Me.BoxNumber.SetFocus
    Exit Sub
End If

If Len(Me.BoxNumber) < 13 Then
    Cancel = True
    MsgBox "Not a Valid Tracking Number." & vbCrLf & vbCrLf & _
    "The tracking Number entered must be at least 13 characters long.", 0 + 48, "ERROR"
    Exit Sub
End If

If Len(Me.FileNumber) <= 10 Then
    If IsNumeric(left(Me.FileNumber, 1)) = True Then
        Cancel = True
            strResult = MsgBox("Not a Valid File Number", 0 + 48 + 65536, "ERROR")
            Select Case strResult
                Case vbOK
                    MsgBox "Please Correct the File Number", 0 + 48 + 65536, "ERROR"
                    Me.FileNumber.Value = ""
                    Exit Sub
                Case vbCancel
                    Exit Sub
                Case vbAbort
                    Exit Sub
                Case vbRetry
                    MsgBox "Please Correct the File Number", 0 + 48 + 65536, "ERROR"
                    Me.FileNumber.Value = ""
                    Exit Sub
                Case vbIgnore
                    Exit Sub
                Case vbYes
                    MsgBox "Please Correct the File Number", 0 + 48 + 65536, "ERROR"
                    Me.FileNumber.Value = ""
                    Exit Sub
                Case vbNo
                    MsgBox "Please Correct the File Number", 0 + 48 + 65536, "ERROR"
                    Me.FileNumber.Value = ""
                    Exit Sub
                Case Else
                    Exit Sub
            End Select
    End If
End If

If Len(Me.FileNumber) > 10 Then
    If IsNumeric(left(Me.FileNumber, 3)) = True Then
        Cancel = True
            strResult = MsgBox("Not a Valid Reciept Number", 0 + 48 + 65536, "ERROR")
            Select Case strResult
                Case vbOK
                    MsgBox "Please Correct the Reciept Number", 0 + 48 + 65536, "ERROR"
                    Me.FileNumber.Value = ""
                    Exit Sub
                Case vbCancel
                    Exit Sub
                Case vbAbort
                    Exit Sub
                Case vbRetry
                    MsgBox "Please Correct the Reciept Number", 0 + 48 + 65536, "ERROR"
                    Me.FileNumber.Value = ""
                    Exit Sub
                Case vbIgnore
                    Exit Sub
                Case vbYes
                    MsgBox "Please Correct the Reciept Number", 0 + 48 + 65536, "ERROR"
                    Me.FileNumber.Value = ""
                    Exit Sub
                Case vbNo
                    MsgBox "Please Correct the Reciept Number", 0 + 48 + 65536, "ERROR"
                    Me.FileNumber.Value = ""
                    Exit Sub
                Case Else
                    Exit Sub
            End Select
    End If
End If


endit:
Exit Sub

err_Handler:
    Select Case Err
        Case 2501
            'Do Nothing
        Case 2046
            'Command not available
            MsgBox "You cannot add a record at this time.", vbInformation, "Not Available"
        Case Else
            MsgBox _
            "An unexpected error has been detected" & Chr(13) & _
            "Error Number: " & Err.Number & " , " & Err.Description & Chr(13) & _
            vbCrLf & "Please note the above details before contacting support"
    End Select
Resume endit
End Sub

Thanks

John Fuhrman
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

breezett93
  • Locked
  • Question
Use domain accounts to log in to Access database
Replies
0
Views
101
breezett93
breezett93
batteam
  • Locked
  • Question
Importing Excel data to SQL table using MSAccess .adp
Replies
3
Views
92
lameid
lameid
Baesucks
  • Locked
  • Question
Trying to insert data from a table in another database
Replies
3
Views
53
lameid
lameid
LeonAtRC
  • Locked
  • Question
Import/Export data by type
Replies
2
Views
41
LeonAtRC
LeonAtRC
sparkbyte
  • Locked
  • Question
Lookup Button (current data or archive)
Replies
1
Views
38
LeonAtRC
LeonAtRC

Part and Inventory Search

Sponsor

Back
Top