Automatically create records

[Mark]

Hi all,
What I am trying to achieve is for a user to enter a colleague
identification number into field [ID]. Then they are to add the first date
of holiday into field [f_date]. Finally they are to add the number of days
into an unbound text box [days]. Once a command is fired, I would like to be
able to add records into tbl_holidays. One for each date the person has
recorded.

e.g.
Input form:

ID f_date Days
10089654 16/11/2004 5

records to be added into table after closure of form:

ID booked_hols
10089654 16/11/2004
10089654 17/11/2004
10089654 18/11/2004
10089654 19/11/2004
10089654 20/11/2004

TIA,

Mark

[Allen Browne]

This example assumes that:
- ID, f_date, and Days are unbound text boxes on your form;
- you wish to add records to Table1, which fields named ID and f_date;
- the code is to run when you click a button named cmdAddRecords
- there are actually entries in all 3 boxes.

Private Sub cmdAddRecords_Click()
Dim rs As DAO.Recordset
Dim dt As Date
Dim dtEnd as Date

dtEnd = DateAdd("d", Me.Days - 1, Me.f_date)
Set rs = dbengine(0)(0).OpenRecordset("Table1", _
dbOpenDynaset, dbAppendOnly)
For dt = Me.f_date To dtEnd
rs.AddNew
rs!ID = Me.ID
rs!f_date = dt
rs.Update
Next
rs.Close
Set rs = Nothing
End Sub

That's completely untested, but should give you an example to work from.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mark" <ma*********@ntlworld.com> wrote in message
news:Vh*************@newsfe5-gui.ntli.net...

What I am trying to achieve is for a user to enter a colleague
identification number into field [ID]. Then they are to add the first date
of holiday into field [f_date]. Finally they are to add the number of days
into an unbound text box [days]. Once a command is fired, I would like to
be able to add records into tbl_holidays. One for each date the person has
recorded.

e.g.
Input form:

ID f_date Days
10089654 16/11/2004 5

records to be added into table after closure of form:

ID booked_hols
10089654 16/11/2004
10089654 17/11/2004
10089654 18/11/2004
10089654 19/11/2004
10089654 20/11/2004

TIA,

Mark

[Mark]

Alan,
Thankyou very much. I had a slight problem when running the code with an
error message stating "Compile error: User-defined type not defined". I did
a quick check on the net and found that I could overcome this by checking
the microsoft DAO 3.51 object library from references.

Once this had been rectified, the code ran like a dream. Thanks again, much
appreciated.

Mark

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

This example assumes that:
- ID, f_date, and Days are unbound text boxes on your form;
- you wish to add records to Table1, which fields named ID and f_date;
- the code is to run when you click a button named cmdAddRecords
- there are actually entries in all 3 boxes.

Private Sub cmdAddRecords_Click()
Dim rs As DAO.Recordset
Dim dt As Date
Dim dtEnd as Date

dtEnd = DateAdd("d", Me.Days - 1, Me.f_date)
Set rs = dbengine(0)(0).OpenRecordset("Table1", _
dbOpenDynaset, dbAppendOnly)
For dt = Me.f_date To dtEnd
rs.AddNew
rs!ID = Me.ID
rs!f_date = dt
rs.Update
Next
rs.Close
Set rs = Nothing
End Sub

That's completely untested, but should give you an example to work from.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mark" <ma*********@ntlworld.com> wrote in message
news:Vh*************@newsfe5-gui.ntli.net...

What I am trying to achieve is for a user to enter a colleague
identification number into field [ID]. Then they are to add the first
date of holiday into field [f_date]. Finally they are to add the number
of days into an unbound text box [days]. Once a command is fired, I would
like to be able to add records into tbl_holidays. One for each date the
person has recorded.

e.g.
Input form:

ID f_date Days
10089654 16/11/2004 5

records to be added into table after closure of form:

ID booked_hols
10089654 16/11/2004
10089654 17/11/2004
10089654 18/11/2004
10089654 19/11/2004
10089654 20/11/2004

TIA,

Mark

[Allen Browne]

Well done.

If you did not have a DAO reference, you are probably running Access 2000 or
2002, because all other versions have the reference by default. In these
versions (and Access 2003), you should be using the DAO 3.6 library, not
3.51.

More info on references:
http://members.iinet.net.au/~allenbrowne/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mark" <ma*********@ntlworld.com> wrote in message
news:sZ************@newsfe5-gui.ntli.net...

Alan,
Thankyou very much. I had a slight problem when running the code with
an error message stating "Compile error: User-defined type not defined". I
did a quick check on the net and found that I could overcome this by
checking the microsoft DAO 3.51 object library from references.

Once this had been rectified, the code ran like a dream. Thanks again,
much appreciated.

Mark

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

This example assumes that:
- ID, f_date, and Days are unbound text boxes on your form;
- you wish to add records to Table1, which fields named ID and f_date;
- the code is to run when you click a button named cmdAddRecords
- there are actually entries in all 3 boxes.

Private Sub cmdAddRecords_Click()
Dim rs As DAO.Recordset
Dim dt As Date
Dim dtEnd as Date

dtEnd = DateAdd("d", Me.Days - 1, Me.f_date)
Set rs = dbengine(0)(0).OpenRecordset("Table1", _
dbOpenDynaset, dbAppendOnly)
For dt = Me.f_date To dtEnd
rs.AddNew
rs!ID = Me.ID
rs!f_date = dt
rs.Update
Next
rs.Close
Set rs = Nothing
End Sub

That's completely untested, but should give you an example to work from.

"Mark" <ma*********@ntlworld.com> wrote in message
news:Vh*************@newsfe5-gui.ntli.net...

What I am trying to achieve is for a user to enter a colleague
identification number into field [ID]. Then they are to add the first
date of holiday into field [f_date]. Finally they are to add the number
of days into an unbound text box [days]. Once a command is fired, I
would like to be able to add records into tbl_holidays. One for each
date the person has recorded.

e.g.
Input form:

ID f_date Days
10089654 16/11/2004 5

records to be added into table after closure of form:

ID booked_hols
10089654 16/11/2004
10089654 17/11/2004
10089654 18/11/2004
10089654 19/11/2004
10089654 20/11/2004