Still in Training - Need help linking 2 tables and checking for a duplicate

  • Thread starter Thread starter Yvonne Reynolds via AccessMonster.com
  • Start date Start date
Y

Yvonne Reynolds via AccessMonster.com

Still in training , but I have almost everything done except for 1 thing.

I would like to be able to type in a SS#, and access to check to see if I
already have an entry with that SS# in it - If I do, I would like to go to
that record, and if I don't I would like it to allow a new entry on the
form.

Help would be much appreciated (and please remember.. I have a little brain
for Access at the moment, and I';m just learning)

Thanks!!!!

Y
 
With products like access there is so much to learn I have been doing access
since v1 and I am still learning new stuff all the time.

you need some code in the click event of a button the textbox for the SS is
called txtSS and is unbound

dim adoTest as new adodb.recordset
dim strSQL as string
strSQL = "Select Your_Pkey from TblTest Where SS='" & me.txtSS.value & "'"
with adoTest
.open strSQL,currentproject.connection,adopenkeyset,adlockreadonly
if .recordcount > 0 then
me.recordsource = strSQL
me.requery
else
DoCmd.RunCommand acCmdRecordsGoToNew
end if
.close
end with
 
Yvonne,

Let's say there is an unbound textbox in the form header that is called
SelSS. On the AfterUpdate event of this textbox, try code like this...
If DCount("*","YourTable","SS='" & Me.SelSS & "'")=0 Then
DoCmd.GoToRecord , , acNewRec
Me.SS = Me.SelSS
Me.SelSS = Null
Else
Me.SS.SetFocus
DoCmd.FindRecord Me.SelSS
End If

By the way, as an aside, it is not a good idea to use a # as part of the
name of a field or control, so I have eliminated it from my example.
 
You could take the existing form, and place a "search combo box" on the top
of the form.

Try building a combo box using the wizard. The result will be a combo box
that you can type in the ss#, and it will go to that record. if the record
does not exist, it will NOT be in the combo list..and you can simply then
add a new record to the form you are in.

The above will not prevent duplices, but using the combo box wizard to
create a 'search' combo for you is very quick and easy..
 
Back
Top