CODEWITHSUNDEEP
formsms-access
CarlF
CarlF

Reputation: 234

MS-Access 2003: Create multiple records from a single form

Can anyone point me to an example of an Access form which can create multiple records (in a single table) based on one form?

To expand: we're recording information about time spent on a project on a given date. We've had a request for a single form that would allow a user to enter data for 5 (or 7) days of a given week on a single form. He/she would pick a week from a calendar control, a project from a listbox, then enter up to 7 numbers for the hours spent that week.

I did check questions 5294128, which doesn't seem applicable, and question 8070706, which seems to imply that this can only be done in VBA (not using the GUI). Any alternatives?

Thanks.

Upvotes: 0

Views: 8262

Answers (1)

Fionnuala
Fionnuala

Reputation: 91376

Something on these lines should suit. This is an unbound form with a subform.

Form

You can get the form type from the form wizard

enter image description here

To work properly, you will need a little code, say:

Private Sub cmdGo_Click()
Dim rs As DAO.Recordset
Dim sSQL As String
Dim sSDate As String
Dim sEDate As String

    sSDate = "#" & Format(Me.txtStartDate, "yyyy/mm/dd") & "#"
    sEDate = "#" & Format(Me.txtStartDate + Me.txtNoDays, "yyyy/mm/dd") & "#"

    sSQL = "SELECT * FROM MyTable WHERE DataDate Between " & sSDate _
        & " AND " & sEDate

    Set rs = CurrentDb.OpenRecordset(sSQL)

    If rs.RecordCount < Me.txtNoDays Then
        AddRecords sSDate, sEDate
    End If

    Me.DataSubform.Form.RecordSource = sSQL
End Sub



Sub AddRecords(sSDate, sEDate)
''Uses counter table with integers from 0 to
''highest required number
''Another useful table is a calendat table, which would
''save some work here.

    sSQL = "INSERT INTO MyTable (DataDate) " _
        & "SELECT AddDate FROM " _
        & "(SELECT " & sSDate _
        & " + [counter.ID] AS AddDate " _
        & "FROM [Counter] " _
        & "WHERE " & sSDate _
        & "+ [counter.ID] Between " & sSDate _
        & " And " & sEDate & ") a " _
        & "WHERE AddDate NOT In (SELECT DataDate FROM MyTable)"

    CurrentDb.Execute sSQL, dbFailOnError
End Sub

Upvotes: 1

Related Questions