Next number

[Ken Ivins]

I want to auto fill a field CheckNo when I create a new record with the next
check number.

I have a Table tblChecks with the check numbers (CheckNo field as a
Number-I'm modifying someone else's work), amounts etc.

I event created a query [qryCheckNum-recent] that shows the last check
number (using the "Max" total).

I tried below and got a type mismatch error

Dim intCkNum As Integer

intCkNum = DLookup ([CheckNo], "qryCheckNum-recent")
intCkNum = intCkNum + 1

Me.CheckNo = Nz(Me.CheckNo, intCkNum)


Please help me figure out what I am doing wrong. Maybe there is a better
way. Any ideas are always welcome.

Thanks,
Ken

 

[Guest]

To use your code
just fix the dlookup
DLookup ("[CheckNo]", "qryCheckNum-recent")
also is the intCkNum variable type is the same as the field in the table?

Now you don't have to use a query, instead use the dmax function
intCkNum = DMax ([CheckNo], "TableName")

 

[Ken Ivins]

Dear Ofer,

Thanks for the tip. I did also try it with the quotes around CheckNo and got
the same error. I then tried it with Dmax and got a Run Time Error 6
"Overflow". I changed the for field format to general Number and left it
blank and got the same error. That does not seem to be the problem. The
error occurs in the first line of the code (before it even gets to the
field.

I have even tried Dim (ing) the intCkNum to variant and long. With no
success.

Another note. I am using 2003 and working in 2000 mode. Is that a problem?

Still confused,

Ken Ivins

To use your code
just fix the dlookup
DLookup ("[CheckNo]", "qryCheckNum-recent")
also is the intCkNum variable type is the same as the field in the table?

Now you don't have to use a query, instead use the dmax function
intCkNum = DMax ([CheckNo], "TableName")

I want to auto fill a field CheckNo when I create a new record with the
next
check number.

I have a Table tblChecks with the check numbers (CheckNo field as a
Number-I'm modifying someone else's work), amounts etc.

I event created a query [qryCheckNum-recent] that shows the last check
number (using the "Max" total).

I tried below and got a type mismatch error

Dim intCkNum As Integer

intCkNum = DLookup ([CheckNo], "qryCheckNum-recent")
intCkNum = intCkNum + 1

Me.CheckNo = Nz(Me.CheckNo, intCkNum)


Please help me figure out what I am doing wrong. Maybe there is a better
way. Any ideas are always welcome.

Thanks,
Ken

 

[Guest]

Try this , you might get the overfolw because the number return is bigger
then integer
Dim intCkNum As double

intCkNum = nz(DLookup ("[CheckNo]", "qryCheckNum-recent"),0)
intCkNum = intCkNum + 1

Dear Ofer,

Thanks for the tip. I did also try it with the quotes around CheckNo and got
the same error. I then tried it with Dmax and got a Run Time Error 6
"Overflow". I changed the for field format to general Number and left it
blank and got the same error. That does not seem to be the problem. The
error occurs in the first line of the code (before it even gets to the
field.

I have even tried Dim (ing) the intCkNum to variant and long. With no
success.

Another note. I am using 2003 and working in 2000 mode. Is that a problem?

Still confused,

Ken Ivins

To use your code
just fix the dlookup
DLookup ("[CheckNo]", "qryCheckNum-recent")
also is the intCkNum variable type is the same as the field in the table?

Now you don't have to use a query, instead use the dmax function
intCkNum = DMax ([CheckNo], "TableName")

I want to auto fill a field CheckNo when I create a new record with the
next
check number.

I have a Table tblChecks with the check numbers (CheckNo field as a
Number-I'm modifying someone else's work), amounts etc.

I event created a query [qryCheckNum-recent] that shows the last check
number (using the "Max" total).

I tried below and got a type mismatch error

Dim intCkNum As Integer

intCkNum = DLookup ([CheckNo], "qryCheckNum-recent")
intCkNum = intCkNum + 1

Me.CheckNo = Nz(Me.CheckNo, intCkNum)


Please help me figure out what I am doing wrong. Maybe there is a better
way. Any ideas are always welcome.

Thanks,
Ken