Defining a recordset in VBA (Access).

[doyapore]

Dear All,
The following piece of code is an extract from an application which I am
developing at the moment.
*************** Code Begins ******************
Public Sub Proc_FamilyRelation(parCardSlNo As String)
On Error GoTo Err_Msg
Dim dbslocal As Object
Dim rstDTE As Object
Set dbslocal = CurrentDb
Set rstDTE = dbslocal.openrecordset("DTE")

Dim vrFamilyRelation As String

With rstDTE
.Index = "CARD_SLNO"
.Seek "=", parCardSlNo
If .NoMatch Then
lblFamilyRelation.Caption = "No relation exists in tabled
information"
Beep
GoTo Eject_Sub
End If
vrFamilyRelation = !Relation
If vrFamilyRelation = Null Then
lblFamilyRelation.Caption = "Relationship undefined in tabled
information"
Else
lblFamilyRelation.Caption = "Relationship : " + vrFamilyRelation
End If
End With
GoTo Eject_Sub

Err_Msg:
MsgBox Err.Description, , "Procedural error"
MsgBox Err.Number, , "Procedural error"
Resume Next

Eject_Sub:
rstDTE.Close
dbslocal.Close
End Sub
***************** code ends *****************

I have noticed that if a DIM statement defining a variable is placed before
the DIM statements which define the database and the recordset as objects, I
get an error. The error(s) is generated around the .Index verb.
My question is whether this is part of the VBA syntax. Can someone shed
somelight in this context?
Your suggestions and advice are always highly appreciated and prized.
Thank you.

 

[Van T. Dinh]

* There is no specific order for the Dim statements. I personally prefer
to dim all variables at the beginning of the Procedure but I have seen other
codes with Dim statements just before the Variables are used.

* It may be more efficient to use early binding in this case, i.e. change
the Dims to:

Dim dbslocal As DAO.Database
Dim rstDTE As DAO.Recordset

* Note that you use Seek in this code which will require the Table (?) "DTE"
to be a local Table, not a linked Table. You will get an error if "DTE" is
a linked Table.

* Since it is possible to assign Null to Variable "vrFamilyRelation" from
your code, you will need to Dim this as a Variant rather than a Variable. A
String Variable can only accept a String value and not a Null. You will get
error 94 if you try to assign a Null value to a String Variable.

 

[doyapore]

Thank you for your reply. I have the following observations to make, which I
shall place before you for your comments.
1. It would be very logical to accept that there is no specific order for
the DIM statements.
2. The "DTE" is a local table and not a linked one.
3. The code on the form verifies that the variable "vrFamilyRelation"
does not contain NULL.
4. None of the fields in the mentioned "DTE" local table contains any
NULL and/or any empty values and/or blanks in any of the records.
5. Keeping view of the points # 2 to 4, however, I still have observed
that it becomes necessary to define/declare the database and the recordset
objects before declaring/defining the variables using DIM statements;
alternatively, I start receiving errors which are related to:
a. .Index
b. .Seek

[Interestingly, in another PC which runs Access 2003, I have had no such
problems. The PC which I have been using for development, has Access 2002
loaded. The finished application would be ultimately deployed on Access 2002
environment.]

What is the advantage of using early binding as you have suggested in your
post? How does it any way differ from declaring the database and recordset
as "Objects"?

Your suggestions and advice are always highly appreciated and prized.
Thank you.

 

[Tom Wickerath]

Hello Doyapore,

I have noticed that if a DIM statement defining a variable is
placed before the DIM statements which define the database
and the recordset as objects, I get an error.

I just tried your code, using Access 2002 SP-3, and it works fine on my PC. My test included
creating a local table named DTE, with three fields: parCardSlNo (Text, Primary Key), CARD_SLNO
(Text) and Relation (Text). I created a form bound to this table, and included a label named
lblFamilyRelation, a textbox named txtString and a command button named Command3. The click
event for the command button is as follows:

Private Sub Command3_Click()
Dim strCard As String

strCard = Me.txtString
Proc_FamilyRelation (strCard)

End Sub


My testing includes declaring the variable vrFamilyRelation before declaring the objects, as in
the following:

Public Sub Proc_FamilyRelation(parCardSlNo As String)
On Error GoTo Err_Msg

Dim vrFamilyRelation As String
Dim dbslocal As Object
Dim rstDTE As Object
:
:
etc.
End Sub

Anyway, it works fine on my PC. Don't know if this matters in this particular case, but what
service packs do you have applied to Access and to the JET database engine?

What is the advantage of using early binding as you have suggested
in your post?

1.) Slightly faster code execution
2.) Intellisense
3.) Compile time error checking

An advantage of using late binding is that it's not version specific. However, this shouldn't be
too much of an issue when using the DAO object library, since Access 2000/2002/2003 all use the
DAO360.dll library (ie. DAO 3.6 Object Library). Another possibility, although I'm just guessing:
Is your DAO360.dll file properly registered?

I tried your code both late bound and early bound--it works fine both ways.

Tom
_________________________________


Thank you for your reply. I have the following observations to make, which I
shall place before you for your comments.
1. It would be very logical to accept that there is no specific order for
the DIM statements.
2. The "DTE" is a local table and not a linked one.
3. The code on the form verifies that the variable "vrFamilyRelation"
does not contain NULL.
4. None of the fields in the mentioned "DTE" local table contains any
NULL and/or any empty values and/or blanks in any of the records.
5. Keeping view of the points # 2 to 4, however, I still have observed
that it becomes necessary to define/declare the database and the recordset
objects before declaring/defining the variables using DIM statements;
alternatively, I start receiving errors which are related to:
a. .Index
b. .Seek

[Interestingly, in another PC which runs Access 2003, I have had no such
problems. The PC which I have been using for development, has Access 2002
loaded. The finished application would be ultimately deployed on Access 2002
environment.]

What is the advantage of using early binding as you have suggested in your
post? How does it any way differ from declaring the database and recordset
as "Objects"?

Your suggestions and advice are always highly appreciated and prized.
Thank you.

 

[doyapore]

Thank you for your observations.
I am not sure which service packs have been applied. But I have made a note
to install the SP-3 which you have mentioned.
It is also a great relief to know that there is nothing wrong with my code
and it should work alright under Access 2002. Thank you for testing out this
code.
--
Your suggestions and advice are always highly appreciated and prized.
Thank you.

Hello Doyapore,

I have noticed that if a DIM statement defining a variable is
placed before the DIM statements which define the database
and the recordset as objects, I get an error.

I just tried your code, using Access 2002 SP-3, and it works fine on my
PC. My test included
creating a local table named DTE, with three fields: parCardSlNo (Text,
Primary Key), CARD_SLNO
(Text) and Relation (Text). I created a form bound to this table, and
included a label named
lblFamilyRelation, a textbox named txtString and a command button named
Command3. The click
event for the command button is as follows:

Private Sub Command3_Click()
Dim strCard As String

strCard = Me.txtString
Proc_FamilyRelation (strCard)

End Sub


My testing includes declaring the variable vrFamilyRelation before
declaring the objects, as in
the following:

Public Sub Proc_FamilyRelation(parCardSlNo As String)
On Error GoTo Err_Msg

Dim vrFamilyRelation As String
Dim dbslocal As Object
Dim rstDTE As Object
:
:
etc.
End Sub

Anyway, it works fine on my PC. Don't know if this matters in this
particular case, but what
service packs do you have applied to Access and to the JET database
engine?

What is the advantage of using early binding as you have suggested
in your post?

1.) Slightly faster code execution
2.) Intellisense
3.) Compile time error checking

An advantage of using late binding is that it's not version specific.
However, this shouldn't be
too much of an issue when using the DAO object library, since Access
2000/2002/2003 all use the
DAO360.dll library (ie. DAO 3.6 Object Library). Another possibility,
although I'm just guessing:
Is your DAO360.dll file properly registered?

I tried your code both late bound and early bound--it works fine both
ways.

Tom
_________________________________


Thank you for your reply. I have the following observations to make, which
I
shall place before you for your comments.
1. It would be very logical to accept that there is no specific order
for
the DIM statements.
2. The "DTE" is a local table and not a linked one.
3. The code on the form verifies that the variable "vrFamilyRelation"
does not contain NULL.
4. None of the fields in the mentioned "DTE" local table contains any
NULL and/or any empty values and/or blanks in any of the records.
5. Keeping view of the points # 2 to 4, however, I still have observed
that it becomes necessary to define/declare the database and the recordset
objects before declaring/defining the variables using DIM statements;
alternatively, I start receiving errors which are related to:
a. .Index
b. .Seek

[Interestingly, in another PC which runs Access 2003, I have had no such
problems. The PC which I have been using for development, has Access 2002
loaded. The finished application would be ultimately deployed on Access
2002
environment.]

What is the advantage of using early binding as you have suggested in your
post? How does it any way differ from declaring the database and recordset
as "Objects"?

Your suggestions and advice are always highly appreciated and prized.
Thank you.

 

[Tom Wickerath]

You're welcome.
In Access, look at Help > About Microsoft Access to determine the service pack level (you
probably already knew this).

You should be at SP8 for the JET database engine
http://support.microsoft.com/kb/239114#2

You might want to go ahead and try re-registering your DAO360.dll file. It won't hurt anything to
re-register it if it's already properly registered. To register this file, first find it on your
hard drive. Close Access (and any other software that might be using this .dll file). Then click
Start > Run and enter the command "Regsvr32" (without the quotes). Drag the DAO360.dll file that
you found to the end of this line, making sure that there is a single space inbetween the name of
the executable and the filename. This way, you won't need to deal with typing in the long path.
Alternatively, use Method 1 in the resolution section of this KB article:

http://support.microsoft.com/?id=248790

I suggest making a few changes in your code:

1) Declare vrFamilyRelation as a variant, as Van suggested.

2) Change the line of code that reads:
If vrFamilyRelation = Null Then
to
If IsNull(vrFamilyRelation) = True Then

3) Change the order of your error-handler, and Eject_Sub. This will allow you to get rid of the
last GoTo Eject_Sub command if you add an Exit Sub statement. Also, I always suggest using On
Error Resume Next when you are closing object variables. Otherwise, you can get yourself into a
nasty loop if you attempt to close an object that may not have gotten opened properly for some
reason.

GoTo Eject_Sub

Err_Msg:
MsgBox Err.Description, , "Procedural error"
MsgBox Err.Number, , "Procedural error"
Resume Next

Eject_Sub:
rstDTE.Close
dbslocal.Close
End Sub
____________________________

Eject_Sub:
On Error Resume Next
rstDTE.Close
dbslocal.Close
Exit Sub

Err_Msg:
MsgBox "Error: " & Err.Number & ", " & Err.Description, , "Procedural error"
Resume Eject_Sub

End Sub



Good Luck

Tom
__________________________________


Thank you for your observations.
I am not sure which service packs have been applied. But I have made a note
to install the SP-3 which you have mentioned.
It is also a great relief to know that there is nothing wrong with my code
and it should work alright under Access 2002. Thank you for testing out this
code.
--
Your suggestions and advice are always highly appreciated and prized.
Thank you.

__________________________________


Hello Doyapore,

I have noticed that if a DIM statement defining a variable is
placed before the DIM statements which define the database
and the recordset as objects, I get an error.

I just tried your code, using Access 2002 SP-3, and it works fine on my PC. My test included
creating a local table named DTE, with three fields: parCardSlNo (Text, Primary Key), CARD_SLNO
(Text) and Relation (Text). I created a form bound to this table, and included a label named
lblFamilyRelation, a textbox named txtString and a command button named Command3. The click
event for the command button is as follows:

Private Sub Command3_Click()
Dim strCard As String

strCard = Me.txtString
Proc_FamilyRelation (strCard)

End Sub


My testing includes declaring the variable vrFamilyRelation before declaring the objects, as in
the following:

Public Sub Proc_FamilyRelation(parCardSlNo As String)
On Error GoTo Err_Msg

Dim vrFamilyRelation As String
Dim dbslocal As Object
Dim rstDTE As Object
:
:
etc.
End Sub

Anyway, it works fine on my PC. Don't know if this matters in this particular case, but what
service packs do you have applied to Access and to the JET database engine?

What is the advantage of using early binding as you have suggested
in your post?

1.) Slightly faster code execution
2.) Intellisense
3.) Compile time error checking

An advantage of using late binding is that it's not version specific. However, this shouldn't be
too much of an issue when using the DAO object library, since Access 2000/2002/2003 all use the
DAO360.dll library (ie. DAO 3.6 Object Library). Another possibility, although I'm just guessing:
Is your DAO360.dll file properly registered?

I tried your code both late bound and early bound--it works fine both ways.

Tom
_________________________________


Thank you for your reply. I have the following observations to make, which I
shall place before you for your comments.
1. It would be very logical to accept that there is no specific order for
the DIM statements.
2. The "DTE" is a local table and not a linked one.
3. The code on the form verifies that the variable "vrFamilyRelation"
does not contain NULL.
4. None of the fields in the mentioned "DTE" local table contains any
NULL and/or any empty values and/or blanks in any of the records.
5. Keeping view of the points # 2 to 4, however, I still have observed
that it becomes necessary to define/declare the database and the recordset
objects before declaring/defining the variables using DIM statements;
alternatively, I start receiving errors which are related to:
a. .Index
b. .Seek

[Interestingly, in another PC which runs Access 2003, I have had no such
problems. The PC which I have been using for development, has Access 2002
loaded. The finished application would be ultimately deployed on Access 2002
environment.]

What is the advantage of using early binding as you have suggested in your
post? How does it any way differ from declaring the database and recordset
as "Objects"?

Your suggestions and advice are always highly appreciated and prized.
Thank you.

 

[doyapore]

All your suggestions have been incorporated. Thank you once again.
--
Your suggestions and advice are always highly appreciated and prized.
Thank you.

You're welcome.
In Access, look at Help > About Microsoft Access to determine the service
pack level (you
probably already knew this).

You should be at SP8 for the JET database engine
http://support.microsoft.com/kb/239114#2

You might want to go ahead and try re-registering your DAO360.dll file. It
won't hurt anything to
re-register it if it's already properly registered. To register this file,
first find it on your
hard drive. Close Access (and any other software that might be using this
.dll file). Then click
Start > Run and enter the command "Regsvr32" (without the quotes). Drag
the DAO360.dll file that
you found to the end of this line, making sure that there is a single
space inbetween the name of
the executable and the filename. This way, you won't need to deal with
typing in the long path.
Alternatively, use Method 1 in the resolution section of this KB article:

http://support.microsoft.com/?id=248790

I suggest making a few changes in your code:

1) Declare vrFamilyRelation as a variant, as Van suggested.

2) Change the line of code that reads:
If vrFamilyRelation = Null Then
to
If IsNull(vrFamilyRelation) = True Then

3) Change the order of your error-handler, and Eject_Sub. This will allow
you to get rid of the
last GoTo Eject_Sub command if you add an Exit Sub statement. Also, I
always suggest using On
Error Resume Next when you are closing object variables. Otherwise, you
can get yourself into a
nasty loop if you attempt to close an object that may not have gotten
opened properly for some
reason.

GoTo Eject_Sub

Err_Msg:
MsgBox Err.Description, , "Procedural error"
MsgBox Err.Number, , "Procedural error"
Resume Next

Eject_Sub:
rstDTE.Close
dbslocal.Close
End Sub
____________________________

Eject_Sub:
On Error Resume Next
rstDTE.Close
dbslocal.Close
Exit Sub

Err_Msg:
MsgBox "Error: " & Err.Number & ", " & Err.Description, , "Procedural
error"
Resume Eject_Sub

End Sub



Good Luck

Tom
__________________________________


Thank you for your observations.
I am not sure which service packs have been applied. But I have made a
note
to install the SP-3 which you have mentioned.
It is also a great relief to know that there is nothing wrong with my code
and it should work alright under Access 2002. Thank you for testing out
this
code.
--
Your suggestions and advice are always highly appreciated and prized.
Thank you.

__________________________________


Hello Doyapore,

I have noticed that if a DIM statement defining a variable is
placed before the DIM statements which define the database
and the recordset as objects, I get an error.

I just tried your code, using Access 2002 SP-3, and it works fine on my
PC. My test included
creating a local table named DTE, with three fields: parCardSlNo (Text,
Primary Key), CARD_SLNO
(Text) and Relation (Text). I created a form bound to this table, and
included a label named
lblFamilyRelation, a textbox named txtString and a command button named
Command3. The click
event for the command button is as follows:

Private Sub Command3_Click()
Dim strCard As String

strCard = Me.txtString
Proc_FamilyRelation (strCard)

End Sub


My testing includes declaring the variable vrFamilyRelation before
declaring the objects, as in
the following:

Public Sub Proc_FamilyRelation(parCardSlNo As String)
On Error GoTo Err_Msg

Dim vrFamilyRelation As String
Dim dbslocal As Object
Dim rstDTE As Object
:
:
etc.
End Sub

Anyway, it works fine on my PC. Don't know if this matters in this
particular case, but what
service packs do you have applied to Access and to the JET database
engine?

What is the advantage of using early binding as you have suggested
in your post?

1.) Slightly faster code execution
2.) Intellisense
3.) Compile time error checking

An advantage of using late binding is that it's not version specific.
However, this shouldn't be
too much of an issue when using the DAO object library, since Access
2000/2002/2003 all use the
DAO360.dll library (ie. DAO 3.6 Object Library). Another possibility,
although I'm just guessing:
Is your DAO360.dll file properly registered?

I tried your code both late bound and early bound--it works fine both
ways.

Tom
_________________________________


Thank you for your reply. I have the following observations to make, which
I
shall place before you for your comments.
1. It would be very logical to accept that there is no specific order
for
the DIM statements.
2. The "DTE" is a local table and not a linked one.
3. The code on the form verifies that the variable "vrFamilyRelation"
does not contain NULL.
4. None of the fields in the mentioned "DTE" local table contains any
NULL and/or any empty values and/or blanks in any of the records.
5. Keeping view of the points # 2 to 4, however, I still have observed
that it becomes necessary to define/declare the database and the recordset
objects before declaring/defining the variables using DIM statements;
alternatively, I start receiving errors which are related to:
a. .Index
b. .Seek

[Interestingly, in another PC which runs Access 2003, I have had no such
problems. The PC which I have been using for development, has Access 2002
loaded. The finished application would be ultimately deployed on Access
2002
environment.]

What is the advantage of using early binding as you have suggested in your
post? How does it any way differ from declaring the database and recordset
as "Objects"?

Your suggestions and advice are always highly appreciated and prized.
Thank you.

 

[Van T. Dinh]

On your point 3, your variable vrFamilyRelation can _never_ be Null
(_regardless_ of what you have in the Field Relation) so there is no point
in testing for Null. A variable declared as String cannot be assigned a
Null value!

More importantly, if you try to assign Null to the variable
vrFamilyRelation, you will get the error 94. Hence, your If condition is
superfluous since it is always false.

 

[Tom Wickerath]

Hi Van,

I agree with your statements "A variable declared as String cannot be assigned a Null value!" and
"More importantly, if you try to assign Null to the variable vrFamilyRelation, you will get the
error 94". However, I'm kind of having a problem with your first sentence that reads: "On your
point 3, your variable vrFamilyRelation can _never_ be Null (_regardless_ of what you have in the
Field Relation)". When the field Relation is null in the DTE table for the tests I conducted, I
see the following in the debug window, after adding two lines of code:

Debug.Print TypeName(vrFamilyRelation)
vrFamilyRelation = !Relation
Debug.Print TypeName(vrFamilyRelation)

Empty
Null

(I had already changed the declaration for this variable to a variant, as per your earlier
suggestion).

Tom
__________________________________


On your point 3, your variable vrFamilyRelation can _never_ be Null
(_regardless_ of what you have in the Field Relation) so there is no point
in testing for Null. A variable declared as String cannot be assigned a
Null value!

More importantly, if you try to assign Null to the variable
vrFamilyRelation, you will get the error 94. Hence, your If condition is
superfluous since it is always false.

 

[Van T. Dinh]

"...I had already changed the declaration for this variable to a variant..."

That's the difference, Tom.

In your case, the Variant Variable "vrFamilyRelation" could be and was
assigned a Null value.

In doyapore's code posted in the starting post, it was declared as String
which could not be assigned a Null value.

--
HTH
Van T. Dinh
MVP (Access)

Hi Van,

I agree with your statements "A variable declared as String cannot be assigned a Null value!" and
"More importantly, if you try to assign Null to the variable

vrFamilyRelation, you will get the

 

[doyapore]

Changes incorporated again. To obviate any confusion, I just test the !Relation to check whether it has got a length less than and/or equal to zero. If it does, I just bypass the record. Here's what I did to my code.

****************** Code begins *********************
Public Sub Proc_FamilyRelation(parCardSlNo As String)
On Error GoTo Err_Msg
Dim dbslocal As Object
Dim rstDTE As Object
Set dbslocal = CurrentDb
Set rstDTE = dbslocal.openrecordset("DTE")

Dim vrFamilyRelation As String

With rstDTE
.Index = "CARD_SLNO"
.Seek "=", parCardSlNo
If .NoMatch Then
lblFamilyRelation.Caption = "No relation exists in tabled information"
Beep
GoTo Eject_Sub
End If
If Len(vrFamilyRelation) <= 0 Then
lblFamilyRelation.Caption = "Relationship undefined in tabled information"
Else
vrFamilyRelation = !Relation
lblFamilyRelation.Caption = "Relationship : " + vrFamilyRelation
End If
End With

GoTo Eject_Sub

Err_Msg:
MsgBox Err.Description, , "Procedural error"
MsgBox Err.Number, , "Procedural error"
Resume Next

Eject_Sub:
rstDTE.Close
dbslocal.Close
End Sub
********************* Code Ends *****************************
I have tested this code against a backdrop where the Relation Field might contain Null or even blank records. It works just fine.

 

[Tom Wickerath]

Okay, thanks Van. That was the difference. I thought you were already considering the change to
the variable type from string to variant when you made the statement. Just a mis-interpretation.

Tom
________________________________


"...I had already changed the declaration for this variable to a variant..."

That's the difference, Tom.

In your case, the Variant Variable "vrFamilyRelation" could be and was
assigned a Null value.

In doyapore's code posted in the starting post, it was declared as String
which could not be assigned a Null value.

--
HTH
Van T. Dinh
MVP (Access)

Hi Van,

I agree with your statements "A variable declared as String cannot be assigned a Null value!" and
"More importantly, if you try to assign Null to the variable

vrFamilyRelation, you will get the

 

[Tom Wickerath]

Doyapore,

It looks like you are testing the length of your string variable, not the Relation field. This
test should always evaluate true since you have an empty string at this point in your code.

In an earlier post, you indicated "All your suggestions have been incorporated". This doesn't
appear to be the case. Try renaming your table from DTE to anything else. Even if you have Name
Autocorrect enabled (which you shouldn't), the reference to "DTE" will not be updated in your VBA
code. Then view your form in normal mode and cause the subroutine to execute. You may need to
change the form's recordsource, in order to get past an error message when you first attempt to
open the form.

The reason I'm suggesting that you make this change is so that you can witness first-hand the
value of including "On Error Resume Next" prior to attempting to close object variables that may
not be opened properly in the first place. I counted (10) errors displayed with your msgbox
statements. This would have been (5) normally, if you combine the two statements into one. The
only reason you don't end up in a nasty infinite loop is your Resume Next statement that occurs
after the two msgbox statements.

Tom
____________________________________


Changes incorporated again. To obviate any confusion, I just test the !Relation to check whether
it has got a length less than and/or equal to zero. If it does, I just bypass the record. Here's
what I did to my code.

****************** Code begins *********************
Public Sub Proc_FamilyRelation(parCardSlNo As String)
On Error GoTo Err_Msg
Dim dbslocal As Object
Dim rstDTE As Object
Set dbslocal = CurrentDb
Set rstDTE = dbslocal.openrecordset("DTE")

Dim vrFamilyRelation As String

With rstDTE
.Index = "CARD_SLNO"
.Seek "=", parCardSlNo
If .NoMatch Then
lblFamilyRelation.Caption = "No relation exists in tabled information"
Beep
GoTo Eject_Sub
End If
If Len(vrFamilyRelation) <= 0 Then
lblFamilyRelation.Caption = "Relationship undefined in tabled information"
Else
vrFamilyRelation = !Relation
lblFamilyRelation.Caption = "Relationship : " + vrFamilyRelation
End If
End With

GoTo Eject_Sub

Err_Msg:
MsgBox Err.Description, , "Procedural error"
MsgBox Err.Number, , "Procedural error"
Resume Next

Eject_Sub:
rstDTE.Close
dbslocal.Close
End Sub
********************* Code Ends *****************************
I have tested this code against a backdrop where the Relation Field might contain Null or even
blank records. It works just fine.