Parameter Qry using VBA

tuktuk

Well-known Member
Joined
Nov 13, 2006
Messages
856
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:

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
 
Last edited:
Hi first I tought maybe to clean up the code to make it more readable.
But looking at what it does, it seems like it can be substituted by a single SQL statement.

As I already rewrote the code I'll still place it, I'm not sure if you can execute a querdef with parameters this way. I think that you need to have something like rs = qdf.openrecordset to have the parameters effectuated. (but maybe I'm wrong)

Code:
Public Sub Append400DatatoAccessTbl()
DoCmd.SetWarnings False
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rsAgntKey As DAO.Recordset
Dim sAgntKeys() As String
Dim lCnt As Long
Const QRY_APPEND = "qry_TxnsTbl_Metrcs_AgntKey_Parmd"
Set db = CurrentDb
Set rsAgntKey = db.OpenRecordset("qry_AgntKey_Parameter")
With rsAgntKey
    .MoveFirst
    ReDim sAgntKeys(lCnt)
         Do Until .EOF
            ReDim Preserve sAgntKeys(lCnt)
            sAgntKeys(lCnt) = FixNull(.Fields("AGTKEY").Value)
            lCnt = lCnt + 1
          .MoveNext
        Loop
     .Close
End With
For lCnt = 0 To UBound(sAgntKeys)
    If Not sAgntKeys(lCnt) = "NoVal" Then
        Set qdf = db.QueryDefs(QRY_APPEND)
        qdf.Parameters(0) = sAgntKeys(lCnt)
        qdf.Execute
    End If
Next lCnt
Set sAgntKeys = Nothing
DoCmd.SetWarnings True
End Sub

Code:
 Public Function FixNull(vValue As Variant) As String
If IsNull(vValue) Then
        FixNull= "NoVal"
Else
        FixNull= CStr(vValue)
End If
End Function

But as I said, you can replace this with a single SQL statement. Should look something like this:

Code:
Insert Into TargetTable (Field1, Field2, Fieldn) 
Select Field1, Field2, Fieldn From SourceTable
Where SourceKey IN (Select Key  From TableWithKey Where Not IsNull(Key))
 
Last edited:
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.
I see that Kreszch68 post a reply for you also. If you can't get either idea to work for you, post back and let us know (a small sample would help - I am a very visual person!).
 
Upvote 0
Apart from that you might use a single SQL statement, I have put together a routine that will work correctly with the qDef and Params.

Note that I declared a Parameter object, it is not mandatory, but it will give a better understanding of what the code does.
Then I declared the ParamValueList as a variant, so it can hold NULL values from the AgntKey recordset, which can be tested in the next step to avoid passing a NULL value to the qdef.
Code:
Public Sub TestExecute()
Dim dbs As DAO.Database
Dim rsAgntKey As DAO.Recordset
Dim qDef As DAO.QueryDef
Dim ParamAgntKey As DAO.Parameter
Dim ParamValueList() As Variant
Dim lCnt As Long
Const RST_AgntKey As String = "qry_AgntKey_Parameter"
Const QRY_Append As String = "qry_TxnsTbl_Metrcs_AgntKey_Parmd"
Const PARAM_Name As String = "[B][COLOR=red]TheNameOfYourParameter"[/COLOR][/B]
Set dbs = CurrentDb
Set qDef = dbs.QueryDefs(QRY_Append)
Set ParamAgntKey = qDef.Parameters(PARAM_Name)
Set rsAgntKey = dbs.OpenRecordset(RST_AgntKey)
With rsAgntKey
.MoveLast
ReDim ParamValueList(.RecordCount - 1)
.MoveFirst
For lCnt = 0 To UBound(ParamValueList)
ParamValueList(lCnt) = .Fields("AGTKEY").Value
.MoveNext
Next lCnt
.Close
End With
For lCnt = 0 To UBound(ParamValueList)
If Not IsNull(ParamValueList(lCnt)) Then
ParamAgntKey.Value = ParamValueList(lCnt)
qDef.Execute (ParamAgntKey)
End If
Next lCnt
'Destroy objects
Set rsAgntKey = Nothing
Set ParamAgntKey = Nothing
Set dbs = Nothing
End Sub
 
Upvote 0
Some post, but initially I forgot the code tags, so same code, but better for reading

Code:
Public Sub TestExecute()
Dim dbs As DAO.Database
Dim rsAgntKey As DAO.Recordset
Dim qDef As DAO.QueryDef
Dim ParamAgntKey As DAO.Parameter
Dim ParamValueList() As Variant
Dim lCnt As Long
Const RST_AgntKey As String = "qry_AgntKey_Parameter"
Const QRY_Append As String = "qry_TxnsTbl_Metrcs_AgntKey_Parmd"
Const PARAM_Name As String = [B][COLOR=red]"TheNameOfYourParameter"[/COLOR][/B]
Set dbs = CurrentDb
Set qDef = dbs.QueryDefs(QRY_Append)
Set ParamAgntKey = qDef.Parameters(PARAM_Name)
Set rsAgntKey = dbs.OpenRecordset(RST_AgntKey)
With rsAgntKey
        .MoveLast
        ReDim ParamValueList(.RecordCount - 1)
        .MoveFirst
            For lCnt = 0 To UBound(ParamValueList)
                ParamValueList(lCnt) = .Fields("AGTKEY").Value
                .MoveNext
            Next lCnt
        .Close
End With
For lCnt = 0 To UBound(ParamValueList)
        If Not IsNull(ParamValueList(lCnt)) Then
            ParamAgntKey.Value = ParamValueList(lCnt)
            qDef.Execute (ParamAgntKey)
        End If
Next lCnt
'Destroy objects
Set rsAgntKey = Nothing
Set ParamAgntKey = Nothing
Set dbs = Nothing
End Sub
 
Upvote 0
hey there....i'm continuing on an older post.

In efforts to increase speed i thought I'd try the SQL statement suggestion but i'm having a compile error.

here is my SQL Statement:
(my issue is in the HAVING portion. also when do you use HAVING and when do you use WHERE?)

Code:
strSql = "INSERT INTO tbl_ACE_Monthly_Metrics ( TYPE, [Date], [Year], PAYDTEKEY, PAYAGTKEY, RECPRDKEY, SumOfVOLUME, SumOfRCPUSD, SumOfCHGBASUSD, SumOfNETFX ) " & vbCrLf & _
"SELECT ""PAYS"" AS TYPE, Format([PAYDTEKEY]+35061,""yyyymm"") AS [DATE], Format([PAYDTEKEY]+35061,""yyyy"") AS [YEAR], DW300PDLIB_DWTXNP01.PAYDTEKEY, DW300PDLIB_DWTXNP01.PAYAGTKEY, DW300PDLIB_DWTXNP01.PAYPRDKEY, Sum(DW300PDLIB_DWTXNP01.VOLUME) AS SumOfVOLUME, Sum(DW300PDLIB_DWTXNP01.RCPUSD) AS SumOfRCPUSD, Sum(DW300PDLIB_DWTXNP01.CHGBASUSD) AS SumOfCHGBASUSD, Sum(DW300PDLIB_DWTXNP01.NETFX) AS SumOfNETFX " & vbCrLf & _
"FROM DW300PDLIB_DWTXNP01 " & vbCrLf & _
"GROUP BY ""PAYS"", Format([PAYDTEKEY]+35061,""yyyymm""), Format([PAYDTEKEY]+35061,""yyyy""), DW300PDLIB_DWTXNP01.PAYDTEKEY, DW300PDLIB_DWTXNP01.PAYAGTKEY, DW300PDLIB_DWTXNP01.PAYPRDKEY " & vbCrLf & _
"HAVING (((DW300PDLIB_DWTXNP01.PAYDTEKEY)>5847) AND ((DW300PDLIB_DWTXNP01.PAYAGTKEY) = ((SELECT [2_tbl_ACE_Added_Agent_IDs].AGTKEY FROM 2_tbl_ACE_Added_Agent_IDs WHERE [2_tbl_ACE_Added_Agent_IDs].AGTKEY) Is Not Null)));"

thanks
tuk
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top