MS Access Database- Survey Issue

Kinlene
Kinlene used Ask the Experts™
on
To whom it may concern:

I am working on a MS ACCESS database in which users take a simple "Yes" and "No" survey (See Attachment).  The survey use's Social Security Numbers as the primary key.  I realize this isn't normal, however, my military "customer" requires the SSN for this application, so I have no way around it.  I must use the full SSN.  The database application functions perfectly except for when a user has a "Zero" in the beginning of their SSN.  I have set the data type to text and the input mask is set to be a SSN.  I have provided the original database (See Attachment) where the SSN was originally a field named "PerticipantID".  This eventually became the SSN in the final application.  I migrated this survey application into my finished application with little trouble.  It wasn't until users started using the new survey, that I identified the issue, so I checked the provided original (See Attachment) and that too is having the same issue.  I had originally thought I missed something, but when I saw the original survey had the same issue, I realized is must be something else.  Note:  Anyone else with any other SSN or Participant ID other than one that starts with a "Zero" works just the way I wanted it to.  Any assistance you could provide after review of this survey will be greatly appriciated.

Edward J Kinlen
Survey-Database.accdb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Nerd
Most Valuable Expert 2012
Top Expert 2013
Commented:
The problem is that in the INSERT query in the After Update event of your SSN textbox on your survey form, you are treating the SSN as a number (and it loses the leading zero).

To treat it as text, you need to embed SSN in single quotes.  This will work:


CurrentDb.Execute "INSERT INTO tbl_Answers(PerticipantID,QuestionID)VALUES('" & Me.SSN & "'," & rst("QuestionsID") & ")"

Open in new window

Author

Commented:
It worked thanks!
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
Glad that helped out :)
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Are you SURE you really want to store a full SSN in an Access database? Are you SURE you want that responsibility ... if someone walks off with the db? Access dbs have essentially NO security.

Just sayin' ...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial