SQL statement

  • Thread starter Thread starter John
  • Start date Start date
J

John

I have a main form (frmPricing) that has on it a tab
control (TabA) that has on one of the tab pages a subform
(subfrmNewClaims). On the subform is a combo box
(cbxClaimType) and a textbox (txtLineOfBusiness). I am
trying to populate cbxClaimType with a list depending on
what txtLineOfBusiness has in it. I am using the SELECT
statement below in the cbxClaimType_Enter event. But,
when I go to use cbxClaimType it asks for a parameter. Or
I have a combo box on the main form that I could use the
After_Update event but I am not getting the wording right
for the cbxClaimType to update correctly either. Here is
what I in the cbxClaimType_Enter event.

Me.cbxClaimType.RowSource = _
"SELECT " & _
"ClaimType, " & _
"LineOfBusiness " & _
"FROM tblClaimType " & _
"WHERE (((LineOfBusiness) = [Forms]!
[subfrmNewClaims]![txtLineOfBusiness]));"

If anyone has any thoughts or ideas, I thank you for it.
John
 
Since "subfrmNewClaims" is NOT a member of the Forms Collection, you cannot
refer to the Control on it that way.

Try:

Me.cbxClaimType.RowSource = "SELECT ClaimType, " & _
" LineOfBusiness " & _
" FROM tblClaimType " & _
" WHERE (LineOfBusiness = [Form]![txtLineOfBusiness])"

Try also (Assuming LineOfBusiness is a Text Field:

Me.cbxClaimType.RowSource = "SELECT ClaimType, " & _
" LineOfBusiness " & _
" FROM tblClaimType " & _
" WHERE (LineOfBusiness = '" & Me.[txtLineOfBusiness] & "')"

Be careful of the single-quotes & double-quotes used. May be a good idea to
copy from this post.
 
Van, thanks. The first method below worked. I also
changed from On Enter to Got Focus and it worked better.
Again, thanks and Happy New Year.
John


-----Original Message-----
Since "subfrmNewClaims" is NOT a member of the Forms Collection, you cannot
refer to the Control on it that way.

Try:

Me.cbxClaimType.RowSource = "SELECT ClaimType, " & _
" LineOfBusiness " & _
" FROM tblClaimType " & _
" WHERE (LineOfBusiness = [Form]![txtLineOfBusiness])"

Try also (Assuming LineOfBusiness is a Text Field:

Me.cbxClaimType.RowSource = "SELECT ClaimType, " & _
" LineOfBusiness " & _
" FROM tblClaimType " & _
" WHERE (LineOfBusiness = '" & Me.[txtLineOfBusiness] & "')"

Be careful of the single-quotes & double-quotes used. May be a good idea to
copy from this post.

--
HTH
Van T. Dinh
MVP (Access)



I have a main form (frmPricing) that has on it a tab
control (TabA) that has on one of the tab pages a subform
(subfrmNewClaims). On the subform is a combo box
(cbxClaimType) and a textbox (txtLineOfBusiness). I am
trying to populate cbxClaimType with a list depending on
what txtLineOfBusiness has in it. I am using the SELECT
statement below in the cbxClaimType_Enter event. But,
when I go to use cbxClaimType it asks for a parameter. Or
I have a combo box on the main form that I could use the
After_Update event but I am not getting the wording right
for the cbxClaimType to update correctly either. Here is
what I in the cbxClaimType_Enter event.

Me.cbxClaimType.RowSource = _
"SELECT " & _
"ClaimType, " & _
"LineOfBusiness " & _
"FROM tblClaimType " & _
"WHERE (((LineOfBusiness) = [Forms]!
[subfrmNewClaims]![txtLineOfBusiness]));"

If anyone has any thoughts or ideas, I thank you for it.
John
Click to expand...


.
Click to expand...
 
Back
Top