Form Data Advice (1 Viewer)

[MattBaldry]

Hi Forumers,

I have been reading many posts and viewing much code on here and notice the Recordset is mentioned alot.

If I have a form that cannot move between records, is there any benefit in declaring the recordset?

My frmQuote is based only on the tblQuote, and cannot move next/previous. To open a different quote to view, the form must be closed and then re-opened from a list box doubleclick. I have the below codes to edit a quote or create a new quote.

Add New - DoCmd.OpenForm "frmQuote", , , , acAdd
Edit Existing - DoCmd.OpenForm "frmQuote", acNormal, "", "[QuoteID]=Forms![frmCustomerOverview]![lstCustomerQuoteList] ", acEdit, acNormal

Would I benefit from declaring the recordset for a single record?

Does anyone have a link to a good guide to recordsets, I have looked around but not found anything that really explains it well to me, but I am sure it is something I should learn.

~Matt

 

[Mike Krailo]

If I have a form that cannot move between records, is there any benefit in declaring the recordset?

Would I benefit from declaring the recordset for a single record?

So is it a form that allows you to add a new quote, or just look at one selected quote? Adding a single quote record has nothing to do with recordsets per say. So its not clear what your trying to accomplish. If you want to jump to a specific quote record and still be able to scroll through other quotes, that can be done using bookmarks using the general code below.

Dim StrCrit As String
Dim rst As Recordset
If Nz(TempVars!MyID) > 0 Then
   StrCrit = "MyID = " & TempVars!MyID
   Set rst = Me.RecordsetClone
   rst.FindFirst StrCrit
   If Not rst.NoMatch Then
      Me.Bookmark = rst.Bookmark
   End If
   rst.Close
   Set rst = Nothing
   TempVars.Remove ("MyID")
End If

 

[Pat Hartman]

Your quote form is bound to a recordset. A Recordset is the result of running a query. It contains the rows selected by the query. The query may be an actual query in the RecordSource property, the name of a saved querydef, or the name of a table. Tabes are never opened directly, Access creates a hidden query which is essentially - Select * from YourTable. It is the least efficient way to retrieve data because it selects all rows and all columns. The best way is to create a saved querydef that selects only the needed columns and has a where clause that limits the number of rows returned to the minimum possible - preferably 1 row for the main form.

You also create recordsets by using the dao command .OpenRecordset which you then loop through and process using VBA.

All the code you didn't write and don't see (because it was written by the elves on the Access dev team at Microsoft) manages the Recordset bound to your form. You have to have written code to layout, populate and manage navigation for the equivalent of a form in a different environment to appreciate what Access is doing for you behind the scenes and you didn't have to write a single line of code.

So, in summary, recordsets contain the data selected by a query and are bound to forms and reports using the RecordSource property or are created by DAO or ADO and processed manually by VBA that you write.

 

[June7]

And for adding new record.

DoCmd.OpenForm "frmQuote"
DoCmd.GotoRecord acDataForm, "frmQuote", acNewRec

 

[MattBaldry]

Your quote form is bound to a recordset. A Recordset is the result of running a query. It contains the rows selected by the query. The query may be an actual query in the RecordSource property, the name of a saved querydef, or the name of a table. Tabes are never opened directly, Access creates a hidden query which is essentially - Select * from YourTable. It is the least efficient way to retrieve data because it selects all rows and all columns. The best way is to create a saved querydef that selects only the needed columns and has a where clause that limits the number of rows returned to the minimum possible - preferably 1 row for the main form.

You also create recordsets by using the dao command .OpenRecordset which you then loop through and process using VBA.

All the code you didn't write and don't see (because it was written by the elves on the Access dev team at Microsoft) manages the Recordset bound to your form. You have to have written code to layout, populate and manage navigation for the equivalent of a form in a different environment to appreciate what Access is doing for you behind the scenes and you didn't have to write a single line of code.

So, in summary, recordsets contain the data selected by a query and are bound to forms and reports using the RecordSource property or are created by DAO or ADO and processed manually by VBA that you write.

Thanks Pat,

That explains it perfectly for me. My next question then would be, is it better to have the form Record Source based on a query or a table with a filter in the DoCmd.OpenForm code?

~Matt

 

[MattBaldry]

And for adding new record.

DoCmd.OpenForm "frmQuote"
DoCmd.GotoRecord acDataForm, "frmQuote", acNewRec

Hi June,

Could you let me know the difference between your code and the DoCmd.OpenForm "frmQuote", , , , acAdd I am currently using?

Whenever I create a new record on any form, I use the DoCmd.OpenForm acAdd method. Should I be changing this?

~Matt

 

[Pat Hartman]

If you read between the lines of my answer, you will never bind a form to a table again You will always use a query with a WHERE clause as the RecordSource. In your case, the WHERE could be:

Where [QuoteID]=Forms![frmCustomerOverview]![lstCustomerQuoteList]

Then, the OpenForm method would be:

DoCmd.OpenForm "frmQuote", acNormal, , , acEdit, acNormal

 

[MattBaldry]

So is it a form that allows you to add a new quote, or just look at one selected quote? Adding a single quote record has nothing to do with recordsets per say. So its not clear what your trying to accomplish. If you want to jump to a specific quote record and still be able to scroll through other quotes, that can be done using bookmarks using the general code below.

Dim StrCrit As String
Dim rst As Recordset
If Nz(TempVars!MyID) > 0 Then
   StrCrit = "MyID = " & TempVars!MyID
   Set rst = Me.RecordsetClone
   rst.FindFirst StrCrit
   If Not rst.NoMatch Then
      Me.Bookmark = rst.Bookmark
   End If
   rst.Close
   Set rst = Nothing
   TempVars.Remove ("MyID")
End If

Hi Mike

My form does not move between records. You can add a new one, or view an existing one selected from a list box.

I did once have forms allowing movement between records, but the number of people that changed the wrong quotes was astounding.

As Pat has now confirmed I already have the recordsource correct, I think all my initial questions have been answered.

~Matt

 

[Mike Krailo]

I did once have forms allowing movement between records, but the number of people that changed the wrong quotes was astounding.

I'm curious, how do you then prevent the user from changing the wrong quote? Are you forcing them to select only the latest active quote or something like that? Or are all the other quotes just read only and not editable?

 

[MattBaldry]

If you read between the lines of my answer, you will never bind a form to a table again You will always use a query with a WHERE clause as the RecordSource. In your case, the WHERE could be:

Where [QuoteID]=Forms![frmCustomerOverview]![lstCustomerQuoteList]

Then, the OpenForm method would be:

DoCmd.OpenForm "frmQuote", acNormal, , , acEdit, acNormal

Thanks Pat,

I understand, so my next question would be, what if I have different forms that have a quote list to open a quote from? Would it benefit me to create a TempVars of the QuoteID from whichever form is open, and then have Where [QuoteID]=TempVars!SelectedQuoteID

So on my DoubleClick code it would load the QuoteID to the TempVars!SelectedQuoteID and then open the form based on the query.

And, hopefully I am understanding this right, if there is no SelectedQuoteID, it would open a new record?

At the moment there are 3 forms that have a quote list. CustomerOverview, QuoteList and UserDashboard.

CustomerOverview has only that customers quotes. QuoteList has the last months (filterable by dates) quotes, UserDashboard has the quotes assigned to that user.

Hopefully I am making sense. I am trying to streamline and improve my work and you guys blow my mind with what you know, so where better to learn

~Matt

 

[MattBaldry]

I'm curious, how do you then prevent the user from changing the wrong quote? Are you forcing them to select only the latest active quote or something like that? Or are all the other quotes just read only and not editable?

Hi Mike,

All the quotes are displayed in a list depending on which form they have open.

They double click the one they want to view/edit, and that is the one it opens.

~Matt

 

[Mike Krailo]

They double click the one they want to view/edit, and that is the one it opens.

So then, there are no wrong quotes in the list? That will work. I was thinking they could click on any quote in the whole table anyways but you have limited the list of initial selections that they can make. Perfect.

 

[Pat Hartman]

No, If you do what I told you to do with the query bound to the form's Recordsource, you do not need TempVars.
Yes, if there is no quote, the form will open to a new record. To handle new records, you have to use the BeforeInsert event and the OpenArgs. You should also pass in the CustomerID using the OpenArgs. NEVER dirty a record until the user has typed something. This just causes confusion and can result in empty records being created if you don't have proper validation in the form's BeforeUpdate event.

Me.CustomerID = Me.OpenArgs

If you want the user to just pick the CustomerID once he is on the new record, forget what I said about the BeforeInsert and OpenArgs.

I use the same technique for all forms. Either the calling form has the ID I want the form to open to or the form itself has a control that is used to select a record. Just FYI, most of my BE's are SQL Server and even when the BE doesn't start out as SQL Server, I still build the app as if it might be converted in the future. This makes the app take advantage of the power of the server. It also means that I can convert any app I built to SQL server in an afternoon and it only takes that long because it needs to be thouroughly tested. Binding forms to tables or to queries with no where clause, causes Access to ask the server to send down ALL rows from the table which defeats any efficiency you might obtain by using SQL Server.

 

[MattBaldry]

So then, there are no wrong quotes in the list? That will work. I was thinking they could click on any quote in the whole table anyways but you have limited the list of initial selections that they can make. Perfect.

I have tried to reduce the number of potential records someone could edit in error. Sadly idiot's will always find a way to idiot. At least this way they can only idot 1 record by mistake. My next step is to lock the record to the person whoe created it, and anyone trying to edit it would need permission from the creator before any changes are saved.

~Matt

 

[MattBaldry]

No, If you do what I told you to do with the query bound to the form's Recordsource, you do not need TempVars.
Yes, if there is no quote, the form will open to a new record. To handle new records, you have to use the BeforeInsert event and the OpenArgs. You should also pass in the CustomerID using the OpenArgs. NEVER dirty a record until the user has typed something. This just causes confusion and can result in empty records being created if you don't have proper validation in the form's BeforeUpdate event.

Me.CustomerID = Me.OpenArgs

If you want the user to just pick the CustomerID once he is on the new record, forget what I said about the BeforeInsert and OpenArgs.

I use the same technique for all forms. Either the calling form has the ID I want the form to open to or the form itself has a control that is used to select a record. Just FYI, most of my BE's are SQL Server and even when the BE doesn't start out as SQL Server, I still build the app as if it might be converted in the future. This makes the app take advantage of the power of the server. It also means that I can convert any app I built to SQL server in an afternoon and it only takes that long because it needs to be thouroughly tested. Binding forms to tables or to queries with no where clause, causes Access to ask the server to send down ALL rows from the table which defeats any efficiency you might obtain by using SQL Server.

Thanks Pat,

I need to learn the OpenArgs now then. Do you have any useful guides to this? I am very intrigued now.

~Matt

 

[Pat Hartman]

OpenArgs is an argument of the OpenForm and OpenReport methods. You can use it to pass data to the Form/Report you are opening. Take a look at the help entry for more information.

 

[June7]

Your original code opens so only new records are allowed. My suggestion was to set focus on new record row and allow navigation. I thought you wanted to allow navigation. Apparently I misunderstood.

If you want to restrict record edit to creator, will need field in table to store user ID.

I never use dynamic parameterized queries and I bind forms to tables.

 

[Pat Hartman]

I never use dynamic parameterized queries and I bind forms to tables.

Binding a form or report to a table or to a query without criteria works fine for Jet/ACE because Access the RAD tool is tightly integrated with Jet and ACE the desktop database engines. That model defeats the purpose of making the server do the heavy lifting when the BE is SQL Server. You really don't want the entire table brought down from the server to memory on the local PC each time you open a form. It is far better to bring down only the record you want to work with. For myself, rather than having two separate development models - one for Jet/ACE BE's and a different one for "real" RDBMS', I use the RDBMS model regardless of the BE. Access works quite well with either when the BE is Jet/ACE but it works better using query model to severely restrict network traffic when the BE is SQL Server.

Querydefs are static. They are compiled and the execution plans are saved the first time the query def runs and reused when the querydef runs subsequently. Dynamic SQL is what we create with VBA. The query must be compiled and the execution plan created each time the query runs. With older versions of Access, this was a much bigger deal than it is with current versions although it still makes a measurable difference if the query is being run inside a code loop.