By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,962 Members | 1,997 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,962 IT Pros & Developers. It's quick & easy.

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

P: 2
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?

THANKS IN ADVANCE!
May 19 '07 #1
Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,344
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

P: 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()
  2.  
  3.     If Not IsNull(Me.Rate) Then
  4.         Recordset.Edit
  5.         strSave = Me.Rate
  6.         RunCommand acCmdSaveRecord
  7.     End If
  8.  
  9. End Sub
  10.  
  11.  
  12. Private Sub Last_name_AfterUpdate()
  13.  
  14. DoCmd.RunCommand acCmdRefresh
  15.  
  16. End Sub
  17.  
  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
  24.  
  25. End Sub
  26.  
  27.  
  28. Private Sub First_Name_AfterUpdate()
  29.  
  30.     ' Find the record that matches the control.
  31.     Dim rs As Object
  32.  
  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
  36.  
  37. End Sub
  38.  
  39.  
May 23 '07 #3

NeoPa
Expert Mod 15k+
P: 31,344
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

NeoPa
Expert Mod 15k+
P: 31,344
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.