Solved

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

Posted on 2014-02-26
7
305 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 49

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 49

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Why can't I get my report to SORT correctly? 5 27
data analyst 3 49
DBF to ... Converter 5 38
Access Excel export not behaving 2 25
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

912 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now