467,077 Members | 1,145 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,077 developers. It's quick & easy.

Fill a subform with new records

Kev
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

Apr 3 '07 #1
  • viewed: 1928
Share:
1 Reply
Kev
On Apr 3, 9:59 am, "Kev" <kevin.vaug...@nhw.hume.org.auwrote:
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] = ' "
&amp; 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] ='" &amp;
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

Can anyone guide me with this
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

Apr 5 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Sally | last post: by
5 posts views Thread by Thelma Lubkin | last post: by
1 post views Thread by z.ghulam@gmail.com | last post: by
1 post views Thread by troy_lee@comcast.net | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.