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

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?

CodePudding user response：

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