I am trying to get a price to list in a field of a form based on the value of
a field called PACKINGOFITEM - My code is:

Private Sub Text31_LostFocus()
If PACKINGOFITEM = "EACH" Then
Text31 = WIEACHPRICE
ElseIf PACKINGOFITEM = "PACK" Then
Text31 = WIPACKPRICE
ElseIf PACKINGOFITEM = "BOX" Then
Text31 = WIBOXPRICE
Else
End If
End Sub

Nothing appears in my field. I used an unbound text box for my field that I
want the information to go to. What am I missing?

Thank You
--
Lynette

Hi

You code should look something like this

If Me.PACKINGOFITEM = "Each" Or "Pack" Or "Box" Then
Me.Text31 = "WIEACHPRICE"
End If

This cuts yoru code down from this (which is what you code was)

Private Sub Text31_AfterUpdate()
If Me.PACKINGOFITEM = "Each" Then
Me.Text31 = "WIEACHPRICE"
ElseIf Me.PACKINGOFITEM = "PACK" Then
Me.Text31 = "WIPACKPRICE"
ElseIf Me.PACKINGOFITEM = "Box" Then
Me.Text31 = "WIPACKPRICE"
End If


BUT
It is not clear from your post what you are refering to. - What is
"WIEACHPRICE". In the code above I have assumed they are text. But you say
you want a price "list".

You will need to supply a little more information to get a better answer.


--
Wayne
Manchester, England.



"ksfireworksgal" wrote:

> I am trying to get a price to list in a field of a form based on the value of
> a field called PACKINGOFITEM - My code is:
>
> Private Sub Text31_LostFocus()
> If PACKINGOFITEM = "EACH" Then
> Text31 = WIEACHPRICE
> ElseIf PACKINGOFITEM = "PACK" Then
> Text31 = WIPACKPRICE
> ElseIf PACKINGOFITEM = "BOX" Then
> Text31 = WIBOXPRICE
> Else
> End If
> End Sub
>
> Nothing appears in my field. I used an unbound text box for my field that I
> want the information to go to. What am I missing?
>
> Thank You
> --
> Lynette

WIEACHPRICE = Wichita Each Price and is a currency value. Each of our items
may have once individual price or may have a individual price, pack price,
and box price. And each area we are located has a different price lever.
For example in Louisiana I have a LAEACHPRICE, LAPACKPRICE, LABOXPRICE, etc.
--
Lynette


"Wayne-I-M" wrote:

> Hi
>
> You code should look something like this
>
> If Me.PACKINGOFITEM = "Each" Or "Pack" Or "Box" Then
> Me.Text31 = "WIEACHPRICE"
> End If
>
> This cuts yoru code down from this (which is what you code was)
>
> Private Sub Text31_AfterUpdate()
> If Me.PACKINGOFITEM = "Each" Then
> Me.Text31 = "WIEACHPRICE"
> ElseIf Me.PACKINGOFITEM = "PACK" Then
> Me.Text31 = "WIPACKPRICE"
> ElseIf Me.PACKINGOFITEM = "Box" Then
> Me.Text31 = "WIPACKPRICE"
> End If
>
>
> BUT
> It is not clear from your post what you are refering to. - What is
> "WIEACHPRICE". In the code above I have assumed they are text. But you say
> you want a price "list".
>
> You will need to supply a little more information to get a better answer.
>
>
> --
> Wayne
> Manchester, England.
>
>
>
> "ksfireworksgal" wrote:
>
> > I am trying to get a price to list in a field of a form based on the value of
> > a field called PACKINGOFITEM - My code is:
> >
> > Private Sub Text31_LostFocus()
> > If PACKINGOFITEM = "EACH" Then
> > Text31 = WIEACHPRICE
> > ElseIf PACKINGOFITEM = "PACK" Then
> > Text31 = WIPACKPRICE
> > ElseIf PACKINGOFITEM = "BOX" Then
> > Text31 = WIBOXPRICE
> > Else
> > End If
> > End Sub
> >
> > Nothing appears in my field. I used an unbound text box for my field that I
> > want the information to go to. What am I missing?
> >
> > Thank You
> > --
> > Lynette

On Wed, 30 May 2007 07:08:01 -0700, ksfireworksgal
<(E-Mail Removed)> wrote:

>WIEACHPRICE = Wichita Each Price and is a currency value. Each of our items
>may have once individual price or may have a individual price, pack price,
>and box price. And each area we are located has a different price lever.
>For example in Louisiana I have a LAEACHPRICE, LAPACKPRICE, LABOXPRICE, etc.

WHOA! Are these the names of table fields? If so you are WAY off track.

For one thing, you can't just name a field in VBA code and pull data from it.
More importantly, if you are storing data (the locale) in fieldnames, you're
in for heaps of trouble.

Where are these currency values stored? What is the structure of your tables?

John W. Vinson [MVP]

i'm not sure that his design really is that bad

talk more about your design; this would be appropriate in certain
circumstances




"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:(E-Mail Removed)...
> On Wed, 30 May 2007 07:08:01 -0700, ksfireworksgal
> <(E-Mail Removed)> wrote:
>
>>WIEACHPRICE = Wichita Each Price and is a currency value. Each of our
>>items
>>may have once individual price or may have a individual price, pack price,
>>and box price. And each area we are located has a different price lever.
>>For example in Louisiana I have a LAEACHPRICE, LAPACKPRICE, LABOXPRICE,
>>etc.
>
> WHOA! Are these the names of table fields? If so you are WAY off track.
>
> For one thing, you can't just name a field in VBA code and pull data from
> it.
> More importantly, if you are storing data (the locale) in fieldnames,
> you're
> in for heaps of trouble.
>
> Where are these currency values stored? What is the structure of your
> tables?
>
> John W. Vinson [MVP]

I have a master database where the tent# and locations are stored . The next
data base for repacks has a linked table from the 1st - as to reduce
duplicating changes with the locations as they can fluctuate from year to
year.

In the data base I am working on I have A price list table with has the
prices of four levels and in each level their is a possiblity of four prices
per item- as we can not sell a box of items asbox if they have been broken
into individuals. I am sure you have been to a fireworks tent and seen the
novelty items in boxes, or as individuals.

I then have a table that assigns the box number (repack sticker bar code) to
each tent. Each tent may have 1 return box or dozens. Some of our buildings
return close to a 100 boxes each season.

I then have a table where I enter the detail (contents) of each box. Each
box may have a few as 1 item or dozens of items. This table's fields are the
box # (which must match a box number listed in the assigned box number table
I just mentioned) this is to avoid incorrect box numbers from being entered.

Then there is an item description field (this field is a look up from
another table named products master table that includes barcodes, item
descriptions, brand names etc.). The next field is an autofill find that
brings up the item number based on the product master table and what is
entered in the product description field.

The next field is the item quanity (how many are being returned). The next
field of this table is the item packaging (how is it being returned).

Once all of the box details are entered I quirey the Box Detail Table and
Price List Master Table so that I can match the price level (i have a
separate table to determine which code = which price level and assign the
code to each tent).

My obstacle at this point is the best way to get the correct price for each
item so that I can take that price and multiply it by the quanity returned.
My boss wants the report to look something like this:

#8 Assorted Cones 45 Each $3.95 $177.75
Jumping Jacks 14 Packs $3.90 $ 54.60
Jumping Jacks 97 Each $0.20 $ 19.40

And I need to be able to sort the repacks by tent, box number, or item
description.

The prices WILL NOT CHANGE - because the prices are already stickered to the
product the next season we will sell it for what the sticker says as our
operators will not have the time to reprice all the items. So box 567980
whose total value is $245.67 will stay $245.67 next year. Once box 567980 is
opened next year and the product put in another tent it will essentailly
cease to exsist.

As you can imagine - I am pulling my hair out. I have some ACCESS skills,
and am learning programming, but alot of what I have accomplished is by
reading two books I have, asking questions on this web site, reading others
questions, and mostly trial and error. So any help is appreicated.
--
Lynette


"Tom Wimpernark" wrote:

> i'm not sure that his design really is that bad
>
> talk more about your design; this would be appropriate in certain
> circumstances
>
>
>
>
> "John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
> news:(E-Mail Removed)...
> > On Wed, 30 May 2007 07:08:01 -0700, ksfireworksgal
> > <(E-Mail Removed)> wrote:
> >
> >>WIEACHPRICE = Wichita Each Price and is a currency value. Each of our
> >>items
> >>may have once individual price or may have a individual price, pack price,
> >>and box price. And each area we are located has a different price lever.
> >>For example in Louisiana I have a LAEACHPRICE, LAPACKPRICE, LABOXPRICE,
> >>etc.
> >
> > WHOA! Are these the names of table fields? If so you are WAY off track.
> >
> > For one thing, you can't just name a field in VBA code and pull data from
> > it.
> > More importantly, if you are storing data (the locale) in fieldnames,
> > you're
> > in for heaps of trouble.
> >
> > Where are these currency values stored? What is the structure of your
> > tables?
> >
> > John W. Vinson [MVP]
>
>

Why would naming the fields like this be off track? I am giving each field
the name of what it is for example

WIEACHPRICE for ITEM #8 Cones would 3.79
WIEACHPRICE for ITEM Jumping Jacks would be .39
WIEACHPRICE for ITEM Dymand Duster would be 59.99

were as

LAEACHPRICE for ITEM #8 Cones would be 3.99
LAEACHPRICE for ITEM Jumping Jacks would be .59
LAEACHPRICE for ITEM Dymand Duster would be 69.95

Is there a better way to keep track of different priced levels that may have
up to 4 prices per each item per level? I will take any suggestions to make
this easier.
--
Lynette


"John W. Vinson" wrote:

> On Wed, 30 May 2007 07:08:01 -0700, ksfireworksgal
> <(E-Mail Removed)> wrote:
>
> >WIEACHPRICE = Wichita Each Price and is a currency value. Each of our items
> >may have once individual price or may have a individual price, pack price,
> >and box price. And each area we are located has a different price lever.
> >For example in Louisiana I have a LAEACHPRICE, LAPACKPRICE, LABOXPRICE, etc.
>
> WHOA! Are these the names of table fields? If so you are WAY off track.
>
> For one thing, you can't just name a field in VBA code and pull data from it.
> More importantly, if you are storing data (the locale) in fieldnames, you're
> in for heaps of trouble.
>
> Where are these currency values stored? What is the structure of your tables?
>
> John W. Vinson [MVP]
>

On Wed, 30 May 2007 14:50:02 -0700, ksfireworksgal
<(E-Mail Removed)> wrote:

>Why would naming the fields like this be off track? I am giving each field
>the name of what it is for example
>
>WIEACHPRICE for ITEM #8 Cones would 3.79
>WIEACHPRICE for ITEM Jumping Jacks would be .39
>WIEACHPRICE for ITEM Dymand Duster would be 59.99
>
>were as
>
>LAEACHPRICE for ITEM #8 Cones would be 3.99
>LAEACHPRICE for ITEM Jumping Jacks would be .59
>LAEACHPRICE for ITEM Dymand Duster would be 69.95
>
>Is there a better way to keep track of different priced levels that may have
>up to 4 prices per each item per level? I will take any suggestions to make
>this easier.

If you have a field for each location, you're "committing spreadsheet". What
do you do if you need to add a new location? or remove one? or one large area
location splits into two? Redesign your table, all your queries, all your
forms, all your reports? OUCH!

If each Location has many Items, and each Item can be sold in many Locations,
you have a many to many relationship. The proper way to handle this is to use
three tables:

Items
ItemID <Primary Key>
Description
<other fields about the item as an entity>

Locations
LocationID <Primary Key>
Location <text, e.g. Wichita, Louisiana, Tahiti>

Prices
ItemID <link to Items>
LocationID <link to Locations>
Price <the price of this item at this location>

and perhaps also an EffectiveDate and ThroughDate field so that you can keep a
historical record of what prices were in effect at what time.


John W. Vinson [MVP]

But that is what I have done. I have a separate table as follows:

Locations Master - in a separate database with other items but table is
linked in this data base

Prodcut Master - each item, description, case count, etc.

Price Master - look up field for item #, then each price level with each
packing level price.

Please explain more about the effective dates, as that is one of my next
obstacles, write now I am looking at creating a data base for each year - I
don't like that idea -to much can get lost in translation so to speak.

Thanks for the help
--
Lynette


"John W. Vinson" wrote:

> On Wed, 30 May 2007 14:50:02 -0700, ksfireworksgal
> <(E-Mail Removed)> wrote:
>
> >Why would naming the fields like this be off track? I am giving each field
> >the name of what it is for example
> >
> >WIEACHPRICE for ITEM #8 Cones would 3.79
> >WIEACHPRICE for ITEM Jumping Jacks would be .39
> >WIEACHPRICE for ITEM Dymand Duster would be 59.99
> >
> >were as
> >
> >LAEACHPRICE for ITEM #8 Cones would be 3.99
> >LAEACHPRICE for ITEM Jumping Jacks would be .59
> >LAEACHPRICE for ITEM Dymand Duster would be 69.95
> >
> >Is there a better way to keep track of different priced levels that may have
> >up to 4 prices per each item per level? I will take any suggestions to make
> >this easier.
>
> If you have a field for each location, you're "committing spreadsheet". What
> do you do if you need to add a new location? or remove one? or one large area
> location splits into two? Redesign your table, all your queries, all your
> forms, all your reports? OUCH!
>
> If each Location has many Items, and each Item can be sold in many Locations,
> you have a many to many relationship. The proper way to handle this is to use
> three tables:
>
> Items
> ItemID <Primary Key>
> Description
> <other fields about the item as an entity>
>
> Locations
> LocationID <Primary Key>
> Location <text, e.g. Wichita, Louisiana, Tahiti>
>
> Prices
> ItemID <link to Items>
> LocationID <link to Locations>
> Price <the price of this item at this location>
>
> and perhaps also an EffectiveDate and ThroughDate field so that you can keep a
> historical record of what prices were in effect at what time.
>
>
> John W. Vinson [MVP]
>

On Thu, 31 May 2007 07:40:02 -0700, ksfireworksgal
<(E-Mail Removed)> wrote:

>But that is what I have done. I have a separate table as follows:
>
>Locations Master - in a separate database with other items but table is
>linked in this data base
>
>Prodcut Master - each item, description, case count, etc.
>
>Price Master - look up field for item #, then each price level with each
>packing level price.

In your previous message you said:

=====
Why would naming the fields like this be off track? I am giving each field
the name of what it is for example

WIEACHPRICE for ITEM #8 Cones would 3.79
WIEACHPRICE for ITEM Jumping Jacks would be .39
WIEACHPRICE for ITEM Dymand Duster would be 59.99

were as

LAEACHPRICE for ITEM #8 Cones would be 3.99
LAEACHPRICE for ITEM Jumping Jacks would be .59
LAEACHPRICE for ITEM Dymand Duster would be 69.95
====

What I'm suggesting is quite different. Rather than a *FIELD* named
WIEACHPRICE, you would have a *separate record* for the Wichita price. E.g.

Product Master
123 #8 Cones
314 Jumping Jacks
225 Dymand Duster

Locations Master
2 Wichita
3 Louisiana
5 West Texas

Pricelist
ProductID Location Price EffectiveDate EndDate
123 2 3.79 8/1/2006 <null>
123 3 3.79 8/1/2006 10/14/2006
123 3 3.99 10/14/2006 <null>
225 2 59.99 5/12/2007 <null>

In this example the price of #8 Cones (item 123) went to $3.79 on 8/1/06 in
Wichita, and since the end date is null it's still at that price; in Louisian
it also was at $3.79 up until October 14, when it went to $3.99 where it is
now.

You can use a Query to find the most current price for any item in any
location from this table; you can use another query to find what the price was
as of September 1, 2006 using date ranges; etc.

>Please explain more about the effective dates, as that is one of my next
>obstacles, write now I am looking at creating a data base for each year - I
>don't like that idea -to much can get lost in translation so to speak.

Storing data (date ranges) in a database name is even *worse* than storing it
in fieldnames - so your aversion to this idea is well founded!

Am I misunderstanding your actual structure? What are the field definitions of
Locations Master and Price Master in your current database?

John W. Vinson [MVP]