Bound Control Query Problem 1

[StlMacMan]

I have a bound control that works fine if I use the adcmdTable, but I want the adcmdText so that I can use an sql query to limit the return. Cannot get it to populate the display form. I've tried several variations. Can anyone help me see the issue? I've been unable to find examples that reference the form field value syntax. Thanks. --Ed

SELECT * FROM Contract WHERE Corps_Institution =
'" & Form3.CboUnit.Text & "'

 

[jebenson]

From your post it seems you are using an ADO data control. If this is the case, then read on below. If not, could you post a little more code showing how you are executing the SQL and how you are binding to your controls.


SELECT * FROM Contract WHERE Corps_Institution =
'" & Form3.CboUnit.Text & "'

Where are you entering this code? In the Properties dialog of the ADO data control? If so, remove it from there and enter it in your code instead:

Adodc1.CommandType = adCmdText
Adodc1.RecordSource="SELECT * FROM Contract WHERE Corps_Institution =
'" & Form3.CboUnit.Text & "'

Adodc1.Recordset.Requery
Adodc1.Refresh



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[StlMacMan]

Jebenson--Thanks for the response. I removed the sql from the ADODB DC properties section (you were right about where I had it) and put it in the code for the DC as follows:

Private Sub AdoGroupUnitSearch_WillMove(ByVal adReason As ADODB.EventReasonEnum, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

AdoGroupUnitSearch.CommandType = adCmdText
AdoGroupUnitSearch.RecordSource = "SELECT * FROM Contract WHERE Corps_Institution = " _
& "'" & Form3.CboUnit.Text & "'"
AdoGroupUnitSearch.Recordset.Requery
AdoGroupUnitSearch.Refresh

End Sub

I now get this error message:

[ADODC]: no RecordSource specified. [ADO]: Command text was not set for the command object.

Is this because I removed them as properties? What do you think? --Ed

 

[jebenson]

It seems that the code is intended to select records when the user selects a value from Form3.CboUnit. If there is a button or something that the user clicks to process the request, I would put the code to populate the Adodc there, or perhaps in the Load event of the form that is opened to display the response. I think you are getting this error because the data control is trying to open (which will cause the WillMove event to fire) but the RecordSource and such are not set until the WillMove event is fired. Basically, you need to set the Adodc's parameters before it is opened.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[StlMacMan]

Thanks again, Jebenson. I tried putting it in several places--no luck. Then I put the sql text back in the control recordsource property and added your code modifed for my particulars to the Form Load event and--it worked!! Thanks very much. By the way, you right about using a combo box for the user input on the Where parameter. Thanks again. Ed