Subform Help Needed

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

John Smith

You list three sub-routines but you do not say where you are calling them, this
may be your problem.

You may have a reason for doing it this way but why not just set the Link Child
Fields and Link Master Fields properties of the sub-form controls and let Access
do it for you? Note that if you want to use controls rather than columns in the
Master fields then you have to type all the fields yourself, the wizard can't do it.

HTH
John
##################################
Don't Print - Save trees
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

I think that might be my problem. I used the Link Child Fields and Link
Master Fields properties of the Sub-Form controls and I still have the same
problem.
The sub-routines are being called from controls on the the main form. Both
of my sub-form have the Link Child Fields and Link Master Fields properties
filled in with the fields that are common to both.


John Smith said:
You list three sub-routines but you do not say where you are calling them, this
may be your problem.

You may have a reason for doing it this way but why not just set the Link Child
Fields and Link Master Fields properties of the sub-form controls and let Access
do it for you? Note that if you want to use controls rather than columns in the
Master fields then you have to type all the fields yourself, the wizard can't do it.

HTH
John
##################################
Don't Print - Save trees
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...
 
J

John Smith

If you are using the link fields then you should not be altering the record
source as well, this will loose your links. Note though that if the mentioned
combos are unbound then you need a mix of fields and controls to meet your need
e.g.:

Master: cmbSiteId, Market, NLP
Child: [Candidate Id], [Market Name], [UMTS NLP Status]

John
##################################
Don't Print - Save trees
Ayo said:
I think that might be my problem. I used the Link Child Fields and Link
Master Fields properties of the Sub-Form controls and I still have the same
problem.
The sub-routines are being called from controls on the the main form. Both
of my sub-form have the Link Child Fields and Link Master Fields properties
filled in with the fields that are common to both.

John Smith said:
You list three sub-routines but you do not say where you are calling them, this
may be your problem.

You may have a reason for doing it this way but why not just set the Link Child
Fields and Link Master Fields properties of the sub-form controls and let Access
do it for you? Note that if you want to use controls rather than columns in the
Master fields then you have to type all the fields yourself, the wizard can't do it.
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...
Click to expand...
 
A

Ayo

Thanks John but I think something else is wrong. I have already decided to
send it to soemone on "Elance". I will be paying $130 for him to seefix it
and help with other parts of the database. Once I get it back I will take a
look at the code and figure out what it is that I was messing up.

John Smith said:
If you are using the link fields then you should not be altering the record
source as well, this will loose your links. Note though that if the mentioned
combos are unbound then you need a mix of fields and controls to meet your need
e.g.:

Master: cmbSiteId, Market, NLP
Child: [Candidate Id], [Market Name], [UMTS NLP Status]

John
##################################
Don't Print - Save trees
Ayo said:
I think that might be my problem. I used the Link Child Fields and Link
Master Fields properties of the Sub-Form controls and I still have the same
problem.
The sub-routines are being called from controls on the the main form. Both
of my sub-form have the Link Child Fields and Link Master Fields properties
filled in with the fields that are common to both.

John Smith said:
You list three sub-routines but you do not say where you are calling them, this
may be your problem.

You may have a reason for doing it this way but why not just set the Link Child
Fields and Link Master Fields properties of the sub-form controls and let Access
do it for you? Note that if you want to use controls rather than columns in the
Master fields then you have to type all the fields yourself, the wizard can't do it.

Ayo wrote:
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...
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

Data not showing up in Subform 2
Subject: Data not showing up in Subform 0
Help with Form code 3
Duplicate record in Form and Subform 0
Duplicating reocords from main form and subform receiving Runtime Error 3022 2
how do I require entry of a field in a subform 7
Urgent Help Needed 10
Subform help please 3

Top