Using info entered in textboxes in a form

[debsmith]

I am creating an inventory control system. I am trying to use the record selected from a combobox and the quantity amount entered in a textbox to subtract the quantity and update the record. Can someone please help.

Please email me @ debsmith_4@hotmail.com or post a response.

Thanks so much,

 

[adam_fleck]

Try an update query?

Suppose your combo box (cbo_Record) lets you get hold of something that will identify the record uniquely, record_ID, say. Also, suppose your text box (txt_Quantity) will have an amount entered into it. Plus, there's maybe a button (cmd_DoIt) that the user will click to downdate the inventory table (table_inventory).

Something like this should work:

Private Sub cmd_DoIt_Click()

    Dim u_SQL As String
    
    u_SQL = "UPDATE table_Inventory " & _
            " SET field_amount = field_amount - " & CDbl(Me.txt_Quantity) & _
            " WHERE record_ID = " & Me.cbo_Record & ";"
    
    CurrentDb.Execute u_SQL
    

End Sub

Before downdating, you may want to check that you're not going to end up with negative quantities in stock. For this sort of control, it's probably best to use recordsets.

 

[adam_fleck]

Inventory

Hi Deb,

Pat’s right. If you want to produce a decent Inventory system, you should think about setting up a table to log all transactions. Every time stuff is put into stock or taken out, an entry should be written to this table.

I looked through the forum for tips on transaction logging, but since I didn’t spot any actual examples, I’ve attached a zip file containing a small sample Access 2000 database. I’m hoping Pat might have time to muck about with this for a few minutes and tell us what needs to be done to get it up to scratch.

The database allows users to log transactions (withdrawals from stock and inputs to stock). New stock items can be created using the Admin button “Stock Items”. Stock can also be withdrawn for stocktaking and then re-input after the stock take.

I think this is one of the points that put people off transaction logging. It seems as if the log will keep on growing for ever, which means calculating current stock will take longer and longer.

However, if I’ve got this right (and Pat will hopefully say) every so often stock is withdrawn by a transaction called “Out For Stock Take”.

The actual real stock is then counted, and a second transaction (“In After Stock Take”) is logged which puts the counted stock back in.

Meanwhile, all transactions that happened before the stock take are flagged as archived. Since archived transactions are not used in calculating current stock, the calculation involves only a relatively small number of non-archived records – so long as stocktaking is reasonably frequent.

The example database lets users stock-take each different stock line separately.

This has been a useful example for me to set up, as I’ll be writing a real version of something similar for my optician.

I’m hoping Pat will be able to advise me on a transactional question here. Besides maintaining stock records for lenses and solutions, the system will automatically upload Standing Order details from the practice bank (this part is sorted already).

The standing order records are inserted into a financial transactions table and need to be reconciled with what each client should have paid. My question is: would the best way of doing this be to have the system generate an internal invoice for each client every month? The invoice would be inserted into the financial transactions table and the current balance for each client would then be easy to calculate.