i have this form, with quantity recieved (Ir0N), quantity listed
on-ebay(LQ0N) and quantity available(ir0av )
if i receive 10 items i can list 5 with one e-bay number . . . the
quantity available is reduced to 5, and i can list 3 with another e-bay
number, this will reduce the quantity available to 2 . . . .the problem
is that quantity available does not get updated until i enter quantity
listed and go to the next record and then when i come back, i see the
updated value, but unless i go back and forth it does not update.
i tryed to place the code in pretty much every event, on open, on
activate, on dirty . . .nothing works
any ideas??
here is the code
Private Sub Form_Current()
Dim Rst As New ADODB.Recordset
Set Rst = New ADODB.Recordset
Dim SQLstmnt As String
Dim CurrentListedIt emsNUM As Long
Dim RQ0 As Integer
Dim RQ1 As Integer
Dim RQ2 As Integer
Dim RQ3 As Integer
Dim RQ4 As Integer
Dim RQ5 As Integer
Dim SumLQav0 As Integer
Dim SumLQav1 As Integer
Dim SumLQav2 As Integer
Dim SumLQav3 As Integer
Dim SumLQav4 As Integer
Dim SumLQav5 As Integer
Dim frmMainListing As Form
Dim FrmSubListing As Form
Set frmMainListing = Forms![frmMainListing]
Set FrmSubListing = Forms![frmMainListing]![FrmSubListing].Form
RQ0 = Nz(Ir0n, 0)
RQ1 = Nz(Ir1n, 0)
RQ2 = Nz(Ir2n, 0)
RQ3 = Nz(Ir3n, 0)
RQ4 = Nz(Ir4n, 0)
RQ5 = Nz(Ir5n, 0)
On Error GoTo ErrorHandler
DoCmd.RunComman d acCmdSaveRecord
CurrentListedIt emsNUM = FrmSubListing![lItemID]
SQLstmnt = "SELECT SUM(LQ0N) AS SumLQav0, SUM(LQ1N) AS SumLQav1,
SUM(LQ2N) AS SumLQav2, SUM(LQ3N) AS SumLQav3, SUM(LQ4N) AS SumLQav4,
SUM(LQ5N) AS SumLQav5 FROM tblListingDetai ls " & "WHERE [LItemID]= " &
CurrentListedIt emsNUM & ""
'Rst.Open SQLstmnt, CurrentProject. Connection, adOpenForwardOn ly,
adLockReadOnly old version
Rst.Open SQLstmnt, CurrentProject. Connection, adOpenDynamic,
adLockOptimisti c
Ir0av = RQ0 - Rst.Fields("Sum LQav0")
Ir1av = RQ1 - Rst.Fields("Sum LQav1")
Ir2av = RQ2 - Rst.Fields("Sum LQav2")
Ir3av = RQ3 - Rst.Fields("Sum LQav3")
Ir4av = RQ4 - Rst.Fields("Sum LQav4")
Ir5av = RQ5 - Rst.Fields("Sum LQav5")
Rst.Close
Set Rst = Nothing
If FrmSubListing![LQ0N] + FrmSubListing![LQ1N] + FrmSubListing![LQ2N] +
FrmSubListing![LQ3N] + FrmSubListing![LQ4N] + FrmSubListing![LQ5N] = 0
And Ir0av + Ir1av + Ir2av + Ir3av + Ir4av + Ir5av = 0 Then
MsgBox "All Items Have Been Listed, You Can't Go Any Further", vbOKOnly
DoCmd.GoToRecor d , , acPrevious
GoTo done
End If
GoTo done
ErrorHandler:
MsgBox Err.Description
done:
End Sub
on-ebay(LQ0N) and quantity available(ir0av )
if i receive 10 items i can list 5 with one e-bay number . . . the
quantity available is reduced to 5, and i can list 3 with another e-bay
number, this will reduce the quantity available to 2 . . . .the problem
is that quantity available does not get updated until i enter quantity
listed and go to the next record and then when i come back, i see the
updated value, but unless i go back and forth it does not update.
i tryed to place the code in pretty much every event, on open, on
activate, on dirty . . .nothing works
any ideas??
here is the code
Private Sub Form_Current()
Dim Rst As New ADODB.Recordset
Set Rst = New ADODB.Recordset
Dim SQLstmnt As String
Dim CurrentListedIt emsNUM As Long
Dim RQ0 As Integer
Dim RQ1 As Integer
Dim RQ2 As Integer
Dim RQ3 As Integer
Dim RQ4 As Integer
Dim RQ5 As Integer
Dim SumLQav0 As Integer
Dim SumLQav1 As Integer
Dim SumLQav2 As Integer
Dim SumLQav3 As Integer
Dim SumLQav4 As Integer
Dim SumLQav5 As Integer
Dim frmMainListing As Form
Dim FrmSubListing As Form
Set frmMainListing = Forms![frmMainListing]
Set FrmSubListing = Forms![frmMainListing]![FrmSubListing].Form
RQ0 = Nz(Ir0n, 0)
RQ1 = Nz(Ir1n, 0)
RQ2 = Nz(Ir2n, 0)
RQ3 = Nz(Ir3n, 0)
RQ4 = Nz(Ir4n, 0)
RQ5 = Nz(Ir5n, 0)
On Error GoTo ErrorHandler
DoCmd.RunComman d acCmdSaveRecord
CurrentListedIt emsNUM = FrmSubListing![lItemID]
SQLstmnt = "SELECT SUM(LQ0N) AS SumLQav0, SUM(LQ1N) AS SumLQav1,
SUM(LQ2N) AS SumLQav2, SUM(LQ3N) AS SumLQav3, SUM(LQ4N) AS SumLQav4,
SUM(LQ5N) AS SumLQav5 FROM tblListingDetai ls " & "WHERE [LItemID]= " &
CurrentListedIt emsNUM & ""
'Rst.Open SQLstmnt, CurrentProject. Connection, adOpenForwardOn ly,
adLockReadOnly old version
Rst.Open SQLstmnt, CurrentProject. Connection, adOpenDynamic,
adLockOptimisti c
Ir0av = RQ0 - Rst.Fields("Sum LQav0")
Ir1av = RQ1 - Rst.Fields("Sum LQav1")
Ir2av = RQ2 - Rst.Fields("Sum LQav2")
Ir3av = RQ3 - Rst.Fields("Sum LQav3")
Ir4av = RQ4 - Rst.Fields("Sum LQav4")
Ir5av = RQ5 - Rst.Fields("Sum LQav5")
Rst.Close
Set Rst = Nothing
If FrmSubListing![LQ0N] + FrmSubListing![LQ1N] + FrmSubListing![LQ2N] +
FrmSubListing![LQ3N] + FrmSubListing![LQ4N] + FrmSubListing![LQ5N] = 0
And Ir0av + Ir1av + Ir2av + Ir3av + Ir4av + Ir5av = 0 Then
MsgBox "All Items Have Been Listed, You Can't Go Any Further", vbOKOnly
DoCmd.GoToRecor d , , acPrevious
GoTo done
End If
GoTo done
ErrorHandler:
MsgBox Err.Description
done:
End Sub
Comment