Dynamic setting RecordSource not display on the subform (1 Viewer)

[thqnryjt]

Hi,

I'm a new Access VBA programmer and also a new member to this forum, hoping you can help me resolve a similar issue in this thread.

I have a main form (frmAll) that have a combo box where I select a value and I want the subform (frmSummary) to display the recordset that meet the criteria of the value that I selected in the combo box.

The MsgBox shows I have records in my RecordSource but the form doesn't show any records after I select the value from the combox (cmbId).

Please let me know what I'm missing in my codes and thank you so much for your help!

My code is below:

Public Sub cmbId_AfterUpdate()
On Error GoTo Error:

Dim tmpId As String
Dim strSql_Sum As String
Dim lngBlack As Long
lngBlack = RGB(0, 0, 0)
Me.cmbId.ForeColor = lngBlack

tmpId = [Forms]![frmAll]![cmbId].Column(2)

Me.txtId = [Forms]![frmAll]![cmbId].Column(2)
Me.txtLOB = [Forms]![frmAll]![cmbId].Column(1)

If tmpId = "ALL" Then

strSql_Sum = "SELECT tbl_Incentive_summary.* " & _
"FROM tbl_Incentive_summary INNER JOIN tbl_Incentive_product ON tbl_Incentive_summary.Incentive_id = tbl_Incentive_product.Incentive_id " & _
"WHERE (((tbl_Incentive_product.Incentive_sort) Is Not Null)) " & _
"ORDER BY tbl_Incentive_product.Incentive_sort;"


ElseIf tmpId = "COMM" Or tmpId = "MCAL" Or tmpId = "MDCR" Then

strSql_Sum = "SELECT tbl_Incentive_summary.* " & _
"FROM tbl_Incentive_summary INNER JOIN tbl_Incentive_product ON tbl_Incentive_summary.Incentive_id = tbl_Incentive_product.Incentive_id " & _
"WHERE (((tbl_Incentive_product.Incentive_sort) Is Not Null) AND ((tbl_Incentive_product.Incentive_LOB)= '" & tmpId & "')) " & _
"ORDER BY tbl_Incentive_product.Incentive_sort;"


Else
strSql_Sum = "SELECT * FROM tbl_Incentive_summary " & _
"WHERE ((Incentive_id)= '" & tmpId & "');"

End If

Forms!frmAll!frmSummary.Form.RecordSource = strSql_Sum

Me.frmSummary.Requery

Me.Requery
Me.Refresh

MsgBox "This form gets its records from " & Forms!frmAll!frmSummary.Form.RecordSource & "."

Exit_cmbId_AfterUpdate:

Exit Sub
Error:

MsgBox Err.Number & " - " & Err.Description, vbCritical, "Error!"

Resume Exit_cmbId_AfterUpdate
End Sub

 

[isladogs]

Welcome to the forum.
For info I removed your duplicate post in a 10 year old thread!

Signing off now as its late here in the UK.
However, I'm sure someone will be along soon to answer your question

 

[thqnryjt]

still pulling my hair out of this, is there someone available to help?

 

[Gasman]

How can a MsgBox show you have records ? all that is showing is the sql string, surely?

However I can see that you are using quotes for tmpID, which I would have thought would be numeric.

I'd be in the Debug window and putting breakpoints after SQL string has been built, and then use Debug.Print to get the string to copy and paste to a query SQL window, and see what that produces.?

Use Debug window to see you are producing what you *think* you are producing.
Also when you set the recordsource, there is no need for a requery, that happens as a consequence of the change of recordsource.

HTH

 

[thqnryjt]

cmbId is a text not number

ignore my MsgBox code, I was trying to say that my strSql_sum is correct it produce records but my subform frmSummary does not show any records :banghead::banghead:

 

[Gasman]

All I can think of is that the subformcontrol is not called the same as the subform name?

 

[thqnryjt]

what does that mean?

 

[Gasman]

You reference the control that holds the subform.

Most times it is the same, especially if you drag a form to another form, but can be different. I have started to use sub prefix for my controls like these.

Hence the

Forms!frmAll!frmSummary.Form.RecordSource = strSql_Sum

where frmSummary is the control and its Form property.

Code looks OK to me with my limited knowledge.
You say the SQL is correct and produces records when used standalone

Zip the DB and upload it for someone to take a look at. I'll even try and see what it could be.

Also try

Me.frmSummary.Form.RecordSource = strSql_Sum

 

[thqnryjt]

Yes it's the same.

I've attached the mdb.

Once open, select a value in the combo box on top, any value should have some records, but when you go to the Summary form, it doesn't display anything.

Thanks for your help.

 

[Gasman]

I have had a quick look, and cannot see why yet. I thought it might due to link fields.?

Whilst the code appears to run, I cannot compile the database?, too many missing references.?

We need an expert on this.

Yes it's the same.

I've attached the mdb.

Once open, select a value in the combo box on top, any value should have some records, but when you go to the Summary form, it doesn't display anything.

Thanks for your help.

 

[thqnryjt]

there's no link fields

I'm still pulling my head on this, two full days already :-(

Experts please help me ...

 

[thqnryjt]

Here's a cleaner mdb that I've deleted old codes and compiled.

 

[Gasman]

:banghead:Ok, I've tracked it down to something linked to the pages/tab control?

I created a form placed your 3 controls on it and the subform frmSummary, and it works as expected.
Still none the wiser though.

 

[Gasman]

It could be that the form is corrupt?
I've just added a tab control and two pages. Put the frmSummary on the first page. Still works.

See attached.
Due to your code and naming conventions, you will need to rename frmAll to something else, and then MyfrmAll to frmAll to see it working.

HTH

 

[essaytee]

Check out the setting, "Filter On Empty Master", yours is currently set to Yes, change it to No, then the records are displayed.

 

[Gasman]

essaytee,
Yes it does, but I copied the subform control and my version is also set to Yes, but will display the records. Now even more confused.

 

[essaytee]

essaytee,
Yes it does, but I copied the subform control and my version is also set to Yes, but will display the records. Now even more confused.

Believe me, I was doing what you were probably doing, setting debug.print all over the place, pasting in the sql directly into a new query (and it worked). I was also pulling my hair out. I came across that setting, changed it, and records appeared. I didn't test any further. Without going to the docs I'm not fully aware of what that setting is to achieve.

 

[Gasman]

Believe me, I was doing what you were probably doing, setting debug.print all over the place, pasting in the sql directly into a new query (and it worked). I was also pulling my hair out. I came across that setting, changed it, and records appeared. I didn't test any further. Without going to the docs I'm not fully aware of what that setting is to achieve.

Well it saves the O/P creating the form all over again. Nice one.

 

[isladogs]

Well you got there quicker than me ....:banghead:
I've removed all the unnecessary requery & refresh lines - all they are doing is causing lots of flickering.
Also removed the double filtering of frmSummary.
Much more responsive now as well

 

[essaytee]

I've only looked at this parent/subform form from the point of view of the 'summary' subform. Haven't looked at the other subforms.

I'm curious as to why in the main form there needs to be a recordsource. It might as well be a straight out unbound main form controlling the subforms. I noticed that the Data Only property was set and I can see why, so as not to show existing records. Unless there is something else going on, remove the recordsource from the main form.