Stumped - linking customers into orders - PLEASE read, I need to get this fixed!!

[KB]

I've got a custom access app, Access 2000.

A short bit of history. The app has master orders, in which there are
individual orders to seperate customers inside. While developing,
customer data was not included in the orders (there was some reasoning
for that), but later added in a crappy fashion. At present I'm trying
to reverse this and do it correctly. I'm having trouble linking
customer data to these "payments" within the master order in a 1 to
many relationship (1 customer, to many payments). Right now, the
system relies on duplicate customers for each payment. My problem
isn't in the relationships between tables, but rather building the
interface to work correctly in front of all that.

Relations go like this
Master Order
Payments (links to maser order id)
- then two tables related to payments
Customers (links via customer ID in payment record)
Order Detail lines - (links via payment ID)

My present interface has a master form (Maser Order) with a subform
(Payments) and two subforms within that (Customers & Order Details).

Now I can put a combo box in the payments subform, which will allow me
to select an existing customer (by name), and fill in the customer
subform - and all the linking is ok. Bound column is customer id
stored in payments table.

My problem is, I don't want to use limit to list. If a customer
already exists, fine - use that and save data entry (1 customer to
many payments). However, if its a new customer name, I need the combo
box to allow addition of that customer name, and create a new record
in the customer table that will be populated in the customer subform.

Since the lookup is actually in the payments table (where I need to
store my cust_id to link everything), it only knows of existing
records in the customer table.

Any thoughts?

 

[John Vinson]

My problem is, I don't want to use limit to list. If a customer
already exists, fine - use that and save data entry (1 customer to
many payments). However, if its a new customer name, I need the combo
box to allow addition of that customer name, and create a new record
in the customer table that will be populated in the customer subform.

I'm of the opinion that Limit to List is exactly what you DO want in
this situation. Use the Combo's Not In List event to open a form to
enter a new Customer, opening it in dialog mode; after you've added
the new customer, close the form and use the newly added name. That's
exactly what the Not In List event is designed to do!

John W. Vinson[MVP]

 

[KB]

I wasn't familiar with not in list event, I'll take a look at that.
If I can use it in a non-dialog format that might solve my problem.
The chances of an existing customer actually being entered for this
application would be like 4 a year or so, so to move the user off the
form would be time consuming.

I'll look into the not in list event however, thanks.

 

[John Vinson]

I wasn't familiar with not in list event, I'll take a look at that.
If I can use it in a non-dialog format that might solve my problem.
The chances of an existing customer actually being entered for this
application would be like 4 a year or so, so to move the user off the
form would be time consuming.

I'll look into the not in list event however, thanks.

Umm... I think you misunderstand.

If the customer is found, the combo box works just the way you're used
to - it simply selects the customer.

If the user types in a name which does not exist in the table (four
times a year) the Not In List event will fire and (depending on how
you've written it) open a form to enter the new customer, or pop up a
message box asking if they wish to do so. I can't see how 45 seconds
four times a year is all THAT time consuming!

John W. Vinson[MVP]

 

[KB]

98% of the time the customer will be NEW, so I'll try not to make a
popup for the new customer, rather have it the subform so its already
there. All that popping up, then closing of a different window will
get annoying.

 

[John Vinson]

98% of the time the customer will be NEW, so I'll try not to make a
popup for the new customer, rather have it the subform so its already
there. All that popping up, then closing of a different window will
get annoying.

Apologies. I did indeed misread your post.

John W. Vinson[MVP]

 

[KB]

With everyone's help, and suggestions I was able to get everything
working how I liked it. Thanks to All!