Solved

Go To Next Record

Posted on 1998-07-10
8
266 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

756 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