?
Solved

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

Posted on 2014-02-26
7
Medium Priority
?
350 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
7 Comments
 
LVL 52

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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
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 52

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

578 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