Auto create records

[Exodus]

How can I auto create a table.

Say I need records 1-365 how can I create this in a new table.

 

[KenHigg]

I would just do a make table query in the query builder and use ADO to populate it....

How do you plan to use it - seems this may raise a normalization issue...

???
ken

 

[Exodus]

This is just for ceating a report for taging pallets.
we program our machines by a region then palletize them.
And every region would have a differrent quantity.
Instead of manualy creating induvidual tags for each pallet I was looking at automating it.

So how would I populate it using ADO?

 

[KenHigg]

If you've never used ADO, you can read up on it in Access Help or better yet search this forum. If you have, I think you would utilize the .addnew method...

???
ken

 

[Exodus]

thanks Ken I read up on it and this is what I came up with. Stole the basic code from the MS knowledge base. Don't know if its right but it works

Private Sub Command0_Click()

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim x As Long
For x = 87965 To 91695
  
 
 rs.Open "Select * from 1A", CurrentProject.Connection, _
            adOpenKeyset, adLockOptimistic


  rs.AddNew
  rs!Pallet = x
  rs.Update
  rs.MoveNext
  rs.Close
  Next
End Sub
[/

 

[KenHigg]

Doing a little too much work. Try:

Private Sub Command0_Click()

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim x As Long

rs.Open "Select * from 1A", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

For x = 87965 To 91695

rs.AddNew
rs!Pallet = x
rs.Update
rs.MoveNext
Next

rs.Close

End Sub

???
ken

 

[Exodus]

Thanks that worked as well.

 

[KenHigg]

Cool - Good ADO is almost sinful...

Are you sure you have to do a make table every time? Could you do it with just one permanent table?

???
ken

 

[Exodus]

Well from election to election the numbers could change. And I need differrent numbers for every region. I really want to use code to make the table. then populate it. I'm thinking maybe using a form so that way I don't have to edit the code 10 times. Having the code look to the fields in the form to deterime the table and number to number. Now that I think about it I only have to make the tables once. Use code to clear the table the populate it.

I don't even know where to begin. Seeings how I don't know Code. But I love a challange.

 

[KenHigg]

Just do another query (delete query) and call it from code!

(Make sure you test real good before you put it into production!)

kh

 

[Exodus]

I'm abit of a pefectionist so I always test before I go production.

 

[Exodus]

How can I refer to the field on the form in the code to specify which table.

 

[Exodus]

Well thought you might want to know how it came out.
With some help from Acess Vba Central this is the final code.
Works great.

Private Sub Command0_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim SQL As String
 
SQL = "DELETE * FROM " & Me.Table & ";"

CurrentDb.Execute SQL

Debug.Print SQL

rs.Open "Select * from " & Me.Table & ";", CurrentProject.Connection, _
            adOpenKeyset, adLockOptimistic



Dim x As Long
For x = Me.XFrom To Me.XTo
  rs.AddNew
  rs!Pallet = x
  rs.Update
  rs.MoveNext
  Next
  rs.Close
  MsgBox "Done!"
End Sub

 

[Exodus]

Ok this is the final maybe

Private Sub Command0_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim SQL As String
Dim x As Long
 
SQL = "DELETE * FROM " & Me.Table & ";"

CurrentDb.Execute SQL

Debug.Print SQL

rs.Open "Select * from " & Me.Table & ";", CurrentProject.Connection, _
            adOpenKeyset, adLockOptimistic


For x = Me.XFrom To Me.XTo
  rs.AddNew
  rs!Pallet = x
  rs.Update
  Next
  rs.Close
  MsgBox "Done!"
End Sub