how do i go from one form to another with the same record number displaying?

[cjl]

I am working on a database which i have divided into 5 forms. I have added command buttons to go from one form to another, but how do i get from the 3rd record (for example) on 1 form to the 3rd record on the next?

 

[alexmb1]

you have to create a 'where' clause that specifies which record in the form you are viewing, for instance form 1, you want to match in the for you are going to open, for instance for 3.

you could do and onClick event that looks like that

Do.Cmd.OpenForm "form_name", acNormal, , "[field_name] = '" & Me("field_in_current_form") & "'"

form_name = the name of the form you would like to open i.e. number 3
field_name = the name of the field in the form you would like to open based on a match in a current form i.e. a field in form 3 that you would like to match in form 1
field_in_current_form = the field in the current form i.e form 1 that you would like to match in form 3

hope this helps

 

[do53093]

Hi there,

relatively new to access but i've strung together several things that seem to work ok. Apart from one thing, related to this post.
I have a table of information. I have a form that uses a keyword search to filter a sub-form of the information in that table. The results are displayed in continuous mode in rows. in each row i have a button which i want to click and open another form to edit that specific record. The edit form is also based on the same table and is in essence the same as my data entry form for new entries.
I have tried the above
(DoCmd.OpenForm "EDIT_RECORD" , , , "[RECORD_NUMBER] = " & Me.RECORD_NUMBER)
and several other methods to click a button and open my 'edit record' form, but either it says the table is in use, or it prompts me to enter the record value or i get an error.
It always opens on the first record.
I've tried the goto and gotorecord commands but get an error message that its not available.
I've tried closing the current forms before opening the new one in VBA and that seems to work to avoid the 'table in use' error. I have tried all sorts of things from multiple sources but i just cant get it to do what i want.
Maybe its to do with the way i have my forms set up i dont know, but i have tried changing settings in the properties but to no effect.
Latest attempt was:

Dim RecNum As Integer
Dim NewRecNum As Integer

RecNum = Me![RECORD NUMBER]
DoCmd.OpenForm "EDIT_RECORD"
NewRecNum = Forms![EDIT_RECORD]![RECORD_NUMBER]
Forms!EDIT_RECORD.FilterOn = True
Forms!EDIT_RECORD.Filter = "RECORD_NUMBER = '" & RecNum & "'"

in debug mode i can see that My RecNum variable is storing the correct record number i need to go to, the NewRecNum variable was just to see if i can store the current record in the new form, which it is doing. I feel like im close but still not working. Just need to force the EDIT_RECORD for to go to the RecNum value so it can be edited etc.

Any help appreciated.:banghead:

 

[June7]

You should have started your own thread instead of hijacking existing one, especially one that is almost 2 decades old. New threads get more attention than old ones that already have replies.

Personally, I would make one form serve for both purposes.

The OpenForm method should work.

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[JHB]

I have tried the above, (I think you mean below)
(DoCmd.OpenForm "EDIT_RECORD" , , , "[RECORD_NUMBER] = " & Me.RECORD_NUMBER)

Latest attempt was:

Dim RecNum As Integer
Dim NewRecNum As Integer

RecNum = Me![RECORD NUMBER]
DoCmd.OpenForm "EDIT_RECORD"
NewRecNum = Forms![EDIT_RECORD]![RECORD_NUMBER]
Forms!EDIT_RECORD.FilterOn = True
Forms!EDIT_RECORD.Filter = "RECORD_NUMBER = '" & RecNum & "'"

in debug mode i can see that My RecNum variable is storing the correct record number i need to go to, ...
Any help appreciated.:banghead:

The problem you are facing is because the correct name is "Me![RECORD NUMBER]" and not "[RECORD_NUMBER]".
So try the below:

DoCmd.OpenForm "EDIT_RECORD" , , , "[RECOR[B][COLOR=Red]D N[/COLOR][/B]UMBER] = " & Me.[RECOR[B][COLOR=red]D N[/COLOR][/B]UMBER]