Open new form on current record

[neilwebber]

Seems like a golden oldie this one but I can't find an answer that does what I want so, with apologies for the repetition, here we go again.

I have a button on 'Form1'

When I click the button I want 'Form2' to open at the same record as was displayed on 'Form1' .

The common field on each form is 'IDNumber' (ie if IDNumber on 'Form1' is ID23 then i want 'Form2' to open at the record with IDNumber = ID23)

I've tried this with the button wizard, which works fine except 'Form2' is filtered when it opens. I'd like 'Form2' to open on the correct record but with all the other records accessible for the users to cycle through.

I've also tried various expressions in the Where statement of an OpenForm action in a macro but this always seems to display a dialog box (like a parameter query) with a prompt to enter the required value, which I want to avoid if possible.

Perhaps using a tabbed form is the answer, although I prefer to keep my forms separate if I can.

Thanks for your help
Neil

 

[cpod]

You can use FindRecord. This will open the form with all records showing and with the record with the corresponding IDNumer as the current record:

'Open Form2
DoCmd.OpenForm "Form2"
'Set focus on the field you are seaching
Forms!Form2!IDNumber.SetFocus
'Go to the record with the corresponding IDNumber from Form1
DoCmd.FindRecord Forms!Form1!IDNumber

 

[neilwebber]

Hi cpod

Thanks very much for your reply - I've just plugged your code into my form and it works a treat. Fantastic. Thanks for supplying a little explanation of what each line does - this helps me understand what I'm doing rather than just copying and pasting chunks of code without knowing what it means.

Taking this a little further, in order to keep things tidy I'd like 'Form1' to close after 'Form2' has opened at the correct record. I made an amateurish attempt to do this by adding

DoCmd.Close "Form1"

at the end of the code already given and needless to say, it didn't work (run-time error 'type mismatch'). Where have I gone wrong?

thanks again cpod

Neil

 

[Oldsoftboss]

Try
DoCmd.Close acForm, "Form1"
HTH
Dave

 

[neilwebber]

Dave

Thanks very much - that works very nicely.

If I could use a little more of your time, could you explain the significance of the 'acForm' bit. I'd like to understand what this is and why it makes the difference, rather than just plugging it in and accepting that it works. (sorry if this is drifting off into a VBA forum thing).

cheers
Neil

 

[cpod]

Access just needs to know what kind of thing it is that you want to close.

acForm is really a "constant" with a value of 2. If you used:

DoCmd.Close 2, "Form1"

it would also work. Access provides us with constants so that we don't have to remember that a form is 2, a report is 3 or a query is 1.

 

[cpod]

Access just needs to know what kind of thing it is that you want to close.

acForm is really a "constant" with a value of 2. If you used:

DoCmd.Close 2, "Form1"

it would also work. Access provides us with constants so that we don't have to remember that a form is 2, a report is 3 or a query is 1.

 

[neilwebber]

Thanks again cpod. I understand this now.

cheers
Neil

 

[neilwebber]

Hello everyone, back again.

I have (yet) another question related to this thread and the idea of opening forms at specified records.

Thanks to cpod and Dave I now have the following code working nicely on my forms, navigating between forms at the appropriate record.

DoCmd.OpenForm "Form2"
Forms!Form2!IDNumber.SetFocus
DoCmd.FindRecord Forms!Form1!IDNumber
DoCmd.Close acForm, "Form1"

My next question is....

If I have 'Form1' with a field 'IDNumber' but I now also have 'Form3' with a subform 'Form3Sbf' and that subform also has a field 'IDNumber', what should my code be to go from 'Form1' at a certain IDNumber to my subform also at that IDNumber? I can do it the other way (ie from a subform to a main form by just altering the code above).

I've tried adding various permutations suggested elsewhere (this forum, MS help) into the format I have already for moving between main forms

eg (Forms!Form3!Form3Sbf!IDNumber.SetFocus) and one or two others

but when I click my button I always go back to the first record, not the one I was previously viewing.

I realise this may be wandering into VBA forum territory but it seemed sensible to keep these related questions all together.

hope you can help
cheers
Neil

 

[neilwebber]

Back again,

I've figured out how to do this using a macro instead of code (after much trial and error) so I guess that draws a line under this (for a while....)

If anyone wants to see the macro i came up with to do this, just post back and I'll post it up.

Thanks for everyone's help in this thread

cheers
Neil

 

[Pat Hartman]

Access provides a built in way of opening a form or report to a specific record. Use the where argument of the OpenForm or OpenReport Method. If you let the wizard build the button, one of the options will be "do you want to open the form/report to a specific record". If you choose that option, Access prompts you for a linking key field and builds the appropriate code. This method is preferable to the FindRecord method suggested earlier because the form that is opening does not need to know which form called it or the name of the control on that form. Therefore, if formA and formB both need to open formC, the FindRecord method would not work, you'd need to know what form opened formC. The where argument of the OpenForm/OpenReport solves that problem.

As to the other problem, there is no way to directly pass parameters to a subform. To sync sfrmA with a form other than its parent form, modify sfrmA's recordsource query to include criteria that references the appropriate field on the form you want to sync with. When you open mfrmA, its subform will use the criteria to sync itself.

 

[Ligaya]

Hello Neil,

I am very new at ACCESS and am workking on trying to view related records on a form. I have 2 combo boxes which when I click on combo box 1, I'd like combo box 2 to show the related records.

I am interested in using a macro instead of code which you have discovered in 2002.

Would you be able to post it please?

Manythanks

 

[Oldsoftboss]

Use a subform to display related records. If you insist on using a combo box, search for "cascading Combo"

 

[Ligaya]

I shall try that one.
Thank you Oldsoftboss!

 

[thisisntwally]

when i try and use this bit of code, I get the error:
"A macro set to one of the current fields properties fail because of an error in a FindRecord action arguement."

I think i've got everything right...any ideas what im doing wrong?

***maybe there is something wrong with the second form?


Private Sub Command6_Click()
On Error GoTo Err_Command6_Click



DoCmd.openForm "Update"
Forms!Update!txtkey.SetFocus
DoCmd.FindRecord Forms!AddMOD!txtkey

Exit_Command6_Click:
Exit Sub

Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click

 

[thisisntwally]

Looking at it, my problem may be that form2 isn't set up to display multiple records, but I dont really know...

What I am realizing though, is what im really looking for is a command which only displays the specific record i was looking at in the last form. Im pretty sure I've seen how this is done, somewhere. but if anyone could point me in the right direction (or just tell me what to do ) I would appreciate it!

 

[Oldsoftboss]

DoCmd.openForm "Update",,, "YourIDField = " & me.txtkey

The cmdbutton wizard will do this automatically.

Dave

 

[thisisntwally]

my code is currently something along those lines:

stLinkCriteria = "txtkey= & Me.txtkey"
    stDocName = "Update"
    DoCmd.openForm stDocName, , , stLinkCriteria

at the moment the new form opens, but the record fails to load. the key field is generated on update of the new form, and i this is the root of my problem. the new form just displays "name#" (as does the old field).

I think I have the first form set to requery before opening the new form, but i worry that my second form is reloading the table after it opens...(edit i tried to solve this below, but no luck)

Option Compare Database

Option Explicit

Sub gotorecord()
   Dim stDocName As String
    Dim stLinkCriteria As String
 
    stLinkCriteria = "txtkey= & Forms![Input Form].txtkey"
    stDocName = "Update"
        DoCmd.gotorecord stDocName, , , stLinkCriteria
End Sub

still no real headway, thanks for the help with this oldsoftboss - im buying a VB book for lunch...