Karen Schaefer
asked on
Looping thru Fields of a single row of data in a table
I am comparing the value (s) in a table that will always have a single row, but I need to check the value of different fields to see if it matches my criteria.
see '>>>>> - also need to handle if the FreezeQ(i) = 0 - since they are checkboxes
and compare that field(FreezeQ & (i) and then check another table tblSummaryQuartersFrozen for the indicated quarter and is if the data already exists.
the problem I am currently having is it loops thru the first time on the strsql, but then get an "NO Current Record" error.
'Checks for which quarter needs to be updated in the tblSummaryQuartersFrozen
strSQL = "Select Year, FreezeQ1, FreezeQ2, FreezeQ3, FreezeQ4" & _
" FROM tblcurrent"
Set rs = curDB.OpenRecordset(strSQL)
see '>>>>> - also need to handle if the FreezeQ(i) = 0 - since they are checkboxes
and compare that field(FreezeQ & (i) and then check another table tblSummaryQuartersFrozen for the indicated quarter and is if the data already exists.
the problem I am currently having is it loops thru the first time on the strsql, but then get an "NO Current Record" error.
Private Sub CmdAppendFrozenQuarters_Click()
Dim curDB As DAO.Database
Dim strSQL As String, strSQL1 As String
Dim rs As DAO.Recordset, rs1 As DAO.Recordset
Dim i As Integer
On Error GoTo CmdAppendFrozenQuarters_Click_Error
Set curDB = CurrentDb
DoCmd.SetWarnings (False)
Call _
MsgBox("This function should only be performed after each Qtr End.", _
vbCritical, "Quarterly Summary Update!!")
'Checks for which quarter needs to be updated in the tblSummaryQuartersFrozen
strSQL = "Select Year, FreezeQ1, FreezeQ2, FreezeQ3, FreezeQ4" & _
" FROM tblcurrent"
Set rs = curDB.OpenRecordset(strSQL)
'Checks for which quarter already exists in the tblSummaryQuartersFrozen
strSQL1 = "SELECT Quarter" & _
" FROM tblSummaryQuartersFrozen" & _
" WHERE (((tblSummaryQuartersFrozen.[Year])=Year(Date())))" & _
" GROUP BY Quarter"
Set rs1 = curDB.OpenRecordset(strSQL1)
For i = 1 To 4
'rs1.MoveFirst
Do Until rs.EOF
'>>>>> If rs.Fields("FreezeQ" & (i)) = -1 Then
If rs1.RecordCount = 0 Then
curDB.Execute ("INSERT INTO tblSummaryQuartersFrozen" & _
" SELECT tblSummary.*" & _
" FROM tblSummary" & _
" WHERE (Quarter = '" & i & "') And ((tblSummary.[Year])=Year(Date()))")
On Error GoTo 0
ElseIf rs1.Fields("Quarter") = (i) Then
rs.MoveNext
ElseIf rs1.Fields("Quarter") <> i Then
curDB.Execute ("INSERT INTO tblSummaryQuartersFrozen" & _
" SELECT tblSummary.*" & _
" FROM tblSummary" & _
" WHERE (Quarter = '" & i & "') And ((tblSummary.[Year])=Year(Date()))")
End If
End If
Loop
rs.MoveNext
Next i
DoCmd.SetWarnings (True)
On Error GoTo 0
Exit Sub
CmdAppendFrozenQuarters_Click_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CmdAppendFrozenQuarters_Click of VBA Document Form_FrmStart"
End Sub
ASKER
but I need to compare the value of the fields within that row - so do you know the best method to check and see if any of the Freezeqtr = -1 and be able to handle if they = 0.
if FreezeQtr = -1 then
check the Quarter value in table Summary - if FreezeQtr1 = - 1 and summary.Qtr = 1 then move to the next FreezeQtr field to check for its value and loop thru -
Note Here is my latest attempt - however on the 2nd pass thru the loop it is not recognizing that the table has been updated from the previous pass
Still getting no current record on second pass
if FreezeQtr = -1 then
check the Quarter value in table Summary - if FreezeQtr1 = - 1 and summary.Qtr = 1 then move to the next FreezeQtr field to check for its value and loop thru -
Note Here is my latest attempt - however on the 2nd pass thru the loop it is not recognizing that the table has been updated from the previous pass
Private Sub CmdAppendFrozenQuarters_Click()
Dim curDB As DAO.Database
Dim strSQL As String, strSQL1 As String
Dim rs As DAO.Recordset, rs1 As DAO.Recordset
Dim i As Integer
Dim fld As Field
On Error GoTo CmdAppendFrozenQuarters_Click_Error
Set curDB = CurrentDb
DoCmd.SetWarnings (False)
Call _
MsgBox("This function should only be performed after each Qtr End.", _
vbCritical, "Quarterly Summary Update!!")
'Checks for which quarter already exists in the tblSummaryQuartersFrozen
strSQL = "SELECT Quarter" & _
" FROM tblSummaryQuartersFrozen" & _
" WHERE (((tblSummaryQuartersFrozen.[Year])=Year(Date())))" & _
" GROUP BY Quarter"
Set rs = curDB.OpenRecordset(strSQL)
'Checks for which quarter needs to be updated in the tblSummaryQuartersFrozen
strSQL1 = "Select Year, FreezeQ1, FreezeQ2, FreezeQ3, FreezeQ4" & _
" FROM tblcurrent"
Set rs1 = curDB.OpenRecordset(strSQL1)
For i = 1 To 4
If rs.RecordCount = 0 And rs1.Fields("FreezeQ" & (i)) = -1 Then
curDB.Execute ("INSERT INTO tblSummaryQuartersFrozen" & _
" SELECT tblSummary.*" & _
" FROM tblSummary" & _
" WHERE (Quarter = '" & i & "') And ((tblSummary.[Year])=Year(Date()))")
ElseIf rs.RecordCount > 0 And rs1.Fields("FreezeQ" & (i)) = -1 And rs.Fields("Quarter") = (i) Then
curDB.Execute ("INSERT INTO tblSummaryQuartersFrozen" & _
" SELECT tblSummary.*" & _
" FROM tblSummary" & _
" WHERE (Quarter = '" & i & "') And ((tblSummary.[Year])=Year(Date()))")
End If
Next i
DoCmd.SetWarnings (True)
On Error GoTo 0
Exit Sub
CmdAppendFrozenQuarters_Click_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CmdAppendFrozenQuarters_Click of VBA Document Form_FrmStart"
End Sub
Still getting no current record on second pass
ASKER
still looking for a simple solution.
Can you post table structure? At what point do you exit the rs?
dim i as integer
for i = 0 to rs.fields.count -1
if rs.fields(i).name like 'freezeqtr*' then
if rs.fields(i).value = somevalue then
next i
else
exit for
endif
endif
next i
roughly speaking.
dim i as integer
for i = 0 to rs.fields.count -1
if rs.fields(i).name like 'freezeqtr*' then
if rs.fields(i).value = somevalue then
next i
else
exit for
endif
endif
next i
roughly speaking.
> on the 2nd pass thru the loop it is not recognizing that the
> table has been updated from the previous pass
That's because you aren't updating anything. Your SQL inserts new records.
/gustav
> table has been updated from the previous pass
That's because you aren't updating anything. Your SQL inserts new records.
/gustav
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
this did the trick thanks.
So cut it down to the For .. Next loop only.
/gustav