0

I am doing an Access project as a newbie and i am facing some problems (have to do all in VBA). In few words i have to show a query results on a form. I have few text boxes and i need to show some info. I understood that i have to change the RecordSource to the form and that i have to set the RecordSet for each text-box. Here is my code but is not working:

 Private Sub Form_open(Cancel As Integer)
    Dim strSQL As String, queryDef As DAO.queryDef
    strSQL = " *SELECT etc...* "
    CurrentDb.QueryDefs.Delete "Info"
    Set queryDef = CurrentDb.CreateQueryDef("Info", strSQL)
    Me.RecordSource = queryDef.OpenRecordset() '**error here type mismatch

How can i change my RecordSource to a query that is created when open the form? (Tried already with Me.RecordSet = strSQL but is not working with INNER JOIN)
Thank you a lot!

Improve this question
1
  • Try Me.Form.RecordSet = queryDef.OpenRecordset(). Commented Sep 3, 2019 at 23:11

1 Answer 1

Reset to default
1

You just mix up the forms recordsource and the forms recordset.

The recordsourceMe.RecordSourceis a string, that contains a table/query-name or a sql select statement.

Me.RecordSource = strSQL

A recordset is an object. Because of that, it has to be set

Set Me.RecordSet = queryDef.OpenRecordSet

Avoid the multiple usage ofCurrentDb,store it in a variable.

Everytime it is used, a new copy of the database-instance (usuallyDbEngine(0).Workspace(0)) is created, what consumes unnecessary resources.

Some useful links on that topic:

Private Sub Form_Open(Cancel As Integer)
    Dim db as DAO.Database
    Dim strSQL As String, queryDef As DAO.queryDef
    strSQL = " *SELECT etc...* "
    Set db = CurrentDb

    Set queryDef = db.CreateQueryDef(vbNullString, strSQL) ' vbNullstring is "" but typo-safe. If you create a querydef with an empty name, it is just temporary and will get lost after querydef is terminated
    Set Me.RecordSet = queryDef.OpenRecordset()

Or just using the recordsource:

Private Sub Form_Open(Cancel As Integer)
    Dim strSQL As String
    strSQL = " *SELECT etc...* "
    Me.RecordSource = strSQL
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

thank you very much! But why complex queries aren't working with the second choise ( = strSQL). Like it works with queries from one table but not with joins. It saying something as can' t find ...\table\ .mdb
Error in sql statement for sure. Debug.Print strSQL before assigining to recordsource and copy and paste the string into a new query (sql-view). This will show you the error when you try datasheet-view.
Avoid the multiple usage ofCurrentDb,store it in a variable. Everytime it is used, a new copy of the database-instance is created, what consumes unnecessary resources. :O never heard of that, but it's very interesting. Any source or documentation I can learn more about it?
@FoxfireAndBurnsAndBurns Read The CurrentDb() Function. Never failed on a tabledef (missing parent)?.Use it in loop and compare execution time to stored reference. I always use the CurrentDbCproperty, as shown in link.
Is there any difference in setting the forms recordsource vs its recordset? Thanks

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.