R
Rafi
I have the following code that is invoked on another form (adjustments)
which results in a record set that I would like to be the source for another
form (Results). Note that Results can be an independent form or a sub form
(preferred)of Adjustments. If you look at the MsgBox in the code, it spells
out the three fields which I would like to display on the Results from which
is a continuous form.
Many thanks in advance
Public Sub Territory_Exit(Cancel As Integer)
Dim stSearch As String
Dim strSQL As String
Dim Counter As Long
Dim Q As Long
Dim db As Database
stSearch = Me.Region.Value & "-" & Me.Area.Value & "-" & Me.District.Value &
"-" & Me.Territory.Value
Q = Me.Quarter.Value
intSearchLen = Len(stSearch)
strSQL = "SELECT Distinct Qry_FY06Adj.Quarter, Qry_FY06Adj.Ref,
Qry_FY06Adj.Measure_Amt" _
& " FROM Qry_FY06Adj" _
& " WHERE Qry_FY06Adj.Quarter =" & Q & " and (Right((Qry_FY06Adj.Gaining),"
& Len(stSearch) & ") = """ & stSearch & """);"
Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)
With rst
If Not (.EOF And .BOF) Then
Counter = 1
Do Until .EOF
MsgBox ("Record = " & Counter & " /" & rst.Fields("Quarter") & " / " &
rst.Fields("Ref") & " / " & rst.Fields("Measure_Amt"))
Counter = Counter + 1
rst.MoveNext
Loop
End If
..Close
End With
Set rst = Nothing
Set db = Nothing
Counter = 1
End Sub
which results in a record set that I would like to be the source for another
form (Results). Note that Results can be an independent form or a sub form
(preferred)of Adjustments. If you look at the MsgBox in the code, it spells
out the three fields which I would like to display on the Results from which
is a continuous form.
Many thanks in advance
Public Sub Territory_Exit(Cancel As Integer)
Dim stSearch As String
Dim strSQL As String
Dim Counter As Long
Dim Q As Long
Dim db As Database
stSearch = Me.Region.Value & "-" & Me.Area.Value & "-" & Me.District.Value &
"-" & Me.Territory.Value
Q = Me.Quarter.Value
intSearchLen = Len(stSearch)
strSQL = "SELECT Distinct Qry_FY06Adj.Quarter, Qry_FY06Adj.Ref,
Qry_FY06Adj.Measure_Amt" _
& " FROM Qry_FY06Adj" _
& " WHERE Qry_FY06Adj.Quarter =" & Q & " and (Right((Qry_FY06Adj.Gaining),"
& Len(stSearch) & ") = """ & stSearch & """);"
Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)
With rst
If Not (.EOF And .BOF) Then
Counter = 1
Do Until .EOF
MsgBox ("Record = " & Counter & " /" & rst.Fields("Quarter") & " / " &
rst.Fields("Ref") & " / " & rst.Fields("Measure_Amt"))
Counter = Counter + 1
rst.MoveNext
Loop
End If
..Close
End With
Set rst = Nothing
Set db = Nothing
Counter = 1
End Sub