How to Set Tabbed Documents = True??

[mrmmickle1]

I have this code that creates a Database. I am trying to set Tabbed Documents = True but cannot get it to work.... Could someone please point me in the right direction?

This is the code I have (See Below). The red line gets a Run Time Error 91: Object Variable or With Block Variable not set:

dbConnectStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBPath & ";"

[COLOR=#008000]    'Create new database[/COLOR]
    [COLOR=#0000ff]Set[/COLOR] Catalog = CreateObject("ADOX.Catalog")
    Catalog.Create dbConnectStr
[COLOR=#ff0000][B]    CurrentDb.Properties("ShowDocumentTabs").Value = True[/B][/COLOR]
    [COLOR=#0000ff]Set[/COLOR] Catalog = Nothing
    [COLOR=#0000ff]Call [/COLOR]SetAttr(DBPath, vbNormalNoFocus)

 

[TerryHogarth21]

Have you ever tried using DAO method that allows you to pretty much read or set any of the properties on access objects?

Found something similar to your question on here to which I think is related.
Setting "Tabbed Documents" via VBA on an MS Access 2010 database

Here's where that code comes from to which is also a very helpful reference.
Microsoft Access tips: DAO Programming Code Examples

 

[mrmmickle1]

TerryHogarth21,

Thanks for taking a look at my issue. Not really familiar with either method ADO or DAO. I only started fiddling around with Access a few days ago. I tried to use the SetPropertyDAO Function you referenced like this but was unsuccessful:

[COLOR=#008000]    'Create new database[/COLOR]
  [COLOR=#0000ff]  Set[/COLOR] Catalog = CreateObject("ADOX.Catalog")
    Catalog.Create dbConnectStr
[COLOR=#0000ff]    Call [/COLOR]SetPropertyDAO(CurrentDb, "ShowDocumentTabs", dbBoolean, [COLOR=#0000ff]True[/COLOR])
[COLOR=#0000ff]    Set[/COLOR] Catalog = [COLOR=#0000ff]Nothing[/COLOR]
[COLOR=#0000ff]    Call [/COLOR]SetAttr(dbPath, vbNormalNoFocus)

I also Tried:

[COLOR=#0000FF] Call [/COLOR]SetPropertyDAO(Db, "ShowDocumentTabs", dbBoolean, [COLOR=#0000ff]True[/COLOR])

The function kept going to the ErrHandler.....



I'm kind of befuddled to what I am actually looking to find. I know how to set the Property Manually:

File > Options > Current Database > Then Under Document Window Options Select Tabbed Documents (Radio Button)

Is there not a simple way to set this property to True? (I would normally just use the macro recorder to see what to do if I was in MS Excel, but I don't see this as an option in Access)

 

[TerryHogarth21]

When you are setting up your properties try adding this

 CurrentDb.Properties("UseMDIMode") = 0

That seems to work for me in Access 2013, The default in the Options -> Current Database is Overlapping.
However as you know just as a caution, if I just ran that code as is it won't impact the DB until I close and re-open it.

Bookmarked this site since it explains and shows more of each property type and what variables you should use for the references.
Microsoft Access tips: Prevent Access 2007 users modifying existing databases

Unfortunately there is no Macro Recorder for Access - welcome to the world of Microsoft.

 

[mrmmickle1]

TerryHogarth21,

Thanks so much for the reply. I really appreciate you helping me out! As I mentioned I am unfamiliar with Access VBA. This is the Code I use to Create the Database (I know it is probably Overkill. I just wanted to make sure I'm not missing something simple and that the context that I am using this in is accurate to your advice):

[COLOR=#0000ff]Option Explicit[/COLOR]

[COLOR=#0000ff]Const[/COLOR] adOpenKeyset = 1
[COLOR=#0000ff]Const[/COLOR] adCmdTable = 2
[COLOR=#0000ff]Const[/COLOR] adLockOptimistic = 3
[COLOR=#0000ff]Public[/COLOR] gadoConnection             [COLOR=#0000ff]As Object[/COLOR]
[COLOR=#0000ff]Public[/COLOR] gadoRecordset             [COLOR=#0000ff] As Object[/COLOR]
[COLOR=#0000ff]Public[/COLOR] DBTableArr()               [COLOR=#0000ff]As Variant
[/COLOR]
[COLOR=#0000ff]Private Function[/COLOR] GetDBPath() [COLOR=#0000ff]As String[/COLOR]
    GetDBPath = "C:\Users\" & Environ("UserName") & "\Desktop\"
[COLOR=#0000ff]End Function[/COLOR]

[COLOR=#0000ff]Private Function [/COLOR]GetDBName() [COLOR=#0000ff]As String[/COLOR]
    GetDBName = "PendingLog_" & Format(Date, "MM.DD.YYYY") & ".accdb"
[COLOR=#0000ff]End Function[/COLOR]

[COLOR=#0000ff]Private Sub[/COLOR] CreateDatabaseAndTables()[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] dbConnectStr           [COLOR=#0000ff] As String[/COLOR]
  [COLOR=#0000ff]  Dim[/COLOR] Catalog                 [COLOR=#0000ff]As Object[/COLOR]
   [COLOR=#0000ff] Dim[/COLOR] cnt                     [COLOR=#0000ff]As Object[/COLOR]
   [COLOR=#0000ff] Dim [/COLOR]dbPath                  [COLOR=#0000ff]As String[/COLOR]
    [COLOR=#0000ff]Dim[/COLOR] strSql                 [COLOR=#0000ff] As String[/COLOR]
    [COLOR=#0000ff]Dim[/COLOR] i                      [COLOR=#0000ff] As Long[/COLOR]
     
    DBTableArr = Array("AllSamples", "Genetics", "Reprep", "RapidFire", "Quest_SendOut", "Needs_Screening", "Needs_Data", "Clerical_Review", "Compliance", "Other")
    dbPath = GetDBPath & GetDBName
[COLOR=#0000ff]    On Error Resume Next[/COLOR]
   [COLOR=#0000ff] Call [/COLOR]SetAttr(dbPath, vbNormal)
[COLOR=#0000ff]    Call[/COLOR] Kill(dbPath)
    [COLOR=#0000ff]On Error GoTo 0[/COLOR]: Err.Clear
    
    dbConnectStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath & ";"

[COLOR=#008000]    'Create new database[/COLOR]
[COLOR=#0000ff]    Set[/COLOR] Catalog = CreateObject("ADOX.Catalog")
    Catalog.Create dbConnectStr
  [COLOR=#0000ff]  Set [/COLOR]Catalog = [COLOR=#0000ff]Nothing[/COLOR]
  [COLOR=#0000ff]  Call [/COLOR]SetAttr(dbPath, vbNormalNoFocus)

[COLOR=#008000]    'Connect to database and insert a new table[/COLOR]
[COLOR=#0000ff]    Set [/COLOR]cnt = CreateObject("ADODB.Connection")
    [COLOR=#0000ff]    With [/COLOR]cnt
           
             .Open dbConnectStr
             
              [COLOR=#0000ff]   For[/COLOR] i = [COLOR=#0000ff]LBound[/COLOR](DBTableArr) [COLOR=#0000ff]To[/COLOR] [COLOR=#0000ff]UBound[/COLOR](DBTableArr)
[COLOR=#008000]                      'Create All Tables[/COLOR]
                      strSql = vbNullString
                      strSql = "CREATE TABLE " & DBTableArr(i) & " ([MR_Num] TEXT(20)"
                      strSql = strSql & vbLf & ", [Chart_Number] TEXT(12)"
                      strSql = strSql & vbLf & ", [Clinic_Location] TEXT(75)"
                      strSql = strSql & vbLf & ", [Last_Name] TEXT(25)"
                      strSql = strSql & vbLf & ", [First_Name] TEXT(25)"
                      strSql = strSql & vbLf & ", [Date_Received] DATETIME"
                      strSql = strSql & vbLf & ", [Sales_Rep] TEXT(25)"
                      strSql = strSql & vbLf & ", [Hold_Reason] TEXT(200)"
                      strSql = strSql & vbLf & ", [Pending_Days] INTEGER"
                      strSql = strSql & vbLf & ", [Notes] TEXT(200));"
                      .Execute strSql
                 
                [COLOR=#0000ff] Next[/COLOR] i
                                           
             .Close
              
[COLOR=#0000ff]        End With[/COLOR]
  
[COLOR=#008000]    'Releasing Memory[/COLOR]
    strSql = vbNullString
    dbConnectStr = vbNullString
    [COLOR=#0000ff]Set [/COLOR]Catalog = [COLOR=#0000ff]Nothing[/COLOR]
    [COLOR=#0000ff]Set[/COLOR] cnt = [COLOR=#0000ff]Nothing[/COLOR]
    dbPath = vbNullString
 [COLOR=#0000ff]   Erase[/COLOR] DBTableArr
    i = [COLOR=#0000ff]Empty[/COLOR]
    
[COLOR=#0000ff]End Sub[/COLOR]

I am unsure where I should insert the line of code you mentioned. I tried to insert it in a few places as is like this:

CurrentDb.Properties("UseMDIMode") = 0

.... I keep getting "Error 91:- Object variable or with block variable not set."

I also tried to do something like this:

[COLOR=#0000ff]Dim [/COLOR]Db [COLOR=#0000ff]As Object[/COLOR]

[COLOR=#0000ff]Set[/COLOR] Db = CurrentDb
Db.Properties("UseMDIMode") = 0

I also tried the same methodology with the Function Call.... Where am I going wrong?

I appreciate the links as well. I skimmed through them. I will go back through and read them more in depth this evening....

 

[TerryHogarth21]

Where is the line of code that fails when you step through? Is this related to the line I gave you or something else?

If you first set your current database to have the Document Window Option of - Overlapping, then close out the DB and reopen.

Now add that line of code i gave you by itself like this and run it. When you check your options does it change the option to have Tabbed?

Function test()
CurrentDb.Properties("UseMDIMode") = 0
End Function

</pre>

 

[mrmmickle1]

I would put the code line around here in the above code:

[COLOR=#008000] 'Create new database
[/COLOR][COLOR=#0000ff]    Set[/COLOR] Catalog = CreateObject("ADOX.Catalog")
    Catalog.Create dbConnectStr 
 [COLOR=#0000ff]  Set [/COLOR]Catalog = [COLOR=#0000ff]Nothing[/COLOR] 
 [COLOR=#0000ff]  Call [/COLOR]SetAttr(dbPath, vbNormalNoFocus)

[B][COLOR=#ff0000]CurrentDb.Properties("UseMDIMode") = 0[/COLOR][/B]

I can open up the database manually after I create it and run this line of code:

[COLOR=#000000]CurrentDb.Properties("UseMDIMode") = 0
[/COLOR]

and it does what it is supposed to do. I just have to close the database and open it back up to take effect. I did the same thing with the function you provided and it will work too.

I would like to do it while I am creating the database. This way when I open it up it will already be set and I don't manually have to run the code. Does that make sense??

 

[mrmmickle1]

I tried this last night to see if the Code recognized CurrentDb. After this test I believe it does (This is a small snippet of the larger Code Above):

[COLOR=#008000]    'Create new database[/COLOR]
   [COLOR=#0000ff] Set[/COLOR] Catalog = CreateObject("ADOX.Catalog")
    Catalog.Create dbConnectStr
   [COLOR=#0000ff] Set[/COLOR] Catalog = Nothing
    Call SetAttr(dbPath, vbNormalNoFocus)
    [COLOR=#0000ff]With[/COLOR] CurrentDb[COLOR=#008000] 'Passes This Line and Therefore I believe it recognizes CurrentDb.....[/COLOR]
    .Properties("UseMDIMode") = 0 [COLOR=#ff0000]'"Error 91:- Object variable or with block variable not set."[/COLOR]
[COLOR=#0000ff]    End With[/COLOR]

Still not able to get this code to function . Seems strange... Am I not declaring something properly??

Do I need to Reference something other then Microsoft Access 15.0 Object Library??

Like I said if I manually open the database and use this line:

CurrentDb.Properties("UseMDIMode") = 0

It works fine......

 

[xenou]

Hi,

I don't know the answer but the posts are very confused about whether we are using DAO or ADO. DO you have a link? I can't see experts_exchange (either I don't have membership or I've got it filtered out by my internet security) so if they have an answer there can we repost it here? (i.e., I can't find any concrete examples that work as of yet).

 

[mrmmickle1]

xenou,

Thank you for the response. I will try to address your inquiries.

Here is the Discussion on the Experts Exchange Website:

Kyle's Question:
To whom it may concern:
Could you tell me how to programmatically (VBA) set an MS Access 2010 database to "Tabbed Documents" instead of "Overlapping Windows"? I am creating a new MS Access 2010 database and I would like it to have this feature when a user opens the database. I have tried the two sets of code below and they both don't seem to work. I am not sure why either.

Try 1:

[COLOR=#000000]    Call SetPropertyDAO(db, "AllowDatasheetSchema", dbBoolean, False, strMsg)[/COLOR]
[COLOR=#000000]    Call SetPropertyDAO(db, "DesignWithData", dbLong, mlngcAllowLayoutView, strMsg)[/COLOR]

[COLOR=#008000]    'Preferences for child windows.[/COLOR]
[COLOR=#000000]Call SetPropertyDAO(db, "UseMDIMode", dbByte, 0, strMsg)[/COLOR]
[COLOR=#000000]    Call SetPropertyDAO(db, "ShowDocumentTabs", dbBoolean, True, strMsg[/COLOR][COLOR=#008000])[/COLOR]

Try 2:

[COLOR=#000000]  db.Properties("UseMDIMode") = 0[/COLOR]

Thank you for your help.

Scott's Response:
What does "SetProperty" do?
Remember that many of the Access properties do not exist until needed, so often you must create the property if it's missing, so your "ShowDocumentTabs" property may not yet exist.


Kyle's Response:
Sorry about that. Here are the procedures that are missing from my explanation above.

Private Function SetPropertyDAO(obj As Object, strPropertyName As String, intType As Integer, _
    varValue As Variant, Optional strErrMsg As String) As Boolean
On Error GoTo ErrHandler
    'Purpose:   Set a property for an object, creating if necessary.
    'Arguments: obj = the object whose property should be set.
    '           strPropertyName = the name of the property to set.
    '           intType = the type of property (needed for creating)
    '           varValue = the value to set this property to.
    '           strErrMsg = string to append any error message to.


    If HasProperty(obj, strPropertyName) Then
        obj.Properties(strPropertyName) = varValue
    Else
        obj.Properties.Append obj.CreateProperty(strPropertyName, intType, varValue)
    End If
    SetPropertyDAO = True


ExitHandler:
    Exit Function


ErrHandler:
    strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not set to " & varValue & _
        ". Error " & Err.Number & " - " & Err.Description & vbCrLf
    Resume ExitHandler
End Function


Private Function HasProperty(obj As Object, strPropName As String) As Boolean
    'Purpose:   Return true if the object has the property.
    Dim varDummy As Variant


    On Error Resume Next
    varDummy = obj.Properties(strPropName)
    HasProperty = (Err.Number = 0)
End Function

Scott's Response:
What is the value of "db"? That would need to be set to CurrentDb, or something of that nature.
Otherwise, the code looks like it should work.

Kyle's Response:
db is the database that I want the "Tabbed Documents" to be set on, which is not the current database. If I set db to the current db, will the database I create from it be set as "Tabbed Documents"?

Scott's Response:
Yes, it should. But assuming you've set the "db" variable correctly, the code you show should work.
Can you set a breakpoint and see what's happening in the SetPropertyDAO code?

Kyle's Response:
I figured out what I had done incorrectly. The code worked correctly. I thought I had the CurrentDb set correctly, but I didn't. Thanks for your help.

By taking what you said into context it looks like I was trying to use both ADO and DAO which is probably the issue. I believe I tried to use the DAO method of setting the tabbed documents = True. However, it would not work. I believe I may need the ADO method of setting tabbed documents = True. Not sure though... Hopefully this information will help. It looks like Kyle's code came from the original website you referenced regarding Allen Browne's examples.


I appreciate your input on this matter!