470,831 Members | 1,717 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,831 developers. It's quick & easy.

Creating a Default Value for field in a subform when a field in the subform & form...

Problem: Creating a Default Value for a field in a subform when a field in the subform & form match.

I'm trying to create a payroll database for a small home business that I have that has had (and hopefully will continue to have) lots of growth.

I have created a Transaction table to hold the hours, date, location of work (as the employees work at various locations), pay rate, and employee id. the employee id field has a one-to-many relationship with my employee information table where the employee name, address, and phone number is stored.

I don't think I can create this type of default value in the transaction table, so I've been trying to create it in the subform, but to no avail.

I saw the post http://www.thescripts.com/forum/thread634973.html that discussed a very similar problem and I tried to utilize the code that was placed there, but even after replacing the necessary fields, it still isn't working.

As all the payroll period entries (transactions) will be in one table, the default value for the pay rate needs to be the last pay rate for the same employee id.

Any suggestions?

May 19 '07 #1
4 1743
32,311 Expert Mod 16PB
If you go through the other thread and decide that you would like to use the method that I proposed there, then you'll need to tell me what you've managed to get working or, if nothing, what you tried and what result you got.
I'm psychic, but not THAT psychic ;)
May 19 '07 #2
First of all, sorry for breaking the rules. This is my first time ever using a forum, so I'm trying to assimilate.

Second, I'm not a very advanced coder, so I may be making tons of stupid mistakes. Any help would be appreciated. This is what I have:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Rate_AfterUpdate()
  3.     If Not IsNull(Me.Rate) Then
  4.         Recordset.Edit
  5.         strSave = Me.Rate
  6.         RunCommand acCmdSaveRecord
  7.     End If
  9. End Sub
  12. Private Sub Last_name_AfterUpdate()
  14. DoCmd.RunCommand acCmdRefresh
  16. End Sub
  18. Private Sub Last_name_AfterUpdate()
  19.     ' Find the record that matches the control.
  20.     Dim rs As Object
  21. Set rs = Me.Recordset.Clone
  22.     rs.FindFirst "[Last Name] = " & Str(Nz(Me![Last Name], 0))
  23.     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  25. End Sub
  28. Private Sub First_Name_AfterUpdate()
  30.     ' Find the record that matches the control.
  31.     Dim rs As Object
  33.     Set rs = Me.Recordset.Clone
  34.     rs.FindFirst "[First Name] = " & Str(Nz(Me![first Name], 0))
  35.     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
  37. End Sub
May 23 '07 #3
32,311 Expert Mod 16PB
Having the code posted may well prove useful later on, but I still need answers to my earlier questions. To whit :
  1. Do you want to use the method I proposed?
    I can assume a yes to A.
  2. What (if anything) is working now?
  3. Where things do go wrong - how do they? What error message are you getting if any? Does it stop on a particular line? If so, which one?
I like to think I can help, but I won't be doing it for you. I will work with you to help discover and fix your problems.
Remember also, that I know only what you tell me.
May 23 '07 #4
32,311 Expert Mod 16PB
As a starter, I'll point out some potential problems with the code I see.
  1. Line 5 - strSave = Me.Rate
    strSave is not defined anywhere I can see.
  2. Lines 22 & 34 need quotes (') around the values.
I shall await your response to my earlier questions.
May 23 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by CSDunn | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.