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
nico5038
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.

Nic;o)
Oct 19 '07 #2
Redbeard
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
nico5038
3,080 Recognized Expert Specialist
Checkout the code in post #8:
http://www.thescripts.com/forum/thre...-bookmark.html

Nic;o)
Oct 19 '07 #4
Redbeard
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()
  2.  
  3.  
  4.     '------------------------------------
  5.     'Declaration of the variable
  6.     '------------------------------------
  7.  
  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
  19.  
  20.     '-------------------------------------
  21.     'End of declaration
  22.     '-------------------------------------
  23.  
  24.     'Close the RecordControlBox
  25.     DoCmd.Close acForm, "frmRecordControlBox"
  26.  
  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
  31.  
  32.     'Initialization of the counter
  33.     Counter = 1
  34.  
  35.     '------------------------------------------------
  36.     'Adding a new record to the duplicated recordset
  37.     '------------------------------------------------
  38.     RSDuplicated.AddNew
  39.  
  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
  46.  
  47.     ' Display message, title, and default value.
  48.     MyValue = InputBox("Enter the Accession Number", "ATCDB - Duplicate Record", RSDuplicated![Accession Number])
  49.  
  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
  60.  
  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
  67.  
  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
  73.  
  74.             '--------------------------------------------------------
  75.             ' Adding the condition report
  76.             '--------------------------------------------------------
  77.  
  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)
  81.  
  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
  87.  
  88.             RecordCounter = 0
  89.  
  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
  96.  
  97.             'if there is at least 1 record
  98.             If rs.RecordCount > 0 Then
  99.  
  100.                 RecordCounter = 1
  101.  
  102.                 Do While RecordCounter <= CollConditionID.Count
  103.  
  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
  107.  
  108.                     'Loop in case there's more than one condition
  109.                     RSDuplicated.AddNew
  110.                     Counter = 0
  111.  
  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
  118.  
  119.                     RSDuplicated![ObjectID] = ObjectID
  120.                     RSDuplicated![ConditionID] = AutoNumberConditionID()
  121.                     RSDuplicated.Update
  122.                     RecordCounter = RecordCounter + 1
  123.  
  124.                 Loop
  125.  
  126.             End If
  127.             '--------------------------------------------------------
  128.             'End of condition ID
  129.             '--------------------------------------------------------
  130.  
  131.             '--------------------------------------------------------
  132.             ' Adding the treatment info
  133.             '--------------------------------------------------------
  134.  
  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)
  138.  
  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
  144.  
  145.             RecordCounter = 0
  146.  
  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
  153.  
  154.             'if there is at least 1 record
  155.             If rs.RecordCount > 0 Then
  156.  
  157.                 RecordCounter = 1
  158.  
  159.                 Do While RecordCounter <= CollTreatmentID.Count
  160.  
  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
  164.  
  165.                     'Loop in case there's more than one condition
  166.                     RSDuplicated.AddNew
  167.                     Counter = 1
  168.  
  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
  175.  
  176.                     RSDuplicated![ObjectID] = ObjectID
  177.                     RSDuplicated.Update
  178.                     RecordCounter = RecordCounter + 1
  179.                 Loop
  180.  
  181.             End If
  182.             '-------------------------------------------------------
  183.             'End of treatment info
  184.             '-------------------------------------------------------
  185.  
  186.  
  187.             'DoCmd.OpenForm "frmDescription", acNormal, , "[Accession Number]='" + MyValue + "'"
  188.  
  189.             '[Accession Number] = Forms!frmDescription!txtAccNum
  190.             'Me!frmDescription.Requery
  191.             DoCmd.OpenForm "frmDescription", acNormal
  192.  
  193.             Dim strCriteria As String
  194.  
  195.             strCriteria = "[Accession Number]='" + MyValue + "'"
  196.             Forms!frmDescription!txtAccNum.FindFirst (strCriteria)
  197.             Forms!frmDescription.Bookmark = Forms!frmDescription!txtAccNum.Bookmark
  198.  
  199.  
  200.         End If
  201.     End If
Any help would be great!
Oct 19 '07 #5
nico5038
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".

Nic;o)
Oct 19 '07 #6
Redbeard
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
nico5038
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.

Nic;o)
Oct 19 '07 #8

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

Similar topics

0
2736
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. ...
3
4175
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. ...
3
2042
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
2
2367
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...
3
1521
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)
5
5857
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
4
22597
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,
5
1990
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)
3
3832
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...
0
9926
marktang
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...
0
9775
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,...
0
10715
jinu1996
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...
1
7952
isladogs
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...
0
7105
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();...
0
5780
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...
0
5974
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4597
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
3
3221
bsmnconsultancy
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.