APPEND [table1].[num_records] (to) table2

S

Snak

Registered User.
Local time
Today, 21:59
Joined
Mar 21, 2003
Messages
32
Hi All

I've had 'experts' across the globe fail on this one - but I feel it MUST be possible.

I have a table (we'll call it table1) in which there is an int field containing a number. I have another table (call it table2) which has no records. I need a query/macro/module/anything that will, for each record in table1, create records in table2 according to the number found in the aforementioned field.

So if [table1].[num_records] = 5, the routine must create 5 records in table2 (and id them as 'belonging' to the entry in table1). If the next record in table1 has 3 in num_records, then another 3 records are to be added to table2 with the id's as before.

I'm using Access 2000/2003 so old code wont do it. I'm boggled that this isn't a built-in function (or is it and I'm too close to the problem?). This HAS to be a regular need, surely? Anyway I'm bleary-eyed from searching forums and am about to give it all up and become a roadsweeper - that HAS to be less stress. I'm willing to swap ACCESS-MySQL expertise.......
 
Dim rs1 as DAO.Recordset, rs2 as DAO.Recordset
dim i as integer

Set rs1 = CurrentDb.OpenRecordset("Table1Name",dbopensnapshot)
Set rs2 = CurrentDb.OpenRecordset("Table2Name",dbopendynaset)

rs1.MoveFirst

Do Until rs1.EOF

For i = 1 to rs1.Field("IntFieldName")

rs2.Add
rs2.Field(1) = ...
rs2.Field(2) = ...
...
rs2.Update

Next i

rs1.MoveNext

Loop
 
Hi Rob

Many thanks for that, but it does not work. I get the following error message: Compile Error: User-defined type not defined. The snippet highlighted is: ..rs1 As DAO.Recordset

I am not familiar with the Access-VB interface so I have just pasted your reply (with table-name, field-name adjustments of course) into a module and placed "Public Function autofilltable()" at the top and "End Function" at the bottom and tried the following:

Debug > Compile access9db which generates the error above

and

Calling the function from a macro, which offers the following invaluable help:
"The expression you entered has a function name that Microsoft Office Access can't find".

Is this because A2000/2003 does not use DAO and/or because I'm doing something wrong?
 
Also, did you put the code into a standalone module or a form's module?
 
It'll work but we'll have to work through a few things together. First, you need to add a reference to DAO. In the visual basic editor click on Tools, then References and choose Microsoft DAO... The highest version you got.

Also, copy and paste the exact code you wrote out.
 
I've just reworked Rob's code into a function.

You can call it as test to find out if it actually worked or not like so:

Code: 
If FillTable = True Then
    MsgBox "Was a success!", vbInformation, "Result"
Else
    MsgBox "Operation failed!", vbExclamtion, "Result"
End If

Code: 
Public Function FillTable() As Boolean

    On Error GoTo Err_FillTable

    Const SourceTable As String = "Table1Name"
    Const DestinationTable As String = "Table2Name"
    Const FieldName As String = "FieldName"

    Dim db As DAO.Database
    Dim rsSource As DAO.Recordset
    Dim rsDest As DAO.Recordset
    Dim lngCounter As Long
    
    Set db = CurrentDb
    
    Set rsSource = db.OpenRecordset(SourceTable, dbOpenSnapshot)
    Set rsDest = db.OpenRecordset(DestinationTable, dbOpenDynaset)

    With rsSource
        .MoveFirst
        Do Until .EOF
            For lngCounter = 1 To .Fields(FieldName)
                rsDest.Add
                    rsDest.Fields(0) = .Fields(0)
                    rsDest.Fields(1) = .Fields(1)
                    rsDest.Fields(2) = .Fields(2)
                    ' continue as necessary
                rsDest.Update
            Next lngCounter
            .MoveNext
        Loop
        rsDest.Close
        .Close
    End With
    
    FillTable = True
    
Exit_FillTable:
    Set rsSource = Nothing
    Set rsDest = Nothing
    Set db = Nothing
    Exit Function
    
Err_FillTable:
    FillTable = False
    Resume Exit_FillTable

End Function
 
I really appreciate this, guys.

OK. I did the checking and unchecking of Microsoft DAO 3.x Object Library. (3.6) and Microsoft ActiveX Data Objects 2.x Library. I ran it again as before with the same errors.

I then replaced my Function with yours, SJ and now compile gives: Compile Error: Method or data member not found, and it highlights the .Add bit of rsDest.Add.

When trying to execute a macro (Run Code), I get the Action Failed message box after "The expression you entered has a function that .... can't find".

Here is the amended function c/w table/fieldnames. And to answer your earlier question, the Function is not bound to a form or anything else.

Code: 
Public Function FillTable() As Boolean

    On Error GoTo Err_FillTable

    Const SourceTable As String = "TimetableDetails"
    Const DestinationTable As String = "Timetable"
    Const FieldName As String = "No_of_Groups"

    Dim db As DAO.Database
    Dim rsSource As DAO.Recordset
    Dim rsDest As DAO.Recordset
    Dim lngCounter As Long
    
    Set db = CurrentDb
    
    Set rsSource = db.OpenRecordset(SourceTable, dbOpenSnapshot)
    Set rsDest = db.OpenRecordset(DestinationTable, dbOpenDynaset)

    With rsSource
        .MoveFirst
        Do Until .EOF
            For lngCounter = 1 To .Fields(FieldName)
                rsDest.Add
                    rsDest.Fields("TT_ID") = Null
                    rsDest.Fields("SessionID") = .Fields("ID")
                    ' rsDest.Fields(2) = .Fields(2)
                    ' continue as necessary
                rsDest.Update
            Next lngCounter
            .MoveNext
        Loop
        rsDest.Close
        .Close
    End With
    
    FillTable = True
    
Exit_FillTable:
    Set rsSource = Nothing
    Set rsDest = Nothing
    Set db = Nothing
    Exit Function
    
Err_FillTable:
    FillTable = False
    Resume Exit_FillTable

End Function

POINT: The NULL is because the ID field is autonumber...
 
Last edited by a moderator:
Snak said:
The NULL is because the ID field is autonumber...
Click to expand...

Then remove that line. You can't assign a Null value to a Long field.
 
Upload the database.

If there's more to it then create a new one and import the two tables and the module.

.zip it up and I'll have a look to see what the problem is.
 
I cannot upload the db. The TimetableDetails and Timetable tables are MySQL tables accessed by Access via ODBC.

The other tables (well, most of them) are access tables but not relevant here. I don't think there's any sensitive data in the table but it's over a thousand records of quite substantial data.
 
Can you maybe do make table queries to make the tables from the mySQL tables and use them for the purpose of an upload.

Limit the tables to relevant fields and delete a bigger proportion of the records. We'd only need three or four from the Source table.
 
Ok I have created a mini version with 8 records in TimetableDetails, with that table considerably shortened. And a straight copy of Timetable (which, of course, is empty),

It's called testautofilldb.zip
 

Attachments

lol, everything's fine - I just spotted the error:

Change
Code: 
rsDest.Add
to
Code: 
rsDest.AddNew

:D
 
:) It happens.

Changing .Add to .AddNew now allows the compile to (seem to) work - no errors etc. in fact no nothing so assumed it worked. However, running it from the macro (Runcode) gets me exactly the same error message.
"The expression you entered has a function that .... can't find".
 
Can I assume that OK, I've created a function, which is in a module, but I need to do something to actually tell Access that the function exists? This subject may have become somewhat tiresome and I'm sorry about that. But I can see the light at the end of the tunnel and I'm just hoping it's not the boss with a torch and more hassle :)
 
Personally, I wouldn't run anything from a macro because they are, for want of a better word, crap.

Have you put the code in a standalone module or a form's module?
 
I assume it's a standalone. It is not part of a button or anything else. I clicked Module, New and just pasted it in (or, equiv. just typed it in). Then I just used Macros, New, RunCode, AutoFill. Then ran the Macro. Well, tried to.
 
RunCode:


FillTable()

You need the brackets at the end to declare it as a function.
 
You must log in or register to reply here.

Similar threads

H
Create a Table for deleted records, and recover this records again
Replies
6
Views
466
theDBguy
theDBguy
J
ms access query by insert a random record which not exist in other table
Replies
8
Views
554
Pat Hartman
P
D
Solved Adding Records Using VBA
2
Replies
31
Views
1,920
DDJKSA
D
smtazulislam
Add field item data create left-side gap
Replies
13
Views
1,038
smtazulislam
smtazulislam
O
Adding days to Now() but with specific times
2
Replies
22
Views
456
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom