Create new record and then set focus to it in a subform

[Shane S via AccessMonster.com]

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

 

[Arvin Meyer [MVP]]

You'll need to capture the Key of the new record in order to move to it:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim lngNewKey As Long

Set db = CurrentDB()
Set rst = db.OpenRecordset("MyTable")

rst.Addnew
' set the value of one field to "dirty" the row
rst!Field1 = "Some value"

' at this point, if the table has an AutoNumber field, its value
' will be set and can be retrieved
lngNewKey = rst!AutoNumField

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Shane S via AccessMonster.com]

Hey Arvin,

Thanks for taking the time to reply. Just want to make sure I'm on the same
page with you. I am opening [frmEquipment] to add a new record, do I still
use the 'rst.AddNew' part in your code, if I've already opened [frmEquipment]
in the add mode?

Thanks again,
Shane

You'll need to capture the Key of the new record in order to move to it:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim lngNewKey As Long

Set db = CurrentDB()
Set rst = db.OpenRecordset("MyTable")

rst.Addnew
' set the value of one field to "dirty" the row
rst!Field1 = "Some value"

' at this point, if the table has an AutoNumber field, its value
' will be set and can be retrieved
lngNewKey = rst!AutoNumField

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

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.

[quoted text clipped - 19 lines]

TIA,
Shane

 

[Shane S via AccessMonster.com]

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

I think I covered it all. Thanks for your help Arvin,
Shane

 

[Crystal]

Hi Shane,

I went off-line to compose this ... slightly different than
what you did (same principle) ... and when I came back here
to tell you, you already had it! (that's great) -- I thought
I'd share it anyway

put this code behind your subform:

Sub FindCustomerDetail(ByVal pRecordID As Long)
If Me.Dirty Then Me.Dirty = False

If Nz(pRecordID) = 0 Then Exit Sub
Me.RecordsetClone.FindFirst "[RecordID_fieldname] = " &
pRecordID
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
end if
End Sub

you can call the sub like this:


Forms![mainformname].[subformname].form.FindCustomerDetail 99

Forms![frmCustomers].[subCusDet].form.FindCustomerDetail 99

where 99 is
the ID you want to find OR
a variable holding the ID

If you may set the record from more than one place, this
makes it so you don't have to write the code again. If not,
then you can tuck this away...

Notice that the Sub does not have PRIVATE before it ...

Often, I make this behind forms:

Private Function FindRecord()

and it is what the find comboboxes/listboxes are assigned on
AfterUpdate (or BeforeUpdate, depending if there is
something else I want to do)

You are really getting good at Access I am impressed!

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
(e-mail address removed)

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

I think I covered it all. Thanks for your help Arvin,
Shane

 

[Shane S via AccessMonster.com]

Thanks Crystal. Didn't know you jumped on this forum too. Thanks for the
compliment. I'm staying after it and working hard at trying to understand it
better.

Thanks for sharing your time and talent,
Shane

Hi Shane,

I went off-line to compose this ... slightly different than
what you did (same principle) ... and when I came back here
to tell you, you already had it! (that's great) -- I thought
I'd share it anyway

put this code behind your subform:

Sub FindCustomerDetail(ByVal pRecordID As Long)
If Me.Dirty Then Me.Dirty = False

If Nz(pRecordID) = 0 Then Exit Sub
Me.RecordsetClone.FindFirst "[RecordID_fieldname] = " &
pRecordID
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
end if
End Sub

you can call the sub like this:

Forms![mainformname].[subformname].form.FindCustomerDetail 99

Forms![frmCustomers].[subCusDet].form.FindCustomerDetail 99

where 99 is
the ID you want to find OR
a variable holding the ID

If you may set the record from more than one place, this
makes it so you don't have to write the code again. If not,
then you can tuck this away...

Notice that the Sub does not have PRIVATE before it ...

Often, I make this behind forms:

Private Function FindRecord()

and it is what the find comboboxes/listboxes are assigned on
AfterUpdate (or BeforeUpdate, depending if there is
something else I want to do)

You are really getting good at Access I am impressed!

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
(e-mail address removed)

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

[quoted text clipped - 51 lines]

I think I covered it all. Thanks for your help Arvin,
Shane

 

[Crystal]

Hi Shane,

always nice to see you...I am here occassionally ... when I
saw your name, I read your post

in the code I posted, you can substitute
[RecordID_fieldname] for [RecordID_controlname]

because I name fields and controls the same, I interchange
them. You don't have to have the field on the form for this
to work, but if the name IS a controlname, that will be used
first -- as long as there is not a conflict with a field in
the recordset not on the form (in which case I imagine you
would get an error)

Me!name refers to recordset
Me.name refers to control

if you don't qualify the name, the first place searched is
the form -- and then the recordset

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
(e-mail address removed)

Thanks Crystal. Didn't know you jumped on this forum too. Thanks for the
compliment. I'm staying after it and working hard at trying to understand it
better.

Thanks for sharing your time and talent,
Shane

Hi Shane,

I went off-line to compose this ... slightly different than
what you did (same principle) ... and when I came back here
to tell you, you already had it! (that's great) -- I thought
I'd share it anyway

put this code behind your subform:

Sub FindCustomerDetail(ByVal pRecordID As Long)
If Me.Dirty Then Me.Dirty = False

If Nz(pRecordID) = 0 Then Exit Sub
Me.RecordsetClone.FindFirst "[RecordID_fieldname] = " &
pRecordID
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
end if
End Sub

you can call the sub like this:

Forms![mainformname].[subformname].form.FindCustomerDetail 99

Forms![frmCustomers].[subCusDet].form.FindCustomerDetail 99

where 99 is
the ID you want to find OR
a variable holding the ID

If you may set the record from more than one place, this
makes it so you don't have to write the code again. If not,
then you can tuck this away...

Notice that the Sub does not have PRIVATE before it ...

Often, I make this behind forms:

Private Function FindRecord()

and it is what the find comboboxes/listboxes are assigned on
AfterUpdate (or BeforeUpdate, depending if there is
something else I want to do)

You are really getting good at Access I am impressed!

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
(e-mail address removed)

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

[quoted text clipped - 51 lines]

I think I covered it all. Thanks for your help Arvin,
Shane

 

[RoyVidar]

Me!name refers to recordset
Me.name refers to control

Erm - are you sure about that? Wouldn't the following within a form
event procedure

debug.print me!name & " and " & me.name

give something like

John Doe and frmNameOfMyForm ;-)

assuming "name" is the name of a control or recordsource field, and the
value of it for the current record is John Doe? Of course name is a
property of the form ...

Assuming you have a field in the recordsource called LastName and a
control on a form named txtLastName wouldn't you be able to refer to
the
recordsource field with both

debug.print me!LastName.Value & " and " & me.LastName.Value

and, similarly, form control values with both

debug.print me!txtLastName.Value & " and " & me.txtLastName.Value

?

 

[Crystal]

Good catch, Roy -- that was a bad example. I should not
have used "Name" -- and as soon as I clicked Send, I wanted
to take it back! (is there a way to Edit a post you have
already sent?)

!fieldname is referring to a field in a recordset
..controlname is to refer to a control (and if it is not on
the form, then the recordset will be searched)

try this:

make a field called Test
do not put it on your form but include it in the form
recordset and give it a value

change the NAME of another control on your form --> Test
whose ControlSource is something else and is filled out

make a command button whose event is:

MsgBox MsgBox Me.Recordset!test, , Me.test

You will get two different values -- one from the recordset
and another from the form control

If you change the control name "Test" to something else, so
there is no longer a control named Test, then both values
will come from the recordset

When you make a reference to a form, Access will first
search the controls for the name -- and then the recordset

In this case, however, the bang and the dot can be interchanged.

Likewise, if you have a query and want to use criteria for a
field that is not on the form but is in the recordset, you
can do this:
[forms].[testform].[recordset].[test]
or
[forms]![testform].[recordset]![test]

If you make a recordset object that is not a recordset for a
form, however, you must use ! to refer to a field, ie:

dim r as dao.recordset
set r = currentdb.openrecordset(tablename")
r!fieldname
will work
r.fieldname
will not

The reason that . works to refer to a field in the form
recordset is because it is part of a collection.


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
(e-mail address removed)

 

[RoyVidar]

Good catch, Roy -- that was a bad example. I should not have used "Name" --
and as soon as I clicked Send, I wanted to take it back! (is there a way to
Edit a post you have already sent?)

!fieldname is referring to a field in a recordset
.controlname is to refer to a control (and if it is not on the form, then the
recordset will be searched)

try this:

make a field called Test
do not put it on your form but include it in the form recordset and give it a
value

change the NAME of another control on your form --> Test whose ControlSource
is something else and is filled out

make a command button whose event is:

MsgBox MsgBox Me.Recordset!test, , Me.test

You will get two different values -- one from the recordset and another from
the form control

If you change the control name "Test" to something else, so there is no
longer a control named Test, then both values will come from the recordset

When you make a reference to a form, Access will first search the controls
for the name -- and then the recordset

In this case, however, the bang and the dot can be interchanged.

Likewise, if you have a query and want to use criteria for a field that is
not on the form but is in the recordset, you can do this:
[forms].[testform].[recordset].[test]
or
[forms]![testform].[recordset]![test]

If you make a recordset object that is not a recordset for a form, however,
you must use ! to refer to a field, ie:

dim r as dao.recordset
set r = currentdb.openrecordset(tablename")
r!fieldname
will work
r.fieldname
will not

The reason that . works to refer to a field in the form recordset is because
it is part of a collection.


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
(e-mail address removed)

With regards to the posting, while I think it might be possible to
ask/click a button or whatever through your newsreader to cancel or
delete a reply, I doubt it will be effective on all servers it's been
propagated. Perhaps on some servers it is changed, on others, the
original is left ...

BTW - please do not alter the subject of a thread in a newsgroup -
leave
it as it is.

If you really, really, really feel you have to do that (for instance to
provide some off topic stuff), please ensure you enclose also the
original thread name, else you are going to create confusement for lot
of people!

For instance:
Me! vs Me. (was Find record in subform from somewhere else)

I initially replied to a post titled Me! vs Me. and now you have posted
a reply to that where you have totally changed topic again. This will
make this reply from me to it (continuing the Me! vs Me.) look
completely out of place and ridiculous ...

You posted the following (here I've taken the liberty of altering the
control/field name to avoid the form property ambiguity in the
discussion)

Me!test refers to recordset
Me.test refers to control

in a reply called Re: Me! vs Me. which I addressed. I tried to address
it with a bit of humour, but quite possibly failed.

My point is that dot and bang are completely interchangable in the
above
quoted sample, which was not evident from the reply where you put it.
They do not perform different actions, nor do they refer to different
things! They are equal - which is the case for dot vs bang for *most*
situations when doing forms coding.

If you wish to discuss dot vs bang (or perhaps the parenthesis/quote)
notation when doing form control/form recordcourse referencing, please
go ahead. Then your last test, according to your claim, and the subject
of that reply, should probably look more like this to not be flawed:

MsgBox Me!test, , Me.test

If you wish to change the topic, that's also fine, but then it won't be
interesting to me, so please forgive me for stepping back.

 

[Crystal]

Roy, point taken

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[Shane S via AccessMonster.com]

Crystal,

Thanks for the help and for being so DANG good at it too. Hope being stepped
on doesn't discourage you from lending a hand to posters in the future. Your
too good at explaining how and why things work, to go away.

Thanks again,
Shane

 

[Crystal]

Thank you, Shane

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com