Re: Programming for all fields in a table

Do you want to:
a) change the value of all fields, in all records (every cell in the entire
table) or
b) change the value of one field, in every record (just one column of the
table)?

If a), execute the update query. Looping through the recordset will be less
efficient to write and execute.

If you want to loop thorugh all records anyway, the syntax would be like
this (assuming all fields are numeric, and you want to change them to 9999):

Function AdjustMyTable()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field

Set db = CurrentDb
Set rs = db.OpenRecordset("MyTable")

Do While Not rs.EOF
rs.Edit
For Each fld In rs.Fields
fld = 9999
Next
rs.Update
rs.MoveNext
Loop
rs.Close

set fld = Nothing
Set rs = Nothing
Set db = Nothing
End Function

For a quick intro to the objects in the DAO library, see:
http://allenbrowne.com/ser-04.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Feico" <feico@xxxxxxxxxxxxxxxxx> wrote in message
news:6f37l1lnfor9ratlt0ku73qahfj0atovu1@xxxxxxxxxx
> On Mon, 17 Oct 2005 16:53:20 +0800, "Allen Browne"
> <AllenBrowne@xxxxxxxxxxxxxx> wrote in comp.databases.ms-access:
>
>>In the VBA code window, choose References on the Tools menu.
>>Check the box beside:
>> Microsoft DAO 3.6 Library
>>This library is automatically selected in all versions of Access except
>>2000
>>and 2002, so presumably you have one of those 2.
>>
>>Unfortunatley, the ADO library also has a Recordset object, so you need:
>> dim db as database, rs as DAO.recordset, i as integer
>
> That helps, thank you (you must be an extremely experience programmer
> to remember such things)
>
>>BTW, DFS interpreted your question that you wanted to apply the change to
>>all fields in all records of your table. If you just wanted to change a
>>particular field in all records, you could just use an Update query, such
>>as:
>> dbEngine(0)(0).Execute "UPDATE MyTable SET MyField = (1.1 *
>> MyField);",
>>dbFailOnError
>
> In fact, I do not want to change the table at all. However, I do (of
> course) want to read the contents of the table. For this I need a
> syntax like:
>
> recordset [index] . fieldname
>
> What's this syntax in VB?
>
> Sorry for appearing to be a dunce. I am actually an experienced
> programmer (Algol, Fortran, Assembly, Cobol, C++, APL) but I used
> Basic only on the Apple II, and I hated the language. But it appears
> that Access has nothing else to offer.
>
> --
> Feico


.

Relevant Pages

  • Re: Inserting rows syntax + row #ing code in protected sheet?
    ... mentioned WP above because I'm going to reference a handy piece of syntax I ... is there is an extra command to handle "cancel" in VB? ... > Dim i As Long ... > Exit Sub ...
    (microsoft.public.excel.programming)
  • Re: Using query values for field name in table
    ... You didn't need to change the syntax on the Fields collection to get it to ... See http://www.QBuilt.com for all your database needs. ... >>> Dim dbsGeneralThoracic As Database ... >>> Dim fOpenedDB As Boolean ...
    (microsoft.public.access.modulesdaovba)
  • Re: FTS Weighted Values Implementation Problem
    ... The syntax error comes from the simple quotes surrounding your search phrase. ... Dim SearchTerm1 As String ... Dim conn As New SqlConnection ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Inserting rows syntax + row #ing code in protected sheet?
    ... > Sub insertrows() ... > Dim i As Long ... >> The syntax to put basically at beginning and end of a macro that's ... >> to a button to allow it to work in a protected sheet works just great! ...
    (microsoft.public.excel.programming)
  • Re: Time checking problem
    ... I still get the error stating that the time in question is duplicated. ... >> provide the warning if there is an overlap. ... >> Dim varResult As Variant ... rather than allenbrowne at mvps dot org. ...
    (microsoft.public.access.forms)