VBA run time error

Question

Run-time error on this code says 'Set Myrs=myqry.openrecordsheet() is the error, any ideas?

I have a button with this code:

Option Compare Database

Private Sub Command5_Click()

GetDataFromDate DateStart.Value, DateEnd.Value

End Sub

then I have vba with this:

Option Compare Database


Sub GetDataFromDate(dtStart As Date, dtEnd As Date)

 Dim MyDb As Database, MyQry As QueryDef, MyRS  As Recordset
 Set MyDb = CurrentDb()
 Set MyQry = MyDb.CreateQueryDef("")

 ' Type a connect string using the appropriate values for your
 ' server.
 MyQry.Connect = "ODBC;DSN=PRDDLPA1;UID=purch_edi1;PWD=useedi;DBQ=PRDDLPA1;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;"

 ' Set the SQL query with Date Range passed to the sub as parameters
 MyQry.SQL = "SELECT NDC_CODE, SUM(INVOICE_QTY) AS TOTAL_QTY, SUM(INVOICE_QTY*UNIT_PRICE) AS EXT_DOLLAR_TOTAL, ITEM_NBR, TO_CHAR(EDI_INVOICE_DT, 'YYYYMM') " & _
 "FROM EDI_INVOICE_DETAIL_LINE" & _
 "WHERE AP_VENDOR_NBR IN('   242081') AND NOT((EDI_INVOICE_TYPE = 'C') AND (UNIT_PRICE = 0))" & _
 "AND (EDI_INVOICE_DT BETWEEN #" & dtStart & "# AND #" & dtEnd & "# " & _
 "AND LOCATION_NBR NOT IN('88017',' 88003',' 88010',' 88011',' 88018',' 88012',' 88008',' 88006',' 88001',' 88007',' 88009',' 88004',' 88019')" & _
" GROUP BY NDC_CODE, ITEM_NBR, TO_CHAR(EDI_INVOICE_DT, 'YYYYMM')"

 MyQry.ReturnsRecords = True
 Set MyRS = MyQry.OpenRecordset()

 MyRS.MoveFirst

 If Not MyRS.BOF Then

    While Not MyRS.EOF

        Debug.Print MyRS!TO_CHAR(EDI_INVOICE_DT)
        Debug.Print MyRS!NDC_CODE
        Debug.Print MyRS!TOTAL_QTY
        Debug.Print MyRS!EXT_DOLLAR_TOTAL
        Debug.Print MyRS!ITEM_NBR


        MyRS.MoveNext
    Wend


 End If

 MyQry.Close
 MyRS.Close
 MyDb.Close

End Sub

How can I fix this so that I can update my pass through query via my form with a new date range?

[From Comment] The Original Query looks like:

SELECT NDC_CODE, SUM(INVOICE_QTY) AS TOTAL_QTY, SUM(INVOICE_QTY*UNIT_PRICE) AS EXT_DOLLAR_TOTAL, ITEM_NBR, TO_CHAR(EDI_INVOICE_DT, 'YYYYMM') FROM EDI_INVOICE_DETAIL_LINE WHERE AP_VENDOR_NBR IN(' 242081') AND NOT((EDI_INVOICE_TYPE = 'C') AND (UNIT_PRICE = 0)) AND (EDI_INVOICE_DT BETWEEN TO_DATE('03/01/2016', 'MM/DD/YYYY') AND TO_DATE('03/31/2016', 'MM/DD/YYYY')) AND LOCATION_NBR NOT IN('88017',' 88003',' 88010',' 88011',' 88018',' 88012',' 88008',' 88006',' 88001',' 88007',' 88009',' 88004',' 88019') GROUP BY NDC_CODE, ITEM_NBR, TO_CHAR(EDI_INVOICE_DT, 'YYYYMM')

What is the error message you receive? And have you double checked that your connection string is correct? — tyg
– tyg, Commented Apr 28, 2016 at 18:28
Run-time error '3146' and then it highlights "SET MyRS=MyQry.OpenRecordSet() //yes connection string is correct. — SQUISH
– SQUISH, Commented Apr 28, 2016 at 18:31
Ohhh...haha sorry, yes, it says ODBC -- call failed, this is odd because it works when i run the query in the DB by itself... — SQUISH
– SQUISH, Commented Apr 28, 2016 at 18:57
Access uses # characters to delimit date values. The database you're connecting to is not Access. So what characters does that database use to delimit date values? (Probably not #) — HansUp
– HansUp, Commented Apr 28, 2016 at 19:08

Gustav · Accepted Answer · 2016-05-02 13:48:08Z

1

Seems like you secretly connect to an Oracle database. Then date values must be formatted as yyyy-mm-dd string expressions:

"AND (EDI_INVOICE_DT BETWEEN '" & Format(dtStart, "yyyy-mm-dd") & "' AND '" & Format(dtEnd, "yyyy-mm-dd") & "' " & _

or try the other date format:

"AND (EDI_INVOICE_DT BETWEEN '" & Format(dtStart, "mm/dd/yyyy") & "' AND '" & Format(dtEnd, "mm/dd/yyyy") & "' " & _

edited May 2, 2016 at 13:48

answered Apr 29, 2016 at 7:20

Gustav

56.5k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

SQUISH Over a year ago

thanks, I tried this but I still get the same error. I'm new to VBA, what does " Set MyRS = MyQry.OpenRecordset()" mean?

Gustav Over a year ago

It means to open a recordset using MyQry.

SQUISH Over a year ago

so now I have the error 'method or data member not found'

Gustav Over a year ago

Try with a very basic select query like "SELECT NDC_CODE FROM EDI_INVOICE_DETAIL_LINE" and modify code until success. Then build up this query step by step, correcting errors you may encounter before proceeding with the next step.

Collectives™ on Stack Overflow

VBA run time error

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related