Update Query only works first time

Hi Jacine,

You should be able to change the value in the Bins subform directly.

you said, "When I run the query from the actual quantity to the current
quantity - it updates all quantities if there are any other bin
quanitites on any other records."

What is the SQL for your query?
Go to the design view of your query and from the menu, choose, View,
SQL... then copy the statement and paste into a reply

Also, what is triggering the query to execute? What code are you using
to execute it?

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Jacine wrote:
The record source of the main form is the part and supplies table.

The record source of the subform is the Bins.

They won't be keeping track of previous inventory counts. That is why I kept
the bin quantities in the Bins table, however, if you think they should be
separate, then I will do so. They will be doing inventory counts on some of
the parts - not all as some parts will not be stocked in inventory. They may
only do certain counts, i.e. in one city at one time and another city at
another time - this could be sporadic. The bins are identified by city by
using a letter to identify therefore the bin labels are alphanumeric.

That is why I need to have the command button I added to the form to only
update the current form. Is this possible to do - the problem I am
encountering is it updates all the records if there are any amounts entered.

They will be using a "real" inventory software in a few years so I want to
do this right since it will be taken from the Access program.

Thank you.

"strive4peace" wrote:

Hi Jacine,

what is the RecordSource for your main and subforms?

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Jacine wrote:
I have set up a new form based on the two tables, and it seems to work quite
well. (Form and subform)

The only problem I have is how do I get it to update just the bin quantities
on the current form.

When I run the query from the actual quantity to the current quantity - it
updates all quantities if there are any other bin quanitites on any other
records.

Thank you.

"strive4peace" "strive4peace2006 at yaho" wrote:

ps

in your Bins table, I admit to copying and pasting and not
changing enough...

the second BinNum should be BinName
(since Name is a reserved word, you never want to use it
without qualifying it)

BinName* -- don't know data type since this is how you label
your bins


*or BinLabel, or even BinNum, in which case, if you want
Access to number bins, you would have to change the previous
fieldname to somthing different like BinNbr. If something
is REALLY a number as opposed to text containing numbers, it
is a good habit to be consistent with the abbreviations that
you use, whatever you choose them to be. For instance, No
and Nbr could refer to fields that are of numeric data type
and Num could refer to a field that is stored as text.


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

strive4peace wrote:
Hi Jacine,

I will assume you have an autonumber field in your Parts and Supplies
table that I will refer to as PartID.

To make it easier to work with, you should consider renaming

Parts and Supplies -- Parts ( or PartsSupplies)

you should not use spaces in table or fieldnames or any names, for that
matter. It is best to use letters, numbers (but don't ever start with a
number) and underscores... no special characters like %, $, etc -- they
can cause problems.

Rather than making 6 Bin fields in that table, what you should do is set
up another table for Bins

*Bins*
BinID, Autonumber
PartID, Long Integer -- correlates to primary key of Parts
Qty, Long Integer (unless you will have fractions in a bin)
BinNum, integer -- if you want to number the bins for a part such as
1,2,3... although it would not be necessary
BinNum -- don't know data type since this is how you label your bins
LocID, Long Integer -- corresponds to Locations table

then, if you have multiple locations where a Bin can be located, set up
a Locations Table:

*Locations*
LocID, Autonumber
Location, text
etc

This will make it MUCH easier for you to take inventory and add up all
the quantities in the different Bins for a particular Part/Supply.
Also, if you end up using more than 6 Bins for a Part, you don't have to
redesign the structure

"...update just the current form..." -- no, you update fields, not
forms. ALL data is stored in fields that are in tables -- forms just
provide a more convenient way to keep track of data in your tables.

Queries give you a way to combine, sort, and filter data from Tables.

BEFORE you spend any more time with forms, redesign the way your tables
are laid out. Think of it like this: each table is a noun (part, bin,
location, etc) and the fields are the adjectives that describe that noun.

You will also need some kind of Transactions table so you can track when
parts are added or removed from the bins.

Your data structure is like the foundation for a building. Making it
sturdy and strong determines how high you can build.

After you put some more thought into your table design, post back with
what your tables/fields are and we can give you feedback.



Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Jacine wrote:

Parts and Supplies is not a query it is the table in which the Bin
Locations also resides.

There are over 4500 parts/supplies in which each Part could have
several bin locations.

It is a pretty complicated database and I am a little over my head.
They want to be able to update the quantities in each bin location by
doing an actual/physical count. I don't know if access is even able
to do all of this.

From the same table, there is a Parts and Supplies form where they can
add new parts and supplies. From the same table, there is an
inventory control form where some of the same information comes from
the table as in the form they add new parts to, but there is inventory
information such as minimums, maximums, purchase order last ordered on
and they want to keep this information separate as they don't want all
users to access. On the inventory control form it has the Bin
Locations and the Units in each Bin.

They want another field for an actual physical count they will do
several times per year and they just want to be able to update the
Units in each bin from that actual count.

I think my problem is I need to have it update just the current form
that I am trying to update. Is this possible - to have it just update
the current form I am updating that has to do with that Part/Supply
number? The part/supply number is the primary key for that table.

Hope this makes more sense.... I am looking forward to finishing this.

"strive4peace" "strive4peace2006 at yaho" wrote:


Hi Jacine,

I don't know either -- but that is what you error message says --
perhaps you are not using an updateable recordset or records were
locked by something else like a form or query

in taking a better look at your SQL, it appears that your data is not
normalized

UPDATE [Parts and Supplies] SET
UnitsBinLocation1 = [ActualQuantityBin1],
UnitsBinLocation2 = [ActualQuantityBin2],
UnitsBinLocation3 = [ActualQuantityBin3],
UnitsBinLocation4 = [ActualQuantityBin4],
UnitsBinLocation5 = [ActualQuantityBin5],
UnitsBinLocation6 = [ActualQuantityBin6];

You should have a seperate table for BinLocation. It would be linked
on PartID and could have 1, 6, or as many related records as you like.

"It also give me a message that you are about to
update 4500 records which I don't understand since it only updates
that particular part or supply."

is [Parts and Supplies] a query? What is its SQL?

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Jacine wrote:

No - I am the only one. I am still building the database. I went
over all the fields in the table and there is nothing set up
incorrectly - I don't know where the 35 records are coming from.

"strive4peace" "strive4peace2006 at yaho" wrote:



Hi Jacine,

are other people using it? What would be causing the database to
lock 35 records?

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Jacine wrote:


I have a command button placed on the form to run the query. The
form is built from a large parts table. This parts table is also
used for several other forms for auto lookup and auto fill and
another form as well. I'm not sure what you mean by another process.

Thank you.

"strive4peace" "strive4peace2006 at yaho" wrote:




Hi Jacine,

how are you executing your queries?

your error message indicates that 35 records are locked, which is
why they cannot be updated -- do you have another process which
has these records tied up?

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Jacine wrote:



Hello,

Thank you for the answer, but I am not sure where to place this
code. Could you provide more information?

Here is the error message I receive:

Microsoft Access did not update 0 field(s) due to a type
conversion failure, 0 record(s) due to key violations, 35
record(s) due to lock violations, and 0 Record(s) due to
validation rule violations.

This occurs after I have run the Query the first time.

Here is the SQL text of the query as well:

UPDATE [Parts and Supplies] SET [Parts and
Supplies].UnitsBinLocation1 = ([ActualQuantityBin1]), [Parts and
Supplies].UnitsBinLocation2 = ([ActualQuantityBin2]), [Parts and
Supplies].UnitsBinLocation3 = ([ActualQuantityBin3]), [Parts and
Supplies].UnitsBinLocation4 = ([ActualQuantityBin4]), [Parts and

Hi Crystal,

Here is the SQL text to Update the Quantities from the Actual/Physical Count:


UPDATE BinLocations SET BinLocations.BinQuantity = ([BinPhysicalCount]);

and, here is the SQL text to Reset the Actual/Physical Count back to zero:

UPDATE BinLocations SET BinLocations.BinPhysicalCount = 0;

As mentioned, it does work, but it updates all records if there is a
quantity instead of just updated the current record.

It is also very, very slow.

Thanks again.



"strive4peace" wrote:

Hi Jacine,

You should be able to change the value in the Bins subform directly.

you said, "When I run the query from the actual quantity to the current
quantity - it updates all quantities if there are any other bin
quanitites on any other records."

What is the SQL for your query?
Go to the design view of your query and from the menu, choose, View,
SQL... then copy the statement and paste into a reply

Also, what is triggering the query to execute? What code are you using
to execute it?

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Jacine wrote:
The record source of the main form is the part and supplies table.

The record source of the subform is the Bins.

They won't be keeping track of previous inventory counts. That is why I kept
the bin quantities in the Bins table, however, if you think they should be
separate, then I will do so. They will be doing inventory counts on some of
the parts - not all as some parts will not be stocked in inventory. They may
only do certain counts, i.e. in one city at one time and another city at
another time - this could be sporadic. The bins are identified by city by
using a letter to identify therefore the bin labels are alphanumeric.

That is why I need to have the command button I added to the form to only
update the current form. Is this possible to do - the problem I am
encountering is it updates all the records if there are any amounts entered.

They will be using a "real" inventory software in a few years so I want to
do this right since it will be taken from the Access program.

Thank you.

"strive4peace" wrote:

Hi Jacine,

what is the RecordSource for your main and subforms?

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Jacine wrote:
I have set up a new form based on the two tables, and it seems to work quite
well. (Form and subform)

The only problem I have is how do I get it to update just the bin quantities
on the current form.

When I run the query from the actual quantity to the current quantity - it
updates all quantities if there are any other bin quanitites on any other
records.

Thank you.

"strive4peace" "strive4peace2006 at yaho" wrote:

ps

in your Bins table, I admit to copying and pasting and not
changing enough...

the second BinNum should be BinName
(since Name is a reserved word, you never want to use it
without qualifying it)

BinName* -- don't know data type since this is how you label
your bins


*or BinLabel, or even BinNum, in which case, if you want
Access to number bins, you would have to change the previous
fieldname to somthing different like BinNbr. If something
is REALLY a number as opposed to text containing numbers, it
is a good habit to be consistent with the abbreviations that
you use, whatever you choose them to be. For instance, No
and Nbr could refer to fields that are of numeric data type
and Num could refer to a field that is stored as text.


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

strive4peace wrote:
Hi Jacine,

I will assume you have an autonumber field in your Parts and Supplies
table that I will refer to as PartID.

To make it easier to work with, you should consider renaming

Parts and Supplies -- Parts ( or PartsSupplies)

you should not use spaces in table or fieldnames or any names, for that
matter. It is best to use letters, numbers (but don't ever start with a
number) and underscores... no special characters like %, $, etc -- they
can cause problems.

Rather than making 6 Bin fields in that table, what you should do is set
up another table for Bins

*Bins*
BinID, Autonumber
PartID, Long Integer -- correlates to primary key of Parts
Qty, Long Integer (unless you will have fractions in a bin)
BinNum, integer -- if you want to number the bins for a part such as
1,2,3... although it would not be necessary
BinNum -- don't know data type since this is how you label your bins
LocID, Long Integer -- corresponds to Locations table

then, if you have multiple locations where a Bin can be located, set up
a Locations Table:

*Locations*
LocID, Autonumber
Location, text
etc

This will make it MUCH easier for you to take inventory and add up all
the quantities in the different Bins for a particular Part/Supply.
Also, if you end up using more than 6 Bins for a Part, you don't have to
redesign the structure

"...update just the current form..." -- no, you update fields, not
forms. ALL data is stored in fields that are in tables -- forms just
provide a more convenient way to keep track of data in your tables.

Queries give you a way to combine, sort, and filter data from Tables.

BEFORE you spend any more time with forms, redesign the way your tables
are laid out. Think of it like this: each table is a noun (part, bin,
location, etc) and the fields are the adjectives that describe that noun.

You will also need some kind of Transactions table so you can track when
parts are added or removed from the bins.

Your data structure is like the foundation for a building. Making it
sturdy and strong determines how high you can build.

After you put some more thought into your table design, post back with
what your tables/fields are and we can give you feedback.



Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Jacine wrote:

Parts and Supplies is not a query it is the table in which the Bin
Locations also resides.

There are over 4500 parts/supplies in which each Part could have
several bin locations.

It is a pretty complicated database and I am a little over my head.
They want to be able to update the quantities in each bin location by
doing an actual/physical count. I don't know if access is even able
to do all of this.

From the same table, there is a Parts and Supplies form where they can
add new parts and supplies. From the same table, there is an
inventory control form where some of the same information comes from
the table as in the form they add new parts to, but there is inventory
information such as minimums, maximums, purchase order last ordered on
and they want to keep this information separate as they don't want all
users to access. On the inventory control form it has the Bin
Locations and the Units in each Bin.

They want another field for an actual physical count they will do
several times per year and they just want to be able to update the
Units in each bin from that actual count.

I think my problem is I need to have it update just the current form
that I am trying to update. Is this possible - to have it just update
the current form I am updating that has to do with that Part/Supply
number? The part/supply number is the primary key for that table.

Hope this makes more sense.... I am looking forward to finishing this.

"strive4peace" "strive4peace2006 at yaho" wrote:


Hi Jacine,

I don't know either -- but that is what you error message says --
perhaps you are not using an updateable recordset or records were
locked by something else like a form or query

in taking a better look at your SQL, it appears that your data is not
normalized

UPDATE [Parts and Supplies] SET
UnitsBinLocation1 = [ActualQuantityBin1],
UnitsBinLocation2 = [ActualQuantityBin2],
UnitsBinLocation3 = [ActualQuantityBin3],
UnitsBinLocation4 = [ActualQuantityBin4],
UnitsBinLocation5 = [ActualQuantityBin5],
UnitsBinLocation6 = [ActualQuantityBin6];

You should have a seperate table for BinLocation. It would be linked
on PartID and could have 1, 6, or as many related records as you like.

"It also give me a message that you are about to
update 4500 records which I don't understand since it only updates
that particular part or supply."

is [Parts and Supplies] a query? What is its SQL?

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Jacine wrote:

No - I am the only one. I am still building the database. I went
over all the fields in the table and there is nothing set up
incorrectly - I don't know where the 35 records are coming from.

"strive4peace" "strive4peace2006 at yaho" wrote:



Hi Jacine,

are other people using it? What would be causing the database to
lock 35 records?

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Jacine wrote:


I have a command button placed on the form to run the query. The
form is built from a large parts table. This parts table is also
used for several other forms for auto lookup and auto fill and
another form as well. I'm not sure what you mean by another process.

Thank you.

Hi Jacine,

in order to get this to act on just the current record, you need a WHERE
clause...

but why are you storing the same information in 2 different fields? If
BinPhysicalCount is not a field in your table, but a control on your
form, why not simply use BinQuantity as your ControlSource?


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Jacine wrote:
Hi Crystal,

Here is the SQL text to Update the Quantities from the Actual/Physical Count:


UPDATE BinLocations SET BinLocations.BinQuantity = ([BinPhysicalCount]);

and, here is the SQL text to Reset the Actual/Physical Count back to zero:

UPDATE BinLocations SET BinLocations.BinPhysicalCount = 0;

As mentioned, it does work, but it updates all records if there is a
quantity instead of just updated the current record.

It is also very, very slow.

Thanks again.



"strive4peace" wrote:

Hi Jacine,

You should be able to change the value in the Bins subform directly.

you said, "When I run the query from the actual quantity to the current
quantity - it updates all quantities if there are any other bin
quanitites on any other records."

What is the SQL for your query?
Go to the design view of your query and from the menu, choose, View,
SQL... then copy the statement and paste into a reply

Also, what is triggering the query to execute? What code are you using
to execute it?

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Jacine wrote:
The record source of the main form is the part and supplies table.

The record source of the subform is the Bins.

They won't be keeping track of previous inventory counts. That is why I kept
the bin quantities in the Bins table, however, if you think they should be
separate, then I will do so. They will be doing inventory counts on some of
the parts - not all as some parts will not be stocked in inventory. They may
only do certain counts, i.e. in one city at one time and another city at
another time - this could be sporadic. The bins are identified by city by
using a letter to identify therefore the bin labels are alphanumeric.

That is why I need to have the command button I added to the form to only
update the current form. Is this possible to do - the problem I am
encountering is it updates all the records if there are any amounts entered.

They will be using a "real" inventory software in a few years so I want to
do this right since it will be taken from the Access program.

Thank you.

"strive4peace" wrote:

Hi Jacine,

what is the RecordSource for your main and subforms?

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Jacine wrote:
I have set up a new form based on the two tables, and it seems to work quite
well. (Form and subform)

The only problem I have is how do I get it to update just the bin quantities
on the current form.

When I run the query from the actual quantity to the current quantity - it
updates all quantities if there are any other bin quanitites on any other
records.

Thank you.

"strive4peace" "strive4peace2006 at yaho" wrote:

ps

in your Bins table, I admit to copying and pasting and not
changing enough...

the second BinNum should be BinName
(since Name is a reserved word, you never want to use it
without qualifying it)

BinName* -- don't know data type since this is how you label
your bins


*or BinLabel, or even BinNum, in which case, if you want
Access to number bins, you would have to change the previous
fieldname to somthing different like BinNbr. If something
is REALLY a number as opposed to text containing numbers, it
is a good habit to be consistent with the abbreviations that
you use, whatever you choose them to be. For instance, No
and Nbr could refer to fields that are of numeric data type
and Num could refer to a field that is stored as text.


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

strive4peace wrote:
Hi Jacine,

I will assume you have an autonumber field in your Parts and Supplies
table that I will refer to as PartID.

To make it easier to work with, you should consider renaming

Parts and Supplies -- Parts ( or PartsSupplies)

you should not use spaces in table or fieldnames or any names, for that
matter. It is best to use letters, numbers (but don't ever start with a
number) and underscores... no special characters like %, $, etc -- they
can cause problems.

Rather than making 6 Bin fields in that table, what you should do is set
up another table for Bins

*Bins*
BinID, Autonumber
PartID, Long Integer -- correlates to primary key of Parts
Qty, Long Integer (unless you will have fractions in a bin)
BinNum, integer -- if you want to number the bins for a part such as
1,2,3... although it would not be necessary
BinNum -- don't know data type since this is how you label your bins
LocID, Long Integer -- corresponds to Locations table

then, if you have multiple locations where a Bin can be located, set up
a Locations Table:

*Locations*
LocID, Autonumber
Location, text
etc

This will make it MUCH easier for you to take inventory and add up all
the quantities in the different Bins for a particular Part/Supply.
Also, if you end up using more than 6 Bins for a Part, you don't have to
redesign the structure

"...update just the current form..." -- no, you update fields, not
forms. ALL data is stored in fields that are in tables -- forms just
provide a more convenient way to keep track of data in your tables.

Queries give you a way to combine, sort, and filter data from Tables.

BEFORE you spend any more time with forms, redesign the way your tables
are laid out. Think of it like this: each table is a noun (part, bin,
location, etc) and the fields are the adjectives that describe that noun.

You will also need some kind of Transactions table so you can track when
parts are added or removed from the bins.

Your data structure is like the foundation for a building. Making it
sturdy and strong determines how high you can build.

After you put some more thought into your table design, post back with
what your tables/fields are and we can give you feedback.



Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Jacine wrote:

Parts and Supplies is not a query it is the table in which the Bin
Locations also resides.

There are over 4500 parts/supplies in which each Part could have
several bin locations.

It is a pretty complicated database and I am a little over my head.
They want to be able to update the quantities in each bin location by
doing an actual/physical count. I don't know if access is even able
to do all of this.

From the same table, there is a Parts and Supplies form where they can
add new parts and supplies. From the same table, there is an
inventory control form where some of the same information comes from
the table as in the form they add new parts to, but there is inventory
information such as minimums, maximums, purchase order last ordered on
and they want to keep this information separate as they don't want all
users to access. On the inventory control form it has the Bin
Locations and the Units in each Bin.

They want another field for an actual physical count they will do
several times per year and they just want to be able to update the
Units in each bin from that actual count.

I think my problem is I need to have it update just the current form
that I am trying to update. Is this possible - to have it just update
the current form I am updating that has to do with that Part/Supply
number? The part/supply number is the primary key for that table.

Hope this makes more sense.... I am looking forward to finishing this.

"strive4peace" "strive4peace2006 at yaho" wrote:


Hi Jacine,

I don't know either -- but that is what you error message says --
perhaps you are not using an updateable recordset or records were
locked by something else like a form or query

in taking a better look at your SQL, it appears that your data is not
normalized

UPDATE [Parts and Supplies] SET
UnitsBinLocation1 = [ActualQuantityBin1],
UnitsBinLocation2 = [ActualQuantityBin2],
UnitsBinLocation3 = [ActualQuantityBin3],
UnitsBinLocation4 = [ActualQuantityBin4],
UnitsBinLocation5 = [ActualQuantityBin5],
UnitsBinLocation6 = [ActualQuantityBin6];

You should have a seperate table for BinLocation. It would be linked
on PartID and could have 1, 6, or as many related records as you like.

"It also give me a message that you are about to
update 4500 records which I don't understand since it only updates
that particular part or supply."

is [Parts and Supplies] a query? What is its SQL?

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Jacine wrote:

No - I am the only one. I am still building the database. I went
over all the fields in the table and there is nothing set up
incorrectly - I don't know where the 35 records are coming from.

"strive4peace" "strive4peace2006 at yaho" wrote:



Hi Jacine,

are other people using it? What would be causing the database to
lock 35 records?

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Jacine wrote:


I have a command button placed on the form to run the query. The
form is built from a large parts table. This parts table is also
used for several other forms for auto lookup and auto fill and
another form as well. I'm not sure what you mean by another process.

Thank you.

It is not the same information.

The inventory amount in each bin is what is there from the last count.

They are using the Actual Physical Count for when they do a physical count
of the inventory and just want to update what was there before the count.

I told them all they have to do is type over/re-enter the amounts but for
some reason they want to see the difference before changing the information.

"strive4peace" wrote:

Hi Jacine,

in order to get this to act on just the current record, you need a WHERE
clause...

but why are you storing the same information in 2 different fields? If
BinPhysicalCount is not a field in your table, but a control on your
form, why not simply use BinQuantity as your ControlSource?


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Jacine wrote:
Hi Crystal,

Here is the SQL text to Update the Quantities from the Actual/Physical Count:


UPDATE BinLocations SET BinLocations.BinQuantity = ([BinPhysicalCount]);

and, here is the SQL text to Reset the Actual/Physical Count back to zero:

UPDATE BinLocations SET BinLocations.BinPhysicalCount = 0;

As mentioned, it does work, but it updates all records if there is a
quantity instead of just updated the current record.

It is also very, very slow.

Thanks again.



"strive4peace" wrote:

Hi Jacine,

You should be able to change the value in the Bins subform directly.

you said, "When I run the query from the actual quantity to the current
quantity - it updates all quantities if there are any other bin
quanitites on any other records."

What is the SQL for your query?
Go to the design view of your query and from the menu, choose, View,
SQL... then copy the statement and paste into a reply

Also, what is triggering the query to execute? What code are you using
to execute it?

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Jacine wrote:
The record source of the main form is the part and supplies table.

The record source of the subform is the Bins.

They won't be keeping track of previous inventory counts. That is why I kept
the bin quantities in the Bins table, however, if you think they should be
separate, then I will do so. They will be doing inventory counts on some of
the parts - not all as some parts will not be stocked in inventory. They may
only do certain counts, i.e. in one city at one time and another city at
another time - this could be sporadic. The bins are identified by city by
using a letter to identify therefore the bin labels are alphanumeric.

That is why I need to have the command button I added to the form to only
update the current form. Is this possible to do - the problem I am
encountering is it updates all the records if there are any amounts entered.

They will be using a "real" inventory software in a few years so I want to
do this right since it will be taken from the Access program.

Thank you.

"strive4peace" wrote:

Hi Jacine,

what is the RecordSource for your main and subforms?

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Jacine wrote:
I have set up a new form based on the two tables, and it seems to work quite
well. (Form and subform)

The only problem I have is how do I get it to update just the bin quantities
on the current form.

When I run the query from the actual quantity to the current quantity - it
updates all quantities if there are any other bin quanitites on any other
records.

Thank you.

"strive4peace" "strive4peace2006 at yaho" wrote:

ps

in your Bins table, I admit to copying and pasting and not
changing enough...

the second BinNum should be BinName
(since Name is a reserved word, you never want to use it
without qualifying it)

BinName* -- don't know data type since this is how you label
your bins


*or BinLabel, or even BinNum, in which case, if you want
Access to number bins, you would have to change the previous
fieldname to somthing different like BinNbr. If something
is REALLY a number as opposed to text containing numbers, it
is a good habit to be consistent with the abbreviations that
you use, whatever you choose them to be. For instance, No
and Nbr could refer to fields that are of numeric data type
and Num could refer to a field that is stored as text.


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

strive4peace wrote:
Hi Jacine,

I will assume you have an autonumber field in your Parts and Supplies
table that I will refer to as PartID.

To make it easier to work with, you should consider renaming

Parts and Supplies -- Parts ( or PartsSupplies)

you should not use spaces in table or fieldnames or any names, for that
matter. It is best to use letters, numbers (but don't ever start with a
number) and underscores... no special characters like %, $, etc -- they
can cause problems.

Rather than making 6 Bin fields in that table, what you should do is set
up another table for Bins

*Bins*
BinID, Autonumber
PartID, Long Integer -- correlates to primary key of Parts
Qty, Long Integer (unless you will have fractions in a bin)
BinNum, integer -- if you want to number the bins for a part such as
1,2,3... although it would not be necessary
BinNum -- don't know data type since this is how you label your bins
LocID, Long Integer -- corresponds to Locations table

then, if you have multiple locations where a Bin can be located, set up
a Locations Table:

*Locations*
LocID, Autonumber
Location, text
etc

This will make it MUCH easier for you to take inventory and add up all
the quantities in the different Bins for a particular Part/Supply.
Also, if you end up using more than 6 Bins for a Part, you don't have to
redesign the structure

"...update just the current form..." -- no, you update fields, not
forms. ALL data is stored in fields that are in tables -- forms just
provide a more convenient way to keep track of data in your tables.

Queries give you a way to combine, sort, and filter data from Tables.

BEFORE you spend any more time with forms, redesign the way your tables
are laid out. Think of it like this: each table is a noun (part, bin,
location, etc) and the fields are the adjectives that describe that noun.

You will also need some kind of Transactions table so you can track when
parts are added or removed from the bins.

Your data structure is like the foundation for a building. Making it
sturdy and strong determines how high you can build.

After you put some more thought into your table design, post back with
what your tables/fields are and we can give you feedback.



Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Jacine wrote:

Parts and Supplies is not a query it is the table in which the Bin
Locations also resides.

There are over 4500 parts/supplies in which each Part could have
several bin locations.

It is a pretty complicated database and I am a little over my head.
They want to be able to update the quantities in each bin location by
doing an actual/physical count. I don't know if access is even able
to do all of this.

From the same table, there is a Parts and Supplies form where they can
add new parts and supplies. From the same table, there is an
inventory control form where some of the same information comes from
the table as in the form they add new parts to, but there is inventory
information such as minimums, maximums, purchase order last ordered on
and they want to keep this information separate as they don't want all
users to access. On the inventory control form it has the Bin
Locations and the Units in each Bin.

They want another field for an actual physical count they will do
several times per year and they just want to be able to update the
Units in each bin from that actual count.

I think my problem is I need to have it update just the current form
that I am trying to update. Is this possible - to have it just update
the current form I am updating that has to do with that Part/Supply
number? The part/supply number is the primary key for that table.

Hope this makes more sense.... I am looking forward to finishing this.

"strive4peace" "strive4peace2006 at yaho" wrote:


Hi Jacine,

I don't know either -- but that is what you error message says --
perhaps you are not using an updateable recordset or records were
locked by something else like a form or query

in taking a better look at your SQL, it appears that your data is not
normalized

UPDATE [Parts and Supplies] SET
UnitsBinLocation1 = [ActualQuantityBin1],
UnitsBinLocation2 = [ActualQuantityBin2],
UnitsBinLocation3 = [ActualQuantityBin3],
UnitsBinLocation4 = [ActualQuantityBin4],
UnitsBinLocation5 = [ActualQuantityBin5],
UnitsBinLocation6 = [ActualQuantityBin6];

You should have a seperate table for BinLocation. It would be linked
on PartID and could have 1, 6, or as many related records as you like.

"It also give me a message that you are about to
update 4500 records which I don't understand since it only updates
that particular part or supply."

is [Parts and Supplies] a query? What is its SQL?

Warm Regards,
Crystal
Microsoft Access MVP 2006

Hi Jacine,

you need to limit the update to the actual record that you are on

put BinID on your form (even if you decide to make its Visible propert=No)

then, you can do this:

'~~~~~~~~~~~~~~~
dim strSQL as string
strSQL = "UPDATE BinLocations " _
& " SET BinLocations.BinQuantity = " & Me.BinPhysicalCount " _
& " WHERE BinID = " Me.BinID_controlname & ";"

'comment next line after debugged
debug.print strSQL
currentdb.execute strSQL
'~~~~~~~~~~~~~~~~~~

this code would be executed behind your form... but rather than
executing code, I still believe you may want to put BinQuantity on your
form (even if it is not showing)... then you simply do this:

me.BinQuantity = Me.BinPhysicalCount

I do not know how your form is laid out... when you say they want to see
what will be changed before the cahgne is done... how are you
accomplishing this?


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Jacine wrote:
It is not the same information.

The inventory amount in each bin is what is there from the last count.

They are using the Actual Physical Count for when they do a physical count
of the inventory and just want to update what was there before the count.

I told them all they have to do is type over/re-enter the amounts but for
some reason they want to see the difference before changing the information.

"strive4peace" wrote:

Hi Jacine,

in order to get this to act on just the current record, you need a WHERE
clause...

but why are you storing the same information in 2 different fields? If
BinPhysicalCount is not a field in your table, but a control on your
form, why not simply use BinQuantity as your ControlSource?


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Jacine wrote:
Hi Crystal,

Here is the SQL text to Update the Quantities from the Actual/Physical Count:


UPDATE BinLocations SET BinLocations.BinQuantity = ([BinPhysicalCount]);

and, here is the SQL text to Reset the Actual/Physical Count back to zero:

UPDATE BinLocations SET BinLocations.BinPhysicalCount = 0;

As mentioned, it does work, but it updates all records if there is a
quantity instead of just updated the current record.

It is also very, very slow.

Thanks again.



"strive4peace" wrote:

Hi Jacine,

You should be able to change the value in the Bins subform directly.

you said, "When I run the query from the actual quantity to the current
quantity - it updates all quantities if there are any other bin
quanitites on any other records."

What is the SQL for your query?
Go to the design view of your query and from the menu, choose, View,
SQL... then copy the statement and paste into a reply

Also, what is triggering the query to execute? What code are you using
to execute it?

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Jacine wrote:
The record source of the main form is the part and supplies table.

The record source of the subform is the Bins.

They won't be keeping track of previous inventory counts. That is why I kept
the bin quantities in the Bins table, however, if you think they should be
separate, then I will do so. They will be doing inventory counts on some of
the parts - not all as some parts will not be stocked in inventory. They may
only do certain counts, i.e. in one city at one time and another city at
another time - this could be sporadic. The bins are identified by city by
using a letter to identify therefore the bin labels are alphanumeric.

That is why I need to have the command button I added to the form to only
update the current form. Is this possible to do - the problem I am
encountering is it updates all the records if there are any amounts entered.

They will be using a "real" inventory software in a few years so I want to
do this right since it will be taken from the Access program.

Thank you.

"strive4peace" wrote:

Hi Jacine,

what is the RecordSource for your main and subforms?

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*



Jacine wrote:
I have set up a new form based on the two tables, and it seems to work quite
well. (Form and subform)

The only problem I have is how do I get it to update just the bin quantities
on the current form.

When I run the query from the actual quantity to the current quantity - it
updates all quantities if there are any other bin quanitites on any other
records.

Thank you.

"strive4peace" "strive4peace2006 at yaho" wrote:

ps

in your Bins table, I admit to copying and pasting and not
changing enough...

the second BinNum should be BinName
(since Name is a reserved word, you never want to use it
without qualifying it)

BinName* -- don't know data type since this is how you label
your bins


*or BinLabel, or even BinNum, in which case, if you want
Access to number bins, you would have to change the previous
fieldname to somthing different like BinNbr. If something
is REALLY a number as opposed to text containing numbers, it
is a good habit to be consistent with the abbreviations that
you use, whatever you choose them to be. For instance, No
and Nbr could refer to fields that are of numeric data type
and Num could refer to a field that is stored as text.


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

strive4peace wrote:
Hi Jacine,

I will assume you have an autonumber field in your Parts and Supplies
table that I will refer to as PartID.

To make it easier to work with, you should consider renaming

Parts and Supplies -- Parts ( or PartsSupplies)

you should not use spaces in table or fieldnames or any names, for that
matter. It is best to use letters, numbers (but don't ever start with a
number) and underscores... no special characters like %, $, etc -- they
can cause problems.

Rather than making 6 Bin fields in that table, what you should do is set
up another table for Bins

*Bins*
BinID, Autonumber
PartID, Long Integer -- correlates to primary key of Parts
Qty, Long Integer (unless you will have fractions in a bin)
BinNum, integer -- if you want to number the bins for a part such as
1,2,3... although it would not be necessary
BinNum -- don't know data type since this is how you label your bins
LocID, Long Integer -- corresponds to Locations table

then, if you have multiple locations where a Bin can be located, set up
a Locations Table:

*Locations*
LocID, Autonumber
Location, text
etc

This will make it MUCH easier for you to take inventory and add up all
the quantities in the different Bins for a particular Part/Supply.
Also, if you end up using more than 6 Bins for a Part, you don't have to
redesign the structure

"...update just the current form..." -- no, you update fields, not
forms. ALL data is stored in fields that are in tables -- forms just
provide a more convenient way to keep track of data in your tables.

Queries give you a way to combine, sort, and filter data from Tables.

BEFORE you spend any more time with forms, redesign the way your tables
are laid out. Think of it like this: each table is a noun (part, bin,
location, etc) and the fields are the adjectives that describe that noun.

You will also need some kind of Transactions table so you can track when
parts are added or removed from the bins.

Your data structure is like the foundation for a building. Making it
sturdy and strong determines how high you can build.

After you put some more thought into your table design, post back with
what your tables/fields are and we can give you feedback.



Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Jacine wrote:

Parts and Supplies is not a query it is the table in which the Bin
Locations also resides.

There are over 4500 parts/supplies in which each Part could have
several bin locations.

It is a pretty complicated database and I am a little over my head.
They want to be able to update the quantities in each bin location by
doing an actual/physical count. I don't know if access is even able
to do all of this.

From the same table, there is a Parts and Supplies form where they can
add new parts and supplies. From the same table, there is an
inventory control form where some of the same information comes from
the table as in the form they add new parts to, but there is inventory
information such as minimums, maximums, purchase order last ordered on
and they want to keep this information separate as they don't want all
users to access. On the inventory control form it has the Bin
Locations and the Units in each Bin.

They want another field for an actual physical count they will do
several times per year and they just want to be able to update the
Units in each bin from that actual count.

I think my problem is I need to have it update just the current form
that I am trying to update. Is this possible - to have it just update
the current form I am updating that has to do with that Part/Supply
number? The part/supply number is the primary key for that table.

Hope this makes more sense.... I am looking forward to finishing this.

"strive4peace" "strive4peace2006 at yaho" wrote:


Hi Jacine,

I don't know either -- but that is what you error message says --
perhaps you are not using an updateable recordset or records were
locked by something else like a form or query

in taking a better look at your SQL, it appears that your data is not
normalized

UPDATE [Parts and Supplies] SET
UnitsBinLocation1 = [ActualQuantityBin1],
UnitsBinLocation2 = [ActualQuantityBin2],
UnitsBinLocation3 = [ActualQuantityBin3],
UnitsBinLocation4 = [ActualQuantityBin4],
UnitsBinLocation5 = [ActualQuantityBin5],
UnitsBinLocation6 = [ActualQuantityBin6];

You should have a seperate table for BinLocation. It would be linked
on PartID and could have 1, 6, or as many related records as you like.

"It also give me a message that you are about to
update 4500 records which I don't understand since it only updates
that particular part or supply."

is [Parts and Supplies] a query? What is its SQL?

Warm Regards,
Crystal
Microsoft Access MVP 2006