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.
:
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.
:
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.
:
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,
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
