Wrong code path taken

B

Bill

In the OnOpen code below, execution is taking the Else
path even though I've verified that OpenArgs is Null, both
by displaying it via the MsgBox AND examining its value
when the runtime error is raised when the statement:

rsReplicate.Open tmpSQL, con, adOpenKeyset, adLockOptimistic

is encountered.

I feared that the mdb might be somehow corrupted, so I created
a new mdb and imported everything into the new mdb only to
discover that the erroneous path continues to be taken.

Have I been eating too many peanuts lately or what?

Bill


Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize 'Open window
maximized
Me.AllowAdditions = False
Me.AllowDeletions = True
Me.AllowEdits = True
Me.OrderByOn = False
CurMedia = "" 'Initialize current media
to nothing
ClearTheTimeRegister
VolID = -1 'Value of zero signifies volume table
update pending

MsgBox "[" & Me.OpenArgs & "]"

If Me.OpenArgs = Null Then 'Brand new entry pending?
'===========================================================================================
'YES, OpenArgs is Null if we have a new entry
CompID = 0 'Composer name is required
OrchID = 61 'None Specified
CondID = 79 'None Specified
GenreID = 0 'Genre is Required
SoloID = 49 'None Specified

LastPlayed = 0
Else
'===========================================================================================
'NO, OpenArgs is an ID of a Works to be replicated.
Set con = Application.CurrentProject.Connection

tmpSQL = "SELECT * FROM [Works] WHERE WorksID = " & Me.OpenArgs
Set rsReplicate = New ADODB.Recordset
rsReplicate.Open tmpSQL, con, adOpenKeyset, adLockOptimistic
WorksTitle = rsReplicate!Title

If Not IsNull(rsReplicate!Comments) Then
Comments = rsReplicate!Comments
Else
Comments = "" 'We allow Comments to be initialized
as zero-length
End If

If Not IsNull(rsReplicate!GAPuse) Then GAPDate = rsReplicate!GAPuse

Me.cmboGenre = DLookup("[SubGenre]", "QGenreFull", "[GenreID] = " &
rsReplicate!GenreID)
GenreID = cmboGenre.Column(0)

Me.cmboComposer = DLookup("[CompName]", "QComposers", "[CompID] = " &
rsReplicate!CompID)
CompID = Me.cmboComposer.Column(0)

Me.cmboOrchestra = DLookup("[Orchestra]", "QOrchestra", "[OrchID] = " &
rsReplicate!OrchID)
OrchID = Me.cmboOrchestra.Column(0)

Me.cmboConductor = DLookup("[CondName]", "QConductors", "[CondID] = " &
rsReplicate!CondID)
CondID = Me.cmboConductor.Column(0)

Me.cmboSoloist = DLookup("[SoloName]", "QSoloist", "[SoloID] = " &
rsReplicate!SoloID)
SoloID = Me.cmboSoloist.Column(0)

rsReplicate.Close
Set rsReplicate = Nothing
Set con = Nothing
End If

End Sub
 
D

Douglas J. Steele

See whether the following works any better:

If Len(Me.OpenArgs & "") = 0 Then

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Bill said:
In the OnOpen code below, execution is taking the Else
path even though I've verified that OpenArgs is Null, both
by displaying it via the MsgBox AND examining its value
when the runtime error is raised when the statement:

rsReplicate.Open tmpSQL, con, adOpenKeyset, adLockOptimistic

is encountered.

I feared that the mdb might be somehow corrupted, so I created
a new mdb and imported everything into the new mdb only to
discover that the erroneous path continues to be taken.

Have I been eating too many peanuts lately or what?

Bill


Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize 'Open window
maximized
Me.AllowAdditions = False
Me.AllowDeletions = True
Me.AllowEdits = True
Me.OrderByOn = False
CurMedia = "" 'Initialize current
media to nothing
ClearTheTimeRegister
VolID = -1 'Value of zero signifies volume table
update pending

MsgBox "[" & Me.OpenArgs & "]"

If Me.OpenArgs = Null Then 'Brand new entry pending?

'===========================================================================================
'YES, OpenArgs is Null if we have a new entry
CompID = 0 'Composer name is required
OrchID = 61 'None Specified
CondID = 79 'None Specified
GenreID = 0 'Genre is Required
SoloID = 49 'None Specified

LastPlayed = 0
Else

'===========================================================================================
'NO, OpenArgs is an ID of a Works to be replicated.
Set con = Application.CurrentProject.Connection

tmpSQL = "SELECT * FROM [Works] WHERE WorksID = " & Me.OpenArgs
Set rsReplicate = New ADODB.Recordset
rsReplicate.Open tmpSQL, con, adOpenKeyset, adLockOptimistic
WorksTitle = rsReplicate!Title

If Not IsNull(rsReplicate!Comments) Then
Comments = rsReplicate!Comments
Else
Comments = "" 'We allow Comments to be initialized
as zero-length
End If

If Not IsNull(rsReplicate!GAPuse) Then GAPDate = rsReplicate!GAPuse

Me.cmboGenre = DLookup("[SubGenre]", "QGenreFull", "[GenreID] = " &
rsReplicate!GenreID)
GenreID = cmboGenre.Column(0)

Me.cmboComposer = DLookup("[CompName]", "QComposers", "[CompID] = " &
rsReplicate!CompID)
CompID = Me.cmboComposer.Column(0)

Me.cmboOrchestra = DLookup("[Orchestra]", "QOrchestra", "[OrchID] = " &
rsReplicate!OrchID)
OrchID = Me.cmboOrchestra.Column(0)

Me.cmboConductor = DLookup("[CondName]", "QConductors", "[CondID] = " &
rsReplicate!CondID)
CondID = Me.cmboConductor.Column(0)

Me.cmboSoloist = DLookup("[SoloName]", "QSoloist", "[SoloID] = " &
rsReplicate!SoloID)
SoloID = Me.cmboSoloist.Column(0)

rsReplicate.Close
Set rsReplicate = Nothing
Set con = Nothing
End If

End Sub
Click to expand...
 
L

Larry Linson

Null, by definition, is never _equal_ to anything, even itself. To test
specifically for Null, Access provides the IsNull function, but Doug's
approach is better because it will also catch a zero-length string which
most likely means the same.

Larry Linson
Microsoft Access MVP

Bill said:
In the OnOpen code below, execution is taking the Else
path even though I've verified that OpenArgs is Null, both
by displaying it via the MsgBox AND examining its value
when the runtime error is raised when the statement:

rsReplicate.Open tmpSQL, con, adOpenKeyset, adLockOptimistic

is encountered.

I feared that the mdb might be somehow corrupted, so I created
a new mdb and imported everything into the new mdb only to
discover that the erroneous path continues to be taken.

Have I been eating too many peanuts lately or what?

Bill


Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize 'Open window
maximized
Me.AllowAdditions = False
Me.AllowDeletions = True
Me.AllowEdits = True
Me.OrderByOn = False
CurMedia = "" 'Initialize current
media to nothing
ClearTheTimeRegister
VolID = -1 'Value of zero signifies volume table
update pending

MsgBox "[" & Me.OpenArgs & "]"

If Me.OpenArgs = Null Then 'Brand new entry pending?

'===========================================================================================
'YES, OpenArgs is Null if we have a new entry
CompID = 0 'Composer name is required
OrchID = 61 'None Specified
CondID = 79 'None Specified
GenreID = 0 'Genre is Required
SoloID = 49 'None Specified

LastPlayed = 0
Else

'===========================================================================================
'NO, OpenArgs is an ID of a Works to be replicated.
Set con = Application.CurrentProject.Connection

tmpSQL = "SELECT * FROM [Works] WHERE WorksID = " & Me.OpenArgs
Set rsReplicate = New ADODB.Recordset
rsReplicate.Open tmpSQL, con, adOpenKeyset, adLockOptimistic
WorksTitle = rsReplicate!Title

If Not IsNull(rsReplicate!Comments) Then
Comments = rsReplicate!Comments
Else
Comments = "" 'We allow Comments to be initialized
as zero-length
End If

If Not IsNull(rsReplicate!GAPuse) Then GAPDate = rsReplicate!GAPuse

Me.cmboGenre = DLookup("[SubGenre]", "QGenreFull", "[GenreID] = " &
rsReplicate!GenreID)
GenreID = cmboGenre.Column(0)

Me.cmboComposer = DLookup("[CompName]", "QComposers", "[CompID] = " &
rsReplicate!CompID)
CompID = Me.cmboComposer.Column(0)

Me.cmboOrchestra = DLookup("[Orchestra]", "QOrchestra", "[OrchID] = " &
rsReplicate!OrchID)
OrchID = Me.cmboOrchestra.Column(0)

Me.cmboConductor = DLookup("[CondName]", "QConductors", "[CondID] = " &
rsReplicate!CondID)
CondID = Me.cmboConductor.Column(0)

Me.cmboSoloist = DLookup("[SoloName]", "QSoloist", "[SoloID] = " &
rsReplicate!SoloID)
SoloID = Me.cmboSoloist.Column(0)

rsReplicate.Close
Set rsReplicate = Nothing
Set con = Nothing
End If

End Sub
Click to expand...
 
B

Bill

Doug's approach being "If Len(OpenArgs & "") = 0 Then"
the equivalent of "If IsNull(OpenArgs) Then"?

Bill



Larry Linson said:
Null, by definition, is never _equal_ to anything, even itself. To test
specifically for Null, Access provides the IsNull function, but Doug's
approach is better because it will also catch a zero-length string which
most likely means the same.

Larry Linson
Microsoft Access MVP

Bill said:
In the OnOpen code below, execution is taking the Else
path even though I've verified that OpenArgs is Null, both
by displaying it via the MsgBox AND examining its value
when the runtime error is raised when the statement:

rsReplicate.Open tmpSQL, con, adOpenKeyset, adLockOptimistic

is encountered.

I feared that the mdb might be somehow corrupted, so I created
a new mdb and imported everything into the new mdb only to
discover that the erroneous path continues to be taken.

Have I been eating too many peanuts lately or what?

Bill


Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize 'Open window
maximized
Me.AllowAdditions = False
Me.AllowDeletions = True
Me.AllowEdits = True
Me.OrderByOn = False
CurMedia = "" 'Initialize current
media to nothing
ClearTheTimeRegister
VolID = -1 'Value of zero signifies volume table
update pending

MsgBox "[" & Me.OpenArgs & "]"

If Me.OpenArgs = Null Then 'Brand new entry pending?

'===========================================================================================
'YES, OpenArgs is Null if we have a new entry
CompID = 0 'Composer name is required
OrchID = 61 'None Specified
CondID = 79 'None Specified
GenreID = 0 'Genre is Required
SoloID = 49 'None Specified

LastPlayed = 0
Else

'===========================================================================================
'NO, OpenArgs is an ID of a Works to be replicated.
Set con = Application.CurrentProject.Connection

tmpSQL = "SELECT * FROM [Works] WHERE WorksID = " & Me.OpenArgs
Set rsReplicate = New ADODB.Recordset
rsReplicate.Open tmpSQL, con, adOpenKeyset, adLockOptimistic
WorksTitle = rsReplicate!Title

If Not IsNull(rsReplicate!Comments) Then
Comments = rsReplicate!Comments
Else
Comments = "" 'We allow Comments to be initialized
as zero-length
End If

If Not IsNull(rsReplicate!GAPuse) Then GAPDate = rsReplicate!GAPuse

Me.cmboGenre = DLookup("[SubGenre]", "QGenreFull", "[GenreID] = " &
rsReplicate!GenreID)
GenreID = cmboGenre.Column(0)

Me.cmboComposer = DLookup("[CompName]", "QComposers", "[CompID] = " &
rsReplicate!CompID)
CompID = Me.cmboComposer.Column(0)

Me.cmboOrchestra = DLookup("[Orchestra]", "QOrchestra", "[OrchID] = "
& rsReplicate!OrchID)
OrchID = Me.cmboOrchestra.Column(0)

Me.cmboConductor = DLookup("[CondName]", "QConductors", "[CondID] = "
& rsReplicate!CondID)
CondID = Me.cmboConductor.Column(0)

Me.cmboSoloist = DLookup("[SoloName]", "QSoloist", "[SoloID] = " &
rsReplicate!SoloID)
SoloID = Me.cmboSoloist.Column(0)

rsReplicate.Close
Set rsReplicate = Nothing
Set con = Nothing
End If

End Sub
Click to expand...
Click to expand...
 
S

Shane S via AccessMonster.com

Hey Bill,

No yours is not exactly the equivalent of Doug's. Doug's checks for Null and
Zero Length String and then converts it to 0.

Yours just checks to see if it's Null.

Hope I got that right,
Shane
Doug's approach being "If Len(OpenArgs & "") = 0 Then"
the equivalent of "If IsNull(OpenArgs) Then"?

Bill
Null, by definition, is never _equal_ to anything, even itself. To test
specifically for Null, Access provides the IsNull function, but Doug's
Click to expand...
[quoted text clipped - 93 lines]
Click to expand...
 
B

Bill

Odd that 99% of the time I code such tests
as Len(Me.OpenArgs & "") = 0. What
possesed me to test directly for a null
value totally escapes me. At least
IsNull(Me.OpenArgs) would have been
better.... SIGH! Maybe too many peanuts
and not enough of Steele's home brew?
Bill


Douglas J. Steele said:
See whether the following works any better:

If Len(Me.OpenArgs & "") = 0 Then

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Bill said:
In the OnOpen code below, execution is taking the Else
path even though I've verified that OpenArgs is Null, both
by displaying it via the MsgBox AND examining its value
when the runtime error is raised when the statement:

rsReplicate.Open tmpSQL, con, adOpenKeyset, adLockOptimistic

is encountered.

I feared that the mdb might be somehow corrupted, so I created
a new mdb and imported everything into the new mdb only to
discover that the erroneous path continues to be taken.

Have I been eating too many peanuts lately or what?

Bill


Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize 'Open window
maximized
Me.AllowAdditions = False
Me.AllowDeletions = True
Me.AllowEdits = True
Me.OrderByOn = False
CurMedia = "" 'Initialize current
media to nothing
ClearTheTimeRegister
VolID = -1 'Value of zero signifies volume table
update pending

MsgBox "[" & Me.OpenArgs & "]"

If Me.OpenArgs = Null Then 'Brand new entry pending?

'===========================================================================================
'YES, OpenArgs is Null if we have a new entry
CompID = 0 'Composer name is required
OrchID = 61 'None Specified
CondID = 79 'None Specified
GenreID = 0 'Genre is Required
SoloID = 49 'None Specified

LastPlayed = 0
Else

'===========================================================================================
'NO, OpenArgs is an ID of a Works to be replicated.
Set con = Application.CurrentProject.Connection

tmpSQL = "SELECT * FROM [Works] WHERE WorksID = " & Me.OpenArgs
Set rsReplicate = New ADODB.Recordset
rsReplicate.Open tmpSQL, con, adOpenKeyset, adLockOptimistic
WorksTitle = rsReplicate!Title

If Not IsNull(rsReplicate!Comments) Then
Comments = rsReplicate!Comments
Else
Comments = "" 'We allow Comments to be initialized
as zero-length
End If

If Not IsNull(rsReplicate!GAPuse) Then GAPDate = rsReplicate!GAPuse

Me.cmboGenre = DLookup("[SubGenre]", "QGenreFull", "[GenreID] = " &
rsReplicate!GenreID)
GenreID = cmboGenre.Column(0)

Me.cmboComposer = DLookup("[CompName]", "QComposers", "[CompID] = " &
rsReplicate!CompID)
CompID = Me.cmboComposer.Column(0)

Me.cmboOrchestra = DLookup("[Orchestra]", "QOrchestra", "[OrchID] = "
& rsReplicate!OrchID)
OrchID = Me.cmboOrchestra.Column(0)

Me.cmboConductor = DLookup("[CondName]", "QConductors", "[CondID] = "
& rsReplicate!CondID)
CondID = Me.cmboConductor.Column(0)

Me.cmboSoloist = DLookup("[SoloName]", "QSoloist", "[SoloID] = " &
rsReplicate!SoloID)
SoloID = Me.cmboSoloist.Column(0)

rsReplicate.Close
Set rsReplicate = Nothing
Set con = Nothing
End If

End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Split DB Path to BE Code 3

Top