[RESOLVED] access/vba appending new records index

[bambo]

Hey,

I've a table in access with an index that is not an autonumber. I'd like to append new records to this table based on another table.

I've tried writing a query for that, and have put dmax("Index","MyTable") +1 as the index for the records. that only works for the first record. so I've tried writng a small vba for that using dao which completely doesnt work:


Set qdf1 = CurrentDb.TableDefs("Import_budgets")
Set rs1 = qdf1.OpenRecordset
rs1.MoveFirst

Set qdf2 = CurrentDb.TableDefs("budget_lines")
Set rs2 = qdf2.OpenRecordset
Index = DMax("budget_line_no", "budgets")
'qdf("station") = Me.station_no

While Not (rs1.EOF)
Index = Index + 1
rs2.AddNew
rs2.budget_line_no = Index
rs2.budget_no = Me.sel_budget_no
rs2.payment_type_no = Me.Sel_Payment_Type
rs2.station_no = rs1.Istation_no
rs2.amount = rs1.budget

rs1.MoveNext
Wend

[si_the_geek]

First of all, there is no need to use TableDefs, you can open recordsets directly.

There is also need to MoveFirst when you open a recordset, as this is done automatically.

Therefore your code can look like this:

Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM Import_budgets")
Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM budget_lines") 
Index = DMax("budget_line_no", "budgets")
'qdf("station") = Me.station_no
 
While Not (rs1.EOF)
  Index = Index + 1
  rs2.AddNew
  rs2.budget_line_no = Index
  rs2.budget_no = Me.sel_budget_no
  rs2.payment_type_no = Me.Sel_Payment_Type
  rs2.station_no = rs1.Istation_no
  rs2.amount = rs1.budget
 
  rs1.MoveNext
Wend

I don't know quite what you are using DMax for, is this just to get the maximum value from a table? (same as rs1?)

[bambo]

Thank you for that. after also correcting the rs.field to rs("field") it is runing but without appending any new records to the table !

[si_the_geek]

Ah yes, I hadn't noticed either of those 'features'

To actually write the new record, you need to add rs2.update after all values have been set.


And back to the DMax line, is this to get the maximum value of budget_line_no from the budgets table?

[bambo]

great. works well.

how do I give credit to you (and others that help me in this forum)

[si_the_geek]

Good stuff, it's always nice to get code working

Apart from marking the thread as resolved (which you've done already ) you can rate posts using the "rate this post" link under the posters name.

You can either rate Approve (for useful posts) or Disaprove (caused problems, etc), and leave a comment if you want. The 'points' for this get added up, and show as green blocks under user names.

People can't see who rated them, so most of us also leave our user name at the end of the comment.

There is more detailed info about it all here.