CODEWITHSUNDEEP
sqlvbams-access
RacmanT
RacmanT

Reputation: 53

Query results on Access form (VBA)

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!

Upvotes: 0

Views: 986

Answers (1)

ComputerVersteher
ComputerVersteher

Reputation: 2696

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

Upvotes: 1

Related Questions