Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Lounger
    Join Date
    Aug 2002
    Location
    Philadelphia, Pa, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need Help With Form (Access 2002)

    I'm a novice who set up a database containing personal info and adresses for applicants for job placement. To reduce the number of duplicates I set the social security box on my form for no duplicates. The only problem is you have to type all the info in and go to the next record before it tells you that it's a duplicate. Is there any way that it can be set up so that when you tab out of the social security box on my form to go to the next box that it would tell you then that it's a duplictate instead of filling out the whole before it tells you?

    Thank you
    Walt

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Help With Form (Access 2002)

    Some simple ideas:

    (1) Make the Social Security text box the last control on the form. If the user tabs away from it, Access wants to move to the next record, so tries to save the current record.
    - OR -
    (2) Instruct the user to try and save the record (Records/Save Record or Shift+Enter) after entering the Social Security number.

    In both cases, the user will get an error message immediately if there is a duplicate.

    For a more sophisticated approach, you'd need to write VBA code for the BeforeUpdate event or AfterUpdate event of the Social Security text box. I don't know if you're familiar with writing code in Access and if you're willing to do so.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Need Help With Form (Access 2002)

    I this is a new record, then in the BeforeUpdate event of your SSN textbox, you can do a DCount to find the # of existing records with same SSN, then take action accordingly:

    If Me.NewRecord=True then
    If Dcount("*","tblApplicants","SSN=" & chr(34) & txtSSN & chr(34)) > 0 Then
    Msgbox "There is already another applicant on file with same SSN!",vbcritical
    Cancel = True
    Exit Sub
    End if
    end if

    It gets a little more complicated if you are checking if the SSN is being changed for an existing record, as you have to exclude the current person.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    New Lounger
    Join Date
    Aug 2002
    Location
    Philadelphia, Pa, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help With Form (Access 2002)

    Thank you for your tips. I'm not familiar with writing code but that is what I'm looking to do so that all the data entry person has to do is type in the social number before continuing as I have other mandatory fields set up so that no info would be forgotten to enter so I don't think it would work because Access would want the other fields filled in before it would create a new record.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help With Form (Access 2002)

    Ow! That's quite a run-on sentence! The code referred to previously would run when the person changed the contents of the SSN field; so the presence of other required fields is irrelevant. me.newrecord is true when you are in the process of creating a new record (i.e. not changing an existing record).

  6. #6
    New Lounger
    Join Date
    Aug 2002
    Location
    Philadelphia, Pa, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help With Form (Access 2002)

    I tried your code changing the table name to mine. I'm not sure what the number means in the parenthesis after chr. I'm assuming that is the number of character spaces. If that is the case my field is limited to 11 characters. I did try changing that 34 to 11 but I keep getting an error message either way. The message I get is "Run-time error '3075' ... Syntex error in query expression 'SSN=(two square boxes)'.

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help With Form (Access 2002)

    If Dcount("*","tblApplicants","SSN=" & chr(34) & txtSSN & chr(34)) > 0 Then
    "SSN=" : replace the SSN with the name of the field containing the Social Security Number in your table.
    chr(34) : is an apostrophe. When you compare text fields you have to put apostrophes around your text.
    If your Social Security Number field is numeric then use :
    If Dcount("*","tblApplicants","SSN=" & txtSSN ) > 0 Then
    txtSSN : replace this with the control name containing the Social Security Number on your form.
    Francois

  8. #8
    New Lounger
    Join Date
    Aug 2002
    Location
    Philadelphia, Pa, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help With Form (Access 2002)

    I guess I'm too much of a novice. Never had any training, and this was my first data base. I'm just not getting it. I want to thank everybody out there for their input and hit the books again to try to get a better understanding.

    Thanks Everybody
    Walt

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help With Form (Access 2002)

    We don't give up so fast in the lounge.
    If you want, you can attach your database so I or somebody else can have a look at it. If the database is to big (max 100k) for an attachment, feel free to send it to me by e-mail (see my profile for my address)
    Francois

  10. #10
    New Lounger
    Join Date
    Aug 2002
    Location
    Philadelphia, Pa, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help With Form (Access 2002)

    Thanks Francois,
    I'll get your email address from your profile and send it to you. I'll have to go back to my original and create a new table to send, as the database is in use and contains personal info.

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help With Form (Access 2002)

    This is the code I add to the before update of the SSN control.
    <pre>Private Sub SSN_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord = True Then
    If DCount("*", "12-01-01 Recruit Applications", "SSN=" & Chr(34) & SSN & Chr(34)) > 0 Then
    MsgBox "There is already another record on file with same SSN!", vbCritical
    Cancel = True
    Exit Sub
    End If
    End If
    End Sub</pre>

    I send you the modified database by e-mail, and attach here a zipped version.
    Attached Files Attached Files
    Francois

  12. #12
    New Lounger
    Join Date
    Aug 2002
    Location
    Philadelphia, Pa, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help With Form (Access 2002)

    It works great now. I don't know what I was doing wrong. Are the spaces critical in the code? The only difference I see from the code that Mark Liquorman posted and yours were the spaces. Could that have prevented it from working properly?
    Thanks Again

  13. #13
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Need Help With Form (Access 2002)

    The chr(n) function returns a 1-byte ascii character for the decimal represented by n. chr(34) returns the double-quote symbol. This eliminates the gyrations on how to wrap a single-quote in a double quote (or vice versa). So instead of something like:<pre> "....WHERE SSN='" & txtSSN & "'"
    You get:
    "....WHERE SSN=" & chr(34) & txtSSN & CHR(34)</pre>

    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  14. #14
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help With Form (Access 2002)

    The only things I changed in Mark's code where the table name and the name of the control (txtSSN replaced by SSN) in the line
    If DCount("*", "12-01-01 Recruit Applications", "SSN=" & Chr(34) & SSN & Chr(34)) > 0 Then
    If you mean the spaces in the name of the table, yes, they have to be there.
    Francois

  15. #15
    New Lounger
    Join Date
    Aug 2002
    Location
    Philadelphia, Pa, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Help With Form (Access 2002)

    I didn't even notice that slight change where you took out the "txt" in front of "SSN". This was my first ever data base and it is only a simple data base but for the people who use it, it's 100% better than what they had before. Now thanks to you it's 200% better. The only thing they had before was a table and everything was typed in manually directly into the table. There were no controls on any of the fields and no way of checking for duplicates. Needless to say it was a mess with many mistakes and duplicates with different address' for the same person. It was useless to our Central Personnel Dept. and they had to enter thousands of applications into their own data base. Now they can just download a copy of our data base. We are a bit behind the times as our computers are not networked so we can't enter directly into their computer. We had to come up with our own data base that would be compatible with theirs.

    Thanks again

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •