Copy subform data to next new record.

[zoey1210]

I’m trying to create a database to keep track of hours worked. Basically it is to track hours worked by work code (B-Day, HOL, Offshore, OT, OT Site, OT Travel, REG, Sick, Site, Standby, Travel, VAC). Time worked now is entered into Excel spreadsheet, printed and written over the next week so there is no collection of data. We want to put it into Access so the data will reside there and they can pull reports from it. My main goal here is to create bare bones database so they can see what Access offers and once they buy into the idea I hope to convince them to hire someone to build a real database for this purpose and incorporate our projects into it because that data is also processed in Excel.

I need help with a couple of things but right now I am looking for help to copy the time card subform data to the subform of the next main form time card entry. Reason is because we often have 2 to 6 employees working on the same job and they all have the same hours. These time cards may have two or more entries per day for each week and it would be simpler to enter the first one and then copy that data down to the next person’s time card. Trying to make this as simple as possible for the data entry person. I’ve found a few examples using a command button. Copied the examples and tried to substitute my field names but couldn’t make it work. Expect that I’m not doing it right but really don’t understand it. Can anyone help with this? I have attached database with sample data in it. Use 4/9 as week start date to open time card entry form. Any help is much appreciated!!!

 

[RainLover]

Your attachment is in 2007 or 2010.

Can you post in 2003.

 

[VilaRestal]

OK, I think this should do it.

I had to change the recordsource of the form so it could be requeried without asking for the Week Start Date again. It is done on the form open event changing the form's recordsource.

The database is attached, the code is as follows:

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
    On Error Resume Next
    Err.Clear
    Dim dtWeekStart As Date
    Dim strIn As String
    strIn = Trim(InputBox("Please enter a Week Start Date:"))
    If strIn = "" Then
        Cancel = True
    Else
        dtWeekStart = CDate(strIn)
    End If
    If Err Then
        Cancel = True
    Else
        Me.RecordSource = "SELECT * FROM tblTimeCards WHERE WeekStartDate = #" & dtWeekStart & "#"
        Me.WeekStartDate.DefaultValue = CLng(dtWeekStart)
    End If
    If Cancel Then MsgBox "The form cannot be opened without a valid start date."
End Sub

Private Sub cmdDuplicate_Click()
    On Error Resume Next
    Err.Clear
    If Me.Dirty Then
        If MsgBox("Save this record first?", vbYesNo) = vbYes Then
            RunCommand acCmdSaveRecord
            If Err Then
                MsgBox "Could not save record." & vbCrLf & "Reason given:" & vbCrLf & vbCrLf & Err.Description
                Exit Sub
            End If
        Else
            Exit Sub
        End If
    End If
    Dim iNewID As Long
    DBEngine.BeginTrans
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("tblTimeCards", dbOpenDynaset)
    With rs
        .AddNew
        !EmployeeID = Me!EmployeeID
        !WeekStartDate = Me!WeekStartDate
        .Update
        .Bookmark = .LastModified
        iNewID = !TimeCardID
        .Close
    End With
    Set rs = Nothing
    CurrentDb.Execute "INSERT INTO tblTimeCardHours (TimeCardID, DateWorked, WC_CodeID, JobNumberID, Hours, WorkCodeID, Notes) SELECT " & iNewID & ", DateWorked, WC_CodeID, JobNumberID, Hours, WorkCodeID, Notes FROM tblTimeCardHours WHERE TimeCardID = " & Me.TimeCardID
    If Err Then
        MsgBox "Could not duplicate record." & vbCrLf & "Reason given:" & vbCrLf & vbCrLf & Err.Description
        DBEngine.Rollback
    Else
        DBEngine.CommitTrans
        Me.Requery
        DoCmd.GoToRecord acDataForm, Me.Name, acLast
    End If
End Sub

 

[VilaRestal]

I probably should have surrounded the code in cmdDuplicate_Click with:

If MsgBox("Are you sure you want to duplicate this record?", vbYesNo) = vbYes Then


End If

 

[zoey1210]

VilaRestal you are a GENIUS!!! I messed around with the Allen Browne instructions and a few other variations I found online for several days. No matter what I did I couldn't make it work. You cannot know how much I appreciate your figuring this out for me!!! I checked through the code to compare it with what I was trying and I see some things the same but yours appears to have more in it. Part of that would be where you took out the parameter for the date in the query and put it in with the command button. Since I don't know much about coding I didn't even know you could do it there. Everything appears to work beautifully. I will work it into my real database now. As to the last part about surrounding the code with the duplicate record question - well I don't know what "surrounding the code" means and didn't know where to put that but I think it's okay because I think it would be hard to click the command button by mistake. (Or does leaving that out fall under bad database design?) Thank you so much for providing the solution to this!!!

 

[zoey1210]

Rain,
This was solved by VilaRestal but am attaching database in 2000 and 2003 formats in case someone else wanted to know what I was talking about. Should have thought of that in the first place. Thanks for the offer of help!

 

[zoey1210]

VilaRestal,
I've got your code copied to my database. So COOL - it all works!!! I even played with trying to get the errors messages in your code to pop up. After all my frustration of trying this and that for days and you just zip it out! I just have one question now. Is it possible, when you copy the record, to leave the employee name blank so you have to pick it? Just thought it might make less confusion if it came up with no name because when things get busy and you are interrupted when entering data you might forget that you copied that persons time and think it's the original entry and then go on. If it's not an easy thing then that's okay I will just tell the data entry person to pay attention. Again, thanks so much for your help in fixing this!!!

 

[RainLover]

Leaving the Employee out does not appear to be a bad idea.

Glad to see you solved your problem.

 

[VilaRestal]

You're very welcome zoey. I'm glad it's working for you.

As Rain says, just leave out the employee. In case you wonder, to do that just remove the line:
!EmployeeID = Me!EmployeeID

To ask for confirmation of the click you could just put:

If MsgBox("Are you sure you want to duplicate this record?", vbYesNo) = vbNo Then Exit Sub

At the start of the Sub.

As for the errors not happening. It's just good practice to handle errors even if you don't think they're possible. Tables and queries can change. Code can change. Users can do things you never imagined.

 

[zoey1210]

I did get some of the error messages to pop up. I was looking at your code and trying to figure out what was what in there. I didn't understand most of it but I like to check things out because sometimes things click. I like to know why it's working if I can. I took the database in today so the woman who will be using it can offer suggestions before the final product and she thought the copy button was great - a huge time saver. She said I was the best so I had to show her where I got the solution from and she said that you are the best and she says thank you to you too. Tonight I put the duplicate record message in where you said and it works perfect. As to making the copied record show up without the employee name in there that didn't work. I actually tried that yesterday as that seemed the logical way to do it but I got a popup box with "Could not duplicate record. Reason given: Invalid use of Null" in it. Since you said it would work I thought I probably did it wrong and tried again but it still comes out with the same message. If we have to open copied record with same name the data entry woman will just have to pay attention. You now have two people who are very happy you could provide the solution for the copy button!!!

 

[RainLover]

Zoey

Check the Table. Do you have the Employee Field set to Required?

If so change it.

Please don't accept "Close enough". I am sure we can get this working.

 

[zoey1210]

Maybe this falls into bad design category? When making the database I used several different databases as examples, checking how things worked to figure out how to put this one together. In the time card table I have the employeeID display control set to combo box and the row source is SELECT tblEmployees.EmployeeID, [LastName] & ", " & [FirstName] AS EmployeeName FROM tblEmployees ORDER BY [LastName] & ", " & [FirstName]; - this was to get the employee name instead of number to show up. The empID in time card table is a number linked to the empID (autonumber) in the employee table. If I change required to No for empID in the time card table and remove the employeeID from the command button code I don't get the Null error message and the new record opens up with no employee name in the field but then it doesn't copy the subform data from the record I copied from.

 

[RainLover]

zoey

Can you post the latest in Version 2003.

Are you doing Two updates.

One for the main form and another for the sub.

 

[zoey1210]

Rain,
I've saved the database where VilaRestal added the command button code to version 2002-2003. Not sure what you mean about the two updates though. As you probably can tell I'm not very versed in this stuff. I know Access is powerful and you can do so many things in it - I just don't know how to do most of it.

 

[VilaRestal]

I see the problem. The subform is based on a query that includes an inner join to the employee table. The code is adding the items but they don't show until it has an employee.

To fix just change the recordsource of the subform from that query to tblTimeCardHours.

 

[zoey1210]

VilaRestal/Rain,
I changed Yes required to No in tblTimeCards, removed the EmployeeID line in the code and switched the subform record source to the tblTimeCardHours and it appears to be working. The duplicate button now copies subform info over and the employee field is empty. Thank you both for the help. I did try changing only some of them but it wouldn't work until all changes were made - had to try though. Of course some answers bring up more questions and I do have one now about form creation. When I was learning Access (way back when) our instructor told us to always base our forms on queries and not the table itself. Not sure what the reason was but I went along with it. Is there a reason? Should I base all forms on the tables when possible?
I have gained a little bit more knowledge from your help and hopefully I can put the finishing touches on this and get it up and running. So again, to both of you - thank you very much for the solutions you've provided!!!

 

[VilaRestal]

It's not a general rule I follow. It is good practice if the table has many fields - it's more efficient to only select the ones you need. However, it's more work. If you add a field to the table you have to remember to adjust all queries to include it.

In most cases binding to a table is fine IMO.

 

[RainLover]

On the other hand I use Queries all the time.

I like the added flexibility it gives.

Sorting is just One.

But then it is up to you. Neither way is incorrect. Just a matter of what suits you best.

 

[zoey1210]

VilaRestal/Rain,
Thanks for your opinions on that. I will watch and see if I can figure out the differences using tables vs queries. And again, thanks for your help with my command button problems. Now it's back to the database to finish it up and hopefully I won't have to come back with yet another problem, but if one pops up rest assured you all here will be hearing from me again! Your help with all this was amazing!!!

 

[RainLover]

Another satisified customer.

It would be nice if you took the time to set up your Control Panel properly.

Look at the left of my post for an example.

It is nice to know where you reside. USA, UK or if really lucky Down Under. This helps with when one could expect to see you online and also with Regional Settings.

Also what systems are you using. e.g. Windows Version and Access Version.

It all helps.