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

Runtime error while changing Linkedmasterfields and LinkedChildfields through events

Runtime error while changing Linkedmasterfields and LinkedChildfields through events

Runtime error while changing Linkedmasterfields and LinkedChildfields through events

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

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

Apparently pageMain is the name of your subform control. 99% of the time the link master/child properties contain either a control or field name. It looks like you are attempting to put string values into the properties. If this is really what you desire, you must add the string delimiters like:

CODE --> vba

Me![pageMain].LinkMasterFields = """" & strMasFld & """" 


I assume you have a good reason for not using a control or field name in the properties.

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

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

(OP)
dhookom thanks for your posting, i tried what you posted,it didnt work..this is a very weird problem, i tried to trick it by giving this


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



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


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

THis works but what happens is it loads all the records(Over 500) in the subform and then picks the one record so this isnt acceptable

I really need to get this done by today ,been holding this piece for a long time with one issue or another

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

Can you confirm the name of your subform control is pageMain?

Does your code compile?

Have you set Option Explicit in the General Declarations section of your code?

Do you know how to set a breakpoint and step through your code one line at a time to see what is happening?

Are cmbGoldNumb and GOLD_NUMBER names of controls or fields in the main form and subform?

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

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

(OP)
Yes option explicit is set

Let me explain what all i tried so far

tried the below, that is without declaring variables



Me![pageMain].LinkMasterFields ="cmbGoldNumber"
Me![pageMain].LinkChildFields = "GOLD_NUMBER"
and i got the error

cmbGoldNumber is the Combobox name
GOLD_NUMBER is the column name that is the childfield

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"

but the problem is it loads all the records(Over 500) in the subform and then picks the one record so this isnt acceptable

in the same main form i have another combo and when i set similar code in the change event of that combo it works like a charm,but that Childfield is numeric, whereas this childfield is varchar

do you think that could cause this issue?

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

First "" is not Null but a zero-length-string (common confusion).

CODE --> vba

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

Rather than mess with link master/child, I will often build a SQL statement in code and set the subform control's form.recordsource to the SQL statement.

You didn't mention anything about setting a breakpoint. Check FAQ705-7148: How to debug your code


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

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

(OP)
Nope still not working, yes I do have breakpoints set in the changeevent and afterupdate events of this combo box..what I still don't understand is how come this approach works for the other combo box,

what happens now is it pops up a textbox to enter value for the cmbGoldNumber combo

also its very frustrating that this below code resets the master and child fields but doesn't stop from all the rows being loaded in the subform

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

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

Do you have a control on your main form named "cmbGoldNumber"?

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

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

(OP)
Of course i have a control called cmbGoldNumber,like i said it works when i first set it to

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

and then assign

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

but the problem is it loads all the rows first in the subform then shows the one row,and its very slow when it happens


RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

I'm not sure why this doesn't work but I would set the RecordSource like:

CODE --> vba

Private Sub cmbGoldNumb_AfterUpdate()
    Dim strMasFld As String, strChlFld As String
    
    If Me![pageMain].LinkChildFields <> "GOLD_NUMBER" Then
        strMasFld = "cmbGoldNumb"
        strChlFld = "GOLD_NUMBER"
        Me.pageMain.Form.RecordSource = "SELECT * FROM qselTT WHERE GOLD_NUMBER ='" & Me.cmbGoldNumb & "'"
        
        Me![pageMain].LinkMasterFields = ""
        Me![pageMain].LinkChildFields = ""
    End If

End Sub 

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

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

(OP)
No changing the recordsource is not a option,because i have to swap between the patientnumber and goldnumber(both combo boxes) as the Master,Child Fields

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

You can use vba to change the record source to whatever you need. This avoids any issues with the link master/child updates.

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

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

(OP)
Recorsource or Recordset change Doesnt work because its not the current form,its the subform so setting them at the runtime for subforms dont work

here is the code i tried

Dim strSelect As String
Dim rs1 As Recordset

Me.cmbLOAN_NUMBER = Null

strSelect = "SELECT * from the patient WHERE GOLD_NUMBER='" & cmbGoldNumber & "';"

Set rs1 = CurrentDb.OpenRecordset(strSelect)

Set Me![pageMain].RecordSource = rs1
Me![pageMain].Requery



Set rs1 = Nothing

tried this too

Set Me.pageMain.RecordSource = rs1
Me.pageMain.Requery

It really sux that the

Me![pageMain].FilterOnEmptyMaster = True wont work or else i wouldve had this finished..

Is there a way to set the recordset or recordsource of a subform from the main form?

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

Please use TGML to make your code more readable. My code works with:

CODE --> vba

Me.pageMain.Form.RecordSource = "SELECT * FROM qselTT WHERE GOLD_NUMBER ='" & Me.cmbGoldNumb & "'" 

Try this:

CODE --> vba

'Update the record source of the form/source object
    Dim strSelect As String
    Me.cmbLOAN_NUMBER = Null
    strSelect = "SELECT * from the patient WHERE GOLD_NUMBER='" & Me.cmbGoldNumber & "';"
    Me![pageMain].Form.RecordSource = strSelect 

You should not need a recordset or requery.

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

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

(OP)
Ok great


This is the code i have

Dim strSelect As String
Dim rs1 As Recordset

Me.cmbLOAN_NUMBER = Null

If cmbGoldNumber <> "" Then
strSelect = "SELECT * FROM PATIENT_MAIN WHERE GOLD_NUMBER='" & cmbGoldNumber.Text & "';"

Set rs1 = CurrentDb.OpenRecordset(strSelect)

If rs1.RecordCount > 0 Then
Set Me![pageMain].Form.RecordSource = strSelect
Me![pageMain].Form.Requery
Else
MsgBox " There are no Loan Details for this Gold Number"
End If
Me![pageMain].Requery
Me![pageMain].Form.record
Set rs1 = Nothing

End If

Works like a charm

So you are saying i dont need to use the recordset,that is created a record set like Set rs1 = CurrentDb.OpenRecordset(strSelect) but instead use a recordsource property ?

Also i have one issue, i am not able to do any update in the Detail subform,i need to be able to update the subform and that goes into the base table

Thanks

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

Again, please try use TGML with the Code tag (looks like a scroll).

You don't need to create any recordset.

If PATIENT_MAIN is a table and is the recordsource of your subform, it should allow edits and additions as long as the properties allow them.

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

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

Here is how you can simply filter a sub form based on two combos on the main form. You can pick from one or both or none.

CODE

Private Sub cmboGold_AfterUpdate()
  FilterSub
End Sub

Private Sub cmboPatient_AfterUpdate()
  FilterSub
End Sub

Public Sub FilterSub()
  Dim strPatientFilter As String
  Dim strGoldFilter As String
  Dim FormFilter As String
  If Not IsNull(Me.cmboPatient) Then
    strPatientFilter = "[PatientID] = " & Me.cmboPatient & " AND "
  End If
  If Not IsNull(Me.cmboGold) Then
    strGoldFilter = "[GoldNumber] = '" & Me.cmboGold & "'"
  End If
  FormFilter = strPatientFilter & strGoldFilter
  Debug.Print FormFilter
  If Right(FormFilter, 4) = "AND " Then FormFilter = Left(FormFilter, Len(FormFilter) - 4)
  Debug.Print FormFilter
  Me.subFrmDemo.Form.Filter = FormFilter
  Me.subFrmDemo.Form.FilterOn = True
End Sub 

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

What is nice about the above approach you can have as many different comboboxes. You just keep repeating the process in the code. This example has 4 combos and allows you to choose if you want an "AND" or and "OR" criteria

CODE

Public Function getFilter() As String
  On Error GoTo errLable
  Dim strType As String
  Dim strLocation As String
  Dim strSeries As String
  Dim strStatus As String
  Dim strIPT As String
  Dim andOR As String
  Dim removeEnd As Integer
  
     If Me.framAndOr.Value = 1 Then
      andOR = " OR "
      removeEnd = 4
    Else
      andOR = " AND "
      removeEnd = 5
    End If
    
    If Not Trim(Me.cboIPT & " ") = "" Then
        strIPT = "[Responsble_Party] Like '*" & Me.cboIPT & "*'" & andOR
    End If
      
    If Not Trim(Me.cboLocation & " ") = "" Then
        strLocation = "[Location] = '" & Me.cboLocation & "'" & andOR
        'Debug.Print "location " & strLocation & vbCrLf
    End If
    
    If Not Trim(Me.cboSeries & " ") = "" Then
        strSeries = "[TIR_Link] = " & Me.cboSeries & andOR
       ' Debug.Print "series " & strSeries
    End If
    
    If Not Trim(Me.cboStatus & " ") = "" Then
        strStatus = "[status] = '" & Me.cboStatus & "'" & andOR
    End If
    
    getFilter = strIPT + strLocation + strStatus + strSeries
    getFilter = Left(getFilter, Len(getFilter) - removeEnd)


   'You may comment this out
  Debug.Print "Filter Criteria: " & getFilter
  Exit Function
errLable:
  MsgBox Err.Number & "  " & Err.Description
End Function 

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

(OP)
Thanks a lot you guys MajP and Dhookom

Let me try these and let you know

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

(OP)
Majp

I tried your code,it works fine for PatientNumber combo but nothing happens when i try it on GoldNumber combo

What change should i make in the Form,i mean in the property sheets and all that

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

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

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

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.

CODE -->

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

CODE -->

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

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

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


RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

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

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

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

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

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

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

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.

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

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

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

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

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

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

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

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

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

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

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

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

When I use code like:

CODE --> vba

Set Me![sfrmProduction].Recordset = rs1 

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

This does work:

CODE --> vba

Set Me![sfrmProduction].Form.Recordset = rs1 

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

RE: Runtime error while changing Linkedmasterfields and LinkedChildfields through events

(OP)
Dhookom

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

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