Work with Form or Table. Which ?

[Middlemarch]

I've been reading that one should interact with the Form, not its table.
Usually I open a recordset on the table, do stuff, update it and refresh the Form.
If this is wrong, how do you manipulate a Form in the same way a table i.e. moving through it, going to a certain record etc?


I know Bookmark, but isn't that applied to a recordset ?

[Micron]

Users should not have access to tables, the exception sometimes being the developer who's testing relationships and queries prior to building forms.

The whole idea of using forms is so you can bind them to tables or queries (IMO, queries are better) and eliminate a ton of code. Why would you create recordsets when a bound form has it's own recordset - unless you must use unbound forms for some reason? Often, the supposed reasons are not valid.

For moving through forms, there are the Find methods (Find, FindFirst, FindNext, etc.) GoTo method and probably others that escape me at the moment - assuming for some reason you can't just use record navigation controls or scroll bars.
So many people tend to unnecessarily complicate things these days and I don't know why that is.

[Middlemarch]

@micron, the answer of course is we all have different levels of expertise, and use what we know works.
I've recently stopped creating rs and using recordset clones while working out which is best or more correct.
And I'm doing this in code so the scrollbar don't apply.
Could you give an expample of Goto method for a Form ? That is, if this doesn't involve using a recordset ?

[Micron]

It usually does involve at least a rs clone so I guess I can't. However, that is the case even if the form is bound.

My comments were about unbound vs bound forms. A form whose recordsource would be from (for example) a DAO recordset is unbound and the records can only be manipulated via code - code that you don't need if the form is bound. If that's not what you meant by

Usually I open a recordset on the table, do stuff, update it and refresh the Form.

then I guess I'm not following. If you need to requery the form after doing that, it implies that the form is not bound to that table (or query) but that its record source is a recordset based on that table/query. That just sound like an unnecessary and complicating layer to me.

[Middlemarch]

My forms are (monstly) bound. I'd open a recordset with some sql to get data from a specific record/field.
This is from the table. Is this what you advise not to do (Users should not have access to tables)?

So instead "Set rx = Form_frmMain.RecordsetClone" - is this NOT from the table and thus preferred?
But then you need to filter rx to get the wanted record/field. Or maybe bookmark.

The data may then be updated and I refresh the Form to immediately see the change. This mightn't be necessary, but I figure it does no harm.

[ArviLaanemets]

An example how to do this.

I have an Access app where is a form fmDevices, with tblDevices as Record Source;
On this form, I have an unbound combo box control cbbSelectDevice, to locate a record in table tblDevices, and activate this record in form fmDevices. As the combo is unbound, the control source of combo is empty. The Record Source of combo is a query:

SELECT a.DeviceID, a.DeviceID & "; " & Trim(Nz(b.LastName,"") & " " & Nz(b.ForeName,"")) & "; " & Trim(Nz(a.Producer,"") & " " & IIf(Nz(a.Mark,"")="",Nz(a.Model,""),a.Mark)) AS DeviceInfo FROM tblDevices AS a, tblUsers AS b WHERE (((a.CurrUL)=Forms!fmMain!sfDeviceGrouping!cbbUserLevel Or (a.CurrUL)<IIf(Forms!fmMain!sfDeviceGrouping!cbbUserLevel=99,89,0)) And ((b.TabN)=a.CurrUser) And ((Left(a.DeviceID,2))=Forms!fmMain!sfDeviceGrouping!txtDevGroup)) ORDER BY 2;

(The form fDevices is a source of subform sfDevices, which is a control of another form, which is used to limit the list of devices available to certain group of devices - because this the query the combo is based on is quite complex. User sees 2nd column of query in combo, the width of 1st column - which is the bound one - is set to 0.);
The AfterUpdate event of combo runs a VBA procedure (NB! My advice is, use VBA procedures instead of macros - you have more control about what you do then!):

Private Sub cbbSelectDevice_AfterUpdate()    ' Find the record that matches the control.
    Dim rs As Object


    Set rs = Me.Recordset.Clone
    rs.FindFirst "[DeviceID] = '" & Me![cbbSelectDevice] & "'"
    Me.Bookmark = rs.Bookmark
End Sub

NB! The DeviceID PK in this table is a text field - because this apostrophes are used, to enclose the combo value in procedure. In case numeric ID is used (e.g. autonumeric one), those must be omitted!

[Micron]

I'd open a recordset with some sql to get data from a specific record/field.

That is what DLookup is for?

is this NOT from the table and thus preferred?

That is not what is meant by giving users access to tables. It means being able to open from the nav pane.