Get the Sub-Subform You've Always Wanted (and Other Tricks) Access won't let you have a subform within a subform if the data has to be displayed in a continuous form. But Peter Heskett found a way by using senior and junior subforms. He also shares a few other tricks for handling forms. (MSDN Newsletter)
1: Relinking Access tables really isn't difficult, and the technique
2: required hasn't changed in a long time. I'm still using DAO code
3: I got from Ken Getz several years ago to accomplish this task.
4: Here's a small code fragment to automate the relink process:
5:
6: Dim db As DAO.Database
7: Dim tdf As DAO.TableDef
8: Dim strPath As String
9: strPath = "C:\Program Files\Microsoft Office\Office\Samples\northwind.mdb;"
10: Set db = CurrentDb()
11: For Each tdf In db.TableDefs
12: If (tdf.Attributes AND dbAttachedTable) = _
13: dbAttachedTable Then
14: tdf.Connect = ";DATABASE=" & strPath
15: tdf.RefreshLink
16: End If
17: Next
18:
19: Access uses the tabledef's Connect property to locate the back-end
20: database, and hook into the table within that database. Simply
21: setting the Connect property is not enough. The RefreshLink method
22: instructs Access (actually, Jet!) to rebuild the link to the table.
23:
24: Notice also how the tabledef's Attributes property is ANDed with
25: dbAttachedTable to determine whether the table is linked. This is
26: a good example of a technique I discussed a couple of months ago
27: for testing whether and ADO Connect object is open. The bitwise
28: AND operator (not to be confused with the logical And used in
29: "If..Then..Else constructs) returns the left-side value if it is
30: included in the right-side value. 2 AND 3 returns 2, and 4 AND 5
31: returns 4, but 2 AND 5 returns zero. It gets confusing, but using
32: intrinsic constants such as dbAttachedTable considerably clarifies
33: the statement.
34:
35: It deserves mentioning that the linked tables do not all have to
36: reside in the same back-end MDB. You could use multiple back-end
37: databases, if necessary.
38:
39: At different times I've recommended to people that their applications
40: routinely break links as the database shuts down, then automatically
41: rebuild the links as it starts up again. Here's some code for deleting
42: linked tables without deleting local tables that must stay in the
43: database:
44:
45: Public Sub DeleteLinks()
46: Dim tdf As DAO.TableDef
47: For Each tdf In CurrentDb.TableDefs
48: If Left(tdf.Name, 4) <> "MSys" _
49: And (tdf.Attributes And dbAttachedTable) = dbAttachedTable Then
50: CurrentDb.TableDefs.Delete tdf.Name
51: End If
52: Next tdf
53: End Sub
54:
55: For instance, you might use the DeleteLinks function to break
56: all existing linked tables and rebuild them pointing at a different
57: back-end database.
58:
59:
1: Option Compare Database
2: Option Explicit
3:
4: Private Sub Command7_Click()
5: Dim MYIMAGE As String
6: Dim CNN As ADODB.Connection
7: Dim RST As New ADODB.Recordset
8: Dim stDocName As String
9:
10: stDocName = "PrintImagesAuto"
11: DoCmd.SelectObject acForm, stDocName, True
12:
13: 'On Error Resume Next
14: Set CNN = CurrentProject.Connection
15: RST.Open "IMAGES", CNN, adOpenDynamic, adLockOptimistic ', adCmdTableDirect
16: RST.MoveFirst
17:
18: Do Until RST.EOF
19: If RST.EOF Then
20: Exit Sub
21: End If
22:
23: MYIMAGE = RST!Folder
24: Me![Image2].Picture = MYIMAGE
25:
26: 'Print this form
27: DoCmd.PrintOut
28:
29:
30: RST.MoveNext
31: Loop
32:
33: CNN.Close
34: ' RST.Close
35: Set CNN = Nothing
36: Set RST = Nothing
37: DoCmd.SelectObject acForm, stDocName, False
38: End Sub
Access 2002 Developer's Handbook Set
by Paul Litwin, Ken Getz, Mike Gunderloy
Just Access Jobs JustTechJobs.com is a jobsite for IT professionals, but with a twist. Rather than being a single monolithic jobsite that contains hundreds of thousands of postings for every discipline within the technology industry, JustTechJobs.com is your gateway to a collection of technology niched jobsites.
Peter's Software Samples, Tools etc (Shrinker - resizing forms based on screen resolution)