INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Form Filter not refreshing

Form Filter not refreshing

(OP)
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

RE: Form Filter not refreshing

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.

RE: Form Filter not refreshing

(OP)
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

RE: Form Filter not refreshing

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

Quote:

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.

RE: Form Filter not refreshing

(OP)
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?

RE: Form Filter not refreshing

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.

RE: Form Filter not refreshing

(OP)
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

RE: Form Filter not refreshing

(OP)
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?

RE: Form Filter not refreshing

(OP)
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

RE: Form Filter not refreshing

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.

RE: Form Filter not refreshing

(OP)
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?

RE: Form Filter not refreshing

(OP)
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?

RE: Form Filter not refreshing

(OP)
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

RE: Form Filter not refreshing

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.

RE: Form Filter not refreshing

(OP)
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

RE: Form Filter not refreshing

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.

RE: Form Filter not refreshing

(OP)
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

RE: Form Filter not refreshing

(OP)
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?

RE: Form Filter not refreshing

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

RE: Form Filter not refreshing

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close