Enter Or Update Child Record?

May 30, 2012

I have a Parent Record "Events" which has data already entered into it. I now want to assign workers to work the "event". The child table is "Assigned Officer". They have a one (event) to many(employees) relationship. Both the parent and the child have the key "Event ID". Of course Event ID is a subkey in the employee's table.

Every time I enter an employee it creates a new blank event.

My query looks like:

Code:
UPDATE Event INNER JOIN [Assigned Officer] ON Event.[Event ID] = [Assigned Officer].[Event ID] SET [Assigned Officer].[Name Assigned] = [Assigned Officer].[forms]![Assigned Officer Subform].[name assigned], [Assigned Officer].[Employee ID] = [Assigned Officer].[forms]![Assigned Officer Subform].[Employee ID];

View Replies


ADVERTISEMENT

How To Update All Queries When Enter A New Record In A Form

Jan 4, 2015

How can I update all the queries when I enter a new record in a form? When I enter a new employee record in a form, I want all the queries that are related to the employee information table to be updated with this new entry.

View 4 Replies View Related

Update Subforms - No Parent/Child

Dec 24, 2005

I have a main form with 2 subforms. Subform 1 shows the results of query 1, subform 2 shows the results of query 2.

Query 2 uses query 1 as a starting point. Query 1 is updated with an option group on the main form. In other words, when I select an option on the main form, qdef for query 1 is updated and query 2 will also be affected.

If I select an option the subforms don’t update. If I close and reopen the main form, the subforms show the correct information so the queries are being updated but the subforms are not showing the updated information.

I’ve been trying to requery the subforms using the following code in the OptionGroup_Click() event:

Forms![MainForm].[Subform1].Requery
Forms![MainForm].[Subform1].Requery


By the way, I don’t have any parent/child relationships.

Am I missing something simple.

View 12 Replies View Related

Forms :: Open A Form For Adding Child Record Related To Highlighted Record In Subform

Oct 2, 2013

Is it possible to open a form to add a child record related to the highlighted record in the subform?

View 2 Replies View Related

Tables :: Add A New Child Record When Master Record Is Created?

Dec 21, 2012

I have a master table with about 40 fields. In addition, I created eight child tables with read only datasheet views of limited fields from the master table. The child table views are based on certain critera in the master table and are being refreshed by using delete and append queries tied to the On Current property of a form. By double-clicking a specific child record, a form is opened to allow editing of the master record. Also, I am using Referential Integrity and Cascade Update Related Fields to update the child table when a change is made to the master. However, I do not know how to add a record to one or more child tables when a record is added to the master table. The new child record would have to be based on the same critera that the append queries use so it gets added to the correct child table or tables.

View 5 Replies View Related

Deleting Child Table Record?

Jul 7, 2015

I have two tables in my access database with Parent Child relationship.Then i have below query based on which i have a datasheet form.

SELECT MOC_EMERGENCY.[MOC NUMBER], MOC_EMERGENCY.PERMANENT_MOC, MOC_DATA.TITLE
FROM MOC_DATA INNER JOIN MOC_EMERGENCY
ON MOC_DATA.[MOC NUMBER] = MOC_EMERGENCY.[MOC NUMBER]

When i delete a record in this form, my parent table record (in MOC_DATA table) is automatically deleted.

View 2 Replies View Related

Delete Record Only If Child Records Don't Exist

Aug 6, 2005

Hi,

I'm trying to have a button on a form that would delete a record from a table only if there are no child records linked to it.

If there are child records for the selected record, the button would be disabled.

Is there a simple way of doing this?

TIA.

View 3 Replies View Related

How To Add New Record From A Form To All Parent And Child Tables

Jul 27, 2015

I have one parent table with 6 child tables (all child tables are linked to the parent table in the relationship through the BoardID autonumber field, and they are all one-to-one relationships). All of the tables feed into one form. I have added the "add new record" button to the form, but when I click on it, it only adds a record to the parent table. The only way I have found to solve this is to go into each individual table and add a new record. Is it possible to have a new record added to every table when I click the "add new record" button on the form?

View 7 Replies View Related

Tables :: Create A New Key For Each New Record Added In Child Table With ID

Aug 22, 2014

Table has a relationship with master table. Joined on TractID primary table - auto number, TractID child table - number. This works as it should when adding a new record.

What I am trying to do is create a new key for each new record added in the child table with an ID that looks like this: TractID.A, TractID.B, TractID.C etc. for each new record added in the child table. if so where do I look, how to accomplish it?

View 6 Replies View Related

Queries :: Parent Child Query - Sum Images In Main Record

Oct 15, 2014

I'm trying to create a capacity report for my database. Originally, I only had the one table, which summed the number of Packs and number of Images, worked out percentage capacity used and then put it in a report, showing for each week of the year. However since then, I've added a child table to this, allowing me to create subrecords. The reason we did this was so we could easily group together multiple mailings under one single master record, so to speak.

Going back to the capacity, I've managed to work it so the Pack capacity is worked out from the mailing quantity in the subrecord. However, the number of Images is only entered onto the Parent record. Now my capacity query is summing the same number of images as there are subrecords, where in reality I only want it to sum the Images in the main record. I know I could add an Images field to the child table and work it out the same way, but I'd rather not do that (because that's not how our business works).

So essentially the question is, how can I sum the Images from the parent table without repeating the sum and massively overexagerating the sum?

View 6 Replies View Related

Queries :: Query Multiple Records Between (Weeks) Where No Record In Child Table?

Jul 1, 2014

I am trying to determine the best method for how to handle this query using Access 2013. I have a clients table that contains the following:

clientID fName lName admissionDate dischargeDate
1 John Doe 05/06/2014 06/27/2014
2 Jane Doe 04/24/2014 05/15/2014
3 Steven Smith 05/15/2014 NULL/Empty
4 Chris Davis 06/12/2014 NULL/Empty

Then there is a WeeklyProgressNotes table that is there for the person that is responsible for auditing the clients charts. It does not contain the actual weeklyprogressnotes, it only contains a Yes/No field and a date field for the date the weeklyprogressnote was completed. Like below:

noteID completed dateCompleted clientID
1 yes 05/08/2014 1
2 yes 05/14/2014 1
3 yes 04/25/2014 2

I am creating a form that the auditor can open to determine what weeks she needs to check for each client to see if they have their weeklyprogressnotes completed that week. The weeks run Mon - Sun and there will be no record in the WeeklyProgressNotes table if she has not yet checked and confirmed for that week. So the form would basically look like this:

fName lName week completed date clientID(hidden)
John Doe 5/19/14-5/25/14 Checkbox Null 1
John Doe 5/26/14-6/1/14 Checkbox Null 1
John Doe 6/2/14-6/8/14 Checkbox Null 1
John Doe 6/9/14-6/15/14 Checkbox Null 1
John Doe 6/16/14-6/22/14 Checkbox Null 1
John Doe 6/23/14-6/29/14 Checkbox Null 1
Jane Doe 4/28/14-5/4/14 Checkbox Null 2
and so on.......

I have thought about creating an SQL statement to select all of the clients and then creating a function that determines their admission date within the specific week and their discharge date withing the specific week and then create a loop with another SQL statement with a BETWEEN clause for all the weeks and determine if there is an entry in the WeeklyProgressNotes table or not. If not then I would display out the above info. I'm not sure if there is an easier, less search intensive way of doing it. Maybe an SQL query that can cut done on some of the looping.

View 5 Replies View Related

Forms :: How To Auto Enter Date Of Previous Record Into New Record

Aug 26, 2013

I need to know how to auto enter the date of the previous record into a new record. I have this db for my vehicle log sheets and I submit monthly a claim for the business km from my company. I usually cant remember the date of the previous claim and have to scroll back until I find the field with a date. It will be useful if the date was copied into every new record until I edit the date when I submit the claim. What I ask is during the new month all the dates will be the same until I submit the claim. I do not know how to do this, and yes I have done a search.

View 3 Replies View Related

Tables Update After Text Box Enter

Sep 17, 2006

I have a number of text boxes which user can change values, however on change of value the bound table does not update until the form is exited.

How do i ensure the table updates regardless of exiting form or not?

View 2 Replies View Related

General :: Update Record ID To Another Record ID In Same Table And Update Related Records

Aug 22, 2013

I have a table called tblCompanies. When a company acquires another company, I need a method by which the acquired company's CompanyID (PK) can be updated to the new company's CompanyID (PK). I also need to be able to update all related CompanyIDs (FKs) to the new value in related tables.

In cases in which the new company does not have an existing record, there is no problem: the company name simply gets changed to the new company and the existing CompanyID is maintained. I then use an audit table and Track Changes function to keep track of the company name data and a union query to keep the old names in the selection lists.

The problem is when both companies already have existing records in the table.

So, let's say I have records for Company A and Company B. Company A merges with Company B and Company B is now the main record. What is the best, simplest and easiest way to update the CompanyID (PK) from A to B and change the CompanyID (FK) to the new value in all related tables?

I am envisioning a pop-up form that directs the user to select the new company and then an update query happens behind the scenes... but exactly how does the criteria for the update query get selected and how do all the related tables get updated? My vba skills are pretty basic, will I need extensive coding to do something like this?

View 6 Replies View Related

Forms :: Selecting Record In Parent Subform From Child Continuous Subform

Jan 26, 2014

I have two subforms on a main form. Both use similar queries and nearly the same set of records and PK. The first subform is for data entry and the 2nd subform is a continuous form that lists the entries in order that are made from the 1st Subform. (for entering in vacation days and appointments)

The continuous form cannot be edited, it is to be a list for viewing the information only.

I have an edit button next to each record on the continuous form. When the button is clicked, I want it to take the 1st subform to that specific record as well (same PK), so the information can be edited there.

I cannot figure out how to get the 1st subform to go to the record on the continuous form when the button next to that record is clicked on the continuous form.

I tried the DoCmdSearch for record and just keep getting object is not open errors.

View 2 Replies View Related

Enter New Record In A Filtered Form

Jan 25, 2005

Hi

I want a filtered pop up form to accept new records.

The archive posting that I found suggested that I insert

[MyForm]![LinkedField] as the default value of the linked field.

Any suggestions gratefully accepted.

Steven811

View 5 Replies View Related

[Enter] Character Creating New Record During Import

Dec 14, 2007

Hello all. I've tried searching the forums as well Google for an answer for my problem, but perhaps I'm not searching on the correct key words. Would someone please point me to a post that addresses the below issue.
My company has a customer service (CS) application where our CS agents document CS requests. For reporting purposes, they drop a text file containing open service requests that I then import in Access 2003 so I can analyze the data. I've had the vendor use È (ALT+0200) as the delimiter and double-quotes to enclose text.
This has been working well until the application vendor upgraded the app last week. Now, whenever a user hits the [Enter] key to create a new line in the comments section of the application, a new line is created during the import process into Access.
When I view the text file I can see the Enter Character because it is displayed as  (except it looks more like a rectangle standing in its short side.)
I thought if creating a macro to run on the text file to delete the enter characters, but I can't find a way to systemically find the character. So far, the only solution on my end is to manually delete the characters, but that's unrealistic because there are thousands of records.
Thank you in advance for pointing me to a helpful post or commenting here.
Stephen

View 5 Replies View Related

General :: When Enter A New Record / Combobox Is Not Being Cleared

Sep 30, 2013

I have a problem with a DB I just designed. It has a combobox that is populated from a query, and it works great. However, the problem is that when I enter a new record the combobox is not being cleared, and is "holding" the data from the previous record. How can I get it to "reset"?

View 13 Replies View Related

Forms :: Enter Multiple Classes To A Record

Jan 21, 2014

I have a new scenario today! I have searched database design to try to figure this out and have an effective database as well as adding multiple records to one table that is linked to 1 record in the other table.I have a master student list with their information that has the fields

Student ID
LastName
FirstName
Address
City
Zip
Community

I have another table that has the fields

ClassName
Date
Community
Instructor

What I would like to do is be able to create a form that can add multiple classes for each student. For example, I have Student 1. Student 1 has attended class A, B, C, and D. I would like the form to have the student's name and ID with a way to add multiple classes linked to that student.

View 9 Replies View Related

Forms :: Form Without Any Navigation But Only Record To Enter

Sep 29, 2014

how to set a subform or table in a form that has no navigation at all?

View 12 Replies View Related

Modules & VBA :: How To Enter A Record In A Table Via Subform

Sep 18, 2013

I have a main form with several tabs. Each of those tab has one subform linked to its related table. I would like to be able to enter a new item in one of the table via the subform and I used the following code:

Code:
Private Sub Form_Load()
DoCmd.GoToRecord acDataForm, Me.tblEmetteurs_AddItems_subform.Name, acNewRec
End Sub

I obtain a msg error from the debogger: "Compile error: Method or data member not found."

View 6 Replies View Related

Forms :: Cmd Button To Open Form To Enter New Record?

Oct 23, 2013

I have a main form created from a query (FrmQuerySearchResults) which has a sub-form within, (FrmPresentationsTabular) which I am using to show each presentation to our service made by any given client.

For ease of use, I've removed the navigation buttons and want to replace them with my own command buttons. For appearance, I've changed the properties of the sub-form so that 'allow additions' is set to No. This is so that the list of presentations users see does not show the bottom row as a new record.

I'm trying (using macros) to add a button in the footer of the subform that opens a new form (FrmNewPresentation) which opens to a blank record allowing the user to click button, enter info, hit close.

I'm having all sorts of trouble getting this button to do as its told! So far, I have a macro that has 2 steps, first it opens FrmNewPresentation, then it has action GoToRecord, with record set to New, object type set to Table, object name set to Presentations (the name of my table populating this sub-form).

I realise the problem may be the allow additions property being set to No, so I believe I need to add parts to this Macro that goes to that property and changes it on opening the input form - I keep getting a message saying "The object 'Presentations' isn't open.

View 11 Replies View Related

General :: Create Combobox On Form To Enter Record Not On List

Jul 22, 2012

I'm creating a simple database to document our supplier's contact information, addresses etc. However, when I create a combo box on the form and try to enter a record not on the list it gives the message "The Text You Entered Isn't an Item in the List".

View 3 Replies View Related

Forms :: Allow User To Enter In Text Into Combobox So That It Saves To That Record?

Aug 5, 2014

How do I allow a user the ability to enter in text into a combo-box so that it saves to that record?

View 6 Replies View Related

General :: Sum On Subform Datasheet - Enter Result To Table Record

Aug 20, 2012

I already make textbox name: TbBanyak on subform footer with control source =sum([Jumlah])

I success display it on mainform with texbox control source =[Jualsubform].[Form]![TbBanyak]

how to enter/record the result to table record??

View 3 Replies View Related

Double Click On A Record In Datasheet Opens A Form To Enter Data

Mar 1, 2008

I have a list of incidents in my Incidents Management Database. It comes up as a grid view when users open the database.

This is what it looks loke (paste the following into address bar of your browser. lh5.google.com.au/snehal.dabhi/R8oc22J_RfI/AAAAAAAABz0/bbIkCzg5whk/image001.png

I want particular incident to open in a form when users double click on any particular incident from the list above.
For example double clicking on the first incident (dated 1/03/2008 in the list above) should open the form below with THAT PERTICULAR incident. And double clicking on 22nd incident should open the following form with 22nd incident loaded, so users can edit that incident

please Paste the following into your browser to see the image.
lh6.google.com.au/snehal.dabhi/R8oc3GJ_RgI/AAAAAAAABz8/vGSjfr5rZ10/image003.png?imgmax=640

Any detailed help is much appreciated. – I am too new to understand macros and vba.
Thanks!

View 4 Replies View Related







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