Run-time error '94'

[HawaiiMacAddict]

Aloha everyone,

I am creating a Help Desk database at work, but have run into an issue
with a form. I'm using the same procedure I've used in the past, but
this time I get the infamous Run-time error '94' message.

Here are the details. I have a table named tblTickets, where I will be
storing all pertinent information about the trouble tickets. I have a
field named SeqNum, which contains a Data Type of Number, which is a
Long Integer. The Default Value of the field has been set to 0. The
Primary Key field of this table is Named TicketNum, which contains a
Data Type of Text, and is set to a value of 8 spaces. My intention is
to pad the Trouble Ticket numbers to four spaces followed by the year
eg., 0001-06 (for the first trouble ticket in 2006).

To that end, I've created a form based on a query of the tables in the
database. I have a label, named lblYr, on the form with the year as
its caption. I also have a text box, named txtSeqNum, whose data
source is the SeqNum field in the tblTicket table.

Here is the VB Code that I am trying to use:

Private Sub Form_Current()

Dim NextSeq As String

If Me.NewRecord then
NextSeq = DMax("[SeqNum]" , "tblTicket") + 1
Me.txtSeqNum.Value = NextSeq
NextSeq = right("0000" + NextSeq, 4)
NextSeq = NextSeq + lblYr.Caption
Me.txtTicketNum.Value = NextSeq
End If

End Sub

Here's how it is supposed to work. The value from the SeqNum field in
the tblTicket table is taken and incremented by 1, then assigned to the
txtSeqNum text box on the form. Next, it's padded with up to three 0s,
in order to make it a 4-digit number. Then, the 2-digit year, the
caption of the lblYr label is appended to the end of the value now in
txtSeqNum. Finally, the complete sequence is assigned to the
txtTicketNum text box. As the data source of the txtTicketNum text box
is TicketNum field in the tblTicket table, the resulting value is
assigned to that field.

I really need this to work in this database. I have used this same
process in a different database, and it works like a charm, but this
time, I'm getting the run time error. I don't understand why, and if
anyone can assist me with finding the solution to this rather vexing
problem, I will be most appreciative and forever be in your debt.

Mahalo in advance for your patience in reading this posting,

William Jones

 

[Allen Browne]

Assigning a value to a bound control in the Current event of the form is a
really bad idea. As soon as you visit a record (e.g. if you enter a record,
and then step back to the one you just entered), a new value is assigned.
Instead, use the BeforeInsert event of the form.

The next issue, is that if there are no matching records, DMax() returns
Null, and you are trying to assign that value to the string. String variable
cannot contain Null, which is the source of Error 94. There are 2 ways to
address this:
- Declare a Variant instead of a string:
Dim NextSeq As String
or
- Use Nz() to indicate the value to use if there is no match:
NextSeq = Nz(DMax("[SeqNum]" , "tblTicket"), 0) + 1

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

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

Aloha everyone,

I am creating a Help Desk database at work, but have run into an issue
with a form. I'm using the same procedure I've used in the past, but
this time I get the infamous Run-time error '94' message.

Here are the details. I have a table named tblTickets, where I will be
storing all pertinent information about the trouble tickets. I have a
field named SeqNum, which contains a Data Type of Number, which is a
Long Integer. The Default Value of the field has been set to 0. The
Primary Key field of this table is Named TicketNum, which contains a
Data Type of Text, and is set to a value of 8 spaces. My intention is
to pad the Trouble Ticket numbers to four spaces followed by the year
eg., 0001-06 (for the first trouble ticket in 2006).

To that end, I've created a form based on a query of the tables in the
database. I have a label, named lblYr, on the form with the year as
its caption. I also have a text box, named txtSeqNum, whose data
source is the SeqNum field in the tblTicket table.

Here is the VB Code that I am trying to use:

Private Sub Form_Current()

Dim NextSeq As String

If Me.NewRecord then
NextSeq = DMax("[SeqNum]" , "tblTicket") + 1
Me.txtSeqNum.Value = NextSeq
NextSeq = right("0000" + NextSeq, 4)
NextSeq = NextSeq + lblYr.Caption
Me.txtTicketNum.Value = NextSeq
End If

End Sub

Here's how it is supposed to work. The value from the SeqNum field in
the tblTicket table is taken and incremented by 1, then assigned to the
txtSeqNum text box on the form. Next, it's padded with up to three 0s,
in order to make it a 4-digit number. Then, the 2-digit year, the
caption of the lblYr label is appended to the end of the value now in
txtSeqNum. Finally, the complete sequence is assigned to the
txtTicketNum text box. As the data source of the txtTicketNum text box
is TicketNum field in the tblTicket table, the resulting value is
assigned to that field.

I really need this to work in this database. I have used this same
process in a different database, and it works like a charm, but this
time, I'm getting the run time error. I don't understand why, and if
anyone can assist me with finding the solution to this rather vexing
problem, I will be most appreciative and forever be in your debt.

Mahalo in advance for your patience in reading this posting,

William Jones

 

[William Jones]

Assigning a value to a bound control in the Current event of the form
is a really bad idea. As soon as you visit a record (e.g. if you enter
a record, and then step back to the one you just entered), a new value
is assigned. Instead, use the BeforeInsert event of the form.

The next issue, is that if there are no matching records, DMax()
returns Null, and you are trying to assign that value to the string.
String variable cannot contain Null, which is the source of Error 94.
There are 2 ways to address this:
- Declare a Variant instead of a string:
Dim NextSeq As String
or
- Use Nz() to indicate the value to use if there is no match:
NextSeq = Nz(DMax("[SeqNum]" , "tblTicket"), 0) + 1

Aloha Allen,

I don't know how to explain it, but the way I had it before works
perfectly under the other database. I had a bit of "outside" help in
creating it, as I wasn't able to wrap my head around everything they
wanted to do with it.

I just wanted to thank you for your "outside" help in this one. I had
already instantiated NextSeq as a String, so I used the Nz() function
and now it works perfectly! Thank you ever so much for that. I am
ever learning how to do things, if not completely differently, just
that much different than before.

Mahalo again,

William Jones

--
---------
HawaiiMacAddict

20" iMac Dual Core
15" MacBook Pro Core2Duo

Successful WinXP->MacOX conversion as of 15 August 2006