Updating tbl from frm checkbox..no primary key 2

[murkydurky]

i am attempting to update an inventory table from a checkbox on a form. The table does not have a primary key. All the checkbox needs to do is subtract 1 from the field when checked, and if accidentally checked, then unchecked needs to add the 1 back. Any help is appreciated!

 

[randy700]

If the table has no primary key, how can you identify which record needs to be updated?


Randy

 

[murkydurky]

i was debating whether i needed it or not. This table is a very simple inventory table. It just tells how many of each part we have. (300 11/32 screws, 100 power supplies, etc.) There isn't a good identifier for each record. Right now I have a checklist for each system these parts go into, and I have been checking off the parts i have used, then going back and manually editing the database. For simplicity all of the checkboxes would represent a value of 1.

 

[randy700]

Why not simply add an autonumber field to use as the primary key? Then you can create your UPDATE query.

UPDATE theTable SET Quantity = Quantity + 1 WHERE TableID = Forms!theForm!txtID

UPDATE theTable SET Quantity = Quantity - 1 WHERE TableID = Forms!theForm!txtID

Randy

 

[murkydurky]

didn't take it...VB got stuck at SET

 

[murkydurky]

the field on the form with a checkbox is a Yes/No field in a different table. When it is checked I am trying to get it to subtract 1 from a different table. (forgive me, i'm attempting to repair a database that I didn't create)

 

[PHV]

How are the 2 tables joined ?
In other words, how do you know for sure that THIS Yes/No field belongs to THAT quantity field ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[murkydurky]

the two tables are not joined. The yes/no field does not belong to that quantity field. I just need it to deduct from that field when checked.

 

[murkydurky]

is there any kind of IF statement I could write to make this happen? (so far i haven't been successfull)

 

[PHV]

I just need it to deduct from that field when checked
to deduct from WHICH field ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[murkydurky]

okay here is the breakdown. This is a very SIMPLE database. I am thinking about just redoing the entire thing, but I would have a lot of data i would have to manually put back in..

tblBFT is the main table of this database. It contains the Yes/No checkbox fields that are on the form frmCables. The only purpose at that point was just a checklist procedure for each tech. When a cable was installed, click the checkbox. Now the database needs to perform more of an inventory / tracking role, so every time a tech checks off the cables they have used, it needs to subtract it from the table tblInventory. This way we know when we have to reorder the appropriate cables.

In a nutshell...

frmCables is the checklist form the techs use. The yes/no checkboxes need to perform two functions :

1.) Provide a "yes" or "no" in a specific field in tblBFT (it is allready doing this)
2.) Deduct a quantity of "1" from a specific field in tblInventory

Sorry this is so confusing.

 

[PHV]

Unless I misunderstood your issue you should have a relationship between tblBFT and tblInventory (probably a cable identifier).

BTW, have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[murkydurky]

it just needs to subtract one when i click on it. Thank you, but I am familiar with the fundamentals of relational database design. I am trying to avoid redesigning the entire database because I would be hand keying old information for a week. I just want it to subtract "1" from a field that I will specify. That's it.

 

[murkydurky]

if that last post seemed a little barbed PHV i didn't intend it to be. I've been fighting with this database for a week and I'm just trying to keep it basic. Once it's functioning properly we'll problalby be looking to SQL and leave access as a front end.