Solved Open a form using just VBA

[Rich77]

Just out of curiosity:
A form is usually based on, for example, a table or a query. You then enter the name of that table or query on the second tab (in this case “Qry_004_Settings_Admin”) and the form will then retrieve the records when opened.
Question:
Is it also possible to NOT enter “Qry_004_Settings_Admin”, but to retrieve this via VBA?

 

[Pat Hartman]

Is it also possible to NOT enter “Qry_004_Settings_Admin”, but to retrieve this via VBA?

You can do that but why? Why would you not want to use a bound form? It is the best feature of Access.

Welcome

 

[LarryE]

Using the forms On Load event
Me.RecordSource = “Qry_004_Settings_Admin”
Me.Requery

 

[Gasman]

My understanding was when you set a recordsource, you are in effect carrying out a requery?, so the Me.Requery is not needed?

 

[theDBguy]

Hi. Welcome to AWF!

I think there's also this approach.

Me.Recordset = rs

Not sure, though, if that's good enough for what you want to do.

PS. Correction:

Set Me.Recordset = rs

Thanks, @cheekybuddha for the reminder.

 

[The_Doc_Man]

There are strategies involving external SQL engines such as SQL Server or ORACLE, where you would want the form to open quickly and offer you some options (such as filters) before loading the form's data. Deferring the definition of the .RecordSource until run-time is one way to accomplish that. It is not the only method, but it would surely work for that purpose. If the form uses a "native Access" back-end and involves a large file, similar reasons (i.e. fast open) might apply if we are talking in the hundreds of thousands of records. If the native Access form involves small numbers of records, then I would wonder why you would bother unless you have security issues.

The ONLY other reason I can imagine for WHY someone might do this is because of a very seriously non-normalized design of multiple identical tables that should have been combined and normalized.

If you were planning as an academic exercise to do some kind of table manipulation NOT using Access features, that might also be viable if rather tedious. If this is for some other reason than any I've mentioned, I would have to echo Pat's comments: WHY would you do such an awkward thing?

 

[cheekybuddha]

Me.Recordset = rs

You may have to use the Set keyword, since a recordset is an object.

 

[theDBguy]

You may have to use the Set keyword, since a recordset is an object.

Sorry, I forgot.

Thanks!

 

[Rich77]

Thank you all for trying to help me, but I can't get it to work... and... I'm just curious if it works. I know there are better methods.
The answers confuse me a bit. What should be the complete code for "on load"?
I want to run the query “Qry_004_Settings_Admin” while calling the form.

Private Sub Form_Load()
Set Me.Recordset = rs
ExecuteSQL = “Qry_004_Settings_Admin” <-----???? (rubbish)
Me.Requery
End Sub

 

[Gasman]

Private Sub Form_Load()
    Me.RecordSource = "tbldates"
    Me.cboDates = DLookup("StepsID", "tblDates", "StepsDate = " & Format(Date, strcJetDate))
    Me.Recordset.FindFirst "StepsID = " & Me.cboDates
End Sub

You need to understand any code posted.

Your attempt is
Make the form's recordset equal to recordset object rs.
There is no such object, not even dimmed?
Next line you are correct, it is , not even correct syntax
I was led to understand that when a recordset is populated, that is in effect a requery, as the data is refreshed. However that is not a novice issue, but a waste of resources.

However unless you are loading a different source depending on certain conditions, I do not see the point?

 

[isladogs]

You could have a subform, set its source object then set the record source of the subform at Form_Load

Private Sub Form_Load()

        Me.SubFormControlName.SourceObject ="YourSubFormName"

        Me.SubFormControlName.Form.RecordSource="SELECT * FROM YourQueryName;"
        Me.SubFormControlName.Requery  'this line may not be needed
     
End Sub

I use this approach for my own navigation type forms where I need to change the record source of the subform.
However, I really can't see the point of doing this in your case.

EDIT: Added .Form to fix error in RecordSource line

 

[cheekybuddha]

I'm just curious if it works.

Try it more like:

Private Sub Form_Load()

  Dim rs As DAO.Recordset, strSQL As String
 
  strSQL = "SELECT * FROM Qry_004_Settings_Admin;"
' or just:
'  strSQL = "Qry_004_Settings_Admin"
  Set rs = CurrentDb.OpenRecordset(strSQL)
  Set Me.Recordset = rs

End Sub

 

[cheekybuddha]

Private Sub Form_Load()

        Me.SubFormControlName.SourceObject ="YourSubFormName"

        Me.SubFormControlName.RecordSource="SELECT * FROM YourQueryName;"
        Me.SubFormControlName.Requery  'this line may not be needed
     
End Sub

@Colin, can you access the .RecordSource and .Requery directly through the subform control, or do you need to go via the .Form property?

 

[Rich77]

You could have a subform, set its source object then set the record source of the subform at Form_Load

Private Sub Form_Load()

        Me.SubFormControlName.SourceObject ="YourSubFormName"

        Me.SubFormControlName.RecordSource="SELECT * FROM YourQueryName;"
        Me.SubFormControlName.Requery  'this line may not be needed
      
End Sub

I use this approach for my own navigation type forms where I need to change the record source of the subform.
However, I really can't see the point of doing this in your case.

Hi Colin,

Thank you very much for your input!!! This might help me in some other cases, I'll remember this solution.
In my case however my subform is not connected to a form so "SubFormControlName" will not work.
My "Sub"-form is in fact a form, oeps.
Later on, when it is working proper, I'll send the solution to this forum.

Regards Richard

 

[cheekybuddha]

In my case however my subform is not connected to a form so "SubFormControlName" will not work.
My "Sub"-form is in fact a form, oeps.

This does not make sense!

To have a subform on a form, it resides in a subform control. Whether it is connected to the main form via LinkMasterFields/LinkChildFoelds property is immaterial.

To refer to the subform in the subform control you must reference via the SubformControlName.Form property of the main form.

 

[Rich77]

Try it more like:

Private Sub Form_Load()

  Dim rs As DAO.Recordset, strSQL As String

  strSQL = "SELECT * FROM Qry_004_Settings_Admin;"
' or just:
'  strSQL = "Qry_004_Settings_Admin"
  Set rs = CurrentDb.OpenRecordset(strSQL)
  Set Me.Recordset = rs

End Sub

GREAT this works fine. You make me very happy,Yes!!!

Regards Richard

 

[Rich77]

Again, thank you all for your input.

Regards Richard

 

[cheekybuddha]

GREAT this works fine. You make me very happy,Yes!!!

That's great!

As long as you are aware that it's much more straightforward to do the equivalent:

Private Sub Form_Load()

  Me.RecordSource = "Qry_004_Settings_Admin"

End Sub

Also, did you see my post #15?

 

[isladogs]

@Colin, can you access the .RecordSource and .Requery directly through the subform control, or do you need to go via the .Form property?

Hi
Thanks for the prompt. .Form needed for the RecordSource but not for the Requery
I've corrected the code

 

[cheekybuddha]

.Form needed for the RecordSource but not for the Requery

Interesting that it's not required for the .Requery

I never knew that it implemented that method for the control directly.

I guess calling via .Form.Requery wouldn't hurt in any case!