And Template For Clearing all FIELDS in a Recordset?

[PSUIVERSON]

I brought in a table from dbase and need to build some code to rebuild the recordset each month based off other tables. In order to test it this month I want to leave the table framework up but delete the fields so I can test my VBA to see if I am re-populating the fields correctly.

Is there a simple template to set a large number of CURRENCY fields to $0.00 before I run my code to re-populate?

Thanks...

 

[JoanneM]

You could use Default value in the table design

hope this helps

jo

 

[PSUIVERSON]

Design is on import. I need code to do it. I actually did it but don't know how long winded it is...

Do While Not rst2.EOF
rst2.Edit
rst2!Field1 = 0
rst2.Update

rst2.Edit
rst2!Field2 = 0
rst2.Update

ETC. ETC...(For about 35 fields...)

rst2.MoveNext
Loop

 

[MichaelRed]

More or less ignoring hte niceitiy of getting rst2 instantiated, as well as using a simple UpDate query, the snipet below, shows a VERY abbreviated approach to simply looping through all fields and setting them (the Numeric ones) to Zero.

Public Function basDepopulateRecSet()


    Do While (Not rst2.EOF)
        
        rst2.Edit
            For Idx = 0 To rst2.Fields.Count - 1
                If (IsNumeric(rst.Field(Idx))) Then
                    rst2!Fields(Idx) = 0
                End If
            Next Idx
        rst2.Update
        
        rst2.MoveNext
    Loop
 

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[PSUIVERSON]

rst.Field method does not work with ADO I don't believe.

ITEM IS NOT FOUND IN THIS COLLECTION

 

[MichaelRed]

Hmmmm,

At least 'WE" now know the recordset is persumed to be of the ADO persuasion.

This is al least one good reason for ME to have carefully avoided the instantiation of the recordset.

On the other hand, my docs do mention the fld collection of ado recordsets, so it should not be a huge leap from hte Fields collection of (DAO?) to the flds collection of ?ADO?)?

as they say ... 'exercise for the student ... '



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[MichaelRed]

Actually, on checking further, my docs say ADO DOES support the fields collection - so as they say in hte other trade " ... back to you Bob (or PSUIVERSON ... .





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[wildcanada1]

If you get the error "ITEM IS NOT FOUND IN THIS COLLECTION", does this not mean that the fields that you are trying to edit are NOT in the recordset??


What are the names of your fields? Just field1, field2, etc??

Mike

 

[PSUIVERSON]

Yeah but that's because I think it doesn't see .field - but I see what the other dude says...

This is how I have it and it works relatively quickly. If I don't get back to you I apologize I'm trying to figure something else out...

Public Sub setFinSumZero()
Dim dbs As Database
Dim rst2 As Recordset

Set dbs = CurrentDb
Set rst2 = dbs.OpenRecordset("FINSUM_pk", dbOpenDynaset)

' 35 fields in FINSUM contain $ values

rst2.MoveFirst
Do While Not rst2.EOF

rst2.Edit
rst2!CURRBUD_IE = 0
rst2!FISCYTD_IE = 0
rst2!INCPTD_IE = 0
rst2!ENCTD_IE = 0
rst2!CURRBUD_CR = 0
rst2!FISCYTD_CR = 0
rst2!INCPTD_CR = 0
rst2!ENCTD_CR = 0
rst2!CURRBUD_CE = 0
rst2!FISCYTD_CE = 0
rst2!INCPTD_CE = 0
rst2!ENCTD_CE = 0
rst2!CURRBUD_DE = 0
rst2!FISCYTD_DE = 0
rst2!INCPTD_DE = 0
rst2!ENCTD_DE = 0
rst2!CURRBUD_RV = 0
rst2!FISCYTD_RV = 0
rst2!INCPTD_RV = 0
rst2!ENCTD_RV = 0
rst2!CURRBUD_TI = 0
rst2!FISCYTD_TI = 0
rst2!INCPTD_TI = 0
rst2!ENCTD_TI = 0
rst2!CURRBUD_TO = 0
rst2!FISCYTD_TO = 0
rst2!INCPTD_TO = 0
rst2!ENCTD_TO = 0
rst2!CURRBUD_SB = 0
rst2!FISCYTD_SB = 0
rst2!INCPTD_SB = 0
rst2!ENCTD_EB = 0
rst2!CURRBUD_EB = 0
rst2!FISCYTD_EB = 0
rst2!INCPTD_EB = 0
rst2.Update

rst2.MoveNext
Loop

MsgBox ("You've set all your values to Zero in FINSUM and are ready to build FINSUM Table.&quot

Set rst2 = Nothing
dbs.Close

End Sub

 

[MichaelRed]

Rather neanderthallish, but if it floats your boat, it must be blue water?

A SIMPLE Update query should do it - and be a LOT faster!

"Other dude"

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[PSUIVERSON]

Thanks man. I'm new to this stuff and am trying to do a bunch of things at once. Update query was much simpler but I've found it hard to decide which items I need to try and write VBA on and which to do simple queries on.

 

[PSUIVERSON]

For the one I ran a simple update query before building the database each month based on another table. However - I used your idea for another table which I need to replace all NULL values with Zero's before doing some calculations!

Here is the hybrid...thanks again...Worked very well...Still confused as to why the rst.fields(i) is working here and not before...

Public Sub finrecXNULLS()
Dim dbs As Database
Dim rst As Recordset

Dim i As Single

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("finrec", dbOpenDynaset)

rst.MoveFirst

Do While Not rst.EOF
For i = 6 To 10
If IsNull(rst.Fields(i)) Then
rst.Edit
rst.Fields(i) = 0
rst.Update
End If
Next i
rst.MoveNext
Loop

Set rst = Nothing
dbs.Close

End Sub

 

[MichaelRed]

Check the "Nz" function, which can be imbeded directly in the query statement (or grid). Help can be helpful.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places