I have a form with two tabs... Its record source is Person and Account.
The main tab has a load of fields relating to the person and the other tab is meant to show the bank accounts that the person has in a subform. However if there is no bank account for the person then nothing will show in either the subform or on the person tab.
I have checked throughly to see if there is anything saying
SELECT Person.*, Account.* WHERE PersonName = etc
which would only get the account and person details if personname was there.
The form is accessed by means of a search form which opens the form like so.
DoCmd.openForm "PersonAccount", , , "[PersonName] = '" & personName.Value & "'"
So any ideas why nothing at all shows not even the Person details?
I'm trying to clean up a form a bit and have it only show certain subforms/graphs if the data exists. I already have columns in a combobox query to show an "X" for if certain data appears:
Now, I know I could build another query and have some system go through and identify these things, but the easiest thing would be to reference the "X" in the columns of the combobox. Is there an easy way to reference values in the other (non-primary) columns? Or can you think of an easy way to make these subforms only be visible when the data exists? Maybe have an on load event for the subform?
I have a main form with multpile tabs - each tab containing a different subform.
Link Master Field: ClientID (field in Master Form) Link Child Field: Client ID (field in all subforms)
There is another field that all the subforms (continuous type) have: ObligorName. All the subforms are based off of a huge table (subtable) with fields: Obligor Name, Address, Zipcode, City, DOcuments Required, Bank Account Number, etc......
Essentially, the user will fill in the 10 obligor names associated to ONE client on the first subform on the first tab + address + zip code + city.
When the user clicks on the second tab to fill out the next subform, I want all 10 obligor names to be there already, so then they can fill out Documents Required + BAnk Account Number.
If I fill out the first subform and then exit out of the form and then reopen it, the other subforms autofill.
HOWEVER, I dont want to have the user have to do this. It wastes a lot of time. What can I do about this so that the table is automatically updated right away. They are all based on the same table so I do not see why there is so many issues.
I'd like to run a search and replace on a query result to remove carriage returns, linek breaks and tabs from the data. I know that MSWord has character aliases that will allow you to do a search and replace - does Access have an equivalent for these?
I have a single form with multiple tabs. At the top of the form appears the name of an individual and below the name are multiple tabs containing information specific to that individual. Each tab has a separate underlying table, which is the data source for the information contained on that tab.
As best as I can tell, I can only use one single data source (a query at the moment) to populate all the data that appears on all the tabs. Is there a way that I can have a separate data source (namely, a table) for each tab?
I have two subforms on a tabbed page. Both subforms read/write data from the same table. The first subform is in dataset view and consists of 5-6 fields on the line containing class information. The second subform consists of two memo-type fields where users can enter comments. The fields from both subforms should be written to the same record in the table. The problem is that the information from the second subform is being written to a different table record; it is creating a new autonumber. The data is being written to two separate records instead of being combined into one.
How do I link the two subforms so that the data gets written to the same record in the table so that the class information and it's related comments stay together?
I need to take data from a form with many sub-forms. The letter has to be in word to allow for edits. Its an appointment letter, however is has a table with 1-100 lines.I need to be able to add the name, address, date of appointment and table with however many lines are needed for that client.I can get the table to work by itself or the name, address and date of appointment but not both.
I'm new to form design and do not know any VBA (but that won't stop me from messing with the code when need be).
I'm creating a form that will serve as the user-interface for viewing, adding, and deleteing data.
The product I want is a form with 3 items: a subform that displays data in a table (so users can see all the data), a button that links to a second form for adding data, and a button that allows users to delete data.
I've gotten close to accomplishing this with the exception of the delete option. The hang-up is due to the fact that the subform that displays all the data is the output of a query (can't delete data through the query). A friend who used to work with Access years back was trying to solve this issue by writing code that would identify the primary key of the record selected in the subform/query and then adding code that would use this extracted primary key to delete the record in the original table.
This seems like a slightly convoluted approach. Any suggestions how I can get the product I'm looking for?
Picking up on some previous conversations with people, I have a form (Interconnect Register) within a database that I am trying to add a "cancel" button to which would close the form without saving. I can do this now, but the problem is there is a subform (Sheetsubfrom) within the main form. I have struggled to get the subform data to clear (was trying to use the DIRTY property but it wouldn't work - believe me, spent ages on it!) but have now got the following in place which I thought would work.
Private Sub Command41_Click() Dim retval As Integer
'Check main form On Error GoTo Sub_Form: Forms![Interconnect Register].SetFocus DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
The problem has come full circle, in that when nothing has been changed ie nothing to cancel, an error occurs saying "nothing to undo". I believe this is something to do with having an error handling procedure nested within another error handling procedure, as from what I can make out from the help files Access backtracks to the level above. So when I have an error in the Sub_Form part, the whole thing crashes.
Any ideas guys and gals? I could try posting then DB on here if that would help, providing I can zip it up ok
I have created a form with two subforms. The context of this is a litigation database. The main form is for testimony. Among other things, it includes a field for a question and an answer. Any given piece of testimony may relate to one or more documents, and any document may be referenced in one or more pieces of testimony. So have two subforms, one for documents produced by the Plaintiff, and one for documents produced by the Defendant. It worked out easier that way because I originally created indices of those documents in separate Excel tables which I then imported to Access.
So the relationship for both document tables relative to the testimony table is many to many, which is established using two junction tables.
I also have a combo box in each subform where I can autofill fields based on selection of the document number.
My problem is that while the combo-box works great, once I select a document in the subform, that selection is carried over into the next entry of the master form, but also the selection is not retained in any of the master forms. So I can browse the document listing from any entry in the master form, but I can't get the document selection in the subform to stick and create a lasting relationship to a given record in the master form.
Is my problem that it is just impossible to have a subform based on a many to many relationship, or is it in the parent child relationship of the form and subform, or is it in the properties of the subform?
I have a field showing who equipment is being distributed to pulling data from my US Employee table. If I make it to only the last name from my lookup
(SELECT [US Employees].[Last Name], [US Employees].[First Name], [US Employees].[Dept] FROM [US Employees] ORDER BY [Last Name], [First Name], [Dept])
I can also view the equipment listed under the individual it is assigned to on a subform on my US Employee table. If I try to have the first and last name displayed on my Equipment table, however,
(SELECT [US Employees].[Last Name]&", "& [US Employees].[First Name], [US Employees].[Dept] FROM [US Employees] ORDER BY [Last Name], [First Name], [Dept]; )
it will not display under the individual on the US Employee table. Is there a way around this without having to combine the fields on the employee table or separate them on the equipment table?
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?
The asset managment system I have been developing is due to go live next week and I am really struggling with the data entry and search forms.
I have attached a picture of the main form (and outlined each subform) to give you an idea of my project. The attached form is actually a Main Equipment Type Detail form with a child subform AssetMain which has a number of child subforms i.e. Asset Details, Purchase Details etc.
When a user clicks to add a new asset a popup form asks them to select an equipment type i.e. PC. When they click it opens the attached form. Their selection filters the equipment type detail values in the combo i.e. Laptop, Tower, Server etc. When a value is then selected in the combo you can then add an asset for that equipment type detail. (One Equip Type > Many Equip Type Detail > Many Assets)
1st Problem) I have set the Asset Main subform to allow additions only. When the whole form opens, the Equipment Type combo (at the top) does not display a value but does actually contain the first value. How do I force my user's to select a value in the combo before it actually filters the child subform?
2nd Problem) The Asset Main Subform (filtered by selected Equip Type) should only be used for data entry. I set the data entry property of the form to True but when the value in the Equip type combo changes, it displays all the relating records in the subform. My only solution so far is to stick
Subform.DataEntry = True in the Equip Type combo's after update event. However, this doesnt look to good as it flashes up with the data and then displays a blank record. Any ideas?
3rd problem) Due to the fact that I am using a number of subforms, my tab order is such that values are entered for the main form (i.e. ID, Condition) then subforms (i.e. manufacturer) then back to the main form (i.e. allocated to). This means that: a) I am having problems validating the record beforeinsert as I have had to change the required property to "no" b) Once the user has tabbed through all the forms what is the best way to go to a new record? Alternatively if I add a new record button I need to check that the necessary fields have been filled in (on all of the forms/subforms).
and finally the 4th) I have a search form that queries the assets. The search results are displayed in a list box. When you dblclick the list box I need to show the asset details. Question: Can I use the above form to do this? My only problem is that I need to open the AssetMain form which is a sub form in the above. If I try and use the existing form it will not open as many of the controls refer to the form as a subform. Do you think I need to create a separate form for showing the data to entering it?
Would really appreciate some help (not that you havent heard that before :) ) My head is going to pop in a minute!!!
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.
Code: (THIS NEVER WORKS FOR ME - RUNTIME ERROR) Me.AllowEdits = Me.Parent.AllowEdits Me.AllowDeletions = Me.Parent.AllowDeletions Me.AllowAdditions = Me.Parent.AllowAdditions
So the main form is locked upon opening and unlocked with the click of an unlock button. How to apply this to the subforms as well. They just stayed unlocked the whole time.
I need to make my mainform fields required before any data can be entered into subforms. Mainform and Subforms are linked with LinkMaster Child ID. This should only be applied in this form.
Hi there. I need a way to pass data between forms, and subforms. I have "form1" which simply contains a text box requiring the user to input an ID which I will call "PID". it also has a button which has the following code:
Private Sub Command5_Click() On Error GoTo Err_Command5_Click
Dim stDocName As String Dim stLinkCriteria As String stLinkCriteria = "[PID]=" & Me.PID stDocName = "TestExam" DoCmd.OpenForm stDocName, , , stLinkCriteria
On the form testExam, I have a page control with 3 separate tabs. For each tab, I've embedded three separate subforms. All forms involved, including the container form (testExam) are bound to separate tables. All tables have one common field - PID I'd like the user to only have to input the PID on one form, and have it trickle down to all subforms.
In the FOrm_Current sub of the testExam form, I have the following code:
Private Sub Form_Current() [PID] = Forms!frmForm1.PID End Sub
Using this code, I am able to pass the data from Form1 to testExam, have the PID on testExam filled in and on tab 2. But tab 1 will not populate automatically. I have no idea why. I don't know where to start troubleshooting this. Please help.
Is this even a good way to pass data between subforms?
I have an application that enters candidates in, who apply for a job.
I found that I can enter a candidate in, and if I dont enter at least an address type or phone type ( giving that candidate an address record, albiet a blank one (just a type of address) and a phone record) then the candidate wont have a sub record for addres or phone at all.
This is hurting my reports. They are set to pull the primary address and phone only, as we dont need to have 2-3 contact information for a candidate on a report This is how I found out that I had blank address and phone for some test records. I need to be able to check when a person does a number of things, that at least the combos for phone type and address type have been chosen as something. This will ensure that they have a primary address and phone, as I have code that takes care of all that. I tried putting the following code in the form's before update, but since it is calling to a subform, the minute I tab TO the address combo, I get the message. The form is updating before I get the chance to skip the field.
Here is the lay out. A user can enter in candidate, their address, phone, application and activities. Address, phone, applications and activities are all sub reports on a tab control. The user should be able to move freely within any record, and only be bothered when they try to leave, if important information is still empty. I dont want to control how they enter ('you must enter this first!' lol) just that they do enter it in the end.
What I want to do is check when the user is leaving a record.(closing form, going to another record, clicking the search button that opens another form etc) if either combo is empty (no primary contact info) then it gives the message and goes back to the combo that is empty.
Here is the code I have. If anyone can help me find the right place to put it, I would really appreciate it. Thank you,
If IsNull(Me.[subAddressEntry].Form![cmbAddressTypeID]) Then msgbox "You must enter an Address" Me.[subAddressEntry].Form![cmbAddressTypeID].SetFocus Cancel = True ElseIf IsNull(Me.[subPhoneEntry].Form![cmbPhoneTypeID]) Then msgbox "You must enter a Phone" Me.[subPhoneEntry].Form![cmbPhoneTypeID].SetFocus Cancel = True Else: Cancel = False End If
If I can't figure this out, I am thinking of making a new address type and phone type. Call it "none chosen" and then make that a default selection. Then one would always be chosen...I think. I would like to avoid that though.
I have several Suppliers, each of whom holds several SalesEvents. At each SalesEvent I might buy none, one or more Bundles. Each Bundle then contains one or more Items. These relationships are all reflected in my Access 2013 desktop db as one-many relationships with cascaded updates.
My form structure for data entry reflects the Relationships, in that I have a Supplier form with a SalesEvents subform. On that subform I can enter data about each of that Supplier's SalesEvents, and each SalesEvent row has a button to call up a SalesEvent form which has a Bundles subform. Then each row of that subform has a button which calls up a Bundle form containing an Items subform. That Bundle form identifies the Supplier, Sales Event and Bundle number, and within its Items subform I can then enter the data for each Item.
Problem : All used to work fine, but I've clearly changed something because now when I select the Items button on the Items subform in the Bundle form, the system gives me the data for the first record in the SalesEvents table, rather than the one selected. I have checked the raw data and that's as it should be. I have tried to undo all the steps I took since it last worked properly, but to no avail. Restoring the last backup (taken when I'm sure it worked properly) still has the problem.how do I find what data is actually being passed between forms and subforms ?
hi iam new to this forum this is my first post , i'am trying to design a student database and i have designed it just want to know how to add different tabs to the view and how to add button/pull down menus to that certain tab when i try to add buttons the go ontop every tab and i can't figure out how to rectefy this any help is app , if you dont know what i'am on about let me know and i will email the database to you and you can see what i mean , thanks ccbup4it
Hi - Could anyone please help me. I have got a form on which I am using tabs. For some reason, when that form is opened, it is not showing the full number of records from the table. It is only opening in Add mode. If I then start to enter multiple records the navigation buttons at the bottom of the screen will show me how many records I have entered and you can use the navigation buttons to switch between them, however, if I think close the form and re-open it, it goes back to showing no records.
I have tried opening it in Add mode and in Show All mode, but neither is working.
The Cycle property is showing "all records" and the data is definately being stored in teh Source table.
Hi - Could anyone please help me. I have got a form on which I am using tabs. For some reason, when that form is opened, it is not showing the full number of records from the table. It is only opening in Add mode. If I then start to enter multiple records the navigation buttons at the bottom of the screen will show me how many records I have entered and you can use the navigation buttons to switch between them, however, if I think close the form and re-open it, it goes back to showing no records.
I have tried opening it in Add mode and in Show All mode, but neither is working.
The Cycle property is showing "all records" and the data is definately being stored in teh Source table.
Is there a way to make it so when you open a database the forms, tables, etc do not appear in the taskbar and just appear in the bottem right of access.
So basically, like already but without the taskbar tabs.
