set rowsource on subform comboBox

  • Thread starter Thread starter Janet
  • Start date Start date
J

Janet

I had requested assistance earlier. Many apologies and
thanks to Marsh for replying to earlier email. But I
discovered what I really need is:

Background is the same: I have three forms. FormA is a
list of salads with two buttons that open the same form
(FormB). FormB contains the ingredients for the various
salads. FormB has a datasheet subform (FormBsub)
allowing you to add or delete ingredients.

FormA: tblSalad, saladID, saladName
child/master link of saladID=saladID between FormA/B
FormB: saladID, ingredientID
FormBsub: ingredientID, ingredientTypeID in continuous
form format with combo box to select above.

What I'd like to do is be able to click Button A on
FormA and have FormB appear but only have items listed in
FormBsub.comboBox with an ingredientTypeID=2 OR if I
click Button B on FormA, have items listed in
FormBsub.comboBox where ingredientTypeID<>2.

I've Tried setting the button click event to:
DoCmd.OpenForm "frmSubParticipants",
whereCondition:="subEventID = [forms]![frmSubEvents]!
[subEventID]"
Forms!frmSubParticipants!txtISDN = 1


Then on the FormBsub load event:
Private Sub Form_Load()
With Me!cboSite
If Forms!frmSubParticipants!txtISDN = 1 Then
.RowSource = "SELECT dbo_xTblSites.siteID,
dbo_xTblSites.siteName, dbo_xTblSites.siteCoor,
dbo_xTblNetwork.networkMethod, " _
& "dbo_xTblNetwork.networkID FROM
(dbo_xTblSites RIGHT JOIN dbo_tblSiteNetworks ON
dbo_xTblSites.siteID = dbo_tblSiteNetworks.siteID) " _
& "LEFT JOIN dbo_xTblNetwork ON
dbo_tblSiteNetworks.networkMethodID =
dbo_xTblNetwork.networkID" _
& "WHERE
(dbo_tblSiteNetworks.networkMethodID<>2) order by
dbo_xTblSites.siteName"
Else
.RowSource = "SELECT dbo_xTblSites.siteID,
dbo_xTblSites.siteName, dbo_xTblSites.siteCoor,
dbo_xTblNetwork.networkMethod, " _
& "dbo_xTblNetwork.networkID FROM
(dbo_xTblSites LEFT JOIN dbo_tblSiteNetworks ON
dbo_xTblSites.siteID = dbo_tblSiteNetworks.siteID) " _
& "LEFT JOIN dbo_xTblNetwork ON
dbo_tblSiteNetworks.networkMethodID =
dbo_xTblNetwork.networkID WHERE " _
& "(dbo_tblSiteNetworks.networkMethodID=2)"
End If
.RowSourceType = "Table/Query"
.BoundColumn = 1
.ColumnCount = 5
.ColumnWidths = "0in;1.7077in;1.166in;1in;0in"
.ColumnHeads = False
.ListRows = 8
End With
End Sub

And, on the AfterUpdate property of the cbo.Site event
Set Forms!frmSubParticipants!frmSubParticipantsB!
networkMethodID = Forms!frmSubParticipants!
frmSubParticipantsB!cboSite!Column(4)

But, I always get the rowset equivalent=2 no matter which
button I use and I get a "Property let procedure not
defined and property get procedure did not return an
object" after updating the combo box. The txtISDN field
does reflect the correct numeric value.

I'm pluggin' away, guys and gals, but could use some
pointers....

jb
 
Look at the syntax of your rowsource object. It needs to be formatted as a
string...
like this:
whereCondition:="subEventID = " & [forms]![frmSubEvents]![subEventID]
when subEventID is a numeric field value
or
whereCondition:="subEventID = " & "'" & [forms]![frmSubEvents]!>
[subEventID] & "'"
when subEventID is a string value
THT
Janet said:
I had requested assistance earlier. Many apologies and
thanks to Marsh for replying to earlier email. But I
discovered what I really need is:

Background is the same: I have three forms. FormA is a
list of salads with two buttons that open the same form
(FormB). FormB contains the ingredients for the various
salads. FormB has a datasheet subform (FormBsub)
allowing you to add or delete ingredients.

FormA: tblSalad, saladID, saladName
child/master link of saladID=saladID between FormA/B
FormB: saladID, ingredientID
FormBsub: ingredientID, ingredientTypeID in continuous
form format with combo box to select above.

What I'd like to do is be able to click Button A on
FormA and have FormB appear but only have items listed in
FormBsub.comboBox with an ingredientTypeID=2 OR if I
click Button B on FormA, have items listed in
FormBsub.comboBox where ingredientTypeID<>2.

I've Tried setting the button click event to:
DoCmd.OpenForm "frmSubParticipants",
whereCondition:="subEventID = [forms]![frmSubEvents]!
[subEventID]"
Forms!frmSubParticipants!txtISDN = 1


Then on the FormBsub load event:
Private Sub Form_Load()
With Me!cboSite
If Forms!frmSubParticipants!txtISDN = 1 Then
.RowSource = "SELECT dbo_xTblSites.siteID,
dbo_xTblSites.siteName, dbo_xTblSites.siteCoor,
dbo_xTblNetwork.networkMethod, " _
& "dbo_xTblNetwork.networkID FROM
(dbo_xTblSites RIGHT JOIN dbo_tblSiteNetworks ON
dbo_xTblSites.siteID = dbo_tblSiteNetworks.siteID) " _
& "LEFT JOIN dbo_xTblNetwork ON
dbo_tblSiteNetworks.networkMethodID =
dbo_xTblNetwork.networkID" _
& "WHERE
(dbo_tblSiteNetworks.networkMethodID<>2) order by
dbo_xTblSites.siteName"
Else
.RowSource = "SELECT dbo_xTblSites.siteID,
dbo_xTblSites.siteName, dbo_xTblSites.siteCoor,
dbo_xTblNetwork.networkMethod, " _
& "dbo_xTblNetwork.networkID FROM
(dbo_xTblSites LEFT JOIN dbo_tblSiteNetworks ON
dbo_xTblSites.siteID = dbo_tblSiteNetworks.siteID) " _
& "LEFT JOIN dbo_xTblNetwork ON
dbo_tblSiteNetworks.networkMethodID =
dbo_xTblNetwork.networkID WHERE " _
& "(dbo_tblSiteNetworks.networkMethodID=2)"
End If
.RowSourceType = "Table/Query"
.BoundColumn = 1
.ColumnCount = 5
.ColumnWidths = "0in;1.7077in;1.166in;1in;0in"
.ColumnHeads = False
.ListRows = 8
End With
End Sub

And, on the AfterUpdate property of the cbo.Site event
Set Forms!frmSubParticipants!frmSubParticipantsB!
networkMethodID = Forms!frmSubParticipants!
frmSubParticipantsB!cboSite!Column(4)

But, I always get the rowset equivalent=2 no matter which
button I use and I get a "Property let procedure not
defined and property get procedure did not return an
object" after updating the combo box. The txtISDN field
does reflect the correct numeric value.

I'm pluggin' away, guys and gals, but could use some
pointers....

jb
Click to expand...
 
THT,
Thanks, but it must have been a funky cut/paste, cause
that is the where condition I have and that part of the
series is working correctly. The field does change
depending on the button I press (see very bottom of
original post).

I think one of the problems is where I placed the event,
so I switched it to happen on the load of the parent form
frmSubParticipants. That gave me the correct 2/<>2.
But, now, if there are no '2' in the current list, you
get nada in the combo box. Is the problem with
child/master linking on the subParticipants and
subParticipantsB forms? Should I try have two subforms,
one with the rowsource query coded for <>2 and one for 2
and then use the visible to make one disappear?

Janet

-----Original Message-----
Look at the syntax of your rowsource object. It needs to be formatted as a
string...
like this:
whereCondition:="subEventID = " & [forms]![frmSubEvents]! [subEventID]
when subEventID is a numeric field value
or
whereCondition:="subEventID = " & "'" & [forms]! [frmSubEvents]!>
[subEventID] & "'"
when subEventID is a string value
THT
Janet said:
I had requested assistance earlier. Many apologies and
thanks to Marsh for replying to earlier email. But I
discovered what I really need is:

Background is the same: I have three forms. FormA is a
list of salads with two buttons that open the same form
(FormB). FormB contains the ingredients for the various
salads. FormB has a datasheet subform (FormBsub)
allowing you to add or delete ingredients.

FormA: tblSalad, saladID, saladName
child/master link of saladID=saladID between FormA/B
FormB: saladID, ingredientID
FormBsub: ingredientID, ingredientTypeID in continuous
form format with combo box to select above.

What I'd like to do is be able to click Button A on
FormA and have FormB appear but only have items listed in
FormBsub.comboBox with an ingredientTypeID=2 OR if I
click Button B on FormA, have items listed in
FormBsub.comboBox where ingredientTypeID<>2.

I've Tried setting the button click event to:
DoCmd.OpenForm "frmSubParticipants",
whereCondition:="subEventID = [forms]![frmSubEvents]!
[subEventID]"
Forms!frmSubParticipants!txtISDN = 1


Then on the FormBsub load event:
Private Sub Form_Load()
With Me!cboSite
If Forms!frmSubParticipants!txtISDN = 1 Then
.RowSource = "SELECT dbo_xTblSites.siteID,
dbo_xTblSites.siteName, dbo_xTblSites.siteCoor,
dbo_xTblNetwork.networkMethod, " _
& "dbo_xTblNetwork.networkID FROM
(dbo_xTblSites RIGHT JOIN dbo_tblSiteNetworks ON
dbo_xTblSites.siteID = dbo_tblSiteNetworks.siteID) " _
& "LEFT JOIN dbo_xTblNetwork ON
dbo_tblSiteNetworks.networkMethodID =
dbo_xTblNetwork.networkID" _
& "WHERE
(dbo_tblSiteNetworks.networkMethodID<>2) order by
dbo_xTblSites.siteName"
Else
.RowSource = "SELECT dbo_xTblSites.siteID,
dbo_xTblSites.siteName, dbo_xTblSites.siteCoor,
dbo_xTblNetwork.networkMethod, " _
& "dbo_xTblNetwork.networkID FROM
(dbo_xTblSites LEFT JOIN dbo_tblSiteNetworks ON
dbo_xTblSites.siteID = dbo_tblSiteNetworks.siteID) " _
& "LEFT JOIN dbo_xTblNetwork ON
dbo_tblSiteNetworks.networkMethodID =
dbo_xTblNetwork.networkID WHERE " _
& "(dbo_tblSiteNetworks.networkMethodID=2)"
End If
.RowSourceType = "Table/Query"
.BoundColumn = 1
.ColumnCount = 5
.ColumnWidths = "0in;1.7077in;1.166in;1in;0in"
.ColumnHeads = False
.ListRows = 8
End With
End Sub

And, on the AfterUpdate property of the cbo.Site event
Set Forms!frmSubParticipants!frmSubParticipantsB!
networkMethodID = Forms!frmSubParticipants!
frmSubParticipantsB!cboSite!Column(4)

But, I always get the rowset equivalent=2 no matter which
button I use and I get a "Property let procedure not
defined and property get procedure did not return an
object" after updating the combo box. The txtISDN field
does reflect the correct numeric value.

I'm pluggin' away, guys and gals, but could use some
pointers....

jb
Click to expand...


.
Click to expand...
 
Hello Janet,

I would appreciate your patience while I am looking into this issue. I will
post my response at soon as I have update for you.

Thanks for posting to MSDN Managed Newsgroup.

Thank you,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
Hi Janet,

Thanks for your post. According to your description, I understand that you
have FormA with two buttons called Button A and Button B. What you want to
do is being able to click Button A on FormA to open another FormB, which
listed the records whose value is 2. If you click Button B on FormA, FormB
is still opened with the rest of the records (the value of the specified
column is not 2). If I have misunderstood, please feel free to let me know.

Based on my research, the filter condition is able to be added with the
DoCmd.OpenForm method as the following syntax.

DoCmd.OpenForm "<FormB>", , , "Filter condition"

For more information regarding OpenForm method, please refer to the article
in Microsoft Visual Basic Help.
Topic: "OpenForm Method"

Also, if I can contact you using this (e-mail address removed) address, I will
send my test mdb file to you so that you can check and see if the database
is able to meet your requirements.

Thanks for using MSDN newsgroup.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
Back
Top