NotInListProblem

  • Thread starter Thread starter Bernd
  • Start date Start date
B

Bernd

Hi,
I've a form with two comboboxes. The first contains a
list of patientnames and the second the related date of
visits (related to each other by a relationship one-to-
many on PatientNameID, which contains the id number of
the patients). When I click on a patient name in the
first combo, when I drop down the second I see all the
dates of visits of that patient. When I add a patient
name on the first combo, everything is all right, but
when I try to add a date to the selected patient there
compares the following error message:
"You cannot add or change a record because a related
record is required in table "tablename""
I also tryed to add a WHERE condition in the code
inserted on NotInList event of the combo, but the result
is a syntax error of Insert Into.
How can I resolve this problem?
Thanks
Bernd
 
I'm not sure the date combo box is the way to go. If each patient could have
many dates associated with his or her name, there is a one-to-many
relationship between Patient and Date. You will need a Patients table
(tblPatient) and a Dates or Office Visits table (tblVisits). They would
contain fields along the lines of:

tblPatient
PatientID (primary key, or PK)
Patient last name
Patient first name
etc.

tblVisits
VisitsID (PK)
PatientID (foreign key)
VisitDate

PatientID (in tblPatient) can derive from your own system of ID numbers,
or it can be an autonumber PK. In tblVisits the filed needs to be of the
same data type, except that if you use autonumber in tblPatient you will use
Number (not autonumber) as the field type in tblVisits.In the Relationships
window (Tools > Relationships), drag PatientID from one table on top of
PatientID in the other, and let go.
Now make a form (frmPatient) based on tblPatient, and another (frmVisits)
based on tblVisits. Consider using autoform, which is fast if not elegant.
With frmPatient open in design view, drag the icon for frmVisits onto
frmPatient, and let go. Arrange as needed. Now when you use your combo box
to select a patient, you will see all of the related dates. Depending on the
size of your subform (frmVisits) you may need to use the scroll bar to see
them all. You could probably devise a query to extract dates just for the
selected patient, then use that query as the basis for a combo box, but I'm
not sure how that would work with adding records. A combo box can be used to
populate a field, but I'm not familiar with using one to add a record in the
way you describe.
 
Hi,
First thanks for answer, but
in your feedback you described the exact situation that I
have, but you didn't answer to my question?
In that case that you described, how can I add a record
to the tblVisits if the 2 tables are related and
everytime I try to add a record, Access gives me an error
message (You cannot add or change a record because a
related record is required in table "tablename"")?
Thanks
Bernd
 
I should probably have read your initial question a bit more carefully. Do I
understand correctly that the problem occurs specifically when you are adding
a new patient name? Can you add dates to old records, or to the new record
if you navigate away from it and then back? If so, try adding:
DoCmd.RunCommand acCmdSaveRecord to the after update event for the patient
name combo box. Also, are you using a subform for the dates, or a combo box?
As I said, I'm not sure just how this would work with a combo box, but I
know I have done almost exactly what you describe with a form/subform
arrangement.
 
Hi,
No I can't add a new date even on an old record even on a
new record (patient). In both the cases the compiler
gives me the same error message.
I'm using a combobox both for the patients as for the
associated dates.
Could you, please, describe exactly how I can implement
this situation using a subform for the dates?
Thanks
Bernd
-----Original Message-----
I should probably have read your initial question a bit more carefully. Do I
understand correctly that the problem occurs
Click to expand...
specifically when you are adding
 
tblPatient
PatientID (primary key)
First
Last
etc.

tblVisits
VisitID (primary key)
PatientID (foreign key)
Date

Use whatever you want for the PatientID in tblPatient, as long as it is
unique. I prefer using numbers for primary key (PK). If you don't have a
patient ID, make the field's data type Autonumber in table design view. Same
idea for VisitID. Right click the field in table design view and designate
it as the PK field. Do this for both tables. For PatientID in tblVisits, in
table design view make the data type number if you are using autonumber for
PatientID in tblPatients; otherwise use the same data type for both PatientID
fields.
Save and close both tables. Click Tools > Relationships. In the dialog
box, select each table and click Add, then click Close. Drag PatientID from
tblPatient on top of PatientID in tblVisits, and let go. Click "Enforce
Referential Integrity". Worry about the other two check boxes later. Close
the relationship window, and save when prompted.
In the database window (the listing of tables, queries, forms, etc.) click
tblPatient, then click Autoform on the toolbar (has a sort of lightning
bolt). Switch to design view for the form, drag the bottom border of the
form to create some blank space, and save it as frmPatients. Similarly,
create a form from tblVisits, and name it fsubVisits. Double click the
little square box at the very top left of the form to open the form's
property sheet. Click Format, and change the default view to Continuous.
Save and close the form. Open frmPatients in design view. Drag the icon for
fsubVisits from the database window onto frmPatients, and let go when it
turns into a + sign. Get rid of any text boxes you don't need, such as
PatientID and VisitID on fsubVisits, and any text boxes for autonumber fields
in either form. Take a few minutes to arrange things on the forms. Enter a
patient name, and some visit dates. Use the navigation arrows at the bottom
of the main form to create a new patient record, and again add a name and
some dates.
That's the basics of it. Your actual needs will probably lead to more
questions, but get the basic database structure working first. Good luck.
 
Back
Top