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

 

[dhookom]

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:

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

 

[Kalyan Ganesan]

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

 

[dhookom]

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

 

[Kalyan Ganesan]

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?

 

[dhookom]

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

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




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

 

[Kalyan Ganesan]

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 =

 

[dhookom]

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

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

 

[Kalyan Ganesan]

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

 

[dhookom]

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

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

 

[Kalyan Ganesan]

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

 

[dhookom]

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

 

[Kalyan Ganesan]

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?

 

[dhookom]

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

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

Try this:

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

You should not need a recordset or requery.

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

 

[Kalyan Ganesan]

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

 

[dhookom]

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

 

[MajP]

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.

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

 

[MajP]

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

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

 

[Kalyan Ganesan]

Thanks a lot you guys MajP and Dhookom

Let me try these and let you know

 

[Kalyan Ganesan]

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