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

Looping records in form

P: 12
I created a form that bound to a query. The form shows the records in tabular view. I want to ask how can I loop through each record to perform my update function?
Mar 10 '08 #1
Share this Question
Share on Google+
2 Replies


P: 2
please use the following code to loop in your records displayed on the screen

Private Sub cmd_proc_Click()

Dim lotgroup As Variant
Dim aqty As Currency
Dim mycode As String
Dim balqty As Currency
Dim chkid As Integer
Dim myflag As Integer
Dim myid As Integer
Dim myrecno As Long

' For progress bar
Dim inti As Integer
Dim dblPct As Double

'Me.txtI.Visible = True
Me.txtPctComplete.Visible = True
Me.boxWhole.Visible = True
Me.Boxpct.Visible = True

fInLoop = True
fExitLoop = False
' For progress bar ends

lotgroup = ""
DoCmd.GoToRecord A_FORM, "mate_dist", A_FIRST
' updating ID field with 1 for the first record of the table
myid = 1
Forms!mate_dist![ID] = myid
myid = myid + 1
' updating ID ends, checking stock value for the first ident code

aqty = 0
chkid = 0
chkid = DCount("[ident_code]", "AVL_IMP", "[ident_code]= Forms!mate_dist![IDENT_CODE]")
If IsNull(chkid) Or chkid = 0 Then
Forms!mate_dist![MY_ERROR] = "Ident Code Not Found"
myflag = 1
aqty = 0
Else
aqty = DLookup("T_ON_HAND_QTY", "AVL_IMP", "[ident_code]= Forms!mate_dist![IDENT_CODE]")
End If

myrecno = [Forms]![mate_dist]![MyRecCnt]
'myrecno = 75000
mycode = Forms!mate_dist![IDENT_CODE]
balqty = aqty

Do
' For progress bar activating
dblPct = inti / myrecno
Me.txtPctComplete = dblPct
Me.Boxpct.Width = Me.boxWhole.Width * dblPct
'Me.txtI = inti
DoEvents
inti = inti + 1
' For progress bar activating ends

If lotgroup = IsNull(Forms!mate_dist![IDENT_CODE]) Then
MsgBox ("Quiting")
Exit Do
Else

Forms!mate_dist![AVAIL_QTY] = balqty

If balqty = 0 And Forms!mate_dist![REQD_QTY] = 0 Then
Forms!mate_dist![SITE_RESV_QTY] = balqty
Forms!mate_dist![SHORT_QTY] = Forms!mate_dist![REQD_QTY]
End If
If Forms!mate_dist![REQD_QTY] > balqty Then
Forms!mate_dist![SITE_RESV_QTY] = balqty
Forms!mate_dist![SHORT_QTY] = Forms!mate_dist![REQD_QTY] - balqty
Forms!mate_dist![MY_ERROR] = "Shortage Qty"
balqty = 0
End If
If Forms!mate_dist![REQD_QTY] <= balqty And balqty > 0 Then
Forms!mate_dist![SITE_RESV_QTY] = Forms!mate_dist![REQD_QTY]
Forms!mate_dist![SHORT_QTY] = 0
balqty = balqty - Forms!mate_dist![REQD_QTY]
End If

DoCmd.GoToRecord A_FORM, "mate_dist", A_NEXT
Forms!mate_dist![ID] = myid
myid = myid + 1

If Forms!mate_dist![IDENT_CODE] = mycode Then
If myflag = 1 Then
Forms!mate_dist![MY_ERROR] = "Ident Code Not Found"
End If
Else
aqty = 0
chkid = 0
myflag = 0
chkid = DCount("[ident_code]", "AVL_IMP", "[ident_code]= Forms!mate_dist![IDENT_CODE]")
If IsNull(chkid) Or chkid = 0 Then
Forms!mate_dist![MY_ERROR] = "Ident Code Not Found"
aqty = 0
myflag = 1
Else
aqty = DLookup("T_ON_HAND_QTY", "AVL_IMP", "[ident_code]= Forms!mate_dist![IDENT_CODE]")
End If
balqty = aqty
mycode = Forms!mate_dist![IDENT_CODE]
End If
On Error GoTo 0
On Error Resume Next
End If
Loop Until IsNull(Forms!mate_dist![IDENT_CODE])
fInLoop = False

MsgBox ("Process Completed...!")
DoCmd.SetWarnings True
DoCmd.GoToRecord A_FORM, "mate_dist", A_FIRST
End Sub
Mar 10 '08 #2

P: 12
please use the following code to loop in your records displayed on the screen

Private Sub cmd_proc_Click()

Dim lotgroup As Variant
Dim aqty As Currency
Dim mycode As String
Dim balqty As Currency
Dim chkid As Integer
Dim myflag As Integer
Dim myid As Integer
Dim myrecno As Long

' For progress bar
Dim inti As Integer
Dim dblPct As Double

'Me.txtI.Visible = True
Me.txtPctComplete.Visible = True
Me.boxWhole.Visible = True
Me.Boxpct.Visible = True

fInLoop = True
fExitLoop = False
' For progress bar ends

lotgroup = ""
DoCmd.GoToRecord A_FORM, "mate_dist", A_FIRST
' updating ID field with 1 for the first record of the table
myid = 1
Forms!mate_dist![ID] = myid
myid = myid + 1
' updating ID ends, checking stock value for the first ident code

aqty = 0
chkid = 0
chkid = DCount("[ident_code]", "AVL_IMP", "[ident_code]= Forms!mate_dist![IDENT_CODE]")
If IsNull(chkid) Or chkid = 0 Then
Forms!mate_dist![MY_ERROR] = "Ident Code Not Found"
myflag = 1
aqty = 0
Else
aqty = DLookup("T_ON_HAND_QTY", "AVL_IMP", "[ident_code]= Forms!mate_dist![IDENT_CODE]")
End If

myrecno = [Forms]![mate_dist]![MyRecCnt]
'myrecno = 75000
mycode = Forms!mate_dist![IDENT_CODE]
balqty = aqty

Do
' For progress bar activating
dblPct = inti / myrecno
Me.txtPctComplete = dblPct
Me.Boxpct.Width = Me.boxWhole.Width * dblPct
'Me.txtI = inti
DoEvents
inti = inti + 1
' For progress bar activating ends

If lotgroup = IsNull(Forms!mate_dist![IDENT_CODE]) Then
MsgBox ("Quiting")
Exit Do
Else

Forms!mate_dist![AVAIL_QTY] = balqty

If balqty = 0 And Forms!mate_dist![REQD_QTY] = 0 Then
Forms!mate_dist![SITE_RESV_QTY] = balqty
Forms!mate_dist![SHORT_QTY] = Forms!mate_dist![REQD_QTY]
End If
If Forms!mate_dist![REQD_QTY] > balqty Then
Forms!mate_dist![SITE_RESV_QTY] = balqty
Forms!mate_dist![SHORT_QTY] = Forms!mate_dist![REQD_QTY] - balqty
Forms!mate_dist![MY_ERROR] = "Shortage Qty"
balqty = 0
End If
If Forms!mate_dist![REQD_QTY] <= balqty And balqty > 0 Then
Forms!mate_dist![SITE_RESV_QTY] = Forms!mate_dist![REQD_QTY]
Forms!mate_dist![SHORT_QTY] = 0
balqty = balqty - Forms!mate_dist![REQD_QTY]
End If

DoCmd.GoToRecord A_FORM, "mate_dist", A_NEXT
Forms!mate_dist![ID] = myid
myid = myid + 1

If Forms!mate_dist![IDENT_CODE] = mycode Then
If myflag = 1 Then
Forms!mate_dist![MY_ERROR] = "Ident Code Not Found"
End If
Else
aqty = 0
chkid = 0
myflag = 0
chkid = DCount("[ident_code]", "AVL_IMP", "[ident_code]= Forms!mate_dist![IDENT_CODE]")
If IsNull(chkid) Or chkid = 0 Then
Forms!mate_dist![MY_ERROR] = "Ident Code Not Found"
aqty = 0
myflag = 1
Else
aqty = DLookup("T_ON_HAND_QTY", "AVL_IMP", "[ident_code]= Forms!mate_dist![IDENT_CODE]")
End If
balqty = aqty
mycode = Forms!mate_dist![IDENT_CODE]
End If
On Error GoTo 0
On Error Resume Next
End If
Loop Until IsNull(Forms!mate_dist![IDENT_CODE])
fInLoop = False

MsgBox ("Process Completed...!")
DoCmd.SetWarnings True
DoCmd.GoToRecord A_FORM, "mate_dist", A_FIRST
End Sub
Wow I didn't expect that fast reply. Thanks for the reply. I try DoCmd.GoToRecord A_FORM, "form name", A_FIRST and it works. ^^ Thank you so much :)
Mar 10 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.