?
Solved

Looping thru Fields of a single row of data in a table

Posted on 2014-02-26
7
Medium Priority
?
332 Views
Last Modified: 2014-03-06
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.

  '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

Open in new window

0
Comment
Question by:Karen Schaefer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39889856
If you have one row only, then there is no records to loop.

So cut it down to the For .. Next loop only.

/gustav
0
 

Author Comment

by:Karen Schaefer
ID: 39890054
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

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

Open in new window


Still getting no current record on second pass
0
 

Author Comment

by:Karen Schaefer
ID: 39890241
still looking for a simple solution.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 26

Expert Comment

by:jerryb30
ID: 39890822
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.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39891163
> 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
0
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 2000 total points
ID: 39899678
Hi,

as far as I understand you want to insert all quarter results of the current year into the frozen summary table which are not already inserted there?

If that's the case you can remove the complete VBA and create a new query with this:

INSERT INTO tblSummaryQuartersFrozen
SELECT * FROM tblSummary AS S
WHERE NOT EXISTS (SELECT 1 FROM tblSummaryQuartersFrozen AS SQF WHERE SQF.[Year] = S.[Year] AND SQF.Quarter = S.Quarter)
AND S.[Year]=Year(Date())

Open in new window


Always try to think SQL first before you use VBA.

BTW: You should not use field names like "Year" as this is a keyword as you can see in your own SQL when using the "Year" function.

Cheers,

Christian
0
 

Author Closing Comment

by:Karen Schaefer
ID: 39910059
this did the trick thanks.
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question