|
|
I have this code that I'm trying to fix. The data type on a field
(Field_Ticket_Number) was changed from integer on the back end to
nvarchar. I'm a beginner and am trying to figure out how to make this
code work now with a string instead of number:
Private Sub dbo_Field_Ticket_Header_Field_Ticket_Number_BeforeUpdate
(Cancel As Integer)
Dim lngField_Ticket_Number As Long, strCriteria As String
If IsNumeric(Me.dbo_Field_Ticket_Header_Field_Ticket_Number) Then
lngField_Ticket_Number =
Me.dbo_Field_Ticket_Header_Field_Ticket_Number
strCriteria = "[Field_Ticket_Number] = " &
lngField_Ticket_Number
If SearchTableByCriteria("dbo_Field_Ticket_Header",
strCriteria) Then
MsgBox "That ticket number already exists in the database.
Please enter another ticket number.", vbExclamation
Cancel = True
End If
Else
MsgBox "INVALID TICKET NUMBER.", vbExclamation
Cancel = True
End If
End Sub
|
|
Hi,
Try changing your strCriteria = line to this:
strCriteria = "[Field_Ticket_Number] = """ & lngField_Ticket_Number & """"
This assumes that there are no leading zeroes. If there are then try:
strCriteria = "[Field_Ticket_Number] = """ &
Format$(lngField_Ticket_Number, "000000") & """"
Substituting the appropriate number of zeroes to get the right total
number of digits being stored.
Hope that helps,
Clifford Bass
"p-rat" wrote:
[Quoted Text] > I have this code that I'm trying to fix. The data type on a field
> (Field_Ticket_Number) was changed from integer on the back end to
> nvarchar. I'm a beginner and am trying to figure out how to make this
> code work now with a string instead of number:
>
>
> Private Sub dbo_Field_Ticket_Header_Field_Ticket_Number_BeforeUpdate
> (Cancel As Integer)
> Dim lngField_Ticket_Number As Long, strCriteria As String
> If IsNumeric(Me.dbo_Field_Ticket_Header_Field_Ticket_Number) Then
> lngField_Ticket_Number =
> Me.dbo_Field_Ticket_Header_Field_Ticket_Number
> strCriteria = "[Field_Ticket_Number] = " &
> lngField_Ticket_Number
> If SearchTableByCriteria("dbo_Field_Ticket_Header",
> strCriteria) Then
> MsgBox "That ticket number already exists in the database.
> Please enter another ticket number.", vbExclamation
> Cancel = True
> End If
> Else
> MsgBox "INVALID TICKET NUMBER.", vbExclamation
> Cancel = True
> End If
>
> End Sub
|
|
When making this change to the code I have above it gives me the
INVALID TICKET NUMBER error. Would you know why this is happening?
Thanks for your response and help on this.
On Dec 22, 8:49 pm, Clifford Bass
<CliffordB...[ at ]discussions.microsoft.com> wrote:
[Quoted Text] > Hi,
>
> Try changing your strCriteria = line to this:
>
> strCriteria = "[Field_Ticket_Number] = """ & lngField_Ticket_Number & """"
>
> This assumes that there are no leading zeroes. If there are then try:
>
> strCriteria = "[Field_Ticket_Number] = """ &
> Format$(lngField_Ticket_Number, "000000") & """"
>
> Substituting the appropriate number of zeroes to get the right total
> number of digits being stored.
>
> Hope that helps,
>
> Clifford Bass
>
>
>
> "p-rat" wrote:
> > I have this code that I'm trying to fix. The data type on a field
> > (Field_Ticket_Number) was changed from integer on the back end to
> > nvarchar. I'm a beginner and am trying to figure out how to make this
> > code work now with a string instead of number:
>
> > Private Sub dbo_Field_Ticket_Header_Field_Ticket_Number_BeforeUpdate
> > (Cancel As Integer)
> > Dim lngField_Ticket_Number As Long, strCriteria As String
> > If IsNumeric(Me.dbo_Field_Ticket_Header_Field_Ticket_Number) Then
> > lngField_Ticket_Number =
> > Me.dbo_Field_Ticket_Header_Field_Ticket_Number
> > strCriteria = "[Field_Ticket_Number] = " &
> > lngField_Ticket_Number
> > If SearchTableByCriteria("dbo_Field_Ticket_Header",
> > strCriteria) Then
> > MsgBox "That ticket number already exists in the database.
> > Please enter another ticket number.", vbExclamation
> > Cancel = True
> > End If
> > Else
> > MsgBox "INVALID TICKET NUMBER.", vbExclamation
> > Cancel = True
> > End If
>
> > End Sub- Hide quoted text -
>
> - Show quoted text -
|
|
Hi,
That would indicate that the ticket number is not numeric. Try
changing the error message line to this so you can actually see what it is
seeing it as:
MsgBox "INVALID TICKET NUMBER (" &
Me.dbo_Field_Ticket_Header_Field_Ticket_Number & ").", vbExclamation
Clifford Bass
"p-rat" wrote:
[Quoted Text] > When making this change to the code I have above it gives me the
> INVALID TICKET NUMBER error. Would you know why this is happening?
> Thanks for your response and help on this.
|
|
Yes, I'm sorry, but I need this field to be text such as 'A5453-1',
etc. It's seeing what I'm typing in but comes back as invalid. This
field was changed on the back-end to NVARCHAR and front-end to TEXT.
So I don't know what else needs to be changed in the code I originally
posted. Thanks.
On Dec 23, 9:13 am, Clifford Bass
<CliffordB...[ at ]discussions.microsoft.com> wrote:
[Quoted Text] > Hi,
>
> That would indicate that the ticket number is not numeric. Try
> changing the error message line to this so you can actually see what it is
> seeing it as:
>
> MsgBox "INVALID TICKET NUMBER (" &
> Me.dbo_Field_Ticket_Header_Field_Ticket_Number & ").", vbExclamation
>
> Clifford Bass
>
>
>
> "p-rat" wrote:
> > When making this change to the code I have above it gives me the
> > INVALID TICKET NUMBER error. Would you know why this is happening?
> > Thanks for your response and help on this.- Hide quoted text -
>
> - Show quoted text -
|
|
Hi,
That is a critical bit on information; that the reason for changing it
to text was to do those new formats. However, I probably could have guessed
that. Better to have it stated explicitely. Anyway, in that case, just
delete the If IsNumeric line and the Else through End If lines. Keep the
change to the strCriteria = line.
Of course, if your back end is well designed (i.e. unique index on
Field_Ticket_Number), you probably could eliminate this part entirely and let
the back end complain about a pre-existing ticket number. Perhaps a little
less tidy for the user.
Clifford Bass
"p-rat" wrote:
[Quoted Text] > Yes, I'm sorry, but I need this field to be text such as 'A5453-1',
> etc. It's seeing what I'm typing in but comes back as invalid. This
> field was changed on the back-end to NVARCHAR and front-end to TEXT.
> So I don't know what else needs to be changed in the code I originally
> posted. Thanks.
|
|
|