Copy field from table into subform and be able to change it in subform?

zoey1210

Registered User.
Local time
Today, 16:42
Joined
Apr 11, 2012
Messages
17
Well here I am again. Except for creating needed reports I had the database finished – mostly due to some very excellent solutions here on this forum! Had it opening to switchboards, password protected and the user was doing test runs on entering data. Then she asks if is it possible to put the WC_Code in the employee table and have it copy down to the TimeCards subform when you pick the employee name. I had asked this very question at the startup but she said no she didn’t want that because the code wasn’t always the same. Well apparently it’s ALMOST always the same and she would like the employee WC_Code from the employee table to populate the timecard subform. Then when she is going through the timecard data entry she would change the WC_Code to a different code when that was needed and it would be saved to the time card hours table. Reason is she needs to be able to pull reports by that code and would like to change just the ones that she needed to instead of entering the code for each line on the subform. So back to searching the internet for me. I found a few examples using the autofill options but no matter what I tried I couldn’t make it work. Can anyone help me with this - maybe an easy to understand example that I could follow? Database attached. Thanks for any help you can provide!
 

Attachments

Put this in the BeforeInsert Event Procedure of the subform.

Code:
Me.WC_CodeID = DLookup("WC_CODE", "tblEmployees", "EmployeeID=" & Me.Parent.EmployeeID)
 
I think this should do it zoey:

Code:
    If Not IsNull(Me.EmployeeID) Then
        Dim iWC As Long
        iWC = Nz(DLookup("WC_Code", "tblEmployees", "EmployeeID = " & Me.EmployeeID), 0)
        If iWC > 0 Then
            Dim sWC As String
            sWC = Nz(DLookup("WC_Code", "tblWC_Codes", "WC_CodeID = " & iWC), "")
            If MsgBox("This will set all of these Time Cards to WC Code '" & sWC & "'" & vbCrLf & vbCrLf & "Do you wish to continue?", vbYesNo) = vbYes Then
                CurrentDb.Execute "UPDATE tblTimeCardHours SET WC_CodeID = " & iWC & " WHERE TimeCardID = " & Me.TimeCardID
                frmTimeCardsSubform.Form.Requery
            End If
        Else
            MsgBox "This employee doesn't have a valid " & vbCrLf & "WC Code to assign to the Time Cards."
        End If
    Else
        MsgBox "Please assign an employee first."
    End If

A possible improvement would be to also set the default value for the subform's WC Code to the employee's WC Code in the main form's current event and the EmployeeID's after update event so new entries will automatically get the employee's WC Code:

Code:
Private Sub Form_Current()
    SetDefaultWC
End Sub

Private Sub EmployeeID_AfterUpdate()
    SetDefaultWC
End Sub

Private Sub SetDefaultWC()
    If Not IsNull(Me.EmployeeID) Then
        Dim iWC As Long
        iWC = Nz(DLookup("WC_Code", "tblEmployees", "EmployeeID = " & Me.EmployeeID), 0)
        If iWC > 0 Then frmTimeCardsSubform.Form.WC_CodeID.DefaultValue = iWC
    End If
End Sub
 
Last edited:
Galaxiom,
I put what you listed where you said to and everything appears to be working. Code pops in and it can be changed if needed. Thanks!

VilaRestal,
I’m not sure what the first part of your code is doing. Remember, I’m not good at understanding this code stuff and unless I'm told where to put it I just put it where I think it might go and try it until I either figure it out or give up. Is this code to put the WC Code into records that were entered without the WC Code? That was my best guess – sorry if I’m way off but I just couldn’t figure out where to put it to see what it would do.
The second code you have is (I believe) what I was looking for. I added the WC Code to the tblEmployees and would like to have that code populate the subform on entry so it doesn’t have to be entered manually on each line of the subform (but can still be changed when needed). I didn’t have any better luck on figuring out where to put the second code either. Tried a few different places but no luck. I see that your code is dealing with nulls and while I don’t really understand it all, everything I’ve read says it’s important to account for them. Does this make your solution somehow safer (or cleaner?) than the one from Galaxiom?
Sorry for my basic questions but hope they make sense. Thanks for taking the time to help out with my db problems!
 
Galaxiom's solution is correct except that you should check WC_CodeID for a value first rather than just overlaying it.
 
The first code would go in the event procedure of a button. It would set all the WC Codes for existing Time Card entries to the same as that of the Employee (if the Employee has a WC Code - that's what checking for Nulls is for - in case he/she doesn't).

The second code would go in the main form's module. It will set the default WC Code for new Time Card entries to the same as the Employee's (again, only if the Employee has a WC Code). I've got to say, I prefer to do that than set values on the before Insert event of the subform - I think it's safer, it doesn't actually modify any values. (As Pat says, Galaxiom's method needs more code to check the main form has an Employee and the Employee has a WC Code.) But that might just be an irrational aversion of mine.

I attach the db with my method (I've probably messed around with the data a bit so it's probably best to just import the Time Card form to yours):
 

Attachments

Last edited:
VilaRestal,
I am again in your debt. I should have at least thought about the possibility that the code was for a button but I was so focused on the fields in the form that it didn't even occur to me. Your solution works like a charm! I played with it a bit trying to put it in myself but couldn't get it right so finally I exported your fixed time card form to the database and everything is great. Thank you so much!!!
I hate to keep asking for help but I have a problem that I thought I had solved but hadn't so am going to ask in case it's an easy answer. I put the button on the time card form to go to enter a new job number if it isn't already in the job number table. While testing entering data I found that if you left the time card form to enter a new job number and came back in the job number wouldn't be in the list. I had to close the time card form, reopen it and then the newest job number would show up. So back I went to the internet and I found a solution that said to put the below into the On Close property of the job number form.

Private Sub Form_Close()
Forms!frmJobNumbers.Requery
End Sub


It appeared to solve the problem. When added the number and returned to the time card form the new number was in the list and I didn't have to close and reopen the form. Then the next day the woman who is entering data told me she was having the same problem. I checked it and it wasn't working again so I went back into some of my backups to check and found where it worked in one but then when I went back into that same one a bit later it didn't work. Hoping there might be an easy answer to what is wrong but if not I will go back to it and carefully watch what I'm doing when it works and when it doesn't to see if I can understand it.
Anyway - thanks so much for all your help!!! It is very much appreciated!!!!!!
 
That code is a bit bizarre: requerying a form as it closes :s

Replace the Add Job Number button's macro with this:

Code:
Private Sub cmdOpenJobNumbersForm_Click()
    DoCmd.OpenForm "frmJobNumbers", , , , acFormAdd, acDialog
    Me.frmTimeCardsSubform.Form.JobNumberID.Requery
End Sub

The second line requerys the subform's JobNumber combobox (updates its list). It's important to do that after the JobNumber has been added (after the form has been closed) and the easy way to do that is to open the form as a dialog - the code after won't resume until that form closes. (There are other ways that don't require it to be a dialog but they require more code.)

I hope that makes sense.
 
VilaRestal,
You are the BEST!!! That took care of the problem – the job number is now there when going back to the time card form. Thanks too for the explanation. We’ve found three other problems with the forms. I was able to fix one (operator error on my part by leaving out limit to list). Other two I’m still looking at. One strange thing happened to both me and the data entry woman with timecard records jumping out of entry order as we went along but I haven’t been able to duplicate the occurrence so maybe something you put in fixed that. We will keep watching that one. The last problem is a weird thing that happens when entering a new week start date and I will dig into that to see if I can spot the part that is causing that to happen – nothing earth shattering with either problem since you’ve come in and fixed the big things. Hopefully you won’t see me here in the forms section again. Next up I will probably be in the queries section for help with calculated fields. Just starting on the reports she needs and am hoping I can get it figured out myself but I will probably show up there soon.
Both myself and the data entry woman are very happy with the solutions you’ve provided and we can’t thank you enough. I would like to say an extra thank you for the first part of the WC Code button. It didn’t seem like it was necessary except for updating the records that she entered without putting in the code. Thought that would be really handy to step through the records and click the button to add the code to them. Didn’t see what a huge help that it really was until I went through and entered some of the time and saw that it was a great function when copying a timecard from one employee that had a different code than the next one – one click of the button and the code is changed for the new employee throughout the subform without having to step through each line to change it. You are a genius! Again, and I’m probably starting to sound sickening by now, but thank you so much for your solutions and please know that they really are much appreciated!!! I’ve been telling anyone who asks that I didn’t do the cool things in the database and that the solutions came from a total stranger willing to help out someone who is obviously in over her head. Thank you so very much!!!
 
Wow! I don't think I've ever been thanked so much before :). You're very welcome zoey and thanks for the complements. Thanks too for letting me know how you got on with it.

I hope you can follow the code to some extent. If not please ask. It would be good to understand it so you're in control of it. In future, requirements and designs might change and it would be good if you could adapt the code where necessary yourself with confidence. ("Give a man a fish..." and all that.)

And finally, thank you for making it easy to help you: Clearly defined and compartmentalized problems and uploaded databases. Others here could learn from that ;)
 
Well you certainly deserve the thanks because you’ve helped us tremendously! I realized that I was thanking you over and over again and didn’t want to come off way over the top but I really meant every thanks I gave you because the solutions you provided were excellent!
I am trying to understand the code and why it works because it helps me see what else might be possible but a lot of it is way beyond me – I just know it’s cool! I’ve been trying to get my boss to understand what Access can do – enter the basic data and track everything from there. Hopefully this database is a start and then they can hire someone like you who can deal with it all. My goal is to get them to build a database that tracks payroll and our project data together (because I deal with the project billing and right now the project data is all done in Excel). No offense to Excel fans but I would much rather have it in Access where the data is in one place and you can query to get whatever information you need rather than go back to all the separate Excel files and manually dig out the information from each file. Yuck!
As to me uploading the database, well that’s mostly because as I was searching the internet for help I would come across posts that would list table names/fields, etc., and then list what they were trying to do. Their problem would seem comparable to what I was trying to do but I couldn’t quite grasp the concept without seeing the actual database. So I figured it would be easier to put the database out there so it would be clearer (at least to someone like me). Had to pull out the real data and substitute with fake data which was a pain but I figured in the end there would be less questions about what I was looking to accomplish if you could see what I was actually dealing with.
So, once again I would like to say how much your help has been appreciated and hopefully I can get management to move forward towards Access. Thanks VilaRestal – you are a rock star!!!
 

Users who are viewing this thread

Back
Top Bottom