New record, then set focus to that record in a subform

[AmigoMobility]

Was wondering if someone would lend a hand is trying to accomplish something
that I have been trying to get to work for several hours now.

Down and dirty version:
MainForm [frmCustomers] subForm on [frmCustomers] named [subCusDet]. I use
[subCusDet] as an unbound subform and change it's SourceObject to fill
several different forms depending upon the button that the user have pushed.
[frmEquipOwned] is one of the Forms I use to fill [subCusDet] and it displays
Equipment owned by the customer. The customer can own more than one piece of
Equipment. From the subForm I have a 'NEW' button that opens [frmEquipment],
which allows the user to add a new piece of equipment for the customer. At
Close of the [frmEquipment] I have [subEquipOwned] being Requeried. All works
well but would like it to do one more thing. When [frmEquipment] closes and
Requeries, I would like for Form![frmCustomers]![subEquipOwned] to go to the
new record that has just been added. Was wondering if someone would lend a
hand in getting this last detail taken care of.

I have searched the forum and found similar circumstances but not quite the
same scenario as mine. I tried to massage some of the code I found but I'm
afraid I'm not smart enough to figure it out.

TIA,
Shane

 

[Pampers]

Maybe SetFocus to the subforms 1st field on the Close event of frmEquipment?


Pampers
You never too young to learn

 

[AmigoMobility]

Thanks pampers for your reply but this is a little more complicated than that and setting the focus on the subform is part of what is going on but setting it on the record I want is not whats happening and that's what I need help with.

Thanks,
Shane

 

[Pampers]

Maybe after the setfocus you can use: go to last (=new) record on your subform

Pampers
You never too young to learn

 

[AmigoMobility]

I guess I could try that but I am filtering the subForm using the CustomersID as a criteria so I don't know whether it would like doing that or not.

Shane

 

[AmigoMobility]

I finally got it working and just wanted to post in case someone does a search in the future.

This declared as form-level variable on [frmEquipOwned] and [frmEquipment]
Public m_lNewEquipID As Long

This code in the [frmEquipment] Unload event
' Save the record if not already saved
If Me.Dirty Then Me.Dirty = False
'Check that frmCustomers is open and,if so,update the lNewEquipID variable
If CurrentProject.AllForms("frmCustomers").IsLoaded Then
Forms![frmCustomers]![subCusDet].Form.m_lNewEquipID = Nz(Me.EquipmentID, 0)
End If

This code in [frmEquipment] On Close Event
If Trim(Nz(Me.OpenArgs, "")) = "" Then
Exit Sub
Else
Select Case Me.OpenArgs
Case "frmEONew"
Me.m_lNewEquipID = Me.EquipmentID
If m_lNewEquipID <> 0 Then
'Requery and find the new record in the subform
With Forms![frmCustomers]![subCusDet].Form
.Requery
![cboEquip].Requery
![cboEquip] = Me.EquipmentID
If Not .RecordsetClone.NoMatch Then
.RecordsetClone.FindFirst "[EquipmentID] = " & _
Forms![frmCustomers]![subCusDet].Form.m_lNewEquipID
.Bookmark = .RecordsetClone.Bookmark
End If
End With
End If
End Select
End If

This code behind the cmdNew button on [frmEquipOwned] (form used as SourceObject for subForm on [frmCustomers]![subCusDet].Form)

m_lNewEquipID = 0

Dim stDocName As String
Dim stOArg As String
stDocName = "frmEquipment"
stOArg = "frmEONew"

DoCmd.OpenForm stDocName, , , , acFormAdd, , stOArg

Shane