Records Problem bwtn Forms

[Chris-eh]

I have a Main Survey form which a user can add a new record then enter data and then click to the next form to fill out the next form data for the same record (which contains subforms); they are linked by the same record # field called (AS Record#) but say i'm at record # 24 on the main survey form page and then click to next form it doesn't set to current #24 record. I have to flick thru records to get to # 24
My question is: Is there a way to set forms so it stays/brings up whatever the current record number was on the last form you were on or if you change to form two and go to a different record then flick to the next/ 3rd page it'll recognize what the current record is and bring up that record upon entering the next form?
Hope I didn't sound too confusing?
THANKS

 

[pdx_man]

On the second forms GotFocus event, have something like this:

Private Sub Form_GotFocus()

    Me.RecordsetClone.FindFirst "[Frm2_ID] = " & Forms![Frm1]![Combo1]
    Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub

 

[Chris-eh]

I didn't mention I'm working in Access 97; so i'm not sure if it likes the "ME" code, but i'll try it and let you know, thanks for reply

 

[pdx_man]

You can use Me in A97.

 

[Chris-eh]

OKay i went to the second form Accessibility Housing Unit- External Form and went into form properties and put the code under On got focus: set it as event procedure and filled out the code:

Private Sub Form_GotFocus()
Me.RecordsetClone.FindFirst "[Accessibility Housing Unit- External Form]" = " & Forms![Accessibility Survey Form]![ASRecord #]"
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub

then saved
Then went back to the initial data entry form Accessibility Survey Form and moved to record #24 then clicked button to go to the second page and it didn't open at record #24; nothing happened
What am I doing wrong?
Help

 

[pdx_man]

Take a closer look at my FindFirst statement. The equal sign is encapsulated within the quotes. Next, the field on the left side of the equal sign should be the field on the second form that is related to the field on the first form, not the form name. When in doubt, right mouse click and select Build ... when you are typing your code. That will allow you to select the fields and Access will put it in the right format for you.

 

[Chris-eh]

OKay new issue

I went back and had a second look at you formula, I must have been on something or just tired. I corrected my entry and now it works (THANKS!). However, new problem works when going forward from one form to the next but if I want to go back to the last sheet I get a debug error. Can I use the same formula?under got focus? if I'm on the Internal1 form (3rd form) and want to go back to External form (2nd form) which sheet properties do I put the formula under?same?

 

[Pat Hartman]

A better method to use would be the where argument of the OpenForm Method. That will work whether you are moving forward or backward through the set of forms.

 

[Chris-eh]

Please elaboate... where is this function in the properties?
I'm still learning...

 

[pdx_man]

What is the error you are getting? What is the line of code?

What exactly do you want to happen when moving from form2 to form1?

 

[tammyt]

This might be what you are looking for.

Create a command button on the first form to open the second form. Use the Wizard to create this command button. When the wizard asks you if you want to view all the data or specific data, chose specific data. The next wizard window asks which fields you want to set the criteria. Chose the record # field. Continue the wizard as usual.

This should work.

 

[Chris-eh]

Run-Time Error '2450'

Access Survey can't find the form 'Accessiblity Survey form' referred to the macro expression or Visual basic code


I'm trying to go back from the 3rd form (Internal form) to the 2nd form (External form) and have the same record # I was on show upon opening the 3rd form.
I've setup an event procedure under Got focus for moving forwad and to have each form open at whatever record I was in on the last form but if I go backward I get an error. see above
All of the forms work from the 1st to the 4th; starting on record #23 on the first form and moving to the next or the next it calculates and opens at #23 but if I go backwards form the 3rd to the 2nd I get this error and have to end

 

[pdx_man]

Look in your code when you are going from form3 to form2. It appears that you have a spelling error or something here where you reference the form name. Confirm the form name that you are trying to make the focus.

 

[Chris-eh]

OKay now I'm alittle confused

I've got setup in the got focus for the 2nd form (External), so when I'm on record #23 on form 1 and click to go to form 2 it opens at record #23
code: on 2nd form got focus
Private Sub Form_GotFocus()
Me.RecordsetClone.FindFirst "[ASRecord #] =" & Forms![Accessibility Survey Form]![ASRecord #]
Me.Bookmark = Me.RecordsetClone.Bookmark

then on #23 in form 2 , I move forward to form 3 (Internal)
code on form3 got focus
Private Sub Form_GotFocus()
Me.RecordsetClone.FindFirst "[ASRecord #] =" & Forms![Accessibility Housing Unit- External Form]![ASRecord #]
Me.Bookmark = Me.RecordsetClone.Bookmark
But say I'm on #23 and want to go back to form #2 and still be on #23 I get the error
I need some way of telling it to that if I move back to get focus from the previous record I was just on form previous form

so get whatever record I was on for moving between forms moving forward or backward
Does this clarify?

 

[pdx_man]

Are you closing the Accessibility Survey Form after you open form2? You must be, otherwise you wouldn't be getting the error:

Run-Time Error '2450'

Access Survey can't find the form 'Accessiblity Survey form' referred to the macro expression or Visual basic code

A couple of solutions:

1) Make sure that you leave that form open (You can make it invisible --> Forms![Accessibility Survey Form].Visible = False then just set it to true if you have to reference it again.). When ever you go back to the second form, it will then be on the same record as the first form.

2) Add the IsLoaded function from the NorthWind Database. Then check to see if the form is open:

If IsLoaded("Accessibility Survey Form") Then
  Me.RecordsetClone.FindFirst "[ASRecord #] =" & Forms![Accessibility Survey Form]![ASRecord #] 
  Me.Bookmark = Me.RecordsetClone.Bookmark 
Else
  Me.RecordsetClone.FindFirst "[ASRecord #] =" & Forms![Accessibility Housing Unit- Internal Form]![ASRecord #] 
  Me.Bookmark = Me.RecordsetClone.Bookmark 
End If

This will then return the record of Form1 if it is open. If it isn't then it will return the record on Form3.

 

[Pat Hartman]

I guess my first statement wasn't emphatic enough.

Get RID of ALL the code! You don't need it!

Just use the "where" argument of the OpenForm Method and Access will take care of syncing the forms and it won't matter whether you are moving forward or backward.

DoCmd.OpenForm "YourFormName", , , "[ASRecord #]
= " & Me.[ASRecord #]

 

[Chris-eh]

Yes, when I move between forms I was having the last close. I had it setup on the button which you click to move between forms.
ON Click: event procedure:
Private Sub Command63_Click()
On Error GoTo Err_Command63_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Accessibility Housing Unit- External Form"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "Accessibility Survey Form"

Exit_Command63_Click:
Exit Sub
because I was having a refresh problem.

Pat - what do you mean "where" argument of the Open form method ?...I'm REALLY new when it comes to this code stuff...

PDX_Man - Add the IsLoaded function where?

Sorry, I'm still learning when it comes to code, but I AM trying... : >

 

[pdx_man]

Since you are closing the forms, I agree with Pat. I thought you had all 3 forms open at once. Look up OpenForm Action under Access Help.

 

[Chris-eh]

okay so I've read the help and I've tried creating a macro with the open form function
But it doesn't like the formula for the "where" statement
I opened the " ... " button and put :
DoCmd.OpenForm "Accessibility Housing Unit- External Form", , , "[ASRecord #] = " & Me.[ASRecord #]

This formula would be for when I open the external form for it to pull the ASRecord # from the previous form Acccessiblilty Survey

Also, do I add this macro to the previous form of the button I click to open the next form ??

Honestly thanks for all your help thus far~!

 

[pdx_man]

What is the datatype of [ASRecord #] ? If it is text, you must encapsulate the criteria with single quotes.

DoCmd.OpenForm "Accessibility Housing Unit- External Form", , , "[ASRecord #] = '" & Me.[ASRecord #] & "'"

Add this code to the button that opens up the next form.