Trouble updating recordset

[consultant29]

Hello, thanks for reading.

I have a form that is based on dynamic query. There is a "main" form where a user selects search criteria from 2 fields. This then calls another form, where the results are displayed.

I am trying to update the record set at the top of my code in the form. I am getting a prompt to "enter a parameter" when I execute. Can some one help?

Database attached...


Public Function refresh()
' Check for LIKE job id
If Forms!main.job_id > "" Then
varWhere = varWhere & "[JobID] LIKE """ & [Forms]![main]![job_id] & "*"" AND "
End If

' Check for LIKE title and description
If Forms!main.keyword > "" Then
varWhere = varWhere & "[JobTitle] LIKE """ & [Forms]![main]![keyword] & "*"" AND "
End If



' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

' Update the record source
Me.RecordSource = "SELECT * FROM Acacia_Joblist_all_knowledge " & BuildFilter
End Function

 

[adaniele]

could you attach the db again pls.
thx, max.

 

[consultant29]

could you attach the db again pls.
thx, max.

Sorry, please try now!

 

[SamDeMan]

i think its because you are using as part of your recordsource a query. what you need to is use a simple for loop to make sure your parameters are all set.

try this:
Dim QueryCmd As ADODB.Command
Dim Pa As ADODB.Parameter
....
For Each Pa In QueryCmd.Parameters
Pa.Value = Eval(Pa.Name)
Next Pa

good luck,

sam

 

[consultant29]

i think its because you are using as part of your recordsource a query. what you need to is use a simple for loop to make sure your parameters are all set.

try this:
Dim QueryCmd As ADODB.Command
Dim Pa As ADODB.Parameter
....
For Each Pa In QueryCmd.Parameters
Pa.Value = Eval(Pa.Name)
Next Pa

good luck,

sam

I am not sure how to implement this. What should I replace Pa with?

Please help!!!

 

[SamDeMan]

just add the code i gave you. it will automatically know the parameters.

sam

 

[consultant29]

please help

I get the error "invalid outside procedure". It occurs on this line:


For Each Pa In QueryCmd.Parameters

 

[SamDeMan]

please post your code.

sam