If then code for empty combo box column

[milwhcky via AccessMonster.com]

I have an order form with a combo box which contains a list of customers.
The combo's source is a query which provides an alphabetized list of
customers joined to the orders table.

One of the many columns of the combo box contains the most recent order
number. Each customer has a unique sequence of order numbers, and when the
customer is chosen from the list, the next order number is generated in the
combo's AfterUpdate code. It works fine until I attempt to choose a new
customer, as the previous order number column is empty for that customer.

The original working code:
Dim ctrl As Control
Set ctrl = Me.ActiveControl
Me.[OrderNumber]= Nz((ctrl.Column(23)),0)+1

Column 23 is the most recent order number for that customer. The combo uses
several columns to fill several fields on the form (shipping address fields,
billing address fields, shipping rates, instructions, etc.).

I've tried to write an If Then statement to deal with new customers, but I
keep getting error codes (usually Run-type error '13', type-mismatch).

Dim ctrl As Control
Set ctrl = Me.ActiveControl
If Len(Me.[OrderNumber]) = 0 Then
Me.[OrderNumber] = 10001
' Each customer's order number sequence begins at 10001
Else
Me.[OrderNumber]= Nz((ctrl.Column(23)),0)+1
End If

I've also tried:
If (Me.[OrderNumber]) = ""
and
If IsNull(Me.[OrderNumber]) = True

Also, the OrderNumber field on the Orders table does not have a Default Value
and is a Long Integer.

 

[Jeff Boyce]

A 'type mismatch' error usually indicates that you're trying to calculate
using a text/character data type ... algebra aside, can you really add "a" &
1?<g>

Re-check the table definitions for the fields involved.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[milwhcky via AccessMonster.com]

My form generates a proper order number for existing customers (with old code
or new attempt at if/then code). I'm only getting an error with a new
customer. Same field (OrderNumber), but it's empty (no previous order number)

 

[Jeff Boyce]

Nope.

I would have thought your Nz() statement would guarantee a 0 to which you
could add 1.

How are you deriving the value in your Column(23)? (and did you remember
that .Column(23) refers to the 24th column in the underlying query?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[milwhcky via AccessMonster.com]

I found my mistake...
Instead of
If Len(Me.[OrderNumber]) = 0 Then
Me.[OrderNumber] = 10001

It should have been
If Len(ctrl.Column(23)) = 0 Then
Me.[OrderNumber] = 10001

Column(23) is a query field of the highest previous order number for each
customer...
Max(Orders.OrderNumber) AS MaxOfOrderNumber
It's an empty field for new customers, but was working for existing customers.


My combo was set up properly, but I didn't think through my if/then/else
statement enough while writing it.

Thanks for your time, Jeff

Nope.

I would have thought your Nz() statement would guarantee a 0 to which you
could add 1.

How are you deriving the value in your Column(23)? (and did you remember
that .Column(23) refers to the 24th column in the underlying query?

Regards

Jeff Boyce
Microsoft Access MVP

My form generates a proper order number for existing customers (with old
code

[quoted text clipped - 23 lines]