Data not showing up in Subform

A

Ayo

I have amain form with two subforms embeded into two TabCtl Pages. I have 3
Tables: tblSiteList, tblMaterialsDatabase and tblMilestones. They are the
following fields in common so I used them as the Link Fields, Master & Child.
tblSiteList:Site Id, Market, NLP
tblMaterialsDatabase:Site ID, Market, NLP
tblMilestones:Candidate Id,Market Name,UMTS NLP Status

tblSiteList has a One-to-Many relationship with the other two tables.
Currently I only have one form with tblSiteList as the Record Source. In the
form there is a TabCtl with 2 Pages named Materials and Milestones
respectively.
In each page there is a subform "tblMilestones subform" on Milestones and
"tblMaterialsDatabase subform" on Materials. The goal is to have the subform
display data based on Site Id, Market, NLP fields on the mainform. Also the
Materials tab also have a combobox called cmbShoppingCart which is used in
conjunction withthe Site Id, Market, NLP fields to narrow down the result in
the tblMilestones subform." But for some reason I am not getting anything in
my subforms and I can't figure it out.
I need help. I have been struggling with this now for about 4 days with no
results. Please help.

Private Sub psBuildComboSQL()
Dim strSQL As String
strSQL = "SELECT DISTINCT [Shopping Cart] " & _
"FROM tblMaterialsDatabase " & _
"WHERE ([Site ID] = """ & Me.cmbSiteId & """) AND " & _
"([Market] = """ & Me.Market & """) AND " & _
"([NLP] = """ & Me.NLP & """) " & _
"ORDER BY [Shopping Cart]"
Me.cmbShoppingCart.RowSource = strSQL
Me.cmbShoppingCart = Null
Me.cmbShoppingCart.Requery
End Sub

Private Sub psBuildMilestoneSubformSQL()
Dim strSubformSQL As String, siteID As String
strSubformSQL = "SELECT * " & _
"FROM tblMilestones " & _
"WHERE ([Candidate Id] = "" & Me.cmbSiteId & "") AND " & _
"([Market Name] = "" & Me.Market & "") AND " & _
"([UMTS NLP Status] = "" & Me.NLP & "")"
Me.tblMilestones_subform.Form.RecordSource = strSubformSQL
End Sub

Private Sub psBuildSubformSQL()
Dim strSubformSQL As String
strSubformSQL = "SELECT * " & _
"FROM tblMaterialsDatabase " & _
"WHERE ([Site ID] = """ & Me.cmbSiteId & """) AND " & _
"([Market] = """ & Me.Market & """) AND " & _
"([NLP] = """ & Me.NLP & """) AND " & _
"([Shopping Cart] = """ & Me.cmbShoppingCart & """)"
Me.tblMaterialsDatabase_subform.Form.RecordSource = strSubformSQL
End Sub
 
J

Jack Leach

A few general thoughts...

Pretend the tab controls don't exist. They have absolutely no bearing on
how you will be working with any controls on their pages (unless of course
you are calling an event procedure from the change in page or tab gotfocus,
etc.). For all programming purposes any types of controls on tab pages are
referred to just as if they were not on a tab. If in fact you do run an
event procedure off a tab page click, that event related to the tab is for an
execution point only.

That said, not worrying about what tab page the subforms are on might make
the situation a bit easier to digest with less to wade through.



Verify all of your sql statements. Run the procedures in debug mode,
debug.print the sql to the immediate window after it's values are filled, and
copy&paste it to an sql view in the query designer to see if it's pulling up
data (I have a custom function that will attempt to open and return a
recordcount of a given select query, but the sql in the designer will let you
view the data as well). This is often a culprit in 'subform has no data'
issues, and often gets skipped in design process (access doesn't check this
for you).


and just to verify:
Me.tblMaterialsDatabase_subform.Form.RecordSource = strSubformSQL

tblMaterialsDatabase_Subform is the name of the Control, and not the Form,
right?


hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



Ayo said:
I have amain form with two subforms embeded into two TabCtl Pages. I have 3
Tables: tblSiteList, tblMaterialsDatabase and tblMilestones. They are the
following fields in common so I used them as the Link Fields, Master & Child.
tblSiteList:Site Id, Market, NLP
tblMaterialsDatabase:Site ID, Market, NLP
tblMilestones:Candidate Id,Market Name,UMTS NLP Status

tblSiteList has a One-to-Many relationship with the other two tables.
Currently I only have one form with tblSiteList as the Record Source. In the
form there is a TabCtl with 2 Pages named Materials and Milestones
respectively.
In each page there is a subform "tblMilestones subform" on Milestones and
"tblMaterialsDatabase subform" on Materials. The goal is to have the subform
display data based on Site Id, Market, NLP fields on the mainform. Also the
Materials tab also have a combobox called cmbShoppingCart which is used in
conjunction withthe Site Id, Market, NLP fields to narrow down the result in
the tblMilestones subform." But for some reason I am not getting anything in
my subforms and I can't figure it out.
I need help. I have been struggling with this now for about 4 days with no
results. Please help.

Private Sub psBuildComboSQL()
Dim strSQL As String
strSQL = "SELECT DISTINCT [Shopping Cart] " & _
"FROM tblMaterialsDatabase " & _
"WHERE ([Site ID] = """ & Me.cmbSiteId & """) AND " & _
"([Market] = """ & Me.Market & """) AND " & _
"([NLP] = """ & Me.NLP & """) " & _
"ORDER BY [Shopping Cart]"
Me.cmbShoppingCart.RowSource = strSQL
Me.cmbShoppingCart = Null
Me.cmbShoppingCart.Requery
End Sub

Private Sub psBuildMilestoneSubformSQL()
Dim strSubformSQL As String, siteID As String
strSubformSQL = "SELECT * " & _
"FROM tblMilestones " & _
"WHERE ([Candidate Id] = "" & Me.cmbSiteId & "") AND " & _
"([Market Name] = "" & Me.Market & "") AND " & _
"([UMTS NLP Status] = "" & Me.NLP & "")"
Me.tblMilestones_subform.Form.RecordSource = strSubformSQL
End Sub

Private Sub psBuildSubformSQL()
Dim strSubformSQL As String
strSubformSQL = "SELECT * " & _
"FROM tblMaterialsDatabase " & _
"WHERE ([Site ID] = """ & Me.cmbSiteId & """) AND " & _
"([Market] = """ & Me.Market & """) AND " & _
"([NLP] = """ & Me.NLP & """) AND " & _
"([Shopping Cart] = """ & Me.cmbShoppingCart & """)"
Me.tblMaterialsDatabase_subform.Form.RecordSource = strSubformSQL
End Sub
Click to expand...
 
A

Ayo

tblMaterialsDatabase_Subform is the name of the SubForm. The funny thing is
that the strSubformSQL works only for the first item in the main form
combobox, the cmbSiteId and it's corresponding values in the cmbShoppingCart.
It just doesn't work if I change the value in the cmbSiteId control.
I will try your suggestions and let you know how things turns up. This is
really frustrating now. Its been five days now and I have been stuck in the
same place.

Jack Leach said:
A few general thoughts...

Pretend the tab controls don't exist. They have absolutely no bearing on
how you will be working with any controls on their pages (unless of course
you are calling an event procedure from the change in page or tab gotfocus,
etc.). For all programming purposes any types of controls on tab pages are
referred to just as if they were not on a tab. If in fact you do run an
event procedure off a tab page click, that event related to the tab is for an
execution point only.

That said, not worrying about what tab page the subforms are on might make
the situation a bit easier to digest with less to wade through.



Verify all of your sql statements. Run the procedures in debug mode,
debug.print the sql to the immediate window after it's values are filled, and
copy&paste it to an sql view in the query designer to see if it's pulling up
data (I have a custom function that will attempt to open and return a
recordcount of a given select query, but the sql in the designer will let you
view the data as well). This is often a culprit in 'subform has no data'
issues, and often gets skipped in design process (access doesn't check this
for you).


and just to verify:
Me.tblMaterialsDatabase_subform.Form.RecordSource = strSubformSQL

tblMaterialsDatabase_Subform is the name of the Control, and not the Form,
right?


hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



Ayo said:
I have amain form with two subforms embeded into two TabCtl Pages. I have 3
Tables: tblSiteList, tblMaterialsDatabase and tblMilestones. They are the
following fields in common so I used them as the Link Fields, Master & Child.
tblSiteList:Site Id, Market, NLP
tblMaterialsDatabase:Site ID, Market, NLP
tblMilestones:Candidate Id,Market Name,UMTS NLP Status

tblSiteList has a One-to-Many relationship with the other two tables.
Currently I only have one form with tblSiteList as the Record Source. In the
form there is a TabCtl with 2 Pages named Materials and Milestones
respectively.
In each page there is a subform "tblMilestones subform" on Milestones and
"tblMaterialsDatabase subform" on Materials. The goal is to have the subform
display data based on Site Id, Market, NLP fields on the mainform. Also the
Materials tab also have a combobox called cmbShoppingCart which is used in
conjunction withthe Site Id, Market, NLP fields to narrow down the result in
the tblMilestones subform." But for some reason I am not getting anything in
my subforms and I can't figure it out.
I need help. I have been struggling with this now for about 4 days with no
results. Please help.

Private Sub psBuildComboSQL()
Dim strSQL As String
strSQL = "SELECT DISTINCT [Shopping Cart] " & _
"FROM tblMaterialsDatabase " & _
"WHERE ([Site ID] = """ & Me.cmbSiteId & """) AND " & _
"([Market] = """ & Me.Market & """) AND " & _
"([NLP] = """ & Me.NLP & """) " & _
"ORDER BY [Shopping Cart]"
Me.cmbShoppingCart.RowSource = strSQL
Me.cmbShoppingCart = Null
Me.cmbShoppingCart.Requery
End Sub

Private Sub psBuildMilestoneSubformSQL()
Dim strSubformSQL As String, siteID As String
strSubformSQL = "SELECT * " & _
"FROM tblMilestones " & _
"WHERE ([Candidate Id] = "" & Me.cmbSiteId & "") AND " & _
"([Market Name] = "" & Me.Market & "") AND " & _
"([UMTS NLP Status] = "" & Me.NLP & "")"
Me.tblMilestones_subform.Form.RecordSource = strSubformSQL
End Sub

Private Sub psBuildSubformSQL()
Dim strSubformSQL As String
strSubformSQL = "SELECT * " & _
"FROM tblMaterialsDatabase " & _
"WHERE ([Site ID] = """ & Me.cmbSiteId & """) AND " & _
"([Market] = """ & Me.Market & """) AND " & _
"([NLP] = """ & Me.NLP & """) AND " & _
"([Shopping Cart] = """ & Me.cmbShoppingCart & """)"
Me.tblMaterialsDatabase_subform.Form.RecordSource = strSubformSQL
End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subject: Data not showing up in Subform 0
Subform Help Needed 4
Help with Form code 3
Duplicate record in Form and Subform 0
Subform not allowing data entry 5
Data not showing properly in subform 2
not allow data in sub-subform until subform field has data 1
Duplicate Subform and SubSubForm Records but not Main 1

Top