parameterized update query

  • Thread starter Thread starter SME
  • Start date Start date
S

SME

I have an update query with several fields whice are
updated with data which is retrieved via a parameter
referencing another field in the same query
(i.e., "Field1" is updated with the contents of
[FieldA], "Field2" with [FieldB], etc. etc.)

I also have the "PlacementID" field which has the
criteria of [myPlacementID]. I would like to supply
the "PlacementID" criteria via VBA, but so far I've been
unable to supply the syntax which will execute the update
query the this specific criteria ... I am getting either
a collection not found or need more parameters errors.
I've trolled through the newsgroup and have tried all the
below syntax (and other more esoteric variations).

The gfrm_placementNo is a form level variable which is
usually updated on the forms on current event. If the
value doesn't exist then it is created and then assigned
to gfrm_placementNo via a combo box selection just prior
to the update query being called. Both gfrm_placementNo
and "PlacementID" are of a Long variable type.

Any help with this would be greatly appreciated.

Thanks. /S


Dim dbs As Database
Dim qdf As QueryDef

Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs
("qryUpdateInternsPlacementAgencyInformation")

' All the below have been used with no successes
'qdf.Parameters(myPlacementID) = gfrm_placementNo
'qdf.parameters("[myPlacementID]") = gfrm_placementNo
'qdf.Parameters("myPlacementID") = gfrm_placementNo
'qdf![myPlacementID] = gfrm_placementNo
'qdf.Parameters![myPlacementID] = gfrm_placementNo

qdf.Execute , dbFailOnError

qdf.Close
Set qdf = Nothing
dbs.Close
Set dbs = Nothing
 
-----Original Message-----
I have an update query with several fields whice are
updated with data which is retrieved via a parameter
referencing another field in the same query
(i.e., "Field1" is updated with the contents of
[FieldA], "Field2" with [FieldB], etc. etc.)

I also have the "PlacementID" field which has the
criteria of [myPlacementID]. I would like to supply
the "PlacementID" criteria via VBA, but so far I've been
unable to supply the syntax which will execute the update
query the this specific criteria ... I am getting either
a collection not found or need more parameters errors.
I've trolled through the newsgroup and have tried all the
below syntax (and other more esoteric variations).

The gfrm_placementNo is a form level variable which is
usually updated on the forms on current event. If the
value doesn't exist then it is created and then assigned
to gfrm_placementNo via a combo box selection just prior
to the update query being called. Both gfrm_placementNo
and "PlacementID" are of a Long variable type.

Any help with this would be greatly appreciated.

Thanks. /S


Dim dbs As Database
Dim qdf As QueryDef

Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs
("qryUpdateInternsPlacementAgencyInformation")

' All the below have been used with no successes
'qdf.Parameters(myPlacementID) = gfrm_placementNo
'qdf.parameters("[myPlacementID]") = gfrm_placementNo
'qdf.Parameters("myPlacementID") = gfrm_placementNo
'qdf![myPlacementID] = gfrm_placementNo
'qdf.Parameters![myPlacementID] = gfrm_placementNo

qdf.Execute , dbFailOnError

qdf.Close
Set qdf = Nothing
dbs.Close
Set dbs = Nothing

.
Click to expand...
Hi SME,

this is just a guess... in the design view of your query,
have you added the parameter(s) that are used in query in
the parameters list found in the menu, query|parameters?

Luck
Jonathan
 
Back
Top