Count and Record filtering based on 'Status' field

[Guest]

Hello,

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.).

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.

What would be the code to achieve #1 and #2 above?

 

[Marshall Barton]

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.).

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.

What would be the code to achieve #1 and #2 above?

#1 Use DCount to get the number of records:

Me.sometextbox = DCount("*", "thetable", "Status = 'New' ")

#2 Use the OpenForm method's WhereCondition argument to
filter the records as the form is opened:

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

 

[Guest]

Hi Marshall,

Thank you for the snippets of code below and they are doing exactly as I
need. 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 Swithboard form) to get all the unique patient
records/IDs from the data set so a patient record can be chosen. What I need
is to be able to add this this code:

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

such that 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'). 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.

 

[Marshall Barton]

Thank you for the snippets of code below and they are doing exactly as I
need. 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 Swithboard form) to get all the unique patient
records/IDs from the data set so a patient record can be chosen. What I need
is to be able to add this this code:

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

such that 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'). 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).

Sorry to leave you hanging for so long, but I've been out of
town for two weeks.

The syntax for a compound condition would be:

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