SetFocus on last record of a subform

I have a main form that I am using as a chat client and within the main form is a subform that I have set as a continuous form. The user would enter text into a textbox and then click send, then the message entered is saved onto a table that is shown on the subform. Eventually when enough messages have been entered into the subform the user can use a scroll bar to scroll up and down to see previous messages. Everytime the subform refreshes, it goes right back up to the first record. I am trying to set the focus onto the last record when a user enters the chat client and when the subform has been refresh. I have tried everything I could think of nothing seems to work. I appreciate the help guys.


Post your answer or comment

comments powered by Disqus
hi i would like to move to next record of a subform on a tabbed form from a different tabbed form. I have the following which works but i was after something that was not physically moving back and forth on the forms.

Private Sub next4_Click()
'Next Record
On Error GoTo Err_next4_Click
Forms![frmDocumentRegister]![TabCtl0] = 0
DoCmd.GoToRecord , , acNext
Forms![frmDocumentRegister]![TabCtl0] = 1
Exit Sub
MsgBox err.Description
Resume Exit_next4_Click
End Sub

Hello all,
I have two subforms (incorporated in a main form “Service”), I would like each subform “contracts” and “Visits” to open on last record of each subform for that “Service” record.

I have tried on both subforms open: DoCmd.GoToRecord , , acLast
but they both still open on the first record. Any ideas?



I have a form that the user will fill in, then click a command button to print a report (to a create a label)based on the entered and calculated fields.

The report is sourced from a query that needs to select only the last record of the base table. I've been successful at getting it to filter based on the primary key that I pick up from the form. However, this will require several different queries to cover all the forms that will print similar information.

1. Is there are way to use a variable in the Criteria field in the query? The variable can be set in each of the forms in vb.
2. If not, is there a way to get the query to simply return the last record in the table? This might be the simplest solution.


I have a database in access 2007. The main form has a subform and I would like a blank new record as the first record of a subform. Is there a way to do that without making two separate subforms (one for a blank new record subform and the other for records that already exist)?
I really appreciate it if sombody could help me.

Thank you,

I need to have the sum of the "matching records" of a subform, exported to a variable of the main form, in order to use it in an if condition.

e.g. "IF a client has brought X? times the vehicle A for a service of type B, THEN do ..."
How do I get the X value in a variable within the main form which presents all activity for all clients (by means of a subform).

I hope this is clear.
Plaese help me, guys. You 've done it before, you're so great!

I have a form (Transactions) with a subform (Detail), In a query what I'd like to do is show all the transactions that have a check box checked (That's easy enough). Then on thoughs results I'd like to show the last record of the detail section. The detail section can have many records and I just want to show the last one. The results of the query will be the source of a report in the end.

I have searched the site on this and could not find any thing.
I would love any ideas!


I've created a query (qryActivities) which looks up records in a table (tblActivity), breaks them down to specific MemberID's and then sorts them first by date, then by ActivityTypeID if there are more than one activity on a given day. This works great when displaying the activities in a subform for each member.

Now, I'd like to take this a step further and have a checkbox either ticked or not ticked based on the last activity for each MemberID, i.e. if a members last activity is Suspended (ActivityTypeID of 2) a checkbox (cbSuspended) on the main form would be ticked.

So far, I haven't had much luck getting the criteria correct, so any help would be greatly appreciated.



I have come across numerous postings about setting the record source of a subform but I am still not able to find a solutio to my problem.

I am using Access 2007 and based on a query constructed in a search form I am setting the record source of a subform on a new form I am opening to display the search results.

I am using the following code:

	[Form_New Course Booking]!SfStudentSearchResults.Form.RecordSource = studentSearchQuery + whereClause + ";"
MsgBox ([Form_New Course Booking]!SfStudentSearchResults.Form.RecordSource)
DoCmd.OpenForm "New Course Booking"

After I set the record source the message box displays the correct query as the record source but when the new form is opened it still displays the old/default record source.

I would really appreciate it if somebody could guide me in the right direction here.

Many Thanks

I am trying to add a new record to a subform from a button on the main form but when I do, the subform's blank new record does not "jump" to the focus like when I click on any of the subform records and hit Insert-->New Record.

Here is what I have on the button:

Forms!F_Parameters!F_Parameter_subform!tbxParamete rName.SetFocus
DoCmd.GoToRecord , , acNewRec

I am trying to Open a subform on the last record each time the main form records are navigated

when i try the following:

Private Sub Form_Current()
DoCmd.GoToRecord , , acLast
End Sub

it works, but the record is locked, i cant add new record or navigate. when i try this command (DoCmd.GoToRecord , , acLast) on form load it only works on the first record when the form loads, when i switch the records on my main form (clients) the subform (client stages) open on the first record. any ideas on how to open a subform on the last record even when the record on the main form changes? i hope i am clear. thanks.


I have a switchboard that lets you choose between three forms. Each of the three forms contains 1 subform. I have the following code in the Switchboard:

Quote: ' Open a form in Edit mode.
Case conCmdOpenFormBrowse
DoCmd.OpenForm rs![Argument]
DoCmd.GoToRecord , , acLast On my first form, this does exactly what I want it to do. It pulls up the last record of the form. If I choose the second or third option from the Switchboard, then the main form opens on the first record and the subform goes to the last record. This happens regardless of which button I choose first after opening the Switchboard. The first button works the way I want it, but the other two do not.

If anyone has any suggestions, please let me know. Thanks!!

I have a form with a subform on it and a button. If i press the button, i want a new record being added in the subform automatically.
I tried several things (invane):
1. using a recordset on the table of the subform, is impossible because i have a form open on this table
2. I tried to use recordsetclone but i understood that this is a static copy of the records in the subform and you can't use it to add another record.
3. I tried to use setfocus eg forms!mainform!subform.form![field].setfocus and docmd.gotorecord,,aclastrecord. But without success.
4. I tried to use a recordsetclone and then to bookmark the subform to the recordsetclone.bookmark in order to put the focus on a record in the subform, but nothing happens.

Does anyone has an idea? This task doesn't seem very complicated to me, but it's driving me mad in realizing it.

I have a form (Resident-Lease) that includes a subform(Occupancy) inside of a subform(Lease). I have a check box on the Lease subform that will enable or disable the Occupancy subform that is inside it whether it is checked or unchecked.

I have a next record button on the main form (Resident-Lease). I want to make it so after it goes to the next record, that it will check whether the check box is checked or not, and depending on that enable or disable the Occupancy subform.

I am totally at a loss to how to do this. Can anyone lend a hand? I hate class projects....

I have a form with a subform; when a record is chosen from the main form, the related subform can display one or more associated records. I also have a chkbox on the main form that when checked, uses vba code to loop through the subform records to create a list of recipients for a follow-on e-mail. The problem is that it's only partially working and I can't see what I'm doing wrong. Should I place the code and chkbox onto the subform? Would that help or hinder things? Here's my code as it stands right now:

With Me![subfrmSessionInstructor].Form.InstructorID

inumInstructors = .ListCount - 1
For iPtr = 0 To inumInstructors

TheId = Me![subfrmSessionInstructor].Form.InstructorID
strCriteria = "SessionId = " & TheId
rst1.FindFirst strCriteria
If rst1.NoMatch Then
GoTo checknext
sSql = "select FNameInst, LNameInst, UserIDInst from tblInstructors "
sSql = sSql & "where InstructorID = " & rst1!InstructorID & ";"

Set rstDef = gdbData.OpenRecordset(sSql, dbOpenForwardOnly)
strInstructor = rstDef!FNameInst & " " & rstDef!LNameInst & " " & rstDef!UserIDInst
End If

Next iPtr
End With

Thank you all for your help!


I have a sub form which, 90% of the time, I want to come from QryA. In one case, however, I want it to come from QryB. I have used the following code:

DoCmd.OpenForm "FrmAttach"
Forms("FrmAttach").RecordSource = "QryAttachFilter"

This works well, opening the form, and then removing certain records (The query is acting like a filter)
However, when i then open the main form, where this form is a sub form the new record source is not brought through.

Is there a way to change the record source of a subform in a similar way?

Thank you

I found this article from Microsoft which demonstrates how you can press TAB key or ENTER key to move from the last control on the last record of a subform to a specified control on the main form.

The code works great when there is data in the record, but if there is no data you constantly get this error message: "You must be on a record with data."

Here is the code:
Private Sub Discount_Exit(Cancel As Integer)

On Error Goto Error_Routine

Dim RS As DAO.Recordset
Set RS = Me.RecordsetClone
If StrComp(Me.Bookmark, rs.Bookmark, 0) = 0 Then
Forms![Orders]![Orders Subform].Requery
End If
Exit Sub

MsgBox "You must be on a record with data"
Exit Sub

End Sub

Is there a solution to this problem?


I am having trouble getting Focus on a new record in a subform. I have used the GoToSubform Macro On Activate. If I open the subfrom separately then this works. The focus is on a new Record. However, When I open my main form and then click on my subform the focus does not go to the new record. I think what is happening is that it is going to the new record on the subform in total but not going to a new record on the subform relating to the record in the main form.

Can anyone help?



I have managed to create a button that when clicked will GetFocus and allow me to enter a new record at the bottom of a subform. However, I would like to be able to enter the new record at the top of the subform. Any Ideas?



How can I get the scrollbar position of a subform (which is inside of a form)? It already has the scrollbar on the side. The subform is just populated with text, not records. I just don't know what code to use to determine the position of the scrollbar (I want to find out when it reaches the end). Thanks!

Hi forum,

I have had experience in the past of using a query to find the last record and it returns false data.

What I want to do is find the last record of an account payment.
Record set has AccountNum , PayDate and PayAmount.
I need the most recent record for each AccountNum.

Another example is to find that most recent Letter Sent.
We have AccountNum, LetterDate
Again, most recent record for LetterDate of each AccountNum.

I don't so much have a problem with the query/sql, just what method can be relied on??

Should this be done with VBA??

Appreciate some advice as to get this one wrong could be embarrassing.

I solved the problem before by using a Temp Table and a bucket of queries but that was before I declared that such items will be view and far between.

Changing records using another form in access?

Well, see, I was making a video rental database for school, and I have a form named 'Rent', and its source is the query 'Rental Data'. In it is a subform, named 'Renting data subform' which source is another query, 'Renting Data'.

What I want to do is, when I press a command button 'Exit' in the Rent form, I want to change all the records in the Renting Data subform: the field 'stock' subtract another field 'quantity rented', also from Renting Data subform. The answer will be the new 'stock'.

And then, after that it's simply exit the form, which is no problem.

The thing is, I tried writing a command and it only works for when the focus is on that record. I want it to work for every record all at once without changing the focus. The focus wouldn't be in the subform and I don't want people focusing on individual records just to update the stock.

Can anyone tell me how to do that?

So I have a main form, and in the header I have a combo box where a user can search for contacts, but I want the box to search for records in a subform, not on the main form. On the combo box's After Update property I have this code:

	Private Sub cboNameSearch_AfterUpdate()
On Error GoTo cboNameSearch_AfterUpdate_Err

    If (IsNull(Screen.ActiveControl)) Then
        Exit Sub
    End If
    On Error Resume Next
    If (Form.Dirty) Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    If (MacroError.Number  0) Then
        MsgBox MacroError.Description, vbOKOnly, ""
        Exit Sub
    End If
    On Error GoTo 0
    TempVars.Add "ActiveControlValue", "[Screen].[ActiveControl]"
    If (CurrentProject.IsTrusted) Then
        Screen.ActiveControl = Null
    End If
    If (Form.FilterOn) Then
        DoCmd.RunCommand acCmdRemoveFilterSort
    End If
    DoCmd.SearchForRecord acForm, Forms![Marketing Targets]!MarketingTargetsSubform.Form, acFirst, "[Contact ID]=" &
    TempVars.Remove "ActiveControlValue"

    Exit Sub

    MsgBox Error$
    Resume cboNameSearch_AfterUpdate_Exit

End Sub

In the body of the form I have a subform with the control name "MarketingTargetsSubform", and the main form's name is "Marketing Targets". I'm trying to use a simple doCmd.GoToRecord, but I keep getting the error "An expression you entered is the wrong data type for one of the arguments", and then in the debugger it jumps to the key line:

	DoCmd.SearchForRecord acForm, Forms![Marketing Targets]!MarketingTargetsSubform.Form, acFirst, "[Contact ID]=" &

The subform is not linked to the main form in any way (no child or master fields)

I've checked syntax and googled as much as I can on the topic. Any suggestions?

Access 07
Windows 7

I've trapped the SHIFT+TAB and TAB keystrokes within the keydown event of a subform to behave appropriately (switch back and forth between subform1 and subform2). However, my current problem is this:

Subform1 and Subform2 have 3 textbox controls each. If I am on control2 of subform2, and mouseclick to subform1, then tab to control1, then control2, then control3 and tab again to appropriately switch to subform2, the focus is always set to the previous active control of subform2 that I clicked out of. This only happens if I mousclick out of subform2. Tabbing works just fine.

I would like that focus to ALWAYS go back to control1 of subform2 when I click out of subform2 to subform1 and tab back to subform2. Confusing enough? I hope not.

I'm fairly proud of myself for figuring out a way to trap these keystrokes to move between the two subforms properly, rather than using the OnExit event, so please no floggings for doing this the complicated way. I have specific reasons for why I need this to behave accordingly. Mostly being that I don't want people to be able to go to control2 of subform2 if control1 of subform1 has nothing in it.

If any advice is available to get the focus to reset to control1 after mousclicking out of subform2 and tabbing back to subform2, I would be very grateful.

Here is my keystroke trap code from the OnKeyDown event of Subform1:

	Private Sub Form_KeyDown(Keycode As Integer, shift As Integer)
If (Keycode = vbKeyTab) And ((shift And acShiftMask) > 0) Then
    If Screen.ActiveControl.Name = Me.Vest_New_PIDtxtbox.Name Then
    ElseIf Screen.ActiveControl.Name = Me.Vest_New_Due_Datetxtbox.Name Then
    End If
End If
If (Keycode = vbKeyTab) And ((shift And acShiftMask) = 0) Then
    If Screen.ActiveControl.Name = Me.Vest_New_Due_Datetxtbox.Name Then
        Keycode = 0
    End If
End If
End Sub

I wondered if anyone could help as i've been at this for 2 days now:

The following article 294202 which can be found by Googling "MS ACCESS ENUMERATE RECORDS" (sorry but i don't have enough posts to give the link directly)

describes how to enumerate records in a form (which i've managed to get working) - so when you select several contiguous records then click a command button, a message box pops up and tells you which records you just selected.

At the end of the article it gives a hint about how to do the same with the command button on a main form and the records in a subform (rather than with the button and records all on the one form).

After 2 days perseverance I just can't get it to work.

Could anyone spare 10 mins to go through the article and try to get it working with the button on a main form and data in a subform? The code can just be copied and pasted directly from the article.


Not finding an answer? Try a Google search.