Microsoft Access
Microsoft Access

clear subform

Access 2010:
I have a subform: but it is not master child linked.

This is the name:
dbo_t_redbook_pricing_escalation_detail_subform

This is the "source Object" :
dbo_t_redbook_pricing_escalation_detail_subform

I'm trying to clear the subform out...
But it just appears white and blank..

by using.
Dim temp As String
temp = ""
temp = dbo_t_redbook_pricing_escalation_detail_subform.SourceObject
dbo_t_redbook_pricing_escalation_detail_subform.SourceObject = temp

me.dbo_t_redbook_pricing_escalation_detail_subform.requery

I want to clear it as if i just opened the database. form

trying to reset the recordsource via sql but saying it is to complex ?
Dim strSQL As String
strSQL = "SELECT TOP 5 dbo_t_redbook_pricing_escalation_detail.[AUTO_ID], dbo_t_redbook_pricing_escalation_detail.[RBP_MASTER_ID], dbo_t_redbook_pricing_escalation_detail.[SKU], dbo_t_redbook_pricing_escalation_detail.[PRODUCT_DESCRIPTION], dbo_t_redbook_pricing_escalation_detail.[QTY], dbo_t_redbook_pricing_escalation_detail.[TARGET_PRICE], dbo_t_redbook_pricing_escalation_detail.[COMPETITOR_PRICE], dbo_t_redbook_pricing_escalation_detail.[TARGET_GP], dbo_t_redbook_pricing_escalation_detail.[CURRENT_PRICE], dbo_t_redbook_pricing_escalation_detail.[CURRENT_GP], dbo_t_redbook_pricing_escalation_detail.[VENDOR_GUIDELINE_GP], dbo_t_redbook_pricing_escalation_detail.[APPROVED_PRICE], dbo_t_redbook_pricing_escalation_detail.[APPROVED_GP] From dbo_t_redbook_pricing_escalation_detail WHERE dbo_t_redbook_pricing_escalation_detail.RBP_MASTER_ID= [Forms]![Main]![PRICING_ESCALATION_ID]"
Me.dbo_t_redbook_pricing_escalation_detail_subform.Form.RecordSource = strSQL

Thanks
fordraiders

LVL 3

FordraidersAsked: 2016-09-27

Who is Participating?

LVL 120

Rey Obrero (Capricorn1)Commented: 2016-09-27

try this

Dim strSQL As String
strSQL = "SELECT TOP 5 dbo_t_redbook_pricing_escalation_detail.[AUTO_ID], dbo_t_redbook_pricing_escalation_detail.[RBP_MASTER_ID], dbo_t_redbook_pricing_escalation_detail.[SKU], dbo_t_redbook_pricing_escalation_detail.[PRODUCT_DESCRIPTION], dbo_t_redbook_pricing_escalation_detail.[QTY], dbo_t_redbook_pricing_escalation_detail.[TARGET_PRICE], dbo_t_redbook_pricing_escalation_detail.[COMPETITOR_PRICE], dbo_t_redbook_pricing_escalation_detail.[TARGET_GP], dbo_t_redbook_pricing_escalation_detail.[CURRENT_PRICE], dbo_t_redbook_pricing_escalation_detail.[CURRENT_GP], dbo_t_redbook_pricing_escalation_detail.[VENDOR_GUIDELINE_GP], dbo_t_redbook_pricing_escalation_detail.[APPROVED_PRICE], dbo_t_redbook_pricing_escalation_detail.[APPROVED_GP] From dbo_t_redbook_pricing_escalation_detail WHERE dbo_t_redbook_pricing_escalation_detail.RBP_MASTER_ID= " & [Forms]![Main]![PRICING_ESCALATION_ID]
Me.dbo_t_redbook_pricing_escalation_detail_subform.Form.RecordSource = strSQL

also, take note that the subform loads first before the main form, so place the codes in the Load event of the main form or in the
current event if it deems necessary.

why not use master/child link fields?

LVL 24

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented: 2016-10-03

>"But it just appears white and blank.."

is AllowAdditions set to True? If not, and there is not a record, the form will be as you describe.

also, Rey suggested setting LinkMasterFields and LinkChildFields for the subform control (DATA tab of Properties) ... let Access do the hard work ;) ~ Just make sure that what you reference is ON each form (it can be Visible=No). LinkMasterFields references a control and it can even be calculated. LinkChildFields must be a field in the RecordSource for the subform. If you have more than one to list, separate with ; (semi-colon)