Yes, that's correct. The so-called physicians table in fact includes Nurse
Practitioners, and is called the "Professionals table". It contains three
fields: name, specialty (Physician, NP, Dentist, Etc.) and the federal DEA
number. This data is used as historical data and label printing of course. I
just learned that we are NOT filling prescriptions written by outside
physicians, so this aspect is not a problem. But the problem remains for
patients who may not appear in the combo box. That should NOT occur, because
they should be asked if they've received prescriptions before. But you know
how that goes...!
The form for preparing prescriptions is rather more complicated, and in fact
contains 3 sub forms for various reasons. It is not only the primary
prescription form for the table of the same name, but one subform in it takes
care of the details of the original filling and all refills filed in the
Prescription Details table. This table is displayed in a continuous form
within the primary form, and is used to track medications as well as costs
for various reports.
Of course, since this is a relational DB, only the record number for the
patient, physician, and medication, is retained in the primary prescription
table along with instructions, quanty, number of refills allowed, date, etc.
Combo boxes are used to select patient by the last 4 of the social security
number (with confirmation of name) OR by typing in the LAST NAME, FIRST NAME.
Physician is selected by name from a combo box, as is the medication. When
all of this info has been entered into the primary form, entry flow moves to
the details sub form to allow input of what filling or refill it is, the
number actually dispensed (which can differ depending of availability),
auto-date, and then preview and printing of the label.
Refills are a breeze. Enter the prescription number at the top of the form,
and move to the details sub form for one entry (refill #) and accepting the
default number dispensed. Couple of clicks and you have the label and the
record is saved.
Each time that prescription is viewed, you see the entire history of
fillings, running total of fillings, running total of units (tabs, pills,
etc.) dispensed, as well as all the primary info.
--
Norm Shimmel
Butler, PA
Klatuu said:
So you are saying there is a table for physicians and a table for patients
and there are forms for entering data for each of these. Or in the case of
physician, it is only a name? And that the form you are entering data in is
not the form you would be entering patient information in? And, the form you
are working from is a different form where you enter prescription information
for the patient?
--
Dave Hargis, Microsoft Access MVP
:
Thank you so much. I am refining a working application dealing with health
clinic prescription medication distribution. The ideal is that the patient's
data is already in the table and can be selected from a combo box by means of
the last 4 SS digits (with confirmation of name) or by typing the name in a
different combo box.
The selection holds true for the prescribing physician (by name only) who
ordinarily is already employed in the clinic. However, we fill prescriptions
from "outside" physicians as well, and they may or may not be in the
Physicians table.
So unless we check the two tables before begining the prescription entry,
that entry can be a crap shoot. The physician's data is just a name, but the
patient's info includes name, address, phone, etc.
Thanks again.
--
Norm Shimmel
Butler, PA
:
Based on your earlier description of the problem, fredg's code for opening a
data entry form to add the record may be the easiest way to do this. But, If
the form you need to enter the new record in is the form you currently have
open, you would do it a little differently. You would just go to a new
record and populate the appropriate field with the NewData value.
--
Dave Hargis, Microsoft Access MVP
:
Yet another possible solution. Thank you very very much. I'm saving all of
these by the way. I'll check this out too.
--
Norm Shimmel
Butler, PA
:
On Thu, 8 May 2008 06:31:01 -0700, NES wrote:
When filling out a form that has combo boxes, very often I find data is not
yet in the dropdown list. (Data appearing in the list is entered through
another form.)
I've seen that option in the properties list, and I see that it requires the
bound column to show. But then what? I still can't enter data into the "other
form" in order to complete the form I'm working in. I can trigger a jump out
of the current form and display the required input form, but besides not
being very elligant, it wastes an auto-number record number in the original
form. The problem is worse if more than one combo box is involved on the
form.
Any help would be appreciated. Thanks in advance.
If you must open a form to add the new data (it isn't necessary in
most instances), you can code the Combo Box's NotInList event:
If MsgBox("The Agency Entered is not in database, would you like to
add it?", vbYesNo) = vbYes Then
DoCmd.OpenForm "FormName", , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
End If
Code the "FormName" Load event:
If Not IsNull(Me.OpenArgs) Then
Me.[ControlName] = Me.OpenArgs
End If
The form will open with the newly added data in the field.
You can then continue filling in additional information on this form,
such as Agency address, Phone, Contact name, etc.
When you close this form, you will be back in the original form and
the entry will be in the combo box list.
If you do not really need to open a form to enter the new data into
(because other than the one Item, there is no additional associated
data to fill in) you can use a simple Append query code to add the new
data.
If MsgBox("The Item Entered is not in database, would you like to add
it?", vbYesNo) = vbYes Then
CurrentDb.Execute " INSERT INTO TableName(FieldName) Select " &
Chr(34) & NewData & Chr(34) & ";", dbFailOnError
Response = acDataErrAdded
End If
