Changing information on a table based on Form Input

[grantsd]

Heya gang,

Not very good with this stuff. At all. That said, I've got a database that controls a lot of stuff - employee hours/scheduling, a list of our parts/prices, invoicing and customer management. It's working well (which is good for me), but I've got one final thing to do.

On my parts list, I've got the basics (part #, name, price) along with a NumberOnHand. When Inventory is done/parts are received and we change those numbers, that's fine and great. However, when someone does an invoice and sells one of those parts, I'd like it to be deducted from the number on hand. Is this doable? If someone could tell me how, I'd be very appreciative. As I said, I'm not great with this, most of what I've done has been pretty much just testing and trial and error.

Thanks tons.

Scott

 

[BLeslie88]

When they invoice...

do they store this info in a table...?

do the use the application.?

does their pc's write to your database?

If so, you could just run an update query specifying the criteria required and deucting -1 from quantity...

If all the above is true, I can provide the sql vb code to do so.

 

[grantsd]

The database I'm using is a modified version of workorder management template - and I haven't changed any of the fundamentals, so it works exactly like that one.

I know I need to learn more about what the heck I'm talking about, but I'm more of the hardware end of the PC spectrum and that's all I've got time for these days.

Basically when I create a workorder, I can to go a Parts or a Labor option. If I go to parts, I can choose a part number and enter the total amount used of that part. When I do so, I would like to have the table updated to reflect the pull from inventory. Then, when we receive shipments, I'll have a different form that our inventory person will use to update the records.

We're not a massive store, we don't have thousands or even hundreds of items really.

Thanks for the input,

Scott

 

[BLeslie88]

update

Get the fields names....

You can go to queries, bring up the tables, select update query as an option. On the order form, you can use the [part no] as the parameter...

ok like this.

"someone does an invoice and sells one of those parts"

there need to be an action, when they commit this invoice, in the same event run this code.

DoCmd.RunSQL ("UPDATE tblInv SET tblInv.Qty = " &YOURNUMBEROFPARTSINVOICED & " Where tblInv.PartNo = " & YOURPARTNUMBER & ";")

[YOURNUMBEROFPARTSINVOICED]
[YOURPARTNUMBER]

these 2 values need to be assigned, by the text boxes they show up on, hopefully this helps some...

 

[grantsd]

Wow, that sounds like it will help. I have no idea where I go to change the code like that - but I'm gonna go try. Thanks for the help!

 

[grantsd]

Hm, will that set the inventory number TO the amount I removed? I'd like it to minus it from the part. Example:

I have 10 40GB Hard Drives. I have a customer, go to his screen, click 'Parts', select the Hard Drive and enter '2', I'd like my Parts On Hand to show '38'.

Possible? No? Thanks.

 

[BLeslie88]

yes

you need to replace the tblName with your tables name and use the right fields...

then


[YOURNUMBEROFPARTSINVOICED] = txtNoOfPart 'which is 2
[YOURPARTNUMBER] = txtPartNumber 'which is hard drives

run this update....

DoCmd.RunSQL ("UPDATE tblInv SET tblInv.Qty = tblInv.Qty - " &YOURNUMBEROFPARTSINVOICED & " Where tblInv.PartNo = " & YOURPARTNUMBER & ";")

you were right I forgot the accrual I was assigning the 2... lol use this code, it'll work. If you get the table and field names right.