Solved Can't Assign a DAO RecordSet to Form Recordset Property

[ADIGA88]

Hi Guys,

I don't what I am missing I always assign a recordset to a form but now I am getting this error what I am missing.

The form is an unbound one.

 

[The_Doc_Man]

Well, unless you have strange naming convention for tables, is that the correct name of the table? Or should it be "tblVATGroup" ??

 

[sonic8]

I would guess, you need a Dynaset- or Snapshot-type recordset.

 

[LarryE]

Which line is giving the error? And if you're going to assign a forms Record Source as a defined recordset, then:
Me.RecordSource=rs.Clone

But I don't know why you are defining a recordset and then assigning it to a form RecordSource anyway. Just assign it directly as shown below:
Me.RecordSource="tblVATGroup"

 

[NauticalGent]

My bet is on the typo type recordset suggestion...Post #2 Post #3

 

[CJ_London]

You should be dimming rs as dao.recordset. not dao.recordset2 (which a sub recordset for multivalue fields)

and you don't appear to be dimming db1

and finally you don't need .clone (but .recordsetclone might work)

Not sure if it matters but I would look to use the load event rather than the open event

note that by assigning a recordset, you may need to write your own rightclick menu options (for sorting/filtering etc)

 

[ADIGA88]

Well, unless you have strange naming convention for tables, is that the correct name of the table? Or should it be "tblVATGroup" ??

the table name is tlbVATGroup the reference is right.

I would guess, you need a Dynaset- or Snapshot-type recordset.

Yes it is, now it's working thanks

Which line is giving the error? And if you're going to assign a forms Record Source as a defined recordset, then:
Me.RecordSource=rs.Clone

The Error came from Set Me.Recordset = rs.clone

But I don't know why you are defining a recordset and then assigning it to a form RecordSource anyway. Just assign it directly as shown below:
Me.RecordSource="tblVATGroup"

because the I am opening the tables from different database.
Set db1 = Workspaces(0).OpenDatabase(strDbPath, False, False, "MS Access;PWD=" & InputBox("Enter Database Passowrd"))

 

[gemma-the-husky]

what type is a Recordset2? Is that a thing?

 

[NauticalGent]

what type is a Recordset2? Is that a thing?

From CJL's response:

You should be dimming rs as dao.recordset. not dao.recordset2 (which a sub recordset for multivalue fields)

 

[theDBguy]

what type is a Recordset2? Is that a thing?

Here's a link to help explain it further. It's mostly used for MVF and Attachment fields.

Recordset2 object (DAO) | Microsoft Docs

 

[Pat Hartman]

Not clear at all why you are doing this. You can change the RecordSource property directly:

Me.RecordSource= "qSomeQueryDef"
Me.RecordSource= "Select .. From ... Where ..."
Me.RecordSource= "tblSometable"

 

[MajP]

You can change the recordset property directly:

From your example I believe you mean recordsource.
However, I agree that there is usually no reason to create your own recordset then bind it to the "unbound" form. Just provide the recordsource.

 

[Pat Hartman]

Thanks Maj, I fixed the code

 

[ADIGA88]

From your example I believe you mean recordsource.
However, I agree that there is usually no reason to create your own recordset then bind it to the "unbound" form. Just provide the recordsource.

I do it like this because I am running this app on a server with a runtime environment and it's modifying a backend Database, this gives me hard time but better from copying the backend to another device and copying it back (live users exist).

This application pass SQL commands to the back end (add fields, rename fields, etc...) and open the backend tables through forms to edit data directly (admin stuff).

 

[Pat Hartman]

Your explanation doesn't make sense. If the users use different drive mapping, then using UNC name to link to the database on the server will eliminate the problem and is just good practice anyway.

If your app is unbound as it seems to be based on your description, you should Not be using Access at all. Access is a RAD tool (Rapid Application Development). The biggest benefit of using such a tool is the things it does for you which in the case of Access is BOUND forms and reports. If you are not using the RAD features of Access, you are paying a heavy price to use it at all. A different platform would give you more flexibility.

Almost all of my applications use SQL Server or other RDBMS and all my applications use bound forms. Sometimes I create views to speed up joins. Sometimes I use pass-through queries for bulk deletes or updates. On a couple of occasions I've had to create stored procedures to create complex reports. But bound forms/reports and Access querydefs work fine even when my BE's have tables with millions of rows. It's all about understanding good client/server techniques and developing with those in mind.