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 cmdPanelInfoOnl y_Click()
On Error GoTo Err_cmdPanelInf oOnly_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.OpenRecords et(strSql)
[EpisID] = rsEpis.Fields(" MaxId").Value + 1

DoCmd.SetWarnin gs False
intPat = [Patient ID].Value
DoCmd.OpenQuery "qinsEpisodeFor PanelOnly" - Inserts the default
values in the new record
DoCmd.SetWarnin gs 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.GoToRecor d , , acNext
Loop

Do Until [subfrmEpisode].Form![Episode ID] = [EpisID]
subfrmEpisode.S etFocus
DoCmd.GoToRecor d , , acNext
Loop

Exit_cmdPanelIn foOnly_Click:
Exit Sub

Err_cmdPanelInf oOnly_Click:
MsgBox Err.Description
Resume Exit_cmdPanelIn foOnly_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.Execu te "qinsEpisodeFor PaelOnly"

Set frmS = Me.subfrmEpisod e.form
frmS.Requery

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

The above is ALL the code you need.

What I don't see, or understand is how your qinsEpisodeForP anelOnly" 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 cmdPanelInfoOnl y_Click()
On Error GoTo Err_cmdPanelInf oOnly_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.OpenRecords et(strSql)
[EpisID] = rsEpis.Fields(" MaxId").Value + 1

DoCmd.SetWarni ngs False
intPat = [Patient ID].Value
DoCmd.OpenQuer y "qinsEpisodeFor PanelOnly" - Inserts the default
values in the new record
DoCmd.SetWarni ngs 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.GoToRecor d , , acNext
Loop

Do Until [subfrmEpisode].Form![Episode ID] = [EpisID]
subfrmEpisode.S etFocus
DoCmd.GoToRecor d , , acNext
Loop

Exit_cmdPanelI nfoOnly_Click:
Exit Sub

Err_cmdPanelIn foOnly_Click:
MsgBox Err.Description
Resume Exit_cmdPanelIn foOnly_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.c om
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.Execu te "qinsEpisodeFor PaelOnly"

Set frmS = Me.subfrmEpisod e.form
frmS.Requery

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

The above is ALL the code you need.

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

[Albert D. Kallal]

<ga*********@nh s.netwrote in message
news:11******** **************@ i3g2000cwc.goog legroups.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.subfrmEpisod e.setfocus

me.subfrmEpsiod e.form.NameOfFi eldOnForm.setfo cus

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