NNLogistics
Registered User.
- Local time
- Today, 08:40
- Joined
- Mar 30, 2009
- Messages
- 14
This is the First time I have written code for a form with all unbound objects. Bascially I have a inventory table that uses a combination of warehouse and PartNumber as the key, It also seperately has those fields(I know its not efficient). So What I want to do is
If I find the key it wont go to EOF and I should be able to update that record. If I dont find the code then I will have to write the code(I didnt write this yet)
In this test 1 is the existing quantity and I want to add another 2 = Total of 3
In this test 1 is the existing quantity and I want to add another 2 = Total of 3
tblInventory tblProducts
qryInventoryAllWarehouses
fldKey(tblInventory)= fldWarehouse & fldPartNumber
fldWarehouse(tblInventory
fldPartNumber(tblInventory
fldDescription(tblProducts)
fldQuantity(tblInventory)
frmInventoryTransaction(All objects unbound)
txtTranferQuantity cmbofromWarehouse cmboPartNumber cmboToWarehouse
txtToKey(Not Visible) txtFinalQuantity(NotVisible)
'We are at the exit of the To Warehouse having already entered all other 'info
'Now Write it to qryInventoryAllWarehouses- if the Key(Warehouse + PartNumber) exists change the quantities(Update)
'If the Key does not exit(EOF), then write it(didn't strat this branch yet).
Me.txtToKey = (Me.cmboToWarehouse + Me.cmboPartNumber)
'Make Connection
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset
Set Conn = New ADODB.Connection
Conn.Open CurrentProject.Connection
Set rst = New ADODB.Recordset
'Open Query
rst.Open "qryAllWarehouseInventory", Conn, adOpenDynamic, adLockBatchOptimistic
‘If you find key here(it means it exists and should only modify(Up or Lower the ‘Quantity), if it doesn’t, then ‘EOF and then write – (I didnt get to this yet code yet). ‘The test I set up = Key exists, just want to add the transferredQuantity(2) to the ‘existing Quantity(1)
'Find Key to Record - make sure that the Part Number exits in the “ToWarehouse"and the ‘existing Quantity is correct
rst.Find "fldKey = '" & Forms!frmInventoryTransactions.txtToKey & "'"
MsgBox (rst!fldKey & " " & rst!fldQuantity) ‘I Get correct quantity of 1
If rst.EOF Then
‘Write a new Record – Didn’t write this code yet
Else
' Increase the Quantity by adding Current and Transferred Quantity
rst!fldKey = Me.cmboToWarehouse + Me.cmboPartNumber ‘I added this later, I didn’t think I needed it but I added it. It didn’t help
rst!fldQuantity = rst!fldQuantity + Me.txtTransferredQuantity
Me.txtToWarehouseFinalQuantity = rst!fldQuantity
rst.Update
MsgBox (Me.txtToWarehouseFinalQuantity & " " & rst!fldQuantity) 'Should result in 3 3 ‘I get this far with correct values
'Close Connection
rst.Close
'Release Table and Connection
Set rst = Nothing
Set Conn = Nothing
Check Inventory qry or Table and it doesn’t get updated, still at 1. It alsmost seems the "rst.Update" is working?
Sorry for the lenght and confused code, hopefully it will progress
Thanks for any suggestions
Joe