Sub Form Combo Box

  • Thread starter Thread starter Dan @BCBS
  • Start date Start date
D

Dan @BCBS

I don't understand why this code does not work.
When the user enters a date in a text box the subform combo box (CA_NAME)
should only show two choices.

But nothing happens, no error, nothing..

Help please....


Private Sub TR_ACKNOWLTR_GotFocus()
If Me.TR_ACKNOWLTR <> " " Then
Me!frmCaseLog!CA_NAME.RowSource = "Select * from tblCommitteeName
where [C_Committee] = 'AG'or 'AL'"

End If
End Sub
 
Dan @BCBS said:
I don't understand why this code does not work.
When the user enters a date in a text box the subform combo box (CA_NAME)
should only show two choices.

But nothing happens, no error, nothing..

Help please....


Private Sub TR_ACKNOWLTR_GotFocus()
If Me.TR_ACKNOWLTR <> " " Then
Me!frmCaseLog!CA_NAME.RowSource = "Select * from tblCommitteeName
where [C_Committee] = 'AG'or 'AL'"

End If
End Sub
Click to expand...

Look like you're referring to the subform control, not the subform itself.
Try:

Me!frmCaseLog.Form!CA_NAME.RowSource = "Select * from tblCommitteeName
 
When do you expect this event to happen? A user entering a date in a text
box will not cause the GotFocus event to happen unless 1) the combo box is a
tab stop and it is next in the tab order OR 2) the user clicks in the combo
box. Maybe you want to put the code in the after update event of the text
box?

Where are the two controls? If the textbox is on the main form and the combo
box is on the subform, refer to it as:

Me!SubformControl.form!CA_NAME,

where SubformControl is the name of the control containing the subform, NOT
the name of the subform itself.

You could try adding the line:

Me!SubformControl.form!CA_NAME.Requery


John

I don't understand why this code does not work.
When the user enters a date in a text box the subform combo box (CA_NAME)
should only show two choices.

But nothing happens, no error, nothing..

Help please....

Private Sub TR_ACKNOWLTR_GotFocus()
If Me.TR_ACKNOWLTR <> " " Then
Me!frmCaseLog!CA_NAME.RowSource = "Select * from tblCommitteeName
where [C_Committee] = 'AG'or 'AL'"

End If
End Sub
Click to expand...
 
I feel reall dumb asking this but your comment "where SubformControl is the
name of the control containing the subform, NOT the name of the subform
itself."

"Control Containing the Subform"??

I'm not sure what the difference is!!

Private Sub TR_ACKNOWLTR_AfterUpdate()
If Me.TR_ACKNOWLTR <> " " Then
Me!frmCaseLog.Form!CA_NAME.RowSource = "Select * from tblCommitteeName where
[C_Committee] = 'CG'or 'IR'"

Me!frmCaseLog.Form!CA_NAME.Requery

End If
End Sub


Thanks





J_Goddard via AccessMonster.com said:
When do you expect this event to happen? A user entering a date in a text
box will not cause the GotFocus event to happen unless 1) the combo box is a
tab stop and it is next in the tab order OR 2) the user clicks in the combo
box. Maybe you want to put the code in the after update event of the text
box?

Where are the two controls? If the textbox is on the main form and the combo
box is on the subform, refer to it as:

Me!SubformControl.form!CA_NAME,

where SubformControl is the name of the control containing the subform, NOT
the name of the subform itself.

You could try adding the line:

Me!SubformControl.form!CA_NAME.Requery


John

I don't understand why this code does not work.
When the user enters a date in a text box the subform combo box (CA_NAME)
should only show two choices.

But nothing happens, no error, nothing..

Help please....

Private Sub TR_ACKNOWLTR_GotFocus()
If Me.TR_ACKNOWLTR <> " " Then
Me!frmCaseLog!CA_NAME.RowSource = "Select * from tblCommitteeName
where [C_Committee] = 'AG'or 'AL'"

End If
End Sub
Click to expand...
Click to expand...
 
Dan @BCBS said:
I feel reall dumb asking this but your comment "where SubformControl is
the
name of the control containing the subform, NOT the name of the subform
itself."

"Control Containing the Subform"??

I'm not sure what the difference is!!

Private Sub TR_ACKNOWLTR_AfterUpdate()
If Me.TR_ACKNOWLTR <> " " Then
Me!frmCaseLog.Form!CA_NAME.RowSource = "Select * from tblCommitteeName
where
[C_Committee] = 'CG'or 'IR'"

Me!frmCaseLog.Form!CA_NAME.Requery

End If
End Sub


Thanks





J_Goddard via AccessMonster.com said:
When do you expect this event to happen? A user entering a date in a
text
box will not cause the GotFocus event to happen unless 1) the combo box
is a
tab stop and it is next in the tab order OR 2) the user clicks in the
combo
box. Maybe you want to put the code in the after update event of the
text
box?

Where are the two controls? If the textbox is on the main form and the
combo
box is on the subform, refer to it as:

Me!SubformControl.form!CA_NAME,

where SubformControl is the name of the control containing the subform,
NOT
the name of the subform itself.

You could try adding the line:

Me!SubformControl.form!CA_NAME.Requery


John

I don't understand why this code does not work.
When the user enters a date in a text box the subform combo box
(CA_NAME)
should only show two choices.

But nothing happens, no error, nothing..

Help please....

Private Sub TR_ACKNOWLTR_GotFocus()
If Me.TR_ACKNOWLTR <> " " Then
Me!frmCaseLog!CA_NAME.RowSource = "Select * from
tblCommitteeName
where [C_Committee] = 'AG'or 'AL'"

End If
End Sub
Click to expand...
Click to expand...
Click to expand...

Open your form in design view. Click once on the subform. Look at the
property sheet's caption. This is a subform control. What it does is to
provide a placeholder for the form which is to be used as the subform. It is
said to 'host' the form object - ie it is a container control (think of it
like the image control, which hosts an image, except that in this case it
hosts another form).

When you refer to these objects in code, just follow the name of the subform
control with .Form to refer to controls/properties etc. on the subform
itself.

Hope that helps
 
Hi -

Subforms are contained in sub-form controls, which have properties just like
any other control. One of these properties is "Name" - the name of the
control. Another property is "Source Object", which specifies what form is
in that control.

When you add a sub-form to a form (using the wizard), it will ask you to
select the form you want to use as a sub-form, and it will then ask you "What
name would you like for your sub-form or sub-report?". The response you give
here is the name of the *control*, which can be the same as the name of the
form *in* the control, but does not have to be.

Suppose your main form is MyMainForm, the subform *control* name is
SubFormControl, the form in the control is MySubForm, on whic is a textbox
called Text1.

To refer to the textbox Text1 from code on the main form, you would use:

Me![SubformControl].form![Text1]

Note that the sub-form name - MySubform - is not in the reference.

Does that help - I hope? I know, it is confusing, I went through it too!

John



I feel reall dumb asking this but your comment "where SubformControl is the
name of the control containing the subform, NOT the name of the subform
itself."

"Control Containing the Subform"??

I'm not sure what the difference is!!

Private Sub TR_ACKNOWLTR_AfterUpdate()
If Me.TR_ACKNOWLTR <> " " Then
Me!frmCaseLog.Form!CA_NAME.RowSource = "Select * from tblCommitteeName where
[C_Committee] = 'CG'or 'IR'"

Me!frmCaseLog.Form!CA_NAME.Requery

End If
End Sub

Thanks
When do you expect this event to happen? A user entering a date in a text
box will not cause the GotFocus event to happen unless 1) the combo box is a
Click to expand...
[quoted text clipped - 31 lines]
Click to expand...
 
Correct me if I am wrong but if I look at the properties of the subform (on
the main form) the "Name" and "Source Object" are both "frmCaseLog" which
says the control is frmCaseLog.

You confused me a little when you said "to refer to the textbox from the
main form" because I am trying to refer to a combo box, on the subform, from
the main form!

Subform contorl = frmCaseLog.
Combo box on the sub form = CA_NAME
Table which combobox is made from = tblCommitteeName

And I need only two choice from that table to appear in the combo drop down.

If Me.TR_ACKNOWLTR <> " " Then
Me!frmCaseLog.Form!CA_NAME.RowSource = "Select * from tblCommitteeName where
[C_Committee] = 'CG'or 'IR'"

I appreciate the help!!





J_Goddard via AccessMonster.com said:
Hi -

Subforms are contained in sub-form controls, which have properties just like
any other control. One of these properties is "Name" - the name of the
control. Another property is "Source Object", which specifies what form is
in that control.

When you add a sub-form to a form (using the wizard), it will ask you to
select the form you want to use as a sub-form, and it will then ask you "What
name would you like for your sub-form or sub-report?". The response you give
here is the name of the *control*, which can be the same as the name of the
form *in* the control, but does not have to be.

Suppose your main form is MyMainForm, the subform *control* name is
SubFormControl, the form in the control is MySubForm, on whic is a textbox
called Text1.

To refer to the textbox Text1 from code on the main form, you would use:

Me![SubformControl].form![Text1]

Note that the sub-form name - MySubform - is not in the reference.

Does that help - I hope? I know, it is confusing, I went through it too!

John



I feel reall dumb asking this but your comment "where SubformControl is the
name of the control containing the subform, NOT the name of the subform
itself."

"Control Containing the Subform"??

I'm not sure what the difference is!!

Private Sub TR_ACKNOWLTR_AfterUpdate()
If Me.TR_ACKNOWLTR <> " " Then
Me!frmCaseLog.Form!CA_NAME.RowSource = "Select * from tblCommitteeName where
[C_Committee] = 'CG'or 'IR'"

Me!frmCaseLog.Form!CA_NAME.Requery

End If
End Sub

Thanks
When do you expect this event to happen? A user entering a date in a text
box will not cause the GotFocus event to happen unless 1) the combo box is a
Click to expand...
[quoted text clipped - 31 lines]
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Hi -
Correct me if I am wrong but if I look at the properties of the subform (on
the main form) the "Name" and "Source Object" are both "frmCaseLog" which
says the control is frmCaseLog.
Click to expand...

Your observation is correct. It's often done this way.

Just add one line to your code, and fix the where clause in the select
statement:

If Me.TR_ACKNOWLTR <> " " Then
Me!frmCaseLog.Form!CA_NAME.RowSource = "Select * from tblCommitteeName where
[C_Committee] = 'CG' or [C_Committee] = 'IR'" <--- note the change in this
line
Me!frmCaseLog.Form!CA_NAME.Requery <--- Add this line

This would go in the After Update event of Me.TR_ACKNOWLTR, I think.

John


Correct me if I am wrong but if I look at the properties of the subform (on
the main form) the "Name" and "Source Object" are both "frmCaseLog" which
says the control is frmCaseLog.

You confused me a little when you said "to refer to the textbox from the
main form" because I am trying to refer to a combo box, on the subform, from
the main form!

Subform contorl = frmCaseLog.
Combo box on the sub form = CA_NAME
Table which combobox is made from = tblCommitteeName

And I need only two choice from that table to appear in the combo drop down.

If Me.TR_ACKNOWLTR <> " " Then
Me!frmCaseLog.Form!CA_NAME.RowSource = "Select * from tblCommitteeName where
[C_Committee] = 'CG'or 'IR'"

I appreciate the help!!
Hi -
Click to expand...
[quoted text clipped - 48 lines]
Click to expand...
 
No idea what your reply means???
[quoted text clipped - 48 lines]
Click to expand...








J_Goddard via AccessMonster.com said:
Hi -
Correct me if I am wrong but if I look at the properties of the subform (on
the main form) the "Name" and "Source Object" are both "frmCaseLog" which
says the control is frmCaseLog.
Click to expand...

Your observation is correct. It's often done this way.

Just add one line to your code, and fix the where clause in the select
statement:

If Me.TR_ACKNOWLTR <> " " Then
Me!frmCaseLog.Form!CA_NAME.RowSource = "Select * from tblCommitteeName where
[C_Committee] = 'CG' or [C_Committee] = 'IR'" <--- note the change in this
line
Me!frmCaseLog.Form!CA_NAME.Requery <--- Add this line

This would go in the After Update event of Me.TR_ACKNOWLTR, I think.

John


Correct me if I am wrong but if I look at the properties of the subform (on
the main form) the "Name" and "Source Object" are both "frmCaseLog" which
says the control is frmCaseLog.

You confused me a little when you said "to refer to the textbox from the
main form" because I am trying to refer to a combo box, on the subform, from
the main form!

Subform contorl = frmCaseLog.
Combo box on the sub form = CA_NAME
Table which combobox is made from = tblCommitteeName

And I need only two choice from that table to appear in the combo drop down.

If Me.TR_ACKNOWLTR <> " " Then
Me!frmCaseLog.Form!CA_NAME.RowSource = "Select * from tblCommitteeName where
[C_Committee] = 'CG'or 'IR'"

I appreciate the help!!
Hi -
Click to expand...
[quoted text clipped - 48 lines]
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Which part confused you?

Your original post had the where clause (in the select statement) like this:

...where [C_Committee] = 'CG'or 'IR'" 'CG'or 'IR'"

This will not work; you have to explicitly state [C_Committee] = for both
possible values:

...where [C_Committee] = 'CG 'or [C_Committee] = 'IR'"

An analogy: In English we can say "The light is red or green" and it is
perfectly understandable, but to phrase it the way SQL needs it, we would
have to say: "The light is red or the light is green".

The line Me!frmCaseLog.Form!CA_NAME.Requery causes the value list in the
combo box to be updated.

You didn't state where your code was, but since it uses Me.TR_ACKNOWLTR in
an if statement, it seems reasonable (I thought) to put in an event for that
control.

John



No idea what your reply means???
[quoted text clipped - 48 lines]
End If
End Sub
Click to expand...
Click to expand...
Hi -
Click to expand...
[quoted text clipped - 42 lines]
Click to expand...
 
Back
Top