Forgive me, I'm pretty new to database programming and, though I have immersed myself in learning material, I haven't found out how to do this yet but really need an answer. I'm thinking it's a basic conceptual problem I'm having preventing me from coming up with an answer.
I have an entry form for inputting trips for a dispatch service. Most of the fields are related to the trips table, but there is a separate table for customers, which includes their phone numbers, names (first & last in same field), and address.
The first thing someone is supposed to do is to select or type in the phone number (which is a listbox populated by a SELECT DISTINCT of [trips]![phonenumber]).
When the phone number is selected, I'm using DLOOKUP() to populate the [customername].RowSource AND [customeraddress].RowSource with all the customer names and addresses at that phone number; when the name is selected, I use DLOOKUP() again to further narrow down [customeraddress].RowSource by populating it with records that match the selected phone number & customer name.
(ideally, each time you select a field the rest of the listboxes related to the customer would default to the most common matches in the [Trips] table, and the selection list would be in order of how frequent they match in the [Trips] table, but right now they default to the first match - not a big deal I'm thinking that'll be some sort of SQL statement I'll have to work out).
Now, what I want, is for it to create a new record in [Customers] with the filled in fields if necessary, but to just select the appropriate record if the entry already exists.
The problem I'm having, is that every record entry with this form creates a new [Customers] record, even if it's identical to an existing record.
I hope that I have explained the problem and what I wish adequately, any solutions, or at least points to where I should be looking to learn about this will be greatly appreciated!
I have an entry form for inputting trips for a dispatch service. Most of the fields are related to the trips table, but there is a separate table for customers, which includes their phone numbers, names (first & last in same field), and address.
The first thing someone is supposed to do is to select or type in the phone number (which is a listbox populated by a SELECT DISTINCT of [trips]![phonenumber]).
When the phone number is selected, I'm using DLOOKUP() to populate the [customername].RowSource AND [customeraddress].RowSource with all the customer names and addresses at that phone number; when the name is selected, I use DLOOKUP() again to further narrow down [customeraddress].RowSource by populating it with records that match the selected phone number & customer name.
(ideally, each time you select a field the rest of the listboxes related to the customer would default to the most common matches in the [Trips] table, and the selection list would be in order of how frequent they match in the [Trips] table, but right now they default to the first match - not a big deal I'm thinking that'll be some sort of SQL statement I'll have to work out).
Now, what I want, is for it to create a new record in [Customers] with the filled in fields if necessary, but to just select the appropriate record if the entry already exists.
The problem I'm having, is that every record entry with this form creates a new [Customers] record, even if it's identical to an existing record.
I hope that I have explained the problem and what I wish adequately, any solutions, or at least points to where I should be looking to learn about this will be greatly appreciated!
