Form design to add data to 3 tables in "Order Entry" style db

Sorry if this is a duplicate. I thought I posted it yesterday but couldn't 
find it today...

I need help figuring out the best way to design a form for data entry in the 
"Order Entry" style, adding data to three related tables. For clarity I have 
eliminated several fields. Here are the three tables, plus the fourth that is 
a reference table:

tblCustomers: CustID (pk), CustName
tblOrders:    OrderID (pk), OrderDate, CustID (fk)
tblOrderProd: OrderProdID (pk), OrderID (fk), ProdID (fk)
tblProducts:  ProdID (pk), ProdName

Each Customer is associated with one or more Orders, and each order has a 
date, a Customer and one or more Products. The current design uses a main 
form for the Date and Customer, and a subform for the Products. The main form 
is bound to this query:

SELECT tblOrders.OrderDate, tblCustmers.CustName
FROM   tblCustomers INNER JOIN tblOrders
ON     tblCustomers.CustID = tblOrders.CustID

The subform is bound to this query:

SELECT tblOrderProd.ProdID, tblProducts.ProdName, tblOrderProd.OrderID
FROM   tblProducts INNER JOIN tblOrderProd
ON     tblProducts.ProdID = tblOrderProd.ProdID

This works for entering orders, and the subform works to add the Product ID 
and display the Product Name. But it does not allow me to use and existing 
customer...this design means that each order must add a customer.

I want to program the form so that as I enter the Customer information it 
will search to see if the Customer exists, ask me if I want to use that 
customer or add a new one. When posting bits of this problem here before the 
advice I get is to use an unbound form, but I can't quite figure out the 
global picture. Should both the main and subform be unbound, and then use 
Append queries to add data to the three tables? If so, if it is a new 
Customer, how do I get the CustID (an Autonumber field) to use when appending 
to the tblOrder table. And how do I get the OrderID (also an Autonumber 
field) to add to the tblOrderProd table? Then, how do I progress through each 
entry from the subform when appending to the tblOrderProd table? Do I store 
the ProdIDs in a Recordset or in a temporary table? Or do I keep the forms 
bound and use another technique?

Thanks very much.
0
Utf
2/9/2010 1:43:02 PM
access.formscoding 7493 articles. 0 followers. Follow

1 Replies
931 Views

Similar Articles

[PageSpeed] 44

This is duplicate....found the original post.

"CuriousMark" wrote:

> Sorry if this is a duplicate. I thought I posted it yesterday but couldn't 
> find it today...
> 
> I need help figuring out the best way to design a form for data entry in the 
> "Order Entry" style, adding data to three related tables. For clarity I have 
> eliminated several fields. Here are the three tables, plus the fourth that is 
> a reference table:
> 
> tblCustomers: CustID (pk), CustName
> tblOrders:    OrderID (pk), OrderDate, CustID (fk)
> tblOrderProd: OrderProdID (pk), OrderID (fk), ProdID (fk)
> tblProducts:  ProdID (pk), ProdName
> 
> Each Customer is associated with one or more Orders, and each order has a 
> date, a Customer and one or more Products. The current design uses a main 
> form for the Date and Customer, and a subform for the Products. The main form 
> is bound to this query:
> 
> SELECT tblOrders.OrderDate, tblCustmers.CustName
> FROM   tblCustomers INNER JOIN tblOrders
> ON     tblCustomers.CustID = tblOrders.CustID
> 
> The subform is bound to this query:
> 
> SELECT tblOrderProd.ProdID, tblProducts.ProdName, tblOrderProd.OrderID
> FROM   tblProducts INNER JOIN tblOrderProd
> ON     tblProducts.ProdID = tblOrderProd.ProdID
> 
> This works for entering orders, and the subform works to add the Product ID 
> and display the Product Name. But it does not allow me to use and existing 
> customer...this design means that each order must add a customer.
> 
> I want to program the form so that as I enter the Customer information it 
> will search to see if the Customer exists, ask me if I want to use that 
> customer or add a new one. When posting bits of this problem here before the 
> advice I get is to use an unbound form, but I can't quite figure out the 
> global picture. Should both the main and subform be unbound, and then use 
> Append queries to add data to the three tables? If so, if it is a new 
> Customer, how do I get the CustID (an Autonumber field) to use when appending 
> to the tblOrder table. And how do I get the OrderID (also an Autonumber 
> field) to add to the tblOrderProd table? Then, how do I progress through each 
> entry from the subform when appending to the tblOrderProd table? Do I store 
> the ProdIDs in a Recordset or in a temporary table? Or do I keep the forms 
> bound and use another technique?
> 
> Thanks very much.
0
Utf
2/10/2010 4:25:01 PM
Reply:

Similar Artilces:

Purchases Order Entry Status Edit
Working in Microsoft Dynamics GP 9.0 Tools>>Utilities>>Purchasing>>Reconcile Process I get the messages: You can’t complete this process while transactions are being edited. I’m tried to reconcile because any time access Purchases Order Entry I get the message: Purchase Order Processing setup information is missing. Also access Batch Inquiry Inquiry>>System>>Batch show me the Purch. Order Entry Status Edit, but I don’t know how delete or change the status. Any ideas? Hi There, It sounds like you have a batch with an error in it. To fix this you need to g...

MO order entry
We are having issues when entering MO orders. After all the information is filled out we click the "Schedule MO" then the hour glass comes up and it does not progress any further. We then have to end task to exit Great Plains. Any help would be MUCH appreciated. Thanks. Hey Ladlenits. I am not sure if you are new to GP / Mfg, or not - please excuse this if it is totally ridiculous but ... When we were implementing and testing Mfg we ran into this problem for several reasons ... we were testing w/ very high quantities of finished goods (hey - it was fun - we used to do it man...

GP 10.0 Security - Purchase Order Entry Window
I'm frustrated and looking for help. The problem is that users are being given the error message "You don't have security privileges to open this window. Contact your system administrator for assistance." when opening the Purchase Order Entry window. When users click OK on this message, the Purchase Order Entry window opens. One of the users does not see this message (it appears on other machines while using that user's login) on his office system. So, it appears as though he can't open the window. As a temporary fix I've assigned him to the POWERU...

Purchase Order Entry
When I Enter Purchase Order Entry it accepts ANY ITEM code. It is not checking ITEM Master. You are correct. That is by design. "Raja" wrote: > When I Enter Purchase Order Entry it accepts ANY ITEM > code. It is not checking ITEM Master. > > > This is because it reads the Item as the "Vendor Item" ie the name as the vendor may have it - its name at the vendor's warehouse -, not the company. >-----Original Message----- >You are correct. That is by design. > >"Raja" wrote: > >> When I Enter Purchase Order Entry i...

Manufacturing Order Entry error
I am getting the error message: 'Your manufacturing order routing has errors, either a work center is undefined, or the earliest work cetner effective date is invalid. I looked in TK, and found nothing for either of these error messages. I havelooked through my routing and looks ok, can anyone suggest where specifically to look in the routing to correct this? I dont understand why error messages like this are not in techknowledge... -- Doug Doug- On the Work Center Setup window you probably need to enter a value in the "Display Date" field. See Work Center Setup in th...

error on sales order entry
when some of the users including sa and dynsa go to change quantities on sales invoices, the system tells them that 'you do not have access privileges to edit this field. Contact your system administrator'. We do not use field security and we dont have any customizations. Any suggestions where to look here? Jack, That isn't a GP error out of the box so I'm thinking that there must be at least one customization- possibly like Advanced Distribution from Trinity (not that I'm saying that you do) that you aren't thinking is like a "security type application&qu...

Change Order Entry failure from Econnect #2
I am using eConnect8.0 to export data into Great Plains. I have added project budget entry with related cost categories.I am having problem with Change order entry into Great Plains. I am using taPAChangeOrderEntryHdr and taPAChangeOrderEntryBudget_ItemsTaPAChangeOrderEntryBudget schemas for change order entry. The code is something like this: --------------------------------------------------------- PAChangeOrderType changeOrder = new PAChangeOrderType(); taPAChangeOrderEntryHdr changeOrderEntryHdr = new taPAChangeOrderEntryHdr(); // Fill changeOrderEntryHdr object or schema taPAChangeO...

Determine Line Sequence No on Sales Order Entry form
How do I determine using VBA what is the line sequence number of the line being worked on or selected. Thank you for your help in advance. ...

Business Portal Order Entry Discount Price Option
This is crucial for Order entry clients that we work with using business portal Microsoft Dynamics Incident Number: 8638390 Incident Type: Technical Support Subject: price override Occurs At: All Workstations Current Status: Open - Solution Delivered Not Confirmed Originated: 8/14/2006 4:58:00 PM PDT Originated by: Rose Business Solutions Inc. Authorized number: 8587949403 Regarding Customer: Emerald Publications Team: NA GP Dist_Mfg_Proj Support Region: North America Agent: Kelly Dettmann Current Contact: Jake Product Line: Microsoft Dynamics GP Topic: Business Portal Order Management Appl...

Tax not calculating in Sales Order Entry
Hello - We are having difficulty in GP 8.0 with taxes not calculating when creating a sales order. The customer is set up with a tax schedule ID, the item is taxable. Any ideas? Any help would be greatly appreciated! Thanks! Lisa D. What is the shipping method you are using: pickup or delivery? Lisa D. wrote: > Hello - > > We are having difficulty in GP 8.0 with taxes not calculating when creating > a sales order. The customer is set up with a tax schedule ID, the item is > taxable. Any ideas? > > Any help would be greatly appreciated! > > Thanks...

Purchase order entry #3
The "Confirm to" field which prints on purchase orders should be able to be setup as a default instead of having to enter the information each time a purchase order is entered. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Business...

Order Entry and Leadtimes
We are having a lot of problems lately with our Customer Service Department entering orders at the appropriate lead-time. We have a lot of products with various lead times so the problem is understandable to a certain extent, but as you can imagine, this problem puts a strain on our procurement group and frustrates customers when we fail to meet commitments. Can something be done in Dynamics to prevent people from entering sales orders in the system inside the lead-time that is set-up for that item? What about a broader lead-time restriction? -- Eric Powers I think the only way to ...

Slow Performance
When opening the PO Entry or Receiving Entry window, and entering or selecting a transaction, the screen hangs for about 30 seconds before allowing entry. We are using Project Accounting in v9.0. This has started happening recently for no apparent reason. It is happening for all users on all workstations including sa. I tried: - Rebooting SQL Server - pointing Dynamics.set locally and deleting all modified reports, form and VBA - run Checklinks on Purchasing Transactions Any ideas? Regards Konrad Konrad, One item that can slow down particular screens is the autocomplete feature. ...

Unhandled Script Exception in Purchase Order Entry
If I click the arrow at the bottom of the Purchase Order Entry screen which should take me to the first open purchase order, I get the hourglass for a couple of minutes followed by Unhandled Script Exception, Value out of Range error. I have run check links and purchase order reconcile utilities. Also get the same error if I try to back up past the first valid open PO. ...

Order Entry #2
We have set up a password to allow price changes when entering orders because we don't want to allow everyone access to this field. I have three customers that request the password even when the salesperson isn't trying to change the price. I have checked the customer cards of these companies and I can't figure out why these three produce this message. Is there something in particular I need to be looking at? Thanks Angie Angie, What's the exact message you are getting? Are you sure you are not being prompted to override price below cost? -- MG.- Mariano Gomez,...

Purchase Order Entry #4
We would like to create PO's for all items. These are all noninventory items. How cab we set it up so the GL code is entered at the time of the PO creating. For example, when entering AP invoicing you can enter GL accounts as part of the transaction entry. Is there a way to do this for PO's (I do not see a distributions link on the PO data entry screen.) Thanks nfp, After entering the item number on the PO, click the little blue arrow next to the Item Number field name. This opens another window where you can enter more info about the item, including the purchases account. F...

PA Purchase Order Entry Currency Conversion ..
hi, When i am trying to change my currency rate in the 'PA Purchase Order entry' screen it shows me the following error message. Unhandled script exception: Type mismatch in return from script 'Set_Calling_Windows_X_Information_2 of form MC_Exchange_Rate_Entry'. EXCEPTION_CLASS_SCRIPT_BAD_PARAM SCRIPT_CMD_RETURN Can you let me know the cause and Resolution for this problem. Regards, Karthick.J ...

Purchase Order Entry #2
Working with Microsoft Dynamics GP 9.0 When tried to access Purchase Order Entry (Transactions>>Purchasing>> Purchases Order) I get this message: Purchase Order Processing setup information is missing or damaged. Please help me jvrodriguez@infomedika.com Typically you will just need to go to Tools - Setup - Purchasing - Purchase Order Processing. That should clear the error. If you are running Project Accounting you will need to do the following: 1. Grant users permissions to access the Alternate Dynamics GP Windows in Project Accounting. To do this, follow these step...

SalesOrder Entry
If I were looking for a Great Plains client using the Sales Order Entry module for their day to day orders, had 11,000 or more customers and in excess of 15,000 products does anyone have a suggestion of who to contact or where to look. A regional MS rep has been involved but without much luck to date. Thanks JDR, What are you trying to accomplish? We have clients roughly have the specifications that you are asking... need help email me gbuenafe@gpcsystems.com Gerald "JDR" wrote: > If I were looking for a Great Plains client using the Sales Order Entry > module f...

Purchase Order Entry Error
We are using GP 7.0 SQL 2000. We have a problem a user Great Plains froze on him and he got locked out. He had to be deleted from the user activity log. When he went to edit the PO he got an error message "this transaction is being edited by another user". There is no one else editing that PO. When I try to edit the PO I get the same error message. Does anyone know how to fix this problem? Thank you, Kevin Kbrown, Go to SQL server or query analyzer and run a delete statement on the SY00800 and SY00801 DELETE FROM SY00800 WHERE (USERID = 'xxx') DELE...

order entry business portal
Need a fix for this in the next release Microsoft Dynamics Incident Number: 8638390 Incident Type: Technical Support Subject: price override Occurs At: All Workstations Current Status: Open - Solution Delivered Not Confirmed Originated: 8/14/2006 4:58:00 PM PDT Originated by: Rose Business Solutions Inc. Authorized number: 8587949403 Regarding Customer: Emerald Publications Team: NA GP Dist_Mfg_Proj Support Region: North America Agent: Kelly Dettmann Current Contact: Jake Product Line: Microsoft Dynamics GP Topic: Business Portal Order Management Application Version: 9.0 Application Languag...

Order Entry type form to add to three tables
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 - Ord...

Deposit during Purchase Order entry
Same functionality as Customer Deposit in Sales but this is for Purchase Order because some vendors require downpayment. This would be during Purchase Order creation. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/...

SOP Order entry Site ID
We have noticed that in entering line items, when a new item is added to a Sales Order and it is not assigned to the "Default Site ID", we get this pop-up window: "This site is not assigned to the selected item. Do you want to assign this site?" Then a button for: ADD or CANCEL If we don't want to ADD it, we have to cancel, delete the row, change the site ID on that row to the one where the item is located, and then add the item. My suggestion is that when that dialog box opens up, we are given a THIRD option to "CHANGE THE SITE ID" so that it allow...

Business Portal and Sales Order Entry
We are going to be installing Business Portal soon and I was wondering if anyone can tell me if you can see the information written on the Order Entry Document Note? ...