473,327 Members | 1,952 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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 MachineSpecification = 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 [MachineSpecification]=2

As an example lets say record 3 is [MachineSpecification]= 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.OpenForm "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?
Jul 27 '10 #1

✓ answered by hype261

@Microblitz
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Option0_Click()
  2.     DoCmd.OpenForm "Form2", , , , , acHidden
  3.     DoCmd.OpenForm "Form1"
  4. End Sub
The forms have similiar code in there on current event...

FORM 1

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.   If Me.Field1 = 2 Then
  3.      If CurrentProject.AllForms("Form2").IsLoaded Then
  4.         Forms![Form2].Recordset.FindFirst "ID = " & Me.ID
  5.         Forms![Form2].Visible = True
  6.         Me.Visible = False
  7.      End If
  8.  End If
  9. End Sub
FORM2

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  If Me.Field1 = 1 Then
  3.      If CurrentProject.AllForms("Form1").IsLoaded Then
  4.         Forms![Form1].Recordset.FindFirst "ID = " & Me.ID
  5.         Forms![Form1].Visible = True
  6.         Me.Visible = False
  7.      End If
  8.  End If
  9. End Sub
  10.  
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.

9 3376
I was looking at Me.CurrentRecord being stored in a global variable in a module.
Jul 27 '10 #2
beacon
579 512MB
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
Jul 27 '10 #3
NeoPa
32,556 Expert Mod 16PB
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.
Jul 27 '10 #4
@beacon
1. The MachineSpecification 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).
Jul 28 '10 #5
hype261
207 100+
@Microblitz
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/libr...ffice.12).aspx
Jul 28 '10 #6
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!
Jul 29 '10 #7
hype261
207 100+
@Microblitz
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Option0_Click()
  2.     DoCmd.OpenForm "Form2", , , , , acHidden
  3.     DoCmd.OpenForm "Form1"
  4. End Sub
The forms have similiar code in there on current event...

FORM 1

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.   If Me.Field1 = 2 Then
  3.      If CurrentProject.AllForms("Form2").IsLoaded Then
  4.         Forms![Form2].Recordset.FindFirst "ID = " & Me.ID
  5.         Forms![Form2].Visible = True
  6.         Me.Visible = False
  7.      End If
  8.  End If
  9. End Sub
FORM2

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  If Me.Field1 = 1 Then
  3.      If CurrentProject.AllForms("Form1").IsLoaded Then
  4.         Forms![Form1].Recordset.FindFirst "ID = " & Me.ID
  5.         Forms![Form1].Visible = True
  6.         Me.Visible = False
  7.      End If
  8.  End If
  9. End Sub
  10.  
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.
Jul 29 '10 #8
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.
Jul 30 '10 #9
NeoPa
32,556 Expert Mod 16PB
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 :)
Jul 30 '10 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Tony | last post by:
Hello, I am having difficulty in getting the current record of the current form to show after pressing a command button that takes me to another form. The command button takes me to another...
4
by: DBQueen | last post by:
I have a subform which is in Continuous Forms view. I have added a button to the bottom of the page to move to the next record using the button wizard (result: DoCmd.GoToRecord , , acNext). I...
1
by: Richard Coutts | last post by:
I have a Continuous Form where each record has a button that activates another form that simplifies entering values into the record. The activated form has the equivalent of a "Done" button. I'd...
8
by: Zlatko Matiæ | last post by:
There is a form (single form) and a combobox. I want that current record of the form is adjusted according to selected value in the combobox. Cuurrent record should be the same as the value in the...
1
by: moelleni | last post by:
Hi, I made an Access 2002 Database and wanted to automate it to sent the current record to Word 2002. So readed the article "How to send the current record to Word 2000 with automation" I tried...
1
by: 4004 | last post by:
I would like to open a columnar form (so I can see all the details) from a datasheet form (so I can see what is there) but keep the same recordset and current record. I can do the recordset set...
22
by: Br | last post by:
First issue: When using ADPs you no longer have the ability to issue a me.refresh to save the current record on a form (the me.refresh does a requery in an ADP). We usually do this before...
14
by: keri | last post by:
Hi, Simple version of the question..... How do I use the where clause of the open form command to show an account with a matching ID to be displayed when the form is opened? Eg. I select a...
4
by: magmike | last post by:
I've created a report for the purpose of printing a one page summary of a record. Of course, when I created the report, it gives me a page on every record. Can I create a button to open that report...
1
by: OzNet | last post by:
Hi I am using Access 2007 I have a form called frmProjects and a tabbed panel on it. The first tab is Project Details, the Second is Lump Sum Estimate and the third is Percentage Estimate ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.