Creating Multiple Records

[Secret Squirrel]

I have a table with the following fields in it:

VacID
VacDate
EmpName
Hours

What I'm looking to do it create a form that I use to enter this info into.
The table is used to track vacation days by employee. But there needs to be a
new record for each vacation day an employee takes. The problem is I want my
users to be able to enter a date range of vacation days and have it create
new records for each day. For example if an employee takes 08/25/08 thru
08/29/08 I want a new record created for each day for that employee. I could
have the users create a new record for each day but that seems a bit tedious.
I want them to be able to enter a starting date, and ending date and then
have records created for each day including the starting and ending dates.
Can this be done?

 

[Larry Linson]

Yes, you can add the Records using either VBA DAO (or, ugh, ADO) code which
may add new Records or may execute a Query that adds the new Records. But,
there's not a built-in Form feature to do what you want, if that's what you
meant.

I don't do Macros, except on very rare occasions, an AutoKeys Macro, but
there's some possibility that you might be able to use a Macro to execute a
Query to do this.

Larry Linson
Microsoft Office Access MVP

 

[Secret Squirrel]

That's what I meant. I can run it through a form but have the query execute
it. How would I start this process using DAO? Can you possibly point me in
the direction I should start from?

 

[a a r o n _ k e m p f]

nothing wrong with ADO.
Adobe / MacroMedia have been using ADO for the past decade.

I can name a half dozen 10,000 + employee companies in Seattle.. where
I've build ADO applications that work just perfectly; thanks

-Aaron

 

[pietlinden]

I have a table with the following fields in it:

VacID
VacDate
EmpName
Hours

What I'm looking to do it create a form that I use to enter this info into.
The table is used to track vacation days by employee. But there needs to be a
new record for each vacation day an employee takes. The problem is I wantmy
users to be able to enter a date range of vacation days and have it create
new records for each day. For example if an employee takes 08/25/08 thru
08/29/08 I want a new record created for each day for that employee. I could
have the users create a new record for each day but that seems a bit tedious.
I want them to be able to enter a starting date, and ending date and then
have records created for each day including the starting and ending dates..
Can this be done?

You would need two unbound text fields formatted as Date, say
txtVacStart and txtVacEnd Then you would add a button to your form
and ahve it do something like this...
Private Sub cmdAddVacationDays_Click()
Dim dtVacDay As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("VacationDays", dbOpenTable,
dbAppendOnly)

For dtVacDay = Me.txtVacStart To Me.txtVacEnd
rs.AddNew
rs.Fields("EmployeeID") = Me.cboEmployeeID
rs.Fields("VacationDay") = dtVacDay
rs.Update
Next dtVacDay

rs.Close
Set rs = Nothing

End Sub

 

[posted_by_anonymous]

nothing wrong with ADO.

It's been superseded by ADO.NET which is something else different. It's just
kept around for backward compatibility because it was hyped so much by
marketing that it lured a few people into using it. Some, like aaron the
troll, became true believers for whom it did not have to be better to become
their obsession. It's obsolete but a few in Microsoft development are
pretending it's only obsolescent and nursing it along until they can move
everyone to ACE.

Adobe / MacroMedia have been using ADO for the past decade.

ADO.NET has been out, replacing classic ADO where things count at Microsoft,
the DotNet world, for _nearly_ a decade. You must think the word decade has a
nice ring to it because you keep using it where it clearly is not true.

I can name a half dozen 10,000 + employee
companies in Seattle..

You can? What a pleasant surprise that you have that much intelligence.

where I've build ADO applications that work
just perfectly; thanks

Given the quality of your advice and postings here, it's hard to imagine
that you have done even one application of any kind that work even
marginally, much less "perfectly". It's not hard to imagine that you have
such a swellled head that your imagination would reshape anything you did to
your view of "perfect".

Buh-bye, cutie pie,

Anony Mous

 

[Secret Squirrel]

Thanks for your help! Question: If I leave the end date control blank will it
just add a record for the date in the start date control? Also, how do I
avoid it adding weekends?

 

[George Hepworth]

IMHO, the best way to deal with trolls is a two-part strategy.

1. Ignore their standard rantings.
2. Step in when it is necessary to correct a misstatement that could
potentially mislead a novice poster.

While it is true that novice posters don't know a great deal about Access,
it is also true that most of them are smart enough to figure out quite
quickly who the trolls are.