How to open an Access ADP Continuous Form and jump to a specific record?

Thanks for your comment, Jonathan.
My Table-B does not have an incremental id, merely an nvarchar(20) "code" field as the primary key.
The Table-B records are displayed in Form-B in "code" order.

The "docmd.gotorecord" option sounds worth trying.
Where would I make that call? In Form_Load (or somewhere?) inside Form-B, or from Form-A after I have opened Form-B?

Thanks in advance for your further help.

Regards. Colin.

you wld probably need to put it on the onload of form b.

another option might be to open form b and set a filter for your new record.

DoCmd.OpenForm "FormB", , "[ID] = " & Me.FornACombo.Column(0)

Hi

Sorry, did you say you're using Access 2016 to open an ADP?
Surely that's not supported?  What are you doing to launch it?

That aside (there may be a version confusion), I've never loved continuous or datasheet standalone forms (I'll always have a parent wrapper form).  If you're standalone, then your listed code could work or you.

DoCmd.GoToRecord acDataForm, "frmListForm", acNewRec

Select all Open in new window

Alternatively, if you use the form's recordset, you can push it directly to the a new record without ambiguity.

With Forms!frmListForm
    .Recordset.AddNew
    !SomeField = NewData
End With

Select all Open in new window

But even if you're using, say, Access 2010, surely you're living on borrowed time with the ADP?
(Don't get me wrong, I'm not saying it was a good thing to end support for them - not at all.)

Cheers

Sorry, I meant to make clear why the recordset wasn't standalone form dependent.
If it's a subform, then you can still use:

DoCmd.OpenForm "frmListForm"
With Forms!frmListForm!SubformName.Form
    .Recordset.AddNew
    !SomeField = NewData
End With

Select all Open in new window

without any ambiguity.

Cheers

Thanks for the further comments.
1. Apologies for getting my Access Version wrong, I think it is Access 2010 we are still using for this project. I am aware that MS support for Access "projects" (ADP/ADE) has ended, but this is quite a large installation and my cash-strapped client has chosen to stick with the ADP/ADE-compatible versions of Access and SQL-Server rather than endure the cost and upheaval of a system change.

2.  I don't want to filter the opening of Form-B with a WHERE clause along the lines of "[ID] = " & Me.FormACombo.Column(0)  because I would like the operator to see the list of all the existing records in Table-B when they are updating the details of the new code they have input in case there already exists a value they should be using instead of creating a new one.

3. I also don't want to use the "AddNew" strategy because in the "NotInList" event in Access I have already told the operator that the value they have keyed in does not currently exist in Table-B and have asked them to confirm they want to add the new value. If they responded <Yes> then I called a SQL-Server Stored Procedure to insert a new record into Table-B with the value they have already keyed. I then want them to see their new code within the list of all existing records so they can fill out further details.

4. I will get a chance to work on this project (forgive the pun) later today, and will try to work with the "docmd.gotorecord" option.

Many thanks for all your help.
Colin.

Hi

If they responded <Yes> then I called a SQL-Server Stored Procedure to insert a new record into Table-B with the value they have already keyed.

Right.  So how do you execute that SP?  Does it return the PK value of the newly added row?  I'd imagine so, since you've been trying to use
RecordsetClone.FindFirst (albeit with an ADO Recordset) which means you must have something to find?

ADO recordsets support the Find method.  It's not as robust as DAO's FindFirst, but for a single field lookup it should be fine.

Also ADO doesn't support the NoMatch property, you'll want to check for the rst having reached the EOF.

So not dissimilar to:

    Dim rst As ADODB.Recordset
    
    Set rst = Forms!YourForm.RecordsetClone
    rst.Find "PKID = " & lngYourNewID
    If Not rst.EOF Then
        Me.Bookmark = rst.Bookmark
    End If

Select all Open in new window

Cheers

Thanks for all the comments.
I eventually used the DoCmd.GotoRecord function in my Form-B's Form_Load sub procedure.
I stepped through the records until I reached the one that matched the newly created record "code" (inserted into Table-B by a SQL-Server stored procedure called from Form-A and passed to Form-B via "OpenArgs"). I then used "SetFocus" to position the cursor in the first field to be filled out in the new record in Form-B.
Regards.
Colin.

Hi

So you're adding records using a Stored Proc, probably as efficient a way as possible, retrieving that new value, and then:

I stepped through the records until I reached the one that matched the newly created record "code"

You've moved through the form's entire recordset one record at a time until you find the match?  That's certainly surprising.

Though I really can't endorse that, at least you've got a solution.  (You can always refine it in time.)

Cheers

Thanks for the further comment, Leigh.
I'm afraid I was already implementing my GoToRecord solution before you posted your suggestion and only saw it after I had accepted the solution and submitted my final comments.
My loop of "DoCmd.GotoRecord ... acNext" commands is probably a bit low-tech, but in a Form bound to a Table with less than 50 records in it, there is not a noticeable delay before the list of records is displayed and the cursor is where I want it.
The more elegant bookmarking approach (which I was originally trying) might be one I try again if I have the same task to perform on a larger table.
Regards. Colin.

Colin Thanks for reward. Im glad you got it sorted :)