Runtime error while changing Linkedmasterfields and LinkedChildfields through events

[Kalyan Ganesan]

I have this code in the Afterupdate and Afterchange events of a Combobox


If Me![pageMain].LinkChildFields <> "GOLD_NUMBER" Then

Dim strMasFld, strChlFld As String
strMasFld = "cmbGoldNumb"
strChlFld = "GOLD_NUMBER"

Me![pageMain].LinkMasterFields = strMasFld
Me![pageMain].LinkChildFields = strChlFld

End If

I tried doing this without using variables and i got the Runtime error 3071

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables

So i am using variables and i am still getting it,please help

 

[Kalyan Ganesan]

Majp

What i need is one filter at a time

When i click on Combo for Gold then it should filter it on GOLD

When i click on Combo for Patient then its for patients

I modified the code like this

If Not IsNull(Me.cmbLOAN_NUMBER) Then
'strLoanNumbFilter = "[LOAN_NUMBER] = " & Me.cmbLOAN_NUMBER.Text & " AND "
strLoanNumbFilter = "[LOAN_NUMBER] = " & Me.cmbLOAN_NUMBER.Text
FormFilter = strLoanNumbFilter
Me.pageMain.Form.FilterOn = True
End If

If Not IsNull(Me.cmbGoldNumber) Then
strGoldFilter = "[GOLD_NUMBER] = '" & Me.cmbGoldNumber.Text & "'"
FormFilter = strGoldFilter
Me.pageMain.Form.FilterOn = True
End If
It works for patient and not for Gold

 

[MajP]

The demo I provided works for patient, gold, or both.

I am getting confused on how you want this to work, because it keeps seeming like it is changing. No sure if you want to filter one at a time or filter on both. If you only want to search on one filter at a time this should be real easy.

I would think on the after update of cmboLoan you would do. This assumes Loan is numeric and Gold is text.

If Not IsNull(Me.cmbLOAN_NUMBER) Then
   strLoanNumbFilter = "[LOAN_NUMBER] = " & Me.cmbLOAN_NUMBER
   me.pagemain.form.filter = strLoanNumbFilter
   Me.pageMain.Form.FilterOn = True
end if

In the afterUpdate of the cmboGold

If Not IsNull(Me.cmbGoldNumber) Then
  strGoldFilter = "[GOLD_NUMBER] = '" & Me.cmbGoldNumber & "'"
  me.pageMain.form.filter = strGoldFilter
  Me.pageMain.Form.FilterOn = True
End If

 

[Kalyan Ganesan]

Majp

I am not changing the method to make this work

I am very clear ,the Filter doesnt work, i just wanted to give it a try since you gave me some new code ,its the same issue

What i am trying to make it work here is this the last few days

I am trying to change the LinkMasterFields and LinkChildFields
Not set the filter or anything,i tried that didnt work so next approach is this


If Me![pageMain].LinkChildFields <> "GOLD_NUMBER" Then

Dim strMasFld, strChlFld As String
strMasFld = "cmbGoldNumber"
strChlFld = "GOLD_NUMBER"

Me.cmbLOAN_NUMBER = Null

Me![pageMain].LinkMasterFields = ""
Me![pageMain].LinkChildFields = ""
Me![pageMain].LinkMasterFields = strMasFld
Me![pageMain].LinkChildFields = strChlFld

End If


This code above does work

However it will work if i assign nulls like this

Me![pageMain].LinkMasterFields = ""
Me![pageMain].LinkChildFields = ""

what that does is it really slows it down,it resets the subforms and pulls all the rows and if i see in the scroll bar below it will show as 600 rows and then after pulling the rows it filters(not using filter here,i am just referring it as filter) using the materfield which is the goldnumber and pulls that one row for that particular goldnumber

however if its patientnumber i dont need this code

Me![pageMain].LinkMasterFields = ""
Me![pageMain].LinkChildFields = ""

so why it needs that code only for Goldnumber and not patient number is what i have been trying to figure out

and this is what i want to make it work somehow...not changing routes,i just tried the code you guys tried to help me with..thats all



what i dont understand is it will work if i assign nulls like this

Me![pageMain].LinkMasterFields = ""
Me![pageMain].LinkChildFields = ""

and then assign

Me![pageMain].LinkMasterFields ="cmbGoldNumber"
Me![pageMain].LinkChildFields = "GOLD_NUMBER"

 

[Kalyan Ganesan]

I am here by attaching the error in this jpeg

This is the error i get when i run the code like this

Private Sub cmbGoldNumber_AfterUpdate()

' DoEvents
' On Error GoTo Errs

If Me![pageMain].LinkChildFields <> "GOLD_NUMBER" Then

Dim strMasFld, strChlFld As String
strMasFld = "cmbGoldNumber"
strChlFld = "GOLD_NUMBER"

Me.cmbLOAN_NUMBER = Null

'Me![pageMain].LinkMasterFields = ""
'Me![pageMain].LinkChildFields = ""
Me![pageMain].LinkMasterFields = strMasFld
Me![pageMain].LinkChildFields = strChlFld

End If

'Errs:
'MsgBox Err.Description


End Sub

If i take the comments out on

'Me![pageMain].LinkMasterFields = ""
'Me![pageMain].LinkChildFields = ""

it will run fine but it will slow the subform by loading all records and then filtering using the master child

 

[dhookom]

I have spent too much time researching and testing with the setting of the link properties. You stated earlier the changing of the recordsource "Works like a charm" so I can't justify any additional effort in supporting another method.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Kalyan Ganesan]

Dhookom

Thanks a lot for spending a lot of time,i did tell you changing the recordset works fine but not the recordsource plus my manager wants it to work without changing the recordset as the controls in the form are not updating the record and they have a reason for wanting to get it working by Linkmasterfields option

i am not trying to run in all directions and waste time myself,i have to release this for the users to test tomorrow and i dont know how i am going to explain to them why it is so slow and brings all the data and then gets the data for gold number

If either one of you know how to fix the error i showed you on the JPEG image then it would save me face and who knows my job as well

 

[MajP]

I am with Duane, why do you need this with the links. It makes no sense to me. The error you are getting is not avoidable. When you switch the link properties that error will always be thrown unless you first set the link properties first to nothing first. And as you said, this will cause the form to basically unfilter first.

 

[Kalyan Ganesan]

MajP
I have already explained why i cant use other methods

I tried using the recordset property it wont take it as its a subform, same thing with recordsource
So the only other option is your way of setting filters

It just doesnt do anything for the goldnumber when i set the filters

I understand my way will give that error and unfilter first,i understand unfiltering but what i dont understand is why cant it take it as a form like a blank subform while unfiltering

 

[dhookom]

I corrected your code for setting the recordsource of a subform and you suggested it worked. I don't know what problem still exists with either solution provided by MajP and me.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[dhookom]

As far as I can tell a subform control does not have a recordset property. Also, your various snippets of code have used both cmbGoldNumber and cmbGoldNumb. Are these supposed to be the same?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Kalyan Ganesan]

Well i am surprised that you say that,this code that i pasted with recordset works and yes it is for the same combo goldnumber

 

[Kalyan Ganesan]

Dhookom

Where i work we are strict on HIPAA compliance otherwise i would just send the MDB for you, but please try this for yourself

it does work

Dim strSelect As String
Dim rs1 As DAO.Recordset


strSelect = "Use your query"

Set rs1 = CurrentDb.OpenRecordset(strSelect)
Set Me![subform].Recordset = rs1
Set rs1 = Nothing

 

[dhookom]

Kalyan,
I did try this (as per usual) before posting. What version of Access are you using?

When I use code like:

Set Me![sfrmProduction].Recordset = rs1

I get "Object doesn't support this property or method" which is what I would expect.

This does work:

Set Me![sfrmProduction].[highlight #FCE94F]Form.[/highlight]Recordset = rs1

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Kalyan Ganesan]

Dhookom

i pasted the exact code that worked for me,i am using MS Access (Office) 2010 professional version
DAO