Solved

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

Posted on 2014-02-26
7
322 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 50

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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 50

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 500 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

737 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