Solved

Go To Next Record

Posted on 1998-07-10
8
258 Views
Last Modified: 2008-02-01
My sample application has a main form (Form1) with two subForms (subFormA) and (subFormB).  The source object for subFormA is (FormA) and for subFormB is (FormB).  The Recordsource for FormA is (TableA) and for FormB is (TableB).
THE PROBLEM!
When focus is lost in the last TextBox on subFormA or subFormB, I want the application to advance to the next record in the associated table.
I have tried the following with no success: (example syntax for subFormA, FormA, LastTextBox)
Sub LastTextBox_LostFocus()
    DoCmd.GoToRecord acDataTable, TableA, acNext
end Sub

Please HELP! -- Thanks
0
Comment
Question by:fairfax
8 Comments
 
LVL 8

Expert Comment

by:Helicopter
ID: 1976507
What happens if your user types something in the last textbox first and then wants to fill in the other fields after? Your method (once working) would dump them into another record.

Having said that have you made sure the cycle property of the subform is set to "all records". This means when the user tabs off the last text box the record will move on. If you have it set to current record it will just go back to the first text box.

I wouldn't recommend the lost focus event for this kind of navigation


0
 
LVL 3

Expert Comment

by:chapie
ID: 1976508
there's an event I belive called 'OnCurrent' for the forms event (not an individual control) that happens whenever the form changes records you can then use a check for the NewRecord property like this

sub Form_OnCurrent()
if newrecord then
  ..code goes here..
end if
end sub

actually thinking about it...doesn't this behavior supposed to happen anyway??
0
 
LVL 4

Expert Comment

by:tomook
ID: 1976509
Yes, it should happen automatically unless the cycle is set funny. fairfax, it should make no difference, but is this a multipart subform?
0
 
LVL 5

Expert Comment

by:tuvi
ID: 1976510
Is your main form unbounded? Is there any relationships between your TableA and TableB? I need more informations in order to help you.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 1

Accepted Solution

by:
cheechew earned 70 total points
ID: 1976511
If you want to trace the properties of a control by keeping data in variables, you must define your variables as static. If not all your value of the variables will lost once you exit the function. It is most easy to define the variable as global so that it can be assess by other method but it is not recommended in object orientation programming.
0
 

Author Comment

by:fairfax
ID: 1976512
First of all, thanks to all that attempted to supply a solution to my problem.  In retrospect after reviewing some of the comments/answers,  I guess I didn't supply enough information in my orginal question.  So as Paul Harvey would say heres the rest of the story.  Actually I have a solution to the question I stated however to me it seems kind of "clugy".  As I have read solutions to others problems, I have realized there are some pretty brite people that try to offer solutions.  I thought there must be a better way then what I have used.  Anyway I will try any explain how I solved this problem and if anyone has, what I think is a better solution, they will get the points.   (Without going into detail, I believe the form/subform methodoligy is the proper solution to this problem and I do not wish to explore another option).  To simplify this as much as possible lets consider only the main form and one subform.   The record source for the main form and subform is the same (TableA).  If you built this application as I described you will notice the record navigators for the main and submain display the same total number of records.  Sure enough as the user steps through the fields in the subform when the last field loses focus the subform willl advance to the next record.  The problem is,  the record on the main form doesn't advance.  When I link the two forms together with the "link master" and "link child" properties on the subform, I notice the following change and new problem.  The total record count on the subform changes to "1" (this seems to be logically correct)  and when focus is lost on the last field in the subform the application appears to try and create a new record (a problem).  To get around this I set the " Allow Additions" properity of the subform to "False".  Now to make the last field advance to the next record for the main and sub forms.  I added the following function to a module and call it on lost focus:

Funciton goNext()
   On Error Resume Next
   Application.Echo False
   Forms!Main1.mfield.SetFocus
   DoCmd.GoToRecord , , acNext
   Forms!Main1.subForm1.SetFocus
   Application.Echo True
   Forms!.main1.subForm1.form!.sfield1.setFocus
End Function

The question: Does anyone have a better idea on how to do this?   Thanks for your help!
0
 
LVL 5

Expert Comment

by:tuvi
ID: 1976513
You don't need to link the two forms as master and child. Just add these two form's OnCurrent event procedures to their respective form. And watch they behave. Assuming MainID is the primary key of your table.

----Main Form-----
Private Sub Form_Current()
  Dim rst As Recordset
 
  If IsNull(Me!MainID) Then
    FormSub.SetFocus
    DoCmd.GoToRecord acActiveDataObject, , acNewRec
    Me.SetFocus
  Else
    If IsNull(FormSub.Form!MainID) Then
      FormSub.SetFocus
      DoCmd.GoToRecord acActiveDataObject, , acPrevious
      Me.SetFocus
    Else
      If Me!MainID <> FormSub.Form!MainID Then
        Set rst = FormSub.Form.RecordsetClone
        rst.MovePrevious
        If rst.BOF Then
          rst.MoveNext
        End If
        Do While Me!MainID <> rst!MainID
          rst.MoveNext
        Loop
        FormSub.Form.Bookmark = rst.Bookmark
        rst.Close
      End If
    End If
  End If
End Sub

------Sub Form--------
Private Sub Form_Current()
  Dim rst As Recordset
 
  If IsNull(Me!MainID) Then
    DoCmd.GoToRecord acDataForm, Me.Parent.Name, acNewRec
  Else
    If IsNull(Me.Parent!MainID) Then
      DoCmd.GoToRecord acDataForm, Me.Parent.Name, acPrevious
    Else
      If Me.Parent!MainID <> Me!MainID Then
        Set rst = Me.Parent.RecordsetClone
        rst.MovePrevious
        If rst.BOF Then
          rst.MoveNext
        End If
        Do While rst!MainID <> Me!MainID
          rst.MoveNext
        Loop
        Me.Parent.Bookmark = rst.Bookmark
        rst.Close
      End If
    End If
  End If
End Sub

I hope this is what you're looking for: synchronizing.
0
 

Author Comment

by:fairfax
ID: 1976514
Just returned from vacation, thanks to all that attempted to answer my question.   The answer I was looking for came from TUVI.  I would like to insure that he/she are awarded the points for my question even if points must again be subtracted from my account. Thanks again!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now