Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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"
Any suggestions gratefully received. Thanks.
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"
Any suggestions gratefully received. Thanks.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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?
OR if the records use an incremental id can you order the form on that and then jump to first or last 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.
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)
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.
Alternatively, if you use the form's recordset, you can push it directly to the a new record without ambiguity.
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, 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
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
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:
Cheers
If it's a subform, then you can still use:
DoCmd.OpenForm "frmListForm"
With Forms!frmListForm!SubformName.Form
.Recordset.AddNew
!SomeField = NewData
End With
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.
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
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:
Cheers
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
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.
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:
Though I really can't endorse that, at least you've got a solution. (You can always refine it in time.)
Cheers
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial