By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,400 Members | 1,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,400 IT Pros & Developers. It's quick & easy.

Complicated Question

P: n/a
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 CurrentListedItemsNUM 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.RunCommand acCmdSaveRecord

CurrentListedItemsNUM = 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 tblListingDetails " & "WHERE [LItemID]= " &
CurrentListedItemsNUM & ""

'Rst.Open SQLstmnt, CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly old version
Rst.Open SQLstmnt, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

Ir0av = RQ0 - Rst.Fields("SumLQav0")
Ir1av = RQ1 - Rst.Fields("SumLQav1")
Ir2av = RQ2 - Rst.Fields("SumLQav2")
Ir3av = RQ3 - Rst.Fields("SumLQav3")
Ir4av = RQ4 - Rst.Fields("SumLQav4")
Ir5av = RQ5 - Rst.Fields("SumLQav5")
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.GoToRecord , , acPrevious
GoTo done
End If

GoTo done

ErrorHandler:
MsgBox Err.Description
done:

End Sub

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On 11 Feb 2005 20:14:11 -0800, gbb0330 <gb*****@gmail.com> wrote:
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 CurrentListedItemsNUM 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.RunCommand acCmdSaveRecord

CurrentListedItemsNUM = 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 tblListingDetails " & "WHERE [LItemID]= " &
CurrentListedItemsNUM & ""

'Rst.Open SQLstmnt, CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly old version
Rst.Open SQLstmnt, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

Ir0av = RQ0 - Rst.Fields("SumLQav0")
Ir1av = RQ1 - Rst.Fields("SumLQav1")
Ir2av = RQ2 - Rst.Fields("SumLQav2")
Ir3av = RQ3 - Rst.Fields("SumLQav3")
Ir4av = RQ4 - Rst.Fields("SumLQav4")
Ir5av = RQ5 - Rst.Fields("SumLQav5")
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.GoToRecord , , acPrevious
GoTo done
End If

GoTo done

ErrorHandler:
MsgBox Err.Description
done:

End Sub

I can't say that I follow exactly what you're doing, but I can offer this
generic advice: look into Form methods Requery and Recalc.
Darryl Kerkeslager
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.