Add Multiple Records in Access Simultaneously via Entry Form

Hello Experts,

I have posted several questions on how to easily accomplish this, as I am a jack of all trades but master of none.  Perhaps, by providing the following scneario, perhaps I can get sample code to begin this project.  I want a form that will contain 4 controls.  Two controls will be short date parameter fields (unbound I assume) that the user can enter, the other field is a cbo with a set list, and a submit button that will bring it all together and generate multiple records where the cbo value will remain the same, but the dates will incrementally change based on the parameter.  For example:

tblAirLanding

date (date)
Flight (cboFlight)

So, if I enter the following dates in the user form: 4/20/2010 - 4/25/2010 and OZ214 in the combobox and click submit, the following results will be generated in the table:

date            Field2  
4/20/2010   OZ214
4/21/2010   OZ214
4/22/2010   OZ214
4/23/2010   OZ214
4/24/2010   OZ214
4/25/2010   OZ214

A simple code to get me started will do.  What I want to accomplish will be to create multiple records in a table and exprt the results to a SharePoint list where users can acquiire the daily flight times.  Thanks guys...
francodhsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dkalelCommented:
Use the attached code under the on click event in your form

You cannot name a field "Date" so I called it "FltDate"
The fields in my form are "StartDate", "EndDate", and "FlightNumber".  

Let me know if this works for you.


    Dim FltRst As DAO.Recordset, X As Double

    Set FltRst = CurrentDb.OpenRecordset("tblAirLanding")
    With FltRst
        For X = 0 To DateDiff("d", Me.StartDate, Me.EndDate)
            .AddNew
            .Fields("FltDate") = DateAdd("d", X, Me.StartDate)
            .Fields("Flight") = Me.FlightNumber
            .Update
        Next X
        .Close
    End With
    Set ADDRst = Nothing

Open in new window

0
dkalelCommented:
I meant to say, use the code under the On Click event for your submit button.
0
francodhsAuthor Commented:
Great!  I'll give it a shot tomorrow afternoon.  I'll keep you posted.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

dkalelCommented:
I just noticed an error, change line 13 of the code to read:

Set FltRst = Nothing

0
francodhsAuthor Commented:
dkalel,

It worked like a charm.  I have several other issues that will require some work.  Here's the next one:

Using the existing code, some airlines will schedule flights for any given month for only certain days of the week.  So, let's say that AirFrance 84 is scheduled to arrive every Monday and Wednesday for the month of May.  I would assume we would need additonal fields to enter the days of the week and somehow have the recordset only post those dates based on the date paramaters and the selected days of the week.  So, only the calendar dates for Mondays and Wednesdays for May will generate as well as the days themselves.

FltDAte        Flight        Day
4/19/2010    AF84       Monday
4/21/2010    AF84       Wednesday
4/26/2010    AF84       Monday
4/28/2010    AF84       Wednesday



0
dkalelCommented:
Add an option button for each day of the week (don't use an option group since, as far as I can tell, you cannot select more than one entry in an option group).

Name each button "Day1" to "Day7" (Sun to Sat).  Don't worry if you have the buttons in a different order on the screen, just name Sunday's to Day1, etc., so the code will work.

The code below only creates a table entry if the matching weekday button is selected.  The Weekday function has one other option, allowing you to change the starting day of the week (i.e. make Wednesday 1, through Tuesday 7) but the code is simpler, and therefore more elegant (imho), if you leave it at the default and number the controls appropriately.

I added the CurDate variable so access wouldn't have to recalculate each date twice in the code.
Private Sub Submit_Click()

    Dim FltRst As DAO.Recordset, X As Double, CurDate As Date

    Set FltRst = CurrentDb.OpenRecordset("tblAirLanding")
    With FltRst
        For X = 0 To DateDiff("d", Me.StartDate, Me.EndDate)
            CurDate = DateAdd("d", X, StartDate)
            If Nz(Me.Controls("Day" & Weekday(CurDate)), 0) Then
                .AddNew
                .Fields("FltDate") = CurDate
                .Fields("Flight") = Me.FlightNumber
                .Update
            End If
        Next X
        .Close
    End With
    Set FltRst = Nothing

End Sub

Open in new window

0
francodhsAuthor Commented:
You're good my friend.  It works well.  Now, what needs to happen if I want an additional field for "day" to be part of the recordset in order to display the "day" next to the corresponding date.  
0
dkalelCommented:
I wouldn't make it part of the recordset.  Just have it calculated in the form and report you want to display it on.

Just create a text box and have the controlsource set to:  WeekdayName(Weekday(FltDate))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
francodhsAuthor Commented:
Thanks a bunch.  As I work on customizing this project, I may come across some addtional speed bumps.  So, I may need you again buddy.  Thanks again.
0
dkalelCommented:
Any time!  This is actually enjoyable for me, so any time you need help just let me know.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.