Close and reopen form to specific record

[Design by Sue]

I have a form with a subform. A change on the main form requires a field on the subform be updated. I am using this to close and reopen the form which forces the update as needed but this opens the form to the first record. I need to reopen to the same record that I was one when closed. I know I have done this in the past but can't remember how to do it. Help please

DoCmd.Close acForm, "form", acSaveYes
DoCmd.OpenForm "form"


Thanks
Sue

 

[pbaldy]

How about

Open a second form to the record

 

[MajP]

dim ID as long 'assumes primary key is numeric adjust as necessary
ID = forms("formName").PKfieldName
DoCmd.Close acForm, "formname"
DoCmd.OpenForm "formname"
forms("Formname").recordset.findFirst "SomePKField = " & ID

However, you should not need to close the form to update the subform.

 

[Gasman]

I agree. Closing and opening form is overkill.

 

[Design by Sue]

I tried requery but could not make it work I would rather not close the form but how do I force a requery - or is there another...?

 

[Design by Sue]

Meanwhile, MajP the field is text but when I change dim ID as long to text I get "User defined type not defined"

 

[Gasman]

Meanwhile, MajP the field is text but when I change dim ID as long to text I get "User defined type not defined"

Don't you mean string?

 

[Design by Sue]

DUH - Thanks for you help - I needed refresh not requery.

 

[Design by Sue]

Gasman - thanks - as I have explained in previous posts it has been a long time since I worked with access - it is only slowly coming back.

Thanks all for your help

 

[Gasman]

Gasman - thanks - as I have explained in previous posts it has been a long time since I worked with access - it is only slowly coming back.

Thanks all for your help

TBH if I saw anything ending in ID I would expect it to be a number, and an autonumber at that, not a string.

 

[MajP]

IF the thing you are searching for is a string you have to put single quotes around the value
forms("Formname").recordset.findFirst "SomePKField = '" & ID & "'"

 

[MajP]

A change on the main form requires a field on the subform be updated.

I am not saying that is wrong design, but it sounds suspect. usually it would be the other way. Example I have a Task with child Action Items. If all the action items are complete the Task status is updated to Closed. Can you explain?

tried requery but could not make it work I would rather not close the form but how do I force a requery - or is there another...?

Anyways from the main form event you requery the subform
me.subformControlName.Form.requery

I always save the current ID, requery, then move back to the ID so as to be at the original location.
If you have not added or deleted records you may be able to use a refresh instead of a requery. This will show changes to the record and does not move location.
You can also requery the forms recordset instead of the form itself. I believe that keeps you at the same location.

 

[Gasman]

You can also requery the forms recordset instead of the form itself. I believe that keeps you at the same location.

It does, and I started using that before I gave up work.
Someone else posted that tip here and I started using it.

 

[Pat Hartman]

A change on the main form requires a field on the subform be updated.

This makes no sense. What subform RECORD are you trying to update? If the answer is "all", then the field is in the wrong table. For any other answer, the process is flawed.