insert records one by one into tabel

[megatronixs]

Hi all,

I have a form where the user would enrol people for a training. in the form there is a subform in the datasheet view with one field called "eid"
The user would put several eid nr (employee id) and then when she/he clicks on the button, it should go one by one record and add them to the enrolment table.

I have tried with the below code, but it only seems to take the last one and even with the MoveFirst did not work out. any clue where I go wrong?

Private Sub btn_enrol_from_list_Click()
    Dim event_id        As String
    Dim eid             As String
    Dim db              As DAO.Database
    Dim manual_list     As DAO.Recordset
    Set db = CurrentDb()
    Set manual_list = db.OpenRecordset("tbl_manual_enrol_temp")
    
    event_id = Me.event_id_text
    eid = Me!frm_manual_enrol_temp_sub.Form![eid]
    
Do While Not manual_list.EOF
    Debug.Print eid
'CurrentDb.Execute "INSERT INTO tbl_enrolment (event_id, participant_peoplesoft_id, date_received) Values( '" & event_id & "','" & eid & "','" & Date & "')"
        manual_list.MoveNext
    Loop
End Sub

Greetings.

 

[sneuberg]

I don't understand what you are trying to do. There nothing varying inside the loop. If this worked all it would do is add the same record to the tbl_enrolment a number of times equal to the number of records in tbl_manual_enrol_temp. Also as if date_received is a date/time field you need pound signs (#) rather than single quotes around Date.

If you are trying to create some default records in a subform you could probably do this more easily will an append query. Please give us a more detailed explanation of what you want to do.

 

[megatronixs]

Hi,
I want to loop trough a table (tbl_manual_enrol_temp) where I have only one column called "eid"
It should start with the first record, then insert it to the table tbl_enrolment. then move to the next record and insert it to the table tbl_enrolment.
till there are no more records in it.
If I would have 3 records in the table tbl_manual_enrol_temp, it should insert those records in the table tbl_enrolment.

I hope this will give a better idea of what I want to do.

Greetings.

 

[sneuberg]

I suggest doing this with an append query like

INSERT INTO tbl_enrolment ( participant_peoplesoft_id, event_id, date_received )
SELECT tbl_manual_enrol_temp.eid, [Forms]![Form1]![event_id_text] AS EventID, Date() AS [Date Rvc]
FROM tbl_manual_enrol_temp;

This is demonstrated in the attached database.

 

[megatronixs]

Hi Sneuberg,

This is a very nice idea and it works. Maybe I wanted to do it the hard and not logical way,

BIG THANKS.

Greetings.

 

[sneuberg]

Maybe I wanted to do it the hard and not logical way.
.

That would be good for getting in some practice using recordsets but it's best to avoid SQL in code if you can.

I once needed a table with a lot (~1000,000) of records for testing something and wrote some code to insert records into this table. It was taking forever. I timed it and it was inserting records at the ridiculously low rate of about 20 records per second. I found the fast way to make a large table is just to create an append query that appends the table to itself. The difference in speed is shocking.

 

[megatronixs]

I tested a and it worked nice

Greetings