Can't get Stephan Lebans save-restore-modify relationships to work

[terrybell105@gmail.com]

I downloaded Stephan's utility from his website but can't get it to
work - or maybe I'm not driving it properly!
The form works OK with the existing 3 "views" - I can switch between
them and they display changes fine.
But if I add or remove a table from any one of them, strange things
happen.
For example: start with a fresh copy of the downloaded database.
Open the form and click on the "Admin" view.
Right click and click on "Show Table"
Select CustomerSales and Add.
Close the Show Table Dialog box. You'll see the new table added to
the view.
Now try selecting another view, say RelationshipsVi ew1.
On my machine, the new table is stuck on it too.
The same with RelationshipsVi ew2, it is hidden behind customer_4.
Switch back to Admin view.
Assign a new name in the Unique Id text box and click to "Save the
current relationship layout".
Close and reopen the form. The change has disappeared!
If I saved the relationship window , the extra table does get saved,
but when you look at all the views, it's stuck on all of them again.
I guess I must be doing something horribly wrong, because I can't see
anyone else having these problems.

Although, looking at the code, it doesn't look like it's clearing the
relationship window before applying a new view - just repositioning
existing windows.


Would like to get it working - useful for large databases ...
TIA
Terry Bell

[Stephen Lebans]

Re: Can't get Stephan Lebans save-restore-modify relationships to work

What version of Access and Windows are you using?

--

HTH
Stephen Lebans

http://www.lebans.com

Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


<terrybell105@g mail.comwrote in message
news:a414d1f3-5a65-434c-90e9-3abe85395818@a3 g2000prm.google groups.com...

>I downloaded Stephan's utility from his website but can't get it to
work - or maybe I'm not driving it properly!
The form works OK with the existing 3 "views" - I can switch between
them and they display changes fine.
But if I add or remove a table from any one of them, strange things
happen.
For example: start with a fresh copy of the downloaded database.
Open the form and click on the "Admin" view.
Right click and click on "Show Table"
Select CustomerSales and Add.
Close the Show Table Dialog box. You'll see the new table added to
the view.
Now try selecting another view, say RelationshipsVi ew1.
On my machine, the new table is stuck on it too.
The same with RelationshipsVi ew2, it is hidden behind customer_4.
Switch back to Admin view.
Assign a new name in the Unique Id text box and click to "Save the
current relationship layout".
Close and reopen the form. The change has disappeared!
If I saved the relationship window , the extra table does get saved,
but when you look at all the views, it's stuck on all of them again.
I guess I must be doing something horribly wrong, because I can't see
anyone else having these problems.
>
Although, looking at the code, it doesn't look like it's clearing the
relationship window before applying a new view - just repositioning
existing windows.
>
>
Would like to get it working - useful for large databases ...
TIA
Terry Bell

[terrybell105@gmail.com]

Re: Can't get Stephan Lebans save-restore-modify relationships towork

Xp, Access 2003 - I think service pack 3 but not sure, I'm at home
now ...

[Stephen Lebans]

Re: Can't get Stephan Lebans save-restore-modify relationships to work

Save the RelationShip view in Acccess before using my Tools to manipulate
the window.

--

HTH
Stephen Lebans

http://www.lebans.com

Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


<terrybell105@g mail.comwrote in message
news:8ffb3392-1479-437b-94fd-c15ff668f220@n3 3g2000pri.googl egroups.com...

Xp, Access 2003 - I think service pack 3 but not sure, I'm at home
now ...
>

[terrybell105@gmail.com]

Re: Can't get Stephan Lebans save-restore-modify relationships towork

Stephen I've tried your suggestion, it still doesn't seem to work
properly.

I've spent some time changing your test tables to illustrate the
problem, and I've set up a vb function to prepare the test data. If
you execute the vb code below against your download database, it will
do the following:
- Add a new table tblAddress(Addr essId,Address)
- Delete all the current relationships (I've done this because you
have a few self-joins in there that complicate the display)
- Added new column Customer.Addres sId as a foreign key to the new
address table.
- Added two relations
- one between Customer and CustomerSales, as you originally had.
I've made this a dbRelationDontE nforce type because your data won't
allow enforce.
- the other between tblAddress and Customer
-It also deletes the contents of tblRelationship Views so we can start
with a clean plate.

To reproduce the problem:
1. Run the code
2. Open and clear the Relationship window.
3. Add all three tables to the window. They will show the relation
lines.
4. Save the Layout (as you suggested)
5. Open your form.
6. Enter Unique Id "AllThree" and click the Save button.
The layout seems to be saved OK
7. Now close the form and open the Relationships window again.
8. Delete one of the tables (eg tblAddress) and save the layout.
9. Leave the layout visble, we want to save this as a second view.
10. Open your form again.
11. Click on the "AllThree" view.

On my machine (incidentally - this one is Win2K and A2K) tblAddress
has disappeared.

Stephen I'm probably having a huge senior's moment over this - very
worrying - so please be patient - but it looks to me like the tool
doesn't seem to be handling different sets of tables correctly.
Changes in table positioning are OK.

I have a large database that I want to split into views for review and
this would be ideal.

Thanks for your time. By the way I've used lots of code from your
site over the years and have the greatest respect for your work -
you've saved me lots of time.

Terry Bell



'***Start code
Option Compare Database
Option Explicit

Function SetupTestCondit ions()
Dim Sql, rel As Relation, db As Database, fld As Field, tdf As
TableDef, i As Long

'Create new table tblAddress
Set db = CurrentDb()
Sql = "CREATE TABLE tblAddress (AddressId INTEGER CONSTRAINT
AddressIdPK PRIMARY KEY,Address TEXT)"
db.Execute Sql, dbFailOnError

'Delete existing relations, self joins complicate the display
For i = db.Relations.Co unt - 1 To 0 Step -1
db.Relations.De lete db.Relations(i) .Name
Next

'Delete original test relationship diagrams from tblRelationship Views
db.Execute "Delete from tblRelationship Views", dbFailOnError

'Add column AddressId to Customer
Set tdf = db.TableDefs("C ustomer")
tdf.Fields.Appe nd tdf.CreateField ("AddressId" , dbLong)

'Create relationship tblAddress to Customer
Set rel = db.CreateRelati on("AddressToCu stomer", "tblAddress ",
"Customer", dbRelationUpdat eCascade)
rel.Fields.Appe nd rel.CreateField ("AddressId" )
rel.Fields!Addr essId.ForeignNa me = "AddressId"
db.Relations.Ap pend rel

'Create relationship Customer to CustomerSales.
'Don't try to enforce Ri, Customer 1 doesn't exist.
Set rel = db.CreateRelati on("CustomerToC ustomerSales", "Customer",
"CustomerSales" , dbRelationDontE nforce)
rel.Fields.Appe nd rel.CreateField ("CustomerId ")
rel.Fields!Cust omerId.ForeignN ame = "CustomerId "
db.Relations.Ap pend rel

MsgBox "Done"

End Function
'*** End Code

[terrybell105@gmail.com]

Re: Can't get Stephan Lebans save-restore-modify relationships towork

Stephen ... have you had a chance to look at this?