mrwendell . . . . .
Sorry to get back so late . . . Had to sing a solo in church tonight . . . .
The only way you can really do this is by having some means of [purple]
tagging all records in a session[/purple]. [blue]For the current session[/blue] tagging will allow the user to navigate throughtout all data entered, wether new or previously saved. Because each session has to be seperate, the [purple]
tagging has to be unique and somehow maintained.[/purple]
The above brings to light two major changes. An [blue]additional table for maintaining the Uniqueness[/blue] of tagging, and an [blue]added field[/blue] to the source table of the subform [blue]for holding the tag[/blue].
[purple]
Backup the database so you can come back to square one if necessary,[/purple] and I've already run a simulation with this that worked just fine. Also Note: I designed the code to handle [blue]Access Navigation Control Buttons[/blue], you'll have to modify for your [blue]fingers[/blue]. So if your ready, lets do this!
[blue]
1)[/blue] Add a new table with the following properties:
Code:
[blue]Table > Name as [purple][b]tblSessionTag[/b][/purple]
Field > Name as [purple][b]TagID[/b][/purple], FieldType as [purple][b]Long Integer[/b][/purple],
Indexed as [purple][b]Yes (No Duplicates)[/b][/purple][/blue]
Do not make the [blue]
TagID[/blue] a Primary Key as we will only be using a single record.
Open the table, enter zero in the first record, save & close.
[blue]
2)[/blue] Open the [blue]Source Table[/blue] for the subForm in design view and add the following field:
Code:
[blue]Field > Name as [purple][b]Tag[/b][/purple], FieldType as [purple][b]Long Integer[/b][/purple],
Indexed as [purple][b]Yes (Duplicates OK)[/b][/purple][/blue]
[blue]
3)[/blue] Setup a query as follows:
In query design view, make a query (using the table for the subforms record source) having all the fields in the RecordSource of the subform. Be sure to include the [purple]
Tag[/purple] field. In the criteria for [purple]
Tag[/purple] copy/paste the following:
Code:
[purple][b]CurTag()[/b][/purple]
[blue]
Name the query [purple]qrySessions[/purple][/blue]. The recordsource for the subform will be switched to this query, and [purple]
CurTag()[/purple] returns the [blue]Session Tag[/blue] value to the quey.
[blue]
4)[/blue] Open the subform is design view and in the dropdown list for the [blue]RecordSource[/blue] select [purple]
qrySessions[/purple].
[blue]
5)[/blue] The Code (you substitute names in [purple]
purple[/purple]):
In the [blue]On Current Event[/blue] of the [blue]Main Form[/blue] copy/paste the following code:
Code:
[blue] Dim sfrm As Form, Flg As Boolean
Set sfrm = Me![purple][b]YourSubFormName[/b][/purple].Form
If sfrm.RecordsetClone.RecordCount > 1 Then
Flg = True
End If
sfrm.NavigationButtons = Flg
Set sfrm = Nothing[/blue]
The code handles [blue]showinging/hiding[/blue] the [blue]navigation buttons[/blue] of the subform when you change records in the main form.
In the code module of the subform copy/paste the following code:
Code:
[blue]Private Sub Form_AfterUpdate()
If Me.RecordsetClone.RecordCount >= 2 Then
Me.NavigationButtons = True
End If
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me!Tag = DMax("[TagID]", "tblSessionTag")
End If
End Sub
Private Sub Form_Open(Cancel As Integer)
Dim TagNow As Long, SQL As String
TagNow = Nz(DMax("[TagID]", "tblSessionTag"), 0) + 1
SQL = "UPDATE tblSessionTag SET TagID = " & TagNow & ";"
DoCmd.RunSQL SQL
End Sub[/blue]
The [blue]AfterUpdate[/blue] event of the subform handles turning on the Navigation Buttons while editing/saving records.
The [blue]BeforeUpdate[/blue] event of the subform handles tagging new records.
The [blue]On Open[/blue] event of the subform handles [blue]updating/saving[/blue] the [purple]
New Session TagID.[/purple]
Finally, in a module in the modules window copy/paste the following code:
Code:
[blue]Public Function CurTag()
CurTag = DMax("[TagID]", "tblSessionTag")
End Function[/blue]
Returns the [blue]Session Tag[/blue] to the query.
Thats it . . . . . give it a whirl and let me know . . . .
See Ya! . . . . . .
