Need Help With Form (Access 2002)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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).

Reply | Quote

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.

Reply | Quote

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)’.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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)

Reply | Quote

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.

Reply | Quote

This is the code I add to the before update of the SSN control.

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

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Hi Francois

Just curious, In your example, why would’nt you code like this to check add and edit ss dups with the same message?

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

John

Reply | Quote

You could do it but if you enter the exact same ssn you will also get the message.
If you want to control the edit, you best add a condition to exclude the current record.

Reply | Quote

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:

	 "....WHERE SSN='" & txtSSN & "'"
You get:
	"....WHERE SSN=" & chr(34) & txtSSN & CHR(34)

Reply | Quote