Stop Form from default to current records... 4

[mrwendell]

I hope someone can help with this....

I have a Form/subform...the main form populates employee info from tblemplinfo...the subform using formview...allows employees to add records to tblrdm. these are related by [ssn] currently when the form opens..it displays all previous entry based on that [ssn]... my subform has navigation fingers for previous and next records. this was necessary to allow users to flip thru their current entries not the old ones.

I need on open..the subform to be in a state of adding a new record only (blank) with no ability to navigate... and once a second record is added in the same session...it will allow use of the navigation fingers...

anyone got any ideas...

 

[FancyPrairie]

Open the subform in design view and set its Data Entry property to Yes.

 

[TheAceMan1]

How are ya mrwendell . . . . .

[blue]the subform using [purple]formview[/purple]...[/blue]

Which view; Single, Continuous, or DataSheet?

[blue]my subform has [purple]navigation fingers[/purple] for previous and next records.[/blue]

I take it you mean you've designed your own . . . navigation fingers. Is this correct?

Anyway, if your using [blue]custom designed[/blue] navigation fingers, yon can manipulate the [purple]Visible[/purple] property of the fingers and the [purple]Data Entry[/purple] property of the form to accomplish what you want.

If your using the navigation buttons provided by Access, manipulate the [purple]Navigation Buttons[/purple] & [purple]Data Entry[/purple] properties of the form.

See Ya! . . . . . .

 

[mrwendell]

guys i tried setting data entry properties to both yes and no...neither worked.

i am thinking i wasnt clear...it is a (single form) both main and sub... and the navigation buttons are custom to the point i used bitmap image but the code was produced by the wizard.

let me see if i can clarify my problem better...

when i my users open this form... based on ssn...it will find tblemplinfo and display that info in the mainform...the subform which is bound to tblrdm should be opened in a state ready for a new record. onopen event has the following code:

Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord , , acNewRec
ocxCalendar.Visible = False
cboWeekEnding.SetFocus

End Sub
my properties are set to yes for edit, delete and add...

NUTSHELL: i need the form open to add a record...and then using navigation, go thru only entries made during the current session...and once they exit...and open again..it will not show them any entries made previously by them.

 

[TheAceMan1]

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:

[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:

[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:

[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:

[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:

[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:

[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! . . . . . .

 

[FancyPrairie]

I guess I misunderstood something. I was under the impression that when the main form moved to a different employee the subform would only allow the user to add new data. But it appears that when the user firsts opens the form and displays an employee record the subform will only add new records. However, when the user moves to another record and then returns to that record (during the same session) the subform will not default to add only but will show all the info entered during the current session the database is opened.

If that is the case, why not just do the following:

1. Add an additional field (date/time stamp (i.e. dtmSession)) to your table (tblrdm). The primary key of this table now would include dtmSession (i.e. Primary Key = ssn and dtmSession).

2. Add a control (visible=false) on the main form (i.e. txtdtmSession) and in the OnOpen event of the main form set the txtdtmSession = Now.

3. Set the Link Child Fields Property of the subform control to ssn;dtmSession. Set the Master Child Field Property of the subform control to ssn;txtdtmSession

That should take care of it.

OnOpen event of main form:

Private Sub Form_Open(Cancel As Integer)

    txtdtmSession = Now
    
End Sub

By the way, you shouldn't have to create your own navigation buttons (unless you want to). Access should handle it like you want.

 

[TheAceMan1]

How are ya FancyPrairie . . . . .

[blue]NUTSHELL: i need the form [purple]open to add a record[/purple]...and then using navigation, [purple]go thru only entries made during the current session[/purple]...and once they [purple]exit[/purple]...and [purple]open again[/purple]..it [purple]will not show them any entries made previously by them[/purple][/blue]

Problem is he needs to do this for any/all records in the main form. So what happens when he does that, changes record in the mainform & goes back. He should still be able to see only those records entered. He's talking a complete session (any all records of main form) here . . .

See Ya! . . . . . .

 

[mrwendell]

Ace...it worked just fine!!! only a couple of issues...

1. when the sql update runs... it prompts with an "you are about to update...." obvisiously i cant have my folks seeing that...anyway to turn it off?

2. if i read it correctly... before and afterupdate code would gray out the navigation buttons? that does not occur...it would be a nice feature but not required since the records able to be viewed are the current ones. but if you get a minute...i wouldnt mind knowing it for future reference.

but other than that....PERFECT!!! THANKS SO MUCH!!

FANCYPRAIRE...

i also tested your code...IT WORKED PERFECTLY!!! a few modifications...i couldnt add the timestamp to the mainform added it to subform because the mainform does not change, it pulls data for display only... and i couldnt setup 2 primary keys... the tblrdm has no primary uses recid field...so i left it blank and proceed...but it worked!

 

[FancyPrairie]

I don't think it matters if the main form changes or not. The date/time control on the main form is just a place to hold the current date/time (it is not included in the table of the main form). It's there on the main form so the subform has something to link to.

And you don't need it to be a primary key (necessarily). Right now you have a field that links the main form with the subform (I'm assuming - ssn). You just need to add the date/time stamp as part of the link (i.e. ssn;dtmSession).

AceMan1, that's what I missed the first time around. So, by having the date/time stamp you can move back and forth between records and see only those records that were part of that session. (Note that the date/time stamp on the main form does not change once the form is opened.) As a result, one can go back later (if so desired for historical purposes) and see which records were added per session.

 

[TheAceMan1]

FancyPrairie . . . . . .

Yeah . . . . if you look at it, were both doing the same thing: [blue]Tagging the complete session with a unique identiier[/blue], which is really whats its all about.

More importantly:

[blue][purple]mrwendell[/purple] has present a method here which is [purple]Great For Security[/purple] as far as user input is concerned.[/blue]

This certainly one for my books, and my chances of using it in the future are very high!

Excellent job [blue]FancyPrairie![/blue]

Excellent job [purple]mrwendell ![/purple]

See Ya! . . . . . .

 

[mrwendell]

one more question guys...

havent tested yet...using the tagid over the network... currently my users access the db from desktop shortcut...and naturally there can mutltiple users at any given moment...

is there any thing i need to be aware of in a multi-user environment??

also, anyway to turn off that "update message" that is ran at the completion of ...

SQL = "UPDATE tblSessionTag SET TagID = " & TagNow & ";"
DoCmd.RunSQL SQL

 

[TheAceMan1]

[blue]is there any thing i need to be aware of in a multi-user environment??[/blue]

There are a number of things to be aware of, but the TagID should be fine. This was the reason I went with the additional table method. Be sure to split the database [purple](backup first!)[/purple] into BackEnd/FrontEnd. This is best on networks.

[blue]anyway to turn off that "update message" that is ran at the completion of ...[/blue]

In the database window click [blue]Tools[/blue] - [blue]Options ...[/blue] - [blue]Edit/Find Tab[/blue]. In the [blue]Confirm[/blue] section, put a check in [purple]Action queries.[/purple] Click OK.

See Ya! . . . . . .

 

[PHV]

also, anyway to turn off that "update message"
DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mrwendell]

GUYS I HAVE ONE MORE STEP I NEED HELP WITH...I POSTED THIS ON ACCESS/SQL FORUM ALSO...BUT I FIGURED YOU GUYS ROCKED WITH WHERE I AM SO FAR...I FIGURED I OWED YOU THE PRIVILEGE OF SEEING IT TO THE END...HAHAHAHA... i know such b.ss!!

anyway...

Mainform/subform-- cbofindrecord select a team as follows
Sub cboFindRecord_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[CoachNbr] = '" & Me![cboFindRecord] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
DoCmd.GoToControl "frmARDMTESTsub"
DoCmd.GoToRecord , , acLast
End Sub

which populates subform/datasheet with all records associated with that coachnbr...from qry of all records...

now I would like to update only those records with a checkall command button...once clicked all the records in the subform.reviewed (checkbox) will be go from false to true... and timestamp the [moddate]using the following sql update...

DoCmd.RunSQL "UPDATE qryRDM041 SET qryRDM041.REVIEWED = 1, qryRDM041.moddate = Now() WHERE (((qryRDM041.REVIEWED)<> 1)); ", -1

IT WORKS PERFECTLY!!! BUT

now I need to update only the records that are currently associated with that coachnbr in the subform... this is what I tried....didnt work....and dont laugh!

DoCmd.RunSQL "UPDATE qryRDM041 SET qryRDM041.REVIEWED = 1, qryRDM041.moddate = Now() WHERE (((qryRDM041.REVIEWED)<> 1 and (qryrdm041.coachnbr)= me.bookmark); ", -1

 

[PHV]

What about this ?
DoCmd.RunSQL "UPDATE qryRDM041 SET REVIEWED=1, moddate=Now() WHERE REVIEWED<>1 And CoachNbr='" & Me![cboFindRecord] & "';", -1


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244