UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Vba Open Database In Design View, Access 2007    
 
   
pdanes
post May 10 2020, 06:33 AM
Post#21



Posts: 108
Joined: 19-June 10



To complete the thread, in case someone in the future might be interested, here's the final version. Field names for the table of stored column values are not in English, but I'm sure what is going on is clear enough based on the control properties, which are in English. Works great.

The Get routine is called from each form's datasheet subform's Close event, via
CODE
GetDSColumnWidths Me

The Set routine is called from each form's datasheet subform's Open event, via
CODE
SetDSColumneWidths Me

and from the data import form, which reads in the user's last saved datasheet column settings, using global variable names pointing to the datasheet forms, via
CODE
SetDSColumneWidths FD_DSC_F
SetDSColumneWidths FL_DSC_F

And here are the two routines that do the work. There are two datasheet subforms in this app, and each has its own set of column properties, indicated by the datasheet name field. On close, all old properties are deleted and the current ones saved.
On load, or during an import from the user's active version into my newly delivered empty version, the previously written properties are read in and set.
CODE
Public Sub SetDSColumneWidths(ByRef frm As Access.Form)
Dim rst As DAO.Recordset, ctl As control
Set rst = CurrentDb.OpenRecordset("Select Policko, Schovany, Poradi, Sirka From DataSheetRozlozeni Where DataSheet = '" & frm.Name & "'", dbOpenSnapshot)
On Error Resume Next
With rst
    Do Until .EOF
        Set ctl = frm.Controls(.Fields("Policko"))
        ctl.ColumnHidden = .Fields("Schovany")
        ctl.ColumnOrder = .Fields("Poradi")
        ctl.ColumnWidth = .Fields("Sirka")
        .MoveNext
    Loop
    .Close
End With
Set rst = Nothing
On Error GoTo 0
End Sub
CODE
Public Sub GetDSColumnWidths(ByRef frm As Access.Form)
Dim rst As DAO.Recordset, ctl As control, x$
With CurrentDb
    .Execute "Delete * From DataSheetRozlozeni Where DataSheet = '" & frm.Name & "'"
    Set rst = .OpenRecordset("DataSheetRozlozeni", dbOpenDynaset)
End With
x = frm.Name
With rst
    For Each ctl In frm.Controls
        If ctl.ControlType <> acLabel Then
            .AddNew
            .Fields("DataSheet") = x
            .Fields("Policko") = ctl.Name
            .Fields("Schovany") = ctl.ColumnHidden
            .Fields("Poradi") = ctl.ColumnOrder
            .Fields("Sirka") = ctl.ColumnWidth
            .Update
        End If
    Next ctl
    .Close
End With
Set rst = Nothing
End Sub

This post has been edited by pdanes: May 10 2020, 06:36 AM
Go to the top of the page
 
cheekybuddha
post May 10 2020, 06:43 AM
Post#22


UtterAccess Moderator
Posts: 13,042
Joined: 6-December 03
From: Telegraph Hill


Why do you have to open in design view?

Those properties are available in normal view too.

--------------------


Regards,

David Marten
Go to the top of the page
 
tina t
post May 10 2020, 12:35 PM
Post#23



Posts: 6,689
Joined: 11-November 10
From: SoCal, USA


QUOTE
There is a substantial conceptual difference between one file and anything more than one file. It's not a matter of copying speed, it's more that EVERY time he moves, he has to remember two things.

well, the easy solution to that is to put both BE and FE dbs in one folder. then just copy the one folder from here to there to there to wherever.

all the advantages of having a separate FE for revisions, enhancements, etc to the user interface, and none of the disadvantages of having to copy two files.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
pdanes
post May 11 2020, 03:43 AM
Post#24



Posts: 108
Joined: 19-June 10



QUOTE
Why do you have to open in design view?

Those properties are available in normal view too.

If you read from the beginning, you'll find that I am loading data from an old database into a new version. I don't want the form open at all - neither in design nor in normal. I just want to read some stored properties from it, but that turned out to not be possible, or at least, nobody on here, incuding me, could figure out how to do it.
This post has been edited by pdanes: May 11 2020, 03:50 AM
Go to the top of the page
 
pdanes
post May 11 2020, 03:49 AM
Post#25



Posts: 108
Joined: 19-June 10



QUOTE
well, the easy solution to that is to put both BE and FE dbs in one folder. then just copy the one folder from here to there to there to wherever.

Sure, that would also work. Of course, there is then the possibility for the user to load extra junk into that folder, or accidentally forget that he has to copy the entire folder. It still suffers from all the disadvantages of having more than one file.

The benefits of a split database do not, in this case, outweigh the benefits of a single-file configuration.
This post has been edited by pdanes: May 11 2020, 03:51 AM
Go to the top of the page
 
cheekybuddha
post May 11 2020, 04:29 AM
Post#26


UtterAccess Moderator
Posts: 13,042
Joined: 6-December 03
From: Telegraph Hill


And you say your db contains no code?

--------------------


Regards,

David Marten
Go to the top of the page
 
pdanes
post May 11 2020, 06:05 AM
Post#27



Posts: 108
Joined: 19-June 10



QUOTE
And you say your db contains no code?

Good Christ, no. I wrote nothing of the sort - where did you get that idea? Quite the opposite - the biggest problem is that when I try to read from the old version, code is run in that old version automatically, which I don't want.
Go to the top of the page
 
cheekybuddha
post May 11 2020, 06:52 AM
Post#28


UtterAccess Moderator
Posts: 13,042
Joined: 6-December 03
From: Telegraph Hill


OK, I think you are there already!

1. Use a table that you have created to store the datasheet properties. If you name it with prefix 'usys' it will be hidden and not be visible to the user in their list of tables (eg 'usysColProps')

2. For the datasheet forms concerned, use the Load and Unload events to grab/and apply/write the required properties to the table.

3. When it comes to updating versions, simply import the data from your properties table along with all the other data. You can do this without opening the database, so the forms won't open and run any code (I think you already know this - if not, see here).

With the code that write the props to your table, I would suggest adding logic to see if the properties already exist and use UPDATE queries instead of deleting all and writing afresh - this will reduce bloat in your db.

With your code that applies the props to the form on open, if you experience screen flicker, adjust your code like:
CODE
Public Sub SetDSColumneWidths(ByRef frm As Access.Form)
Dim rst As DAO.Recordset, ctl As control
Set rst = CurrentDb.OpenRecordset("Select Policko, Schovany, Poradi, Sirka From DataSheetRozlozeni Where DataSheet = '" & frm.Name & "'", dbOpenSnapshot)
On Error Resume Next
With rst
    Do Until .EOF
        Set ctl = frm.Controls(.Fields("Policko"))
        If Not ctl.ColumnHidden = .Fields("Schovany") Then ctl.ColumnHidden = .Fields("Schovany")
        If Not ctl.ColumnOrder = .Fields("Poradi") Thenctl.ColumnOrder = .Fields("Poradi")
        If Not ctl.ColumnWidth = .Fields("Sirka") Then ctl.ColumnWidth = .Fields("Sirka")
        .MoveNext
    Loop
    .Close
End With
Set ctl = Nothing
Set rst = Nothing
On Error GoTo 0
End Sub


I agree, in the situation that you describe, that splitting the db is of little value.

Good luck with your project, thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
pdanes
post May 11 2020, 07:54 AM
Post#29



Posts: 108
Joined: 19-June 10



QUOTE
OK, I think you are there already!

Pretty much, but it never hurts to have another viewpoint.

QUOTE
1. Use a table that you have created to store the datasheet properties. If you name it with prefix 'usys' it will be hidden and not be visible to the user in their list of tables (eg 'usysColProps')

That's a good trick - didn't know about that one.

QUOTE
2. For the datasheet forms concerned, use the Load and Unload events to grab/and apply/write the required properties to the table.

I've been using the Open and Close events - seems to work fine.

QUOTE
3. When it comes to updating versions, simply import the data from your properties table along with all the other data. You can do this without opening the database, so the forms won't open and run any code (I think you already know this - if not, see here).

Yes, I already do that for all the data, but I crashed and burned trying to read settings directly from the datasheet form, hence this entire thread.

QUOTE
With the code that write the props to your table, I would suggest adding logic to see if the properties already exist and use UPDATE queries instead of deleting all and writing afresh - this will reduce bloat in your db.

You're right, that would be cleaner.

QUOTE
With your code that applies the props to the form on open, if you experience screen flicker, adjust your code like:

Yes, that's a better way. I don't understand why Access doesn't do this 'under the covers' - if a property already contains the exact value being loaded, especially a graphic property, just leave it alone. Pointless screen flicker is one of my pet peeves with Access.

QUOTE
Good luck with your project

Thanks, it's running nicely. Actually, it's been in service for around ten years, but both I and the user are constantly coming up with new research ideas and tweaks to make things smoother, faster, more relevant to new conditions. This has been a minor irritant for quite a while, and I'm glad I finally got around to fixing it. Appreciate the tips.
Go to the top of the page
 
theDBguy
post May 11 2020, 08:19 AM
Post#30


UA Moderator
Posts: 78,481
Joined: 19-June 07
From: SunnySandyEggo


QUOTE (pdanes)
I just want to read some stored properties from it, but that turned out to not be possible, or at least, nobody on here, incuding me, could figure out how to do it.

Hi. My bad! I asked to see your code and somehow I missed it when you posted it in post #11. The reason you couldn't do it is because you were using OpenCurrentDatabase instead of just OpenDatabase. Not sure it that still matters now, but I just wanted to mention it. Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
pdanes
post May 11 2020, 08:39 AM
Post#31



Posts: 108
Joined: 19-June 10



QUOTE
The reason you couldn't do it is because you were using OpenCurrentDatabase instead of just OpenDatabase.

All right, I'll give that a try, but I think there was something with that where I couldn't then open forms, only tables. I'll have to try it again, but I'm at work now, so it'll have to wait a bit.
Go to the top of the page
 
theDBguy
post May 11 2020, 08:52 AM
Post#32


UA Moderator
Posts: 78,481
Joined: 19-June 07
From: SunnySandyEggo


Sounds good. Let us know how it goes. Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
pdanes
post Jun 7 2020, 10:34 AM
Post#33



Posts: 108
Joined: 19-June 10



Well, I looked at it. No luck - OpenDatabase is not a method of the Access.Application object. OpenDatabase is only available in workspaces, which gives access to the data. I don't need that - I need to read properties from the class definition of a form. The only methods that do that also activate the form's code module. If there is a way to read the properties of a form without waking it, it's well hidden.

I suppose it make sense, in a way. Forms are not stored as graphic objects, but as definitions of a class module. There is no way to get properties from a class module without creating an instance of it, which necessarily also runs the Class_Initialize code. Forms apparently work the same way in that as well.

My original objective is probably a lost cause. Storing column settings on the Form_Close event seems to be the way to go. It works and is simple enough. It's not exactly the WAY I wanted to do it, but that's just too bad. I have a functional setup and learned something along the way. That's going to have to be good enough.
Go to the top of the page
 
theDBguy
post Jun 7 2020, 10:41 AM
Post#34


UA Moderator
Posts: 78,481
Joined: 19-June 07
From: SunnySandyEggo


Hi. Sorry to hear that. Thanks for the update. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    11th July 2020 - 05:11 AM