Adding new customer with combo box ID lookup

[BFish]

My db tracks invoice sales by customer, up until now I have I have had the
phone attached to the main table (one field). I should have seen the writing
on the wall with extension numbers, multiply phone numbers ect.

I have split the tables, modified my main form, which can lookup a customer
by Business Name, Account or Phone number, all unbound. Table relationship
is one to many (Business phone, Cell, 800 ect.). I have most everthing
working except that when I need to enter a new customer for a phone number.

My setup is the lookup form gets the data from the unbound lookup controls,
data is then editable then drops to a subform for invoice entry. If a new
phone is entered I have a acdialog popup with phone fields to add, phone
number, ext., type of phone. and then the combo box control to attach a
current businessID if the new phone is a multiple phone contact. When I save
this record on popup close all fields are then populated back to main lookup
form for invoice entry.

My problem comes in adding a new phone number as well as a new business, with
the combo box looking up the business ID (which is autonumber) and
referential integrity enforced (all businesses must have a phone).

I have tried to use notinthelist vb, but run into an issue of control source
and lookup field not being the same enity.

Has anyone been down this road and is there a solution, as I am typing my
thoughts are maybe to use a dialog box before the phoneadd form pops up to
ask if this is a new business, but with over 5,000 business the user probably
won't know on an absolute.

Also any ideas on how better to make sure a phone number is unique before
being added, right now I have phone table properties for phone set to no
duplicates. I want to ultimately make sure a business is entered only one
time.

Any help will be greatly appreciated.

I am sure more information is needed on this db setup so please ask.

Thanks,

Bill

 

[Klatuu]

I would change the construction a bit. Rather than using ubound controls, I
would include the Customer table in a query and base my form on that. As to
the multiple contacts and phones per customer, I would make that a subform.
If one contact for a company could have multiple phones, then you would need
a subform for contacts and a subform in the subform for phones for the
contact.

This would also solve the multiple business problem. Use a combo to look up
the customer. If the customer already exists, it is in the combo list. If
it does not, you can use the Not In List event to open a form to add the new
customer Info.