Solved

Need to pull last 4 digits from an entered field and store this info in the form and in the table

Posted on 2006-06-26
11
339 Views
Last Modified: 2011-10-03
New employees are entered into our Access database through an Employee Form.  The last 4-digits of their SSN is used as their Payroll number.  I would like to have the 4 digits automatically stored in the Employee table and displayed on the Employee form to avoid an individual mis-keying this information.  
I have limited Access knowledge so an easy to implement solution would be welcome.
A very quick response would be appreciated since we need this field for a Payroll conversion we are performing.
0
Comment
Question by:PJ_KD
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
11 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 300 total points
ID: 16985793
Rather than storing this as an additional field... you already have the full ssn:

- Set up a text box on your form /report named txtPayrollNumber
- In the textbox's property window, control source property:
= Right([YourSSNFieldName], 4)

(include the = sign.)
0
 

Author Comment

by:PJ_KD
ID: 16985942
I had tried this and for some reason the Right function is being rejected as an unacceptable function to use in the Control Source Property.  It was also not accepted in an Update Query.
I also would like to store this 4 digit value in the Table so that I can export the table contents to an Excel spreadsheet that is then imported into our payroll system.
0
 
LVL 13

Assisted Solution

by:lucas911
lucas911 earned 200 total points
ID: 16986043
If you are getting an error using this function, then you probably have some references missing or active X objects are not registered properly.

View your source code then click on tools->references and see which ones are missing and re-click them if need be.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 61

Accepted Solution

by:
mbizup earned 300 total points
ID: 16986531
As lucas911 suggested, check your references.

To update a table field with the last four SSN digits from your form, set up a textbox bound to payrollnumber, and use this code in the afterupdate event of the textbox where SSN is entered:

Private sub txtSSN_Afterupdate()
   me.txtPayrollNumber = Right(me.txtSSN,4)             '** Replace with your control names as needed
End Sub

This will take care of new and changed records.  You will need an update query, as you'd mentioned to extract the last four digits of your existing SSNs into a new field.
0
 

Author Comment

by:PJ_KD
ID: 16987350
Although the Right function should work, even after checking all of the references that could be checked, the Right function is not being recognized.  This database is Access 97 and Right should work, but I can't find what else to check.  I will keep looking.
0
 
LVL 13

Expert Comment

by:lucas911
ID: 16987379
Exit your DB.  The go to START->Run - copy and paste:

regsvr32 C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL

0
 
LVL 13

Expert Comment

by:lucas911
ID: 16987524
0
 

Author Comment

by:PJ_KD
ID: 16987571
I have scanned the C: drive and the Office 97 installation disk and the VBE6.dll file is not present.  I will look on Microsoft's web site and see if there is something that I can download. Thank you for your ongoing comments.
0
 

Author Comment

by:PJ_KD
ID: 16987724
I am looking at the link that you referenced and will see if that helps.  I will be back in touch.
0
 

Author Comment

by:PJ_KD
ID: 17103911
Thanks for both of your assistance.  It took me a while to figure out that I had some extra references that were preventing the "Right" function from working.  After the References in the Module area were set correctly, your help resulted in the resolution of my issue.

Many thanks.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 17116179
Glad to help out.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question