If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
debugging query-forms
I'd like to create a database to keep track of which routes go with which
carriers, and which substitute carriers are associated with them. A carrier can have more than one route, but a route can have only one carrier, and they change occasionally to frequently. After a lot of trial and error, I have designed a simple database to test Access' behavior. Forgetting the substitutes for the moment, I have constructed 2 tables: tblRoutes ID (AutoNum), primary key Route (Integer) Carrier (Long Integer) tblCarriers ID (AutoNum) LastName (String) Account# (Long Integer), primary key Carriers.Account# is related one-to-many to Routes.Carrier with referential integrity and cascade update and cascade delete enabled, and tblRoutes will naturally have more records than tblCarriers. I'm trying to add records to the tables via a form with a query as the data source: SELECT Routes.ID AS Routes_ID, Routes.Route, Routes.Carrier, Carriers.ID AS Carriers_ID, Carriers.LastName, Carriers.[Account Number] FROM Routes RIGHT JOIN Carriers ON Routes.Carrier=Carriers.[Account Number] ORDER BY Routes.Route; This was built in the Access Wizard, as I am not familiar with SQL, yet, and I've never used Access before and the help files are not very instructional for design view. If I try to enter a record for a route associated with a carrier already in the table, I can type in the Routes.Carrier field and Access will automatically fill in Carrier.LastName and Carrier.Account# boxes. But, if I try to enter a new route with a carrier not yet in the table via the Routes.Carrier field, I get the following error: The Microsoft Jet database engine cannot find a record in the table 'Carriers' with the key matching field(s) 'carrier'. .. . . meaning I need to enter the Carrier.Account# first. But, if I try to enter a record via the Carrier.Account# field, and that carrier already exists, I get an error message about not allowing duplicates in primary keys. So, I need to have a single text box on the data entry form that can sense which field to access: Routes.Carrier when the carrier already exists, and Carrier.Account# when the carrier does not yet exist. Is this accomplished via the query, or some switch in the form properties, or how? thank you for your help. |
#2
|
|||
|
|||
debugging query-forms
On Jul 11, 10:50*am, News Boy
wrote: I'd like to create a database to keep track of which routes go with which carriers, and which substitute carriers are associated with them. * A carrier can have more than one route, but a route can have only one carrier, and they change occasionally to frequently. After a lot of trial and error, I have designed a simple database to test Access' behavior. *Forgetting the substitutes for the moment, I have constructed 2 tables: * * *tblRoutes * * * * *ID (AutoNum), primary key * * * * *Route (Integer) * * * * *Carrier (Long Integer) * * tblCarriers * * * * *ID (AutoNum) * * * * *LastName (String) * * * * *Account# (Long Integer), primary key Carriers.Account# is related one-to-many to Routes.Carrier with referential integrity and cascade update and cascade delete enabled, and tblRoutes will naturally have more records than tblCarriers. I'm trying to add records to the tables via a form with a query as the data source: SELECT Routes.ID AS Routes_ID, Routes.Route, Routes.Carrier, Carriers.ID AS Carriers_ID, Carriers.LastName, Carriers.[Account Number] FROM Routes RIGHT JOIN Carriers ON Routes.Carrier=Carriers.[Account Number] ORDER BY Routes.Route; This was built in the Access Wizard, as I am not familiar with SQL, yet, and I've never used Access before and the help files are not very instructional for design view. If I try to enter a record for a route associated with a carrier already in the table, I can type in the Routes.Carrier field and Access will automatically fill in Carrier.LastName and Carrier.Account# boxes. *But, if I try to enter a new route with a carrier not yet in the table via the Routes.Carrier field, I get the following error: The Microsoft Jet database engine cannot find a record in the table 'Carriers' with the key matching field(s) 'carrier'. . . . meaning I need to enter the Carrier.Account# first. *But, if I try to enter a record via the Carrier.Account# field, and that carrier already exists, I get an error message about not allowing duplicates in primary keys. So, I need to have a single text box on the data entry form that can sense which field to access: *Routes.Carrier when the carrier already exists, and Carrier.Account# when the carrier does not yet exist. Is this accomplished via the query, or some switch in the form properties, or how? thank you for your help. The table structure here is somewhat confusing. I expected to see a schema in the form of: tblRoutes RouteId text(16) primary key, RouteDescription text(32) tblCarriers CarrierID text(16) primary key, LastName text(24) tblCarrierRouteLink RouteId text(16), CarrierID text(16), DateEffective datetime The form would have a record source of tblCarrierRouteLink. The tblCarrierRouteLink.RouteId would be restricted to the values in tblRoutes.RouteID and the tblCarrierRouteLink.CarrierID would be restricted to the values in tblCarriers.CarrierID. These restrictions would be enforced by a form that had the RouteID and CarrierID with a Row Source of tblRoute and tblCarriers respectively. The user's job would be to select the RouteId and the CarrierID combinations from drop-down list boxes and supply the DateEffective in a text edit field. With this data structure, you can accommodate one carrier handling one or more routes, idle carriers, unassigned routes and carriers who assume route duties temporarily. |
#3
|
|||
|
|||
debugging query-forms
Thank you for your suggestion. Sadly, it does not solve the problem. The
question is, how to enter a New carrier without getting an error message? For example, if I want to assign a route to someone Not Yet in the data base, I can't enter their key into tblCarrierRouteLink.CarrierID because they don't exist in tblCarriers. So the control box on the form must reference tblCarriers.CarrierID. On the other hand, if I want to assign a route to someone who Is Already in the data base, and the form's control box references tblCarriers.CarrierID, I get an error message about duplicate primary keys. So the control box on the form must Not reference tblCarriers.CarrierID. There are many examples of similar structures - one supplier, many products; one customer, many orders - in the Help files. They all use two tables - Suppliers and Products, Customers and Orders. They all show a similar situation as mine - one carrier, many routes. But None of them explain how to set up a form that can add a record to one table but not the other. How can I use forms to add an Order to a Customer, a Product to a Supplier, a Route to a Carrier? That's the question. Here is a sample of the data: Routes 5 14 17 37 78 103 Carriers johnny jenny julie joey Johnny has Route 5, Jenny has 14 and 37, Julie has 17, joey has 78, and wants to take over 103 as well. It's very easy to go in and edit the tables by hand, but How do I set up the form to point Joey to 103 without getting the Duplicate Key error? And, what happens when Jerry (not in the database yet) comes along wanting Route 105 (not in the database yet)? Again, I can edit the tables by hand, but this method is far from idiot-proof, and other users will not understand it. How do I set up the form to add a record to one table but not the other, when the form accesses, refers to, updates, both tables? That's the question. " wrote: On Jul 11, 10:50 am, News Boy wrote: I'd like to create a database to keep track of which routes go with which carriers, and which substitute carriers are associated with them. A carrier can have more than one route, but a route can have only one carrier, and they change occasionally to frequently. After a lot of trial and error, I have designed a simple database to test Access' behavior. Forgetting the substitutes for the moment, I have constructed 2 tables: tblRoutes ID (AutoNum), primary key Route (Integer) Carrier (Long Integer) tblCarriers ID (AutoNum) LastName (String) Account# (Long Integer), primary key Carriers.Account# is related one-to-many to Routes.Carrier with referential integrity and cascade update and cascade delete enabled, and tblRoutes will naturally have more records than tblCarriers. I'm trying to add records to the tables via a form with a query as the data source: SELECT Routes.ID AS Routes_ID, Routes.Route, Routes.Carrier, Carriers.ID AS Carriers_ID, Carriers.LastName, Carriers.[Account Number] FROM Routes RIGHT JOIN Carriers ON Routes.Carrier=Carriers.[Account Number] ORDER BY Routes.Route; This was built in the Access Wizard, as I am not familiar with SQL, yet, and I've never used Access before and the help files are not very instructional for design view. If I try to enter a record for a route associated with a carrier already in the table, I can type in the Routes.Carrier field and Access will automatically fill in Carrier.LastName and Carrier.Account# boxes. But, if I try to enter a new route with a carrier not yet in the table via the Routes.Carrier field, I get the following error: The Microsoft Jet database engine cannot find a record in the table 'Carriers' with the key matching field(s) 'carrier'. . . . meaning I need to enter the Carrier.Account# first. But, if I try to enter a record via the Carrier.Account# field, and that carrier already exists, I get an error message about not allowing duplicates in primary keys. So, I need to have a single text box on the data entry form that can sense which field to access: Routes.Carrier when the carrier already exists, and Carrier.Account# when the carrier does not yet exist. Is this accomplished via the query, or some switch in the form properties, or how? thank you for your help. The table structure here is somewhat confusing. I expected to see a schema in the form of: tblRoutes RouteId text(16) primary key, RouteDescription text(32) tblCarriers CarrierID text(16) primary key, LastName text(24) tblCarrierRouteLink RouteId text(16), CarrierID text(16), DateEffective datetime The form would have a record source of tblCarrierRouteLink. The tblCarrierRouteLink.RouteId would be restricted to the values in tblRoutes.RouteID and the tblCarrierRouteLink.CarrierID would be restricted to the values in tblCarriers.CarrierID. These restrictions would be enforced by a form that had the RouteID and CarrierID with a Row Source of tblRoute and tblCarriers respectively. The user's job would be to select the RouteId and the CarrierID combinations from drop-down list boxes and supply the DateEffective in a text edit field. With this data structure, you can accommodate one carrier handling one or more routes, idle carriers, unassigned routes and carriers who assume route duties temporarily. |
Thread Tools | |
Display Modes | |
|
|