How to Open a form at the current record index without filtering

[Microblitz]

(Access 2007)
I'm designing a database to take test data.
There are two types of test which require data entered in two different orders, but stored in the same record.

To do this I have created two forms, lets call them form1 and form2.

When a record entry called MachineSpecific ation = 2 I want form2 to display else I want form1 to display.

Im currently using a hide/unhide routine (donated to me by Neopa) in the "On Current" Event to switch from form1 to form2 when [MachineSpecific ation]=2

As an example lets say record 3 is [MachineSpecific ation]= 2

As i click through the records 1,2, .. then 3 Form2 unhides and form1 hides, but its record displayed is record 2 not record 3.

I then click to record 3 the form2 shows the correct data, then I click to record 4. As expected form1 displays but is still on record 3 not 4.

So even though the Nav button is pressed form2 is getting the previous forms record index, not the next index. This happens in reverse too record 4 to 1.

I tried using "DoCmd.Open Form "form2", , , "[index]=" & Me![index] "
But this sets a filter to a single record when it passes the index. So it doesnt suit my purpose.

So how do you switch to a different form and back again when scrolling through records?

[Microblitz]

I was looking at Me.CurrentRecor d being stored in a global variable in a module.

[beacon]

Hi Micro,

I'm confused by some of the things in your post, such as:

• Where are you entering the machine specification at?
• Are the forms you're talking about subforms or standalone?
• If subforms, what are the child/master fields you're linking to on the main form?

Thanks,
beacon

[NeoPa]

Sorry. I have subscribed to this thread, but I expect this will take a bit of looking at and thinking about, which is why I haven't posted a comment yet.

Tonight is busy again, but I will when I get an opportunity.

[Microblitz]

Hi Micro,

I'm confused by some of the things in your post, such as:

• Where are you entering the machine specification at?
• Are the forms you're talking about subforms or standalone?
• If subforms, what are the child/master fields you're linking to on the main form?

Thanks,
beacon

1. The MachineSpecific ation is a field in the Main table, the value is entered from a related table via a drop-down. I have a full database to test the forms with which already has the values I need for test purposes.
Eventually using the dropdown to select one of the three machine types will change the form to the appropriate type automatically.

2. The forms are stand alone.

3. Subforms are not used. (and wouldnt make sense in this context).

[hype261]

1. The MachineSpecific ation is a field in the Main table, the value is entered from a related table via a drop-down. I have a full database to test the forms with which already has the values I need for test purposes.
Eventually using the dropdown to select one of the three machine types will change the form to the appropriate type automatically.

2. The forms are stand alone.

3. Subforms are not used. (and wouldnt make sense in this context).

You might want to take a look at the BookMark property of recordsets. Here is an example from Microsoft on how to use it.

http://msdn.microsoft.com/en-us/library/bb258175(v=office.12).aspx

[Microblitz]

I cant get bookmark to carry over to the newly unhidden form.

The objective is to get a form to:

Use the inbuilt nav buttons to move to the next / previous record.

Check to see if the 'current' record [field] contains the value 1 or 2

If the field is 1 stay on form one and index to the next/previous record.

If the field is 2 index to the next record.
open form2 on THAT record and transfer control to it.
Close form1

The reverse then becomes true for form2


Use the inbuilt nav buttons to move to the next / previous record.

Check to see if the 'current' record [field] contains the value 1 or 2

If the field is 2 (This time) stay on form two

If the field is 1
open form1 on THAT record and transfer control to it.
Close form2
---
It's a kind of ying/yang system.

The problems I get trying to use this method is that there seems to be no global method of controlling the current record index value.

If I could get at this value I could just check the field increment the index open the new form close the old one.

But one issue is the minute I close a form the vba stops executing on that form, so I switched to using hide/unhide.

If I use the findfirst it set the filter on and ONLY displays the one record.(I cant index anymore), if I switch the filter off it goes back to record one.

Ive been working on this one stupid little problem for three days now and its driving me bonkers. It seems that Microsoft doesnt want to let me at the record indexing system. What annoys me most is I've written DBMS's in assembly language 25 years ago so I know how they work and this MUST be possible, but either I dont know the syntax or its verboten!

[hype261]

I cant get bookmark to carry over to the newly unhidden form.

The objective is to get a form to:

Use the inbuilt nav buttons to move to the next / previous record.

Check to see if the 'current' record [field] contains the value 1 or 2

If the field is 1 stay on form one and index to the next/previous record.

If the field is 2 index to the next record.
open form2 on THAT record and transfer control to it.
Close form1

The reverse then becomes true for form2


Use the inbuilt nav buttons to move to the next / previous record.

Check to see if the 'current' record [field] contains the value 1 or 2

If the field is 2 (This time) stay on form two

If the field is 1
open form1 on THAT record and transfer control to it.
Close form2
---
It's a kind of ying/yang system.

The problems I get trying to use this method is that there seems to be no global method of controlling the current record index value.

If I could get at this value I could just check the field increment the index open the new form close the old one.

But one issue is the minute I close a form the vba stops executing on that form, so I switched to using hide/unhide.

If I use the findfirst it set the filter on and ONLY displays the one record.(I cant index anymore), if I switch the filter off it goes back to record one.

Ive been working on this one stupid little problem for three days now and its driving me bonkers. It seems that Microsoft doesnt want to let me at the record indexing system. What annoys me most is I've written DBMS's in assembly language 25 years ago so I know how they work and this MUST be possible, but either I dont know the syntax or its verboten!

Have you tried doing this... I have one Form that when you push a button it launches both forms. One form is hidden by default.

Private Sub Option0_Click()
    DoCmd.OpenForm "Form2", , , , , acHidden
    DoCmd.OpenForm "Form1"
End Sub

The forms have similiar code in there on current event...

FORM 1

Private Sub Form_Current()
  If Me.Field1 = 2 Then
     If CurrentProject.AllForms("Form2").IsLoaded Then
        Forms![Form2].Recordset.FindFirst "ID = " & Me.ID
        Forms![Form2].Visible = True
        Me.Visible = False
     End If
 End If
End Sub

FORM2

Private Sub Form_Current()
 If Me.Field1 = 1 Then
     If CurrentProject.AllForms("Form1").IsLoaded Then
        Forms![Form1].Recordset.FindFirst "ID = " & Me.ID
        Forms![Form1].Visible = True
        Me.Visible = False
     End If
 End If
End Sub

This seems to work out for me you will also need to put some code in the on close event for each form so that they close together. I wouldn't bother with closing and reopening the forms each record cycle because you will get a flicker and it won't look as smooth to the user.

[Microblitz]

I had worked on somthing similar to this during my meanderings around the language.

Is it me or does the VBA compiler appear to have no error handling system? Because if there is a minor error it doesnt appear to report it, it just ignores the event and doesnt process it.

Anyway full marks to you Hype, this code once modified to fit my form names and index field works flawlessly.

Thank you guys for your help.

[NeoPa]

I was hoping to get more involved in this Micro, so apologies for my absence.

Congratulations also to Hype for helping you to find your solution :)