Auto Fill in Continuous Form

[Balfour211]

I am using Access 2003

I have a subform that lists employees by name. The data source for that
subform is from an "Index" table that lists all employees. There are usually
a total of at least 14 employees in the Index, and every day a new entry is
started with this form having to list all 14 employees. Right now, instead
of going down the list and entering each of the 14 employees, I hard code the
filling in of the list with a "Value" (Employee = "Smith").

DoCmd.GoToRecord , , acNext
Employee= "Smith"
DoCmd.GoToRecord , , acNext
Employee= "Jones"
DoCmd.GoToRecord , , acPrevious, 1
Status.SetFocus

The "acPrevious" command takes me back to the first entry. If I had 14
names, the value would be 13.

The problem with this is that I have to put the Employees name in the code.
This makes the changing of employees a "recoding task" instead of just
changing the Index table.

Question: Is there a way to have each of the names listed in the Index
table automatically fill in the employee fields of the continuous form? If I
could hit a command button, I would end up with 14 rows with the Employee
field filled in with the names out of the Employee Index table. Just for
clarification, there is another field on this form that goes with the
Employee's name. That other filed is "Status".

Thanks in advance,
Balfour211

 

[Tom Wickerath]

You should be able to run an append query in VBA code to achieve this result.
Here is an example from a database of mine. You'll need to change the field
and table names for your database:

Private Sub cmdAddProjSchedRecords_Click()
On Error GoTo ProcError

'This procedure inserts a cartesian product set of records into
tblChgReqSchedItems
Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb()

'First, save the parent record, if it is dirty. Set focus back to this
subform, since the
'Form_Current event procedure that fires when the record is saved sets focus
to the "pgeGeneral" page.
If Me.Dirty = True Then
Me.Dirty = False
Me.TabOnDemand.Pages("pgeProjectSchedule").SetFocus
End If

'Create insert statement to insert records into tblChgReqSchedItems
strSQL = "INSERT INTO tblChgReqSchedItems ( fkChangeRequest, fkProjSchedID )
" _
& "SELECT Chng_rqst.pkChangeRequest, tblChgReqSched.pkProjSchedID " _
& "FROM Chng_rqst, tblChgReqSched " _
& "WHERE Chng_rqst.pkChangeRequest= " & Me.Request_rk & " AND
tblChgReqSched.blnActiveSchdItem = -1;"

db.Execute strSQL 'Note: We do not want to include the optional
dbFailOnError parameter. If you do, a user
' could not add a record back in, unless all
records were first deleted.

'Requery subform
Me.ProjectScheduleContainer.[Form].Requery
Me.txtHidden.SetFocus
Me.cmdAddProjSchedRecords.Enabled = False
Me.txtSummaryTotalHours.Visible = True

ExitProc:
'Cleanup
On Error Resume Next
Set db = Nothing
Exit Sub
ProcError:
Select Case Err.Number
Case 2101 'Ignore this error
Case 3022 'Duplicates
MsgBox "You cannot add these records again.", _
vbCritical, "Attempt To Add Duplicate Records Detected..."
Me.txtHidden.SetFocus
Me.cmdAddProjSchedRecords.Enabled = False
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in cmdAddProjSchedRecords_Click event
procedure..."
End Select
Resume ExitProc
End Sub



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________