MS ACCESS vba - error

Question

I'm creating a MS Access report. I came across a small issue and I have no idea where it's going wrong. So what I'm trying to do is

1) Select everything from a query (query requires "TO" and "FROM" dates. I pass these values to the frmX which then gets referenced in the query). When i run a query by itself with the frmX open - it runs fine. 2) Im trying to change some values in the data 3) Insert the new values into tempTable1

Here's my code:

dim rs1 as DAO.Recordset
dim rs2 as DAO.Recordset
CurrentDb.Execute "DELETE FROM [tempProvider-Detail]"

'Repopulating temp table
DoCmd.OpenQuery "qryProvider-FINAL"

'Input Source
Set rs1 = CurrentDb.OpenRecordset("Select * from [qryProvider-Final]", , dbOpenSnapshot)

'Target Source
Set rs2 = CurrentDb.OpenRecordset("Select * from tempProvider-DETAIL", dbOpenDynamic)

What's interesting here is that it does not hang up on DoCMD.OpenQuery - however when I get to set rs1...... then it tells me that it expects 2 parameters. I don't know why - since the query already opened - and it works fine when I try opening it by itself it opens (with dates in frmX that i reference in the query).

Please help me out!

So I did this as Heinzi helped me.. still getting same error What is wrong??????

DoCmd.OpenQuery "qryProvider-FINAL"

Set qdf = CurrentDb.QueryDefs("qryProvider-FINAL")
qdf.Parameters(0) = [Forms]![frmX]![txtFrom]
qdf.Parameters(1) = [Forms]![frmX]![txtTo]
Set rs1 = qdf.OpenRecordset

strSQL = "SELECT * FROM [qryProvider-FINAL];"

'Input Source
Set rs1 = CurrentDb.OpenRecordset(strSQL, , dbOpenSnapshot) ---this is where it hangs up

Almost there. Just remove the two last lines of your current example (you have already set rs1 in line 6, no need to open it again). — Heinzi
– Heinzi, Commented Apr 29, 2016 at 15:12
@Heinzi -- ok so I stop after set rs1 = qdf.openRecordset ? but I want to make sure I have all data selected! — FatBoySlim7
– FatBoySlim7, Commented Apr 29, 2016 at 15:15
I need to make sure it's all selected - as I'm going to move through the recordset and make edits as I go. — FatBoySlim7
– FatBoySlim7, Commented Apr 29, 2016 at 15:16
@Heinzi is right. rs1 is opened, so it makes no sense trying to open it again (even in the wrong way). The records are retrieved, there's nothing to "make sure". If you insist, loop through the recordset. — Gustav
– Gustav, Commented Apr 29, 2016 at 15:34
Why do you think qdf.OpenRecordset will not return all data? — Heinzi
– Heinzi, Commented Apr 29, 2016 at 15:41

Darren Bartrup-Cook · Accepted Answer · 2016-04-29 15:38:20Z

2

Would this work:

Sub Test()

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim prm As DAO.Parameter

    Set db = CurrentDb
    Set qdf = db.QueryDefs("qryProvider-FINAL")
    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next prm
    Set rst = qdf.OpenRecordset

End Sub

answered Apr 29, 2016 at 15:38

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

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

1 Comment

FatBoySlim7 Over a year ago

Thanks, @Heinzi helped me out. I will save this for future use!

Heinzi · Accepted Answer · 2016-04-29 14:49:15Z

1

You cannot reference form controls when opening a recordset with CurrentDb.OpenRecordset. It's just not supported. Details can be found in the following MSDN article:

Everything About Using Parameters from Code

The answer is that youre invoking the Jet engine in a different context here, and that makes all the difference. When you get data from a parameter query that uses a form to supply the parameter via the Access user interface, as in the earlier example, Access can evalute the expression involved and supply a value to Jet. When you get data from a parameter query that uses a form to supply the parameter via VBA, instead of through a form, the bits of Access that manage user interface matters arent involved. Consequently, Jet is passed the string "[Forms]![frmSelectCountry]![cboCountry]" instead of the value in cboCountry. Because Jet doesnt know how to evaluate the expression, it cant open the recordset.

answered Apr 29, 2016 at 14:49

Heinzi

173k61 gold badges386 silver badges554 bronze badges

2 Comments

FatBoySlim7 Over a year ago

@ heinzi what if instead i do something like dim strSQL as string strSQL = "Select * from [qry];" and then set rs1=currentdbo.openrecordset(strSQL,,dbopensnapshot)

Heinzi Over a year ago

@FatBoySlim7: Won't make a difference. You need to provide the parameter values in code, as described in the article, if you need to open the recordset in code.

Collectives™ on Stack Overflow

MS ACCESS vba - error

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related