473,881 Members | 1,718 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Trying to change code that open a form and filters out records that were not altered

112 New Member
Hi, I am newbie that is using Access 2003. I have a command button on a pop-up form that duplicates the current record displayed on my main form and all sub-forms attached to it. The last part of the code directs the form to reopen and filters the form for the duplicated record, only displaying it. Is there any way of having the form reopen and going to the duplicated record without filtering all the other records out?

Here is the last line of the code:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frmDescription", acNormal, , "[Accession Number]='" + MyValue + "'"
If all the code is need I will post it but it is rather long.
Oct 18 '07 #1
7 1399
3,080 Recognized Expert Specialist
For this you'll need to use a bookmark and (when not established before) a .find command.

Check it out and let me know or more assistance is needed.

Oct 19 '07 #2
112 New Member
Sorry I don't know code that well what is a bookmark? and how do I code the find command?
Oct 19 '07 #3
3,080 Recognized Expert Specialist
Checkout the code in post #8:

Oct 19 '07 #4
112 New Member
Thanks for the direction but I am still having problems. Ok here is the whole code... I am trying to get them last bit at the bottom to work with no luck. After the records are duplicated I am try it find the duplicated record in the rest of the records instead of filtering for it.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Duplicate_Record_Click()
  4.     '------------------------------------
  5.     'Declaration of the variable
  6.     '------------------------------------
  8.     Dim rs As Recordset             'recordset that contain the original record
  9.     Dim RSDuplicated As Recordset   'recordset use to duplicate the record
  10.     Dim QuerySelect As String       'represents each SQL query
  11.     Dim MyValue As String           'represents the new accession number
  12.     Dim ObjectID As Integer         'represents the new ObjectID of the duplicated record
  13.     Dim OldObjectID As Integer      'represents the old ObjectId of the original record
  14.     Dim Counter As Integer          'represents the counter use for many loops
  15.     Dim RecordCounter As Integer    'represents a counter for each record duplicated
  16.     Dim ConditionID As String       'represent a conditionId number
  17.     Dim CollConditionID As New Collection   'represents the list of each conditionId record to be duplicated
  18.     Dim CollTreatmentID As New Collection   'represents the list of each treatmentId record to be duplicated
  20.     '-------------------------------------
  21.     'End of declaration
  22.     '-------------------------------------
  24.     'Close the RecordControlBox
  25.     DoCmd.Close acForm, "frmRecordControlBox"
  27.     'Retrieving data from the record to be duplicated
  28.     QuerySelect = "Select * From Description where Description.[Accession Number] = '" & Forms!frmDescription!txtAccNum.Value & "'"
  29.     Set rs = CurrentDb().OpenRecordset(QuerySelect)
  30.     Set RSDuplicated = rs.Clone
  32.     'Initialization of the counter
  33.     Counter = 1
  35.     '------------------------------------------------
  36.     'Adding a new record to the duplicated recordset
  37.     '------------------------------------------------
  38.     RSDuplicated.AddNew
  40.     'starting the loop.
  41.     '***should not be a constant, in case the table change! ***
  42.     Do While Counter < 47
  43.         RSDuplicated.Fields(Counter) = rs.Fields(Counter)
  44.         Counter = Counter + 1
  45.     Loop
  47.     ' Display message, title, and default value.
  48.     MyValue = InputBox("Enter the Accession Number", "ATCDB - Duplicate Record", RSDuplicated![Accession Number])
  50.     'Check if the Accession Number is correct, otherwise, show a new dialog box
  51.     If Len(MyValue) = 0 Then
  52.         'cancel everything, show cancel popup
  53.         MsgBox "This action was canceled by the user. No data has been duplicated"
  54.     Else
  55.         While Len(MyValue) > 0 And AccessionNumberPresent(MyValue) = True
  56.             'show a new popup until it works
  57.             MsgBox "This Accession Number already exists, try another one."
  58.             MyValue = InputBox("Enter the Accession Number", "ATCDB - Duplicate Record", RSDuplicated![Accession Number])
  59.         Wend
  61.         If Len(MyValue) = 0 Then
  62.             'cancel everything, show cancel popup
  63.             MsgBox "This action was canceled by the user. No data has been duplicated"
  64.         Else
  65.             'Update the data to the table
  66.             RSDuplicated![Accession Number] = MyValue
  68.             'update the objectid number, cuz the autonumber doesn't work properly (bug?)
  69.             OldObjectID = rs![ObjectID]
  70.             ObjectID = AutoNumberObjectID()
  71.             RSDuplicated![ObjectID] = ObjectID
  72.             RSDuplicated.Update
  74.             '--------------------------------------------------------
  75.             ' Adding the condition report
  76.             '--------------------------------------------------------
  78.             'i stock the condition Id on a collection
  79.             QuerySelect = "Select * From [Condition Info] where [Condition Info].[ObjectID] = " + CStr(OldObjectID)
  80.             Set rs = CurrentDb().OpenRecordset(QuerySelect)
  82.             'i go through the recordset to get all the conditionId
  83.             If Not rs.EOF Then
  84.                 rs.MoveLast
  85.                 rs.MoveFirst
  86.             End If
  88.             RecordCounter = 0
  90.             'putting the condition id into the collection
  91.             Do While RecordCounter < rs.RecordCount
  92.                 CollConditionID.Add (CStr(rs![ConditionID]))
  93.                 rs.MoveNext
  94.                 RecordCounter = RecordCounter + 1
  95.             Loop
  97.             'if there is at least 1 record
  98.             If rs.RecordCount > 0 Then
  100.                 RecordCounter = 1
  102.                 Do While RecordCounter <= CollConditionID.Count
  104.                     QuerySelect = "Select * From [Condition Info] where [Condition Info].[ConditionID] = " + CStr(CollConditionID.Item(RecordCounter))
  105.                     Set rs = CurrentDb().OpenRecordset(QuerySelect)
  106.                     Set RSDuplicated = rs.Clone
  108.                     'Loop in case there's more than one condition
  109.                     RSDuplicated.AddNew
  110.                     Counter = 0
  112.                     'starting the loop.
  113.                     'should not be a constant, in case the table change!
  114.                     Do While Counter < 6
  115.                         RSDuplicated.Fields(Counter) = rs.Fields(Counter)
  116.                         Counter = Counter + 1
  117.                     Loop
  119.                     RSDuplicated![ObjectID] = ObjectID
  120.                     RSDuplicated![ConditionID] = AutoNumberConditionID()
  121.                     RSDuplicated.Update
  122.                     RecordCounter = RecordCounter + 1
  124.                 Loop
  126.             End If
  127.             '--------------------------------------------------------
  128.             'End of condition ID
  129.             '--------------------------------------------------------
  131.             '--------------------------------------------------------
  132.             ' Adding the treatment info
  133.             '--------------------------------------------------------
  135.             'i stock the treatment Id on a collection
  136.             QuerySelect = "Select * From [Treatment Info] where [Treatment Info].[ObjectID] = " + CStr(OldObjectID)
  137.             Set rs = CurrentDb().OpenRecordset(QuerySelect)
  139.             'i go through the recordset to get all the treatmentId
  140.             If Not rs.EOF Then
  141.                 rs.MoveLast
  142.                 rs.MoveFirst
  143.             End If
  145.             RecordCounter = 0
  147.             'putting the treatment id into the collection
  148.             Do While RecordCounter < rs.RecordCount
  149.                 CollTreatmentID.Add (CStr(rs![TreatmentID]))
  150.                 rs.MoveNext
  151.                 RecordCounter = RecordCounter + 1
  152.             Loop
  154.             'if there is at least 1 record
  155.             If rs.RecordCount > 0 Then
  157.                 RecordCounter = 1
  159.                 Do While RecordCounter <= CollTreatmentID.Count
  161.                     QuerySelect = "Select * From [Treatment Info] where [Treatment Info].[TreatmentID] = " + CStr(CollTreatmentID.Item(RecordCounter))
  162.                     Set rs = CurrentDb().OpenRecordset(QuerySelect)
  163.                     Set RSDuplicated = rs.Clone
  165.                     'Loop in case there's more than one condition
  166.                     RSDuplicated.AddNew
  167.                     Counter = 1
  169.                     'starting the loop.
  170.                     'should not be a constant, in case the table change!
  171.                     Do While Counter < 5
  172.                         RSDuplicated.Fields(Counter) = rs.Fields(Counter)
  173.                         Counter = Counter + 1
  174.                     Loop
  176.                     RSDuplicated![ObjectID] = ObjectID
  177.                     RSDuplicated.Update
  178.                     RecordCounter = RecordCounter + 1
  179.                 Loop
  181.             End If
  182.             '-------------------------------------------------------
  183.             'End of treatment info
  184.             '-------------------------------------------------------
  187.             'DoCmd.OpenForm "frmDescription", acNormal, , "[Accession Number]='" + MyValue + "'"
  189.             '[Accession Number] = Forms!frmDescription!txtAccNum
  190.             'Me!frmDescription.Requery
  191.             DoCmd.OpenForm "frmDescription", acNormal
  193.             Dim strCriteria As String
  195.             strCriteria = "[Accession Number]='" + MyValue + "'"
  196.             Forms!frmDescription!txtAccNum.FindFirst (strCriteria)
  197.             Forms!frmDescription.Bookmark = Forms!frmDescription!txtAccNum.Bookmark
  200.         End If
  201.     End If
Any help would be great!
Oct 19 '07 #5
3,080 Recognized Expert Specialist
I guess that you want to open "frmDescription " with the bookmark positioning.
This will imply that you don't need the current "Filter passing", but add some comma's in front of it so it becomes the last parameter named "OpenArgs" of the OpenForm command.
In the "frmDescription " you'll have to use the OnOpen event to intercept the passed string from Me.OpenArgs
This will hold the string "[Accession Number]='" + MyValue + "'"
For the find needed for the bookmark this string can be used "straight away".

Oct 19 '07 #6
112 New Member
I am sorry you are going right over my head. First of all what lines from 187 down do I need to keep and what ones are wrong? What is "OpenArgs"? and how do I use it here and how do I get it to intercept the code on the OnOpen event in frmDescription? Sorry I am a newbie.
Oct 19 '07 #7
3,080 Recognized Expert Specialist
Somewhere you'll have to open a form and I thought the last commented out Docmd.Openform was that position as it's after the loop with the addition of records.

For the OpenArgs you can check the F1 (help) description of the OpenForm command.

Oct 19 '07 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Frances | last post by:
Hi All, I'm having a problem trying to add a record to a simple Access 2000 db (db is very similar to an address book but with more info than the usual address, phone, etc.). The database is one table, 36 fields. The record_id field is an autonumber field (long int) and primary key. The rest of the fields comprise of 30 text fields, 3 memo fields, 1 date field and 1 currency field. Text fields vary in number of characters allowed. ...
by: Sam Gledhill | last post by:
Hi, I'm hoping someone can help me out. I have a database with a form. This form has no recordsource (I'm using it as a complex MsgBox). It has a single textbox of type Date. It also has three unrelated subform that are filtered according to the date entered in the form's text box. By using after update events, I've been able to allow users to change the date in the textbox and alter records from the day throughout three other tables. ...
by: Alicia | last post by:
Hi, I am trying to match two queries and make it into one query. I have only been about to group similar attributes but nothing that looks like the FinalQuery below. If anyone knows of a query that would create the final result that would be great. Thanks in advance. The FIRSTQUERY looks like this Month Week OpenedCount May Week: 3 3 May Week: 4 6
by: KashMarsh | last post by:
Access 2003 I need to have a user filter records on a linked, continuous form and then I want to run various reports/queries from this recordset the user created. I only need to see the PK values they filtered for. On the form, there could be 10 controls displaying that the user filters/sorts in whatever way they want to, but I only need to extract the control holding the PK values as a "recordset" so that I can use this to create a...
by: sparks | last post by:
I have 2 fields that I have to make sure don't get screwed up. (could be one it doesn't matter) but what they want is no duplicates, warn the user changing existing numbers is a no no. (of course they will have to change errors so this is kinda WHAT ??) the way it is set up is they put in an account number then a person id number (which is text LOL)
by: sparks | last post by:
I have a form that is broken down into 24 sub forms. these are all tied to one subform, they are filtered based on other things. But I need to change the sub forms source object based on a selection. I was going to try this.. For Each doc In dbs.Containers("Forms").Documents frmname = doc.Name
by: Bob | last post by:
Hi all, I've got a table that I've imported and it has junk at the top of the table, so after import I run a delete query to remove the junk lines then I'm left with the field names I want for the table at the top of the table but the field names currently are 'field1' etc, so how do I rename the field names to the fields on the top row of the table. Cheers,
by: agarwasa2008 | last post by:
Hi, I have a linked table called tbltest and some bounded forms (which add, update, delete records) that were created using that linked table. For some necessary reasons I had to create another linked table called tblComponents and then I bounded the original forms to this new table. I did change the control source: Description (Field name) RowSource Type: Table/Query (which remains the same)
by: Coll | last post by:
I have a form that had been setup with a combo box. You'd select a value from the combo box and a query would open and display matching records. I now need to remove the combo box and set up a text box so the user types in a value and the query opens displaying any records that contain that value in the field. For example, the user enters "smith" in the text box. The query opens, and filters for any records that contain *smith* in the name...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.