Combo box requery

[Raddle]

Hi



Could anyone help me with this please? I have combo box that is causing Access to complain.

myFulfilledBy = "Select * from CPReqs where ([FulfilledBy] = " & Me.cbo_FulfilledBy & ")"
Me.sfrm_CPReqs.Form.RecordSource = myFulfilledBy
Me.sfrm_CPReqs.Form.Requery

the combo is called cbo_FulfilledBy but access complains asking me to enter the parameter value. Even when I do it doesn't filter the sub form on the page.

Any hints?

[Bob Fitz]

What is the Data Type of the field called "FulfilledBy" and what Data Type is returned by "Me.cbo_FulfilledBy"?

[pbaldy]

What is specified in the parameter prompt? Is FulfilledBy a text field, or numeric. If text, you need delimiters around the value.

[Raddle]

It’s text. So the combo lists the systems fulfilling the reqs and in the table it’s shorttext. Example would be SaaS + APIs

[pbaldy]

Try

myFulfilledBy = "Select * from CPReqs where [FulfilledBy] = '" & Me.cbo_FulfilledBy & "'"

[Raddle]

so so so good - thank you. So I was using parenthesises when I should have gone for single quotes. Your fix prepares a proper str which is what I have declared. So the full sub routine is



Private Sub cbo_FulfilledBy_AfterUpdate()
Dim myFulfilledBy As String


myFulfilledBy = "Select * from CPReqs where [FulfilledBy] = '" & Me.cbo_FulfilledBy & "'"
Me.sfrm_CPReqs.Form.RecordSource = myFulfilledBy
Me.sfrm_CPReqs.Form.Requery


End Sub


I think you have helped me out before mr PBaldy some time ago in fact. Great to see you are still here helping folk out. Well done. Have a great weekend.

[June7]

Error was due to lack of apostrophe delimiters. The parentheses were irrelevant. Look at any SQL statement in query object and you will see Access has thrown in a bunch of unnecessary parentheses.

[Raddle]

so so so good - thank you. So I was using parenthesises when I should have gone for single quotes. Your fix prepared a proper str which is what I have declared. So the full module is



Private Sub cbo_FulfilledBy_AfterUpdate()
Dim myFulfilledBy As String


myFulfilledBy = "Select * from CPReqs where [FulfilledBy] = '" & Me.cbo_FulfilledBy & "'"
Me.sfrm_CPReqs.Form.RecordSource = myFulfilledBy
Me.sfrm_CPReqs.Form.Requery


End Sub


I think you have helped me out before mr PBaldy some time ago in fact. Great to see you are still here helping folk out. Well done. Have a great weekend.

[pbaldy]

I think you have helped me out before mr PBaldy some time ago in fact. Great to see you are still here helping folk out. Well done. Have a great weekend.

Happy to help!