Deleting Record Using Main/Subform While Maintaining Record Position

Jul 6, 2006

Hello All,

I have an ordering db that utilyzes the typical Form / Subform layout. I
would like to be able to delete a record from the Main form and maintain the
same postion relative to the other records. For example, if I am scrolling
through the records and want to delete record #45, After I delete it I would
like to be able to have record #44 visible to the user.

I am currently using (2) SQL statements to delete the records from the main
Order table and the Order Details table which are linked without Cascade
Delete Related Records being on.

I had set-up a recordset procedure to try and find the bookmark set before
deletion but the only way I can get the sub-form to not show a blank form (no
controls visible for the record just deleted and the #deleted in controls on
the main form) is to requery after the delete, which loses the bookmark. If
i place the ReQuery at the end it displays the records properly, (without the
blank record) but returns the record to the first record. I have tried
turning off any sorting references to OrderBy, etc. but it still returns to
the first record.

I have also tried using "DoCmd.RunCommand acCmdDeleteRecord" but it doesn't delete the record from both tables.



Dim rst As Recordset
Dim strSearchName As String

Me.AllowDeletions = True
Me.sfmOrderDetails.Form.AllowDeletions = True

Set rst = Me.RecordsetClone
strSearchName = Str(Me.sfmOrderDetails.Form.txt_Order_No.Value)
rst.FindFirst "Order_No = " & strSearchName

CurrentDb.Execute "DELETE Order_ID FROM tblOrderDetails WHERE Order_ID = " &
Order_ID,dbFailOnError
CurrentDb.Execute "DELETE Order_ID FROM tblOrders WHERE Order_ID = " &
Order_ID, dbFailOnError

Me.Requery
Me.sfmOrderDetails.Form.Requery

Me.Bookmark = rst.Bookmark
Set rst = Nothing

Thanks!

View Replies


ADVERTISEMENT

Updating Main Form When Deleting Record From Subform Datasheet

Dec 21, 2006

Hi, I have a problem I don't know how to solve it. I have a subform in datasheet view, linked to the main form. There is a running total on the main form based on a dsum on a field of the subform.
When I delete a row on the subform datasheet, the main form calculation is not automatically updated. I had to create a "update" button to do the refresh.
Is there a "on record delete" event available for a datasheet sub form?
Thank you for any help.

View 1 Replies View Related

Deleting All Related Sub Records When Deleting The "main" Record.

Apr 12, 2008

I have two tables.

1. Persons (list of persons)
2. Job history (list of jobs)

each person have their own job history. all these jobs are stored in the job history table. when i delete a person i would like the job history for this person deleted as well. each job stored in the job table have a field with person name, so that it is linked to this person.

how can i do this? vba or simple properties options?

- Roy

View 1 Replies View Related

Forms :: Main Form Jumps Back To 1st Record When Moving To Next Record In Subform?

Mar 24, 2015

When I add a record in the subform and then move to the next record whilst still in the subform, the main form jumps back to its first record? I then have to move back to the right record in the main form to update the next record in the subform.

I want to move to the next record in the subform without affecting the main form.

View 1 Replies View Related

Open Record In Main Form By Clicking Record ID In Subform

Dec 16, 2014

I am creating an events database. In my main form, I have all the details of a single event (billing code, fees, customer, support personnel, etc.). Then, I have a subform (form view) that lists each of the other services which share an event ID (this ID comes from a separate, unrelated events management suite). In the subform, I display the most-commonly-edited fields so that they are editable without navigating to a different record.

However, there will be instances where it becomes necessary to navigate to one of the records listed in the subform. I would like to make this a quick process by just clicking on the primary key in the subform, and have the corresponding record appear in the main form.

In my searches I've found a common suggestion to use a DoCmd.OpenForm method where the form opens to that specific record -- in effect filtering by the primary key number. I don't prefer this method because it disables the ability to navigate to other records using the previous, next, find records, (etc.) buttons without closing and re-opening the form.

Is there a way to make the main form 'jump' to a specific record by clicking on a control in the subform, without limiting the ability to navigate in the main form?

View 10 Replies View Related

Forms :: Main Form To Subform Cursor Position

Oct 31, 2013

On main form cursor working is goods as per default. But after come to in Subform its shown the cursor position in the last filed. So every new record is shown as the same last field. How to do this cursor position will be change in subform as per default?? and every new record its need to show as per default TabIndex "0".

I have checked my Tab Index & Order Settings also it is good. But its shown as last filed in subform

I have attached the Screen shot.

View 7 Replies View Related

Refresh Subform After Deleting Record

Jan 10, 2006

Ok heres the situation, I have one form(frmBikes) that i use to filter the results in a subform(frmSubBikes). From the main form i have a button which opens another form(frmSell) which allows me to enter the sold price and when "cmdSell" is pressed many delete and append queries are run on the data stored in (frmSubBikes). When this button is pressd the deleted record has #deleted in each field where as it should be gone and i get this error message.
(The expression you entered refers to an object that is closed or does not exist)
What do i need to change? my minds been off this project for ages now ive got a brain block so help would be appreciated.

this is the code for "cmdSell"

If MsgBox("You are about to complte selling transaction: " & r & ". " & Chr(13) & " Is that correct ? ", vbQuestion + vbYesNo, " User Accounts") = vbYes Then

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70


DoCmd.SetWarnings False
DoCmd.OpenQuery "appSoldBikes", acNormal, acEdit
DoCmd.OpenQuery "UpdSoldBikes", acNormal, acEdit
DoCmd.OpenQuery "DelHires", acNormal, acEdit
DoCmd.OpenQuery "DelRepairs", acNormal, acEdit
DoCmd.OpenQuery "DelSoldBikes", acNormal, acEdit
DoCmd.Close
Me![frmSubBikes].Form.Filter = Searchstr
Me![frmSubBikes].Form.FilterOn = True
End If
Exit_cmdSave_Click:
Exit Sub

Hope you can help,
Thanks Sci

View 4 Replies View Related

Forms :: Requery Subform After Deleting A Record

Nov 8, 2014

I'm trying to execute some lines of code right after deleting a record in a subform, with a right click on the row I want to delete (the idea is that if a record is deleted the other ones should be updated by module1.tblUpdateLatestDocuments).

I've tried putting this code into the events On Delete and After Del Confirm of the subform, none of that worked.

Code:
Option Compare Database
Private Sub Form_AfterDelConfirm(Status As Integer)
Call Module1.tblUpdateLatestDocuments
Me.Requery
End Sub

Private Sub Form_Delete(Cancel As Integer)
Call Module1.tblUpdateLatestDocuments
Me.Requery
End Sub

Aren't there any events that I could use right AFTER the record has been deleted?

View 10 Replies View Related

General :: Form With Subform Datasheet - Record Deleting

Aug 17, 2015

Currently i have a form with subform datasheet

Form from Purchase order table
Subform from serial table(Brand,Model,Type,Location,Serial) with serialtrans table(InOut,AQty,Comment)
(SELECT tbl_Serial.*, tbl_SerialTrans.*
FROM tbl_Serial LEFT JOIN tbl_SerialTrans ON tbl_Serial.SerialID = tbl_SerialTrans.SerialID

when i click on the datasheet row and delete the row it only delete the record from tbl_SerialTrans but record on tbl_Serial was not deleted

is there any way to delete it?

View 3 Replies View Related

Forms :: How To Make Form Refresh After Deleting A Record In Subform

Sep 3, 2014

I have been trying to figure out how to make a form refresh after deleting a record in a subform. The code in the combo box that populates the subform works fine. If you select the "Action" of "sign" from the unbound combo box above the subform, it places "Sign" in the the subform and then makes a checkbox on the main form [WillTake] = True.

But when I delete the record by right clicking on the entry, the focus is sent back to the main form so I can refresh the form and thus update the [WillTake] checkbox. I have tried just about everything. I can get the focus to a field on the form, but I cannot figure out how to refresh or requery the form.

The code to transfer focus is on the OnDelete of the subform.

I have included a watered down version of the DB.

View 11 Replies View Related

Creating New Record In Main Form From Subform

Aug 4, 2005

Hi,

I want a user to be able to create a new record in the main form, after they have finished filling in the boxes in a subform by pressing the Enter key, rather than having to click the 'new record' button on the main form.

I reckon I need a 'default button' on the subform but I have no idea what this should do when clicked to create the new record...

View 1 Replies View Related

Finding Record On Main Form From Subform

Sep 12, 2006

Hello,

I have tried everywhere for a solution to this:mad: Any help is much appreciated.

I have a main form with a subform. The subform shows related records using a match criteria. Each record that shows in the subform also has a unique ID. This unique ID is also shown on the main form record (although not used to link the main and sub forms).

I would like to be able to click on a command button on each of the records in the subform and goto the Main form record. I can't find any threads that deal with this though - sorry!

Hope it makes sense

DJ

View 1 Replies View Related

Subform Record To Populate Main Form

Aug 26, 2004

I have a subform that is populated with records, filtered by two combo boxes on the main form. I would like to be able to double-click on one of the records in the subform and have it populated in the main form for editing.

Any help would be greatly appreciated.


Best regards,
MrAviator

View 5 Replies View Related

Clear All The Records In The Subform Of The Record In The Main Form

Jul 19, 2005

Hello,

I am trying to create a command button that clears all the records in the Sub form in the CURRENT RECORD OF THE MAIN FORM.
I know this sounds a little bit confusing, but I hope you understand!

The main form is: frmOrder2
The subform is: subfrmItemOrder

The fields in the subfrm that has to be cleared are:

Items (combobox)
UnitID (combobox)
K1 (txtbox)
K2 (txtbox)
K3 (txtbox)
CSB (txtbox)
DTSS (txtbox)
H (txtbox)
Remarks (txtbox)


The two forms are linked by the field OrderID.

Please help me!!
Thanks!
jenny

View 14 Replies View Related

Forms :: Main Form And Subform Search For Same Record

Nov 20, 2014

I am currently working on a project in access 2010 and I am having a search for record dilemma. I have a main form that has a subform in it under a tab control. I place a search box at the top of the form so that user could select either the ID, the internal case number, and/or the reviewer name in the text boxes to search the record.

I have tried the using the search for record macros and the convert the macros to VBA and possibly write so extra coding for calling the subform into the search but nothing seems to work in getting the search in sync between the main form an the subform. VBA code that will look for same record in both forms.

The reason for the tab control is because depending on the data entered the users switch between mid section data about case and so one set of info goes into one tbl and the other goes into another, but the subform that I am referring to in this question is being generated from the same tbl as the main form.

View 2 Replies View Related

Forms :: Subform Needs Results For More Than 1 Main Form Record?

Dec 24, 2013

I have two unbound subforms on an unbound "display form". I managed to link the two subforms by following this method: [URL] This is great and works well enough, however, by design, subform2 will only display records relevant to the very first record on subform1. I need subform2 to be filtered based on ALL of the relevant records being displayed in subform1 not just the first one.

Previously, subform2 was based on a query using subform1's records as criteria, but this gave me the same result.

View 14 Replies View Related

Copy Field Data From Main Form To A Subform On Add New Record

Feb 25, 2006

I have a main form that has some sub forms on it linked by master child on PK. When I select a new record on the sub forms I want to take a date value from the main form and populate the value into a field in the sub form. How do I do this as I cannot see a new record event on the sub form?

help would be much appreciated.
regards

Peter

View 7 Replies View Related

Find Record In Main Form Based On Selection In Subform

Nov 8, 2004

Can someone tell me how I migh find a record in a main form based a a selection in my subform?

I have a Main form called frm_ProductionSchedule.

It contains three subforms:
frm_ProductionSchedule_subform
frm_ProductionSchedule2_subform
frm_ProductionSchedule3_subform


I would like to be able to select a record within any of the subforms and have the main form display it. The main form is linked to a SQL server Table. The subform are based on queries. The unique PK field is a date field called record_date.

I should add that the Link Child/Master fields are blank for all Subforms. Each Subform is linked to a query and displays data for a particular machine (1,2 or 3) AND is updated based on a combo selection of the Production week. The queries for one subform would look something like this:

SELECT dbo_tbl_ProdSchedule.PartType, dbo_tbl_ProdSchedule.PartNumber, dbo_tbl_ProdSchedule.Work_order_no, dbo_tbl_ProdSchedule.FrameType, dbo_tbl_ProdSchedule.FrameNumber, dbo_tbl_ProdSchedule.Shift, dbo_tbl_ProdSchedule.Sch1_date, dbo_tbl_ProdSchedule.Sch1_spins, dbo_tbl_ProdSchedule.Sch1_sheets, dbo_tbl_ProdSchedule.Sch2_spins, dbo_tbl_ProdSchedule.Sch2_sheets, dbo_tbl_ProdSchedule.Sch3_spins, dbo_tbl_ProdSchedule.Sch3_sheets, dbo_tbl_ProdSchedule.Sch4_spins, dbo_tbl_ProdSchedule.Sch4_sheets, dbo_tbl_ProdSchedule.Sch5_spins, dbo_tbl_ProdSchedule.Sch5_sheets, dbo_tbl_ProdSchedule.Sch6_spins, dbo_tbl_ProdSchedule.Sch6_sheets, dbo_tbl_ProdSchedule.Sch7_spins, dbo_tbl_ProdSchedule.Sch7_sheets, dbo_tbl_ProdSchedule.Frames_due_date, dbo_tbl_ProdSchedule.Comments, dbo_tbl_ProdSchedule.Record_date
FROM dbo_tbl_ProdSchedule
WHERE (((dbo_tbl_ProdSchedule.Sch1_date)=[Forms]![frm_ProductionSchedule]![cmbSchDisDate]) AND ((dbo_tbl_ProdSchedule.SpinCoaterNo)=1));

AND WHERE ((dbo_tbl_ProdSchedule.SpinCoaterNo)=2)), etc. Respectively for each subform.

Thanks in Advance!!

John

View 4 Replies View Related

Forms :: Creating Record From Subform - Use One Of Values In Main Form

Apr 5, 2013

I'm trying to create a record for a table that has 2 attributes:

ContainsTracks
+album
+tracks

I have my main form, where the album is determined by a TextBox. My subform has a textbox that lists the tracks (in datasheet view). I have this set up and working fine.At the moment if I try to add a new record by typing a new entry into the subform I get the error: "Index or primary key can not contain a null value". This is because both album and tracks make the composite primary key of ContainsTracks.

My question is, how can I tell the subform to grab the value from TextBox1 in the main form as the +album when I create a new record.I have provided two screenshot that perhaps explain my predicament a bit better

View 2 Replies View Related

Forms :: Using Subform To Add Progress Reports To Main Form Record?

Jan 14, 2015

Basically, I've created a form to track maintenance requests for specific types of equipment. Basically it's like a ticketing system.

Once a user creates the request, I want to use a subform (I guess this is the best way) to let them add data to that specific main form record to track the progress of the request until the repair is made and the "ticket" is closed out.

Do I need to create another table for the "progress reports" (for lack of a better term) or should this be done using some sort of an append query?

I've seen plenty of sample databases where they have a box within a form that looks like a datasheet where these types of "update" notes can be added and then submitted with the click of a button, but I've never been able to get at the code behind it.

View 14 Replies View Related

Forms :: Creating New Record In Subform - Button To Add New Object To Main Job

Jul 1, 2013

What i'm trying to do is add a new record to a subform. The problem is, I couldn't use a subform based on a table in order to achieve this. I needed extended information for it to be useful, so I made the visible part of the subform based on a query instead.I'd like to add a button that adds a new object to the main job. I'd like the adding of that button to call a new form allows you to fill in the information for that object (this called form actually contains 5 subforms to populate all the data of the object). That form works.

What I need is for the button to call the 2nd detailed form AND create the necessary entries in the project/object junction table.The idea as it is now is a button on the main form, but if it was possible to do so via continuous form in a subform, that's doable.

View 11 Replies View Related

Forms :: Datasheet Subform Not Updating From Main Form Add Record?

Jun 23, 2015

I have a navigation tab with 6 sections, of the 6, 5 of them are self-made split view with a form view on top and datasheet on bottom and upon adding records through a command button on form view I would like the datasheets to update.

On other forms it would (I believe) update to the bottom of the datasheet, now on the ones I've been banging my head against it either replaces the top record or doesn't show up unless I switch tabs and switch back. From my searches I assume this is in need of a requery...

View 14 Replies View Related

General :: Get A Record In Main Form With Double Click In Subform

Sep 15, 2013

I have a mainform named EMA and a subform named Q_EMO. In the mainform there is one event related to a patient.In the subform Q_EMO there are all the events releted to the patient.I would like to get a record in the mainform EMA when I double click in the record of the subform.

View 12 Replies View Related

Modules & VBA :: Event Information - Deleting Record Creates A Blank Record

Sep 8, 2013

I have a form set to the table Client Information with a subform set to the table Event Information. Client Information has a one to many relationship to Event Information.

There is a button that deletes the current record in Client Information--also deleting the related records in Event Information--then closes the form. The code works fine but a blank record in Event Information is apparently being created before the form closes.

Here's the VBA that I'm using:

Private Sub CmdDelReturn_Click()
Dim CmdDelReturnMsg As String
CmdDelReturnMsg = MsgBox("Delete event & client then return to front?", vbYesNo + vbDefaultButton1, "Delete and Return?")

[Code] ....

It's not a big deal because the button won't be used often and I can manually go into the table to delete the blank record. But if there's a simple solution to prevent this that would be nice.

View 3 Replies View Related

Forms :: Deleting Subform Data When Main Form Is Deleted

Dec 9, 2014

I have a main form with several linked by ID worksheet sub-forms. I would like to add a on-click function to delete the main form and all related record on each sub-form. I have tried several methods but nothing is working correctly. The cascading delete function is not available as part of the form relationships. How I can accomplish this?

View 1 Replies View Related

Forms :: Main Form Show Record Selected Within Datasheet Subform

Sep 30, 2013

The situation is i have two forms, looking at the same table, but the main table shows more records, I would like to select a record in the datasheet subform and have the main form flick through to the corresponding record.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved