Data problems in a form (1 Viewer)

[James001]

I have a question pertaining to tables and forms.
I have an invoice form that has a number of subforms and the data is flowing very good. The problem i have is with an invoicedetails table subform that has a standard price field. I also have a table "products" that has a standard price field as well. What i want to be able to accomplish is when i choose from a drop menu a product in my form, that it puts the standard price that is located in the products table. I can get it do this, but one may want to overide this standard price. Now if i change the price it will change this standard price in the products table. I don't want this, i want to be able to overide the price just for the particular invoice. I think it has to do with relationships. The relationship i have right now is a one to many from the products.itemid to invoicedetails.itemid.
If more information is required for my problem i will gladly give it. Your help is very much appreciated.
James

 

[PNGBill]

You should have a tblProducts which has data for each Product including a Price - assuming you have just one price for now.

You have a tblInvoiceHeader which holds Sales data eg date ot sale, customer number, delevery address etc.

You have a tblInvoiceDetails which has a record appended for each line item on an invoice.
This table will hold the tblInvoiceHeader ref, productID, SalePrice, QTYSold etc

An Invoice for 10 items will result in One record added to tblInvoiceHeader and 10 records added to tblInvoiceDetail.

When you create an invoice you can have the Standard Price to be the default value for the tblInvoiceDetail record and then give an option to overide the price.

The process should not involve you accessing the tblProducts, just getting data from it.

The form to create an invoice will have a main form and a continous subform.

The controls on the continous subform can include DLookup to get the data from tblProducts where the ProductID mathes that selected in the same record of the continous subform - if this makes sence.

eg the left most control would be a list box with product names etc, use a query as source, and when selected the ProductID is stored. The rest of the controls (fields) are populated by DLookup.

If you want control of Price Edit you can have the controls set to allow only Additions but not Edit unless another command button is clicked.

 

[James001]

Thank you for your help.

I have the setup of the tables as to what you have mentioned. I will break it down a little bit more. I have a "tblProducts" table which contains fields such as "ProductCode" , "StandardCost", "ListPrice" (which is were the customers price is located) and other fields that are related to the products.

I have a "tblInvoices" table which contains fields such as "InvoiceID", "InvoiceDate", "CustomerID","EmployeeID","Tax","ShipperID"

I also have the related table called "tblInvoiceDetails" which contains fields "InvoiceID , "ItemID","Quantity","ListPrice" (this is the field that i want it to retrieve data from the "tblProducts"."ListPrice")and "ExtendedPrice".

I think what you stated a dlookup would be the way to go, from retrieving the data from "tblProducts"."ListPrice". But i don't know how to make the expression, I am getting the ?Name. If you can help with this expression it would be really really appreciated.

James

 

[PNGBill]

You may not or even should not have a filed ExtendedPrice if this is QQTY x Price then just calculate it every time.
This way, should the Price change or the Qty change you will still have a correct invoice value in the system because it will recalculate should it be required.
Of course you don't want an invoce value to change after a certain event, eg Month End so you would build in loacks to revent changes and messages advising to Raise a Credit etc.

Can you paste your DLookup. Use the # key above the screen where you type the message to give a place to paste the code.

 

[James001]

=DLookUp("[tblProducts]","[StandardPrice]","[ItemID]='" & [B][COLOR=red]Forms!frmProductsInvoiced[/COLOR][/B].[ItemID] & "'")

I get an Error on this code
James

 

[PNGBill]

Try this

=DLookUp("[tblProducts]","[StandardPrice]","[ItemID]=" & Forms![frmProductsInvoiced]![ItemID])

 

[James001]

Nope, get error.

 

[James001]

should there be a me statement?

 

[PNGBill]

see attached .pdf

You are referring to a control on the same form you are on.
Me!ControlName (Me!txtItemID)

Check the control names and make sure they are not the same as the record source
ItemID is the field name in the table which is the record source (unless you use a query and have renamed it)
The control name should be something like txtItemID
That way you have a clear seperation of the Control on the form and the data that was used to populate same.

 

[James001]

I am trying to understand the dlookup statement.

The "frmProductsInvoiced" has 4 fields the first one is a cbo box named "cboItemSearch" the Control Source for this cbo box is ItemID. It takes the list from the "tblProducts" and uses the "ProductCode" field for us. The second field is "Quantity", the third is the "StandardCost" (again this is the field that we want updated from the "frmProducts"."ListPrice"). The fourth is "ExtendedPrice" which is a calculated field that takes "quantity" * "StandardCost". This forms record source is the "tblInvoiceDetails". This has a relationship to the "tblProducts" using the "ItemsID" field and conects to the "tblInvoiceDetails" with a one to many relationship. I don't know how to write the dlookup, I really need help. Once i see how this is written i can then see the path of doing things. This is my first dlookup but i think this is the way to go, becuase it will put the the cost in the field but it could also be overwritten by the form (so I think).
Thanks so much,
James

 

[PNGBill]

This should work

=DLookUp("[tblProducts]","[StandardPrice]","[ItemID]=" & Me![cboItemSearch])

=DLookup("[TableName where data is]","[feild name in table]", "[other field name in table that can be matched to data on form]=" & Me![control name on form where data to match to table is])

Once you crack it it will make sence but in the meantime .

 

[James001]

OK I got an #Name? error so it is much closer, I think.
Thanks so much for you help.
James

 

[PNGBill]

Table name correct ?
Both field names in the table correct ?
Control name on form correct ?

 

[James001]

Table Name were data is stored :tblProducts
Fields in the "tblProducts" - "ItemID" and "ListPrice" which i changed in the code.
The form that I am using to put the data in is "frmProductsInvoiced" but i see you use the Me! and the field that is being used in this form is called "StandardCost".

I am very confused.
James

 

[James001]

Ok i change the combo box so that it retrieves the price as well. Then i take the price from the combo box. This is the code.
=cboItemSearch.Column(2).
Now the price shows up, i can't change the data. Is there some settings that one is aware of that would allow a person to overwright the data without affect the source data found in "tblProducts"?
Thanks
James

 

[PNGBill]

The control events should be able to resolve this.

On Edit event could have some code that removes the DLookup as the control source.

Some one else may be a ble to offer exact advice and if not, I will have a look in a few hours when I will be available.

 

[PNGBill]

Just before I head away.... The form you have will allow you to Add a record to the Invoice Detail Table.

If you want to edit that record, you could use another form that loads the Invoice Data and allows you to edit the Price and Qty and Delete a Row or Add a row (item).

The next step is you actually use the same form but a Command Button sets it to be for Adding a New record (new sales) or Editing an existing sale.
The code behind the button will have controls visible or not visible as required and the form data source etc.

If you use different Forms they can be identical in so many respects so that when you Click to Edit an existing sale or Add a New Sale you don't even notice the change of form.

 

[stopher]

Hi James

Take a look at the attached example (which I think collates PNGBills points). Specifically, the On Change event for the ItemID field looks up the price from the combo and puts the value in the ListPrice field for the invoice detail.

By doing it this way, the price is only looked up when a product is change. Thus, the user can overwrite whatever is in the ListPrice field.

The ExtendedPrice should be a calculated field i.e. no need to store it.

hth
Chris

 

[James001]

OK,
I didn't see the second page of the post, , I thought i was forgotten about. You have answered some pertitnent questions. This afternoon i will look at it, but i may have a question or two, but it looks like this has resolved the issue. I haven't though put it into mydatabase I still wonder why the dlookup did not work. I think this command is quite important when developing something like this but i will study this a bit more when i get a chance.
James

 

[James001]

Thanks for all the help. This worked out. Thank you as well for the working example. When i broke it apart and looked at the event procedure that also helped, especially the on change event. I want to say thank you, thank you, thank you.


James

More Questions will come.