[AccessD] Access 101 on VBA coding syntax

Darryl:  It looks like the difference between what you are doing and moving a form to a record via a recordsetclone bookmark is that that your form is populated just in time whereas the recordsetclone approach requires the form to pull all the records in a conventional table/query record source.  Both approaches are valid and each has specific advantages.  Your approach negates the need for a form record selector and navigation buttons and prevents the possiblilty of continuous forms.  In your case, you would set the form cycle to single record and you've got a nice single record recordset that loads with minimal network traffic.  The alternative takes a one time hit to load more records and allows users to navigate to adjacent, first, last and new records.  If users tend to keep a form open and use an application to move between records regularly, it makes some sense to take the initial load hit.  If users want to move to adjacent records sorted by some query, the recordsetclone approach is your better approach.  Most of these decisions depend on how users will ultimately work with an application.
 
Personally, I tend to use an approach similar to yours for forms that are intended to display single records and have found that it requies less memory (where those huge recordsets are stored) and LAN traffic (one time big hit vs multiple smaller hits that may ultimately result in total greater traffic yet spread out the access) resulting in an application better able to support more users.  I don't run the requery method as setting the recordsource already forces the requery.  This avoids hitting a backend twice when only one hit is necessary.  Also, I tend to use a non-existent record for the blank record, usually an ID llike -1, which is defined as a global constant (BLANKRECORD).  I would not check whether 'Is Null = True' but simply for 'Is Null'.  I also like to disable the controls when on a blank record so no one can ever start entering data into the blank record.  I've found in the past that my blank records had garbage in them until I preempted the possibility.  By the way, I ordinarily use a bound form and set the opening recordsource to an empty record that does not retrieve an index or otherwise hit a server database.  An alternative to that is to place an opaque box over the display controls so you don't see the #Name error should you set the record source to a local single record dummy table or usys or msys table and then you don't need to disable the controls in the blank record.
 
How do you handle adding new records?  I use the search combo not in list event to prompt the user to confirm addition of new records based on the combo entry with these kinds of forms.  Perhaps you set the data entry mode for the form?CiaoJürgen WelzEdmonton, Albertajwelz at hotmail.com> Date: Wed, 23 Apr 2008 09:40:25 +1000> From: Darryl.Collins at coles.com.au> To: accessd at databaseadvisors.com> Subject: [AccessD] Access 101 on VBA coding syntax> > > Hi everyone,> > This might sound really dumb but I want to get it clear in my head. Been reading the Access 2002 Desktop Dev handbook and it seems nothing I have done in the past has been anything like how it should have been done. Despite this the stuff I have built seems to work fine (well, most of the time anyway). That said, none of it has been enterprise standard and/or rocket science.> > For example, If I have a form based on a table or query, and I want to pull a record for a specific client/whatever I use this sort of code. Basically have an unbound combobox which retrieves the Client ID and then create a recordset based on a query using the Client ID as a parameter ra ra ra ....> > ' --------- start code -------------> Sub ClientMainFormSetup()> > ' Error traps removed to make reading easier...> > Dim sSQL As String> Dim lCLIENT_ID As Long> > If IsNull([Forms]![frm_ClientsMain].cbChooseClient.Value) = True Then> lCLIENT_ID = 331 > ' Load Blank Client Record> Else> lCLIENT_ID = [Forms]![frm_ClientsMain].cbChooseClient.Value> ' Load Actual client record> End If> > sSQL = "SELECT * FROM tbl_Clients WHERE ClientID = " & lCLIENT_ID> > With [Forms]![frm_ClientsMain]> .RecordSource = sSQL> .[frm_subReturnFamilyID].[Form].Requery> End With> > ' Call other modules here etc> > End Sub> '------ End code ---------------> > This is pretty much how I do lots of stuff in Access - basic stuff it seems. However in the Access Dev book I see none of this sort of coding and lots of the following way of doing stuff. > > ' --------- start code -------------> Dim db as DAO.database> Dim rs as DAO.recordset> > Set db = Currentdb()> set rs = db.OpenRecordset(SELECT * FROM tbl_Clients WHERE ClientID = " & lCLIENT_ID)> ' --------- End code -------------> > and stuff with manipulating RecordsetClones etc.> > Now I believe the folks who wrote the book know exactly what they are talking about and I know very little. So my question is this, are both approaches valid? or does one work ok, and the other work much better? or is it just a difference in style and makes no difference?> > Looking at this I realise I have a HUGE amount to learn. hmmmmm...> > thanks for you thoughts> Darryl.
_________________________________________________________________
Find hidden words, unscramble celebrity names, or try the ultimate crossword puzzle with Live Search Games. Play now!
http://g.msn.ca/ca55/212