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

I have an Access 2016 ADP (Project) as a front-end to a SQL Server 2008 R2 Express database.
This arrangement has been running OK for the past 8 years.

What I am currently trying to do seems relatively simple, but I am having trouble achieving it.

In Access Form-A,  bound to Table-A, I have a combo box that fills a field with a value from Table-B.
If an entered value in the combo box is does not currently exist in Table-B, I use the "NotInList" event to add the new value to Table-B then immediately open Form-B (bound to Table-B) so that a couple of other pieces of data can be saved for the new record. This works OK up to a point.

Form-B is a continuous Access Form and I would like to immediately jump to the newly entered record in the list of all records so that the operator doesn't need to go scrolling down the list to find the new record.
How do I achieve this?

I have been trying to use "bookmarks" from recordset clones without success. Other on-line sites suggest using the "RecordsetClone.FindFirst" method, but my ADODB.Recordset clones don't seem to offer the "FindFirst" property.

Any suggestions gratefully received. Thanks.
colinasadAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jonathan KellyCommented:
Can you trap the newly created id then jump to that record with docmd.gotorecord  ?

OR if the records use an incremental id can you order the form on that and then jump to first or last record?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
colinasadAuthor Commented:
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.
0
Jonathan KellyCommented:
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)
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Leigh PurvisDatabase DeveloperCommented:
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

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

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
0
Leigh PurvisDatabase DeveloperCommented:
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

Open in new window

without any ambiguity.

Cheers
0
colinasadAuthor Commented:
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.
0
Leigh PurvisDatabase DeveloperCommented:
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

Open in new window


Cheers
0
colinasadAuthor Commented:
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.
1
Leigh PurvisDatabase DeveloperCommented:
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
0
colinasadAuthor Commented:
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.
0
Jonathan KellyCommented:
Colin Thanks for reward. Im glad you got it sorted :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.