Endless loop driving me crazy... any ideas?

[karatekid]

Hi guys,

I'm new on this forum and with Access VBA, and I've been struggling with a VBA function for almost 2 days now. What I have is a query based on an union query, and I would like to categorize the records in the union query according to 24 criteria. What I have come up with is a VBA function that checks the records and assigns certain category codes that I have defined. The purpose is to then have the category codes check against time targets for each one of them.

The trouble is that once the code runs, it is stuck in an endless loop, and I cannot seem to make it work.

Here's the code. At first there was a Select Case statement, but I've changed it to multiple If statements. I have no problem reverting to the Select Case statement once the loop issue is solved:

Function CatCode(vTargetGroup, vGroupByType, vActionAnsType, vAdminAns, vComplType)

    Dim db As DAO.Database
    Dim tdf As DAO.Recordset

    
    Set db = CurrentDb
    Set tdf = db.OpenRecordset("Union_CC_Written")
    With tdf
        vTargetGroup = tdf("TargetGroup").Value
        vGroupByType = tdf("GroupByType").Value
        vActionAnsType = tdf("Action").Value
        vAdminAns = tdf("AdminAns").Value
        vComplType = tdf("Complaint Type EN").Value
    End With

    On Error GoTo 0

    If tdf.RecordCount = 0 Then Exit Function

    tdf.MoveFirst

    Do Until tdf.EOF = True

        If tdf("LogonName").Value = "null" Then
            CatCode = "Pending"
        End If


        If vTargetGroup = "voucher" Then
             CatCode = "IllegVoucher"
        End If

        If vGroupByType = "Residential" And vTargetGroup = _
           "Consumer - Internal use only" And vActionAnsType = "STATISTICA" And _
           vAdminAns = "Nefondata" And vComplType = "Consumer - Internal use only" Then
             CatCode = "ConsInternal_N(STAT)"
        End If

        If vGroupByType = "Residential" And vTargetGroup = _
           "Consumer - Internal use only" And vAdminAns = "Fondata" And vComplType = _
           "Consumer - Internal use only" Then
             CatCode = "ConsInternal_F"
        End If

        If vGroupByType = "Residential" And vTargetGroup = "taxare" And _
           vActionAnsType = "WRITTEN" And vAdminAns = "Fondata" Then
             CatCode = "ConsCharging_W_F"
        End If

        If vGroupByType = "Residential" And vTargetGroup = "taxare" And _
           vActionAnsType = "WRITTEN" And vAdminAns = "Nefondata" Then
             CatCode = "ConsCharging_W_N"
        End If

        If vGroupByType = "Residential" And vTargetGroup = "taxare" And vAdminAns = _
           "Fondata" Then
             CatCode = "ConsCharging_CC_F"
        End If

        If vGroupByType = "Residential" And vTargetGroup = "taxare" And vAdminAns = _
           "Nefondata" Then
             CatCode = "ConsCharging_CC_N"
        End If

        If vGroupByType = "Residential" And vTargetGroup = "tehnice" And _
           vActionAnsType = "WRITTEN" Then
             CatCode = "ConsTech_W"
        End If

        If vGroupByType = "Residential" And vTargetGroup = "diverse" And _
           vActionAnsType = "WRITTEN" Then
             CatCode = "ConsOther_W"
        End If

        If vGroupByType = "Residential" And vTargetGroup = "diverse" Then
             CatCode = "ConsOther_CC"
        End If

        If vGroupByType = "Residential" And vTargetGroup = "tehnice" Then
             CatCode = "ConsTech_CC"
        End If

        If vGroupByType = "Residential" And vActionAnsType = "OTHER" Then
             CatCode = "ConsToS_OTHER"
        End If

        If vGroupByType = "Residential" And vActionAnsType = "STATISTICA" Then
             CatCode = "ConsToS_STAT"
        End If


        If vGroupByType = "Corporate" And vTargetGroup = _
           "Corporate - Internal use only" And vComplType = _
           "Corporate - Internal use only" Then
             CatCode = "Corp_internal"
        End If

        If vGroupByType = "Corporate" And vTargetGroup = "diverse" And _
           vActionAnsType = "WRITTEN" Then
             CatCode = "CorpOther_W"
        End If

        If vGroupByType = "Corporate" And vTargetGroup = "taxare" And _
           vActionAnsType = "WRITTEN" Then
             CatCode = "CorpCharging_W"
        End If

        If vGroupByType = "Corporate" And vTargetGroup = "tehnice" And _
           vActionAnsType = "WRITTEN" Then
             CatCode = "CorpTech_W"
        End If

        If vGroupByType = "Corporate" And vTargetGroup = "diverse" Then
             CatCode = "CorpOther_CC"
        End If

        If vGroupByType = "Corporate" And vTargetGroup = "taxare" Then
             CatCode = "CorpCharging_CC"
        End If

        If vGroupByType = "Corporate" And vTargetGroup = "tehnice" Then
             CatCode = "CorpTech_CC"
        End If

        If vGroupByType = "Corporate" And vActionAnsType = "OTHER" Then
             CatCode = "CorpToS_OTHER"
        End If

        If vGroupByType = "Corporate" And vActionAnsType = "STATISTICA" Then
             CatCode = "CorpToS_STAT"
        End If


        If vTargetGroup = "Resending a written answer" And vActionAnsType = _
           "RETRANSMIS" And vComplType = "Resending a written answer" Then
             CatCode = "RetryAns_R"
        End If

        If vTargetGroup = "Resending a written answer" And vActionAnsType = _
           "WRITTEN" And vComplType = "Resending a written answer" Then
             CatCode = "RetryAns_R"
        End If

        tdf.MoveNext

    Loop
 
        MsgBox "Query executed", vbInformation
        tdf.Close
        Set db = Nothing
        Set tdf = Nothing
   
End Function

Any ideas would be much appreciated, as I am due with this on Monday (so no pressure ).

Also, is there a way to do this using SQL rather than VBA, given the multiple conditions (24)?

Thank you all for your input,
karatekid

 

[Galaxiom]

Try: While Not .EOF

The main thing I noticed is a lot of literal text being processed. This would be better done by storing and comparing integers and having a lookup for display. It would be many, many times faster and much simpler to maintain.

BTW, tdf is an unconventional choice of a variable name for a recordset. It is more often encountered for a table definition.

 

[karatekid]

Hey Galaxiom,

Thanks for your answer. I've changed the name of the variable to myQuery, and I've used the While...Wend construct you've suggested, but I get the same situation.

The main thing I noticed is a lot of literal text being processed. This would be better done by storing and comparing integers and having a lookup for display. It would be many, many times faster and much simpler to maintain.

Could you elaborate a bit on that? Would I have to set up an ID system for the source tables, and then go from there?

Cheers,
karatekid

 

[stopher]

Welcome to the forum.

I see several problems here, some critical.

As well as the point raised by Galaxiom, you said that you are trying to update the recordset? But I can't see anywhere in the code that does that.

In any case, if your data source is a union query you won't be able to update it. You can't update union queries. Big problem!

The use of union queries sometime suggests bad data structure. Maybe your data should all be in one table?

You have created a function with some variables as arguments. But then you set the arguments in the With...End With block. So why have the arguments passed?

The With..End With block is setting the variables to the values of the first record in the recordset. Is that what you want? Or should this block be in the recordset loop?

If there are no records in the Union query, the code will error when it gets to the With...End With block because the block assumes that there is a record to read and will error if there isn't. Not that your Recordcount =0 check is after this.

If you need to do this quickly then I would suggest simply writing a series of queries - one for each case. Then just run them in series using ether a macro or VBA. The benefit of this approach is it's very easy to set up and maintain. The downside is there could be a performance issue (because you cycle the records multiple times). But I you want it working quick then that's what I'd do.

If you are happy to continue on this route then you'll need to update each source table independently - not via a union.

hth
Chris

 

[karatekid]

Hi Chris,

Thanks for your answer. I am not trying to update the union query, I am trying to create another query based on that union query, and then add a Category Code field that would show me the category in which my record falls. The union query will always have at least one record, as the data source is an activity report for an entire team.

I am using the With...End with loop to check the records in the union query, and then, based on the condition that I have set up, write the category in which the record falls.

So I want my code to do this: take each record from the data source query (Union_CC_Written), read the data in fields TargetGroup, GroupByType, Action, AdminAns and Complaint Type EN and then, based on the combination of the values of these fields, assign to each record the Category Code that I have set up.

Regarding the series of queries for each criteria, can you tell me how will I do this? I have no problem writing them, but how do I make them do what I want via macro? Please keep in mind that later on I will have to create a crosstab query that would actually represent the final report (meaning, for each of the names in LogonName, I will have to create a statistics of how many records/complaints they have processed, how many of them in the/off target time, and how many of them are still pending, with no action whatsoever done upon them).

Please let me know if you need further details.

Cheers,
karatekid

 

[karatekid]

Another idea, guys: suppose I would put the criteria in the table that contains the targets, is there a way that I could query against the table to find out the category codes (i.e., have separate columns for TargetGroup, GroupByType, Action, AdminAns and Complaint Type EN directly in the table, then check against them)? Is that possible?

Thank you for your input,
karatekid

 

[stopher]

Would you be happy to use a temporary table?

In other words, create a Make Table query that creates a temp table using all the fields from your UNION query and also adds a new field CatCode which is blank.

Then you can run your code using the temp table as the source and use it to update the value of CatCode (although it does need fixing which we can help with).

Then you can run your crosstab from the temp table.

The downside to all this is you have to rememer to update the temp table before running the crosstab ... but if your short on time...

Chris

 

[stopher]

There are many ways to approach your problem but I just think if your deadline is tomorrow, you just need to get a working solution.

 

[karatekid]

I haven't thought of the temporary table solution, Chris. Thank you for your suggestion

But how to delete the records in the table before running the temporary query again? Can I do that via macro (I already have some macros set up which are updating the source reports)? Unfortunately, deleting the temporary table records manually is not a solution for me, as I would not want the users going through the data themselves...

 

[stopher]

I'm just wring some code for you.

A Make Table query will overwrite any old table. No need to delete records.

 

[stopher]

Make sure you do not have a table called tblResults before running this:

When you've run it, take a look at the table tblResults to see if is what you expect. If it doesn't run, please post your database with some sample data.

Public Sub CatCode()

    Dim db As DAO.Database
    Dim tdf As DAO.Recordset
    Dim strCatCode As String
    
    'Delete the results table
    CurrentDb.Execute "DROP Table tblResults"
    
    'create the reasults table
    CurrentDb.Execute "SELECT *, '' AS CatCode INTO tblResults FROM Union_CC_Written"
    
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblResults")
    
    With rs
    
        If .RecordCount = 0 Then
            Exit Sub
        End If
    
        Do While Not .EOF = True
               
            If !LogonName = "null" Then
                strCatCode = "Pending"
            End If
    
            If !TargetGroup = "voucher" Then
                strCatCode = "IllegVoucher"
            End If
    
            If !GroupByType = "Residential" And !TargetGroup = _
               "Consumer - Internal use only" And !Action = "STATISTICA" And _
               !AdminAns = "Nefondata" And ![Complaint Type EN] = "Consumer - Internal use only" Then
                 strCatCode = "ConsInternal_N(STAT)"
            End If
    
            If !GroupByType = "Residential" And !TargetGroup = _
               "Consumer - Internal use only" And !AdminAns = "Fondata" And ![Complaint Type EN] = _
               "Consumer - Internal use only" Then
                strCatCode = "ConsInternal_F"
            End If
    
            If !GroupByType = "Residential" And !TargetGroup = "taxare" And _
               !Action = "WRITTEN" And !AdminAns = "Fondata" Then
                 strCatCode = "ConsCharging_W_F"
            End If
    
            If !GroupByType = "Residential" And !TargetGroup = "taxare" And _
               !Action = "WRITTEN" And !AdminAns = "Nefondata" Then
                 strCatCode = "ConsCharging_W_N"
            End If
    
            If !GroupByType = "Residential" And !TargetGroup = "taxare" And !AdminAns = _
               "Fondata" Then
                 strCatCode = "ConsCharging_CC_F"
            End If
    
            If !GroupByType = "Residential" And !TargetGroup = "taxare" And !AdminAns = _
               "Nefondata" Then
                 strCatCode = "ConsCharging_CC_N"
            End If
    
            If !GroupByType = "Residential" And !TargetGroup = "tehnice" And _
               !Action = "WRITTEN" Then
                 strCatCode = "ConsTech_W"
            End If
    
            If !GroupByType = "Residential" And !TargetGroup = "diverse" And _
               !Action = "WRITTEN" Then
                 strCatCode = "ConsOther_W"
            End If
    
            If !GroupByType = "Residential" And !TargetGroup = "diverse" Then
                 strCatCode = "ConsOther_CC"
            End If
    
            If !GroupByType = "Residential" And !TargetGroup = "tehnice" Then
                 strCatCode = "ConsTech_CC"
            End If
    
            If !GroupByType = "Residential" And !Action = "OTHER" Then
                 strCatCode = "ConsToS_OTHER"
            End If
    
            If !GroupByType = "Residential" And !Action = "STATISTICA" Then
                 strCatCode = "ConsToS_STAT"
            End If
    
    
            If !GroupByType = "Corporate" And !TargetGroup = _
               "Corporate - Internal use only" And ![Complaint Type EN] = _
               "Corporate - Internal use only" Then
                 strCatCode = "Corp_internal"
            End If
    
            If !GroupByType = "Corporate" And !TargetGroup = "diverse" And _
               !Action = "WRITTEN" Then
                 strCatCode = "CorpOther_W"
            End If
    
            If !GroupByType = "Corporate" And !TargetGroup = "taxare" And _
               !Action = "WRITTEN" Then
                 strCatCode = "CorpCharging_W"
            End If
    
            If !GroupByType = "Corporate" And !TargetGroup = "tehnice" And _
               !Action = "WRITTEN" Then
                 strCatCode = "CorpTech_W"
            End If
    
            If !GroupByType = "Corporate" And !TargetGroup = "diverse" Then
                 strCatCode = "CorpOther_CC"
            End If
    
            If !GroupByType = "Corporate" And !TargetGroup = "taxare" Then
                 strCatCode = "CorpCharging_CC"
            End If
    
            If !GroupByType = "Corporate" And !TargetGroup = "tehnice" Then
                 strCatCode = "CorpTech_CC"
            End If
    
            If !GroupByType = "Corporate" And !Action = "OTHER" Then
                 strCatCode = "CorpToS_OTHER"
            End If
    
            If !GroupByType = "Corporate" And !Action = "STATISTICA" Then
                 strCatCode = "CorpToS_STAT"
            End If
    
    
            If !TargetGroup = "Resending a written answer" And !Action = _
               "RETRANSMIS" And ![Complaint Type EN] = "Resending a written answer" Then
                 strCatCode = "RetryAns_R"
            End If
    
            If !TargetGroup = "Resending a written answer" And !Action = _
               "WRITTEN" And ![Complaint Type EN] = "Resending a written answer" Then
                 strCatCode = "RetryAns_R"
            End If
            
            .Edit
            !CatCode = strCatCode
            .Update
    
            .MoveNext

        Loop
    
    End With

    MsgBox "Query executed", vbInformation
        
    Set db = Nothing
    Set rs = Nothing
   
End Sub

 

[karatekid]

Wow, thank you so much, Chris. You're a lifesaver. I will try it as soon as I get to my computer, and get back to you.

Cheers,
karatekid

 

[vbaInet]

As a suggestion, you can also use Chris' function as a new column inside your query and pass the 5 values, perform the check and return the value. No updates to a table will be required in this case.

 

[stopher]

As a suggestion, you can also use Chris' function as a new column inside your query and pass the 5 values, perform the check and return the value. No updates to a table will be required in this case.

The penny drops. I think must be what the OP was trying to do at the start. I just didn't see it - doh. Very good point.

Chris

 

[karatekid]

Hey guys,

I've tested Chris' code and it works! Hats off to you, you're great!

Now, I've only got to tweak a form to export the data and I'll be home free

Big thanks to you, ladies and gentlemen!

Have a great evening, and a successful week!
karatekid

 

[Galaxiom]

The main thing I noticed is a lot of literal text being processed. This would be better done by storing and comparing integers and having a lookup for display. It would be many, many times faster and much simpler to maintain.

Could you elaborate a bit on that? Would I have to set up an ID system for the source tables, and then go from there?

Set up another table with an Integer ID field and a field for the description. Then store the integer instead of the text in the main table.

The display on the form or report is done using combos to display the text and store the number. Firstly this reduces the storage significantly.

However the big gain is instead of the likes of:
"WHERE whatever = 'Consumer - Internal use only'"

use the likes of:
WHERE whatever = 1

Searching for the integer would be up to fifty times more efficient than comparing those strings.

 

[smig]

this part is totaly unclear to me

Function CatCode([COLOR=darkred]vTargetGroup, vGroupByType, vActionAnsType, vAdminAns, vComplType[/COLOR])

    Dim db As DAO.Database
    Dim tdf As DAO.Recordset

    
    Set db = CurrentDb
    Set tdf = db.OpenRecordset("Union_CC_Written")
    With tdf
        [COLOR=darkred]vTargetGroup[/COLOR] = tdf("TargetGroup").Value
        [COLOR=darkred]vGroupByType[/COLOR] = tdf("GroupByType").Value
        [COLOR=darkred]vActionAnsType[/COLOR] = tdf("Action").Value
        [COLOR=darkred]vAdminAns[/COLOR] = tdf("AdminAns").Value
        [COLOR=darkred]vComplType[/COLOR] = tdf("Complaint Type EN").Value
    End With

you send the variables to the function and change them in the function (Before you do anything else with them)

also, you take the values of non specific record

 

[stopher]

this part is totaly unclear to me

Function CatCode([COLOR=darkred]vTargetGroup, vGroupByType, vActionAnsType, vAdminAns, vComplType[/COLOR])

    Dim db As DAO.Database
    Dim tdf As DAO.Recordset

    
    Set db = CurrentDb
    Set tdf = db.OpenRecordset("Union_CC_Written")
    With tdf
        [COLOR=darkred]vTargetGroup[/COLOR] = tdf("TargetGroup").Value
        [COLOR=darkred]vGroupByType[/COLOR] = tdf("GroupByType").Value
        [COLOR=darkred]vActionAnsType[/COLOR] = tdf("Action").Value
        [COLOR=darkred]vAdminAns[/COLOR] = tdf("AdminAns").Value
        [COLOR=darkred]vComplType[/COLOR] = tdf("Complaint Type EN").Value
    End With

you send the variables to the function and change them in the function (Before you do anything else with them)

also, you take the values of non specific record

Yes, I pointed that out in my first post.

I thought the OP was trying to manipulate recordsets. But, as vbaInet pointed out, the code could be used as a function which I think was the OP's original intention.

So all the recordset stuff can be removed and just use the parameters. Then the function can just be used in a query.

Chris

 

[smig]

Yes, I pointed that out in my first post.

I thought the OP was trying to manipulate recordsets. But, as vbaInet pointed out, the code could be used as a function which I think was the OP's original intention.

So all the recordset stuff can be removed and just use the parameters. Then the function can just be used in a query.

Chris

OK, sorry, didn't notice it in your first post

 

[karatekid]

Thanks a lot for all your answers, guys, you've really helped me save my behind today My little project has gone for testing to my colleagues that need it.

Galaxiom, smig,

I will try to implement your suggestions. Right now, I'm just basking in the glory of getting it done in time .

Cheers,
karatekid