VBA coded submit button to Enter data for each day of the week

[jackg6]

Hello,
I am currently trying to create a form to enter in an allocated number of hours for a day.
Each entry has 5 aspects:

1. Date
2. Resource
3. Project
4. Position
5. Hours

The tricky part about the form is that I want to be able to enter records for all 5 days of any given week at the same time.

The form is set up with 4 comboboxes on top:

• Resource
• Project
• Position
• Week

And across the bottom are 5 text boxes each labelled with their respective day of the week.

I am very new to VBA but from what I've gathered through research is that the best way to accomplish this is through VBA code behind a submit button. The code would need to grab the data from the first 3 comboboxes for Resource, Project, and Position, then for date it would need to use the Week combobox in addition to a formula to adjust the date depending on which day text box, then take the hours number from inside the text box, enter it into into the table, and then loop through the rest of the days.
So I know what I want to do and how to do it, but I've been researching VBA and trying to learn how to accomplish this and it is confusing the hell out of me. I would really appreciate if anyone could provide any assistance or insight on how I might go about doing this. Also if anyone see's any flaws in my plan or easier ways to accomplish my goal I would appreciate this as well.
Thanks in advance for any help,
Jack

[Perceptus]

Have you tried a split form or do you literally wish to enter the data into the table at the same time for all 5 records? You could use a temp/dummy table to store the data as you prepare it and then have them inserted into the table you store at.

[jackg6]

Yes I've looked into split forms but I didn't think I could really accomplish what I wanted to. This form is going to be used to enter a lot of data in at once all by one person who is only slightly familiar with access, so I wanted to set it up so that you have to reenter data as little as possible. From my understanding, using a split form you would be able to see multiple records but only edit one at a time. Ideally the user of my form would enter in Resource, Project, Position, and Week one time and then enter all 5 numbers and press submit. Then if the user hypothetically wanted to create records for the same Project, Position and week, but a different resource, they would need only to change the resource combobox, and enter the hours for that resource. Please let me know if my understanding of split forms is wrong.

In regards to the dummy table, I'm not really sure what you mean by that or how to go about doing it. I have tried creating a query to display records by day then using it as the source for a datasheet subform that was sorted by the same 4 comboboxes listed above on the parent form. My problem was that in order to create data for a new week, the user would have to enter all of the data for that record into the datasheet subform. I also found it very difficult to get the data to display by week using a query while keeping my data tables normalized and logical.
Is this similar to the approach you meant?

[Perceptus]

any one record is generally entered one at a time.
So if you entered Monday and Tuesday was on a new record. The moment you leave mondays record it would be saved unless you discarded the change.

You could create a dummy table that would have the fields that you need to be filled. And then after all the weeks entries have been made you could then insert that data into your "main" table, check that it moved correctly and then clear out the "dummy" table.

[jackg6]

Ok i think I understand what you're saying but is it possible to set this up to run automatically so that the user doesn't have to worry about the dummy table at all?
I dont really understand how the data would move from the dummy table to the main table. How similar is this approach to the one I mentioned above using a query and subfrom datasheet?
I know that records are submitted one at a time on bound form, but isn't it true that on an unbound form I could use a submit button with VBA code to loop through each record?
I don't necessarily have a problem with the records saving as soon as they are entered, but I don't know any way to set up the text boxes to pull the date using the week combobox in addition to the rest of the data in a bound form.

[jackg6]

I felt like I wasn't very clear on my description of what i wanted my code to do so I am going to try to reword it.

The code behind the submit button needs to submit 5 records. Each of these records will have a resource, project, position, date, and hours field. When the records are saved, resource, project and position will be taken directly from the combo boxes and be the same for all 5 records. The date field will come from the date combo box which is populated with only monday dates, so days will have to be added in order to compensate for the day of the week it is (monday +0, tuesday +1, wednesday +2...).And lastly the hours field will come from the 5 text boxes corresponding to the days of the week at the bottom. And these records need to be saved in a table called allocations that contains all 5 of these fields.
And again, any help would be greatly appreciated.

[ssanfu]

Yes, it can be done. You want to append 5 records using getting the data from controls on a form; calculating the date for 5 days and the hours from controls on the form.

I'm guessing the controls are unbound. If you name the controls correctly, you can use a loop.

The control names
--------------------
"cboMonDate", "cboResource", "cboProject", "cboPosition"

The 5 hour controls would be "tbHours2", "tbHours3', "tbHours4", "tbHours5", "tbHours6"

I don't know your table names or the source for the combo boxes, but maybe the attached dB will help you.

[jackg6]

That solution seems to work very well but there is one issue. The date saves as the monday date for all 5 of the days. I am going to see if I can figure out a solution but any help would be great. Thanks for the help ssanfu!

[jackg6]

Just got it to work using this code. Barely had to change anything to get it to work from ssanfu's version but I put the change I made for the date in red text.

Private Sub Allocation_Submit_Click()
    Dim sSQL As String
    Dim k As Integer




    'check for all values entered/selected
    If Len(Nz(Me.cboMonDate, "")) = 0 Then
        MsgBox "Missing Week. Please make a selection."
        Me.cboMonDate.SetFocus
        Exit Sub
    End If


    If Len(Nz(Me.cboResource, "")) = 0 Then
        MsgBox "Missing Resource. Please make a selection."
        Me.cboResource.SetFocus
        Exit Sub
    End If


    If Len(Nz(Me.cboProject, "")) = 0 Then
        MsgBox "Missing Project. Please make a selection."
        Me.cboProject.SetFocus
        Exit Sub
    End If


    If Len(Nz(Me.cboPosition, "")) = 0 Then
        MsgBox "Missing Position. Please make a selection."
        Me.cboPosition.SetFocus
        Exit Sub
    End If


    For k = 2 To 6
        If Len(Nz(Me("tbhour" & k), "")) = 0 Then
            MsgBox "Missing hours. Please enter hours for " & WeekdayName(k) & "."
            Me("tbhour" & k).SetFocus
            Exit Sub
        End If
    Next


    'build insert SQL string
    For k = 2 To 6
        sSQL = "INSERT INTO Allocations (TheDate, Resource_ID, Project_ID, Position_ID, Hours)"
        sSQL = sSQL & " VALUES (#" & Me.cboMonDate + k - 2 & "#, " & Me.cboResource & ", " & Me.cboProject & ", "
        sSQL = sSQL & Me.cboPosition & ", " & Me("tbHour" & k) & ");"
        '        Debug.Print sSQL
        CurrentDb.Execute sSQL, dbFailOnError
    Next


    Me.Requery


    MsgBox "Done"
End Sub

[ssanfu]

Sorry about that. It was late and I missed the date thingy (technical term ). I thought about having to increment the date, but I have a mind like a sieve and the thought fell through..

Perfect solution...

[jackg6]

No problem you got me 99.9% of the way there anyways

[ssanfu]

Ready to mark this solved??