473,320 Members | 1,946 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

go to chosen record on subform after refresh

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
2 2081
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Mary Litten | last post by:
Hi - (This is my very first post) I have gotten to this point of registering to post because I have been spinning my wheels so long, I believe I am all caught up in the weeds. (and mud) I have...
1
by: Reginald Bal | last post by:
Main Form/SubForm When I requery or recalc my parent form after I updated a record in my subform, I lose the focus of that particular record in my subform. Instead, the first record is selected....
2
by: Kaur | last post by:
Hi, I am working in MS Access 2000 and have created two forms. Form 1 is called frmParent (which has a subform called SfrmChild). FrmParent has a list box that lists all the Last Names of...
1
by: Jeff | last post by:
Its a little more complex than just that. From a pop-up dialog I want to repeatedly select an item from a list and press a button to add the item to a subform on another form underneath. I call...
1
by: Per | last post by:
Hi, I have a problem that I can't figure out. I have a database application to keep track of boxes that contain files. For data entry, I have a form with a main form section for the box-specific...
4
by: Steven | last post by:
Hi, Would need some thought about using a button of the existing form to search record before deleting it. Any quick help is very appreciated. Steve
3
by: blakerrr | last post by:
Hi All, I have a strange situation that I can't figure out. The task is quite simple, delete a record from a table. Here is my situation: I have a form called Order Create which cycles through...
1
by: stuart | last post by:
I have a list of records in a subform that a user can either edit or delete. This is an unbound form. If the user deletes a record, I want to refresh the form, and then position the cursor on the...
10
beacon
by: beacon | last post by:
Hi everybody, This is probably going to sound unorthodox, but I have to log records that are deleted...I know, go figure. Anyway, I have a form with a (continuous) subform, and on the subform I...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.