query works but not with command btn

[Waubain]

I have a query: qryNoSurgery with linked tables tblSurgery(many) to tblPatients(one) where tblSurgery!SurgeryDate criteria = Is Null. When I run the query by itself, it works perfectly. I get all the tblPatients data where SurgeryDate field is null.



I created a command button that opens a form, frmPatients, If I enter qryNoSurgery as the filter argument in the embedded macro and run it, I get a Msgbox which wants me to enter a parameter for qryNoSurgery!SugergyDate.

I am enough of a beginner that I don't understand why it works in one and not the other, thus I am having trouble fixing it.

Thank you in advance for any help.

[Toyman]

I have a query: qryNoSurgery with linked tables tblSurgery(many) to tblPatients(one) where tblSurgery!SurgeryDate criteria = Is Null. When I run the query by itself, it works perfectly. I get all the tblPatients data where SurgeryDate field is null.

I created a command button that opens a form, frmPatients, If I enter qryNoSurgery as the filter argument in the embedded macro and run it, I get a Msgbox which wants me to enter a parameter for qryNoSurgery!SugergyDate.

I am enough of a beginner that I don't understand why it works in one and not the other, thus I am having trouble fixing it.

Thank you in advance for any help.

Don't up the query in the filter property. Instead, set it to the recordsource

[Waubain]

Don't up the query in the filter property. Instead, set it to the recordsource

The recordsource for frmPatients is tblPatients. I wanted qryNoSurgery only to act as a filter and not as the record set. I am unclear how to carry out "Instead, set it to the recordsource".

There is a continuous subform within frmPatients that links to tblSurgery. This is the first time I have ever tried to open the parent form and filter based on a field(SurgeryDate) in the sub (child)form.

Thank you.

[Toyman]

The recordsource for frmPatients is tblPatients. I wanted qryNoSurgery only to act as a filter and not as the record set. I am unclear how to carry out "Instead, set it to the recordsource".

There is a continuous subform within frmPatients that links to tblSurgery. This is the first time I have ever tried to open the parent form and filter based on a field(SurgeryDate) in the sub (child)form.

Thank you.

A filter is a criteria: [Name] = 'Charlie'. You cannot use a query in its place. What are the criterias you need to filter the form? What are you trying to do

[Waubain]

A filter is a criteria: [Name] = 'Charlie'. You cannot use a query in its place. What are the criterias you need to filter the form? What are you trying to do

OpenForm frmPatients showing all records where Subform [SurgeryDate] = Is Null

[Toyman]

OpenForm frmPatients showing all records where Subform [SurgeryDate] = Is Null

Let me see if I got this correct. You want to be able to open your main form, "frmPatients". In the forms, "frmPatients", you want to use the filter property of the form to filter out all records where the subform contained in the form, "frmPatients" contains null value? If this is correct, then the filter property is not where you do this. Please give a full description of what you are trying to achieve so that we can give you some suggestion on how to achieve it.

[Waubain]

Let me see if I got this correct. You want to be able to open your main form, "frmPatients". In the forms, "frmPatients", you want to use the filter property of the form to filter out all records where the subform contained in the form, "frmPatients" contains null value? If this is correct, then the filter property is not where you do this. Please give a full description of what you are trying to achieve so that we can give you some suggestion on how to achieve it.

You asked for the full description so you need some background. I am the hospital research pharmacist, so I have built a few Access db's to help me with my daily work. No real applications and a little VBA as required, with the help of Access.Forums.net. I was asked to by another department if I could convert their "MS Word database (exact words for their table) to the other MS thing because mine looked different than theirs and did more things". We have no IT help in this area. I created menus so there daily tasks are pretty much point and click and fill in the blanks. Patients are scheduled for surgery and entered into the main form. After they have had surgery (days to months later) the surgery date field has to be populated. One patient can have multiple surgeries so want to keep the data normalized (Sorry for the lengthy description).

There are 2 tables:
tblPatient
tblSurgery

Relationship: tblPatient(one) to tblSurgery(many) linked by HospitalID

There are 2 forms:
frmPatient (this is the main form)
fsubSurgery (this is the subform within frmPatients listing all the surgeries by date)

I want to create a command button on the Main Menu that opens frmPatient and only shows those records in frmPatient where the embeded subform sfrmSurgery field SurgeryDate = Is Null

Thank you for your continued efforts.

[Toyman]

You asked for the full description so you need some background. I am the hospital research pharmacist, so I have built a few Access db's to help me with my daily work. No real applications and a little VBA as required, with the help of Access.Forums.net. I was asked to by another department if I could convert their "MS Word database (exact words for their table) to the other MS thing because mine looked different than theirs and did more things". We have no IT help in this area. I created menus so there daily tasks are pretty much point and click and fill in the blanks. Patients are scheduled for surgery and entered into the main form. After they have had surgery (days to months later) the surgery date field has to be populated. One patient can have multiple surgeries so want to keep the data normalized (Sorry for the lengthy description).

There are 2 tables:
tblPatient
tblSurgery

Relationship: tblPatient(one) to tblSurgery(many) linked by HospitalID

There are 2 forms:
frmPatient (this is the main form)
fsubSurgery (this is the subform within frmPatients listing all the surgeries by date)

I want to create a command button on the Main Menu that opens frmPatient and only shows those records in frmPatient where the embeded subform sfrmSurgery field SurgeryDate = Is Null

Thank you for your continued efforts.

The way I would go about this is to create a sql statement with both of your tables, "tblPatients" and "tblSurgury". In it, create a left join on the patientID, assumming the primary key and foreign key relationship is the patientID. Set the criteria for the foreign key to null. This query will pull all records from "tblPatient" that don't have a related record (no surgury record) in "tblSurgury". Set this sql statement to your main form and requery. I would use an option group to run the code. Set your option group with two options, "All Records" and "Rec w/No Surguries". In the after update event of the option group, put the following code.

Dim Mysql as string

Select case Ms.YourOptionGroup
Case 1 'All Records
Mysql = "SELECT * From tblPatient"
Case 2 'Rec w/No Surguries
Mysql = "SELECT tblPatient.*
FROM tblPatient LEFT JOIN tblSurgury ON tblPatient.PatientID = tblSurgury.SuPatientID;
End Select

Me.YourFormName.RecordSource=Mysql

[Waubain]

Thanks Toyman for your help.

The query I built in the past worked so I am assuming the SQL statement Access built would be sufficient. It was a left join....

Unfortunately I was just given a different project that has a higher priority. The I will give this a try when I can. Thank you again.