Confused with ADO code (1 Viewer)

NNLogistics · Feb 16, 2011

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

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

JoanneJames · Feb 16, 2011

try adding rst.movenext after updating

NNLogistics · Feb 16, 2011

Thanks Joanne, but no difference.

I added a msgbox right before the update and now move next and before the update fldQuantity = 3 and after update it = 1, just like the table tells me.

I think I'm just more confused.

Thanks so much , please let me know if you tink of any other suggestions.

Steve R. · Feb 16, 2011

Try this website. I have found the sample syntax provided to be extremely useful. Comparison of DAO and ADO Recordset Syntax.

NNLogistics · Feb 16, 2011

Thanks Steve

I think I'm doing exactly what they suggest.

Editing a Recordset (ADO Server)

' ADO is naturally in edit mode, so do not use "rs.Edit"

rs("LastName") = "Smith-Jones"

rs.Update

But I have to be overlooking something

boblarson · Feb 16, 2011

Have you checked to see if your query:
qryAllWarehouseInventory
is updatable? Can you update records directly in it? You need to be able to in order for the code to be able to.

NNLogistics · Feb 16, 2011

Thanks Bob

I just manually updated the qry(ok) and then checked the table and it was also updated.

boblarson · Feb 16, 2011

Personally, I would just use an update query It would be much more efficient and would eliminate your problem. And, while I understand the desire to use ADO (I started with ADO many years ago), it is really DAO which would be a better choice when working with tables and queries inside the current database.

NNLogistics · Feb 16, 2011

Thanks Bob.

At this point i've invested more than I want to giveup (just yet). I love writing this code but its also very frustrating.

Thanks again.

Joe

JoanneJames · Feb 16, 2011

Is your query linked to the tables you think they are supposed to be linked to? i.e.: Are you looking in the correct table to verify results? ie test vs. production?

boblarson · Feb 16, 2011

Try changing this:

adLockBatchOptimistic

to this:

adLockOptimistic

or else change
.Update
to

.UpdateBatch

NNLogistics · Feb 16, 2011

Thanks Bob

Yes, as soon as I saw the adLockBatchOptimistic, I thought what the heck is that. I dont know how to use batch. I must have used the first line on the drop doww, saw optimistic and picked it. Thank you so much.

Dont go far, I have a lot more code to write or screw up.

Thanks again

Joe

Confused with ADO code (1 Viewer)

NNLogistics

Registered User.

JoanneJames

Registered User.

NNLogistics

Registered User.

Steve R.

Retired

NNLogistics

Registered User.

boblarson

Smeghead

NNLogistics

Registered User.

boblarson

Smeghead

NNLogistics

Registered User.

JoanneJames

Registered User.

boblarson

Smeghead

NNLogistics

Registered User.

Similar threads

Users who are viewing this thread