Solved Option Explicit (lack of use thereof) ?

[Gasman]

Hi all,
Is there any reason anyone can think of as to why you would NOT use Option Explicit?

The lack of doing so came back to bite this user big time. I did not spot although inadvertently I was on the right track.

Determining a value - Page 2 - Eileen's Lounge

Solution is on page 2. Unfortunately that site does not have post numbers.

I have mentioned that thread in another post whereby that user was using a method of setting recordset fields unknown to me.

Solved - Virtual link between variable declared as field and recordset field?

No sure how to call the title TBH, so will try and explain. In attempting to help a user on another forum I find he is doing this. Declaring a variable as a Field called Fld12 Opening a recordset and setting that Field variable to a recordset field. He then issues an rs.Edit He then overwrites...

www.access-programmers.co.uk

 

[Pat Hartman]

Is there any reason anyone can think of as to why you would NOT use Option Explicit?

Ignorance, arrogance, a death wish all come to mind.

This is such poor practice, MS should have long ago changed the default to set this option on. It would have upset a bunch of sloppy coders but they could have easily fixed their errors and moved on.

 

[MajP]

I cannot think of a reason that this is even allowed because it can cause problems that are nearly impossible to see. MS somehow thinks this is actually helping people and making it easier to code. However, I disagree that is the cause of the OP's problem.

VBA does support polymorphism in a way.
I can ensure you the following code surprisingly works and sets all my agent names to New Name.

Option Compare Database

Public Sub NoDeclaration()
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("tblAgents")
  Set fld12 = rs!AgentName
  Debug.Print TypeName(fld12)
  Do While Not rs.EOF
    rs.Edit
      fld12.Value = "New Name"
    rs.Update
    rs.MoveNext
  Loop
  fld12 = "Hello World"
  Debug.Print fld12 & "  " & TypeName(fld12)
  fld12 = Date
  Debug.Print fld12 & "  " & TypeName(fld12)
  Set fld12 = rs
  Debug.Print fld12.Name & "  " & TypeName(fld12)
End Sub

fld12 is a undeclared variable, so it is of type variant. When you set it to an dao.field it subclasses to a dao field. In fact whatever you set it to it subclasses to that. Here is the debug.print. You can see it is a Field, String, Date, and Recordset depending on the assignment.

Field3
Hello World String
6/23/2023 Date
tblAgents Recordset2

 

[The_Doc_Man]

Probably it stems from the original Beginner's All-purpose Symbolic Instruction Code (BASIC), where as early as the late 1960s, declarations were NEVER necessary for any data type up to and including arrays with subscripts 0 to 10. The version I was using then was DEC BASIC for PDP-10. (Which was a butt-kicking 36-bit machine.)

 

[jdraw]

I would suggest that anyone not using Option Explicit is:
a) new to Access/vba, or
b) lazy.

I do not understand why M$oft's NOT setting this ON by default is in anyone's best interest.

 

[Gasman]

I cannot think of a reason that this is even allowed because it can cause problems that are nearly impossible to see. MS somehow thinks this is actually helping people and making it easier to code. However, I disagree that is the cause of the OP's problem.

VBA does support polymorphism in a way.
I can ensure you the following code surprisingly works and sets all my agent names to New Name.

Option Compare Database

Public Sub NoDeclaration()
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("tblAgents")
  Set fld12 = rs!AgentName
  Debug.Print TypeName(fld12)
  Do While Not rs.EOF
    rs.Edit
      fld12.Value = "New Name"
    rs.Update
    rs.MoveNext
  Loop
  fld12 = "Hello World"
  Debug.Print fld12 & "  " & TypeName(fld12)
  fld12 = Date
  Debug.Print fld12 & "  " & TypeName(fld12)
  Set fld12 = rs
  Debug.Print fld12.Name & "  " & TypeName(fld12)
End Sub

fld12 is a undeclared variable, so it is of type variant. When you set it to an dao.field it subclasses to a dao field. In fact whatever you set it to it subclasses to that. Here is the debug.print. You can see it is a Field, String, Date, and Recordset depending on the assignment.

No idea as to why not for the O/P then. Once he corrected that issue and Dimmed the fields, he said it then worked.

I just tried with this code and record is not updated?
Option Explicit commented out as well.

Sub UpdateRst()
Dim db As DAO.Database
'Dim fld As DAO.Field
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Select Description from TestTransactionsDelete")

Set fld = rst!Description

Do Until rst.EOF
    rst.MoveLast
    rst.Edit
    fld = "Test Description 4"
    rst.Update
    rst.MoveNext
Loop
Set fld = Nothing
Set rst = Nothing
Set db = Nothing
End Sub

 

[ebs17]

MoveNext after MoveLast?
Functioning is a broad concept. Maybe you dare to take the step towards working GOOD (code makes sense, correct, useful, ...)

 

[Josef P.]

Try:

...
Set fld = rst!Description

Do Until rst.EOF
    rst.MoveLast
    rst.Edit
    Debug.Print TypeOf fld Is DAO.Field  ' <-- True
    fld = "Test Description 4"
    Debug.Print TypeOf fld Is DAO.Field  ' <-- False !!!
    rst.Update
    rst.MoveNext
Loop
...

vs.

...
Set fld = rst!Description

Do Until rst.EOF
    rst.MoveLast
    rst.Edit
    Debug.Print TypeOf fld Is DAO.Field  ' <-- True
    fld.Value = "Test Description 4"
    Debug.Print TypeOf fld Is DAO.Field  ' <-- True
    rst.Update
    rst.MoveNext
Loop
...

 

[Gasman]

MoveNext after MoveLast?
Functioning is a broad concept. Maybe you dare to take the step towards working GOOD (code makes sense, correct, useful, ...)

How else am I going to get to EOF?

 

[Gasman]

Try:

...
Set fld = rst!Description

Do Until rst.EOF
    rst.MoveLast
    rst.Edit
    Debug.Print TypeOf fld Is DAO.Field  ' <-- True
    fld = "Test Description 4"
    Debug.Print TypeOf fld Is DAO.Field ' <-- False !!!
    rst.Update
    rst.MoveNext
Loop
...

vs.

...
Set fld = rst!Description

Do Until rst.EOF
    rst.MoveLast
    rst.Edit
    Debug.Print TypeOf fld Is DAO.Field  ' <-- True
    fld.Value = "Test Description 4"
    Debug.Print TypeOf fld Is DAO.Field ' <-- True
    rst.Update
    rst.MoveNext
Loop
...

I did spot the .Value in MajP's code, but have got so used to not using it except with TempVars.

 

[ebs17]

MoveLast + MoveNext results in EOF on the first pass. The loop is meaningless, only EOF can come after MoveLast.

 

[Gasman]

MoveLast + MoveNext results in EOF on the first pass. The loop is meaningless, only EOF can come after MoveLast.

Which is exactly what I wanted, just amend the last record.?

What is your problem?

 

[Josef P.]

How else am I going to get to EOF?

What is the loop for anyway?
According to the code, you want to edit only the last record in the recordset.

without Do Until rst.EOF:

...
    Set fld = rst!Description

    rst.MoveLast
    rst.Edit
    fld.Value = "Test Description 4"
    rst.Update
...

 

[Gasman]

Because I started with a loop as I amended all the records, like the O/P did, then I just wanted to update the last record with different text.

I could have used FindFirst on the ID field for a single record, but as I already had a loop, I just amended that.

 

[Josef P.]

Then everything is clear. It's just a leftover from testing.
Not related to the actual problem anyway.

 

[Gasman]

It is nothing but test code.
I keep stuff like that in a module named ModCrap.

 

[The_Doc_Man]

Which is exactly what I wanted, just amend the last record.?

MoveFirst, MoveLast, MoveNext, and MovePrevious methods (ADO)

Office developer client VBA reference documentation

learn.microsoft.com

Just to be clear, .MoveNext following a .MoveLast should put you at the last record for DAO or AFTER the last record for ADO. DAO won't move beyond EOF but might give an error depending on how you moved it. In essence, the .MoveNext issued after a .MoveLast is superfluous for DAO recordsets.

 

[Gasman]

or

 

[MajP]

@Gasman, I understand the point now. I see that I missed something, because my example was not what the OP did. This really echos your point of how you can get into big trouble without declaring variables. This is pretty subtle, and few people would probably see what is really going on.

Without declaring a fld12 variable, if the OP did the following this would work
Fld12.value = Abs(Fld5) * CountTicks * 7.8125 - Abs(Fld5 * 1.5) - Abs(Fld5 * 0.67) - Abs(dblPrevCom) - Abs(dblPrevFee)
This is what they did, and it does not work. Why?
Fld12 = Abs(Fld5) * CountTicks * 7.8125 - Abs(Fld5 * 1.5) - Abs(Fld5 * 0.67) - Abs(dblPrevCom) - Abs(dblPrevFee)

To demo why this happens, this code would work and would update the recordset if I assign x to fld12.value. It will not work if I omit the default .value property.

Public Sub NoDeclaration()
  Dim rs As DAO.Recordset
  
  Set rs = CurrentDb.OpenRecordset("tblAgents")
  Set fld12 = rs!AgentName
  Debug.Print TypeName(fld12)
  Do While Not rs.EOF
    rs.Edit
        'fld12.value = "x"
        fld12 = "x"
      Debug.Print TypeName(fld12)
    rs.Update
    rs.MoveNext
  Loop
End Sub

The reason is that in this line
Set fld12 = rs!AgentName
resolves to a Field
In this line if I do this
fld12.value = "x"
It remains a Field variable
Without the .value property VBA coerces the fld12 back to a string.
Confusing enough? The below print out showing the casting back to a string from a Field3.

Field3
String
String
String
String
String

 

[Gasman]

Yes, Josef P. pointed that out as well.
As I rarely use the .Value as I mentioned, that would catch me out, but then again, I never knew of that method.
You see it mentioned many times by people, that there is no need as default property.

Plus I would always use Option Explicit and Dim my variables.