Dim Explanation Please

[gunnie]

Could some kind person explain the use of Dim in modules please.

 

[ziwacky]

Hi gunnie,

The Dim statement is one way of declaring variables in modules and procedures. Variables do not have to be declared beforehand (that is by omitting the "Option Explicit" statement at the beginning of the module), but if you're not careful enough not declaring a variable can lead you to logical errors in your code. Therefore it is highly recommended that you declare your variables. Dim is mostly used to declare local variables which means they are only available to be use in the current procedure where they are declared, but it can also be used in the Declarations section of the module (at the top) and it will be available for the entire module.

Another important note is that when declaring variables with the Dim statement you also specify the data type for your variable. Not doing so will default the data type to variant, and variant can hold almost any data type, but it requires a significant amount of storage space and are also slow because they must be resolved by the compiler at runtime.

Hope that explanation helps.

Z

 

[Magnetar]

Hi Gunnie!

The 'Dim' statement (short for 'Dimension', I believe!)is used in modules and procedures to declare, for example:

- the data types of variables
Eg 'Dim NoOfStudents As Integer'
(declaring a variable named NoOfStudents as an Integer);

- the object type of a variable
Eg 'Dim dbMyDatabase As Database'
(declaring a variable named dbMyDatabase as a database object);

- a variable array
Eg 'Dim strMyString(51 To 100) As String * 20
(declaring an array named strMyString which contains fixed-length strings which are 20 characters long, and contain 50 entries from 51 through to 100).

Basically, one needs to inform Visual Basic of the type of data that the variable will use, and allocate appropriate storage space in memory. This is usually implemented via the Dim statement.

Hope this has been helpful. Have a look at the 'Help' menu within your Access application, for a more indepth explanation.

Best wishes

Magnetar

 

[rccline]

How do I declare a field in my query as a variable?
And my current database as the record set?

Here is what I am trying, in MSAccess2000....and it isn't working:

Private Sub Command4_Click()
Dim Db As DAO.Database
Set Db = CurrentDb
Dim rst As DAO.Recordset
Dim fld As DAO.Field

Set rst = Db.OpenRecordset("NameOfMyDatabase", dbOpenForwardOnly, dbReadOnly)
While Not rst.EOF
'Open the database
'Set Db = DBEngine.OpenDatabase _
' (".\NAMEOFDB.mdb&quot
'Open the Recordset
Set rst = Db.OpenRecordset _
("SELECT * FROM MyTblName " &
"WHERE (MyFIELDNAME = 'AnyFieldName' &quot, _
dbOpenForwardOnly, dbReadOnly)


This is not working.

Thanks.

Robert

 

[Magnetar]

Afternoon rccline

Sorry for not getting back sooner!! (Only saw this reply recently!).

Had a quickie check on the help menu (under Access 97) - the following example may help:

Sub UKOrders()
Dim dbs As Database, rst As Recordset
[red]Dim strSQL As String[/red]

' Return reference to current database.
Set dbs = CurrentDb
[red]strSQL = "SELECT * FROM Orders WHERE [ShipCountry]= 'UK'"
Set rst = dbs.OpenRecordset(strSQL)[/red]
rst.MoveLast
Debug.Print rst.RecordCount
rst.Close
Set dbs = Nothing
End Sub

As stated above the query is declared as a string, which is then referenced within the RecordSet object.

Anyway, do take a look at this, - may help a little.

Best wishes

Magnetar

 

[RJFost]

If you're wanting to substitute a run-time entry for a query value, try something like this:

Dim Dbs As Database, Rst As Recordset
Dim TableName As String, FieldName As String, TargetVal as String '{or whatever it needs to be}
Dim SQL As String

TableName = {run-time entry [from control, prompt, etc.]}
FieldName = {ditto}
TargetVal = {still more of same}

SQL = "SELECT * FROM " & TableName & " " _
"WHERE " & FieldName & " = '" & TargetVal & "'"
'{or whatever your condition(s) is/are}