create multiple records with a single form 1

[clickster]

I am trying to make a form that, with the click of a button, creates multiple records from the data that I just entered. I need a date box at the top where I can enter a date. Below that, I need to have 10 rows of names and 3 check boxes for each. I will try to demonstrate below. <cb> means empty checkbox and <x> means checked check box:

<date box>
A B C

John <cb> x <cb>
Jane <cb> <cb> <cb>
Sam x x <cb>
Sally <cb> <cb> x

Some people may have only one box checked, others may have none or multiple boxes checked.
For each CHECKED box, I need a record created that has the person's name, the date that is typed in the date box, and whether it was A, B, or C. If a person has no boxes check, they don't need to have a record that day. If they have 2 boxes checked, they need a seperate record for each A/B/C response. So the table entries for the above form entry would look like this in the end:

Name Date A B C

John 10/29/03 0 1 0
Sam 10/29/03 1 1 0
Sally 10/29/03 0 0 1

I just can't figure out how to get a single form to let me create the multiple records. Since there will be about 100 names, I don't want a form where I have to make the records one at a time. Any help with this problem would be greatly appreciated. Also, I don't have a lot of knowledge of VB or SQL, so if you could tell me how to do it in Access without much coding, it would be easiest for me (i.e. create a make-table query where...and then). Thank you in advance.

 

[cvincent]

I found this because it is my question. Have you gotten an answer to this?

 

[TheAceMan1]

How are ya clickster . . . . .

[blue]I just can't figure out how to get a [purple]single form[/purple] to let me create the multiple records[/blue]

Can't be done without the help of Queries, SQL, or VBA. I would do the following:

1) Make a table to hold the data, and design a form with recordsource based on that table, wether thru table direct, Query, or SQL. This would allow you to add as many records as you like for duplication, specific to the CheckBoxes. [blue]In this way you could pump-up as many names as necessary in one shot[/blue].

2) Thru a Command Button, [blue]run three Append Queries[/blue], one each for each CheckBox, to the destination table!

So . . . . . Ya Think!

See Ya! . . . . . .

 

[willir]

clickster / cvincent

You can do this, but it will require a lot of coding.

Basically, you read through the fields, and, using logic, decide if a record is inserted or not.

Start of with an unbound form.

Now, where do the names come from?

This is code for an example I posted a while back. The criteria was that the end user would make one or more selections from three list boxes with a maximum of five selections allowed within the listbox.

Option Compare Database
Option Explicit
[COLOR=blue]
Private Sub ComittRecord_Click()[/color]

Dim lst2 As ListBox, lst3 As ListBox
Dim varItem2 As Variant, varItem3 As Variant
Dim lngChoice As Long, strType As String, strSelect As String
Dim strMsg As String
Dim booPass As Boolean

booPass = True
strMsg = ""
Set lst2 = Me.lstBox2
Set lst3 = Me.lstBox3

If Nz(Me.ID, 0) > 0 Then
    lngChoice = Me.ID
Else
    booPass = False
    strMsg = strMsg & "No Choice Number provided" & vbCrLf
    Me.ID.SetFocus
End If

If Len(Nz(Me.lstBox1, "")) > 0 Then
    strType = Me.lstBox1
Else
    booPass = False
    strMsg = strMsg & "No Type Selected" & vbCrLf
End If

If booPass Then
    If lst2.MultiSelect > 0 Then
        If lst2.ItemsSelected.Count > 0 Then
            If lst2.ItemsSelected.Count < 6 Then
                For Each varItem2 In lst2.ItemsSelected
                    strSelect = lst2.ItemData(varItem2)
                    [COLOR=blue]CommittRecord2 lngChoice, strType, strSelect[/color]
                Next varItem2
            Else
                booPass = False
                strMsg = strMsg & "Only allowed to select 5 or less listbox #2 items"
                Me.lstBox2.Requery
            End If
        End If
    End If
End If

If booPass Then
    If lst3.MultiSelect > 0 Then
        If lst3.ItemsSelected.Count > 0 Then
            If lst3.ItemsSelected.Count < 6 Then
                For Each varItem3 In lst3.ItemsSelected
                    strSelect = lst3.ItemData(varItem3)
                    [COLOR=blue]CommittRecord2 lngChoice, strType, strSelect[/color]
                Next varItem3
            Else
                booPass = False
                strMsg = strMsg & "Only allowed to select 5 or less listbox #3 items"
                Me.lstBox3.Requery
            End If
        End If
    End If
End If

If lst2.ItemsSelected.Count + lst3.ItemsSelected.Count = 0 Then
    booPass = False
    strMsg = strMsg & "No categories selected" & vbCrLf
End If

If booPass Then
    'clear list boxes if using "extended" instead of "simple" property
    Me.lstBox2.Requery
    Me.lstBox3.Requery
    Me.lstBox1 = ""
    Me.lstBox1 = ""
    Me.ID = lngChoice + 1
Else
    MsgBox strMsg, vbOKOnly
End If

End Sub

[COLOR=blue]
Private Sub CommittRecord2(lngChoice As Long, strType As String, strSelect As String)[/color]

Dim dbs As DAO.Database, rst As DAO.Recordset

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Animals")

With rst
    .AddNew
    !ID = lngChoice
    !Type = strType
    !Category = strSelect
    .Update
End With

rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing

End Sub

You will have to tweak to meet your needs.

In your case, you would have to allow updates for each person x selected letter.

Richard

 

[MorningSun]

Going on AceMan's theory, what if you have a static list of items that you need to enter an amount for for each month. How do you get the form to pull up all of the items when it opens with a box for every month? The table would need to store

Item
JanValue
FebValue
etc

But I'll need to store 40 ITEMS and their associated monthly values from the same form....

Jan Feb March...
Item1
Item2
Item3
...
Item40

Possible??

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.