Open Query and send parameter by code

  • Thread starter Thread starter Gina Meyer
  • Start date Start date
G

Gina Meyer

Hi.

I would need to send an ID to the query to be executed properly

The following code does somehow recognise the ID .
I really got terribly stuck :(

_____________________________________________
Dim stDocName As String

stDocName = "[ID]=" & "'" & Me![ID] & "'"
DoCmd.OpenQuery stDocName, acNormal, acEdit

MsgBox stDocName
________________________________________

I'd really appreciate if someone could please help me with a piece of code.

Thanks for taking time

Gina
 
From your code it looks like stDocName will end up to be something like:

"ID = 1234"

but then you are asking it to open a query by that name. I have my doubts
that you have a query by that name. You can probably accomplish what you
want most easily by simply referencing your text box in the query criteria
like this.

= Forms!YourFormName!ID
 
hi,
stdocName should be the name of the query to run.
you have the criteria.
try somehting like this
docmd.openquery "queryName", acnormal, acedit
the "[ID]=" & "'" & Me![ID] & "'" part is a vb way of
stateing criteria but in this case, i would suggest that
you put the criteria in the query itself. in the query's
id field's criteria box put
[Forms]![yourform]![ID]
 
Thanks for your fast answer

Well ... to do it on form level was my first attempt ...

Having the above problem, I tried various ways for the correct syntax but
ended always up being asked to type in the value for my query manually:

I tried the following criteria set in the query as param which gets called
on button click:

[Forms]![Cust].[frm_Car].[frm_Work].[frm_WorkDetails]![txtWorkID]

other varieties were:
[Forms]![Cust]![frm_Car]![frm_Work]![frm_WorkDetails]![txtWorkID]
[Forms]![frm_WorkDetails]![txtWorkID]

the control [txtWorkID], that does contain the actual value, is located on
frm_WorkDetails

when pressing the button I am only asked to type in the parameters for the
query - only then it works.
On earlier tries Access told me that it couldn't find the form ...
eventhough its loaded!!

I understand that a subform is part of its main container and obviously not
visible then ??
what is the correct syntax for the criteria in the query ?? How can I solve
my problem ??

Thought I'll better give it a try in VB now ....

Gina

Lynn Trapp said:
From your code it looks like stDocName will end up to be something like:

"ID = 1234"

but then you are asking it to open a query by that name. I have my doubts
that you have a query by that name. You can probably accomplish what you
want most easily by simply referencing your text box in the query criteria
like this.

= Forms!YourFormName!ID



--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Gina Meyer said:
Hi.

I would need to send an ID to the query to be executed properly

The following code does somehow recognise the ID .
I really got terribly stuck :(

_____________________________________________
Dim stDocName As String

stDocName = "[ID]=" & "'" & Me![ID] & "'"
DoCmd.OpenQuery stDocName, acNormal, acEdit

MsgBox stDocName
________________________________________

I'd really appreciate if someone could please help me with a piece of
code.

Thanks for taking time

Gina
Click to expand...
Click to expand...
 
Hi.

sorry .... fairly new to this access form stuff
I do not know the syntax for calling a query and sending a parameter at the
same time
is it the right way at all ??

? ? ?
_______

stFilter = Str(Me![ArbeitsID])

stDocName = "qry_expData"

DoCmd.ApplyFilter stDocName, stFilter
DoCmd.OpenQuery stDocName, acNormal, acEdit

_____

Gina

hi,
stdocName should be the name of the query to run.
you have the criteria.
try somehting like this
docmd.openquery "queryName", acnormal, acedit
the "[ID]=" & "'" & Me![ID] & "'" part is a vb way of
stateing criteria but in this case, i would suggest that
you put the criteria in the query itself. in the query's
id field's criteria box put
[Forms]![yourform]![ID]

-----Original Message-----
Hi.

I would need to send an ID to the query to be executed properly

The following code does somehow recognise the ID .
I really got terribly stuck :(

_____________________________________________
Dim stDocName As String

stDocName = "[ID]=" & "'" & Me![ID] & "'"
DoCmd.OpenQuery stDocName, acNormal, acEdit

MsgBox stDocName
________________________________________

I'd really appreciate if someone could please help me with a piece of code.

Thanks for taking time

Gina






.
Click to expand...
Click to expand...
 
What does the query do? Does it fill a recordset or does it update
data? Are you using DAO or ADO?
 
It selects data to fill my details form according to theWorkID

I copied the query, renamed it and the idea was to run a macro call the
param query with the value from workID so that the records can be exported
to excel

when I type in the value by hand it works

I am using access and so far I am not using VB .... don't know the answer to
your question
 
Found the correct syntax for the criteria to be put as param for the query
to execute correctly without having to type anything and it gets exported
properly

if anyone may have the same problem in the future:

[Forms]![_frmCust]![Car].[Form]![Work].[Form]![WorkDetails].[Form]![WorkID]

..... would have thought there would be a way via VB ... which is easier

Thanks to you for your time and advice


Gina
 
Back
Top