Complex Problem Seeking Simple Solution

G

Guest

I have a database to inventory and track repack boxes (fireworks that have
been broken down for sell the previous season).

Each box is assigned to a specific tent # and each tent may have many boxes
and each box may have many products. Each box is given a unique bar code
number.

I have set up a form for entering the items in each box. The only fields in
this table based form are box #, item description, item # (auto populates
based on description), quantity returned, and packaging type of return (each,
box, brick, pack).

When these items are entered, I run them through an append to table query
that adds prices and calculation fields based on several criteria from
different tables (price level for customer, etc).

My problem is that for the value of the item and value of the box to
calculate I have to physically go into a form based on this new table and tab
through the value fields. This is okay if I am only entering a few items.
But some locations will have 30 plus boxes and each box could have dozens of
items. No to mention I have to search through all of the previously entered
boxes to find the new ones.

I attempted a Totals Query, but there is no place to put the code that
calculates the totals. Here is the code.

Private Sub PRICEOFITEM_GotFocus()
If PRICELISTCODE.Value = "JK" And PACKINGTYPE.Value = "EACH" Then
PRICEOFITEM = JKEACHPRICE
ElseIf PRICELISTCODE.Value = "JK" And PACKINGTYPE.Value = "PACK" Then
PRICEOFITEM = JKPACKPRICE
If JKPACKPRICE.Value = 0 Then
PRICEOFITEM = JKEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE.Value = "JK" And PACKINGTYPE.Value = "BOX" Then
PRICEOFITEM = JKBOXPRICE
If JKBOXPRICE = 0 Then
PRICEOFITEM = JKEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE.Value = "JK" And PACKINGTYPE.Value = "BRICK" Then
PRICEOFITEM = JKBRICKPRICE
ElseIf PRICELISTCODE.Value = "KS" And PACKINGTYPE.Value = "EACH" Then
PRICEOFITEM = KSEACHPRICE
ElseIf PRICELISTCODE.Value = "KS" And PACKINGTYPE.Value = "PACK" Then
PRICEOFITEM = KSPACKPRICE
If KSPACKPRICE.Value = 0 Then
PRICEOFITEM = KSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE.Value = "KS" And PACKINGTYPE.Value = "BOX" Then
PRICEOFITEM = KSBOXPRICE
If KSBOXPRICE.Value = 0 Then
PRICEOFITEM = KSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE.Value = "KS" And PACKINGTYPE.Value = "BRICK" Then
PRICEOFITEM = KSBRICKPRICE
ElseIf PRICELISTCODE.Value = "LA" And PACKINGTYPE.Value = "EACH" Then
PRICEOFITEM = LAEACHPRICE
ElseIf PRICELISTCODE.Value = "LA" And PACKINGTYPE.Value = "PACK" Then
PRICEOFITEM = LAPACKPRICE
If LAPACKPRICE.Value = 0 Then
PRICEOFITEM = LAEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE.Value = "LA" And PACKINGTYPE.Value = "BOX" Then
PRICEOFITEM = LABOXPRICE
If LABOXPRICE.Value = 0 Then
PRICEOFITEM = LAEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE.Value = "LA" And PACKINGTYPE.Value = "BRICK" Then
PRICEOFITEM = LABRICKPRICE
ElseIf PRICELISTCODE.Value = "WI" And PACKINGTYPE.Value = "EACH" Then
PRICEOFITEM = WIEACHPRICE
ElseIf PRICELISTCODE.Value = "WI" And PACKINGTYPE.Value = "PACK" Then
PRICEOFITEM = WIPACKPRICE
If WIPACKPRICE.Value = 0 Then
PRICEOFITEM = WIEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE.Value = "WI" And PACKINGTYPE.Value = "BOX" Then
PRICEOFITEM = WIBOXPRICE
If WIBOXPRICE.Value = 0 Then
PRICEOFITEM = WIEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE.Value = "WI" And PACKINGTYPE.Value = "BRICK" Then
PRICEOFITEM = WIBRICKPRICE
ElseIf PRICELISTCODE.Value = "WS" And PACKINGTYPE.Value = "EACH" Then
PRICEOFITEM = WSEACHPRICE
ElseIf PRICELISTCODE.Value = "WS" And PACKINGTYPE.Value = "PACK" Then
PRICEOFITEM = WSPACKPRICE
If WSPACKPRICE.Value = 0 Then
PRICEOFITEM = WSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE.Value = "WS" And PACKINGTYPE.Value = "BOX" Then
PRICEOFITEM = WSBOXPRICE
If WSBOXPRICE.Value = 0 Then
PRICEOFITEM = WSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE.Value = "WS" And PACKINGTYPE.Value = "BRICK" Then
PRICEOFITEM = WSBRICKPRICEElse
End If
End Sub

Private Sub Text43_GotFocus()
Text43 = PRICEOFITEM * QUANITYOFITEM
End Sub

It took me weeks to get this correct and working as I have it now.

Any suggestions on how to streamline this process.--
Lynette
 
G

Guest

Lynette:

As the PRICEOFITEM is computed from other values you don't need to have a
field for this in the table at all; you can use an unbound control in a form
or report. You just need to create a function in the form or report's
module, using your current code; you can simply paste the following code into
the form or report's module in its Declarations area, immediately below the
two lines already in place there. It will then be inserted as a new function
in the module. Note that you don't actually need to specify the Value
property of a control. As it’s the defaulkt property it can be omitted:

''''code begins''''
Private Function GetPriceOfItem() As Currency

If PRICELISTCODE= "JK" And PACKINGTYPE= "EACH" Then
PRICEOFITEM = JKEACHPRICE
ElseIf PRICELISTCODE= "JK" And PACKINGTYPE= "PACK" Then
PRICEOFITEM = JKPACKPRICE
If JKPACKPRICE= 0 Then
PRICEOFITEM = JKEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "JK" And PACKINGTYPE= "BOX" Then
PRICEOFITEM = JKBOXPRICE
If JKBOXPRICE = 0 Then
PRICEOFITEM = JKEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "JK" And PACKINGTYPE= "BRICK" Then
PRICEOFITEM = JKBRICKPRICE
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "EACH" Then
PRICEOFITEM = KSEACHPRICE
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "PACK" Then
PRICEOFITEM = KSPACKPRICE
If KSPACKPRICE= 0 Then
PRICEOFITEM = KSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "BOX" Then
PRICEOFITEM = KSBOXPRICE
If KSBOXPRICE= 0 Then
PRICEOFITEM = KSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "BRICK" Then
PRICEOFITEM = KSBRICKPRICE
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "EACH" Then
PRICEOFITEM = LAEACHPRICE
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "PACK" Then
PRICEOFITEM = LAPACKPRICE
If LAPACKPRICE= 0 Then
PRICEOFITEM = LAEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "BOX" Then
PRICEOFITEM = LABOXPRICE
If LABOXPRICE= 0 Then
PRICEOFITEM = LAEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "BRICK" Then
PRICEOFITEM = LABRICKPRICE
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "EACH" Then
PRICEOFITEM = WIEACHPRICE
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "PACK" Then
PRICEOFITEM = WIPACKPRICE
If WIPACKPRICE= 0 Then
PRICEOFITEM = WIEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "BOX" Then
PRICEOFITEM = WIBOXPRICE
If WIBOXPRICE= 0 Then
PRICEOFITEM = WIEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "BRICK" Then
PRICEOFITEM = WIBRICKPRICE
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "EACH" Then
PRICEOFITEM = WSEACHPRICE
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "PACK" Then
PRICEOFITEM = WSPACKPRICE
If WSPACKPRICE= 0 Then
PRICEOFITEM = WSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "BOX" Then
PRICEOFITEM = WSBOXPRICE
If WSBOXPRICE= 0 Then
PRICEOFITEM = WSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "BRICK" Then
PRICEOFITEM = WSBRICKPRICEElse
End If

End Function
''''code ends''''

After adding this function to the form or report's module, delete the code
for the PRICEOFITEM control's GotFocus event procedure and set its
ControlSource property to:

=GetPriceOfItem()

This control is now unbound, as the PRICEOFITEM field in the table no longer
exists, so it will automatically show the computed value without you having
to tab through the records.

For the Text43 control do the same, deleting the code for its GotFocus event
procedure and setting its ControlSource to:

=GetPriceOfItem() * QUANITYOFITEM

You could do the same in a query using a Public function in a standard
module, but you would need to change the code for the function so that,
instead of referencing other controls, as in a form or report, the values of
other fields needed for the computation are passed into the function as
arguments.

As far as finding the new boxes is concerned there is probably some way you
could filter the form and/or report to show just the ones you want, but I
can't say more than that on the basis of the information you've given.
You'll know what the criteria would be, I've no doubt, so if you can expand a
bit on this aspect we might be able to help further.

Ken Sheridan
Stafford, England
 
G

Guest

Ken,

Where exactly is the Declarations area of a module? Do I get to it by going
into the design view of the form and then clicking on the code button. Or do
I need to create a new module?

As far as finding the boxes, they are assigned to tent numbers, but I do not
include the tent numbers in the repack detail list, I have a query that adds
them later. The reason I did this is the bosses want me to be able to do
several reports from each tent's total repacks, to each individual item
returned and quanity with a total value for accounting purposes.

Generally I take one tent location at a time and entry all of their repack
boxes. They are entered in a clean table called Repack Box Detail. Then I
run a quick report and verify all the entries. At this point I have only the
box number, item despcription, item no, quanity and packaing. After
corrections (if any) are made, I run the entries through the query that
appends them to Retail Box Append Table. They are added to the entries from
prievious locations. News fields are then included such as the price per
item, the value of each item, and prices of all five price levels. This seem
to be the only way I could get the calcualations to work. Then a delete
query is ran to clear out the Repack Box Detail table for the next location.
When this is completed, I open the Repack Box Detail Append Table and tab
through all the entries to get the calculations to figure.

Is there a way to search by entry date or time?
 
G

Guest

I have the code changed and set in the visual basic screen that appears when
you click the code button in the design view of the form. But the field now
says #error
Any thoughts--
Lynette
 
G

Guest

Lynette:

Mea culpa; I omitted to change the code so it set the returns value of the
function rather than assigns a value to the control. It should be:

''''code begins''''
Private Function GetPriceOfItem() As Currency

If PRICELISTCODE= "JK" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = JKEACHPRICE
ElseIf PRICELISTCODE= "JK" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = JKPACKPRICE
If JKPACKPRICE= 0 Then
GetPriceOfItem = JKEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "JK" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = JKBOXPRICE
If JKBOXPRICE = 0 Then
GetPriceOfItem = JKEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "JK" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = JKBRICKPRICE
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = KSEACHPRICE
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = KSPACKPRICE
If KSPACKPRICE= 0 Then
GetPriceOfItem = KSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = KSBOXPRICE
If KSBOXPRICE= 0 Then
GetPriceOfItem = KSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = KSBRICKPRICE
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = LAEACHPRICE
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = LAPACKPRICE
If LAPACKPRICE= 0 Then
GetPriceOfItem = LAEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = LABOXPRICE
If LABOXPRICE= 0 Then
GetPriceOfItem = LAEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = LABRICKPRICE
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = WIEACHPRICE
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = WIPACKPRICE
If WIPACKPRICE= 0 Then
GetPriceOfItem = WIEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = WIBOXPRICE
If WIBOXPRICE= 0 Then
GetPriceOfItem = WIEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = WIBRICKPRICE
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = WSEACHPRICE
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = WSPACKPRICE
If WSPACKPRICE= 0 Then
GetPriceOfItem = WSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = WSBOXPRICE
If WSBOXPRICE= 0 Then
GetPriceOfItem = WSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = WSBRICKPRICEElse
End If

End Function
''''code ends''''

You are right about clicking the Code button when in form design to open the
form's module. When it opens it should say Declarations in the top right
combo box. If not then select Declarations from the list. When you've
pasted the function in the window below the lines already in place it should
show the inserted function and the function name, GetPriceOfItem, will appear
in the combo box.

As regards searching by the entry date/time you could add a column,
DateTimeStamp say, of date/time data type to the table with its DefaultValue
property set to Now() in table design. When a new row is inserted into the
table the current date/time will automatically be inserted into the new row.

You can then search by date in a query with a criterion for the
DateTimeStamp column of, for instance:
= [Enter start date:]
Click to expand...

That way when you open the query or a form or report which uses it as its
RecordSource you'd be prompted to enter a date and only the rows inserted on
or after that date would be returned. If you include the time of day when
responding to the prompt only rows inserted n or after that time on the day
along with rows inserted at any time on subsequent days would be returned.

Or you could return all rows inserted in the current week for instance with
a criterion for the DateTimeStamp column of:
= Date() – Weekday(Date())+1
Click to expand...

This expression returns the Sunday previous to the current date, or the
current date if it is Sunday, so the criterion would return all rows inserted
= Date()
Click to expand...

Note that there are no date values as such in Access only date/time values.
A date without a time is actually midnight at the start of the day. That's
why its necessary to use >= rather than merely =. The latter would return
only those rows entered exactly on the dot of midnight, which is not going to
be many I'd guess!

Would something like the above help? If you need to pin them down by some
other date/time criterion let me know and I'll see if I can come up with a
suitable expression. You can for instance be prompted for start and end
dates, so you could return rows inserted on particular days in a range.

BTW I started learning to drive in the car park of a fireworks factory here
in England. My mother worked there and I used to practice in my Dad's car
while we were waiting for here to finish work.

Ken Sheridan
Stafford, England

ksfireworksgal said:
I have the code changed and set in the visual basic screen that appears when
you click the code button in the design view of the form. But the field now
says #error
Any thoughts--
Lynette
Click to expand...

ksfireworksgal said:
Ken,

Where exactly is the Declarations area of a module? Do I get to it by going
into the design view of the form and then clicking on the code button. Or do
I need to create a new module?

As far as finding the boxes, they are assigned to tent numbers, but I do not
include the tent numbers in the repack detail list, I have a query that adds
them later. The reason I did this is the bosses want me to be able to do
several reports from each tent's total repacks, to each individual item
returned and quanity with a total value for accounting purposes.

Generally I take one tent location at a time and entry all of their repack
boxes. They are entered in a clean table called Repack Box Detail. Then I
run a quick report and verify all the entries. At this point I have only the
box number, item despcription, item no, quanity and packaing. After
corrections (if any) are made, I run the entries through the query that
appends them to Retail Box Append Table. They are added to the entries from
prievious locations. News fields are then included such as the price per
item, the value of each item, and prices of all five price levels. This seem
to be the only way I could get the calcualations to work. Then a delete
query is ran to clear out the Repack Box Detail table for the next location.
When this is completed, I open the Repack Box Detail Append Table and tab
through all the entries to get the calculations to figure.

Is there a way to search by entry date or time?
Click to expand...
 
G

Guest

Another Delima

I guess this is why you test things before you enter thousands of rows of
data.

Because the form is no longer storing the value of each price and value of
item. My query total to sum the boxes doesn't work. The query doesn't have
a value to attach.

Any suggestions?
--
Lynette


Ken Sheridan said:
Lynette:

Mea culpa; I omitted to change the code so it set the returns value of the
function rather than assigns a value to the control. It should be:

''''code begins''''
Private Function GetPriceOfItem() As Currency

If PRICELISTCODE= "JK" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = JKEACHPRICE
ElseIf PRICELISTCODE= "JK" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = JKPACKPRICE
If JKPACKPRICE= 0 Then
GetPriceOfItem = JKEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "JK" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = JKBOXPRICE
If JKBOXPRICE = 0 Then
GetPriceOfItem = JKEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "JK" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = JKBRICKPRICE
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = KSEACHPRICE
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = KSPACKPRICE
If KSPACKPRICE= 0 Then
GetPriceOfItem = KSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = KSBOXPRICE
If KSBOXPRICE= 0 Then
GetPriceOfItem = KSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = KSBRICKPRICE
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = LAEACHPRICE
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = LAPACKPRICE
If LAPACKPRICE= 0 Then
GetPriceOfItem = LAEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = LABOXPRICE
If LABOXPRICE= 0 Then
GetPriceOfItem = LAEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = LABRICKPRICE
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = WIEACHPRICE
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = WIPACKPRICE
If WIPACKPRICE= 0 Then
GetPriceOfItem = WIEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = WIBOXPRICE
If WIBOXPRICE= 0 Then
GetPriceOfItem = WIEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = WIBRICKPRICE
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = WSEACHPRICE
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = WSPACKPRICE
If WSPACKPRICE= 0 Then
GetPriceOfItem = WSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = WSBOXPRICE
If WSBOXPRICE= 0 Then
GetPriceOfItem = WSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = WSBRICKPRICEElse
End If

End Function
''''code ends''''

You are right about clicking the Code button when in form design to open the
form's module. When it opens it should say Declarations in the top right
combo box. If not then select Declarations from the list. When you've
pasted the function in the window below the lines already in place it should
show the inserted function and the function name, GetPriceOfItem, will appear
in the combo box.

As regards searching by the entry date/time you could add a column,
DateTimeStamp say, of date/time data type to the table with its DefaultValue
property set to Now() in table design. When a new row is inserted into the
table the current date/time will automatically be inserted into the new row.

You can then search by date in a query with a criterion for the
DateTimeStamp column of, for instance:
= [Enter start date:]
Click to expand...

That way when you open the query or a form or report which uses it as its
RecordSource you'd be prompted to enter a date and only the rows inserted on
or after that date would be returned. If you include the time of day when
responding to the prompt only rows inserted n or after that time on the day
along with rows inserted at any time on subsequent days would be returned.

Or you could return all rows inserted in the current week for instance with
a criterion for the DateTimeStamp column of:
= Date() – Weekday(Date())+1
Click to expand...

This expression returns the Sunday previous to the current date, or the
current date if it is Sunday, so the criterion would return all rows inserted
= Date()
Click to expand...

Note that there are no date values as such in Access only date/time values.
A date without a time is actually midnight at the start of the day. That's
why its necessary to use >= rather than merely =. The latter would return
only those rows entered exactly on the dot of midnight, which is not going to
be many I'd guess!

Would something like the above help? If you need to pin them down by some
other date/time criterion let me know and I'll see if I can come up with a
suitable expression. You can for instance be prompted for start and end
dates, so you could return rows inserted on particular days in a range.

BTW I started learning to drive in the car park of a fireworks factory here
in England. My mother worked there and I used to practice in my Dad's car
while we were waiting for here to finish work.

Ken Sheridan
Stafford, England

ksfireworksgal said:
I have the code changed and set in the visual basic screen that appears when
you click the code button in the design view of the form. But the field now
says #error
Any thoughts--
Lynette
Click to expand...

ksfireworksgal said:
Ken,

Where exactly is the Declarations area of a module? Do I get to it by going
into the design view of the form and then clicking on the code button. Or do
I need to create a new module?

As far as finding the boxes, they are assigned to tent numbers, but I do not
include the tent numbers in the repack detail list, I have a query that adds
them later. The reason I did this is the bosses want me to be able to do
several reports from each tent's total repacks, to each individual item
returned and quanity with a total value for accounting purposes.

Generally I take one tent location at a time and entry all of their repack
boxes. They are entered in a clean table called Repack Box Detail. Then I
run a quick report and verify all the entries. At this point I have only the
box number, item despcription, item no, quanity and packaing. After
corrections (if any) are made, I run the entries through the query that
appends them to Retail Box Append Table. They are added to the entries from
prievious locations. News fields are then included such as the price per
item, the value of each item, and prices of all five price levels. This seem
to be the only way I could get the calcualations to work. Then a delete
query is ran to clear out the Repack Box Detail table for the next location.
When this is completed, I open the Repack Box Detail Append Table and tab
through all the entries to get the calculations to figure.

Is there a way to search by entry date or time?
Click to expand...
Click to expand...
 
G

Guest

I got it to work in the form. But because is doesn't save the value
anywhere, I need it to show up in a report as well.

But I get a #Error - the only difference I can think of, is that in the form
each level of pricing is displayed. But not in the report. Each of the five
price levles has a price for each, pack, box, brick. I don't have room to
display such info in the report.

Any suggestions.
--
Lynette


Ken Sheridan said:
Lynette:

Mea culpa; I omitted to change the code so it set the returns value of the
function rather than assigns a value to the control. It should be:

''''code begins''''
Private Function GetPriceOfItem() As Currency

If PRICELISTCODE= "JK" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = JKEACHPRICE
ElseIf PRICELISTCODE= "JK" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = JKPACKPRICE
If JKPACKPRICE= 0 Then
GetPriceOfItem = JKEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "JK" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = JKBOXPRICE
If JKBOXPRICE = 0 Then
GetPriceOfItem = JKEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "JK" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = JKBRICKPRICE
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = KSEACHPRICE
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = KSPACKPRICE
If KSPACKPRICE= 0 Then
GetPriceOfItem = KSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = KSBOXPRICE
If KSBOXPRICE= 0 Then
GetPriceOfItem = KSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = KSBRICKPRICE
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = LAEACHPRICE
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = LAPACKPRICE
If LAPACKPRICE= 0 Then
GetPriceOfItem = LAEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = LABOXPRICE
If LABOXPRICE= 0 Then
GetPriceOfItem = LAEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = LABRICKPRICE
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = WIEACHPRICE
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = WIPACKPRICE
If WIPACKPRICE= 0 Then
GetPriceOfItem = WIEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = WIBOXPRICE
If WIBOXPRICE= 0 Then
GetPriceOfItem = WIEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = WIBRICKPRICE
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = WSEACHPRICE
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = WSPACKPRICE
If WSPACKPRICE= 0 Then
GetPriceOfItem = WSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = WSBOXPRICE
If WSBOXPRICE= 0 Then
GetPriceOfItem = WSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = WSBRICKPRICEElse
End If

End Function
''''code ends''''

You are right about clicking the Code button when in form design to open the
form's module. When it opens it should say Declarations in the top right
combo box. If not then select Declarations from the list. When you've
pasted the function in the window below the lines already in place it should
show the inserted function and the function name, GetPriceOfItem, will appear
in the combo box.

As regards searching by the entry date/time you could add a column,
DateTimeStamp say, of date/time data type to the table with its DefaultValue
property set to Now() in table design. When a new row is inserted into the
table the current date/time will automatically be inserted into the new row.

You can then search by date in a query with a criterion for the
DateTimeStamp column of, for instance:
= [Enter start date:]
Click to expand...

That way when you open the query or a form or report which uses it as its
RecordSource you'd be prompted to enter a date and only the rows inserted on
or after that date would be returned. If you include the time of day when
responding to the prompt only rows inserted n or after that time on the day
along with rows inserted at any time on subsequent days would be returned.

Or you could return all rows inserted in the current week for instance with
a criterion for the DateTimeStamp column of:
= Date() – Weekday(Date())+1
Click to expand...

This expression returns the Sunday previous to the current date, or the
current date if it is Sunday, so the criterion would return all rows inserted
= Date()
Click to expand...

Note that there are no date values as such in Access only date/time values.
A date without a time is actually midnight at the start of the day. That's
why its necessary to use >= rather than merely =. The latter would return
only those rows entered exactly on the dot of midnight, which is not going to
be many I'd guess!

Would something like the above help? If you need to pin them down by some
other date/time criterion let me know and I'll see if I can come up with a
suitable expression. You can for instance be prompted for start and end
dates, so you could return rows inserted on particular days in a range.

BTW I started learning to drive in the car park of a fireworks factory here
in England. My mother worked there and I used to practice in my Dad's car
while we were waiting for here to finish work.

Ken Sheridan
Stafford, England

ksfireworksgal said:
I have the code changed and set in the visual basic screen that appears when
you click the code button in the design view of the form. But the field now
says #error
Any thoughts--
Lynette
Click to expand...

ksfireworksgal said:
Ken,

Where exactly is the Declarations area of a module? Do I get to it by going
into the design view of the form and then clicking on the code button. Or do
I need to create a new module?

As far as finding the boxes, they are assigned to tent numbers, but I do not
include the tent numbers in the repack detail list, I have a query that adds
them later. The reason I did this is the bosses want me to be able to do
several reports from each tent's total repacks, to each individual item
returned and quanity with a total value for accounting purposes.

Generally I take one tent location at a time and entry all of their repack
boxes. They are entered in a clean table called Repack Box Detail. Then I
run a quick report and verify all the entries. At this point I have only the
box number, item despcription, item no, quanity and packaing. After
corrections (if any) are made, I run the entries through the query that
appends them to Retail Box Append Table. They are added to the entries from
prievious locations. News fields are then included such as the price per
item, the value of each item, and prices of all five price levels. This seem
to be the only way I could get the calcualations to work. Then a delete
query is ran to clear out the Repack Box Detail table for the next location.
When this is completed, I open the Repack Box Detail Append Table and tab
through all the entries to get the calculations to figure.

Is there a way to search by entry date or time?
Click to expand...
Click to expand...
 
G

Guest

Lynette:

You should be able to do it in a report. First you put the function in the
report's module, just like in the form's. Reports do differ from forms'
however in that you can only refer to a bound control in a report's module,
not to a column in the underlying table or query. This is not a problem,
however because you simply put bound controls in the detail section of the
report and set their Visible property to false to hide them. The position or
size of the hidden controls is irrelevant; you can put all of them on top of
each other if you wish and set their height to zero. You only really need do
the latter, however, if the detail section's height is zero, i.e. if you are
only showing aggregated values in footers in the report.

If you want to aggregate the return values of a function over a group in a
report you do it in a rather different way to the usual method when dealing
with a column in the table. Say you want to sum a PriceOfItem column from
the underlying table in a group footer in a report you'd just use
=Sum([PriceOfItem]) as the ControlSource of an unbound text box in the group
footer.

When the price of each item is returned by the GetPriceOfItem function in
the report's module you can't do that, The way its done would be to have
both a group header and footer. The group header can be zero height and
doesn't necessarily contain any controls.

You put an unbound text box, txtTotalPrice say, in the group footer. In the
group header's Format event procedure you initialise it to zero:

txtTotalPrice = 0

And in the detail section's format event procedure you increment it by the
return value of the function

If FormatCount = 1 Then
txtTotalPrice = txtTotalPrice + GetPriceOfItem()
End If

You can of course aggregate values over nested group levels if you wish.
You just put unbound controls in the relevant group footers and initialise
each to zero in the relevant group header.

Ken Sheridan
Stafford, England

ksfireworksgal said:
I got it to work in the form. But because is doesn't save the value
anywhere, I need it to show up in a report as well.

But I get a #Error - the only difference I can think of, is that in the form
each level of pricing is displayed. But not in the report. Each of the five
price levles has a price for each, pack, box, brick. I don't have room to
display such info in the report.

Any suggestions.
--
Lynette


Ken Sheridan said:
Lynette:

Mea culpa; I omitted to change the code so it set the returns value of the
function rather than assigns a value to the control. It should be:

''''code begins''''
Private Function GetPriceOfItem() As Currency

If PRICELISTCODE= "JK" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = JKEACHPRICE
ElseIf PRICELISTCODE= "JK" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = JKPACKPRICE
If JKPACKPRICE= 0 Then
GetPriceOfItem = JKEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "JK" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = JKBOXPRICE
If JKBOXPRICE = 0 Then
GetPriceOfItem = JKEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "JK" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = JKBRICKPRICE
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = KSEACHPRICE
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = KSPACKPRICE
If KSPACKPRICE= 0 Then
GetPriceOfItem = KSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = KSBOXPRICE
If KSBOXPRICE= 0 Then
GetPriceOfItem = KSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = KSBRICKPRICE
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = LAEACHPRICE
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = LAPACKPRICE
If LAPACKPRICE= 0 Then
GetPriceOfItem = LAEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = LABOXPRICE
If LABOXPRICE= 0 Then
GetPriceOfItem = LAEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = LABRICKPRICE
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = WIEACHPRICE
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = WIPACKPRICE
If WIPACKPRICE= 0 Then
GetPriceOfItem = WIEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = WIBOXPRICE
If WIBOXPRICE= 0 Then
GetPriceOfItem = WIEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = WIBRICKPRICE
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = WSEACHPRICE
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = WSPACKPRICE
If WSPACKPRICE= 0 Then
GetPriceOfItem = WSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = WSBOXPRICE
If WSBOXPRICE= 0 Then
GetPriceOfItem = WSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = WSBRICKPRICEElse
End If

End Function
''''code ends''''

You are right about clicking the Code button when in form design to open the
form's module. When it opens it should say Declarations in the top right
combo box. If not then select Declarations from the list. When you've
pasted the function in the window below the lines already in place it should
show the inserted function and the function name, GetPriceOfItem, will appear
in the combo box.

As regards searching by the entry date/time you could add a column,
DateTimeStamp say, of date/time data type to the table with its DefaultValue
property set to Now() in table design. When a new row is inserted into the
table the current date/time will automatically be inserted into the new row.

You can then search by date in a query with a criterion for the
DateTimeStamp column of, for instance:
= [Enter start date:]
Click to expand...

That way when you open the query or a form or report which uses it as its
RecordSource you'd be prompted to enter a date and only the rows inserted on
or after that date would be returned. If you include the time of day when
responding to the prompt only rows inserted n or after that time on the day
along with rows inserted at any time on subsequent days would be returned.

Or you could return all rows inserted in the current week for instance with
a criterion for the DateTimeStamp column of:
= Date() – Weekday(Date())+1
Click to expand...

This expression returns the Sunday previous to the current date, or the
current date if it is Sunday, so the criterion would return all rows inserted
= Date()
Click to expand...

Note that there are no date values as such in Access only date/time values.
A date without a time is actually midnight at the start of the day. That's
why its necessary to use >= rather than merely =. The latter would return
only those rows entered exactly on the dot of midnight, which is not going to
be many I'd guess!

Would something like the above help? If you need to pin them down by some
other date/time criterion let me know and I'll see if I can come up with a
suitable expression. You can for instance be prompted for start and end
dates, so you could return rows inserted on particular days in a range.

BTW I started learning to drive in the car park of a fireworks factory here
in England. My mother worked there and I used to practice in my Dad's car
while we were waiting for here to finish work.

Ken Sheridan
Stafford, England

ksfireworksgal said:
I have the code changed and set in the visual basic screen that appears when
you click the code button in the design view of the form. But the field now
says #error
Any thoughts--
Lynette
Click to expand...

ksfireworksgal said:
Ken,

Where exactly is the Declarations area of a module? Do I get to it by going
into the design view of the form and then clicking on the code button. Or do
I need to create a new module?

As far as finding the boxes, they are assigned to tent numbers, but I do not
include the tent numbers in the repack detail list, I have a query that adds
them later. The reason I did this is the bosses want me to be able to do
several reports from each tent's total repacks, to each individual item
returned and quanity with a total value for accounting purposes.

Generally I take one tent location at a time and entry all of their repack
boxes. They are entered in a clean table called Repack Box Detail. Then I
run a quick report and verify all the entries. At this point I have only the
box number, item despcription, item no, quanity and packaing. After
corrections (if any) are made, I run the entries through the query that
appends them to Retail Box Append Table. They are added to the entries from
prievious locations. News fields are then included such as the price per
item, the value of each item, and prices of all five price levels. This seem
to be the only way I could get the calcualations to work. Then a delete
query is ran to clear out the Repack Box Detail table for the next location.
When this is completed, I open the Repack Box Detail Append Table and tab
through all the entries to get the calculations to figure.

Is there a way to search by entry date or time?
--
Lynette


:

Lynette:

As the PRICEOFITEM is computed from other values you don't need to have a
field for this in the table at all; you can use an unbound control in a form
or report. You just need to create a function in the form or report's
module, using your current code; you can simply paste the following code into
the form or report's module in its Declarations area, immediately below the
two lines already in place there. It will then be inserted as a new function
in the module. Note that you don't actually need to specify the Value
property of a control. As it’s the defaulkt property it can be omitted:

''''code begins''''
Private Function GetPriceOfItem() As Currency

If PRICELISTCODE= "JK" And PACKINGTYPE= "EACH" Then
PRICEOFITEM = JKEACHPRICE
ElseIf PRICELISTCODE= "JK" And PACKINGTYPE= "PACK" Then
PRICEOFITEM = JKPACKPRICE
If JKPACKPRICE= 0 Then
PRICEOFITEM = JKEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "JK" And PACKINGTYPE= "BOX" Then
PRICEOFITEM = JKBOXPRICE
If JKBOXPRICE = 0 Then
PRICEOFITEM = JKEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "JK" And PACKINGTYPE= "BRICK" Then
PRICEOFITEM = JKBRICKPRICE
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "EACH" Then
PRICEOFITEM = KSEACHPRICE
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "PACK" Then
PRICEOFITEM = KSPACKPRICE
If KSPACKPRICE= 0 Then
PRICEOFITEM = KSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "BOX" Then
PRICEOFITEM = KSBOXPRICE
If KSBOXPRICE= 0 Then
PRICEOFITEM = KSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "BRICK" Then
PRICEOFITEM = KSBRICKPRICE
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "EACH" Then
PRICEOFITEM = LAEACHPRICE
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "PACK" Then
PRICEOFITEM = LAPACKPRICE
If LAPACKPRICE= 0 Then
PRICEOFITEM = LAEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "BOX" Then
PRICEOFITEM = LABOXPRICE
If LABOXPRICE= 0 Then
PRICEOFITEM = LAEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "BRICK" Then
PRICEOFITEM = LABRICKPRICE
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "EACH" Then
PRICEOFITEM = WIEACHPRICE
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "PACK" Then
PRICEOFITEM = WIPACKPRICE
If WIPACKPRICE= 0 Then
PRICEOFITEM = WIEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "BOX" Then
PRICEOFITEM = WIBOXPRICE
If WIBOXPRICE= 0 Then
PRICEOFITEM = WIEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "BRICK" Then
PRICEOFITEM = WIBRICKPRICE
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "EACH" Then
PRICEOFITEM = WSEACHPRICE
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "PACK" Then
PRICEOFITEM = WSPACKPRICE
If WSPACKPRICE= 0 Then
PRICEOFITEM = WSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "BOX" Then
PRICEOFITEM = WSBOXPRICE
If WSBOXPRICE= 0 Then
PRICEOFITEM = WSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "BRICK" Then
PRICEOFITEM = WSBRICKPRICEElse
End If

End Function
''''code ends''''

After adding this function to the form or report's module, delete the code
for the PRICEOFITEM control's GotFocus event procedure and set its
ControlSource property to:

=GetPriceOfItem()

This control is now unbound, as the PRICEOFITEM field in the table no longer
exists, so it will automatically show the computed value without you having
to tab through the records.

For the Text43 control do the same, deleting the code for its GotFocus event
procedure and setting its ControlSource to:

=GetPriceOfItem() * QUANITYOFITEM

You could do the same in a query using a Public function in a standard
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Lynette:

If the query is the basis for a report then you can use an ungrouped query
and do the summing in the report in the way I described in my last post. You
could do it in the query, but as I said in one of my earlier replies the
function would have to be declared Public in a standard module, not a form or
report's class module, and the function would have to accept the values from
the other columns as arguments instead of referring to the values in the body
of the code as at present. In fact that's how a function would usually be
written, but as you've obviously expended a lot of time and effort in your
code I tried to keep as close to it as possible.

Rather than try and amend your code let me give you a simple example of how
a function might be written. That way you should be able to understand the
underlying principles better than if I just rewrote the function. With an
understanding of what's going on you'll be equipped to go on and find
solutions to your problems rather than just implementing something I give you
without really knowing how it does it. Let's say you have a table Products
with columns ProductID, CategoryID , NetPrice and TaxRate. You want a query
which gives the sum of the gross prices (net prices plus tax) for each
category of product. Now that's easily done with an expression, but for the
sake of this example lets do it with a function. So you'd put a function in
a standard module:

Public Function GetGrossPrice(curNetPrice As Currency, dblTaxRate As Double)
As Currency

GetGrossPrice = curNetPrice * (1 + dblTaxRate)

End Function

This function takes the net price and tax rate (stored as a decimal value
such as 0.175) as its arguments and multiplies the net price times 1 plus the
tax rate.

In a query you'd then call the function as the argument for the built in SUM
function like so, passing the value of the net price and tax rate columns
into it:

SELECT CategoryID,
SUM(GetGrossPrice(NetPrice,TaxRate)) AS GrossPrice
FROM Products
GROUP BY CategoryID;

In your case the function would have each value you presently refer to in
your code passed into it as an argument, the PRICELISTCODE, JKPACKPRICE etc
and in the code instead of referring directly to the value the code would
refer to the argument in the same way that my simple example above does.

Ken Sheridan
Stafford, England

ksfireworksgal said:
Another Delima

I guess this is why you test things before you enter thousands of rows of
data.

Because the form is no longer storing the value of each price and value of
item. My query total to sum the boxes doesn't work. The query doesn't have
a value to attach.

Any suggestions?
--
Lynette


Ken Sheridan said:
Lynette:

Mea culpa; I omitted to change the code so it set the returns value of the
function rather than assigns a value to the control. It should be:

''''code begins''''
Private Function GetPriceOfItem() As Currency

If PRICELISTCODE= "JK" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = JKEACHPRICE
ElseIf PRICELISTCODE= "JK" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = JKPACKPRICE
If JKPACKPRICE= 0 Then
GetPriceOfItem = JKEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "JK" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = JKBOXPRICE
If JKBOXPRICE = 0 Then
GetPriceOfItem = JKEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "JK" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = JKBRICKPRICE
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = KSEACHPRICE
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = KSPACKPRICE
If KSPACKPRICE= 0 Then
GetPriceOfItem = KSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = KSBOXPRICE
If KSBOXPRICE= 0 Then
GetPriceOfItem = KSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = KSBRICKPRICE
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = LAEACHPRICE
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = LAPACKPRICE
If LAPACKPRICE= 0 Then
GetPriceOfItem = LAEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = LABOXPRICE
If LABOXPRICE= 0 Then
GetPriceOfItem = LAEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = LABRICKPRICE
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = WIEACHPRICE
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = WIPACKPRICE
If WIPACKPRICE= 0 Then
GetPriceOfItem = WIEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = WIBOXPRICE
If WIBOXPRICE= 0 Then
GetPriceOfItem = WIEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = WIBRICKPRICE
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = WSEACHPRICE
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = WSPACKPRICE
If WSPACKPRICE= 0 Then
GetPriceOfItem = WSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = WSBOXPRICE
If WSBOXPRICE= 0 Then
GetPriceOfItem = WSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = WSBRICKPRICEElse
End If

End Function
''''code ends''''

You are right about clicking the Code button when in form design to open the
form's module. When it opens it should say Declarations in the top right
combo box. If not then select Declarations from the list. When you've
pasted the function in the window below the lines already in place it should
show the inserted function and the function name, GetPriceOfItem, will appear
in the combo box.

As regards searching by the entry date/time you could add a column,
DateTimeStamp say, of date/time data type to the table with its DefaultValue
property set to Now() in table design. When a new row is inserted into the
table the current date/time will automatically be inserted into the new row.

You can then search by date in a query with a criterion for the
DateTimeStamp column of, for instance:
= [Enter start date:]
Click to expand...

That way when you open the query or a form or report which uses it as its
RecordSource you'd be prompted to enter a date and only the rows inserted on
or after that date would be returned. If you include the time of day when
responding to the prompt only rows inserted n or after that time on the day
along with rows inserted at any time on subsequent days would be returned.

Or you could return all rows inserted in the current week for instance with
a criterion for the DateTimeStamp column of:
= Date() – Weekday(Date())+1
Click to expand...

This expression returns the Sunday previous to the current date, or the
current date if it is Sunday, so the criterion would return all rows inserted
= Date()
Click to expand...

Note that there are no date values as such in Access only date/time values.
A date without a time is actually midnight at the start of the day. That's
why its necessary to use >= rather than merely =. The latter would return
only those rows entered exactly on the dot of midnight, which is not going to
be many I'd guess!

Would something like the above help? If you need to pin them down by some
other date/time criterion let me know and I'll see if I can come up with a
suitable expression. You can for instance be prompted for start and end
dates, so you could return rows inserted on particular days in a range.

BTW I started learning to drive in the car park of a fireworks factory here
in England. My mother worked there and I used to practice in my Dad's car
while we were waiting for here to finish work.

Ken Sheridan
Stafford, England

ksfireworksgal said:
I have the code changed and set in the visual basic screen that appears when
you click the code button in the design view of the form. But the field now
says #error
Any thoughts--
Lynette
Click to expand...

ksfireworksgal said:
Ken,

Where exactly is the Declarations area of a module? Do I get to it by going
into the design view of the form and then clicking on the code button. Or do
I need to create a new module?

As far as finding the boxes, they are assigned to tent numbers, but I do not
include the tent numbers in the repack detail list, I have a query that adds
them later. The reason I did this is the bosses want me to be able to do
several reports from each tent's total repacks, to each individual item
returned and quanity with a total value for accounting purposes.

Generally I take one tent location at a time and entry all of their repack
boxes. They are entered in a clean table called Repack Box Detail. Then I
run a quick report and verify all the entries. At this point I have only the
box number, item despcription, item no, quanity and packaing. After
corrections (if any) are made, I run the entries through the query that
appends them to Retail Box Append Table. They are added to the entries from
prievious locations. News fields are then included such as the price per
item, the value of each item, and prices of all five price levels. This seem
to be the only way I could get the calcualations to work. Then a delete
query is ran to clear out the Repack Box Detail table for the next location.
When this is completed, I open the Repack Box Detail Append Table and tab
through all the entries to get the calculations to figure.

Is there a way to search by entry date or time?
--
Lynette


:

Lynette:

As the PRICEOFITEM is computed from other values you don't need to have a
field for this in the table at all; you can use an unbound control in a form
or report. You just need to create a function in the form or report's
module, using your current code; you can simply paste the following code into
the form or report's module in its Declarations area, immediately below the
two lines already in place there. It will then be inserted as a new function
in the module. Note that you don't actually need to specify the Value
property of a control. As it’s the defaulkt property it can be omitted:

''''code begins''''
Private Function GetPriceOfItem() As Currency

If PRICELISTCODE= "JK" And PACKINGTYPE= "EACH" Then
PRICEOFITEM = JKEACHPRICE
ElseIf PRICELISTCODE= "JK" And PACKINGTYPE= "PACK" Then
PRICEOFITEM = JKPACKPRICE
If JKPACKPRICE= 0 Then
PRICEOFITEM = JKEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "JK" And PACKINGTYPE= "BOX" Then
PRICEOFITEM = JKBOXPRICE
If JKBOXPRICE = 0 Then
PRICEOFITEM = JKEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "JK" And PACKINGTYPE= "BRICK" Then
PRICEOFITEM = JKBRICKPRICE
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "EACH" Then
PRICEOFITEM = KSEACHPRICE
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "PACK" Then
PRICEOFITEM = KSPACKPRICE
If KSPACKPRICE= 0 Then
PRICEOFITEM = KSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "BOX" Then
PRICEOFITEM = KSBOXPRICE
If KSBOXPRICE= 0 Then
PRICEOFITEM = KSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "BRICK" Then
PRICEOFITEM = KSBRICKPRICE
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "EACH" Then
PRICEOFITEM = LAEACHPRICE
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "PACK" Then
PRICEOFITEM = LAPACKPRICE
If LAPACKPRICE= 0 Then
PRICEOFITEM = LAEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "BOX" Then
PRICEOFITEM = LABOXPRICE
If LABOXPRICE= 0 Then
PRICEOFITEM = LAEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "BRICK" Then
PRICEOFITEM = LABRICKPRICE
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "EACH" Then
PRICEOFITEM = WIEACHPRICE
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "PACK" Then
PRICEOFITEM = WIPACKPRICE
If WIPACKPRICE= 0 Then
PRICEOFITEM = WIEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "BOX" Then
PRICEOFITEM = WIBOXPRICE
If WIBOXPRICE= 0 Then
PRICEOFITEM = WIEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "BRICK" Then
PRICEOFITEM = WIBRICKPRICE
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "EACH" Then
PRICEOFITEM = WSEACHPRICE
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "PACK" Then
PRICEOFITEM = WSPACKPRICE
If WSPACKPRICE= 0 Then
PRICEOFITEM = WSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "BOX" Then
PRICEOFITEM = WSBOXPRICE
If WSBOXPRICE= 0 Then
PRICEOFITEM = WSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "BRICK" Then
PRICEOFITEM = WSBRICKPRICEElse
End If

End Function
''''code ends''''

After adding this function to the form or report's module, delete the code
for the PRICEOFITEM control's GotFocus event procedure and set its
ControlSource property to:

=GetPriceOfItem()

This control is now unbound, as the PRICEOFITEM field in the table no longer
exists, so it will automatically show the computed value without you having
to tab through the records.

For the Text43 control do the same, deleting the code for its GotFocus event
procedure and setting its ControlSource to:

=GetPriceOfItem() * QUANITYOFITEM
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

New Problem - I changed the PriceOfItem tag to GetPriceOfItem in the code,
now I get a complie error "member already exists in an object module from
which this object module derives."

The text box displays #Name?

What did I do wrong?

--
Lynette


Ken Sheridan said:
Lynette:

Mea culpa; I omitted to change the code so it set the returns value of the
function rather than assigns a value to the control. It should be:

''''code begins''''
Private Function GetPriceOfItem() As Currency

If PRICELISTCODE= "JK" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = JKEACHPRICE
ElseIf PRICELISTCODE= "JK" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = JKPACKPRICE
If JKPACKPRICE= 0 Then
GetPriceOfItem = JKEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "JK" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = JKBOXPRICE
If JKBOXPRICE = 0 Then
GetPriceOfItem = JKEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "JK" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = JKBRICKPRICE
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = KSEACHPRICE
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = KSPACKPRICE
If KSPACKPRICE= 0 Then
GetPriceOfItem = KSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = KSBOXPRICE
If KSBOXPRICE= 0 Then
GetPriceOfItem = KSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "KS" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = KSBRICKPRICE
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = LAEACHPRICE
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = LAPACKPRICE
If LAPACKPRICE= 0 Then
GetPriceOfItem = LAEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = LABOXPRICE
If LABOXPRICE= 0 Then
GetPriceOfItem = LAEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "LA" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = LABRICKPRICE
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = WIEACHPRICE
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = WIPACKPRICE
If WIPACKPRICE= 0 Then
GetPriceOfItem = WIEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = WIBOXPRICE
If WIBOXPRICE= 0 Then
GetPriceOfItem = WIEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WI" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = WIBRICKPRICE
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "EACH" Then
GetPriceOfItem = WSEACHPRICE
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "PACK" Then
GetPriceOfItem = WSPACKPRICE
If WSPACKPRICE= 0 Then
GetPriceOfItem = WSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "BOX" Then
GetPriceOfItem = WSBOXPRICE
If WSBOXPRICE= 0 Then
GetPriceOfItem = WSEACHPRICE * BOXCOUNT
End If
ElseIf PRICELISTCODE= "WS" And PACKINGTYPE= "BRICK" Then
GetPriceOfItem = WSBRICKPRICEElse
End If

End Function
''''code ends''''

You are right about clicking the Code button when in form design to open the
form's module. When it opens it should say Declarations in the top right
combo box. If not then select Declarations from the list. When you've
pasted the function in the window below the lines already in place it should
show the inserted function and the function name, GetPriceOfItem, will appear
in the combo box.

As regards searching by the entry date/time you could add a column,
DateTimeStamp say, of date/time data type to the table with its DefaultValue
property set to Now() in table design. When a new row is inserted into the
table the current date/time will automatically be inserted into the new row.

You can then search by date in a query with a criterion for the
DateTimeStamp column of, for instance:
= [Enter start date:]
Click to expand...

That way when you open the query or a form or report which uses it as its
RecordSource you'd be prompted to enter a date and only the rows inserted on
or after that date would be returned. If you include the time of day when
responding to the prompt only rows inserted n or after that time on the day
along with rows inserted at any time on subsequent days would be returned.

Or you could return all rows inserted in the current week for instance with
a criterion for the DateTimeStamp column of:
= Date() – Weekday(Date())+1
Click to expand...

This expression returns the Sunday previous to the current date, or the
current date if it is Sunday, so the criterion would return all rows inserted
= Date()
Click to expand...

Note that there are no date values as such in Access only date/time values.
A date without a time is actually midnight at the start of the day. That's
why its necessary to use >= rather than merely =. The latter would return
only those rows entered exactly on the dot of midnight, which is not going to
be many I'd guess!

Would something like the above help? If you need to pin them down by some
other date/time criterion let me know and I'll see if I can come up with a
suitable expression. You can for instance be prompted for start and end
dates, so you could return rows inserted on particular days in a range.

BTW I started learning to drive in the car park of a fireworks factory here
in England. My mother worked there and I used to practice in my Dad's car
while we were waiting for here to finish work.

Ken Sheridan
Stafford, England

ksfireworksgal said:
I have the code changed and set in the visual basic screen that appears when
you click the code button in the design view of the form. But the field now
says #error
Any thoughts--
Lynette
Click to expand...

ksfireworksgal said:
Ken,

Where exactly is the Declarations area of a module? Do I get to it by going
into the design view of the form and then clicking on the code button. Or do
I need to create a new module?

As far as finding the boxes, they are assigned to tent numbers, but I do not
include the tent numbers in the repack detail list, I have a query that adds
them later. The reason I did this is the bosses want me to be able to do
several reports from each tent's total repacks, to each individual item
returned and quanity with a total value for accounting purposes.

Generally I take one tent location at a time and entry all of their repack
boxes. They are entered in a clean table called Repack Box Detail. Then I
run a quick report and verify all the entries. At this point I have only the
box number, item despcription, item no, quanity and packaing. After
corrections (if any) are made, I run the entries through the query that
appends them to Retail Box Append Table. They are added to the entries from
prievious locations. News fields are then included such as the price per
item, the value of each item, and prices of all five price levels. This seem
to be the only way I could get the calcualations to work. Then a delete
query is ran to clear out the Repack Box Detail table for the next location.
When this is completed, I open the Repack Box Detail Append Table and tab
through all the entries to get the calculations to figure.

Is there a way to search by entry date or time?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help With If and Else If Statement 5
CODE 911 13
Complex calculations, passing value to tables, etc 3
Value From ComboBox On UserForm 3
Evaluate Range of Cell 2
Simple Code but slow 15
Reference is not valid 9
Trying to place 2 after update procedures in 1 combo box 1

Top