473,325 Members | 2,792 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,325 software developers and data experts.

How do I find and edit record in continuous subform after deleting a different record

I have been searching for a way to solve my problem for the last few days and have yet to find an answer. Hoping someone here can help.

Some background:
I am building a db that tracks various requirements that we must meet. The requirements are stated in the different documents which apply to our specific project. When a document is revised, many of the requirements remain the same.

My form:
The form that I am having issues with is the form that will be used to select which requirements are replicated in the new revision. I have a main form which shows the new document revision in text boxes. The first subform (lets call it "SubForm1") shows the requirements that were in the previous version of the document. There is a button which, when clicked, makes a copy of the requirement and changes the document ID to the new revision. It also changes the "Copied to New Rev" value to 1 (Yes) and enters the source requirement's ID into the appropriate field (SourceReq) for reference.

The second subform (SubForm2) shows the requirements which have been copied. It has a button which is intended to be used if the user realizes that they copied the requirement by mistake. It should delete the new record and change the source requirement’s "Copied to New Rev" value to "2" (No), but I can't get it to work.

What I've tried:
I made following macro to try and accomplish the task:

Expand|Select|Wrap|Line Numbers
  1. If Not [Form].[NewRecord] Then
  2.  
  3.    SetTempVar
  4.       Name [Req]
  5.       Expression = [SourceReq]
  6.  
  7.    SetTempVar
  8.       Name [NewReq]
  9.       Expression = [Requirement ID]
  10.  
  11.    GoToControl
  12.       Control Name  [SubForm1]
  13.  
  14.    FindRecord
  15.       Find Waht  [TemVars]![Req]
  16.       Match  Whole Field
  17.       Match Case  No
  18.       Search  All
  19.       Search As Formatted   No
  20.       Only Current Field   Yes
  21.       Find First  Yes
  22.  
  23.    SetValue
  24.       Item=[Copied to New Rev]
  25.       Expression=2
  26.  
  27.    GoToControl
  28.       Control Name  [SubForm2]
  29.  
  30.    FindRecord
  31.       Find Waht  [TemVars]![NewReq]
  32.       Match  Whole Field
  33.       Match Case  No
  34.       Search  All
  35.       Search As Formatted   No
  36.       Only Current Field   Yes
  37.       Find First  Yes
  38.  
  39.    RunMenuCommand
  40.       Command  DeleteRecord
  41.  
  42. End If
When I step through the macro, it gives an error (2162) on the step which sets the value of "Copied to New Rev". I'm not very skilled with VB, but I tried converting it anyway. Here's what I came up with:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command53_Click()
  2.  
  3.     If (Not Form.NewRecord) Then
  4.  
  5.         TempVars.Add "Req", Me.SourceReq.Value
  6.         TempVars.Add "NewReq", Me.Requirement_ID.Value
  7.         Forms![Input-Doc-NewRev].SetFocus
  8.         Forms![Input-Doc-NewRev]!SubForm1.SetFocus
  9.  
  10.         With Forms![Input-Doc-NewRev]!SubForm1![Requirement ID]
  11.             DoCmd.FindRecord ("[TempVar]![Req]")
  12.  
  13.             If Not NoMatch Then 'we found the record
  14.                 .Edit
  15.                 [Copied to New Rev].Value = "2"
  16.                 .Update
  17.             End If
  18.  
  19.         End With
  20.  
  21.         Forms![Input-Doc-NewRev].SetFocus
  22.         Forms![Input-Doc-NewRev]!SubForm2.SetFocus
  23.  
  24.         With Forms![Input-Doc-NewRev]!SubForm2![Requirement ID]
  25.         DoCmd.FindRecord ("[TempVar]![NewReq]")
  26.         DoCmd.RunCommand acCmdDeleteRecord
  27.         End With
  28.  
  29.     End If
  30.  
  31.     If (Form.NewRecord And Not Form.Dirty) Then
  32.         Beep
  33.     End If
  34.  
  35.     If (Form.NewRecord And Form.Dirty) Then
  36.         DoCmd.RunCommand acCmdUndo
  37.     End If
  38.  
  39.     If (MacroError <> 0) Then
  40.         Beep
  41.         MsgBox MacroError.Description, vbOKOnly, ""
  42.     End If
  43.  
  44. End Sub

When I run this, I get the same error (2162) and it highlights the first FindRecord line.

The two subforms pull data from two queries which are based off the same table. The only difference between the queries is the document that they are filtered to show.

Any help would be greatly appreciated!

Thanks,
Jill
Jan 16 '13 #1
1 1457
NeoPa
32,556 Expert Mod 16PB
Jill, I'm afraid very little of your explanation means anything to me at all. I suspect that is because you explain things you know about in terms of other things you know about, but which no-one else would.

That's the bad news. The good is that I'm hoping (and suspect) that we won't need to worry too much about how it all fits together as you have made an attempt to identify the line of VBA code that crashes. If it's line #11, as I suspect from your description, then that line certainly has a glaring error in that the value passed is a string value of "[TempVar]![Req]". I'm pretty sure you intended to use the contents of the TempVar called "Req" instead.

I suspect that you'll probably find more errors in the code once that's been cleared away mind you, so I'll post a link (Before Posting (VBA or SQL) Code) to some advice that will help you identify and fix all of your most basic errors without needing to post further. When they are cleared away will be a good time to deal with the rest which may require some experienced assistance.
Jan 17 '13 #2

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

Similar topics

2
by: Simon P | last post by:
Hello group, I'm in desperate need of help. Here goes : I have the following tables : CONTACTS (ContactID, FirstName, LastName, Company, etc.), SHOWS (ShowID, ShowDescription) and SHOWDETAILS...
0
by: RAllsopp | last post by:
I am having trouble deleting a record from a subform. I actually have a main form with two subforms, one dependent on the record selected in the other. The user selects a record to view on the...
4
by: Dave Boyd | last post by:
Hi, I have two very similar forms each with a subform. The main form gets a few fields from the user and passes this back to a query that the subform is bound to. The requery is done when the...
2
by: Karl Roes | last post by:
Thanks Turtle, but ... what for ? Could you please explain a little more. I am wanting to know the status of all check boxes in a continuous form ie. all are 'ticked'. MacDermott wrote - ...
1
by: gavo | last post by:
Hello everyone! Using A2K i have a form(a) with a subform(b) and within the subform there is a continuous subform(c). in the subform (b) there is a command button used to call a public...
5
by: MOC835 | last post by:
I have calendar control that I am trying to use on a continuous subform to select two dates. My MainForm collects user data, and the continuous SubForm collects two dates for that specific...
1
by: Richard | last post by:
A shipment of material is received. The shipment contains several items. Each item is assigned an internal tracking number for auditing purposes and further processed. The tracking number is...
8
by: JohnDoe528 | last post by:
Using Access 2000 on XP, I have a continuous subform that lists the different licenses attributed to a person selected (current record of the main form). This is what each line of the sub form looks...
1
by: Chipperzs | last post by:
All, First time user, I tried searching for related posts but none pertaining to my specific problem. I have a form with a "Continuous" subform on it. The Continuous subform has a combo box...
1
by: sknaina | last post by:
Hi Eveybody, I've a main form with a continuous subform based on 'DonorTranSmasterF' & 'DonorTranSdetailsF' , both tables are linked via 'DonorTransiD' field. I know & learn , How to save record...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
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: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
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...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.