Queries :: Mass Duplicate Main Records And Related Subform Records
May 29, 2014
In my simple database (attached), I need to mass duplicate Tasks and their Notes.
I have three tables: tbTasks (PK: Task_ID), tbNotes (PK: Note_ID), jtbTaskNotes (FKs: Task_ID and Note_ID). jtbTaskNotes is my many-to-many junction table that ties Tasks to Notes.
The main form (fmTasks), bound to tbTasks, has a subform (sbfm_TaskNotes) that displays notes associated with each Task. On themain form,you select which Tasks you want duplicated via a checkbox. The append query (quCopyTasks) will duplicate all tasks that have the checkbox checked. All good there. However, I can't figure out how to also duplicate each task's Notes.
I found Allen Browne's solution [URL] ....., but that only handles duplication of one record at a time, whereas I need to duplicate many records at a time (sometimes 10+ records). How do I go about duplicating multiple Tasks and their associated Notes?
Before you ask "why are you duplicating records?": There are times when tasks need to be re-accomplished and therefore need to have a new record. It's easier to duplicate records than it is to hand-jam everything again.
View Replies
ADVERTISEMENT
Jun 4, 2013
I'm trying to duplicate the records in a subform to a new form but keep getting a too few parameters error.
Code:
strSql = "INSERT INTO [OrderDetailT] ( OrderID, ProductID, Quantity, DiscountPercentage ) " & _
"SELECT " & lngID & " As NewOrderID, ProductID, Quantity, DiscountPercentage " & _
"FROM [OrderDetailT] WHERE OrderNumber = " & Me.OrderNumber & ";"
The debug.print comes out as below:
INSERT INTO [OrderDetailT] ( OrderID, ProductID, Quantity, DiscountPercentage ) SELECT 49 As NewOrderID, ProductID, Quantity, DiscountPercentage FROM [OrderDetailT] WHERE OrderT!OrderNumber = 11;
View 4 Replies
View Related
Jun 23, 2014
I have created a database in microsoft access 2010 to show invoices for different customers in different countries. In doing so, I created using a two subforms in a main form. I have used the "country name" to link the subforms to the main form. When I enter new records into the subforms for a specific country, I realized that a duplicate record of an old record are being created in the subforms. What can I do to prevent this from happening? I tried to change the query link between the main form and subform to "invoice number" but the same problem has occurred.
I am novice to microsoft access 2010 ...
View 3 Replies
View Related
Jul 11, 2013
I have a table that has the list of "Project design" choices, and I enter in there the choices that a project can be. I then have that table related to a junction table that has the "Project design choices" linked to the "project code." Anyway, I THOUGHT that one of the bonuses to using Access was that if you see something spelled wrong, you could fix it in one table and it would fix it everywhere. However, when I see that I spelled something wrong in "TBLProjectDesignChoices" and I want to fix it, it tells me that I can't because it contains related records.... ok so, if this is bad design.. I may have to leave it because I have spent countless hours doing data entry for this..
View 8 Replies
View Related
Mar 4, 2015
I am looking for a query that will return records from a table that have related records in another table. Opposite to the Unmatched Query Wizard.I have two tables: tblSupplier and tblSupplierProducts.The two tables are related by the field "SupplierId".I need the query to only return Suppliers that have Products.
View 3 Replies
View Related
Nov 8, 2014
I have a query which looks for like * surname*
in tblemployee fname lname dept active
this works fine and i can search using a requery button
however as deptartments are stored in tbldepts
when i change the query to retrieve the dept name instead of number directly from the table and i try to change this on the datasheet subform it changes it in tbldepts instead?
how can i change what dept the employee is in (as in change the number in tblemployee - but display the actual name?)
View 1 Replies
View Related
Dec 19, 2006
OK.... This is a DB I've been playing with a while... Got a few things fixed and working.... But of course more problems came up. This is a DB for tracking time spent on a project. This was all it was at first... Then I decided to add invoicing and got into trouble :) I have one main problem now and can't decide the best way to fix it.... Other then starting again and redesigning the DB.
I attached a copy... Easier to see the problem that way. From the main menu, click on "Open Client Billing"......From there, select a record on the subform and click "View Unbilled Time" From this form you will check a box to "Export" that time to create the invoice. And here is the problem. For example, if you click one out of two records to export it shows your record and creates the invoice. BUT... then if you close out and open it again to add the other record creating another invoice the previous record is ALSO in the new invoice!
So.... The records in the subform are not getting tied to the invoice number. Anyone want to make some suggestions on how the best way to correct my problem would be?
Thanks
View 1 Replies
View Related
Mar 5, 2015
I am having issues with my subform loading all records from the main table that its linked with. My main table has 8,849 records that fall Group 1 but only 8,831 records are showing up in my subform. I need to figure out how I can fix this. By a SQL code or VB code.
View 1 Replies
View Related
Jun 25, 2015
How to duplicate records in SubForm, i have created a form (transmittal) with a subform that contains all items listed:
Master Field : Transmittal No
Child Fields: IDTransmittalNumber
There is a button that allows me to duplicate form , however when i change the Transmittal no in the main form it automatically deletes all data in subform since they are both linked by that field..
View 8 Replies
View Related
Feb 23, 2005
Hello. The problem I am having is related to two tables. I have a table called Void and a table called Panel. A panel can have multiple voids and a void is assigned to only panel (one-to-many). In the Panel table, I set an autoincrementing number(SprayPanelId) for the pK. In the Void table, I set an autoincrementing number (VoidId) for the pk. I place the SprayPanelId into the Void table as a foreign key (same name in the void table). The problem I have is in my forms. I created a Panel form with a button to open a subform for data entry on the voids related to the panel in a new window. Data can be entered and is properly saved in the Void table. I open the Void table in a datasheet view under the table section and see it is properly there. I can run queries where Void.SprayPanelId = Panel.SprayPanelId and it returns the correct void with the correct Panel. The problem becomes when I open my void subform, it no longer displays that record associated with that panel. however, the record is there in my void table. Any thoughts? Do I need a filter perhaps, run any sql commands? Thanks in advance, Kevin
View 2 Replies
View Related
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
Jan 23, 2012
I have search filter form that when I click the search button is passing a value in OpenArgs to the Main Form on open event. The OpenArgs is changing the recordsource of the subform on the main form appropriately. However, the main form is referencing all of the records, not just the ones that are int eh subform recordsource. My link master and child fields are correct. There is a 1:N relationship between my mainform table and subform table.
For example, my my subform, the recordsource is diplaying 2 records where the Status = BLUE, however, the mainform (Projects) is showing ALL records even where the status isn't blue. So when I scroll through the mainform records, if the project isn't status = blue, the status subform shows a blank record. I only want the Projects where the status is blue.
View 14 Replies
View Related
Aug 29, 2013
I have a form that uses Table A as a record source and has a subform that uses Table B as a record source. There is a one to many relationship between Table A (one) and Table B (many).The user opens the form to a set record, then uses the subform to create a new record in Table B.
This works perfectly and I can do everything I want. But sometimes the subform creates a blank entry in Table B before I enter any data. Then, once I start entering data, it creates another record in which it stores the data that I am entering.The result is two records: One that only has the date of the record's creation (I set that to be automatic upon creating a new record) and the primary key that links Table B to Table A. A second one is the "true" record, the one which stores all of the data that I am entering.
It won't happen for 10 records or something and there is no apparent pattern. In case it makes a difference, the form is set to open as the subform as the focus.
View 14 Replies
View Related
Mar 9, 2015
I currently have a report which contains a subform. The master/child fields used to link them are called 'StudentID'.
In the table on which the subform is based, it is possible for multiple records to be associated with a single StudentID: in this case, the subform shows the locations where a student is studying, therefore if a student is based in three different locations the subform will display all three.
This seems to be resulting, when I run the master report, in three identical iterations of the same report being displayed (at least this is the only reason i can see why these duplicates would be displayed; it doesn't happen if a student only has one record in the subform).
Clearly, as a single instance of the report shows all three records in the subform anyway, I don't want to be seeing these duplicates.
View 5 Replies
View Related
Jun 28, 2014
I have a form with a subform in it. The Subform relates to a junction table.
For each record on the main form, there may be more than one record on the subform. The Subform only has one field on it (a combobox).
What I want to be able to do is have a text box on the main form that shows a concatenated list of all of the records on the subform.
For example, if I have 2 subform records, 'Yellow' and 'Blue', I want a textbox on the main form record to show 'Yellow, Blue'.
I have tried so many things I have found online (over about 5 hours, including a module called ConcatRelated) and absolutely nothing works!
View 5 Replies
View Related
Jun 28, 2015
I have a form with 22 subforms (in about 11 tabs). Each one of these subforms has a controlling field in the main form (table) that dictates if the associated subform needs to comply with data entry rules or not.
For example:
Field in main form cboMed (Yes; No)
Subform: frmSubMeds has a list of medications
I want to be able to loop through all the records of the subform and determine if they are complete.
This check is run from a command button; that should also consider the value in the combo box. i.e. Check only if the combo has a value of "Yes"
Since i have 22 subforms and have to run the check on everyone of them, i have used the tag property to check if the record is blank or not. This is what i have so far
Code:
For Each ctl In Me.Controls
Select Case ctl.Properties("ControlType")
Case acSubform ' only look for subforms
If ctl.Enabled Then
If Not (IsNull(ctl.Properties("SourceObject"))) Then 'if it is a subform type
[Code] ....
The problem with this is that it only looks for the selected record in the subform and doesnt look for ALL the records.
View 5 Replies
View Related
Nov 20, 2013
I am working with a sub-form where once a staff member enters there sub measure I would want to create a duplicate of that record. The problem I am having is that once you enter the sub-form and click the duplicate button it creates a duplicate of the record selected but overwrites the first record in the table. I want it to create a new SubMeasure Number which is the primary key and assigns the record the next available number.
Also if I try to add another record after one has been added I get runtime error "3021" - No current record. I would have to close the form and reopen for it to be able to add again.
I have attached the code below:
Private Sub cmdDuplicate_Click()Dim dbs As DAO.Database, Rst As DAO.Recordset
Dim F As Form
'Return Database variable pointing to current database
Set dbs = CurrentDb
Set Rst = Me.RecordsetClone
[code]....
View 4 Replies
View Related
Feb 13, 2014
In my query, I have several results that relate to the same PK overall, and I'd like to concatenate these records into one expression/one field in a form.
Currently my query looks like this;
As you can see, the BandPK/BandFK are repeated where the GenreFK/GenrePK are different. What I'd like to do is concatenate the column named Genres into one field so the BandFK/PK isn't repeated.
View 14 Replies
View Related
Sep 12, 2013
I have an existing Main form that has a sub form that the user uses to enter multiple records into a table....it works fine EXCEPT that I need to make it even easier and more intuitive and add a lot of labels. Basically the user selects items from a drop down list that adds items to a Work Order. I need to add some labels to the form to make it more descriptive for the user.
So, what I want to add multiple records using a single main form.
Is is possible to?:
1. simply turn the subform into a single main form? Can this be done by using a Command button or something similar?
2. copy all of the controls etc from the sub form into a new main form and have it all work nicely?
View 2 Replies
View Related
Jun 13, 2013
I have a code that works great from the parent form but I decided to change the format and call it from a lostfocus event in the subform instead. Now I keep getting error 3314:"You must enter a value in the tbGuests.LastName field".
The code should copy the parent form fields and create x number of duplicate records according to a field on sub-form. It then runs an append query to add the information from the subform.
Code:
Private Sub GuestsInParty_LostFocus()
Dim partymsg As Integer
Dim dbs As dao.Database, rst As dao.Recordset
Dim F As Form
Dim intHowMany As Integer
Dim intCounter As Integer
[code]....
View 1 Replies
View Related
Sep 1, 2013
In my database main form with subform. subform have query as recordsource.total of one of field in subform shown on main form. all is ok and show total correctly but when subform have no records then total field on main form shows #error. How to convert this value either into null string or zero(0).
View 2 Replies
View Related
Jun 18, 2014
I have an "Returns" master form that contains two subforms. The subforms contain items that we are returning back into our inventory. The underlying queries in each subform show only those records where the "Return Date" is null. The query(s) works fine, except that if there are 3 items that need to be returned there are 3 records that show in the master Returns form. I tried the Totals option in the query but the I need that Return Date on the subform. I only want one Returns master form to show the 3 records (not 3 records of the same master form).
View 5 Replies
View Related
May 10, 2006
I thought I had just about finished my DB but now Ive printed out and checked my reports I notice I have several records in different types of reports showing duplicate records. Iv'e gone back over the queries and there are one or two duplicates in several of my queries that I did not notice before because there are are only the odd one or two. No matter what I do I can't stop this happening. I think it must be something to do with the dates in my payment table. PaymentID is key field - foreign key is MemberID. One member can have several paymentID's a new one every year when membership renewed. The problem seems to be when the member has two payment dates in the same year. This shouldn't be anyway but the database has not been used properly with entry dates missing, written over of wrong dates etc. Ive tried to correct this but do not want to tamper with past payment date records. I already have 'select distinct' in my queries and have tried 'distinct row' which seems to return even more duplicate records. Im pulling my hair out over this there must be a way to return the records from members showing just 1 only of their very latest payment record/date. Im using this expression in the query.
LastPaid: (SELECT MAX(PaymentDate) FROM S_Payments_Table WHERE S_Payments_Table.MemberID =S_Members_Table.MemberID)
View 1 Replies
View Related
Oct 25, 2014
I have been hitting my head against the wall to have my query not show any DUPLICATE STOCK CODES in my report..I have two "AQN.PR.A" records, I only want my report to print one "AQN.PR.A"...I have three "ALA" records, I only want my report to print one "ALA"AQN.PR.A
View 14 Replies
View Related
Dec 4, 2014
How can I make certain my user enters records on a subform before attempting to save the main form? Right now they can completely ignore the subform before saving the record.The Main form has business address, etc. on it. the subform is bound to a join table that lists the multiple categories, subcategories and sector the business is listed in for a directory.
I already have my fields set to required at the table level in the join table, and have some existing VBA in both my subform (to update edited date) and my form (to validate empty records where a certain condition is met) but that's not the issue...
How do I focus the user to enter a record on the subform to the point where they are forced to enter something and complete the subform before the record is updated.
View 2 Replies
View Related
Sep 25, 2015
I have developed a database but have had difficulty with sorting data within subform of a main form. The subform displays the related tasks that correspond with main form that has been selected. Within the main form I have also created a text field that defines a particular sorting sequence of the tasks found within the subform which is titled Task Sequence. What I would like to do, is use the Task Sequence field to sort the order of tasks within subform. I have tried: IIf([ID] Is Null, 0, Val([Task Sequence])) within the Advanced filter/sort but either it shows only the first task defined in the Task Sequence or it wants to filter the main form and not the subform.
View 14 Replies
View Related
