Hi Kevin,
For the first part of your question, it appears to me as if you will need
to
run an append query to automatically append the departsments to your join
or
intersection table. This can be set up to run by clicking a button on the
main form, such that the VBA code runs an append query. Here is an example
that I use for a database. Hopefully you can follow it okay. Watch for
word
wrap.
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
I'm not understanding the second part of your question, so if you could
expand on that a bit...
Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
Kevin Labore said:
Hi
I have a question on how to get a list of items from a table to populate
to
the subform (rather than having to enter manually every time)
I currently have a form/subform that works fine but I have to enter the
Dept
(Departments about 50 of them) manually each time rather than just
entering
the sales info. The same entries happen every week. How do I have a form
that will automatically have list in the subform part? I will also need
in
to be in a specific custom order which will be done by a field in the
department table(say SortOrder for the field name) Can you also tell me
where I can find an example of this.
Thanks
Kevin