Parameter Qry using VBA

[tuktuk]

hey there,

ive used this code in many modules before but the .Execute line is getting hung up I believe due to a NULL Set (qry Returns no record to append). is that assumpiton correct

here is hte code:

Public Sub Append400DatatoAccessTbl()
DoCmd.SetWarnings False

Dim rsAgntKey As Recordset
Dim x As Integer
Dim SQLT As String
Dim strAgntkey As String

Dim flds As DAO.Fields
Dim fld As DAO.Field

Set rsAgntKey = CurrentDb.OpenRecordset("qry_AgntKey_Parameter")

'this defines the desired field in the qry to be the parameter
Set flds = rsAgntKey.Fields
Set fld = flds("AGTKEY")

With rsAgntKey
    .MoveFirst
    
Do While Not .EOF
 strAgntkey = fld

Const QRY_APPEND = "qry_TxnsTbl_Metrcs_AgntKey_Parmd"
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
'this is the append query which ontain the parameter to gether necessary info
Set qd = db.QueryDefs("qry_TxnsTbl_Metrcs_AgntKey_Parmd")
qd.Parameters(0) = strAgntkey
qd.Execute
rsAgntKey.MoveNext
Loop
  End With

Set rsAgntKey = Nothing

Set fld = Nothing
Set flds = Nothing

DoCmd.SetWarnings True

End Sub

more specifically, if the results for the string, strAgntkey are null then the execution line cannot complete the append.

thanks in advance.

IS there a way to use the IF(IsNull.....??????

aslo, the thing i do not understand is that if the Results are Zero and the Warnings are off wouldn't i just be appending ZERO records?
T

 

[Joe4]

Maybe you could check the query to see if there are any records in it before running the EXECUTE line, i.e.

IF DCOUNT("*","qry_TxnsTbl_Metrcs_AgntKey_Parmd")>0 THEN
    qd.Execute
END IF

That way if there are no records, it will stick the EXECUTE line.

 

[tuktuk]

hmmm well

i get an run time error 3417

an action query cannot be use as a row source.

i should state that the qry that i am passing a parameter is an append qry.

t

 

[Joe4]

A few ideas:

- You could create an exact copy of your Append Query, but as a Select
Query instead and use that in your checking.

- What happens if you put the line:
On Error Resume Next
before your Execute line? That might just ignore that error and proceed with your code.

After the Execute statement, you would want to set it back with:
On Error Goto 0

 

[tuktuk]

alright the ON ERROR still caused it to hnag up....ie no result.

so as suggested i created a select qry.....keep in mind i still am passing a [parameter].

get this....in the design of hte qry (not executed from the module) if i run the qry w [parameter] in the criteria line....i'm prompted to enter in the vlaue in the msg box that pops up.....after doing so....click run.....it get hung up.

if i remove [parameter] and enter in the same value as i entered in the msg box prompt in the criteria line.....it return the results almost instantaneously....keep in mind it is a null dataset but nonotheless it is returning a result.

does this mean passing a parrameter that returns a null set is not doable?

 

[tuktuk]

not sure if this matter but i thought i'd give a bit more info...

the parameter is from a table with the datatype set as a number.

if i run the qry specifiying the parameter, the criteria line contains no quotations. it is simply the numbers (ex. 145767) in the criteria line rather than "145767".

if i run more than one if would be In(145767,145768)

 

[Joe4]

I am not 100% certain I follow exactly what you are doing or why. I am not sure if you have a separate query just for the parameters that you are trying to apply to your Append Query?

Might I suggest a somewhat different method?
How about using VBA to build the SQL you need "on-the-fly"? I do this regularly, where I have an Unbound Form where the user enters their criteria, and then based on the criteria, use VBA to build the SQL for the query you want to run.

For an example, see this post here: http://www.mrexcel.com/forum/showpost.php?p=1619906&postcount=4

 

[tuktuk]

sorry about the double posting.....thought of it as a different issue and didn't want to confuse folks.

so in short this what i am doing....

i have a tbl with a list of agntkey.....from there i was trying to use a parameter qry to apppend to a table as i loop thru the list of agntkey.

the issue was that it seems to not complete if the parameter value eneter returned a null set. there for i was recommended to make another copy of the append qry, but make it a select qry and use the DCOUNT fcn to deteremine if it was a null set.

here was the code i've used for that, which appears to now be working:

Public Sub Append400DatatoAccessTbl()
DoCmd.SetWarnings False

Dim rsAgntKey As Recordset
Dim x As Integer
Dim SQLT As String
Dim strAgntkey As String

Dim flds As DAO.Fields
Dim fld As DAO.Field

Set rsAgntKey = CurrentDb.OpenRecordset("qry_AgntKey_Parameter")

'this defines the desired field in the qry to be the parameter
Set flds = rsAgntKey.Fields
Set fld = flds("AGTKEY")

With rsAgntKey
    .MoveFirst
    
Do While Not .EOF
 strAgntkey = fld

DoCmd.SetWarnings False

Const QRY_APPEND = "qry_TxnsTbl_Metrcs_AgntKey_Parmd"
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
'this is the append query which ontain the parameter to gether necessary info
Set qd = db.QueryDefs("qry_TxnsTbl_Metrcs_AgntKey_Parmd")
qd.Parameters(0) = strAgntkey


'''**********''COUNT RECORDSET TO ONLY APPEND IF NOT NULL SET


Dim SQL As String

SQL = "SELECT DW300PDLIB_DWTXNP01.RECPRNBKEY, DW300PDLIB_DWTXNP01.RECAGTKEY " & vbCrLf & _
"FROM DW300PDLIB_DWTXNP01 " & vbCrLf & _
"GROUP BY DW300PDLIB_DWTXNP01.RECPRNBKEY, DW300PDLIB_DWTXNP01.RECAGTKEY " & vbCrLf & _
"HAVING (((DW300PDLIB_DWTXNP01.RECAGTKEY)=' & strAgntkey & '));"

Dim RS As New ADODB.Recordset
Call RS.Open(SQL, CurrentProject.Connection)

If RS.EOF = False Then
RS.Close

'append if not a null recordset

qd.Execute
Else
RS.Close
End If

rsAgntKey.MoveNext

Loop
  End With

Set rsAgntKey = Nothing
Set RS = Nothing

Set fld = Nothing
Set flds = Nothing

DoCmd.SetWarnings True

End Sub

now the other isssue was that my append qry is getting hung up. more specifically if i enter a vaule (agntkey) in the criteria line of the append qry it completes the append. if i set a parameter in the append qry on agntkey, run, then enter the same agntkey in the parameter prompt msg box it does not complete the append.

this is what i believe is causing the

qd.Execute

in the above Sub to get hung up.

Thanks for your help.
Tuk

 

[Joe4]

I'll have to see if I can recreate your situation when I am at home tonight. Do you have the ability to post a small data sample and example to assist me in that matter?

 

[tuktuk]

i'm gonna try your suggested route by Stringin the SQL statement. Sounds like that might work.....not to mention that sounds like some thing i can use for future projects as well. i sure do like being introduced to new methods.