some codes did not work in MS365

[dollygg]

I have an old .mdb file. It is not working in MS365. The menu items are not showing up on the switchboard.
here is the code. Should I add in someting in reference library or simply change the code below? The fillOptions() has problem.


Private Sub Form_Open(cancel As Integer)
' Minimize the database window and initialize the form.


' Move to the switchboard page that is marked as the default.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.FilterOn = True

End Sub


Private Sub Form_Current()
' Update the caption and fill in the list of options.


Me.Caption = Nz(Me![ItemText], "")
FillOptions

End Sub


Private Sub FillOptions()
' Fill in the options for this switchboard page.
On Error GoTo Err1


' The number of buttons on the form.
Const conNumButtons = 8

Dim con As Object
Dim rs As Object
Dim stSql As String
Dim intOption As Integer

' Set the focus to the first button on the form,
' and then hide all of the buttons on the form
' but the first. You can't hide the field with the focus.
Me![Option1].SetFocus
For intOption = 2 To conNumButtons


Me("Option" & intOption).Visible = False
Me("OptionLabel" & intOption).Visible = False
Next intOption

' Open the table of Switchboard Items, and find
' the first item for this Switchboard Page.
Set con = Application.CurrentProject.Connection
stSql = "SELECT * FROM [Switchboard Items]"
stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & Me![SwitchboardID]
stSql = stSql & " ORDER BY [ItemNumber];"
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1 ' 1 = adOpenKeyset

' If there are no options for this Switchboard Page,
' display a message. Otherwise, fill the page with the items.
If (rs.EOF) Then
Me![OptionLabel1].Caption = "There are no items for this switchboard page"
Else
While (Not (rs.EOF))
Me("Option" & rs![ItemNumber]).Visible = True
Me("OptionLabel" & rs![ItemNumber]).Visible = True
Me("OptionLabel" & rs![ItemNumber]).Caption = rs![ItemText]
rs.MoveNext
Wend
End If


' Close the recordset and the database.
rs.Close
Set rs = Nothing
Set con = Nothing
Err1:
MsgBox Err.Number & Err.Description

End Sub

[ranman256]

inside access there is no need for building connections. Those are for external data like Sql Server data.

just make the sql and run it with: docmd.runSql sSql

[dollygg]

The same code works in MS office LTSC , but not MS office 365. I change it to the following code but still not working

Set rs = CurrentDb.OpenRecordset(stSql)

[Minty]

Please put cut in posts within Code tags (Use the # in the menu)

I'd be amazed if this line works when you assign a recordset to it.
Dim rs As Object

Add Option Explicit at the top of you code modules and try to compile it?
What does "Doesn't work" mean - what is the error message and what line gets highlighted in the code

[dollygg]

no lucks. "Doesn't work" means no menu items showing on the user form. No error message either. The FillOptions() did not get called at all.

[dollygg]

I am not allowed to add Reference library "Microsoft DAO 3.6 object library" . the error message is "err in loading DLL".

[Gicu]

Can you try this updated code:

Private Sub FillOptions()
' Fill in the options for this switchboard page.
On Error GoTo Err1


' The number of buttons on the form.
Const conNumButtons = 8


'Dim con As Object
Dim db as DAO.Database
Dim rs As DAO.Recordset   'Object
Dim stSql As String
Dim intOption As Integer


' Set the focus to the first button on the form,
' and then hide all of the buttons on the form
' but the first. You can't hide the field with the focus.
Me![Option1].SetFocus
For intOption = 2 To conNumButtons
Me("Option" & intOption).Visible = False
Me("OptionLabel" & intOption).Visible = False
Next intOption


' Open the table of Switchboard Items, and find
' the first item for this Switchboard Page.
'Set con = Application.CurrentProject.Connection
stSql = "SELECT * FROM [Switchboard Items]"
stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & Me![SwitchboardID]
stSql = stSql & " ORDER BY [ItemNumber];"
Set db=CurrentDb
Set rs = db.openrecordset(stSql) 'CreateObject("ADODB.Recordset")
'rs.Open stSql, con, 1 ' 1 = adOpenKeyset


' If there are no options for this Switchboard Page,
' display a message. Otherwise, fill the page with the items.
If rs.recordcount=0 Then  '(rs.EOF) Then
	Me![OptionLabel1].Caption = "There are no items for this switchboard page"
Else
	While (Not (rs.EOF))
		Me("Option" & rs![ItemNumber]).Visible = True
		Me("OptionLabel" & rs![ItemNumber]).Visible = True
		Me("OptionLabel" & rs![ItemNumber]).Caption = rs![ItemText]
	rs.MoveNext
Wend
End If




' Close the recordset and the database.
rs.Close
Set rs = Nothing
Set db=Nothing
'Set con = Nothing
Err1:
MsgBox Err.Number & Err.Description
End Sub

The DAO 3.6 library is now incorporated into the Access database engine library so no longer needed.
Cheers,

[dollygg]

thanks for your reply! However, that FillOptions() didn't get called up at all. The same code ( .mdb file) works in MS office LTSC , but not MS office 365. It must be the reference library issue.
1.Visual basic for application 2.MS access 16.0 object library 3. MS office 16.0 object library 4. MS outlook 16.0 5. OLE automation 6.MS Excel 16.0 object library 7.Accessibilitycpladmin 1.0 type library

[Gicu]

Is any of your code working? Have you enabled Trusted Locations for the folder that hosts your front-end?
https://learn.microsoft.com/en-us/de...sted-locations
Cheers,

[dollygg]

The problem is , I am not allowed to add "Microsoft DAO 3.6 object library " in A.mdb, but I could do the same in B.mdb . (background: I have MS 365 installed so the Access if 64 bit)

[Gicu]

As I mentioned earlier that DAO library is now obsolete and was replaced with the Access Database Engine Object library:
https://social.msdn.microsoft.com/Fo...ss-2010-64-bit
https://answers.microsoft.com/en-us/...4-be8f53ef1f41
So I don't think that is the problem. Have you tried to put a break in the form current event and step through the code?

Cheers,

[dollygg]

There is a compile error now "user-defined type not defined" .

These are highlighted in yellow: "Dim db as dao.database Dim rs as DAO.recordset"

I can not add in "MS DAO3.6 object library" in a.mdb. this is cause of the issue. I could add the same in b.mdb file.

[Gicu]

You do not need\can't add a deprecated library that is not compatible with your current Access client! You need to add the Access Database Engine object library (in your case should be MS Access 16.0 - that has all the code from the old DAO 3.6 library and much more.

Cheers,

[dollygg]

Actually the a.mdb is working now.

[Gicu]

For others on this site that might run into the same problem do you care to share more what you did to make it work again?

Cheers,