Form Filter not refreshing

[Kalyan Ganesan]

I have a main form and a subform, the main form has a combo box that will have a list of items patient no, once i choose one of those the sub form gets populated with data, the subform has a recorsource already set during design in the property sheet,but the challenge is i need to use one of the combo boxes in the subform to set a Filter, when i use it as a filter and write this code in the

Me.Filter = "Discount_Code=10454835"
Me.FilterOn = True
Me.Refresh

and it works if its written in a Button or something,but if its in the combo box it throws the unique constraint error as it thinks the data is getting updated as a new patient(discount code) and the patient is a existing one..so how do i by pass this, i should use the combo to choose the discount code and pull up patient details for that discount code but not update the discount code and after i use the combo to do this the subform should be updateable

Thanks

 

[MajP]

You can not use the same control for data entry and for searching. Search controls are unbounded. My search controls are using in the header or footer of the form or subform. So you would likely have a filter combo different from you data entry combo.

 

[Kalyan Ganesan]

Majp,

Thanks,that makes sense that the search combo has to be different,i actually have a bigger problem,the sub form if opened seperately (without navigating from main form) displays all the records,so the Filter works in that,but when the subform(even though its open as its bound in the main form) when it gets populated thru the Combo in the main form doesnt allow me the filter function,it just doesnt do anything when i set the filter property that way..

The design is this

Main form opens(sub form is also shown but no data displayed)
The main form has a combo for Patient numbers,i click on one then the subform displays all the record thru populating all the controls in the subform
So when i use the filter it doesnt do anything

I tried updating the subforms recordset by changing the query for it but thats not how this should be done,i want it done thru Filter so i can go back to main form and use the other values in the search combo in the main form to display the data

Thanks

Kalyan

PS this is lot more complex than it appears

 

[MajP]

Can you provide the code for the filter and any code that is run when you select a patient number from the main? I do not understand

i click on one then the subform displays all the record thru populating all the controls in the subform

Is the subform linked to the combo box on the main form. I would expect the properties to be something like this with the subform linked to the combobox in the main form.
link Master Fields: [ComboPatient]
link child fields: Patient_ID

In general, there should not be a reason you cannot apply a filter to a subform linked to a mainform.

 

[Kalyan Ganesan]

Yes the subform is linked to the main form, there is a property Link master fields, that field is the Patient Number,then there is field called Link Child fields(both these in the property sheet of the subform)which is a base table column that is not present in the subform

So in the subform i tried this code

me.Filter="PatientNumber=121111"
me.FilterOn = True
Me.Requery

Cycle=CurrentRecord

is there any other property that needs to be set?

 

[MajP]

I do not think that filter would do anything.
The form is linked by patient number already. So you cannot filter on that field. You could filter on a different field. Not sure I understand a scenario where you would filter on the same field as the linked field.

 

[Kalyan Ganesan]

Oh ok I understand what I am trying is I have a Discount Code,so when I choose it I want it to send the Patient Number to the main form as I thought you can link only thru one column,so you are saying there is no point in creating a filter on the same column that is already linked correct?

So should I set the filter more like this

me.Filter="DiscountCode=50388"
me.FilterOn = True
Me.Requery

also I created a separate combo box to make sure the updates are not done and the unique constraint error doesn't gets thrown

 

[Kalyan Ganesan]

That filter didnt work either,it refreshes the form but nothing gets populated,i am expecting the record to show just like how i choose the patient number from the main form..why is it not showing that way?

 

[Kalyan Ganesan]

Ok finally i got the filter to work but it still works only if i run the subform by itself

The subform has 500 records and the filter pulls up exactly the record i want to pull

However this is not enough,i want the main form to pull the records when the drop down gives the Patient Numbers and one patient number is chosen

Then in the sub form i want to have the Gold Number as a search combo,will this be possible?if not i have to use it only in the main form, i am not sure how that will work because the main form has only one search combo which is the Patient Number that is linked,master child and all that

 

[MajP]

Yes it should work? There is no reason you cannot have a subform linked to a main form and then filter on a different field on the subform. I would remove the requery from the code. That may cause problems and not needed, although it should not. I like to see an image of this because there may be a better design. If you are always filtering by Patient ID and then Gold Number, you could even link the subform to two combo boxes in the main form. Also are you making new records are just showing existing records? Having a linked subform is really easy and handy when you are creating records, but there may be easier solutions if just using it for a search form.

 

[Kalyan Ganesan]

How about this?In the main form I have added a combo box for Discount Code so if I choose a code it will get the PatientID for that code and

cmbPatient.text=patientid

then it should just pull the records in the subform(details) right?

 

[Kalyan Ganesan]

I am trying something like this


Dim strSQL As String

Dim rsDB As DAO.Recordset

strSQL = "SELECT PATIENT_NUMBER FROM MAIN WHERE DISCOUNT_NUMBER='" & DISCOUNT& "'"

Set rsDB = CurrentDb.OpenRecordset(strSQL)

cmbPati_NUMBER.SetFocus
cmbPati_NUMBER.Value = rsDB.Fields("PATIENT_NUMBER").Value


so this should work right?

 

[Kalyan Ganesan]

I get this error

I am getting the following error: "The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing [dbName] from saving the data in the field."

I am not trying to save a new patient name,just show the patient name thats in the list,so what property should i set for that?

Like they choose a discount code,it pulls the patient number and shows in the patient combo then the subform will pick it up from there

 

[MajP]

If you wanted a main form to be able to filter a subform on two fields: PatientId, DiscountCode, you could put two unbound comboboxes on the main form and link them to the subform. Something like

link Master Fields: [CmboPatient];[CmboDiscount]
Link Child Fields: PatientID, DiscountCode

That will work but may not be what you want. It will only bring up fields that match both. But it would allow you to enter a record for a give patientID and a given discount code.

If instead you just want a search form, where you can enter either Patient ID or Discount Code or both, I would do it differently. Posting an image may help to show how you would like this to work. I do not think anything limits you from doing what you want, you may not have yet designed it correctly.

 

[Kalyan Ganesan]

The first option is exactly what i want

So i tried adding this to in the properties

link Master Fields: [CmboPatient];[CmboDiscount]
Link Child Fields: PatientID, DiscountCode

but it didnt pull anything when i choose values from the combo boxes in the main form,now even the first combo which was working when chosen a value(Patient ID) doesnt work

But this is what i want,no room for search form,all they want is add a second combo(Discount Code) to get the data in the subform..

Sorry i didnt explain this clearly in the beginning,what i thought was the filter would just work for any number of columns...it does only without all these master detail links..

Anyway now i know what i need please help me have two Link Master Fields

Thanks this would definitely make a big difference to my repuatation here

 

[MajP]

Sorry a typo on may part.
You separate the fields or controls with semicolons. The comma was a mistake
link Master Fields: [CmboPatient];[CmboDiscount]
Link Child Fields: PatientID; DiscountCode

That assumes the main form has comboboxes with those exact names, and the values in those comboboxes match the fields values with those names.

 

[Kalyan Ganesan]

I tried like this

link Master Fields: [CmboPatient];[CmboDiscount]
Link Child Fields: PatientID; DiscountCode

Nothing happens, infact now even the search thru patient code using CmboPatient isnt working

There was no specific code in the cmbopatient that pulled the records in the subform, so i am not sure why this isnt working once i add the Discount

 

[Kalyan Ganesan]

Ok i got it,by doing this

link Master Fields: [CmboPatient];[CmboDiscount]
Link Child Fields: PatientID; DiscountCode

I am forced to enter both the PatientID and Discount code,and it pulls the record in the detail section
my goal is to search just by one of them how do i do that or should i have two subforms?

 

[Kalyan Ganesan]

Can the link Master Fields and Link Child Fields property be changed during runtime?

 

[Kalyan Ganesan]

I gave this during run time

Me.pageMain.LinkMasterFields = cmbDiscNumb
Me.pageMain.LinkChildFields = DISC_NUMBER

and it shows a text box to enter data when this code is run in the change event of the combo box

I need the data in the combo box to be used as a master link field