Close and reopen form to specific record (1 Viewer)

Design by Sue

Registered User.
Joined
Jul 16, 2010
Messages
739
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
 
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.
 
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...?
 
Meanwhile, MajP the field is text but when I change dim ID as long to text I get "User defined type not defined"
 
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 - 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.
 
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 & "'"
 
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.
 
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.
 
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.
 

Users who are viewing this thread

Back
Top Bottom