Filter records on Patient ID and Record Status

[Guest]

I have a database that has one main table and form that contains 'edit'
records of all records that have data entry mistakes. Each of these records
has a status ('New', 'Fixed', 'In Process', etc.).

On the Switchboard below, I also have a Combo box that looks up to
another table (different from the 'Status' table that acts as the actual
Record Source for my Switchboard form) to get all the unique patient
records/IDs from the data set so a patient record can be chosen.

From the Switchboard, our user would like to: 1) Click on a command button
that will get a Count of all records that have a certain status (e.g., 'New',
'Fixed', 'In Process', etc.). 2) Then, they would like to click on another
command button that would open up the form that contains all Edit Records
with the Status they just got the Count from (in Step #1 above), so they
could scroll thru those records for review. 3) Finally, how I can get all
records with a status of new AND pertain to the patient selected from my
combo box on the Switchboard form ('fEnterPatientInfo')?

What I need is to be able to add this this code:

DoCmd.OpenForm "FormStatus", , , "Status = 'New' "

I've played with variations of the code below, but
cannot get it to fly:

DoCmd.OpenForm "FormStatus", , , "Status = 'New' " & [patient]=
[forms]![fEnterPatientInfo]![selectpatient]

where [patient] is the Patient ID number in the header of the form
"FormStatus" that I would like to filter on (in addition to the 'Status'
field).

Thank you.

 

[Michel Walsh]

Hi,

DoCmd.OpenForm "FormStatus", , , "Status = 'New' AND patient=
forms!fEnterPatientInfo!selectpatient "


should do.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Looks like I had the gist of the code, just had the wrong syntax. Do you
have any suggestions for where I can find a good book for VBA syntax? I
often find I know what I want and have a rough idea of the code, but end up
spending too much time as a result of working thru sytax errors.

Thanks again.
--
Pat Dools

Hi,

DoCmd.OpenForm "FormStatus", , , "Status = 'New' AND patient=
forms!fEnterPatientInfo!selectpatient "


should do.


Hoping it may help,
Vanderghast, Access MVP

I have a database that has one main table and form that contains 'edit'
records of all records that have data entry mistakes. Each of these
records
has a status ('New', 'Fixed', 'In Process', etc.).

On the Switchboard below, I also have a Combo box that looks up to
another table (different from the 'Status' table that acts as the actual
Record Source for my Switchboard form) to get all the unique patient
records/IDs from the data set so a patient record can be chosen.

From the Switchboard, our user would like to: 1) Click on a command button
that will get a Count of all records that have a certain status (e.g.,
'New',
'Fixed', 'In Process', etc.). 2) Then, they would like to click on
another
command button that would open up the form that contains all Edit Records
with the Status they just got the Count from (in Step #1 above), so they
could scroll thru those records for review. 3) Finally, how I can get
all
records with a status of new AND pertain to the patient selected from my
combo box on the Switchboard form ('fEnterPatientInfo')?

What I need is to be able to add this this code:

DoCmd.OpenForm "FormStatus", , , "Status = 'New' "

I've played with variations of the code below, but
cannot get it to fly:

DoCmd.OpenForm "FormStatus", , , "Status = 'New' " & [patient]=
[forms]![fEnterPatientInfo]![selectpatient]

where [patient] is the Patient ID number in the header of the form
"FormStatus" that I would like to filter on (in addition to the 'Status'
field).

Thank you.

 

[Michel Walsh]

Hi,


This newsgroup is a good "book", as well as Google, or MSDN or the online
Access Knowledge database. It sounds, indeed, you get the idea and the
notions right, just need some fine-tuning tricks, and so, this ng is
probably one of the best tool you can get


Hoping it may help,
Vanderghast, Access MVP

Looks like I had the gist of the code, just had the wrong syntax. Do you
have any suggestions for where I can find a good book for VBA syntax? I
often find I know what I want and have a rough idea of the code, but end
up
spending too much time as a result of working thru sytax errors.

Thanks again.
--
Pat Dools

Hi,

DoCmd.OpenForm "FormStatus", , , "Status = 'New' AND patient=
forms!fEnterPatientInfo!selectpatient "


should do.


Hoping it may help,
Vanderghast, Access MVP

I have a database that has one main table and form that contains 'edit'
records of all records that have data entry mistakes. Each of these
records
has a status ('New', 'Fixed', 'In Process', etc.).

On the Switchboard below, I also have a Combo box that looks up to
another table (different from the 'Status' table that acts as the
actual
Record Source for my Switchboard form) to get all the unique patient
records/IDs from the data set so a patient record can be chosen.

From the Switchboard, our user would like to: 1) Click on a command
button
that will get a Count of all records that have a certain status (e.g.,
'New',
'Fixed', 'In Process', etc.). 2) Then, they would like to click on
another
command button that would open up the form that contains all Edit
Records
with the Status they just got the Count from (in Step #1 above), so
they
could scroll thru those records for review. 3) Finally, how I can get
all
records with a status of new AND pertain to the patient selected from
my
combo box on the Switchboard form ('fEnterPatientInfo')?

What I need is to be able to add this this code:

DoCmd.OpenForm "FormStatus", , , "Status = 'New' "

I've played with variations of the code below, but
cannot get it to fly:

DoCmd.OpenForm "FormStatus", , , "Status = 'New' " & [patient]=
[forms]![fEnterPatientInfo]![selectpatient]

where [patient] is the Patient ID number in the header of the form
"FormStatus" that I would like to filter on (in addition to the
'Status'
field).

Thank you.