Facing problems Inserting multiple records in a continuous form in access from a query

Question

I have a continuous form where I need to populate all employees' ID & names to set their yearly target. I don't want to manually add them through a combo box because it can create duplicate records for a single employee. The form contains two fields named EMPID(employee ID) and EmpName(name of the employee). I want all the EMPID and EmpName to auto populate in that continuous form from a Query named "InsertNameTarget". After watching some YouTube videos, I came up with something like the following:

Dim i As Integer

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("InsertNameTarget")

For i = 0 To rs.RecordCount - 1
    DoCmd.GoToControl "EMPID"
    DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
    EMPID = rs.Fields("EMPID")
    EmpName = rs.Fields("EmpName")
    rs.MoveNext
Next i

rs.Close
Set rs = Nothing
db.Close

It doesn't work as intended. It gives me the following result:

It is giving the first record out of 10 records and doesn't insert the EMPID properly to all records. I don't know what I'm doing wrong here. Can you point me in the right direction?

Answer 1

Continuous forms are difficult to work with. Since each control is only represented once, you can't assign values to it reliably.

Instead, work with the forms recordset:

Dim i As Integer

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim frs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("InsertNameTarget")
Set frs = Me.Recordset

Do While Not rs.EOF
    frs.AddNew
    frs!EMPID = rs.Fields("EMPID")
    frs!EmpName = rs.Fields("EmpName")
    frs.Update
    rs.MoveNext
Loop

rs.Close
Set rs = Nothing
db.Close