Still need help

  • Thread starter Thread starter brett
  • Start date Start date
B

brett

I did not have this set but at the same time, I am not
understanding this part. My goal is to have a VB code or
macro set the parameter/criteria/whatever of a certain
query field and then run the query; I know in the criteria
field that you can put a statement in brackets so it would
prompt but having user prompting when a query is run to
view a report is breaking some of my reports so I want
something to put a number in the query field and then have
the query run after the number is inserted. I'm starting
to see my only other alternative is to have code create
and delete a query but I'd rather not do that if I can
avoid it (setting a criteria value of a field would be
better). If I need to use the parameters dialog, that's
fine but I don't understand how that works. I guess the
overall question is can a macro or VB code set a criteria
value of a query field (the help documentation never
stated a query, it always stated a form).

Brett

-----------------------------------------------------------

In query design view, have you opened the Query
menu, selected Parameters and completed the
Parameters dialog (Access 2000)? You need to
do this to insert a Parameters clause.

To check whether you have or have not inserted
a parameters clause, in query design view, open
the View menu and select SQL view. You will see
your query as an SQL statement, which should
begin with the word Parameters.

I haven't seen your earlier posts, so hope this
addresses your issue.

Regards
Geoff
 
As you want to open a report using vb, limiting its
output, you do not want a parameter query.
Instead, use something like:

Dim lngNumber As Long

lngNumber = InputBox("Enter Number")

DoCmd.OpenReport ("MyReportName"), acViewPreview, , _
"MyFieldName = " & CStr(lngNumber)

Good luck!
Geoff

(PS As it seems you're posting new posts each time
on the same topic, Newsgroup readers can't easily see
your previous posts. Also people who respond to you
can't effectively mark your messages to watch out for
your further requests on the same topic. I saw your
further request by chance. It might be better to
repond within the thread of your original post?
Just a thought... Anyway, hope the above helps.
Regards, Geoff.)
 
My goal is to have a VB code or
macro set the parameter/criteria/whatever of a certain
query field and then run the query; I know in the criteria
field that you can put a statement in brackets so it would
prompt but having user prompting when a query is run to
view a report is breaking some of my reports so I want
something to put a number in the query field and then have
the query run after the number is inserted.
Click to expand...

The best way to do this depends on what you want to do with the query and
where the parameters are coming from.

You can use the parameters collection of the QueryDef oject, and then open
its recordset or execute it...

set qdf = QueryDefs("MyUpdateQuery")
qdf.Parameters("DateToStart") = CDate("2003-02-01") ' VBE bug
qdf.Parameters("NameOfUser") = "Eric"

qdf.Execute dbFailOnError

Alternatively, you can set up the parameters so that Access can read them
from values on a form

SELECT MyName, YourName, GlassType
FROM Nonsense
WHERE CrisisType = Forms!MyParameterForm!txtCrisis

which will fail if the form is not open, but works seamlessly if it is.

Finally, you can build the Recordsource string ad hoc and poke it into a
report etc

Private Sub Report_Open()

' create the sql and poke in the actual values of the
' parameters
strSQL = "SELECT .... " & _
"WHERE MandatoryUse = " & GetMandatoryValue() & _
" etc.... "

' set the recordsource and force it to update
Me.RecordSource = strSQL
Me.Requery


That is a bit of a short explanation, but you'll need to provide more
information about what you are trying to do in order to get more specific
advice.


By the way, Brett, I think I saw this in a previous thread: it's generally
helpful if you can keep responses within the same thread so that we can
check on what has been said already.


Best wishes


Tim F
 
Back
Top