Hello
I have a form (RosterForm) based on a table - RosterRange
RosterRange has 4 fields:
RosterRangeID Autonumber
RosterStartDate Date
RosterEndDate Date (probably unnecessary)
Ward Text
I have 2 unbound combo boxes looking up values - cmbDepartment and
cmbStartDate. I also have a subform based on the table Roster28Day.
This table has
fields:
28DayID AutoNumber
RosterID Long (same as RosterRangeID in above table)
Emp_ID Long
Shift1 to
Shift 28 Text
Once the user has entered a department and a start date in the
RosterForm,
the RosterStartDate_AfterUpdate() event should look at the RosterRange
table
to see if the combination of Department and Date match an existing
record.
If it does I want to get the RosterRangeID and load the matching
records
from Roster28Day table. This works OK
If there is no matching Department and Date in the RosterRange table,
I want
to insert a new record into the RosterRange table and then populate
the
subform with employees using the new RosterRangeID for the RosterID
(Roster28Day table).
Each employee has 2 fields in their record of the Staff table called
BaseRoster and BaseRoster2. This is the same as the Ward field in the
RosterRange table. The PK in the Staff table is Emp_ID, the same as
Emp_ID in Roster28Day table.
Hence I would like to fill the subform with employees whose
BaseRoster or BaseRoster2 = Me!Department.
Subform fields
28DayID is an autonumber and should be generated automatically,
RosterID is the RRId below
Emp_ID is gathered by be the same as Long
The line which is causing me greif is:
Private Sub RosterStartDate_AfterUpdate()
Dim Dept As String, RRid As Long
Dim RSdate As Date, EndDate As Date
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim rs As Object
If IsNull(Me!Department) Then
MsgBox "You must enter a Department"
Else
Dept = Me!Department
End If
EDate = Me!RosterStartDate + 27
RSdate = Me!RosterStartDate
strSDate = Format$(RSdate, "\#mm\/dd\/yyyy#")
strEDate = Format$(EndDate, "\#mm\/dd\/yyyy#")
strSQL = " INSERT INTO [RosterRange]
(RosterStartDate,RosterEndDate,Ward) " & " _
VALUES ( #" & Me![RosterStartDate] & "#, " & "#" & EndDate & _
"#, " & "'" & Me.Department & "')"
CurrentDb().Execute strSQL, dbFailOnError
strSQL = "SELECT @@IDENTITY" (Thanks to Bobby Heid for this)
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
RRid = rs(0)
rs.Close
strSQL = "Select [Emp_ID] from Staff Where [BaseRoster] = ' "
& Dept & " ' " _
Do I fill the table? (I know the syntax and logic is not right)
strSQL = " INSERT INTO [Roster28Day] (RosterID,EMPID)" & " VALUES ( '"
&
RRid & "', '" & strSQL & "')"
CurrentDb().Execute strSQL, dbFailOnError
or the subform
If so how do I do this.
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
Set rs = Nothing
Set db = Nothing
Else
RRid = DLookup("[RosterRangeID]", "RosterRange", "[Ward] ='" &
Dept & "'
and [RosterStartDate] = #" & RSdate & "#")
Set rs = Me.Recordset.Clone
rs.FindFirst "[RosterRangeID] = " & RRid
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If
I am trying to figure out the issue of populating the sub form via
code.
I presume I need to populate the table (Roster28Day) of the subform
first
and then use this as the record source. Is this correct or is there a
way to
set the recordsource of the subform based on an sql statement and
populate
the table through this.
Could someone please point me in the right direction.
I presume that I need to do a loop or something so that it populates
line by
line - is this correct or am I way off target?
Regards
Kevin
