[AccessD] Access 101 on VBA coding syntax

Hi Darryl,

The two examples you are giving do two different things.  In the first you
are deciding what to use as the recordset for a form.  In the second you are
creating a recordset to get data that you can use to do 'something'.

Both are correct for what their purpose.

The Access Developer's Handbook is one of the best.  I'm keeping mine!

Learn from the book - but you're already doing fine!

Dan



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins
Sent: Tuesday, April 22, 2008 6:40 PM
To: Access Developers discussion and problem solving
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.


This email and any attachments may contain privileged and confidential
information and are intended for the named addressee only. If you have
received this e-mail in error, please notify the sender and delete this
e-mail immediately. Any confidentiality, privilege or copyright is not
waived or lost because this e-mail has been sent to you in error. It is your
responsibility to check this e-mail and any attachments for viruses.  No
warranty is made that this material is free from computer virus or any other
defect or error.  Any loss/damage incurred by using this material is not the
sender's responsibility.  The sender's entire liability will be limited to
resupplying the material.

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com