K
Kev
Hello
I have form A based on Table A and subform B based on table B.
If a user chooses an existing record in Table A through 2 combo boxes
in Form A.
Subform B is populated OK.
Table B has an autonumber primary key, long FK (Table A PK) an
EmployeeCode (EmpID) plus 28 text fields.
The choices in the combo boxes are Department and future start dates.
There may be no records with the selected combination in Table A. In
this case I create a new record and save the recordnumber in a
variable. RRid
I also have a Staff table with EmpID, BaseRoster1 and BaseRoster2 and
others.
If a user chooses a start date not present in Table A, I want to
populate the subform with
Those staff who have a base roster or base roster 2 = to the entered
Department as below
SubformB
Autonumber RosterNum (ie. RRid) EmpID
As the staff file is not incuded in any of the recordsources I am
wondering if it is possible to select those staff based on their Base
roster and add the RRid to each staff member and then fill subform B
with these selected records.
If so does anyone know how this may be achieved?
I have a more detailed explaination and some code below
Regards Kev
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/ward 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
I have form A based on Table A and subform B based on table B.
If a user chooses an existing record in Table A through 2 combo boxes
in Form A.
Subform B is populated OK.
Table B has an autonumber primary key, long FK (Table A PK) an
EmployeeCode (EmpID) plus 28 text fields.
The choices in the combo boxes are Department and future start dates.
There may be no records with the selected combination in Table A. In
this case I create a new record and save the recordnumber in a
variable. RRid
I also have a Staff table with EmpID, BaseRoster1 and BaseRoster2 and
others.
If a user chooses a start date not present in Table A, I want to
populate the subform with
Those staff who have a base roster or base roster 2 = to the entered
Department as below
SubformB
Autonumber RosterNum (ie. RRid) EmpID
As the staff file is not incuded in any of the recordsources I am
wondering if it is possible to select those staff based on their Base
roster and add the RRid to each staff member and then fill subform B
with these selected records.
If so does anyone know how this may be achieved?
I have a more detailed explaination and some code below
Regards Kev
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/ward 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