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?
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?
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
I am new to access but I am familiar with SQL databases. I have two tables: one for project data and another for project leader data. These tables are linked by a third table that relates projectIDs to leaderIDs. I am currently working on making a form that will go through each project and display the people involved in each project in a subform. I pulling the data from the the leader table that matches the the projectID with a query.
I would like to be able to update the information in this subform directly (which I can currently do) and be able to add new project leaders to a project. What is the best way to add the new leader to this? If a person is already in the database how do I add them with the form without re-adding new information to the table?
I have a form with sub-forms. All the subforms work smoothly, I am able to assign students to classes, enter their late/absence data etc... But there's 1 subform that I want to include the Gender (male,female) so I have to go back to the query (see attached picture) to include the table with gender data and map it with other tables, then insert a combo box in that subform, which display the gender of the student along side with other data.
But the problem is as soon as I modify the query for that subform, all the subforms (including that one) stop working, I'm not able to enter and change anything with error like Recordset is Noteditable ( see attached screenshot).
And after I remove that table from the query and the combo box from the subform every works just fine again.
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.
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??
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
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?
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?
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.
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.
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?!
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.
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 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.
I have an unbound form on that form I want to put three sub forms one on a products table the other on a course start dates table and the link table that joins the other two together. all three are related to each other with Pk/FK links.
When I try to link them it says you cannot link items on an unbound form.
This is probably an age old question but after a search I could not find the answer. I have a table with 3 fields..... last, first, ssn I have a form with 3 text boxes. I want users to input data into those text boxes and that data inserted into the table. The code I have figured out, the problem is access tells me I cannot add to the table because the controls do not have focus. Well, I can set focus to one control at a time but then that adds three separate entrees. Am I doing this the real hard way? Im sure there is an easier way to add records to a table with multiple fields. Thanks
I have am building a program that requires a staff person to enter a daily activity report on employee activity. It's initiated by adding a new record that will allow the selection of an employees name and ID number from a drop box into respective first name, last name and ID fields. The staff person would then go on to complete the daily report by adding information in several other fields. My form is based on a relational query built from two tables; tblClient and tbldailyreport.
Hi, I am having a problem with a form in that I can no longer add new records to it! The data comes from a query formed of 2 tables, linked by a 1-many relationship. I can add records to both the underlying tables and the query with no problem, but in the form I can't. I get no error message when I try to add a new record, just the windows error bleep. Properties for the form are exactly as they were last time it worked properly. I have made changes to the database since - but not to this form specifically. Any ideas of what I might have inadvertently done? Many thanks
Hi, I am having a problem with a form in that I can no longer add new records to it! The data comes from a query formed of 2 tables, linked by a 1-many relationship. I can add records to both the underlying tables and the query with no problem, but in the form I can't. I get no error message when I try to add a new record, just the windows error bleep. Properties for the form are exactly as they were last time it worked properly. I have made changes to the database since - but not to this form specifically. Any ideas of what I might have inadvertently done? Many thanks
I got myself pretty much backed into a corner here. I am trying to add images to records. For example, in my database there will be certificates scanned and put on each specific record. I have been searching high and low and found that the OLE object tool will allow me to do this. However, the only method I've found was to right click right in the table and selecting add object. I'm trying to find a more userfriendly method. I was hoping someone can point me in the right direction.
Hope someone can help! I was wondering if is possible to add a series of records that are in serquence to a table by just entering the first and last numbers. For example a string of numbers starting at TP11000 and ending at TP11100, the prefix TP doesn't change. Currently I have to enter every one manually, so any help would be much appreciated!
