Filling controls on a subform based on value in main form

[Guest]

I have a main form with a tab control that contains several tabs showing
subforms. On the main form, users are required first to select a state and
then to select a series of categories from cascading combo boxes. When
specific values are selected in the third combo(cboDescription), I want the
appropriate/related tab to move forward and its subform to display specified
data. For example, if the cboDescription = "IP Address", I want the
Communications Data tab to move to the front displaying the current IP
addresses for a customer. (The name of the subform control is
tab5_CommsData.)

Here's the code I'm using (that doesn't work - get error on RecordSource):
If Me![cboDescription] = 14 Then
Me.Tab5_CommsData.SetFocus
Tab5_CommsData.RecordSource = "SELECT DISTINCTROW " & _
"tbl_Comms_Information.RouterLAN, " & _
"tbl_Comms_Information.RouterLANSubnet, " & _
"tbl_Comms_Information.Gateway, " & _
"tbl_Comms_Information.Mainframe, " & _
"tbl_Comms_Information.MainframeSubnet, " & _
"FROM tbl_Comms_Information " & _
"WHERE tbl_Comms_Information.State = ' " & _
Forms!frm_SR_Data_Entry.cboState.Value & " ';"
End If

The tab does move to the front (but it shows for all records,not just the
one where IP Address is, nor does it display when the DB is opened again.)
But right now, I'm at a loss as to how to get the data to show. Any
suggestions?

 

[John Spencer (MVP)]

Line 8 has an extra comma.
Line 10 and Line 11 have extra spaces before or after the "single quote" mark.

Rewrite as follows and see if that clears up the problem

If Me![cboDescription] = 14 Then
Me.Tab5_CommsData.SetFocus
Tab5_CommsData.RecordSource = "SELECT DISTINCTROW " & _
"tbl_Comms_Information.RouterLAN, " & _
"tbl_Comms_Information.RouterLANSubnet, " & _
"tbl_Comms_Information.Gateway, " & _
"tbl_Comms_Information.Mainframe, " & _
"tbl_Comms_Information.MainframeSubnet " & _
"FROM tbl_Comms_Information " & _
"WHERE tbl_Comms_Information.State = '" & _
Forms!frm_SR_Data_Entry.cboState.Value & "';"
End If

 

[Guest]

Thanks for helping me clean up my SQL. I copied from a book and it looked as
if there were spaces after the single quotes.

I still had an error when compiling and after some research found that the
correct syntax for the query is: Me!SubformControlName!Form =
(I added this comment in case someone else has the same question. Syntax is
everything.) Thanks again.
--
susan

Line 8 has an extra comma.
Line 10 and Line 11 have extra spaces before or after the "single quote" mark.

Rewrite as follows and see if that clears up the problem

If Me![cboDescription] = 14 Then
Me.Tab5_CommsData.SetFocus
Tab5_CommsData.RecordSource = "SELECT DISTINCTROW " & _
"tbl_Comms_Information.RouterLAN, " & _
"tbl_Comms_Information.RouterLANSubnet, " & _
"tbl_Comms_Information.Gateway, " & _
"tbl_Comms_Information.Mainframe, " & _
"tbl_Comms_Information.MainframeSubnet " & _
"FROM tbl_Comms_Information " & _
"WHERE tbl_Comms_Information.State = '" & _
Forms!frm_SR_Data_Entry.cboState.Value & "';"
End If

I have a main form with a tab control that contains several tabs showing
subforms. On the main form, users are required first to select a state and
then to select a series of categories from cascading combo boxes. When
specific values are selected in the third combo(cboDescription), I want the
appropriate/related tab to move forward and its subform to display specified
data. For example, if the cboDescription = "IP Address", I want the
Communications Data tab to move to the front displaying the current IP
addresses for a customer. (The name of the subform control is
tab5_CommsData.)

Here's the code I'm using (that doesn't work - get error on RecordSource):
If Me![cboDescription] = 14 Then
Me.Tab5_CommsData.SetFocus
Tab5_CommsData.RecordSource = "SELECT DISTINCTROW " & _
"tbl_Comms_Information.RouterLAN, " & _
"tbl_Comms_Information.RouterLANSubnet, " & _
"tbl_Comms_Information.Gateway, " & _
"tbl_Comms_Information.Mainframe, " & _
"tbl_Comms_Information.MainframeSubnet, " & _
"FROM tbl_Comms_Information " & _
"WHERE tbl_Comms_Information.State = ' " & _
Forms!frm_SR_Data_Entry.cboState.Value & " ';"
End If

The tab does move to the front (but it shows for all records,not just the
one where IP Address is, nor does it display when the DB is opened again.)
But right now, I'm at a loss as to how to get the data to show. Any
suggestions?