Ms-access – Learning MS Access

ms-access

i am new to ms-access, and i have started to look at code that works. unfortunately it is completely uncommented.

    Private Sub OKbut_Click()
Dim dt As Date
dt = Now()
Set rstOrder = New ADODB.Recordset
rstOrder.Open "tblUsers", CurrentProject.Connection, adOpenStatic, adLockOptimistic
If rstOrder.Supports(adAddNew) Then
    With rstOrder
        .AddNew
        .Fields("title") = title
        .Fields("first") = first
        .Fields("last") = last
        .Fields("gender") = gender
        .Fields("date_submitted") = dt
        .Update
    End With
End If
rstOrder.Close
Set rstOrder = Nothing
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT id FROM tblUsers WHERE date_submitted=#" & dt & "#")
duser = rs.Fields("id")
rs.Close
Set rs = Nothing
    Do While Not user_defined(duser)
        DoCmd.OpenForm "define_user_frm", , , , , acDialog
    Loop
'Forms(0).user_lst.RowSource = "select * from users where id=" & duser
Me.SetFocus
DoCmd.Close
End Sub
  1. what does the function Now() do?
  2. ADODB.Recordset is just a way to connect to a table?
  3. adOpenStatic, adLockOptimistic what are these?
  4. why are we checking this: if rstOrder.Supports(adAddNew) ?
  5. why do we need to do this: Set rstOrder = Nothing?
  6. what does this do:? Do While Not user_defined(duser)
    DoCmd.OpenForm "define_user_frm", , , , , acDialog
    Loop

Best Solution

  1. what does the function Now() do?

Now() returns the current system date and time

  1. ADODB.Recordset is just a way to connect to a table?

Yes - more importantly, it is a good way to iterate through records of a dataset individually. Like a cursor in SQL Server. You could do something like 

While not rstOrder.EOF
'a.k.a. while there are still records left to iterate through
   'Do something
End While
  1. adOpenStatic, adLockOptimistic what are these?

From http://www.dotnetspider.com/forum/16958-In-VB-What-difference-between-adopendynamic.aspx

adOpenForwardOnly - this is the default cursor if no other is specified. This cursor allows only forward movement through a recordset

adOpenKeyset - this cursor supports forwards as well as backwards navigation. It also allows you to update a recordset and all changes will be reflected in other users recordsets. The cursor also supports bookmarking

adOpenDynamic - this cursor supports forward and backward navigation but bookmarks may not be supported (ie Access). Any changes made to data are immediately visible with no need to resynchronise the cursor with the database

adOpenStatic - this cursor uses a static copy of data from the database and therefore no changes to the data are visible and supports forward and backward navigation

  1. why are we checking this: if rstOrder.Supports(adAddNew) ?

It's a way of writing more robust code - i.e. before attempting to add a new record, first check whether the recordset supports the addition of new records.

  1. why do we need to do this: Set rstOrder = Nothing?

Frees the recordset from memory. Not absolutely necessary but again, makes for more robust code.

  1. what does this do:? Do While Not user_defined(duser) DoCmd.OpenForm "define_user_frm", , , , , acDialog Loop

Checks for existence of a user and if it doesn't exist, it opens a form called "define_user_frm" which I assume allows for creating a new user.

Related Question