Subform recordsource change based on openarg of parent form (1 Viewer)

[Wysy]

Hi,
I have a form frmMain with subform fsubMain. FrmMain is opened with different open arguments. I am trying to set the record source of fsubMain according to. So what i did is setting this up in the OnLoad event of the parent form frmMain like this and apply a filter but it does not work.

Private Sub Form_Load()
If Me.OpenArgs = "DisplayAll" Then
       Me!fsubMainTM.Form.RecordSource = "SELECT ID from tbInvoices;"
       me.fsubMainTM.form.filter="ID=" & intX
       me.fsubMainTM.form.filterOn=true
      
End If

What do i do wrong?
thanks

 

[arnelgp]

it is better you do it in the Load event of your Main Form.
you create a Query for each "source", example:

(qryInvoice_id):
select id from tblInvInvoice;

on your FrmMain Load event:

Private Sub Form_Load()
With Me.fsubMainTM
.SourceObject = "qryInvoice_Id"
With .Form
.Filter = "ID = " & intX
.FilterOn = True
End With
End With
End Sub

 

[Wysy]

that is what i have done onLoad event but it does not work

 

[Wysy]

I have read in a forum that subform loads before parent form. Is it correct?

 

[arnelgp]

Is it correct?

yes, that is why i instruct you to use the FrmMain Load event.

 

[Wysy]

I am not sure if i understand it correctly: put the code in the OnLoad event of the frmMain?
I think i have done it in the beginning. I have put my code there, or do i miss something?

 

[Gasman]

If you are passing in 'DisplayAll', why on earth would you set a filter?

 

[Wysy]

Because for different needs i am to use the same form.

 

[Gasman]

Because for different needs i am to use the same form.

Yes, I understand that, I would probably do the same thing.
However to me 'DisplayAll' actually means that..... display all, so no filter would ever be used, else I would not be displaying all?

 

[Wysy]

DisplayAll means in my setup that all records for given customer regardless the status of the records.

 

[Gasman]

Have you walked through your code to see if is being executed?

 

[Pat Hartman]

but it does not work.

Are we supposed to guess which of the many things this could mean?
Did you get an error?
Did you get unexpected records?
Did you get no records?
?????

 

[Wysy]

it seems to me that the recommended solution was not different with what i tried to use namely to put the code in the onload event of parent form. However the solution i have found was to declare the record source string as tempVars "one form earlier" when the different setup can be selected. This way it works. The point was to use the same forms for tasks based on different data setups.
Thanks for the help

 

[arnelgp]

The point was to use the same forms for tasks based on different data setups

you can only do that if the "tables/queries" you are "changing" has same number of columns.
otherwise if they are "less" than what the "form" has, and the textbox that occupies it has a control source, then
likely you will get error on your subform.

what i suggest is just change the SourceObject of your subform, so it will adjust to whatever number of
columns/fields you have.

 

[Wysy]

it was/is the same table/query with the same number of columns, but different parameters.

 

[MsAccessNL]

Are you sure this is correct --> "SELECT ID from tbInvoices;" you are only selecting the ID field no other fields/records. Your form will only show the ID number ?

 

[Wysy]

sorry for not being completely clear: it was only for testing purposes to provide a short but valid sql statement

 

[Wysy]

thank you for all the help