Passing Paramater

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I posted a similiar question friday but didn't want to respond to it in fear of no one checking it. So I am reposting a better question?

I am trying to pass a paramater from one form to another's record source. The second form (receiving the paramater) is built using the record source property in the QBE grid.

When data is inputted into a text box an After Update event is triggered and the VBA creates a string to a hidden text box on the form.

Here is the code I use to assign the string to the text box
Private Sub JobName_AfterUpdate()
If IsNull(Me.JobName) Then
'Me.txtJobName = "Like " & Chr(34) & "*" & Chr(34) & " " & "Or j_JobName Is Null"
Me.txtJobName = "Like [Forms]![SearchForm]![txtJobName] Or Is Null"
Else
Me.txtJobName = "*" & Me.JobName & "*"
End If
End Sub

The string in the text box is Like "*" Or Is Null So when Like "*" or Is Null is passed to form1 record source I get no results. But if I copy Like "*" Or is Null to the receiving form in the criteria box in the QBE grid of the record source it works just fine.

How does Access read this string and how can I pass it so Access can understand it.

Also, I noticed if I put Like in the criteria box and pass * then I get all the results but if I pall Like * then I get nothing. Please help me understand!

Thanks

If you need more information please let me know.
 
Here is the code I use to assign the string to the text box

Private Sub JobName_AfterUpdate()
If IsNull(Me.JobName) Then
'Me.txtJobName = "Like " & Chr(34) & "*" & Chr(34) & " " & _
"Or j_JobName Is Null"
Me.txtJobName = "Like [Forms]![SearchForm]![txtJobName] Or Is Null"

Else
Me.txtJobName = "*" & Me.JobName & "*"

End If

End Sub
Click to expand...

Okay: the first thing I would do if you want to use hidden text boxes, is
to make the text box visible. At least that way you can tell what is being
passed. In this case, you are going to get,

Like Derek OR j_JobName Is Null

whereas what you want is

j_JobName LIKE "*Derek*" OR j_JobName IS NULL

Your first attempt is closer, but you do need the full syntax. Try
something like

Me!txtJobName = "j_JobName LIKE ""*" & Me!JobName.Value & "*"" OR " & _
"j_JobName IS NULL"

By the way, I am a little bit confused by your object names. You have a
text box called JobName and one called txtJobName and a field called
j_JobName. You might like to reconsider this -- for our sake if not for
your own!!
How does Access read this string and how can I pass it so Access can
understand it.
Click to expand...

It has to be everything in the WHERE clause of the SQL statement, without
the WHERE word itself.

Also, I noticed if I put Like in the criteria box and pass * then I
get all the results but if I pall Like * then I get nothing. Please
help me understand!
Click to expand...

One gets you LIKE "*" (correct) and the other one is LIKE * (wrong, and
should raise a data type error.

Hope that helps


Tim F
 
Back
Top