Solved

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

Posted on 2014-02-26
7
316 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

830 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