Go to Record

[seeker63a]

I have a subform that when the user deletes a record from it he would like it to return to a similar location.

Code I have used is

lcate = me.currentrecord
delete query
close form
reopen form
forms!parentform!subform!txtbox.gotorecord lcate

this is not the actual code but gives the idea as to what i want to do. What would work? Thanks

 

[burrina]

You don't need to close the form, just Requery it. What do you mean by similar location?
In order to help we need details.

 

[seeker63a]

Here is the code I am using and it tells me the object is not open but it is because i just did a requery on it.

If KeyCode = vbKeyDelete Then
Me.txtVendorKey.Visible = True
lcate = Me.CurrentRecord
DoCmd.RunSQL "Delete From tblVendorList where [VendorKey] = " & txtVendorKey
Me.txtVendorKey.Visible = False
DoCmd.Close acForm, "frmVendorEdit"
'Forms!frmNameSearch!frmVendorList.Requery
'DoCmd.Close acForm, "frmNameSearch", acSaveYes
'DoCmd.OpenForm "frmNameSearch", acNormal, , , , acWindowNormal
Me.Requery
DoCmd.GoToRecord acDataForm, "forms!frmNameSearch!frmVendorList", acGoTo, lcate
DoCmd.SetWarnings False
End If

I have defined lcate at the beginning of the module. Thanks for the requery suggestion that is working great now to get this other to operate the way the customer wants it to work.

 

[burrina]

Okay, progress. What part does not work and when? Describe step by step.
Is frmNameSearch the main form and frmVendorList the subform?

 

[seeker63a]

The part that apparently does not work is

DoCmd.GoToRecord acDataForm, "forms!frmNameSearch!frmVendorList", acGoTo, lcate

And yes frmVendorList is a subform of frmNameSearch. when the form reopens(requerys) it needs to go to the record whose number is in the lcate variable.

 

[burrina]

If you Requery, then the form loses the focus on the current record. perhaps what you really need is to stay on the current record after a Requery.

Dim recNum As Long

recNum = Me.CurrentRecord
Me.Requery
DoCmd.GoToRecord acDataForm, "YourFormNameHere", acGoTo, recNum

HTH

 

[seeker63a]

Here is the code I am using now

Me.Requery
DoCmd.GotoRecord acDataForm, "frmVendorList", acgoto, lcate

run-time error 2489
The object "frmVendorList" isnt open

is what is returned. Since this is the subform in frmNameSearch I have also tried "frmNameSearch!frmVendorList" same error and I have also tried
"Forms!frmNameSearch!frmVendorList" with the same error.

If I leave this line of code out it requeries just fine and returns to first record in frmVendorList. It errors when I attempt to use docmd.gotorecord

thanks for the quick response.

 

[burrina]

This is getting too confusing. Can you post a demo copy with all sensitive data removed?