Why won't accessing records work?!?!

[boblovesyousomuch]

I am trying to open an sql statement that the vb code will loop through and add the ids to a string. However I can't even open the query. The query works because I use it to populate the rowsource of a combo box.

It won't open the recordset.

If possible as well could you tell me how to do this but by calling a stored procedure with parameters?

Code is:

Dim SQLAgentByDept As String
Dim db As Database
Dim rs As Recordset
SQLAgentByDept = "SELECT [tblAgentList]![A_Forename] & ' ' & [tblAgentList]![A_Surname] " & _
"AS A_Fullname, tblAgentList.A_ID FROM tblAgentList INNER JOIN tblTeamList ON " & _
&quot;tblAgentList.A_TeamID = tblTeamList.T_ID WHERE (((tblAgentList.A_ID)<>[tblTeamList]![T_Leader]) &quot; & _
&quot;AND ((tblTeamList.T_Department)=[Forms]![FrmCallSummary]![Combo_Dept]));&quot;

Me!Combo_Agent.RowSource = SQLAgentByDept



Set db = CurrentDb
Set rs = db.OpenRecordset(SQLAgentByDept)

With rs
While Not .EOF
AgentString = AgentString & &quot;'&quot; & rs(&quot;A_ID&quot & &quot;',&quot;
.MoveNext
Wend
.Close
End With
AgentString = Left(AgentString, Len(AgentString) - 1)

End Code

Any help greatly appreciated.

Carl

 

[RoyVidar]

Hi!

Addressing opening the recordset:
Think the last line of the SQL statement is passing the reference to the form to the SQL statement, not the value it contains.

Something like this might work:

[tt] &quot;AND ((tblTeamList.T_Department)= &quot; & _
[Forms]![FrmCallSummary]![Combo_Dept] & &quot);&quot;[/tt]

if the combovalue is numeric, and something like this:

[tt] &quot;AND ((tblTeamList.T_Department)= '&quot; & _
[Forms]![FrmCallSummary]![Combo_Dept] & &quot;'));&quot;[/tt]

if it's text.

HTH Roy-Vidar

 

[boblovesyousomuch]

Cheers Roy that's sorted out my first problem but do you know how to do the same using a query that is stored in access instead or a hard coded SQL as above ie:

Me!Combo_Agent.RowSource = &quot;QryAgents&quot; & para1,para2 etc

OR

Set rs = db.OpenRecordset(&quot;QryAgents&quot; & para1,para2 etc)


Cheers

 

[RoyVidar]

Hi!

I'm afraid I'll have to pass that on to others. I'm not very fluent in querydef's (which is the reason I only addressed the SQL string thingie in previous post;-))

Check F1 on querydef is the best advice I can give, and hope someone with that knowledge also visits this thread.

Roy-Vidar

 

[Waud]

Carl

The code below is an example of how to call a stored procedure (a saved query) in access and set values to its parameters.

I've assumed that you using DAO if not let me know and I'll post an example using ADO


Dim recRecordSet1 As DAO.Recordset
Dim qdfQuery1 As DAO.QueryDef
Dim strValue1 as String, intValue2 as Integer,

Set qdfQuery1 = CurrentDb.QueryDefs(&quot;Query1&quot
With qdfQuery1
.Parameters(&quot;Parameter1&quot = strValue1
.Parameters(&quot;Parameter2&quot = strValue2
'etc...
End With

Set recRecordSet1 = qdfQuery1.OpenRecordset
With recRecordSet1
Do Until recRecordSet1.EOF
'manipulate records
.MoveNext
Loop
End With

'Clean up
qdfQuery1.Close
recRecordSet1.Close
Set recRecordSet1 = Nothing
Set qdfQuery1 = Nothing

Hope this helps

Chris

 

[boblovesyousomuch]

I am not using DA but Ill work it out cheers.