By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 941 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

Loop through a data table and then update another table

P: 8
Good morning people,

I wish to use VBA to open an Access Query, loop through each record, and then based on the result of a certain field create calculations in which i wish to place as a new record in a new table.

If anyone out there can give be the basic code that will : -

Collect the Data,
Loop through data
create a new table
post variable to the new table (as records).

I would be most greateful.
Oct 17 '06 #1
Share this Question
Share on Google+
1 Reply


MMcCarthy
Expert Mod 10K+
P: 14,534
To open a query as a recordset. First make sure there is a reference to the Microsoft DAO library ticked. In the VB Editor, go to Tools - References and tick it if not already ticked. Then the following should
help you get started:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Function yourFunctionName() 
  3. Dim db As DAO.Database
  4. Dim rs1 As DAO.Recordset
  5. Dim rs2 As DAO.Recordset
  6.  
  7.     Set db = CurrentDb() 
  8.     Set rs1 = db.OpenRecordset("YourQueryName") 
  9.     set rs2 = db.OpenRecordset("YourNewTableName")
  10.  
  11.     rs1.MoveFirst 
  12.     Do Until rs1.EOF 
  13.         If rs1![FieldName] = <Your Criteria> Then
  14.             rs2.AddNew
  15.             rs2![FieldName] = rs1![FieldName]
  16.             rs2.Update
  17.         End If
  18.     Loop
  19.  
  20.     rs.Close 
  21.     Set rs = Nothing
  22.     Set db = Nothing
  23.  
  24. End Sub 
  25.  
  26.  
To create a New Table:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Sub createNewTable() 
  3. Dim db As Database 
  4. Dim tbl As TableDef 
  5. Dim fld As Field 
  6. Dim idx As Index
  7.  
  8.     ' Start by opening the database 
  9.     Set db = CurrentDb() 
  10.     ' Create a tabledef object 
  11.     Set tbl = db.CreateTableDef("NewTableName")
  12.  
  13.     ' Create a field; set its properties; add it to the tabledef 
  14.     Set fld = tbl.CreateField("FieldID", dbLong) 
  15.     fld.OrdinalPosition = 1 
  16.     fld.Attributes = dbAutoIncrField 
  17.     tbl.Fields.Append fld 
  18.  
  19.     ' Create another; set its properties; add it to the tabledef 
  20.     Set fld = tbl.CreateField("FieldName", dbText) 
  21.     fld.OrdinalPosition = 2 
  22.     fld.Size = 50 
  23.     fld.Required = True 
  24.     fld.AllowZeroLength = False 
  25.     tbl.Fields.Append fld 
  26.  
  27.     <create more fields here>
  28.  
  29.     ' Finally add table to the database 
  30.     db.TableDefs.Append tbl 
  31.     ' And refresh the database window 
  32.     RefreshDatabaseWindow 
  33.     ' Indicate creation was successful 
  34.     MsgBox "The " & tbl.Name & " table was successfully created" 
  35.  
  36.     fld.close
  37.     set fld = nothing
  38.     tbl.close
  39.     set tbl = nothing
  40.     set db = nothing
  41.  
  42. End Sub
  43.  
  44.  
Use this code to start off with and if you run into any problems, let me know.
Oct 18 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.