entering unique number in form

[bluestone]

Hi-

I have a form with a subform. The form has the follwing text boxes,
linked to a table (tbl Patients):
Namelast
Namefirst
MedRec
DOBirth
Sex

The subform has combo boxes linked to another table (tbl Procedures),
for entry of diagnoses and procedures.

My question is: Since I don't want duplicates in the patient table,
the MedRec field is the unique primary key. How do I set the form up
so that a) entry of a new patient is seamlessly allowed, and more
importantly b)if the user attempts to enter the medrec of a patient
already in the DB, the corresponding namelast, namefirst, DOBirth etc
fields are seamlessly and automatically filled in with the correct
data ? Do I need combo boxes for all of these fields ??

Thanks
CL Snyder

 

[tina]

since MedRec is the primary key, you'll automatically get a system error if
you enter a value that's already in tblPatients. you can trap the error
code, and do a "find" on that field, to go to the existing record. you don't
need any combo boxes to support this solution.
note: since you're starting from a position of entering a new record,
you'll probably need to include something like an Undo command before the
Find command, or a Me.Dirty = False before the Find command.

hth

 

[bluestone]

Tina -
Thanks for the reply. I understand the concept, but am too
inexperienced with VBA /macros to implement it - could you elaborate
on specifically how this can be done

I tried selecting properties of the text field, then On Exit use a
macro to find a record but this failed since I don't know how to tell
it to use the entered value.

Thanks

CL Snyder

 

[tina]

i built a sample database, using the table/field names you provided in your
first post. in the form, i added an unbound "Find" textbox at the top of the
detail section, calling it txtFind. i set the tab order of the controls on
my form as follows:

txtFind
MedRec
NameLast
NameFirst
DOBirth
Sex

you gave no details of your subform, so i didn't include it. i don't think
my solution will be affected by the presence of a subform, but you'll have
to test it to find out.
open your form's module (in form design view, click View, Code from the menu
bar) and paste in the following code:

Private Sub Form_Current()

'locks the MedRec control if the current record is an existing
'record, and unlocks it if the current record is a new record.
Me!MedRec.Locked = Not Me.NewRecord

End Sub

Private Sub txtFind_AfterUpdate()

Dim strFind As String

'saves the value entered in txtFind, into strFind, and
'then erases the control, ready for the next search.
strFind = Me!txtFind
Me!txtFind = Null

'moves the cursor to the MedRec control, then does
'a search on that control for the value saved into strFind.
Me!MedRec.SetFocus
DoCmd.FindRecord strFind

'checks to see if a match was found. i did this indirectly
'by comparing the value in the current record's MedRec
'control with the value in strFind. if they don't match, then
'then the cursor moves to a new record, and sets the value
'of the MedRec control to the value in strFind
If Not Me!MedRec = strFind Then
DoCmd.RunCommand acCmdRecordsGoToNew
Me!MedRec = strFind
End If

'whether in a new or existing record, the cursor
'always moves to the next control in the tab order;
'in my sample db, it's NameLast.
Me!NameLast.SetFocus

End Sub

i assumed that the MedRec field in tblPatients is a Text data type. if,
instead, it is a Number data type in *your* table, use the following code
*instead of* the code above:

Private Sub Form_Current()

Me!MedRec.Locked = Not Me.NewRecord

End Sub

Private Sub txtFind_AfterUpdate()

Dim lngFind As Long

lngFind = Me!txtFind
Me!txtFind = Null

Me!MedRec.SetFocus
DoCmd.FindRecord lngFind

If Not Me!MedRec = lngFind Then
DoCmd.RunCommand acCmdRecordsGoToNew
Me!MedRec = lngFind
End If

Me!NameLast.SetFocus

End Sub

if you want to look at the sample database i built to test this code, let me
know and i'll email it to you.

hth

 

[csnyder1]

Hi Tina -
Thanks for all your help -
I was out of town for several days, and just put the code into my DB -
it doesn't seem to work - entering a MedRec already in the patient
table into the txtFind box doesn't do anything. Incidentally, my
MedRec field is text not numeric.

CLS

 

[tina]

well, you didn't really give me anything to go on. my solution works for me,
but didn't for you. i went ahead and sent you the sample i built to test my
solution, using the email address in your post. hopefully that will help.

 

[Charles L. Snyder]

Tina-

Thanks for all your help - the DB you sent me works fine (Although I had
looked at it umpteen times, I had a error in case: TxtFind vs textFind
in the code).

BTW - a) Is there really a need for a new "search" field - one would
think that the same mechanism could be tied to the Med Rec field without
the need for an extra field on the form, and secondly
b) If the med rec is not already in the system, what VB code copies the
contents of txtFind into the MedRec field without requiring the user to
re-type it?


Thanks for your time and solution!!!

CLS

 

[tina]

comments inline.

Tina-

Thanks for all your help - the DB you sent me works fine (Although I had
looked at it umpteen times, I had a error in case: TxtFind vs textFind
in the code).

BTW - a) Is there really a need for a new "search" field - one would
think that the same mechanism could be tied to the Med Rec field without
the need for an extra field on the form,

you have to type a medrec value *somewhere* - how else would the system have
anything to search for? if you type it directly in the medrec control, you'd
have to always go to a new record first, otherwise you'd be *overwriting*
the value already in the medrec field of an existing record. having gone to
a new record, by entering a medrec value in the control, you have now
*started* a new record - which will have to completed, deleted or in some
other way addressed before the Find command can take you to the record you
really wanted to go to.

and secondly
b) If the med rec is not already in the system, what VB code copies the
contents of txtFind into the MedRec field without requiring the user to
re-type it?

from my second post to this thread, on 6/9/04:

'saves the value entered in txtFind, into strFind, and
'then erases the control, ready for the next search.
strFind = Me!txtFind
Me!txtFind = Null

'checks to see if a match was found. i did this indirectly
'by comparing the value in the current record's MedRec
'control with the value in strFind. if they don't match, then
'the cursor moves to a new record, and sets the value
'of the MedRec control to the value in strFind
If Not Me!MedRec = strFind Then
DoCmd.RunCommand acCmdRecordsGoToNew
Me!MedRec = strFind
End If

note that only the portions of the procedure relevant to the question are
copied above.

Thanks for your time and solution!!!

you're welcome!