Weird BrowseTo Runtime Error 6054 (1 Viewer)

[ahmed_optom]

There appears to be a weird issue with the browseto command.

If I use it in isolation, it works as expected. If the same browseto comes after any vba that includes the use of "set", then it gives a runtime error 6054.

my browseto code is as below:

DoCmd.BrowseTo ObjectType:=acBrowseToForm, _
ObjectName:="Existing Patients", _
PathToSubformControl:="Main Menu.NavigationSubform", _
WhereCondition:="", _
Page:="", _
DataMode:=acFormReadOnly

my googling on this issue found someone in 2014 with the same issue

DoCmd.BrowseTo statement produces strange PathToSubformControl error

Background: I'm working on a project that relies on 21 different lookup tables for basic data used throughout the rest of the application. As part of my user interface, I intend to expose these lookup tables to the user through forms to allow for modification of the data. I have created a...

www.accessforums.net

Does anyone know more or a way around this?

I have tried moving code into functions, but as expected, this did not help.

if I change the order of the code, that particular runtime error goes away, but the form breaks for other reasons, eg moving away from the form whilst other code is trying to reference it.

Any thoughts ?

 

[arnelgp]

are you trying to open "Existing Patients" form using

Set frm = Form_???

on the first place there is a Space on the form name, how did you
get it open using the Set method.

you can only open using Set frm = Form_someform
when you can see it it in project explorer (meaning Has Module is Set to Yes).

i was able to open the form (i renamed it "Existing_Patients") and at same time BrowseTo
without any error.

 

[ahmed_optom]

Hi Arnelgp,

no, im not using set in that manner, here is the preceding code

Dim dbVideoCollection As Object
Dim rstVideos As Object
Dim fldEnumerator As Object
Dim fldColumns As Object

Set dbVideoCollection = CurrentDb
Set rstVideos = dbVideoCollection.OpenRecordset("tbl_epayment", dbOpenDynaset, dbSeeChanges)
Set fldColumns = rstVideos.Fields

' Scan the records from beginning to each
While Not rstVideos.EOF
' Check the current column
For Each fldEnumerator In rstVideos.Fields
' If the column is named Title
If fldEnumerator.Name = "Patient ID" Then
If fldEnumerator.value = Me.PatientID Then
' then change its value
rstVideos.Edit
If IsNull(rstVideos("ExamID").value) Then
rstVideos("ExamID").value = Me.ExamID
rstVideos.Update
End If
End If
End If
Next
rstVideos.MoveNext
Wend

this code makes the browseto command not work. There is some other code before, which is similar, making changes on other tables. If either parts or both of that code is there, the browseto does not work.


So my issue is that browseTo command seems to have an issue when used after certain code. Im not sure of the issue is with my code, or with the browseto command.

 

[arnelgp]

you can make your code simpler by using SQL Update query:

Dim db As DAO.Database
Set db = Currentdb
db.Execute _
"Update tbl_epayment Set [ExamID] = Nz([ExamID], " & Me!ExamID & ") Where [Patient ID] = " & Me!PatientID
Set db = Nothing

 

[ahmed_optom]

Thanks Arnelgp, I will try that and see if it helps.

I have further isolated the issue. The code below always triggers the issue of breaking the browseto command.

If Me.Check461 <> -1 Then
'want the email sent, box not ticked
'check if email is setup.
If IsNull(DLookup("[EmailAddress]", "Settings", "PracticeLocation = [Forms]![Main Menu]![location]")) Or IsNull(DLookup("[SMTP]", "Settings", "PracticeLocation = [Forms]![Main Menu]![location]")) _
Or IsNull(DLookup("[SMTPPort]", "Settings", "PracticeLocation = [Forms]![Main Menu]![location]")) Or IsNull(DLookup("[EmailUser]", "Settings", "PracticeLocation = [Forms]![Main Menu]![location]")) _
Or IsNull(DLookup("[EmailPassword]", "Settings", "PracticeLocation = [Forms]![Main Menu]![location]")) Then
'email not setup
MsgBox ("Please go to Settings and set up your Email"), , ""
Else
If DLookup("GDPREmail", "Patients", "[Patient ID] =" & Me.PatientID) = -1 Then
If Not IsNull(Me.[Email Address]) Then
If DLookup("SendSTEmail", "Settings", "PracticeLocation = [Forms]![Main Menu]![location]") = -1 Then

Dim mail As CDO.Message
Dim config As CDO.Configuration

Set mail = CreateObject("CDO.Message")
Set config = CreateObject("CDO.Configuration")

config.Fields(cdoSendUsingMethod).value = cdoSendUsingPort
config.Fields(cdoSMTPServer).value = DLookup("SMTP", "Settings", "PracticeLocation = [Forms]![Main Menu]![location]")
config.Fields(cdoSMTPServerPort).value = DLookup("[SMTPPort]", "Settings", "PracticeLocation = [Forms]![Main Menu]![location]")
config.Fields(cdoSMTPAuthenticate).value = cdoBasic
config.Fields(cdoSendUserName).value = DLookup("EmailUser", "Settings", "PracticeLocation = [Forms]![Main Menu]![location]")
config.Fields(cdoSendPassword).value = DLookup("EmailPassword", "Settings", "PracticeLocation = [Forms]![Main Menu]![location]")
config.Fields.Update

Set mail.Configuration = config

With mail
.Organization = DLookup("Practice", "Settings", "PracticeLocation = [Forms]![Main Menu]![location]")
.Sender = DLookup("Practice", "Settings", "PracticeLocation = [Forms]![Main Menu]![location]") & "<" & DLookup("EmailAddress", "Settings", "PracticeLocation = [Forms]![Main Menu]![location]") & ">"
.To = Me.[Email Address]
.From = DLookup("Practice", "Settings", "PracticeLocation = [Forms]![Main Menu]![location]") & "<" & DLookup("EmailAddress", "Settings", "PracticeLocation = [Forms]![Main Menu]![location]") & ">"
.Subject = DLookup("STEmailSubject", "Settings", "PracticeLocation = [Forms]![Main Menu]![location]")
'.TextBody = "This is the body of the first plain text email with CDO."
.HTMLBody = "Dear " & Me.[First Name] & "," & DLookup("STEmail", "Settings", "PracticeLocation = [Forms]![Main Menu]![location]")

.Send
End With

Set config = Nothing
Set mail = Nothing
End If
End If
End If
End If
End If

 

[arnelgp]

... the code is long.
please, always, above all your code (at the beginning), put:

Option Compare Database
Option Explicit

then compile your code, and see if there are undeclared variables.

 

[arnelgp]

put those two lines, i Saw some errors on the code (post #5)

If Me.Check461 <> -1 Then
    'want the email sent, box not ticked
    'check if email is setup.
    If IsNull(DLookup("[EmailAddress]", "Settings", "PracticeLocation = '" & [Forms]![Main Menu]![location] & "'")) Or _
        IsNull(DLookup("[SMTP]", "Settings", "PracticeLocation = '" & [Forms]![Main Menu]![location] & "")) _
        Or IsNull(DLookup("[SMTPPort]", "Settings", "PracticeLocation = '" & [Forms]![Main Menu]![location] & "'")) Or _
        IsNull(DLookup("[EmailUser]", "Settings", "PracticeLocation = '" & [Forms]![Main Menu]![location] & "'")) _
        Or IsNull(DLookup("[EmailPassword]", "Settings", "PracticeLocation = '" & [Forms]![Main Menu]![location] & "'")) Then
        'email not setup
        MsgBox ("Please go to Settings and set up your Email"), , ""
    Else
        If Len(Trim(DLookup("GDPREmail", "Patients", "[Patient ID] =" & Me.PatientID) & "")) > 0 Then
            If Not IsNull(Me.[Email Address]) Then
                If Len(Trim(DLookup("SendSTEmail", "Settings", "PracticeLocation = '" & [Forms]![Main Menu]![location] & "'") & "")) > 0 Then

                    Dim mail As CDO.Message
                    Dim config As CDO.Configuration

                    Set mail = CreateObject("CDO.Message")
                    Set config = CreateObject("CDO.Configuration")

                    config.Fields(cdoSendUsingMethod).Value = cdoSendUsingPort
                    config.Fields(cdoSMTPServer).Value = DLookup("SMTP", "Settings", "PracticeLocation = '" & [Forms]![Main Menu]![location] & "'")
                    config.Fields(cdoSMTPServerPort).Value = DLookup("[SMTPPort]", "Settings", "PracticeLocation = '" & [Forms]![Main Menu]![location] & "'")
                    config.Fields(cdoSMTPAuthenticate).Value = cdoBasic
                    config.Fields(cdoSendUserName).Value = DLookup("EmailUser", "Settings", "PracticeLocation = '" & [Forms]![Main Menu]![location] & "'")
                    config.Fields(cdoSendPassword).Value = DLookup("EmailPassword", "Settings", "PracticeLocation = '" & [Forms]![Main Menu]![location] & "'")
                    config.Fields.Update

                    Set mail.Configuration = config

                    With mail
                        .Organization = DLookup("Practice", "Settings", "PracticeLocation = [Forms]![Main Menu]![location]") & vbNullString
                        .Sender = DLookup("Practice", "Settings", "PracticeLocation = [Forms]![Main Menu]![location]") & "<" & DLookup("EmailAddress", "Settings", "PracticeLocation = [Forms]![Main Menu]![location]") & ">"
                        .To = Me.[Email Address]
                        .From = DLookup("Practice", "Settings", "PracticeLocation = [Forms]![Main Menu]![location]") & "<" & DLookup("EmailAddress", "Settings", "PracticeLocation = [Forms]![Main Menu]![location]") & ">"
                        .Subject = DLookup("STEmailSubject", "Settings", "PracticeLocation = [Forms]![Main Menu]![location]")
                        '.TextBody = "This is the body of the first plain text email with CDO."
                        .HTMLBody = "Dear " & Me.[First Name] & "," & DLookup("STEmail", "Settings", "PracticeLocation = [Forms]![Main Menu]![location]")

                        .Send
                    End With

                    Set config = Nothing
                    Set mail = Nothing
                End If
            End If
        End If
    End If
End If

 

[ahmed_optom]

Arnelgp,

There are no errors when I do it my end, even with explicit.

There is definitely something that is causing the browseTo to misbehave though, as if I comment that section out, the browseto works fine.

 

[theDBguy]

Arnelgp,

There are no errors when I do it my end, even with explicit.

There is definitely something that is causing the browseTo to misbehave though, as if I comment that section out, the browseto works fine.

Hi. Can you post a sample file to demonstrate the problem?

 

[arnelgp]

is PracticeLocation a String, then this is the correct syntax (post #7) as opposed to your code:

IsNull(DLookup("[EmailAddress]", "Settings", "PracticeLocation = '" & [Forms]![Main Menu]![location] & "'"))

 

[ahmed_optom]

Hi guys,

Arnelgp, I changed all the dlookups to the format you suggested, and that has fixed it!

thanks again.

 

[ahmed_optom]

Well, this bug is back again. Same thing happening, even though I havent touched the code from last time.

Im beginning to think my version of Access is punishing me

 

[ahmed_optom]

Just for the sake of others, I never did solve this, and I confirm this is simply an access bug. If you write any perfectly working dlookup then use browseto command, you will get a rte. Either can work separately without issue, but not together.

 

[arnelgp]

It is not an access bug. Its your programming bug. You dont know how to delimit the Criteria in dlookup().