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

go to chosen record on subform after refresh

P: n/a
Hi--I have an unbound form that either edits an existing record or creates a new one and then
refreshes a subform that views the record(s) chosen. What I would like to do from here is go to the
chosen record after the code runs so we can edit filed in that record. I set focus to the subform
and then refresh it--then I want to go to the record brought up from the sql string

Thanks for any ideas
Bob Stafford

et db = CurrentDb
sql = "SELECT * from qryjoincutslide WHERE HistNum = '" & Me.tmpHistNum & "' And Block = '"
& Me.tmpBlock & "' And Slide = '" & Me.tmpSlide & "'"
Set rs = db.OpenRecordset(sql, dbOpenDynaset)

If rs.RecordCount > 0 Then 'record in slide table exists

rs.Edit 'Edits existing record
' TRANSFER DATA TO FIELDS
' rs!Stained = True
' rs!StainDate = Me.adate
rs!Show = True
rs.Update
Me!frmImage.SetFocus
Me.Refresh

Else
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

Set db = CurrentDb
sql = "SELECT * from ColonSlides WHERE SpecID = " & Me.tmpspecid & " And Slide = '" &
Me.tmpSlide & "'"
Set rs = db.OpenRecordset(sql, dbOpenDynaset)
rs.AddNew 'Creates new record
' TRANSFER DATA TO FIELDS
rs!SpecID = Me.tmpspecid
rs!Slide = Me.tmpSlide
' rs!StainBy = "added rec"
' rs!StainDate = Me.adate
' rs!Stained = True
rs!Show = True
rs.Update
Me!frmImage.SetFocus
Me.Refresh

End If
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
al*****@cox.net wrote:
Hi--I have an unbound form that either edits an existing record or creates a new one and then
refreshes a subform that views the record(s) chosen. What I would like to do from here is go to the
chosen record after the code runs so we can edit filed in that record. I set focus to the subform
and then refresh it--then I want to go to the record brought up from the sql string

Thanks for any ideas
Bob Stafford

et db = CurrentDb
sql = "SELECT * from qryjoincutslide WHERE HistNum = '" & Me.tmpHistNum & "' And Block = '"
& Me.tmpBlock & "' And Slide = '" & Me.tmpSlide & "'"
Set rs = db.OpenRecordset(sql, dbOpenDynaset)

If rs.RecordCount > 0 Then 'record in slide table exists

rs.Edit 'Edits existing record
' TRANSFER DATA TO FIELDS
' rs!Stained = True
' rs!StainDate = Me.adate
rs!Show = True
rs.Update
Me!frmImage.SetFocus
Me.Refresh

Else
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

Set db = CurrentDb
sql = "SELECT * from ColonSlides WHERE SpecID = " & Me.tmpspecid & " And Slide = '" &
Me.tmpSlide & "'"
Set rs = db.OpenRecordset(sql, dbOpenDynaset)
rs.AddNew 'Creates new record
' TRANSFER DATA TO FIELDS
rs!SpecID = Me.tmpspecid
rs!Slide = Me.tmpSlide
' rs!StainBy = "added rec"
' rs!StainDate = Me.adate
' rs!Stained = True
rs!Show = True
rs.Update
Me!frmImage.SetFocus
Me.Refresh

End If
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing


Don't know. I might want to store the key to a variable. Then Requery
the records instead of Refreshing. Then find the record again.

Dim rst As Recordset
Set rst = Forms!MainFormName!SubFormName.Form.Recordsetclone
rst.FindFirst "KeyID = " & variablenameofkey
Forms!MainFormName!SubFormName.Form.Bookmark = rst.bookmark
Nov 13 '05 #2

P: n/a
On Fri, 17 Sep 2004 21:36:40 GMT, Salad <oi*@vinegar.com> wrote:
al*****@cox.net wrote:
Hi--I have an unbound form that either edits an existing record or creates a new one and then
refreshes a subform that views the record(s) chosen. What I would like to do from here is go to the
chosen record after the code runs so we can edit filed in that record. I set focus to the subform
and then refresh it--then I want to go to the record brought up from the sql string

Thanks for any ideas
Bob Stafford

et db = CurrentDb
sql = "SELECT * from qryjoincutslide WHERE HistNum = '" & Me.tmpHistNum & "' And Block = '"
& Me.tmpBlock & "' And Slide = '" & Me.tmpSlide & "'"
Set rs = db.OpenRecordset(sql, dbOpenDynaset)

If rs.RecordCount > 0 Then 'record in slide table exists

rs.Edit 'Edits existing record
' TRANSFER DATA TO FIELDS
' rs!Stained = True
' rs!StainDate = Me.adate
rs!Show = True
rs.Update
Me!frmImage.SetFocus
Me.Refresh

Else
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

Set db = CurrentDb
sql = "SELECT * from ColonSlides WHERE SpecID = " & Me.tmpspecid & " And Slide = '" &
Me.tmpSlide & "'"
Set rs = db.OpenRecordset(sql, dbOpenDynaset)
rs.AddNew 'Creates new record
' TRANSFER DATA TO FIELDS
rs!SpecID = Me.tmpspecid
rs!Slide = Me.tmpSlide
' rs!StainBy = "added rec"
' rs!StainDate = Me.adate
' rs!Stained = True
rs!Show = True
rs.Update
Me!frmImage.SetFocus
Me.Refresh

End If
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing


Don't know. I might want to store the key to a variable. Then Requery
the records instead of Refreshing. Then find the record again.

Dim rst As Recordset
Set rst = Forms!MainFormName!SubFormName.Form.Recordsetclone
rst.FindFirst "KeyID = " & variablenameofkey
Forms!MainFormName!SubFormName.Form.Bookmark = rst.bookmark

Thanks--my solution right now is put a counter firld on the form and have it sorted descending byu
that field in the underlying query--puts the searched record on top. NOt the most elegant but it
works for now

b

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.