Another Requery problem

N

NNLogistics

Registered User.
Local time
Today, 04:57
Joined
Mar 30, 2009
Messages
14
I know this topic has been covered lately and often, so I apologize in advance for the repeat, but I have been working on this for quit some time. I have a Form (frmInventoryTransactions) and a subform(subfrmDailyTransaction.Form). The subform is based on a query. The form is unbound. “On Exit” from a combo box, on the form, I have code that writes to several tables, one of them being a Transaction Table which the subform is based on. After I close the connection on that table I try to requery the subform to show the last transaction. I tried - Me.subfrmDailyTransaction.Form.Requery and Forms![frmInventoryTransactions]![subfrmDailyTransaction].Form.Requery. I can’t say that it doesn’t work, its just so very inconsistent. If I close and reopen form all the transactions on the subform show up.

I then set up a command button on the form and just put the requery code in it (on Click). I tried Me.subfrmDailyTransaction.Form.Requery and then frmInventoryTransactions]![subfrmDailyTransaction].Form.Requery, both react the same way. The first time I hit it N.G.. It may work on the 2nd or 3rd time.

Any thoughts?

As always(and often), I appreciate any help.

Joe
 
Book mark this link for future reference, it contains the correct syntax for referring to forms, sub-forms and their control from various relative locations.
 
Thanks,John

Sorry I didn't respond on Monday. I have spent the last 2 days basically on this statement - Me!subfrmDailyTransaction.Form.Requery - and every variation of it. on every event that applies. I was familar with the document you suggested. Perhaps I am still missing the exact syntac, its happened before, do you see anything wrong with it?

The problem still remains the same, its inconsistant. As I mentioned I also used the statement on a command button with the same results.

I am at a loss. Does anyone else have this problem?

Thanks

Joe
 
Let's see the code behind the combo box's event.
 
Thanks vbainet, for taking the time. I hope this is relatively clear, I know I find it very difficult to follow someone else's code. It's long . Basically if there is a record in the "To Warehouse" I update it. If there is no record I write it. Then I always write to a transaction table(This is data that I want to refresh in the subform). I put the requery statement in a few places, I didn't think it would hurt. So here goes, you asked for it. I hope your at least entertained.

Private Sub cmboToWarehouse_Exit(Cancel As Integer)
'For Reference
'Transfer Quantity=me.txtTransferredQuantity fromfrmInventoryTransactions
'From Key = me.txtFromKey = me.cmboWarehouseID + me.cmboPartNumber established in me.cmboPartNumber exit
'To Key = me.txtToKey = me.cmboToWarehouse + me.cmboPartNumber established in me.cmboWarehouse exit
'From Warehouse = me.cmboWarehouseID established in frmInventoryTransactions
'Part Number = me.cmboPartNumber established in frmInventoryTransactions
'To Warehouse = me.cmboToWarehouse established in frmInventoryTransactions
'Current Quantity = Quantity in From Warehouse established in me.cmboPartNumber exit
'To Warehouse Final Quantity = txtToWarehouseFinalQuantity established in me.cmboToWarehouse
'From Warehouse Final Quanitity = me.txtFromWarehouseFinalQuantity = Me.txtCurrentQuantity - Me.txtTransferredQuantity established in me.cmboPartNumber

'We are at the exit of the To Warehouse
'Now Write or Update qryAllWarehouseInventory that uses tblInventory - if the Key(Warehouse + PartNumber) exists change the quantities
'If the Key does not exit, then write it.


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, adLockOptimistic

'Find "ToKey" Record - make sure that the Part Number exits in the To Warehouse
'And use this as a switch to either write or update
rst.MoveFirst
rst.Find "fldKey = '" & Me.txtToKey & "'"

If rst.EOF Then

'Confirm Update or Write
Dim intConfirmTransactionWrite As Integer
intConfirmTransactionWrite = MsgBox("You are about to transfer Quantity " & Me.txtTransferredQuantity & " Of Part Number " & Me.cmboPartNumber & "From Warehouse " & Me.cmboWarehouseID & "To Warehouse " & Me.cmboToWarehouse & " Please Confirm (Y/N)", vbQuestion + vbYesNo + vbDefaultButton1, "Logistics DB, Inventory Module")
If intConfirmTransactionWrite = vbYes Then


Else 'This is a NO Clear all the fields and start over
Me.txtTransferredQuantity.SetFocus
Me.txtTransferredQuantity = Null
Me.txtCurrentQuantity = Null
Me.cmboWarehouseID = Null
Me.cmboPartNumber = Null
Me.txtFromKey = Null
Me.txtDescription = Null
Me.cmboToWarehouse = Null
Me.txtToKey = Null
Me.txtFromWarehouseFinalQuantity = Null
Me.txtToWarehouseFinalQuantity = Null
Me.txtTransactionNotes = Null
Exit Sub
End If

'Write a New Record with Key and Quantity

'Write to ToWarehouse
rst.AddNew

rst!fldKey = Me.txtToKey

'rst!fldKey = Me.cmboToWarehouse + Me.cmboPartNumber
rst!fldWarehouse = Me.cmboToWarehouse
rst!fldPartNumber = Me.cmboPartNumber
rst!fldDescription = Me.txtDescription
rst!fldQuantity = Me.txtTransferredQuantity
Me.txtToWarehouseFinalQuantity = rst!fldQuantity
rst.Update
'Remove from FromWarehouse
rst.MoveFirst
rst.Find "fldKey = '" & Forms!frmInventoryTransactions.txtFromKey & "'"
rst!fldQuantity = (Me.txtCurrentQuantity - Me.txtTransferredQuantity)
rst.Update





'Now Write to tblInventoryTransaction(IT)Write
Dim ConnITW As ADODB.Connection
Dim rstITW As ADODB.Recordset
Set ConnITW = New ADODB.Connection
ConnITW.Open CurrentProject.Connection
Set rstITW = New ADODB.Recordset

'Open Table

rstITW.Open "tblInventoryTransaction", ConnITW, adOpenDynamic, adLockOptimistic

'Write to tblInventoryTransaction
rstITW.AddNew
rstITW!fldQuantity = Me.txtTransferredQuantity
rstITW!fldToWarehouse = Me.cmboToWarehouse
rstITW!fldPartNumber = Me.cmboPartNumber
rstITW!fldFromWarehouse = Me.cmboWarehouseID
rstITW!fldDateofTransaction = Date
rstITW!fldTransactionNotes = Me.txtTransactionNotes
rstITW!fldTime = Time()
rstITW.Update

'Close Connection
rstITW.Close

'Release Table and Connection
Set rstITW = Nothing
Set ConnITW = Nothing


'Requery SubForm

Me!subfrmDailyTransaction.Form.Requery


'Clear all fields and move to cmd button


Me.cmdRequerySubform.SetFocus
Me.cmdRequerySubform.ForeColor = vbRed
Me.txtTransferredQuantity = Null
Me.txtCurrentQuantity = Null
Me.cmboWarehouseID = Null
Me.cmboPartNumber = Null
Me.txtFromKey = Null
Me.txtDescription = Null
Me.cmboToWarehouse = Null
Me.txtToKey = Null
Me.txtFromWarehouseFinalQuantity = Null
Me.txtToWarehouseFinalQuantity = Null
Me.txtTransactionNotes = Null




'Update
Else
'Update From warehouse and to warehouse then comeback and write code for Confirmation
'Confirm Update or Write use intConfirmTransactionUpdate

'Confirm Update or Write
Dim intConfirmTransactionUpdate As Integer
intConfirmTransactionUpdate = MsgBox("You are about to transfer Quantity " & Me.txtTransferredQuantity & " Of Part Number " & Me.cmboPartNumber & "From Warehouse " & Me.cmboWarehouseID & "To Warehouse " & Me.cmboToWarehouse & " Please Confirm (Y/N)", vbQuestion + vbYesNo + vbDefaultButton1, "Logistics DB, Inventory Module")
If intConfirmTransactionUpdate = vbYes Then


Else 'This is a NO Clear all the fields and start over
Me.txtTransferredQuantity.SetFocus
Me.txtTransferredQuantity = Null
Me.txtCurrentQuantity = Null
Me.cmboWarehouseID = Null
Me.cmboPartNumber = Null
Me.txtFromKey = Null
Me.txtDescription = Null
Me.cmboToWarehouse = Null
Me.txtToKey = Null
Me.txtFromWarehouseFinalQuantity = Null
Me.txtToWarehouseFinalQuantity = Null
Me.txtTransactionNotes = Null
Exit Sub
End If

'Decrease the Quantity in From Warehouse by subtracting
'Start here tommorrow need decrease current Quantity by transferred Quantity

rst.MoveFirst
rst.Find "fldKey = '" & Forms!frmInventoryTransactions.txtFromKey & "'"
rst!fldQuantity = (Me.txtCurrentQuantity - Me.txtTransferredQuantity)
rst.Update

'Increase the Quantity in To Warehouse by adding Current and Transferred
'Do a Find here rst!fldKey = Me.txtToKey
rst.MoveFirst
rst.Find "fldKey = '" & Forms!frmInventoryTransactions.txtToKey & "'"
rst!fldQuantity = (rst!fldQuantity + Me.txtTransferredQuantity)
Me.txtToWarehouseFinalQuantity = rst!fldQuantity

rst.Update







'Now Write to tblInventoryTransaction(IT)Update
Dim ConnITU As ADODB.Connection
Dim rstITU As ADODB.Recordset
Set ConnITU = New ADODB.Connection
ConnITU.Open CurrentProject.Connection
Set rstITU = New ADODB.Recordset

'Open Table

rstITU.Open "tblInventoryTransaction", ConnITU, adOpenDynamic, adLockOptimistic

'Write to tblInventoryTransaction
rstITU.AddNew
rstITU!fldQuantity = Me.txtTransferredQuantity
rstITU!fldToWarehouse = Me.cmboToWarehouse
rstITU!fldPartNumber = Me.cmboPartNumber
rstITU!fldFromWarehouse = Me.cmboWarehouseID
rstITU!fldDateofTransaction = Date
rstITU!fldTime = Time()
rstITU!fldTransactionNotes = Me.txtTransactionNotes
rstITU.Update

'Close Connection
rstITU.Close

'Release Table and Connection
Set rstITU = Nothing
Set ConnITU = Nothing

'Requery SubForm

Me!subfrmDailyTransaction.Form.Requery



'Make all Field Null

Me.cmdRequerySubform.SetFocus
Me.cmdRequerySubform.ForeColor = vbRed
Me.txtTransferredQuantity = Null
Me.txtCurrentQuantity = Null
Me.cmboWarehouseID = Null
Me.cmboPartNumber = Null
Me.txtFromKey = Null
Me.txtToKey = Null
Me.txtDescription = Null
Me.cmboToWarehouse = Null
Me.txtFromWarehouseFinalQuantity = Null
Me.txtToWarehouseFinalQuantity = Null
Me.txtTransactionNotes = Null
End If


'Close Connection
rst.Close

'Release Table and Connection
Set rst = Nothing
Set Conn = Nothing


'Requery SubForm

Me!subfrmDailyTransaction.Form.Requery






'The Me.cmdRequerySubform.SetFocus
Me.cmdRequerySubform.ForeColor = vbRed sends it to a command button, That also has a requery. Both are inconsistant. Sorry the indents didn't export to message.


Thanks again
 
Thanks, Hope this is better


Code: 
Private Sub cmboToWarehouse_Exit(Cancel As Integer)
'For Reference
'Transfer Quantity=me.txtTransferredQuantity fromfrmInventoryTransactions
'From Key = me.txtFromKey = me.cmboWarehouseID + me.cmboPartNumber established in me.cmboPartNumber exit
'To Key = me.txtToKey = me.cmboToWarehouse + me.cmboPartNumber established in me.cmboWarehouse exit
'From Warehouse = me.cmboWarehouseID established in frmInventoryTransactions
'Part Number = me.cmboPartNumber established in frmInventoryTransactions
'To Warehouse = me.cmboToWarehouse established in frmInventoryTransactions
'Current Quantity = Quantity in From Warehouse established in me.cmboPartNumber exit
'To Warehouse Final Quantity = txtToWarehouseFinalQuantity established in me.cmboToWarehouse
'From Warehouse Final Quanitity = me.txtFromWarehouseFinalQuantity = Me.txtCurrentQuantity - Me.txtTransferredQuantity established in me.cmboPartNumber
 
'We are at the exit of the To Warehouse
'Now Write or Update qryAllWarehouseInventory that uses tblInventory - if the Key(Warehouse + PartNumber) exists change the quantities
'If the Key does not exit, then write it.
 
 
        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, adLockOptimistic
 
        'Find "ToKey" Record - make sure that the Part Number exits in the To Warehouse
        'And use this as a switch to either write or update
            rst.MoveFirst
            rst.Find "fldKey = '" & Me.txtToKey & "'"
 
        If rst.EOF Then
 
         'Confirm Update or Write
           Dim intConfirmTransactionWrite As Integer
                intConfirmTransactionWrite = MsgBox("You are about to transfer Quantity  " & Me.txtTransferredQuantity & "  Of Part Number   " & Me.cmboPartNumber & "From Warehouse  " & Me.cmboWarehouseID & "To Warehouse  " & Me.cmboToWarehouse & "  Please Confirm (Y/N)", vbQuestion + vbYesNo + vbDefaultButton1, "Logistics DB, Inventory Module")
                    If intConfirmTransactionWrite = vbYes Then
 
 
                    Else 'This is a NO Clear all the fields and start over
                        Me.txtTransferredQuantity.SetFocus
                        Me.txtTransferredQuantity = Null
                        Me.txtCurrentQuantity = Null
                        Me.cmboWarehouseID = Null
                        Me.cmboPartNumber = Null
                        Me.txtFromKey = Null
                        Me.txtDescription = Null
                        Me.cmboToWarehouse = Null
                        Me.txtToKey = Null
                        Me.txtFromWarehouseFinalQuantity = Null
                        Me.txtToWarehouseFinalQuantity = Null
                        Me.txtTransactionNotes = Null
                        Exit Sub
                    End If
 
'Write a New Record with Key and Quantity
 
            'Write to ToWarehouse
                rst.AddNew
 
                rst!fldKey = Me.txtToKey
 
                'rst!fldKey = Me.cmboToWarehouse + Me.cmboPartNumber
                rst!fldWarehouse = Me.cmboToWarehouse
                rst!fldPartNumber = Me.cmboPartNumber
                rst!fldDescription = Me.txtDescription
                rst!fldQuantity = Me.txtTransferredQuantity
                Me.txtToWarehouseFinalQuantity = rst!fldQuantity
                rst.Update
            'Remove from FromWarehouse
                rst.MoveFirst
                rst.Find "fldKey = '" & Forms!frmInventoryTransactions.txtFromKey & "'"
                rst!fldQuantity = (Me.txtCurrentQuantity - Me.txtTransferredQuantity)
                rst.Update
 
 
 
 
 
            'Now Write to tblInventoryTransaction(IT)Write
                Dim ConnITW As ADODB.Connection
                Dim rstITW As ADODB.Recordset
                Set ConnITW = New ADODB.Connection
                ConnITW.Open CurrentProject.Connection
                Set rstITW = New ADODB.Recordset
 
            'Open Table
 
                rstITW.Open "tblInventoryTransaction", ConnITW, adOpenDynamic, adLockOptimistic
 
            'Write to tblInventoryTransaction
                rstITW.AddNew
                rstITW!fldQuantity = Me.txtTransferredQuantity
                rstITW!fldToWarehouse = Me.cmboToWarehouse
                rstITW!fldPartNumber = Me.cmboPartNumber
                rstITW!fldFromWarehouse = Me.cmboWarehouseID
                rstITW!fldDateofTransaction = Date
                rstITW!fldTransactionNotes = Me.txtTransactionNotes
                rstITW!fldTime = Time()
                rstITW.Update
 
            'Close Connection
                rstITW.Close
 
            'Release Table and Connection
                Set rstITW = Nothing
                Set ConnITW = Nothing
 
 
            'Requery SubForm
 
            Me!subfrmDailyTransaction.Form.Requery
 
 
            'Clear all fields and move to cmd button
 
 
                    Me.cmdRequerySubform.SetFocus
                    Me.cmdRequerySubform.ForeColor = vbRed
                    Me.txtTransferredQuantity = Null
                    Me.txtCurrentQuantity = Null
                    Me.cmboWarehouseID = Null
                    Me.cmboPartNumber = Null
                    Me.txtFromKey = Null
                    Me.txtDescription = Null
                    Me.cmboToWarehouse = Null
                    Me.txtToKey = Null
                    Me.txtFromWarehouseFinalQuantity = Null
                    Me.txtToWarehouseFinalQuantity = Null
                    Me.txtTransactionNotes = Null
 
 
 
 
'Update
            Else
            'Update From warehouse and to warehouse then comeback and write code for Confirmation
            'Confirm Update or Write use intConfirmTransactionUpdate
 
             'Confirm Update or Write
           Dim intConfirmTransactionUpdate As Integer
                intConfirmTransactionUpdate = MsgBox("You are about to transfer Quantity  " & Me.txtTransferredQuantity & "  Of Part Number   " & Me.cmboPartNumber & "From Warehouse  " & Me.cmboWarehouseID & "To Warehouse  " & Me.cmboToWarehouse & "  Please Confirm (Y/N)", vbQuestion + vbYesNo + vbDefaultButton1, "Logistics DB, Inventory Module")
                    If intConfirmTransactionUpdate = vbYes Then
 
 
                    Else 'This is a NO Clear all the fields and start over
                        Me.txtTransferredQuantity.SetFocus
                        Me.txtTransferredQuantity = Null
                        Me.txtCurrentQuantity = Null
                        Me.cmboWarehouseID = Null
                        Me.cmboPartNumber = Null
                        Me.txtFromKey = Null
                        Me.txtDescription = Null
                        Me.cmboToWarehouse = Null
                        Me.txtToKey = Null
                        Me.txtFromWarehouseFinalQuantity = Null
                        Me.txtToWarehouseFinalQuantity = Null
                        Me.txtTransactionNotes = Null
                        Exit Sub
                    End If
 
            'Decrease the Quantity in From Warehouse by subtracting
            'Start here tommorrow need decrease current Quantity by transferred Quantity
 
                rst.MoveFirst
                rst.Find "fldKey = '" & Forms!frmInventoryTransactions.txtFromKey & "'"
                rst!fldQuantity = (Me.txtCurrentQuantity - Me.txtTransferredQuantity)
                rst.Update
 
            'Increase the Quantity in To Warehouse by adding Current and Transferred
                'Do a Find here        rst!fldKey = Me.txtToKey
                rst.MoveFirst
                rst.Find "fldKey = '" & Forms!frmInventoryTransactions.txtToKey & "'"
                rst!fldQuantity = (rst!fldQuantity + Me.txtTransferredQuantity)
                Me.txtToWarehouseFinalQuantity = rst!fldQuantity
 
                rst.Update
 
 
 
 
 
 
 
            'Now Write to tblInventoryTransaction(IT)Update
                Dim ConnITU As ADODB.Connection
                Dim rstITU As ADODB.Recordset
                Set ConnITU = New ADODB.Connection
                ConnITU.Open CurrentProject.Connection
                Set rstITU = New ADODB.Recordset
 
            'Open Table
 
                rstITU.Open "tblInventoryTransaction", ConnITU, adOpenDynamic, adLockOptimistic
 
            'Write to tblInventoryTransaction
                rstITU.AddNew
                rstITU!fldQuantity = Me.txtTransferredQuantity
                rstITU!fldToWarehouse = Me.cmboToWarehouse
                rstITU!fldPartNumber = Me.cmboPartNumber
                rstITU!fldFromWarehouse = Me.cmboWarehouseID
                rstITU!fldDateofTransaction = Date
                rstITU!fldTime = Time()
                rstITU!fldTransactionNotes = Me.txtTransactionNotes
                rstITU.Update
 
            'Close Connection
                rstITU.Close
 
            'Release Table and Connection
                Set rstITU = Nothing
                Set ConnITU = Nothing
 
            'Requery SubForm
 
            Me!subfrmDailyTransaction.Form.Requery
 
 
 
            'Make all Field Null
 
                Me.cmdRequerySubform.SetFocus
                Me.cmdRequerySubform.ForeColor = vbRed
                Me.txtTransferredQuantity = Null
                Me.txtCurrentQuantity = Null
                Me.cmboWarehouseID = Null
                Me.cmboPartNumber = Null
                Me.txtFromKey = Null
                Me.txtToKey = Null
                Me.txtDescription = Null
                Me.cmboToWarehouse = Null
                Me.txtFromWarehouseFinalQuantity = Null
                Me.txtToWarehouseFinalQuantity = Null
                Me.txtTransactionNotes = Null
        End If
 
 
         'Close Connection
                rst.Close
 
            'Release Table and Connection
                Set rst = Nothing
                Set Conn = Nothing
 
 
      'Requery SubForm
 
            Me!subfrmDailyTransaction.Form.Requery
 
You must log in or register to reply here.

Similar threads

A
Form with subform in transaction
2
Replies
39
Views
1,662
Albert D. Kallal
A
D
Subform requery is starting a new record
Replies
2
Views
857
dullster
D
D
After update
Replies
15
Views
1,572
dullster
D
A
Label not showing on subform if value is 0
2
Replies
32
Views
1,051
isladogs
isladogs
J
Solved Troubleshooting Subform
Replies
10
Views
401
jamesave
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom