By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,029 Members | 1,349 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,029 IT Pros & Developers. It's quick & easy.

Sub Form record Access/navigatiion

P: n/a
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

Oct 3 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
Oct 3 '06 #2

P: n/a
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

Oct 3 '06 #3

P: n/a
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
Oct 4 '06 #4

P: n/a

<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
Oct 4 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.