A formula for my table

[Stephanie T.]

OK, normally I would not do a calculation in a table, I prefer to keep that to queries and reports, however this is necessary.

I have a table that keeps all of our sales data per order. When a distributor does not exchange a pallet at the time of pick up we charge them $7.50, but when the warehouse brings the paperwork into the office, they only write down how many pallets went out the door. So, it will say "2" and then the secretary does the math and charges the distributor $15.00.

I would like to set up the cell in the table to accpet the number "2" (based on example above), but enter that as $15.00. Is this possible? Is it too much trouble for Access??

Let me know if I'm being unreasonable and should just put this in the query for creating the report.

Thanks,
Stephanie

 

[Jerry Stoner]

Assuming you are using a form for data entry you can do this:
Make a table tblPalletChargeCost ( you are doing this so if that 7.50 charge changes you can alter in the table not code) that has one record: 7.50. Now add the table to the form recordsource (query). Make sure query is still updateable. If not you can use a subform that is not linked and is not visible. In your sales data per order table youll have a Qty field and a PalletCharge field. Now in your form you need txtQty, txtPalletCharge and txtPalletChargeCost(ie.7.5 which can be invisible) .

In the OnCurrent event use code like:
txtPalletCharge = txtQty * txtCurrentPalletCost

Good luck

 

[Stephanie T.]

Dear Jerry,

Thanks for the help. When I add the tblPalletChargeCost to the recordsource (query), my form comes up blank in Form View. Perhaps I'm adding it incorrectly?

What I did was open the query builder which already had the Sales Order table in there since it is a bound form. Then added the new tblPallerChargeCost, keeping both tables in there.

Am I doing something wrong? I am working with Access 97.

Thanks,
Stephanie

 

[Pat Hartman]

Having a table to hold the pallet charge cost is a good idea but you can't add it to the form's recordsource query because there is no field that you can use to link the two tables. Having both tables in the same query will result in a Cartesian product which is NOT updateable. You'll need to use DLookup() to obtain the pallet charge cost.

I would keep the qty and the unit cost for a pallet charge in the exchange table and calculate the extended cost for display.

Plade the DLookup() in the BeforeInsert event of the form to obtain the current pallet return charge.

Me.ReturnCharge = DLookup(........)

Then in the AfterUpdate event of the quantity field, calculate the extended cost:

Me.ExtendedCost = Me.Qty * Me.ReturnCharge

 

[Jerry Stoner]

Thanks for jumping in Pat

One question though. Whats wrong with the unlinked invisible subform idea? I do it this way all the time. Is DLookup a better way and if so why?

 

[Pat Hartman]

Much as I hate DLookup() (I have made lots of posts regarding its inefficiency), in this case, I believe it is better. Not by much, but subforms are "expensive" controls and require more system resources than a DLookup().

 

[Jerry Stoner]

Thanks Pat

And here Ive been using the subform for the very wrong reason - time. Oh well. Thanks again. Even in a subform with 1 field only( or 2 at most)?Yeah I guess that won't really matter.
Jerry

 

[Stephanie T.]

OK, let me see if I understand correctly. I have my tblOrders which has the related form where I want the data entry person to simply enter in the amount of pallets taken (Qty field) and then under the “Pallet Charge” field the formula we set up here will automatically calculate the amount to charge. So to do this, I should have a second table (we’ll call this tblPalletRecords) that has the Qty field, and the Unit Cost field. Would I also have the calculated cost in that table? What table should the calculated cost go to?

I am having a hard time writing the Dlookup() code. I have a VBA handbook with instruction, but I think I need to have a clearer picture of what tables with which fields I should be creating first. Then I need to know what is the first argument, what is the second argument or domain, and what is the search condition for this code? Finally, will this be written to the form or to the table and if so where? Sorry, this VBA is very new to me and I’m learning as I go with it – I am a pretty quick study though.

Thanks for your help,
Stephanie

 

[Jerry Stoner]

Your tblOrders contains qty and CurrentPalletCharge
tblPalletChargeCost has 1 field CurrentPalletCost

Your DLookup will look like this:
Me.txtCurrentPalletCharge = DLookup(CurrentPalletCost,tblPalletChargeCost,) (syntax may not be right) You should need no criteria there is only ever 1 record in the table

In the form do your DLookup in the BeforeInsert as Pat suggested, the recordsource is CurrentPalletCharge and the textbox is named txtCurrentPalletCharge


Then in AfterUpdate
txtPalletCharge = txtQty * txtCurrentPalletCharge

You are not storing the calculation result only the Current Pallet Charge. Since you have qty and the current cost stored you can always retrieve the correct total.
In your reports simply redo the calculation.

Hope I didnt make things worse for you with the somewhat convoluted explanation.

 

[Stephanie T.]

Jerry,

I am having some problems with the code, but before I burden you with all of that let me clarify one thing. All of the fields you mention correlate with my fields in a way that makes sense, except one. In the After Update the txtPalletCharge. I am assuming that this code is telling Access that in the PalletCharge field it needs to put the answer to that equation, but I do not have a PalletCharge field. I have, as you suggested, Qty and (what you call) CurrentPalletCharge fields in my tblOrders., and then CurrentPalletCost in my tblPalletChargeCost. So should I have an unbound field in the Orders form called PalletCharge where the final equation comes out (since we are not storing it in a table), or did you mean another field name, or am I missing something. I’m hoping that this will clear up my other coding problems, if not I’ll go from there – I am also using a VBA Handbook to assist me where I can.

Thanks,
Stephanie

 

[Jerry Stoner]

Yes an unbound field named txtPalletCharge to display the results of the calc.

 

[Stephanie T.]

Jerry,

Done, I created an unbound box FinalPalletCharge; for what you call CurrentPalletCharge I have PalletCharges. All of the other fields are the same. Here is my code:

Private Sub Form_AfterUpdate()
txtFinalPalletCharge = txtQty * txtCurrentPalletCost
End Sub

Private Sub Form_BeforeInsert()
Me.txtPalletCharges = DLookup(CurrentPalletCost, tblPalletChargeCost)
End Sub

Error:

The expression Before Insert you entered as the event property setting produced the following error: Event procedure declaration does not match description of event having the same name.

*the expression may not result in the name of a macro, the name of a user-defined function or [Event Procedure].
* there may have been an error evaluating the function, event or macro.

Perhaps the Event Procedures shouldn’t be in the “form” but part of a field?

Thanks,
Stephanie

 

[Jerry Stoner]

Me.txtPalletCharges = DLookup("CurrentPalletCost", "tblPalletChargeCost")

 

[Pat Hartman]

You need to be careful if you just type the name of an event procedure rather than letting Access build it for you. The proper syntax is:

Private Sub Form_BeforeInsert(Cancel As Integer)

Also,
The AfterUpdate event of the FORM is not the correct event to use to show the extended price. This event is not executed until after the record is saved which in most applications is when the form is closed or the user scrolls to a new record. Put the code in the AfterUpdate event of the quantity field. You also need the code in the Current event of the Form so that the extended price will show for each current record. However, you will need to check the NewRecord property because you don't want to do the calculation for new records at this point in time because no data has yet been entered on the form. So,

Private Sub Form_Current()
If Not Me.NewRecord Then
txtPalletCharge = txtQty * txtCurrentPalletCharge
End If
End Sub

 

[Jerry Stoner]

Thanks again Pat, The AfterUpdate of the form was a typo, I meant in the control. The rest I wouldn't have caught unless I was actually implementing the code. Once again you have proven your intuitiveness and expertise.
BTW whats up with the book?

 

[Pat Hartman]

I wish I could report some real progress. I've got several sample db's under construction:

Many-to-Many which I've sent to a number of people -- It shows a stripped down example of how to build and maintain the three tables involved in a many-to-many relationship.

CodeTableMaintenance -- Most systems have at least a few simple code tables. The usual solution is to either build separate tables or sometimes just use value lists in comboboxes. This db contains forms/reports and a table that you can import into your db to consolidate the maintenance of all your little "lookup" tables. This db also includes a sample of user-defined security.

ReportManager -- Forms and a table that let you define all your reports and consolidate parameter prompting for batch printing. This won't be a drop in. You will need to customize it for your set of parameters. You'll also need to change your reports to get their parameters from the prompting form.

LinkedTablesManager -- Refreshes links for both Access and ODBC linked tables on startup. Uses the common dialog API to let you find a new back end if any of the links fail. Handles multiple backend databases.

SurveyDB -- Forms/Reports/Tables to manage multiple surveys

DocumentationDB -- Links to database specified by common dialog API to obtain table/field/index info for local or linked tables and print report with more information than the built-in documenter. It has a couple of other non-related but useful code such as a form that shows the library references of the database you are going to document. Remember you can't see the entire string in the Access References dialog. It has a Fill List Box example that uses the database objects and last but not least a very rudimentary DDL builder.

 

[Jerry Stoner]

Great!

Thanks for the update. Maybe you should post this seperately so "your audience" knows whats up?

 

[Stephanie T.]

Pat and Jerry,

I'm getting the same error message as before, now in relation to the "On Current" code. As I mentioned I am in Access 97, so there wasn't a "Current" option, but only an "On Current" option, I assumed these work the same or this is what you meant.

I have the same problem with NewRecord Property. There is no NewRecord listed in the Properties option list. Am I looking in the wrong place or is it called something else in Access 97? I wonder if this part of the code was fixed if that would fix the "On Current" problem.

Thanks for your help,
Stephanie

 

[Pat Hartman]

Just type it in the on current event procedure:

Me.NewRecord

I just checked, A97 recognizes it.

 

[Stephanie T.]

OK, is this driving you mad? Here is the code I have:

Option Compare Database
Option Explicit

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.txtPalletCharges = DLookup("CurrentPalletCost", "tblPalletChargeCost")
End Sub

Private Sub Form_Current()
Me.NewRecord
End Sub

Private Sub Qty_AfterUpdate(Cancel As Integer)
txtFinalPalletCharge = txtQty * txtCurrentPalletCost
End Sub

I am still getting that error code of everything.

I may just leave it with her entering the data and we can do the equation in the query to show us in the report. I was hoping she would be able to do it in the form so that she could do a simple check of her invoice at that time. If you see an abovious problem your help is greatly appreciated!