Order Entry type form to add to three tables
in [Tables]
|
From: CuriousMark on 10 Feb 2010 11:25 Thanks Jeff. Agreed that the form should be based only on the tblOrders table, with a combo box for Customer. But I couldn't figure out how to add new customer if one didn't exist. "Jeff Boyce" wrote: > These newsgroups are "staffed" by (mostly) volunteers. The Code of Conduct > precludes soliciting paid work. > > Before you sign any contracts, you might want to consider whether you want > to hire someone who doesn't feel it necessary to follow the rules. > > Why do you have a main form with Customer and Order joined? If the main > form is your Order form, you can use a combobox to list possible customers > and select which one it is, to add to your Order record (customerID). > > If you'd rather have Customer as your main form, your Order is your subform > and the OrderDetail is the subsubform. > > Using subforms and the Parent/Child joins between main and sub (and between > sub and subsub) will allow Access to properly record the "parent" to which a > particular Order Detail or Order belongs > > Good luck! > > Regards > > Jeff Boyce > Microsoft Access MVP > > -- > Disclaimer: This author may have received products and services mentioned > in this post. Mention and/or description of a product or service herein > does not constitute endorsement thereof. > > Any code or pseudocode included in this post is offered "as is", with no > guarantee as to suitability. > > You can thank the FTC of the USA for making this disclaimer > possible/necessary. > > "CuriousMark" <CuriousMark(a)discussions.microsoft.com> wrote in message > news:FC28667D-80CA-414C-9464-2C1EA0207E3A(a)microsoft.com... > >I have been working on this database for several years, learning as I go. > > However I have been stuck on the problem of the best way to design a form > > for > > data entry into three tables. I now give up and put the whole thing on the > > Forum in the hope that someone can help me with the design. For > > simplicity, I > > will illustrate using the "Order Entry" style database, and limit the > > fields > > to only those necessary. > > > > There are four tables with the following fields: > > > > tblCustomers - CustomerID (pk), CustomerName > > tblOrders - OrderID (pk), OrderDate, CustomerID (fk) > > tblOrderProd - OrderProdID (pk), OrderID (fk), ProductID (fk) > > tblProducts - ProductID (pk), ProductName > > > > One customer is associated with one or more orders, and one order can have > > one or more products. The user should be able to create a new order by > > specifying the date and the customer, and then one or more products. > > > > I have a main form with subform to do this. The main form is bound to a > > query that joins the tblOrders and tblCustomers tables, and the subform is > > bound to a query that joins the tblOrderProd and tblProducts tables. The > > subform is linked to the main form through the OrderID field. > > > > Main form query: > > SELECT tblOrders.OrderDate, tblCustomers.CustomerName > > FROM tblCustomers INNER JOIN tblOrders > > ON tblCustomers.CustomerID = tblOrders.CustomerID > > > > Subform query: > > SELECT tblOrderProd.ProductID, tblProducts.ProductName, > > tblOrderProd.OrderID > > FROM tblProducts INNER JOIN tblOrderProd > > ON tblProducts.ProductID = tblOrderProd.ProductID > > > > This setup works great for adding records, and when I enter a ProductID in > > the subform it will show the ProductName but the problem is that this > > design > > means a new customer is added for each new order: I can't figure out how > > to > > search based on the entry for an existing customer, use it if it exists, > > or > > add it if it is new. > > > > My hunch is that I should unbind both forms, using Append queries to enter > > the data in the related three fields. If so, then how do I find out the > > new > > OrderID to use to append to tblOrderProd? And how do I find out the > > CustomerID to use to append to the tblOrders if it is a new Customer? Then > > how do I progress through each ProductID entered in the subform when > > appending to the tblOrderProd table? > > > > Thanks very much. > > > > > . >
From: Jeff Boyce on 10 Feb 2010 12:00 Check Access HELP for NotInList and LimitToList. There are examples of the code you'd use to pop open a "new customer" form to add a new one before returning back to the Order form. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "CuriousMark" <CuriousMark(a)discussions.microsoft.com> wrote in message news:923BC169-E96E-4EF8-BE00-5CB8FB959A2A(a)microsoft.com... > Thanks Jeff. > Agreed that the form should be based only on the tblOrders table, with a > combo box for Customer. But I couldn't figure out how to add new customer > if > one didn't exist. > > "Jeff Boyce" wrote: > >> These newsgroups are "staffed" by (mostly) volunteers. The Code of >> Conduct >> precludes soliciting paid work. >> >> Before you sign any contracts, you might want to consider whether you >> want >> to hire someone who doesn't feel it necessary to follow the rules. >> >> Why do you have a main form with Customer and Order joined? If the main >> form is your Order form, you can use a combobox to list possible >> customers >> and select which one it is, to add to your Order record (customerID). >> >> If you'd rather have Customer as your main form, your Order is your >> subform >> and the OrderDetail is the subsubform. >> >> Using subforms and the Parent/Child joins between main and sub (and >> between >> sub and subsub) will allow Access to properly record the "parent" to >> which a >> particular Order Detail or Order belongs >> >> Good luck! >> >> Regards >> >> Jeff Boyce >> Microsoft Access MVP >> >> -- >> Disclaimer: This author may have received products and services mentioned >> in this post. Mention and/or description of a product or service herein >> does not constitute endorsement thereof. >> >> Any code or pseudocode included in this post is offered "as is", with no >> guarantee as to suitability. >> >> You can thank the FTC of the USA for making this disclaimer >> possible/necessary. >> >> "CuriousMark" <CuriousMark(a)discussions.microsoft.com> wrote in message >> news:FC28667D-80CA-414C-9464-2C1EA0207E3A(a)microsoft.com... >> >I have been working on this database for several years, learning as I >> >go. >> > However I have been stuck on the problem of the best way to design a >> > form >> > for >> > data entry into three tables. I now give up and put the whole thing on >> > the >> > Forum in the hope that someone can help me with the design. For >> > simplicity, I >> > will illustrate using the "Order Entry" style database, and limit the >> > fields >> > to only those necessary. >> > >> > There are four tables with the following fields: >> > >> > tblCustomers - CustomerID (pk), CustomerName >> > tblOrders - OrderID (pk), OrderDate, CustomerID (fk) >> > tblOrderProd - OrderProdID (pk), OrderID (fk), ProductID (fk) >> > tblProducts - ProductID (pk), ProductName >> > >> > One customer is associated with one or more orders, and one order can >> > have >> > one or more products. The user should be able to create a new order by >> > specifying the date and the customer, and then one or more products. >> > >> > I have a main form with subform to do this. The main form is bound to a >> > query that joins the tblOrders and tblCustomers tables, and the subform >> > is >> > bound to a query that joins the tblOrderProd and tblProducts tables. >> > The >> > subform is linked to the main form through the OrderID field. >> > >> > Main form query: >> > SELECT tblOrders.OrderDate, tblCustomers.CustomerName >> > FROM tblCustomers INNER JOIN tblOrders >> > ON tblCustomers.CustomerID = tblOrders.CustomerID >> > >> > Subform query: >> > SELECT tblOrderProd.ProductID, tblProducts.ProductName, >> > tblOrderProd.OrderID >> > FROM tblProducts INNER JOIN tblOrderProd >> > ON tblProducts.ProductID = tblOrderProd.ProductID >> > >> > This setup works great for adding records, and when I enter a ProductID >> > in >> > the subform it will show the ProductName but the problem is that this >> > design >> > means a new customer is added for each new order: I can't figure out >> > how >> > to >> > search based on the entry for an existing customer, use it if it >> > exists, >> > or >> > add it if it is new. >> > >> > My hunch is that I should unbind both forms, using Append queries to >> > enter >> > the data in the related three fields. If so, then how do I find out the >> > new >> > OrderID to use to append to tblOrderProd? And how do I find out the >> > CustomerID to use to append to the tblOrders if it is a new Customer? >> > Then >> > how do I progress through each ProductID entered in the subform when >> > appending to the tblOrderProd table? >> > >> > Thanks very much. >> > >> >> >> . >>
From: CuriousMark on 11 Feb 2010 20:47 Thanks Jeff, I'm familiat with that approach but I'm trying to do something a little different.... I want to be able to enter the "Customer" information on the same form, without having to open a new form, so I have several fields for the customer on the "Order" form. I also want the program to search for matches on several of the "Customer" info fields, not just one combo box. The best way I can think to do it is to use an unbound form and run a query after data is entered into one of the fields used for searching. Then using an Append Query to add the data to the appropriate table. "Jeff Boyce" wrote: > Check Access HELP for NotInList and LimitToList. > > There are examples of the code you'd use to pop open a "new customer" form > to add a new one before returning back to the Order form. > > Regards > > Jeff Boyce > Microsoft Access MVP > > -- > Disclaimer: This author may have received products and services mentioned > in this post. Mention and/or description of a product or service herein > does not constitute endorsement thereof. > > Any code or pseudocode included in this post is offered "as is", with no > guarantee as to suitability. > > You can thank the FTC of the USA for making this disclaimer > possible/necessary. > > "CuriousMark" <CuriousMark(a)discussions.microsoft.com> wrote in message > news:923BC169-E96E-4EF8-BE00-5CB8FB959A2A(a)microsoft.com... > > Thanks Jeff. > > Agreed that the form should be based only on the tblOrders table, with a > > combo box for Customer. But I couldn't figure out how to add new customer > > if > > one didn't exist. > > > > "Jeff Boyce" wrote: > > > >> These newsgroups are "staffed" by (mostly) volunteers. The Code of > >> Conduct > >> precludes soliciting paid work. > >> > >> Before you sign any contracts, you might want to consider whether you > >> want > >> to hire someone who doesn't feel it necessary to follow the rules. > >> > >> Why do you have a main form with Customer and Order joined? If the main > >> form is your Order form, you can use a combobox to list possible > >> customers > >> and select which one it is, to add to your Order record (customerID). > >> > >> If you'd rather have Customer as your main form, your Order is your > >> subform > >> and the OrderDetail is the subsubform. > >> > >> Using subforms and the Parent/Child joins between main and sub (and > >> between > >> sub and subsub) will allow Access to properly record the "parent" to > >> which a > >> particular Order Detail or Order belongs > >> > >> Good luck! > >> > >> Regards > >> > >> Jeff Boyce > >> Microsoft Access MVP > >> > >> -- > >> Disclaimer: This author may have received products and services mentioned > >> in this post. Mention and/or description of a product or service herein > >> does not constitute endorsement thereof. > >> > >> Any code or pseudocode included in this post is offered "as is", with no > >> guarantee as to suitability. > >> > >> You can thank the FTC of the USA for making this disclaimer > >> possible/necessary. > >> > >> "CuriousMark" <CuriousMark(a)discussions.microsoft.com> wrote in message > >> news:FC28667D-80CA-414C-9464-2C1EA0207E3A(a)microsoft.com... > >> >I have been working on this database for several years, learning as I > >> >go. > >> > However I have been stuck on the problem of the best way to design a > >> > form > >> > for > >> > data entry into three tables. I now give up and put the whole thing on > >> > the > >> > Forum in the hope that someone can help me with the design. For > >> > simplicity, I > >> > will illustrate using the "Order Entry" style database, and limit the > >> > fields > >> > to only those necessary. > >> > > >> > There are four tables with the following fields: > >> > > >> > tblCustomers - CustomerID (pk), CustomerName > >> > tblOrders - OrderID (pk), OrderDate, CustomerID (fk) > >> > tblOrderProd - OrderProdID (pk), OrderID (fk), ProductID (fk) > >> > tblProducts - ProductID (pk), ProductName > >> > > >> > One customer is associated with one or more orders, and one order can > >> > have > >> > one or more products. The user should be able to create a new order by > >> > specifying the date and the customer, and then one or more products. > >> > > >> > I have a main form with subform to do this. The main form is bound to a > >> > query that joins the tblOrders and tblCustomers tables, and the subform > >> > is > >> > bound to a query that joins the tblOrderProd and tblProducts tables. > >> > The > >> > subform is linked to the main form through the OrderID field. > >> > > >> > Main form query: > >> > SELECT tblOrders.OrderDate, tblCustomers.CustomerName > >> > FROM tblCustomers INNER JOIN tblOrders > >> > ON tblCustomers.CustomerID = tblOrders.CustomerID > >> > > >> > Subform query: > >> > SELECT tblOrderProd.ProductID, tblProducts.ProductName, > >> > tblOrderProd.OrderID > >> > FROM tblProducts INNER JOIN tblOrderProd > >> > ON tblProducts.ProductID = tblOrderProd.ProductID > >> > > >> > This setup works great for adding records, and when I enter a ProductID > >> > in > >> > the subform it will show the ProductName but the problem is that this > >> > design > >> > means a new customer is added for each new order: I can't figure out > >> > how > >> > to > >> > search based on the entry for an existing customer, use it if it > >> > exists, > >> > or > >> > add it if it is new. > >> > > >> > My hunch is that I should unbind both forms, using Append queries to > >> > enter > >> > the data in the related three fields. If so, then how do I find out the > >> > new > >> > OrderID to use to append to tblOrderProd? And how do I find out the > >> > CustomerID to use to append to the tblOrders if it is a new Customer? > >> > Then > >> > how do I progress through each ProductID entered in the subform when > >> > appending to the tblOrderProd table? > >> > > >> > Thanks very much. > >> > > >> > >> > >> . > >> > > > . >
From: Jeff Boyce on 12 Feb 2010 13:42 Hey, whatever works! Best of luck Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "CuriousMark" <CuriousMark(a)discussions.microsoft.com> wrote in message news:2132478F-D0B8-4C0E-96E6-F35AB7A43A37(a)microsoft.com... > Thanks Jeff, I'm familiat with that approach but I'm trying to do > something a > little different.... > I want to be able to enter the "Customer" information on the same form, > without having to open a new form, so I have several fields for the > customer > on the "Order" form. I also want the program to search for matches on > several > of the "Customer" info fields, not just one combo box. The best way I can > think to do it is to use an unbound form and run a query after data is > entered into one of the fields used for searching. Then using an Append > Query > to add the data to the appropriate table. > > "Jeff Boyce" wrote: > >> Check Access HELP for NotInList and LimitToList. >> >> There are examples of the code you'd use to pop open a "new customer" >> form >> to add a new one before returning back to the Order form. >> >> Regards >> >> Jeff Boyce >> Microsoft Access MVP >> >> -- >> Disclaimer: This author may have received products and services mentioned >> in this post. Mention and/or description of a product or service herein >> does not constitute endorsement thereof. >> >> Any code or pseudocode included in this post is offered "as is", with no >> guarantee as to suitability. >> >> You can thank the FTC of the USA for making this disclaimer >> possible/necessary. >> >> "CuriousMark" <CuriousMark(a)discussions.microsoft.com> wrote in message >> news:923BC169-E96E-4EF8-BE00-5CB8FB959A2A(a)microsoft.com... >> > Thanks Jeff. >> > Agreed that the form should be based only on the tblOrders table, with >> > a >> > combo box for Customer. But I couldn't figure out how to add new >> > customer >> > if >> > one didn't exist. >> > >> > "Jeff Boyce" wrote: >> > >> >> These newsgroups are "staffed" by (mostly) volunteers. The Code of >> >> Conduct >> >> precludes soliciting paid work. >> >> >> >> Before you sign any contracts, you might want to consider whether you >> >> want >> >> to hire someone who doesn't feel it necessary to follow the rules. >> >> >> >> Why do you have a main form with Customer and Order joined? If the >> >> main >> >> form is your Order form, you can use a combobox to list possible >> >> customers >> >> and select which one it is, to add to your Order record (customerID). >> >> >> >> If you'd rather have Customer as your main form, your Order is your >> >> subform >> >> and the OrderDetail is the subsubform. >> >> >> >> Using subforms and the Parent/Child joins between main and sub (and >> >> between >> >> sub and subsub) will allow Access to properly record the "parent" to >> >> which a >> >> particular Order Detail or Order belongs >> >> >> >> Good luck! >> >> >> >> Regards >> >> >> >> Jeff Boyce >> >> Microsoft Access MVP >> >> >> >> -- >> >> Disclaimer: This author may have received products and services >> >> mentioned >> >> in this post. Mention and/or description of a product or service >> >> herein >> >> does not constitute endorsement thereof. >> >> >> >> Any code or pseudocode included in this post is offered "as is", with >> >> no >> >> guarantee as to suitability. >> >> >> >> You can thank the FTC of the USA for making this disclaimer >> >> possible/necessary. >> >> >> >> "CuriousMark" <CuriousMark(a)discussions.microsoft.com> wrote in message >> >> news:FC28667D-80CA-414C-9464-2C1EA0207E3A(a)microsoft.com... >> >> >I have been working on this database for several years, learning as I >> >> >go. >> >> > However I have been stuck on the problem of the best way to design a >> >> > form >> >> > for >> >> > data entry into three tables. I now give up and put the whole thing >> >> > on >> >> > the >> >> > Forum in the hope that someone can help me with the design. For >> >> > simplicity, I >> >> > will illustrate using the "Order Entry" style database, and limit >> >> > the >> >> > fields >> >> > to only those necessary. >> >> > >> >> > There are four tables with the following fields: >> >> > >> >> > tblCustomers - CustomerID (pk), CustomerName >> >> > tblOrders - OrderID (pk), OrderDate, CustomerID (fk) >> >> > tblOrderProd - OrderProdID (pk), OrderID (fk), ProductID (fk) >> >> > tblProducts - ProductID (pk), ProductName >> >> > >> >> > One customer is associated with one or more orders, and one order >> >> > can >> >> > have >> >> > one or more products. The user should be able to create a new order >> >> > by >> >> > specifying the date and the customer, and then one or more products. >> >> > >> >> > I have a main form with subform to do this. The main form is bound >> >> > to a >> >> > query that joins the tblOrders and tblCustomers tables, and the >> >> > subform >> >> > is >> >> > bound to a query that joins the tblOrderProd and tblProducts tables. >> >> > The >> >> > subform is linked to the main form through the OrderID field. >> >> > >> >> > Main form query: >> >> > SELECT tblOrders.OrderDate, tblCustomers.CustomerName >> >> > FROM tblCustomers INNER JOIN tblOrders >> >> > ON tblCustomers.CustomerID = tblOrders.CustomerID >> >> > >> >> > Subform query: >> >> > SELECT tblOrderProd.ProductID, tblProducts.ProductName, >> >> > tblOrderProd.OrderID >> >> > FROM tblProducts INNER JOIN tblOrderProd >> >> > ON tblProducts.ProductID = tblOrderProd.ProductID >> >> > >> >> > This setup works great for adding records, and when I enter a >> >> > ProductID >> >> > in >> >> > the subform it will show the ProductName but the problem is that >> >> > this >> >> > design >> >> > means a new customer is added for each new order: I can't figure out >> >> > how >> >> > to >> >> > search based on the entry for an existing customer, use it if it >> >> > exists, >> >> > or >> >> > add it if it is new. >> >> > >> >> > My hunch is that I should unbind both forms, using Append queries to >> >> > enter >> >> > the data in the related three fields. If so, then how do I find out >> >> > the >> >> > new >> >> > OrderID to use to append to tblOrderProd? And how do I find out the >> >> > CustomerID to use to append to the tblOrders if it is a new >> >> > Customer? >> >> > Then >> >> > how do I progress through each ProductID entered in the subform when >> >> > appending to the tblOrderProd table? >> >> > >> >> > Thanks very much. >> >> > >> >> >> >> >> >> . >> >> >> >> >> . >>
First
|
Prev
|
Pages: 1 2 3 Prev: Problem with Group By & Max Rcd Next: Are rows stored in fixed or variable length records. |