I've been plodding along in my attempt to redesign my contact manager that is currently in an Access 2.0 database. I have made some good progress with Alpha Five v.7. I have designed the new system entirely with standard menus, genies, and Active Scripting. So far. I'm an amateur database developer. My real job is managing a fireplace dealership (selling, installing, servicing fireplaces and stoves).
I designed some tables and related them in sets. I have a master form attached to the main set where I want everyone to start. The idea is to first go to the customer records and then branch off to other records, such as contact requests, unit specs, estimates, and invoices. It was working fine up to the point of adding and editing request records. Yesterday I added the unit specs form and now I am having some problems. I think it is related to how I have the set arranged but I can't figure out how to solve it. I have a sample of the database zipped up and can post it. Here's the basic layout:
Customers
|====>Requests
...............|--------Units
...............|=====Estimates
Customers is linked to Requests as One-to-Many, Requests is linked to Units as One-One and Estimates as One-to-Many.
The customer number field is the linking field.
On my master form for this set I have the customer data placed directly on the form. The request, unit, and estimate data is on a tabbed form and is accessed through embedded browses. This worked fine for the requests data. I was able to zoom to the child record and edit and start up the form to add a new record. The request browse would automatically be updated. I have attemped to set up the same thing for the unit data but have ran into a problem. When I try to add a new record I get an error saying the "custnum" is required. This error repeats itself every time I move from one field to another.
I think it is trying to tell me that the requests table requires the customer number field to be filled. That is to be expected since I have field rules set up to require customer numbers on all records in the Requests table and the Units table. What I can't figure out is how to satisfy the requirement to fill in the customer number in the Requests table when entering a new record into the Units table.
I want all Unit records to be entered with this form to be entered only when a Request already exists. The Units table should be able to get the custnum field value from the Requests table. The process should be something like this:
Add customer record to Customers table
Add request to Requests table, linking together with the Custnum field
Add units to the Units table, linking from the existing Requests record on the RequestNum field.
A request cannot be entered without a customer number. A unit cannot be entered without a request number and a customer number.
Actually, I want to be able to enter Units without a request existing, but not from this form and set. I will have another form for entering units that are not linked to requests.
I thought of trying to remove the field rule that requires the customer number to see if it will work to ad new records. But I really need to enforce the rule that all requests and units have a customer number entry. Yet, one customer can have several requests. And one customer can have several units. But each request can only have one unit, or no units, linked.
Where have I strayed from the pure path of database design? Can you give me some guidance on how to structure my sets and forms correctly to get the results I want?
Thanks,
Sean Kennedy
I designed some tables and related them in sets. I have a master form attached to the main set where I want everyone to start. The idea is to first go to the customer records and then branch off to other records, such as contact requests, unit specs, estimates, and invoices. It was working fine up to the point of adding and editing request records. Yesterday I added the unit specs form and now I am having some problems. I think it is related to how I have the set arranged but I can't figure out how to solve it. I have a sample of the database zipped up and can post it. Here's the basic layout:
Customers
|====>Requests
...............|--------Units
...............|=====Estimates
Customers is linked to Requests as One-to-Many, Requests is linked to Units as One-One and Estimates as One-to-Many.
The customer number field is the linking field.
On my master form for this set I have the customer data placed directly on the form. The request, unit, and estimate data is on a tabbed form and is accessed through embedded browses. This worked fine for the requests data. I was able to zoom to the child record and edit and start up the form to add a new record. The request browse would automatically be updated. I have attemped to set up the same thing for the unit data but have ran into a problem. When I try to add a new record I get an error saying the "custnum" is required. This error repeats itself every time I move from one field to another.
I think it is trying to tell me that the requests table requires the customer number field to be filled. That is to be expected since I have field rules set up to require customer numbers on all records in the Requests table and the Units table. What I can't figure out is how to satisfy the requirement to fill in the customer number in the Requests table when entering a new record into the Units table.
I want all Unit records to be entered with this form to be entered only when a Request already exists. The Units table should be able to get the custnum field value from the Requests table. The process should be something like this:
Add customer record to Customers table
Add request to Requests table, linking together with the Custnum field
Add units to the Units table, linking from the existing Requests record on the RequestNum field.
A request cannot be entered without a customer number. A unit cannot be entered without a request number and a customer number.
Actually, I want to be able to enter Units without a request existing, but not from this form and set. I will have another form for entering units that are not linked to requests.
I thought of trying to remove the field rule that requires the customer number to see if it will work to ad new records. But I really need to enforce the rule that all requests and units have a customer number entry. Yet, one customer can have several requests. And one customer can have several units. But each request can only have one unit, or no units, linked.
Where have I strayed from the pure path of database design? Can you give me some guidance on how to structure my sets and forms correctly to get the results I want?
Thanks,
Sean Kennedy
Comment