Sub Form record Access/navigatiion

[gary.cobden]

Hi

I have a form/subform where a new record is created on the subform by a
button click.

After the new record is created, I then need to go directly and display
the new record in the subform.

My current code is:-

Private Sub cmdPanelInfoOnly_Click()
On Error GoTo Err_cmdPanelInfoOnly_Click
Dim strSql As String
Dim dbs As Database
Dim rsEpis As Recordset
Dim intPat As Integer

Set dbs = CurrentDb()

strSql = "Select Max([Episode ID]) as MaxId from Episode;"

Set rsEpis = dbs.OpenRecordset(strSql)
[EpisID] = rsEpis.Fields("MaxId").Value + 1

DoCmd.SetWarnings False
intPat = [Patient ID].Value
DoCmd.OpenQuery "qinsEpisodeForPanelOnly" - Inserts the default
values in the new record
DoCmd.SetWarnings True

[Forms]![frmpatient].Form.Filter = "[Patient]![Patient ID] = " & intPat
[Forms]![frmpatient].Form.Requery

'This next bit works, but needs to be sped up!!!!!

Do Until [subfrmEpisode].Form![Patient ID] = [intPat]
DoCmd.GoToRecord , , acNext
Loop

Do Until [subfrmEpisode].Form![Episode ID] = [EpisID]
subfrmEpisode.SetFocus
DoCmd.GoToRecord , , acNext
Loop

Exit_cmdPanelInfoOnly_Click:
Exit Sub

Err_cmdPanelInfoOnly_Click:
MsgBox Err.Description
Resume Exit_cmdPanelInfoOnly_Click

End Sub

I cannot seem to find a more efficient way of navigating directly to
the new sub form record
Can anybody assist please

Thanks

Gary

[Albert D. Kallal]

try:
Dim frmS As Form

[Episid] = DMax("MaxId", "Episode") + 1

CurrentDb.Execute "qinsEpisodeForPaelOnly"

Set frmS = Me.subfrmEpisode.form
frmS.Requery

frmS.RecordsetClone.FindFirst "[Patient id] = " & [Episid]
frmS.Bookmark = frmS.RecordsetClone.Bookmark

The above is ALL the code you need.

What I don't see, or understand is how your qinsEpisodeForPanelOnly" knows
what record to work on?
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[RichardP via AccessMonster.com]

Gary, just out of interest, what's the maximum number of Patients you're
forseeably likely to want to store in the database?
ga*********@nhs.net wrote:

>Hi

I have a form/subform where a new record is created on the subform by a
button click.

After the new record is created, I then need to go directly and display
the new record in the subform.

My current code is:-

Private Sub cmdPanelInfoOnly_Click()
On Error GoTo Err_cmdPanelInfoOnly_Click
Dim strSql As String
Dim dbs As Database
Dim rsEpis As Recordset
Dim intPat As Integer

Set dbs = CurrentDb()

strSql = "Select Max([Episode ID]) as MaxId from Episode;"

Set rsEpis = dbs.OpenRecordset(strSql)
[EpisID] = rsEpis.Fields("MaxId").Value + 1

DoCmd.SetWarnings False
intPat = [Patient ID].Value
DoCmd.OpenQuery "qinsEpisodeForPanelOnly" - Inserts the default
values in the new record
DoCmd.SetWarnings True

[Forms]![frmpatient].Form.Filter = "[Patient]![Patient ID] = " & intPat
[Forms]![frmpatient].Form.Requery

'This next bit works, but needs to be sped up!!!!!

Do Until [subfrmEpisode].Form![Patient ID] = [intPat]
DoCmd.GoToRecord , , acNext
Loop

Do Until [subfrmEpisode].Form![Episode ID] = [EpisID]
subfrmEpisode.SetFocus
DoCmd.GoToRecord , , acNext
Loop

Exit_cmdPanelInfoOnly_Click:
Exit Sub

Err_cmdPanelInfoOnly_Click:
MsgBox Err.Description
Resume Exit_cmdPanelInfoOnly_Click

End Sub

I cannot seem to find a more efficient way of navigating directly to
the new sub form record
Can anybody assist please

Thanks

Gary

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1

[gary.cobden]

Thats great, thanks

What I really want now is to set the focus to the newly added record on
the subform

Thanks

Gary
Albert D. Kallal wrote:

try:
Dim frmS As Form

[Episid] = DMax("MaxId", "Episode") + 1

CurrentDb.Execute "qinsEpisodeForPaelOnly"

Set frmS = Me.subfrmEpisode.form
frmS.Requery

frmS.RecordsetClone.FindFirst "[Patient id] = " & [Episid]
frmS.Bookmark = frmS.RecordsetClone.Bookmark

The above is ALL the code you need.

What I don't see, or understand is how your qinsEpisodeForPanelOnly" knows
what record to work on?
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[Albert D. Kallal]

<ga*********@nhs.netwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...

Thats great, thanks

What I really want now is to set the focus to the newly added record on
the subform

you have to do it in two steps

step 1 - set focus to sub-form control
step 2 - set focus to a field on the sub-form control

so,

me.subfrmEpisode.setfocus

me.subfrmEpsiode.form.NameOfFieldOnForm.setfocus

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com