Solved How do I move to a new record automatically in a subform

[bmaccess]

This is the code I used just add new record.

Private Sub cmdAddRecord_Click()

With Me.frmStudentBooksSubform.Form.Recordset
.AddNew
.Update
End With

 

[bmaccess]

Thanks MajP. Now if anybody can just show me how to get the new record selected please than my weekend is made.
Thanks.

 

[LarryE]

@bmaccess:
This is just crazy. What are you doing?

 

[Gasman]

Thanks MajP. Now if anybody can just show me how to get the new record selected please than my weekend is made.
Thanks.

Perhaps findfirst using the ID field?

 

[MajP]

This is just crazy. What are you doing?

I am equally lost, but I think something is getting lost in translation.
I think whatever the OP is asking and what we think they are asking is not the same.

Thanks MajP. Now if anybody can just show me how to get the new record selected please than my weekend is made.
Thanks.

We seem to not understand what you are asking, because in our mind the solutions provided from the very beginning selected the new record.
My demo does this

Adds a new record from the listbox and clearly selects it. But maybe that is not good enough and you want this?

As far as I know that is not possible in a datasheet. Even if tab stops are set to no, a control is still going to get the focus. I can add dirty = false to get rid of the pencil, but one of the controls will have focus. You cannot unfocus controls. You can only set the focus to something else. If you use a continuous form you could add an additional control that is very small and colored the same as the form. Then set the focus to that control. It would give you this effect.

 

[KitaYama]

You are over thinking this.

@MajP your method fails if the subform has a Required field.
Your code fails on Update and will never go further to fill the fields.

If it was me, I would append the record with sql and re-query the subform.

 

[Privateer]

Reread Pat Hartman's suggestion. The answer is not to move the cursor there, it's to put the information there using an append query. If your text boxes and combo boxes have the information for the next record, create variables, assign the new information to them, then do an append query using the variables. It will add the new record.

Once I got started on the append query I couldn't stop. Enjoy the code below, it will solve your problem.

My naming convention is to use Box for text boxes and Option for combo boxes.
Also, assuming the combo boxes are populated from tables that have a primary key in them, and that is the value being stored in the table, the variables are numbers, not text.

    Dim BT As String        'The BookTitle
    Dim BID As Long         'The Book ID number
    Dim BGID As Long        'The grade, the primary key for 1A
    Dim BSID As Long        'The Book Stream, the primary key for Grade1
    Dim PFN As String       'The Person's Full Name
    Dim strSQL As String    'The append query with variables

    BT = Trim(Me.BookTitleBox.Value)
    BID = Nz(Me.BookIDOption.Value, 0)
    BGID = Nz(Me.BookGradeOption.Value, 0)
    BSID = Nz(Me.BookStreamOption.Value, 0)
    PFN = Trim(Me.SurNameBox.Value)

    strSQL = "INSERT INTO tblBooks ( BookTitle, BookID, GradeID, StreamID ) " & vbCrLf & _
    "SELECT " & BT & " AS BookTitle, " & BID & " AS BookID, " & BGID & " AS GradeID, " & BSID & " AS StreamID;"

    CurrentDb.Execute strSQL, dbFailOnError

 

[gemma-the-husky]

As a general thing, I can't understand why you would want to control a subfotm from its container.

The subform ought to be a black box to the main form, other than linking data based on certain fields.

What process are you doing that needs your approach?

 

[bmaccess]

Hello all. I highly appreciate all your input. What I am asking might be confusing and maybe you do not see my logic.

I like LarryE's comment. Thanks Larry
"This is just crazy. What are you doing?"

I have design a text Book System originally without a scanner. It has been implemented by a school where I teach.
Now I am trying to modifying the application to include a scanner. I know it would be a better idea using foreign keys to fill in the fields of the books and the learners automatically but then I have to redo my forms and reports. These reports I struggled for more than a year to achieve.
I could not find any resource to help me with my dynamic crosstab queries. I manage to create a form using dynamic crosstab queries and it produces quite nice reports. I need to change the form into a report and there I will need all your help again.

So is there anybody who are able to make the new added record get selected let me know if it is possible please.

If not I will try to see how I can use MajP solution to match my requirements.

Speak to you guys again. Great job all of you. Thanks

 

[Gasman]

So what is it about post #45 that is not doing it for you?
Are you actually reading the replies, trying the code supplied out?
The new record gets selected, even in my 2007 version?

 

[MajP]

@MajP your method fails if the subform has a Required field.
Your code fails on Update and will never go further to fill the fields.

If it was me, I would append the record with sql and re-query the subform.

That makes NO sense! To say it fails for not accounting for something not asked for is illogical. We can what if everything. If there is required fields then simply remove the update (because you do not want to update) or pass the required values if it exists.

However your recommendation for an insert will never work if you do not pass a required field. So that is a less universal solution.

At least by removing the update you can push the values you have, but give the user a chance to update required fields.

Doing an Insert query or Manipulating the Recordset is no different.
However, everyone agrees manipulating the form and using Docmd calls is an extremely convoluted approach

Once I got started on the append query I couldn't stop. Enjoy the code below, it will solve your problem.

That definitely does not solve the problem.
Without a requery the new record does not show
WIthout a movelast the new record is not selected.

To demo I did this with an Insert and a Recordset. In either case AS FAR AS I CAN TELL, the new record is added and selected. Can someone explain how this does not meet the requirement?

Private Sub List9_Click()
  If IsNull(Me.subFrmStudents.Form.Team) Then
    MsgBox "Team Required"
  Else
    Me.Dirty = False
   ' InsertRS
   InsertSQL
  End If

End Sub

Public Sub InsertRS()
  Dim frm As Access.Form
  Dim rs As DAO.Recordset
  Set frm = Me.subFrmStudents.Form
  Set rs = frm.Recordset

  frm.Recordset.AddNew
  frm.Dirty = False
  frm.FirstName = Me.List9.Column(1, Me.List9.ListIndex)
  frm.LastName = Me.List9.Column(2, Me.List9.ListIndex)
End Sub
Public Sub InsertSQL()
  Dim strSql As String
  Dim first As String
  Dim last As String
  Dim frm As Access.Form
  Set frm = Me.subFrmStudents.Form
  first = Me.List9.Column(1, Me.List9.ListIndex)
  first = "'" & first & "'"
  last = Me.List9.Column(2, Me.List9.ListIndex)
  last = "'" & last & "'"
' strSql = "insert into tblPersons2 (FirstName, LastName) values (" & first & ", " & last & ")"
  strSql = "insert into tblPersons2 (FirstName, LastName, Team) values (" & first & ", " & last & ", 'Team D')"
  MsgBox strSql
  CurrentDb.Execute strSql, dbFailOnError
  MsgBox "Before Requery"
  frm.Requery
  MsgBox "Before MoveLast"
  frm.Recordset.MoveLast
End Sub

 

[LarryE]

Hello all. I highly appreciate all your input. What I am asking might be confusing and maybe you do not see my logic.

I like LarryE's comment. Thanks Larry
"This is just crazy. What are you doing?"

I have design a text Book System originally without a scanner. It has been implemented by a school where I teach.
Now I am trying to modifying the application to include a scanner. I know it would be a better idea using foreign keys to fill in the fields of the books and the learners automatically but then I have to redo my forms and reports. These reports I struggled for more than a year to achieve.
I could not find any resource to help me with my dynamic crosstab queries. I manage to create a form using dynamic crosstab queries and it produces quite nice reports. I need to change the form into a report and there I will need all your help again.

So is there anybody who are able to make the new added record get selected let me know if it is possible please.

If not I will try to see how I can use MajP solution to match my requirements.

Speak to you guys again. Great job all of you. Thanks

Now we can understand why you said you have struggled in the past. It's because you are not using ACCESS in the manner it was designed to be used. If you are not connecting Primary and Foreign Keys in your tables, it's no wonder you are having trouble. You apparently are not using the relational part of ACCESS at all. And you will continue to struggle with this if you don't re-design this application properly.

It also appears to me by looking at your screen shot in post #40 that you have added many new blank records to your sub-form. Was that only for testing purposes or did you intend to fill in those records later? I hope you are not just creating new records and leaving them blank.

You asked:
"So is there anybody who are able to make the new added record get selected let me know if it is possible please."

If you want to select the Last record on a form without creating a new record, then use the ACCESS VBA command:
DoCmd.RunCommand acCmdRecordsGoToLast
or
DoCmd.GoToRecord acActiveDataObject, , acLast

If you want to Create a new record on a form and select it then use the ACCESS VBA command:
DoCmd.RunCommand acCmdRecordsGoToNew
or
DoCmd.GoToRecord acActiveDataObject, , acNew

But remember, these commands only work on the form that is currently active, so if you want to select a reord on a subform, then you need to set the focus to that subform first.

With Me.frmStudentBooksSubform.Form.Recordset
.AddNew
.Update
End With
Me.frmStudentBooksSubform.SetFocus
DoCmd.RunCommand acCmdRecordsGoToLast

 

[Gasman]

I am out of this, just becoming a joke now.

 

[LarryE]

I am out of this, just becoming a joke now.

I thought maybe there was a glimmer of hope. We will see.

 

[MajP]

Here is a demo of all three methods
1. Manipulating the forms recordset
2. Insert query
3. Using the Application actions (Docmd)

They all work as described (or my interpretation of the ask.) Records are added and the new record is selected. Point is all methods described by respondents in this thread will work if properly coded.

I would recommend the Recordset or Insert query because these are the most direct methods. Using the Docmd methods are not as flexible and very dependent on where the focus is. For example in this case you cannot use the name of the form because it is instantiated as a subform. You have to hope the focus is on the subform.

Private Sub List9_Click()
  If IsNull(Me.subFrmStudents.Form.Team) Then
    MsgBox "Team Required"
  Else
    Me.Dirty = False
   Select Case Me.frameMethod
     Case 1
      InsertRS
     Case 2
       InsertSQL
     Case 3
      InsertForm
   End Select
  End If

End Sub

Public Sub InsertRS()
  Dim frm As Access.Form
  Dim rs As DAO.Recordset
  Set frm = Me.subFrmStudents.Form
  Set rs = frm.Recordset
  'Not passing the required field
  frm.Recordset.AddNew
  frm.Dirty = False
  frm.FirstName = Me.List9.Column(1, Me.List9.ListIndex)
  frm.LastName = Me.List9.Column(2, Me.List9.ListIndex)
End Sub
Public Sub InsertSQL()
  Dim strSql As String
  Dim first As String
  Dim last As String
  Dim frm As Access.Form
  Set frm = Me.subFrmStudents.Form
  first = Me.List9.Column(1, Me.List9.ListIndex)
  first = "'" & first & "'"
  last = Me.List9.Column(2, Me.List9.ListIndex)
  last = "'" & last & "'"
' strSql = "insert into tblPersons2 (FirstName, LastName) values (" & first & ", " & last & ")"
  strSql = "insert into tblPersons2 (FirstName, LastName, Team) values (" & first & ", " & last & ", 'Team A')"
  MsgBox strSql
  CurrentDb.Execute strSql, dbFailOnError
  MsgBox "Before Requery"
  frm.Requery
  MsgBox "Before MoveLast"
  frm.Recordset.MoveLast
End Sub
Public Sub InsertForm()
  Dim frm As Access.Form
  Set frm = Me.subFrmStudents.Form
  Me.subFrmStudents.SetFocus
  DoCmd.RunCommand acCmdRecordsGoToNew
  frm.FirstName = Me.List9.Column(1, Me.List9.ListIndex)
  frm.LastName = Me.List9.Column(2, Me.List9.ListIndex)
  frm.Team = "Team A"
End Sub

In the demo I do not pass a required Team in the recordset method to show that it is still editable. In the Insert method I show the requirement to requery and movelast.

 

[bmaccess]

Hello guys. All I ask was: Instead of the user clicking the asterisk(*) where we going to insert a new record with the mouse I wanted to know if there is code that will do this automatically when a button is clicked. This is what my application requires at the moment. I appreciate all your help in suggesting doing things differently. Thanks

 

[GaP42]

I expect you are struggling to work out what is needed because the underlying db structure is poorly thought out. The OP had a prior thread:

https://www.access-programmers.co.u...t-one-field-is-not-saved-in-the-table.329166/

Advice was provided but I think not acted on ... apparently it works - see post #49, but is finding the changes needed difficult to implement on that structure, and confusing for you

 

[bmaccess]

Here is my ERD again.
I know I can use auto numbers as primary keys. I know that there are not two books and persons with the same name in my application.

If there is anybody interested to see how my application work I am prepare to make a video and show you what I am asking in this post.
Thanks.

 

[ebs17]

code that will do this automatically when a button is clicked

Look at the example. It's the same as what I suggested in #3.
Of course you have to know
- what the desired bound subform is called,
- where the button to execute the instruction is located and
- depends on how the clean reference from button to subform is to be formulated.

The subform should of course be expandable, i.e. not have any specially set up locks. There should also be no additional and conflicting codes.

In the example: The GotoNewRecord button sets the focus on a new record. The SubformRequery button returns the focus to the first record.
Use this example to prove to me that this doesn't work!

 

[bmaccess]

Look at the example. It's the same as what I suggested in #3.
Of course you have to know
- what the desired bound subform is called,
- where the button to execute the instruction is located and
- depends on how the clean reference from button to subform is to be formulated.

The subform should of course be expandable, i.e. not have any specially set up locks. There should also be no additional and conflicting codes.

In the example: The GotoNewRecord button sets the focus on a new record. The SubformRequery button returns the focus to the first record.
Use this example to prove to me that this doesn't work!

Thanks . I will look at the db. I appreciate everyone's effort to assist me.