Queries / Subforms & Saving Records
Jun 12, 2005
Hi all,
I've got this form working ok but need to add some extra functionality but haven't a clue how to do it!
The uploaded database has two main forms. The first is used to enter customer data, the second runs from a query and shows all those records that have been completed in the first form (frmCustInfo - AgentLog = Yes) but not completed in the second form (frmControlsSource - AnalystLog = No)
Then more data is input into one of the subforms (frmControlChecks). Now in order for me to see the results of the queries connected to two other subforms I have to move off the record and back to it (for the record to be saved and then the queries run)
After moving back to the record, the query results on the right (Information Only) are manually added to the last subform 'Input Two', then the user would move on to the next record and do the same.
-------------------------------------------------------------------------------------------------------------
What I need it to do then is this: When the user tabs off the last field in the subform (frmControlChecks), the queries to the right (two subforms) would run, showing the results and also populating the last subform (frmAnalystInput)
The user would then click AnalystLog and move to the next record.
I hope this is possible without redoing the forms/queries because it's taken ages to get this far :o
I've also added info in this sample database and some working data if you want to take a look...thanks for any help
View Replies
ADVERTISEMENT
Aug 6, 2005
OK, I've tried everything I can think of with this query/subform/records problem. I've searched the internet for days, even bought some big fat Access reference books and read them cover-to-cover, but I still can't get my database to do what I want. This newbie would *really* appreciate some help...
I am trying to create a database which I can use to store client’s advertising expenses. I currently have three tables: tblListings (which stores basic information about each client, with the field ListingID as the primary key); Advertising Rates (which stores the different types of advertising products and their prices, with the field ProductNumber as the primary key); and tblAdvertisingExpenses (which stores each “order” of an advertising product, including the client’s ListingID as a foreign key, date, the type of advertising, and the total cost - the primary key is an auto-numbered field called OrderNumber).
I have a main form called frmListings, which displays the client’s information from the tblListings table, and includes a subform. I wanted the subform to show only the advertising expenses corresponding to the ListingID (ie. client) displayed on the main form, so I based the subform on a query which displays all records from tblAdvertisingExpenses with a ListingID that matches the ListingID displayed from tblListings. So far so good - the records displayed in the subform change correctly when the record in the main form changes.
Now the tricky part…
The subform displays a blank row for a new record, and I would like to be able to enter a new advertising expense in that blank record (date, type of ad, and cost), and have that data added as a new record in the tblAdvertisingExpenses table with a new auto-numbered OrderNumber. I don’t know whether it’s because the subform is actually a dynaset from a query, or because I’ve constructed the query incorrectly (highly likely, I’m still pretty new to queries), but when I add in the details for the new record, I get the following error:
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship (etc, etc).
I suspect that Access is attempting to write the record (along with the ListingID) back to the tblListings table, where the same ListingID already exists (uniquely) as the primary key. But I don’t know for sure, because I don’t really know what the heck I’m doing. What I actually want to do is have the new record I’ve entered in the subform added to the tblAdvertisingExpenses table, which is the record source for the qryListings query which displays records in the subform. It shouldn’t be a problem, should it? So why can’t I get it to work? Perhaps there’s a fundamental flaw in the way I’ve broken up the database into tables, or my assigning of primary keys and foreign keys. Or perhaps there’s a flaw with the way I’ve constructed my query. Or am I forced to have a completely separate form solely for entering data into the tblAdvertisingExpenses table? Or will I need to use a macro or VBA to accomplish what I want?...
Sorry if this is terribly confusing. I can provide more specific details (and screen captures) of the tables, queries, forms, and relationships (if I haven’t provided enough specific details already), and would really appreciate any help that anyone can provide. Help?!
View 3 Replies
View Related
Dec 20, 2006
I have a form that shows entries based on other parameter values they are changing when new searches are used. I created another form that shows the entries I would like to save and they are bound to the other form. I can view the data in the second form one by one based on what happened in the previous form but when I close the first form the data goes away in the second and leaves me with a #Name?. I am trying to come up with a way to save every different entry and create continuous records on what was entered. Is there a way to have the text boxes be bound but still save the record and just create a new record for the next entry? I tried the save record option in the records menu but when I close out it is gone. Thanks.
Mitch
View 1 Replies
View Related
Apr 12, 2006
Firstly, my thanks to everyone who is going to be able to help with my problem, and also my apologies if this thread isn't in the right forum.
Ok, I am trying to construct a database at the moment for a small business. The database holds a number of customer records, and all of the basic design and construction I am skilled enough to write myself.
However, I'm running into major problems whilst trying to tackle a certain task...
The Required Situation
I have a form called Contacts. This form shows all of the customer details, laid out neatly, and draws its information from a table of the same name. This aspect works fine.
Now what I've been asked to look into is the possibility of adding two buttons to this form. Each button, when clicked, will open a pop-up window where extra details, such as a log of received telephone calls, can be entered and searched if necessary. the two buttons should do the same thing where the code is concerned, but store their details separately (one button is for sales calls logged, the other for support calls).
Now it is essential that these records are tied - uniquely - to whichever record the database user was on when they clicked the button.
My Current Thinking
I'm stumped. Well, half stumped. I think that the solution lies in sub-forms, but I'm not nearly skilled enough to actually impliment them (and don't want to waste my time doing so if there's an easier way).
Currently I have two tables, contacts and calls, the former to store the customer information, the latter to store the calls that are entered on this new form.
The form contacts has a button on it which opens the form calls. Now I can go to any record I like, click the button and open the calls form. I can enter details for such, and the record gets saved to my calls table. The problem is that it is not unique. No matter which record I'm on when I open the calls form, the details are always the same.
Yes, I know the absoute easiest way is to simply have a few fields on the main contacts form to log these details, and that it will uniquely tie to that record, but unfortunately that is not a practical solution. These calls may run into the hundreds, and must be easily navigated by the database users.
If I've not been clear enough, I can attach a copy of my database for you guys to take a look at, but what I'm after in the first instance is (a) whether there is an easier way to solve my problem, and, (b) if not, whether I'm right about needing subforms.
View 14 Replies
View Related
Dec 5, 2005
I am creating an Access database that I want to be able to use to change a header table and its associated detail table. I have created a fmain-form that shows the header table fields. This form also has a subform which shows the detail records which are linked to a field in the header table. One header may have many linked detail records from the detail table. I have been able to easily enter information into the detail records that are shown in the subform.
I would like to know how I can add new detail records directly on the subform so that they will automatically pick up the correct header field from the main form. This way I know that the record I add to the sub-form with be linked to the open record of the main form.
Also, I would like to learn a way to sum all of the values of one of the fields shown in the sub-form and place it on the main-form. How can I do this so that I only sum those values that are in the sub-form that are linked to the open man-form record... not sum all of the values from all of the records in the database?
Thank you in advance for your help.
Mike
View 1 Replies
View Related
Jan 28, 2006
Hi All
I have a form for booking employee holidays and in that I have a subform that displays their current holiday entitlment, which work great until I enter in a new record on the main form to book some holidays and I'm ask for a Name from a parameter box?? which I press cancel or ok at which point the subform goes blank (which is whatI want) but when I enter the employees name from the combo list it doesn't update the subform?
my question is; how do i remove the parameter box and update the subform once I have selected the employee name??
cheers
View 2 Replies
View Related
Sep 14, 2005
I have a subform within a form and the subform has the child records for the main form. When I move from the main form to the subform by tabbing the records in the main form are saved. However, in the subform after it is completed if a certain field in a groupbox is chosed another form opens up which contains records in the many side of the relationship with the first subform. The problem is that the first subforms records aren't saved because the originall form isn't closed and I don't want it closed...How would I save those subform records when the new form opens up?
Thanks,
View 3 Replies
View Related
Aug 14, 2006
I have 4 tables with the following fields:
Table1 - CustID, customer information....
Table2 - CustID, VehicleID, vehicle information....
Table3 - VehicleID, PolicyID, policy number information
Table4 - PolicyID, premium collection information
The bold/underlined fields are my primary keys in the Relationship.
Everything works well, except for Table4!!! All the records encoded in it can not be saved.
View 6 Replies
View Related
Aug 4, 2005
I am using the below piece of code behind my Save command buttons on my various forms in my db; however, it does not always save the record. It will save if we completely exit the forms, but if we just switch records after clicking it, sometimes the save doesn't happen. If anyone has an idea of where I went wrong, I would appreciate hearing it.
Code:Private Sub cmdSupSave_Click()On Error GoTo Err_cmdSupSave_Click'CHECKS TO SEE IF YOU WANT TO SAVEstrMsg = "Are you sure you want to save changes? Edits cannot be undone." If MsgBox(strMsg, vbQuestion + vbYesNo, "Save Entry?") = vbYes Then DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 Else 'IF NO, ENTRY REMAINS THE SAME Cancel = True End If Exit_cmdSupSave_Click: Exit Sub
View 8 Replies
View Related
Oct 13, 2011
I am managing a database for an academic project that requires a large amount of data-entry. In particular, there are several times when there are hundreds of identical records that must be entered. I would like to design a form that has a quantity box, so that if the person entering the data had to enter 320 identical records, they could just enter 320 in the quantity box, and then enter the rest of the data, and press the save button and then 320 identical (except for the Primary Key) records will be saved at the same time.
View 5 Replies
View Related
Apr 3, 2006
Hi,
Can someone help me with this... I've got a main form with 6 subforms. The main form is associated with the "one-side" table. Each subform is associated with its own "many-side" table. What I would like to do is be able to click a button on the main form to make a copy of the record and all associated records in the "many-side" tables, but obviously with the new records having a different primary key. What is the best way to go about doing this?
Thanks!
View 7 Replies
View Related
Apr 28, 2015
I am working on MS Access 2010 and have come across a bit complex situation. I have a table with about 20 fields. In a main form, I have added 4 sub-forms where each sub-form is showing 5 fields of each record from the table. This is because of step by step data entry into the same record. I have made these sub-forms as "Continuous Forms" and have given them look of Data Sheet.
There are about 1000 records currently present in the table and these are showing in my sub-forms (5 fields each). Now the problem is that, when I have my Focus on one record in my first sub-form to enter some data, and then I click on second tab of sub-form, the same records should be shown in second sub-form as well. Currently it is not being happening and I have to scroll down my second sub-form to search that particular record. It will become much more difficult when number of records will increase.
How can I work it around and get my cursor on the same record on every sub-form?
View 3 Replies
View Related
May 10, 2014
I created a new form with two subforms. They each reference their own table but are linked by a field called Case_Name. I created a unbound combo box in the main form to allow filtering of the records based on Case_name and I do a requery when the record is found. I am importing data from another application into the three tables ( main with other fields, as well as other tables ..Case_Name only).
The form/subforms are then used to modify or add new data to the existing records in each table/subform. The problem I have is when I go to update/add data in the subforms, it comes back and tells me that it cannot add data due to data integrity. It thinks I am trying to add a new record with the same Case_Name. I have the forms and subforms set up as Data Entry = No so that no new records can be added. I only want to modify existing data as the records are created from the import process.
View 5 Replies
View Related
Jul 6, 2012
I have a stock db with records that go back to 2006. I want to clear some of the old records but save them incase required. I still need to leave from 2010 until present on the db. I have a front and back end on the db.
View 1 Replies
View Related
May 4, 2014
what i want to do is lock each individual record created by each user from the other 3 users after they have been saved ( I am not referring to record locking/table locking while updating/creating ). ie They can all read each others created records BUT only update/modify their own created records. They all have individual passwords to log in.
View 3 Replies
View Related
Feb 23, 2006
Hi All,
I’m hoping someone can help a very confused newbie :confused:
While I have knowledge in using MSAccess I can honestly say I am well and truly stuck. I have searched the forum, but cannot find anything to help me out … if there is anything I am sorry for re-posting. While there may be helpful responses, they mostly involve code, which I do not know.
I have a Main Form (frmPreApprovedTravel) and numerous Subforms (sfrmFlightRail, sfrmHotel, etc). I have designed my Main Form with tabs and each tab contains staff details, flight, hotel, taxi, etc information.
My problem is that when I go to the next record in my Main Form I want the Subforms (all of them) to go to the next record as well. I know this is possible but having tried to figure it out myself for the last 2 hours, but know I’m still missing something!
I would appreciate any help and thank you in advance for the help and taking the time to read my problem.
View 3 Replies
View Related
Sep 16, 2014
I have a database that I've created for a number of clerks under me to use to track their daily tasks.
When clerks are entering their tasks, they do so through a number of combo boxes on a form (Assigned by:, Division of Law:, Subcategory:, Casename etc). When all of the combo boxes have been filled, the employee can click a button in order to save the record to the back end of the database and open a new blank record.
Currently, if an employee tries to enter the wrong data in a combo box and save the record, they will be prompted to enter data and not allowed to save the record; the problem is that if they leave all fields blank they can still save the record - thus creating useless data for reporting purposes. For example, an employee could enter their name and the date of record but leave every other combo box blank and still save the record. I want to lock the form such that they cannot save the record unless all of the combo boxes on the form are populated to reduce user error.
How can I do this simply?
View 13 Replies
View Related
May 4, 2014
I have 4 users all with the same rights. What I want to do is lock each individual record created by each user from the other 3 users after they have been saved ( I am not referring to record locking/table locking while updating/creating ). i.e., They can all read each others created records BUT only update/modify their own created records. They all have individual passwords to log in.
View 4 Replies
View Related
Oct 10, 2014
I have a form with a tabbed area, each tab containing a subform. One of these subforms adds records to a table. Another subform shows the totals from that table.
But when I add records, the totals tab is not updated with the new quantities unless I close the form completely and go back in. I tried adding Me.Dirty = False to the subform that adds the records but that makes no difference.
It seems as though the 'totals' subform gets those values as the form is loaded and does not change, even when records are added and then that tab is selected.
How can I get the totals subform to show the updated totals?
View 5 Replies
View Related
Jan 26, 2015
I have a form with several subforms for entering information while surveying rooms in a building. I am trying to create a button to copy records from one of the subforms if the data is the same (for example if there are 3 types of flooring in multiple rooms) and append it into the same subform with a different space ID. I can not get it to recognize the Space ID on the current record. This is what I have in the command button code:
Private Sub AppendFloorCmd_Click()
Dim FloorTypes As String, SpaceUpdate As String
Dim CurrentSpace As TextBox
Set CurrentSpace = Me.SpaceID
FloorTypes = "INSERT INTO FlooringSurveyTable (FlooringHomoID)" & _
"SELECT FlooringSurveyTable.FlooringHomoID FROM FlooringSurveyTable " & _
"WHERE (((FlooringSurveyTable.SpaceID)=[Enter Space ID to copy]))"
[code]....
When I click the button, it appends the right records but makes me type in the current space ID - I've tried a bunch of ways of naming that control, but it will not work.
View 7 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
Aug 29, 2012
I have a main form and four subforms all of which were created from the tables without queries. The main form pulls employees personal data (never changes). The subforms pull Events attended, Lodging at the event, Family that attended the event and Employee minutia which changes per event. I'd like to have a button on the main form that changes the records in view for all of the subforms at once. This way it is easier for the users and I know that I am view all the pertainent info for event A instead of viewing event A's lodging info with event B's family info.
View 3 Replies
View Related
Sep 18, 2013
My subforms are randomly adding blank records and one subform I would like to stay blank repopulates with data, though not necessarily the most recently added record. Both of these seem to happen when I navigate to other main records in the database and then return to this page.Using Access 2013, I have a large form with 10 pages. On one of the pages, I have two subforms. This is set up to gather many-to-one data. The top subform is my data entry form with three fields (two combo and a text) and a command button. The bottom subform is a datasheet displaying the three fields.
The two combo boxes are cascading, and they work great. The text box is there to collect additional info for each selection.
The command button works to
1) save the record,
2) requery both subforms in order to display the new data on the datasheet,
3) clear the combo and text boxes, and 4) set focus back to the initial combo box. It all works!
But then when I leave that main record, the horror begins: blank records (from the "many" table) show up on the datasheet and the data entry fields do not stay blank. I suspect my problem is in the command button. I added this code to the OnClick for the command button:
Code:
Private Sub addMinistryItems_Click()
'save record
If Me.Dirty Then Me.Dirty = False
'requery both subforms
Forms![BCD MAIN 2013]!Child572.Form.Requery
Forms![BCD MAIN 2013]!Ministries1.Form.Requery
[code]....
View 1 Replies
View Related
Aug 28, 2014
Users are too lazy to check whether a patient record exists or not. Consequently, we have a number of duplicate records that I would like to merge.
My problem is finding a way of linking 2 subforms to a parent that is not based upon a query.
On the parent form, I want two Comboboxes called 'Patient_to_Keep' and 'Patient_to_Discard'.
The two subforms called Frm_DUP_Patient1 and Frm_DUP_Patient2 should then display the data of the respective combo boxes.
Once I have this working, I can write the necessary code to compare and update the data as required.
View 5 Replies
View Related
May 22, 2006
When I try and save an older query my entire access shuts down without warning. I tried "Save" and "Save As". I even tried saving to a different name. Any Ideas?
View 4 Replies
View Related
Jul 22, 2013
I've been working on a database for work for the last few weeks and this forum has been a Godsend many times over, but now I have a problem that I can't find any reference to.
Using Access 2003, I have a form that uses 3 cascading combo boxes in the header to find a unique record, and a command button that brings up the rest of the record into unbound fields in the body of the form.
It works fine for finding records, but whenever I enter/change data on it, the record doesn't save. The navigation buttons at the bottom don't work - the Next/Previous record buttons are disabled, and the First/Last Record buttons do nothing.
Navigation buttons are enabled/activated in the properties.
I tried getting around this by creating a Save Record command button - first using the wizard, then using VBA code DoCmd.RunCommand acSaveRecord as advised by [missinglinq;696351], but this still doesn't save the changes.
View 4 Replies
View Related
