Stumped - linking customers into orders -interface to match relations - existing vs. new cust.

[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?

 

[DL]

A thought; Surely you cannot place an order if there is no customer in place
in the customer table? It seems to me that an order can be placed and the
customer is'nt acknowledged until they make a payment.
Perhaps the add customer should be in the Master Order form.
It may be that maybe you're linking the wrong fields.
Customer Table ID -> Master Order, CustomerID
Master OrderID -> Order Details ID
Payment Table ID -> Master Order ID
But there again, perhaps I'm missreading the scenario.

 

[KB]

You quite right, I'm stuck in the logic of how the program is now.
Like I said, the customer linking was added in latest in development,
and was added to fit into the payment model. I think you just knocked
me out of my thinking.. thanks.. let me go work with that.

I'm hoping - thinking ahead though that I don't still have problems
with the non limit to list property - but more on that later.

Thanks

 

[KB]

Not much luck. I'm focusing let on relationships at this point and
more on getting the customer entry to work the way I want it.

I want the data entry person to be able to enter a customer name, and
if it exists pull of the existing customer info (combo box).

If its a new customer, continue to enter new information, creates a
new customer record (combo box w/out limit to list).

However, I end up with the situation of having a combo getting its
information from the exact table I'm trying to populate.

I tried to use an unbound combo outside the subform with my customer
information (addr, etc...) and link the combo to the subform, that
didn't fly either.

 

[DL]

If I recollect, if you in some way add a client, the subform may not be
updated with the new info unless you specifically refresh? the subform data.
- forgive me if I'm wrong, I'm a little rusty -