Full Version: Data Entry Error
UtterAccess Forums > Microsoft® Access > Access Forms
Grassy7
I've finally got the subform to do what I want...pull up the unitprice for each product when I select the product name from a combo box. Now after I select the product, the price pops up, then I enter the quantity and discount, just like I need. However, I can't click onto a new record. There error "You cannot change or add a record because a related record is required in the table 'Products'." How can this be when the products in the combo box come straight from the products table?
RuralGuy
Try clearing the ControlSource of the ComboBox you are using (unbind it).
Grassy7
Well, now when I select the product, the same product fills in for every record. Is there a code error?
RuralGuy
What do you have for the LinkChild/MasterFields setting of the SubFormControl?
Grassy7
If you're talking about the table where the form gets info from (Order Details), then there is nothing listed in the LinkChild/MasterFields box.
RuralGuy
How did you get this to h
Grassy7
Visual Basic Code:
Private Sub ProductName_AfterUpdate()
Me!UnitPrice = Me.ProductName.Column(2)
End Sub
Whatever it does, it works (to a certain extent I guess). I'm not to schooled on code writing. I dug around the internet and got help on this website. After I played around with that code for a bit, it finally worked, then I started getting the error when I tried to enter a new record.
RuralGuy
I can see where that would fill in the UnitPrice control on the main form but how does that control the SubForm?
Grassy7
Alright, I just discovered a problem in my main order form. When I opened that up, it showed both products ordered for each jobsite. (I only have two orders so far, each order has only one item ordered). So how can I fix this? Do I need stay away from the subform, which is what I've been working on?
RuralGuy
Normally you would use a Form/SubForm combination to display a 1:M relationship of at least two tables. The form would display the 1 side and the SubForm would display the M side. Is that what you have here? What real world business function are you trying to achieve with this form?
Grassy7
Trying to print invoices to keep better track of reimbursements to the construction yard account from the jobsites. The company I work for builds high rises (condos, hotels, hospitals, etc...). The purchasing manager (my boss) buys lots of power tools from different companies. Then when the project managers want a tool for their jobsite, then our yard account has to be credited from the jobsite account by way of our accounting dept. My boss' previous way of keeping track of this stuff was horrible, and he specifically asked if I could develop a way to print invoices. I've been working with Access on other stuff, and decided to use it for this. It's a small database. So far I have about 8 items. This shouldn't be that complicated right?
RuralGuy
So basically your system is a modified Inventory system correct? You purchase tools and put them into your inventory at the purchased price and then need to create an invoice so accounting will bill the job site for the tool and you remove it from your inventory. Does that sound about right?
ScottGem
Shouldn't be. You should have a transaction table, to assign equipment. Then use that table as the line items in your invoices.
RuralGuy
Scott,
Is that true even if the tool just gets expensed as soon as it gets assigned to a job site? Tools, even power tools are almost consumables for construction. They have a very short life span and I believe the IRS lets you expense it quickly. I don't think the OP is trying to keep track of where the tool is located, just get the expense to the right job site and off of the Purchasing Agent's books.
ScottGem
RG,
Not sure of the accounting. As I understood the process, he needs to prepare an "invoice" that accounting can process to bill the job site. I would assume the line items of that invoice would be the equipment assigned. So I would think there would need to be a table that lists what equipment is assigned to what site.
RuralGuy
I would think that all that is necessary is a table of equipment that is available to be assigned. I don't believe there is a requirement to track the equipment beyond it's first assignment. I could be wrong and maybe the OP will jump in here somewhere.
Grassy7
My Row Source for my combo box is
SELECT Products.*, [Products].[ProductID], [Products].[ProductName], [Products].[UnitPrice] FROM Products; "
Should this be different? I figured if I changed my Subform, it would automatically correct the Main Form the Subform is in. All I wanted was the Unit Price to pop up when I selected the product for each individual order. I've looked at other databases trying to mimic it but to no avail I guess.
RuralGuy
You do not need the "*" and the three fields. Just the three fields should be enough:
"SELECT ProductID, ProductName, UnitPrice FROM Products; "
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.