Feebly trying to evaluate several fields by VBA

[Gingertrees]

Trying to classify businesses by a number of factors put together. I'd like to do this in a query; number of variable factors leads me to believe some of this needs to be a UDF in VBA. The classification system would be similar to the packages you can buy at a car wash (except pretend you can order each option separately, e.g. wash, undercarriage, but not tire-bright):

Good: wash, hot wax, hot dry
Better: wash, undercarriage flush, tire wash, hot wax, hot dry
Best: wash, undercarriage flush, tire-bright, Premium wax, spot-free rinse, soft-cloth

So the table would be wax (hot/Premium), undercarriage (yes/no), tires (NA/tirewash/tirebright), spotfree (yes/no), dry (hot/softcloth)

I need to figure out how to say
"if wax=premium, undercarriage=yes, tires=tire-bright, spotfree=yes, dry=softcloth, THEN type=BEST
Else if wax=hot OR premium, undercarriage=yes, tires=tirewash OR tire-bright, spotfree=no, dry=hot, THEN type=BETTER
Else if wax=hot OR premium, dry=hot, undercarriage=no, tires=no, spotfree=no, THEN GOOD
Otherwise type = NA"

Option Compare Database

Public Function TypeAchieved() As Variant
Dim rs As DAO.Recordset
Dim db As Database

Set db = CurrentDb

Set rs = db.Recordsets("CarPkgs")

‘pseudocode…
‘If (And(rs!wax=”premium”, rs!undercarriage=”yes”, rs!tires=”tire-bright”, rs!spotfree=”yes”, ‘rs!dry=”softcloth”)) Then TypeAchieved = “Best”
 
‘Else if(And(rs!undercarriage=”yes”, rs!tires/=”NA”,rs!spotfree=”no”)) Then TypeAchieved = “Better”
 
‘Else if(And(rs!wax=”hot”,rs!dry=”hot”)) Then TypeAchieved = “Good”
 
‘Else
‘End if

End Function

 

[xenou]

Yep - I think you got it. By putting it in a sub you could run the sub to either

1) update a field in the table (if you can add a field to the table), or
2) copy the data to a temp table with the extra added field.

So you'd run your sub first, then publish your reports. The sub would work as in our earlier examples (you open a recordset and loop the records in the table).

 

[Gingertrees]

Tried so hard to figure this out on my own, just failing all over the place...
OK, so the recordset "carpkgs" is a different table from the new table "Table2" I created for the TypeAchieved results. I could find anything anywhere about how to refer to two different tables in this sort of thing, so I kind of winged it (failingly):

Public Sub TypeAchieved()
Dim rs As DAO.Recordset
Dim a2 As DAO.Recordset '///MAYBE this is how to refer to the 2nd table???
Dim x As Long

Dim TypeAchieved As Variant '////it appeared to need a defn; is Variant right? Or String? Or ____???

    Set rs = CurrentDb.OpenRecordset("carpkgs")
    Set a2 = CurrentDb.OpenRecordset("Table2") '///table with ID fields and field "Category", which needs to be updated with TypeAchieved
    
    Do While Not rs.EOF
        With rs
            TypeAchieved = ""
            If TypeAchieved = "" Then
                x = 0
                x = x + IIf(rs!wax = "hot", 1, 0)
            '/// (etc etc )
                If x = 3 Then
                    TypeAchieved = "Good"
                    Else: TypeAchieved = "N/A"
                End If
            End If
'////HERE'S WHAT I GUESSED SHOULD BE IN PLACE OF "SOME FIELD" IN YOUR CODE,
'////BUT IT'S NOT IN rs...................///////
            a2!Category= IIf(TypeAchieved = "", Null, TypeAchieved)
            .MoveNext
        End With
    Loop
    
    rs.Close
    Set rs = Nothing

End Sub

1) I can't tell if it's actually running this code based on the info in Table2 or not (should I open the table 1st? Add more code???)
2) Run-time error at my version of your SomeField: "3020: Update or CancelUpdate without AddNew or Edit."
3) field Category currently has a single space in it (couldn't have a blank field in a maketable query) - will the code replace that " " with the type achieved?

I'm sorry I'm a bit out of my depth on this whole mess... any help is appreciated.

 

[xenou]

Okay. So you don't really need two recordsets. First just copy the data into the temp table (Table2). Then you just work with Table2. You can dim TypeAchieved as a String since it will hold string values ("Great", "Good", "N/A", etc.)

Here's how I would probably write this. If it still isn't working I would post your complete code with (possible) some sample records from carpkgs so that we can both test the same data and get the same results.

[COLOR="Navy"]Public[/COLOR] [COLOR="Navy"]Sub[/COLOR] TypeAchieved()
[COLOR="Navy"]Dim[/COLOR] rs [COLOR="Navy"]As[/COLOR] DAO.Recordset
[COLOR="Navy"]Dim[/COLOR] x [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] TypeAchieved [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
    
    [COLOR="SeaGreen"]'//Turn off Warnings for the next two commands[/COLOR]
    DoCmd.SetWarnings False

    [COLOR="SeaGreen"]'//Truncate Table2 (Temp Table)[/COLOR]
    DoCmd.RunSQL "DELETE * FROM Table2;"
    
    [COLOR="SeaGreen"]'//Populate Table2 with data from carpkgs table - PLEASE EDIT field names To fields from carpkgs[/COLOR]
    DoCmd.RunSQL "INSERT INTO Table2 SELECT Field1, Field2, Field3, Field4, Field5 FROM carpkgs;"

    [COLOR="SeaGreen"]'//Turn back on alerts[/COLOR]
    DoCmd.SetWarnings True
    
    [COLOR="SeaGreen"]'//Table2 is now a copy of carpkgs, but has the extra field "Category" in Table2[/COLOR]
    [COLOR="Navy"]Set[/COLOR] rs = CurrentDb.OpenRecordset("Table2", dbOpenTable)
    
    [COLOR="SeaGreen"]'//Loop each record and update the Category field[/COLOR]
    [COLOR="Navy"]Do[/COLOR] [COLOR="Navy"]While[/COLOR] [COLOR="Navy"]Not[/COLOR] rs.EOF
        [COLOR="Navy"]With[/COLOR] rs
            TypeAchieved = ""
            [COLOR="Navy"]If[/COLOR] TypeAchieved = "" [COLOR="Navy"]Then[/COLOR]
                x = 0
                x = x + IIf(rs!wax = "hot", 1, 0)
            [COLOR="SeaGreen"]'/// (etc etc )[/COLOR]
                [COLOR="Navy"]If[/COLOR] x = 3 [COLOR="Navy"]Then[/COLOR]
                    TypeAchieved = "Good"
                    Else: TypeAchieved = "N/A"
                [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
            [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
            
            [COLOR="SeaGreen"]'//Update Category Field[/COLOR]
            !Category = IIf(TypeAchieved = "", Null, TypeAchieved)
            [COLOR="SeaGreen"]'//Move to next record and repeat loop until end of records is reached[/COLOR]
            .MoveNext
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
    [COLOR="Navy"]Loop[/COLOR]
    
    rs.Close
    [COLOR="Navy"]Set[/COLOR] rs = [COLOR="Navy"]Nothing[/COLOR]

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

 

[Gingertrees]

Runtime error 3127 The Insert Into statement contains the following unknown field name: (Anything that wasn't already in Table 2 when I started)

If my existing table2 columns are BusID and Earned, it errors on the first Field listed in the Insert statement (e.g. "wax"). If wax was there, it doesn't error until it hits undercarriage, etc. I tried removing all but Earned column from Table 2 - then it errored on BusID.

I hope there's just some simple word I'm missing here. If not I take some time to massage my data (which isn't really related to car wash) into my example.

 

[Gingertrees]

OK, went ahead and massaged some data. Here are 5 records from "Carpkgs", in CSV format:

ID,BusID,miscfld1,Date,BusName,BusCity,Bst,Bzip,TYPEAIMED,TopStock,CatA,CatB,CatC,CatD,Choice1,Choice2,Choice3,CatE,MgrTrnd,AllTrnd,CatF,CatG,CatH,,,,,,,,,,,,
5,AX45,x,5/9/2012 0:00,john,Columbus,OH,45612,BEST,ALMOST BEST,50plus,yes,yes,yes,yes,no,no,yes,yes,yes,yes,yes,yes,,,,,,,,,,,,
6,AB76,X,4/27/2012 0:00,jacob,Prescott,AZ,87654,BETTER,needs work,50plus,yes,no,yes,yes,no,no,yes,no,no,yes,yes,yes,,,,,,,,,,,,
7,AR92,x,5/9/2012 0:00,paul,Anniston,CO,56789,BEST,BEST,50plus,yes,yes,yes,yes,no,yes,yes,no,no,yes,yes,yes,,,,,,,,,,,,
8,AA22,x,5/1/2012 0:00,andy,Hollywood,CA,90211,BEST,BEST,25-49,yes,yes,yes,yes,yes,no,yes,no,no,yes,yes,yes,,,,,,,,,,,,
9,AW10,x,5/2/2012 0:00,bill,Homestead,FL,33032,GOOD,BEST,under25,yes,yes,yes,yes,yes,no,yes,no,no,yes,yes,yes,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

And here's the full code that errors:

Option Compare Database
 
Public Sub TypeAchieved()
Dim rs As DAO.Recordset
Dim x As Long
Dim TypeAchieved As String
   
   '//Turn off Warnings for the next two commands
    DoCmd.SetWarnings False
 
‘#>#>#>#>#
‘////current Table2 is 4 columns, Earned being where TypeAchieved
‘////should go: BusID,BusName,TYPEAIMED,Earned
‘#>#>#>#>#
 
    '//Truncate Table2 (Temp Table)
    DoCmd.RunSQL "DELETE * FROM Table2;"
   
    '//Populate Table2 with data from carpkgs table - PLEASE EDIT field names To fields from carpkgs
    DoCmd.RunSQL "INSERT INTO Table2 SELECT BusID, BusName, TYPEAIMED,TopStock,CatA,CatB,CATC,CatD,Choice1,Choice2,Choice3,MgrTrnd,AllTrnd,CatF,CatH FROM Carpkgs;"
‘>>>>>>>>IN ABOVE LINE, the runtime “unknown field name” thing appears at ‘TopStock’ <<<<<<<<<<
 
    '//Turn back on alerts
    DoCmd.SetWarnings True
   
    '//Table2 is now a copy of carpkgs, but has the extra field "Earned" in Table2
    Set rs = CurrentDb.OpenRecordset("Table2", dbOpenTable)
       
    Do While Not rs.EOF
        With rs
            TypeAchieved = ""
            If TypeAchieved = "" Then
                x = 0
                x = x + IIf(rs!TopStock = "BEST", 1, 0)
                x = x + IIf(rs!CatA = "50plus", 1, 0)
                x = x + IIf(rs!CatB = "yes", 1, 0)
                x = x + IIf(rs!CATC = "yes", 1, 0)
                x = x + IIf(rs!CatD = "yes", 1, 0)
                x = x + IIf(rs!MgrTrnd = "yes", 1, 0)
                x = x + IIf(rs!AllTrnd = "yes", 1, 0)
                x = x + IIf(rs!CatF = "yes", 1, 0)
                x = x + IIf(rs!Choice1 = "yes" Or rs!Choice2=”yes” Or rs!Choice3=”yes”, 1, 0)
                x = x + IIf(rs!CatH = "yes", 1, 0)
                If x = 10 Then '////this should be the number of factors above
                    TypeAchieved = "Best"
                End If
            End If
   
    '//Type BETTER
            If TypeAchieved = "" Then
                x = 0
                x = x + IIf(rs!TopStock = "BETTER" Or rs!TopStock = “ALMOST BEST” Or rs!TopStock = “BEST”, 1, 0)
                x = x + IIf(rs!CatA = "50plus", 1, 0)
                x = x + IIf(rs!CatB = "yes", 1, 0)
                x = x + IIf(rs!MgrTrnd = "yes", 1, 0)
                x = x + IIf(rs!AllTrnd = "yes", 1, 0)
                x = x + IIf(rs!CatF = "yes", 1, 0)
                If x = 6 Then
                    TypeAchieved = "Better"
                End If
            End If
   
    '//Type GOOD
            If TypeAchieved = "" Then
                x = 0
                x = x + IIf(rs!CatA = "25-49" Or rs!CatA=”50plus”, 1, 0)
                x = x + IIf(rs!CatB = "yes", 1, 0)
                x = x + IIf(rs!MgrTrnd = "yes", 1, 0)
                If x = 3 Then
                    TypeAchieved = "Good"
                    Else: TypeAchieved = "N/A"
                End If
            End If
‘///update Earned (f.k.a. “Category”) field
            !Earned = IIf(TypeAchieved = "", Null, TypeAchieved)
‘//move to next record and repeat loop until end of records is reached
            .MoveNext
        End With
    Loop
   
    rs.Close
    Set rs = Nothing
 
End Sub

Hope that helps. I feel like I'm so close to a breakthrough here...

 

[Gingertrees]

OK, went ahead and massaged some data. Here are 5 records from "Carpkgs", in CSV format:

ID,BusID,miscfld1,Date,BusName,BusCity,Bst,Bzip,TYPEAIMED,TopStock,CatA,CatB,CatC,CatD,Choice1,Choice2,Choice3,CatE,MgrTrnd,AllTrnd,CatF,CatG,CatH,,,,,,,,,,,,
5,AX45,x,5/9/2012 0:00,john,Columbus,OH,45612,BEST,ALMOST BEST,50plus,yes,yes,yes,yes,no,no,yes,yes,yes,yes,yes,yes,,,,,,,,,,,,
6,AB76,X,4/27/2012 0:00,jacob,Prescott,AZ,87654,BETTER,needs work,50plus,yes,no,yes,yes,no,no,yes,no,no,yes,yes,yes,,,,,,,,,,,,
7,AR92,x,5/9/2012 0:00,paul,Anniston,CO,56789,BEST,BEST,50plus,yes,yes,yes,yes,no,yes,yes,no,no,yes,yes,yes,,,,,,,,,,,,
8,AA22,x,5/1/2012 0:00,andy,Hollywood,CA,90211,BEST,BEST,25-49,yes,yes,yes,yes,yes,no,yes,no,no,yes,yes,yes,,,,,,,,,,,,
9,AW10,x,5/2/2012 0:00,bill,Homestead,FL,33032,GOOD,BEST,under25,yes,yes,yes,yes,yes,no,yes,no,no,yes,yes,yes,,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

And here's the full code that errors:

Option Compare Database
 
Public Sub TypeAchieved()
Dim rs As DAO.Recordset
Dim x As Long
Dim TypeAchieved As String
   
   '//Turn off Warnings for the next two commands
    DoCmd.SetWarnings False
 
‘#>#>#>#>#
‘////current Table2 is 4 columns, Earned being where TypeAchieved
‘////should go: BusID,BusName,TYPEAIMED,Earned
‘#>#>#>#>#
 
    '//Truncate Table2 (Temp Table)
    DoCmd.RunSQL "DELETE * FROM Table2;"
   
    '//Populate Table2 with data from carpkgs table - PLEASE EDIT field names To fields from carpkgs
    DoCmd.RunSQL "INSERT INTO Table2 SELECT BusID, BusName, TYPEAIMED,TopStock,CatA,CatB,CATC,CatD,Choice1,Choice2,Choice3,MgrTrnd,AllTrnd,CatF,CatH FROM Carpkgs;"
‘>>>>>>>>IN ABOVE LINE, the runtime “unknown field name” thing appears at ‘TopStock’ <<<<<<<<<<
 
    '//Turn back on alerts
    DoCmd.SetWarnings True
   
    '//Table2 is now a copy of carpkgs, but has the extra field "Earned" in Table2
    Set rs = CurrentDb.OpenRecordset("Table2", dbOpenTable)
       
    Do While Not rs.EOF
        With rs
            TypeAchieved = ""
            If TypeAchieved = "" Then
                x = 0
                x = x + IIf(rs!TopStock = "BEST", 1, 0)
                x = x + IIf(rs!CatA = "50plus", 1, 0)
                x = x + IIf(rs!CatB = "yes", 1, 0)
                x = x + IIf(rs!CATC = "yes", 1, 0)
                x = x + IIf(rs!CatD = "yes", 1, 0)
                x = x + IIf(rs!AllPromo = "yes", 1, 0)
                x = x + IIf(rs!MgrTrnd = "yes", 1, 0)
                x = x + IIf(rs!AllTrnd = "yes", 1, 0)
                x = x + IIf(rs!CatF = "yes", 1, 0)
                x = x + IIf(rs!Choice1 = "yes" Or rs!Choice2=”yes” Or rs!Choice3=”yes”, 1, 0)
                x = x + IIf(rs!CatH = "yes", 1, 0)
                If x = 11 Then '////this should be the number of factors above
                    TypeAchieved = "Best"
                End If
            End If
   
    '//Type BETTER
            If TypeAchieved = "" Then
                x = 0
                x = x + IIf(rs!TopStock = "BETTER" Or rs!TopStock = “ALMOST BEST” Or rs!TopStock = “BEST”, 1, 0)
                x = x + IIf(rs!CatA = "50plus", 1, 0)
                x = x + IIf(rs!CatB = "yes", 1, 0)
                x = x + IIf(rs!MgrTrnd = "yes", 1, 0)
                x = x + IIf(rs!AllTrnd = "yes", 1, 0)
                x = x + IIf(rs!CatF = "yes", 1, 0)
                If x = 6 Then
                    TypeAchieved = "Better"
                End If
            End If
   
    '//Type GOOD
            If TypeAchieved = "" Then
                x = 0
                x = x + IIf(rs!CatA = "25-49" Or rs!CatA=”50plus”, 1, 0)
                x = x + IIf(rs!CatB = "yes", 1, 0)
                x = x + IIf(rs!MgrTrnd = "yes", 1, 0)
                If x = 3 Then
                    TypeAchieved = "Good"
                    Else: TypeAchieved = "N/A"
                End If
            End If
‘///update Earned (f.k.a. “Category”) field
            !Earned = IIf(TypeAchieved = "", Null, TypeAchieved)
‘//move to next record and repeat loop until end of records is reached
            .MoveNext
        End With
    Loop
   
    rs.Close
    Set rs = Nothing
 
End Sub

Hope that helps. I feel like I'm so close to a breakthrough here...

 

[xenou]

Table2 needs to be an exact copy of carpkgs (copy the carpkgs table, then paste it "structure only"). Then you just add the one extra field "Earned" to it.

So Table2 should have all these fields:
Table2 (BusID, BusName, TYPEAIMED,
TopStock,CatA,CatB,CATC,CatD,
Choice1,Choice2,Choice3,MgrTrnd,
AllTrnd,CatF,CatH, Earned)

Does that make sense? We want all the fields we need so that we can do all the tests that are in the rest of the code by comparing the field values. So we need all those fields for the processing even though at the end of it all we only care about the fields BusID, BusName, TYPEAIMED,and Earned.

Will that get us past the error now?

 

[Gingertrees]

Yes, but now I have different problems:

#1: When I run this, a box pops up and it asks me to enter a parameter value for "Earned". ???? I tried just pressing Enter, or putting a space or a number in there, or whatever, doesn't seem to matter. Then if goes on to invoke this error:
#2: runtime error: 3063 Duplicate output destination "ID" <== this is the autonumber field I have for each record in Carpkgs.
I've read that this might be something I have to fix with my PC - which blows because this is a work computer and my little programming needs are pretty low on the totem pole. Is that correct or???
This will be worth it when it's done. Worth it when it's done. Worth it when it's done...

 

[Gingertrees]

Even more info:
for CatF above, when I float my cursor over the erroring code, it says "Object variable or With block variable not set"
[I hate that error - it always means I've screwed up in some way I can't even figure out]

 

[Gingertrees]

EUREKA! I figured out my own problem at long last:
Instead of enumerating each indiv. field, I used:

     '//Truncate Table2 (Temp Table)
    DoCmd.RunSQL "DELETE * FROM Table2;"
    
    '//Populate Table2 with data from Carpkgs- must duplicate source table
    
    DoCmd.RunSQL "SELECT * INTO Table2 FROM Carpkgs"
'/#!#!#!#To add the field "Earned", I used the ALTER TABLE command: #!#!#!
    DoCmd.RunSQL "ALTER TABLE Table2 ADD Earned text"
'If TypeAchieved code stayed the same, down to the last type.

Also, I found a reference on another blog,
Run Time error: 3020 when editing recordSet - Access World Forums
about the rs.edit command, which I was apparently missing. So I changed this:

            End If ‘///update Earned (f.k.a. “Category”) field             !Earned = IIf(TypeAchieved = "", Null, TypeAchieved) ‘//move to next record and repeat loop until end of records is reached             .MoveNext

With this:

            End If
            .Edit
            !Earned = IIf(TypeAchieved = "", Null, TypeAchieved)
            .Update
            .MoveNext

Now everything works! Thanks xenou for all your help -
I don't think I would've gotten to these little problems I could fix without your help on the big ones.
</pre>