?
Solved

excel vba - code understanding

Posted on 2011-05-07
8
Medium Priority
?
330 Views
Last Modified: 2012-05-11
Hi,
Can any please explain the below code
specially rs.EOF()
.MoveFirst, Movenext - are they pointers?
it would good to get as much knowledge on this code as possible

Thanks
With rs
        If rs.EOF() Then .MoveFirst
        Do While Not .EOF()
            If .Fields("FirstName") = Range("E1").Value Then
                Range("C4").Value = .Fields("FirstName")
                Range("D4").Value = .Fields("LastName")
                Range("E4").Value = .Fields("Address")
                Exit Do
            End If
            
.MoveNext

        Loop
    End With

Open in new window

0
Comment
Question by:SameerMirza
  • 3
  • 3
  • 2
8 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35711789
EOF is a property that indicates whether or not the record pointer is beyond the end of the recordset. MoveFirst and MoveNext are methods that move the record pointer to the first or next record in the recordset.

Fields is a collection of the fields in the recordset. Use it to access the fields in the current record.

Kevin
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 35711791
Here is some information on how to use an ADODB connection.

The following text and sample code illustrates how to:

   -> open a database connection
   -> use a stored procedure to perform a query
   -> execute any SQL command against a database
   -> open a recordset using a custom query
   -> open a recordset using a table name
   -> check for an empty recordset
   -> read all records in a recordset
   -> add a record to a recordset
   -> delete a record from a recordset
   -> copy a recordset with headers to a worksheet
   -> close a recordset and database

This sample code, except for Open database method, can be used with any database such as Access, SQL Server, or Oracle. When using a database, most interaction happens via a recordset. Data is manipulated almost entirely using Recordset objects. Any number of Recordset objects can be created and used at the same time - each representing a different query or the same query. Different Recordset objects can access the same tables, queries, and fields without conflicting.

After opening a Recordset the Recordset can contain zero or more records. One record in the Recordset is always the current record except when the Recordset BOF or EOF property is true in which case no record is the current record. The current record is the record that is affected by any record-specific methods. To move amongst the records in a Recordset use the MoveNext, MovePrevious, MoveLast, and MoveFirst Recordset methods. A specific record can be made the current record by setting the AbsolutePosition property to the index number of the desired record. Fields in the current record are access as illustrated below.

   Value = MyRecordset!Field1
   MyRecordset!Field2 = Value + 1

When the current record is changed use the Update method to apply the changes to the database. Use the Add method to add a new record and the Delete method to delete the current record. The Add method can be used even if the query returns an empty recordset.

Caution about using the RecordCount method: The RecordCount for a serverside recordset may return a -1. This occurs with ActiveX Data Objects (ADO) version 2.0 or later when the CursorType is adOpenForwardonly or adOpenDynamic and when with ADO 1.5 only when the cursortype is adOpenForwardonly. To get around this problem use either adOpenKeyset or adOpenStatic as the CursorType for server side cursors or use a client side cursor. Client side cursors use only adOpenStatic for CursorTypes regardless of which CursorType is selected.

Before writing any ADODB code the data objects library "Microsoft ActiveX Data Objects x.x Library" must be referenced in the VBA project (Tools->References).

For additional information on the ADODB interface see the MSDN pages at:

   http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadoapireference.asp

Sample code:

   Dim MyDatabase As ADODB.Connection
   Dim MyCommand As ADODB.Command
   Dim MyRecordset As ADODB.RecordSet
   Dim Column As Long

   ' Open database connection
   Set MyDatabase = New ADODB.Connection
   MyDatabase.CursorLocation = adUseClient
   MyDatabase.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='C:\full\path\to\database.mdb'; User Id=admin; Password=;"
   ' For more information about Open syntax:
   '   http://msdn.microsoft.com/en-us/library/ms808201.aspx
   ' Additional help constructing connection strings can be found at http://www.connectionstrings.com/

   ' Query database using stored procedure (requires command object)
   Set MyCommand = New ADODB.Command
   Set MyCommand.ActiveConnection = MyDatabase
   MyCommand.CommandText = "qrySomeQuery" ' <- name of procedure
   MyCommand.CommandType = adCmdStoredProc
   With MyCommand
      .Parameters.Refresh
      .Parameters.Append .CreateParameter("QueryTextParam", adVarChar, adParamInput, 10, StringValue)
      .Parameters.Append .CreateParameter("QueryTextParam", adDouble, adParamInput, , DoubleValue)
      .Parameters.Append .CreateParameter("QueryLongParam", adBigInt, adParamInput, , LongValue)
      .Parameters.Append .CreateParameter("QueryDateParam", adDate, adParamInput, , DateValue)
      .Parameters.Append .CreateParameter("QueryDateTimeStampParam", adDBTimeStamp, adParamInput, , DateTimeValue)
      .Parameters.Append .CreateParameter("BooleanParam", adBoolean, adParamInput, , BooleanValue)
      ' For more information about CreateParameter syntax:
      '   http://msdn.microsoft.com/en-us/library/ms808298.aspx
   End With
   
   ' Open recordset using command object
   Set MyRecordset = New ADODB.Recordset
   MyRecordset.Open MyCommand, , adOpenDynamic, adLockPessimistic
   ' For more information about Open method syntax:
   '   http://msdn.microsoft.com/en-us/library/ms808656.aspx
   
   ' Build a custom query using command object
   Set MyCommand = New ADODB.Command
   With MyCommand
      Set .ActiveConnection = MyDatabase
      .CommandType = adCmdText
      .CommandText = "SELECT * From tblMyTable WHERE (tblMyTable.MyID = 1)"
   End With
   MyRecordSet.Open MyCommand, , adOpenDynamic, adLockReadOnly

   ' Execute any SQL statement
   MyDatabase.Execute "INSERT INTO TableName (Field1, Field2) VALUES ('" & Range("A1").Value & "','" & Range("A2").Value & "')"

   ' Open a recordset by specifying specific table (no query)
   MyRecordset.Open "TableName", MyDatabase, adOpenDynamic, adLockPessimistic
   
   ' Open a recordset by specifying query without using command object
   MyRecordset.Open "SELECT * FROM MyTable", MyDatabase, adOpenDynamic, adLockPessimistic

   ' Test for no records
   If MyRecordset.BOF And MyRecordset.EOF Then
      MsgBox "No records in table"
   End If

   ' Determine total records (see notes above about inconsistencies with the RecordCount method)
   MsgBox "Total records: " & MyRecordset.RecordCount
   
   ' Look at all records in record set
   While Not MyRecordset.EOF
      MsgBox "Record number: " & MyRecordset.AbsolutePosition
      MyRecordset.MoveNext
   Wend

   ' Find a specific record given a field value
   MyRecordset.MoveFirst
   MyRecordset.Find "ID='ABC123'"
   If Not MyRecordset.BOF And Not MyRecordset.EOF Then
      MyRecordset!Field1 = "Match Found"
      MyRecordset.Update
   End If

   ' Copy the entire recordset to a worksheet (this technique does not copy field names)
   Sheets("Sheet1").[A2].CopyFromRecordset MyRecordset
   
   ' Create headers and copy data
   With Sheets("Sheet1")
      For Column = 0 To MyRecordset.Fields.Count - 1
         .Cells(1, Column + 1).Value = MyRecordset.Fields(Column).Name
      Next
      .Range(.Cells(1, 1), .Cells(1, MyRecordset.Fields.Count)).Font.Bold = True
     .Cells(2, 1).CopyFromRecordset MyRecordset
   End With

   ' Update current record
   MyRecordset!Field1 = "Some data"
   MyRecordset!Field2 = "Some more data"
   MyRecordset.Update

   ' Move specific fields from current record to worksheet
   With Sheets("Sheet1")
      Cells(Row, "A") = MyRecordset!Field1
      Cells(Row, "B") = MyRecordset!Field2
   End With
   
   ' Add new record and set field values
   MyRecordset.AddNew
   MyRecordset!Field1 = "Some data"
   MyRecordset.Update

   ' Update an existing record or add it if it does not exist
   MyRecordset.MoveFirst
   MyRecordset.Find "Field1='"& SourceSheet.Cells(Row, "A") & "'"
   If MyRecordset.BOF Or MyRecordset.EOF Then
      MyRecordset.AddNew
   End If
   MyRecordset!Field1 = SourceSheet.Cells(Row, "A")
   MyRecordset!Field2 = SourceSheet.Cells(Row, "B")
   MyRecordset.Update
   
   ' Delete current record
   MyRecordset.Delete

   ' Close recordset
   MyRecordset.Close

   ' Close database
   MyDatabase.Close

Kevin
0
 

Author Comment

by:SameerMirza
ID: 35711814
thanks Kevin - its very helpful
I am very new to VBA
I can code but I need to clear my logics a little
So just a quick one
We can say,
'declaration
Dim firstName as string
'initialization
Set firstName = 'test'

and some time we even see
firstName = 'test'

I believe both statements work but how important is it to use 'Set' and when we must?

Thanks and sorry if it dosnt make any sense at all :)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35711822
Use the Set command when setting any object. When setting any non-object variable such as a string, long, double, date, etc., then don't use Set.

So this is incorrect:

   Set firstName = "Test"

This is correct:

   firstName = "Test"

Kevin
0
 

Author Closing Comment

by:SameerMirza
ID: 35711825
thanks :)
0
 
LVL 17

Expert Comment

by:wobbled
ID: 35711832
Here is a simple explanation of each part of the code

With rs

rs in this case will refer to the recordset.  Literally the list of records that have been return from the database (or other datastore)

        If rs.EOF() Then .MoveFirst

rs.EOF - if the recordset is at the last record in the set then move to the first record in the recordset

        Do While Not .EOF()

Do this as long as the record is not the last record (when it becomes the last record the loop will stop and everything in between here and the "Loop" word in the code is finished with

            If .Fields("FirstName") = Range("E1").Value Then

If the recordset field "FirstName" is the same as the value in cell E1 then do the following

                Range("C4").Value = .Fields("FirstName")

set cell C4 = the value in the recordset field "Firstname"

                Range("D4").Value = .Fields("LastName")

set cell D4 = the value in the recordset field "LastName"

                Range("E4").Value = .Fields("Address")

set cell E4 = the value in the recordset field "Address"

                Exit Do

Exit Do means that it will close of the Do While loop.  The code here is basically looping through the recordset,  If .Fields("FirstName") = Range("E1").Value is true then it will perform the set cell stuff explained above and exit out of the loop as is written to only run once the If statement is true.

            End If

closes If statement
           
.MoveNext

moves to the next record in the recordset

        Loop

Go back through the code from the Do While.... part (unless we have hit the Exit Do or we have reached the end of the recordset - ie looped through all the records)

    End With

Ends using rs.  The With RS command is just a more effecient way of using objects, so instead of having to type rs.movenext, you just type .movenext.  The code already knows that anything begining .keyword is associated to rs so it runs faster (there is abit more to this, but that is the basics)

Hope that makes it a bit clearer
0
 

Author Comment

by:SameerMirza
ID: 35711836
thanks alot wobbled
Unfortunatly I have already alocated the points but thanks
0
 
LVL 17

Expert Comment

by:wobbled
ID: 35711838
Don't worry about - hope it helps you get started in VBA :)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question