A couple of list box problems - Please help!

[NDClark]

I have a form named 'Patient Information Form'. This is my main form. On
this form I have a unbound list box that shows all the visits for all the
patients.

Problem #1 - I need the list box to only display the specific visits for
that patient on the main form 'Patient Information Form' at that time.

Problem #2 - I want to click on a specific visit date in the list box and
open another form named 'Doctor Visit form' and edit the data. The issue I
have, when I do click the data is the form opens with no problem. However it
always goes to a form with no ID field and no data to edit.

The property is set to edit on the second form. The Data Mode on the macro
is set to edit.

 

[NDClark]

Please disregard Problem #2 I have solved that one.

I need help on Problem #1 The list box is showing all the records of all the
patients. I need it to show only records pertinant to that particular
patient.

 

[SF]

Modify your Row Source of the listbox to include ClientID. Refresh the
listbox everytime you select/change patient name

SF

 

[NDClark]

The Row Source of the listbox includes the PatientID. All records are still
showing on the listbox of the main form. Also, when I click an entry on the
list box the second form just opens to NEW.

 

[John Spencer]

You apparently need cascading list boxes.

You need to refresh the list box every time the record on the main form changes.

The row source of the list box should be something like
SELECT VisitID, PatientID, VisitDate
FROM VisitsTable
WHERE PatientID = Forms![MainForm]![PatientID]

In the Current event of your main form you would need code like
Me.ListboxName = Null 'Deselect any entry
Me.ListboxName.Requery

I thought you posted that you fixed the second form problem. As a guess, you
have set the Data Entry property on the second form to Yes. This opens the
form to a blank so you can enter a new record and it filters out any records
that already exist.

If you have code that opens the form it is a good idea to post that code so we
can see what you are doing and suggest a modification to your code if that is
needed.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[NDClark]

John, I do have the following set

SELECT VisitID, PatientID, VisitDate
FROM VisitsTable
WHERE PatientID = Forms![MainForm]![PatientID]

I have no code that I have created on the form. The list box was created
from the wizard control.

Now when I click data the second form is going to the first entry for that
patient.

You apparently need cascading list boxes.

You need to refresh the list box every time the record on the main form changes.

The row source of the list box should be something like
SELECT VisitID, PatientID, VisitDate
FROM VisitsTable
WHERE PatientID = Forms![MainForm]![PatientID]

In the Current event of your main form you would need code like
Me.ListboxName = Null 'Deselect any entry
Me.ListboxName.Requery

I thought you posted that you fixed the second form problem. As a guess, you
have set the Data Entry property on the second form to Yes. This opens the
form to a blank so you can enter a new record and it filters out any records
that already exist.

If you have code that opens the form it is a good idea to post that code so we
can see what you are doing and suggest a modification to your code if that is
needed.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

The Row Source of the listbox includes the PatientID. All records are still
showing on the listbox of the main form. Also, when I click an entry on the
list box the second form just opens to NEW.

.

 

[Larry Linson]

Just clicking on the data in a list box does not open another form. What
code is executed when you click on the List Box... that might be in Click,
Double Click, BeforeUpdate, or AfterUpdate? It is that code which will
determine the record to which the new form is going to be opened. It seems
likely that you have a DoCmd.OpenForm statement, and that statement does not
refer to VisitID, but only to PatientID.

But, don't give a "yes" or "no" answer... give us the details. It's
difficult, at best, to debug remotely (but impossible without the necessary
information... remember all the information we have about your database is
what you tell us).

Larry Linson
Microsoft Office Access MVP

John, I do have the following set

SELECT VisitID, PatientID, VisitDate
FROM VisitsTable
WHERE PatientID = Forms![MainForm]![PatientID]

I have no code that I have created on the form. The list box was created
from the wizard control.

Now when I click data the second form is going to the first entry for that
patient.

You apparently need cascading list boxes.

You need to refresh the list box every time the record on the main form
changes.

The row source of the list box should be something like
SELECT VisitID, PatientID, VisitDate
FROM VisitsTable
WHERE PatientID = Forms![MainForm]![PatientID]

In the Current event of your main form you would need code like
Me.ListboxName = Null 'Deselect any entry
Me.ListboxName.Requery

I thought you posted that you fixed the second form problem. As a guess,
you
have set the Data Entry property on the second form to Yes. This opens
the
form to a blank so you can enter a new record and it filters out any
records
that already exist.

If you have code that opens the form it is a good idea to post that code
so we
can see what you are doing and suggest a modification to your code if
that is
needed.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

The Row Source of the listbox includes the PatientID. All records are
still
showing on the listbox of the main form. Also, when I click an entry
on the
list box the second form just opens to NEW.

:

Modify your Row Source of the listbox to include ClientID. Refresh the
listbox everytime you select/change patient name

SF

Please disregard Problem #2 I have solved that one.

I need help on Problem #1 The list box is showing all the records of
all
the
patients. I need it to show only records pertinant to that
particular
patient.

:

I have a form named 'Patient Information Form'. This is my main
form.
On
this form I have a unbound list box that shows all the visits for
all the
patients.

Problem #1 - I need the list box to only display the specific visits
for
that patient on the main form 'Patient Information Form' at that
time.

Problem #2 - I want to click on a specific visit date in the list
box and
open another form named 'Doctor Visit form' and edit the data. The
issue
I
have, when I do click the data is the form opens with no problem.
However it
always goes to a form with no ID field and no data to edit.

The property is set to edit on the second form. The Data Mode on
the
macro
is set to edit.

.

.

 

[NDClark]

I have a macro set up to run OnClick of the list box data. It is set to
OpenForm 'Doctor Visit' when the data is clicked.

Data Mode: edit
Where condition: [Patient ID]=[Forms]![Patient Information Form]![Patient ID]

In the Query for the Listbox I have the following:
Doctor Visit ID Patient ID Date of Visit
Doctor Visit tbl Doctor Visit tbl Doctor Visit tbl

There is no other code on this list box.

The relationships of the tables are as follows:
Patient tbl is linked to the Doctor Visit tbl from
Patient ID-------------------Patient ID

The Patient ID is the Primary Key in the Patient tbl but not in the Doctor
Visit tbl
The Doctor Visit ID is the Primary key in the Doctor Visit tbl


The form does open as it should. The form displays all the records for all
patients. When clicked it only opens to the first visit for that patient.

I am sorry I do not know enough about VB to go deeper that this. If you can
help it would be greatly appreciated.

Just clicking on the data in a list box does not open another form. What
code is executed when you click on the List Box... that might be in Click,
Double Click, BeforeUpdate, or AfterUpdate? It is that code which will
determine the record to which the new form is going to be opened. It seems
likely that you have a DoCmd.OpenForm statement, and that statement does not
refer to VisitID, but only to PatientID.

But, don't give a "yes" or "no" answer... give us the details. It's
difficult, at best, to debug remotely (but impossible without the necessary
information... remember all the information we have about your database is
what you tell us).

Larry Linson
Microsoft Office Access MVP

John, I do have the following set

SELECT VisitID, PatientID, VisitDate
FROM VisitsTable
WHERE PatientID = Forms![MainForm]![PatientID]

I have no code that I have created on the form. The list box was created
from the wizard control.

Now when I click data the second form is going to the first entry for that
patient.

You apparently need cascading list boxes.

You need to refresh the list box every time the record on the main form
changes.

The row source of the list box should be something like
SELECT VisitID, PatientID, VisitDate
FROM VisitsTable
WHERE PatientID = Forms![MainForm]![PatientID]

In the Current event of your main form you would need code like
Me.ListboxName = Null 'Deselect any entry
Me.ListboxName.Requery

I thought you posted that you fixed the second form problem. As a guess,
you
have set the Data Entry property on the second form to Yes. This opens
the
form to a blank so you can enter a new record and it filters out any
records
that already exist.

If you have code that opens the form it is a good idea to post that code
so we
can see what you are doing and suggest a modification to your code if
that is
needed.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

NDClark wrote:
The Row Source of the listbox includes the PatientID. All records are
still
showing on the listbox of the main form. Also, when I click an entry
on the
list box the second form just opens to NEW.

:

Modify your Row Source of the listbox to include ClientID. Refresh the
listbox everytime you select/change patient name

SF

Please disregard Problem #2 I have solved that one.

I need help on Problem #1 The list box is showing all the records of
all
the
patients. I need it to show only records pertinant to that
particular
patient.

:

I have a form named 'Patient Information Form'. This is my main
form.
On
this form I have a unbound list box that shows all the visits for
all the
patients.

Problem #1 - I need the list box to only display the specific visits
for
that patient on the main form 'Patient Information Form' at that
time.

Problem #2 - I want to click on a specific visit date in the list
box and
open another form named 'Doctor Visit form' and edit the data. The
issue
I
have, when I do click the data is the form opens with no problem.
However it
always goes to a form with no ID field and no data to edit.

The property is set to edit on the second form. The Data Mode on
the
macro
is set to edit.

.

.

.

 

[John W. Vinson]

I have a macro set up to run OnClick of the list box data. It is set to
OpenForm 'Doctor Visit' when the data is clicked.

Change this to run in the listbox's AfterUpdate event instead of the Click
event.

 

[NDClark]

Ken, your answer was what I needed to get the form to open to the correct
form and data to edit.

Now, the list box is still showing all the records for all the patients.
There something I missing on how to get the list box to show ONLY the records
for that Patient I am looking at on the main form.

You'll also need to amend the WhereCondition to either:

[Patient ID]=[Forms]![Patient Information Form]![Patient ID] And [VisitID] =
[Forms]![Patient Information Form]![lstVisits]

or simply:

[VisitID] = [Forms]![Patient Information Form]![lstVisits]

might well be enough if VisitID on its own identifies a visit by a particular
patient uniquely.

where lstVisits is the name of the list box on the form.

Ken Sheridan
Stafford, England

I have a macro set up to run OnClick of the list box data. It is set to
OpenForm 'Doctor Visit' when the data is clicked.

Data Mode: edit
Where condition: [Patient ID]=[Forms]![Patient Information Form]![Patient ID]

In the Query for the Listbox I have the following:
Doctor Visit ID Patient ID Date of Visit
Doctor Visit tbl Doctor Visit tbl Doctor Visit tbl

There is no other code on this list box.

The relationships of the tables are as follows:
Patient tbl is linked to the Doctor Visit tbl from
Patient ID-------------------Patient ID

The Patient ID is the Primary Key in the Patient tbl but not in the Doctor
Visit tbl
The Doctor Visit ID is the Primary key in the Doctor Visit tbl

The form does open as it should. The form displays all the records for all
patients. When clicked it only opens to the first visit for that patient.

I am sorry I do not know enough about VB to go deeper that this. If you can
help it would be greatly appreciated.

Just clicking on the data in a list box does not open another form. What
code is executed when you click on the List Box... that might be in Click,

[quoted text clipped - 107 lines]

.

 

[NDClark]

Ken I am still having the same issue is all records are showing on my list
box.

My list box name is 'List156'

I put in the code on the OnCurrent for the form, exactly where you said:
Private Sub Form_Current()
Me.List156 = Null 'Deselect any entry
Me.List156.Requery
End Sub

In the macro for the list box under AfterUpdate I have the following under
the Where condition: [Doctor Visit ID]=[Forms]![Patient Information
Form]![List156]

When I tried to use John Spencer's suggestion of
[Patient ID]=Forms![Patient Information Form]![Patient ID]
It would ask for a Parameter ID for Patient ID

Table #1 is Patient tbl - This table contains my basic information for each
patients, name birthdate, etc.

Table #2 is Doctor Visits tbl - This table contains the doctor visit
specific information, prescription given, blood pressure etc.

The first form is the Patient Information form. It has the list box on it
with the doctor visits displayed. When I click on the record in the list box
the action opens my second form 'Doctor Visit' with the data to edit. The
only issue I have at this point is I need the list box to display only the
records for that particular patient. It is displaying all records.

John Spencer gave you the solution to that earlier in fact with:

The row source of the list box should be something like
SELECT [Visit ID], [Patient ID], [VisitDate]
FROM [VisitsTable]
WHERE [Patient ID] = Forms![Patient Information Form]![Patient ID]

In the Current event of your main form you would need code like
Me.ListboxName = Null 'Deselect any entry
Me.ListboxName.Requery

I've substituted the name you gave for the form in an earlier post for the
example John used. The WHERE clause is the crucial thing and in fact it can
be simplified to:

WHERE PatientID = Form![Patient ID]

which uses the Form property rather than a full reference to the form. This
is possible as the listbox is in the same form as that being referenced. The
SELECT and FROM clauses will be whatever they are at present, using your real
table and field names.

The way this works is that the WHERE clause in the list box's RowSource
property restricts the rows in the list box to those where the Patient ID
matches the Patient ID primary key of the form's current record. This will
not automatically update as you move from patient to patient in the form,
however, so you need the code in the form's Current event procedure to do
this. This code firstly sets the value of the listbox to Null to clear any
selection, and then requeries it so that it lists only the visits relating to
the current patient. You'll need to substitute the real name of the listbox
of course.

If you are unfamiliar with entering code in event procedures you do this by
selecting the form object in form design view and opening its properties
sheet if its not already open. Then select the On Current event property in
the properties sheet. Click on the 'build' button; that's the one on the
right with 3 dots. Select 'Code Builder' in the dialogue, and click OK. The
VBA window will open at the event procedure with the first and last lines
already in place. Enter the two lines of code between these two existing
lines.

Ken Sheridan
Stafford, England

Ken, your answer was what I needed to get the form to open to the correct
form and data to edit.

Now, the list box is still showing all the records for all the patients.
There something I missing on how to get the list box to show ONLY the records
for that Patient I am looking at on the main form.

You'll also need to amend the WhereCondition to either:

[quoted text clipped - 45 lines]

.

 

[NDClark]

WOW~ ! Thanks Ken! I finally have it working. My where clause was not in the
RowSource. When I copied it for you to look at I saw it.

Your code for the form's current event procedure is fine.

You do need to reference the current Patient ID in the WHERE clause of the
list box's RowSource property to get it to be restricted to the current
patient. The fact that you are getting a parameter prompt means that its not
recognizing something in the SQL statement This could be a simple
misspelling of the [Patient Information Form] form name or the [Patient ID]
field name. One possibility is that [Patient ID] is the name of the primary
key of the Patient table on which the form is based, but the corresponding
foreign key field in the Doctor Visits table is named differently; it might
be simply patient for instance. However the WhereCondition you originally
had for your macro suggests it is in fact Patient ID.

If we assume for that moment hat the list box is listing just the dates of
the visit in reverse date order its RowSource would be along these lines:

SELECT [Doctor Visit ID], [Visit Date] FROM [Doctor Visits tbl] WHERE
[Patient ID] = Form![Patient ID] ORDER BY [Visit Date] DESC;

Its other properties would be like this:

BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert them. The important thing is that the first dimension
is zero to hide the first column.

If you still have problems post the current SQL of the list box's RowSource
property back here.

Ken Sheridan
Stafford, England

Ken I am still having the same issue is all records are showing on my list
box.

My list box name is 'List156'

I put in the code on the OnCurrent for the form, exactly where you said:
Private Sub Form_Current()
Me.List156 = Null 'Deselect any entry
Me.List156.Requery
End Sub

In the macro for the list box under AfterUpdate I have the following under
the Where condition: [Doctor Visit ID]=[Forms]![Patient Information
Form]![List156]

When I tried to use John Spencer's suggestion of
[Patient ID]=Forms![Patient Information Form]![Patient ID]
It would ask for a Parameter ID for Patient ID

Table #1 is Patient tbl - This table contains my basic information for each
patients, name birthdate, etc.

Table #2 is Doctor Visits tbl - This table contains the doctor visit
specific information, prescription given, blood pressure etc.

The first form is the Patient Information form. It has the list box on it
with the doctor visits displayed. When I click on the record in the list box
the action opens my second form 'Doctor Visit' with the data to edit. The
only issue I have at this point is I need the list box to display only the
records for that particular patient. It is displaying all records.

John Spencer gave you the solution to that earlier in fact with:

[quoted text clipped - 54 lines]

.

--



.