Forms :: Locking Main Form Controls Causes Data In Subform To Disappear?
Dec 12, 2014
So I am creating a Complaint Tracking Database with Access 2013. In my 'complaint entry form' I have added to ability to "Open" and "Close" the complaint. While the complaint is open the user is able to enter/edit data in the complaint (new or old). When the complaint is "Closed" (done so with a button on the form that prompts the user to enter a date of their choice) the user should not be able to enter/edit data in the given complaint. In my original thinking, I placed checks on various events (on load, after update, on click) that checked to see if the current complaint had a date in the "Close date" field.
If the close date field was null then that meant that the complaint was open and the the controls would NOT be locked. If the close date field was NOT null (i.e. a date was present) then the complaint was closed and the main form fields were locked but the data was still viewable and unless they "re-opened" the complaint they could not change the data.
Now, in the complaint entry form, I have a subform that acts as a action tracker that is used by the user to keep track of the actions taken on the complaint (i.e. they requested documentation from such and such on this date..etc). My problem through all of this is upon closing the complaint and locking the main form controls, my subforms data just vanishes! At first I figured the problem might be with the linking of master and child fields, so I made sure that I left the ID in the main form unlocked when I loop through my controls during the locking process. This did not solve my problem. Code below:
Private Sub Form_Load()
Dim ctl As Control
If Not IsNull(Me.close_date) Then
Me.FormHeader.Visible = True
Me.btnClosed.Enabled = False
Me.btnOpen.Enabled = True
For Each ctl In Me.Controls
Well this is going to take a bit of explaining, because my colleagues and I are totally baffled.
I have an Access 2000 front end connected to a Sql production server. I went to make some changes to my forms and subforms, but I did not want to do it with the live data. So I made a copy of the database to my own Sql server on my desktop. So far so good . . . Now when I opened my main form, my subform appeared, but its controls were invisible--I still had not touched the code.
I looked at all the properties for my form and subforms and it is set to Visible and Display always. Now, even stranger, when I advanced my records, if the subform had data its controls would become visible, and then disappear again when I reached a record that had no data for the subform (the main form and the subform are bound to two seperate tables within my database, all the subform controls are bound as are the majority of the form's controls).
Then I went to the code. Nowhere do I change the Visible related properties within the form/subform code. I decided to put a Stop in the Form_Current code of the subform and see what happens as I advance from record to record. What happened was that when I reached a record with subform data present the stop would be read and I would toggle through the Form_Current code. When I would reach a record with no subform data, the Form_Current code would not be read as evidenced by there being no Stop action.
Anyone have an idea of what's going on? Something gets changed when I connect to my local SQL server. Even when I reconnect to the production server the problem remains. Its like some property changes, although I cannot find it, and it is changed permanently. The only property I play with by code is that I enable the controls of the subform when there is NO data in the subform, and I disable the controls when there is data. This code follows:
Private Sub Form_Current() On Error GoTo Err_Form_Current
If Me!AssignedNumber = True Then Me!AssignedNumber.Enabled = False Me!txtWTNumber.Locked = True Else Me!AssignedNumber.Enabled = True Me!txtWTNumber.Locked = False End If
Finally I tried to set Visible to true within my code for the individual controls, but that still did not overide the problem. I connect to my server via an ODBC connection to a .dsn.
I've got some forms that I've set to lock controls based on an added tag. I have a loop that goes through the entire form and disables (controlname.enabled = false) or locks (controlname.locked =true) all the controls with that tag.
This appears to work fine when the form is on a blank record but when an existing record is opened, the controls that have data in them are no longer locked or disabled (empty controls are still locked). Is there an explanation for this behavior? I don't want to disable the entire form because there are controls that I want the user to be able to always access. I started by just disabling them, then when I noticed the problem I tried locking them instead but it happens with both. Is there another property I can try that will really lock them if they have data in them or not?
What's the best way to lock down form controls after the user has entered data into them? I will have a button that reads "Save Record" that will make it so the records cannot be accidentally (or intentionally) changed after the button is clicked.
tblEmployeeAbsences for employee absences tblVacation for employee vacation info
While entering the employees hours I want an adjacent subform to show the matching employees absence and vacation days taken if any.My approach was to use a Cartesian qry for the record source for the subform and link it with a fake key from the main form.
I have a main form titled "projects" with a subform titled "inventory". They are linked by two fields "project" and "fiscal year" with a one to many relationship. These fields are also the primary keys in the main form. I also have the referential integrity option activated.
Whenever data is entered in the subform inventory, it duplicates the main form and all the other subforms linked to the main form. (I have about 11 other subforms linked to the main form). So if I enter 2 records in the inventory tab, there will be 2 records for the same project and fiscal year in the subform and all the other linked subforms with the same data, so it is almost like it is seeing the inventory subform as the main form. I have tried deleting the duplicate record from the main form, then it deletes the 2nd entry of the subform. I have attached a pdf with a print screen of the 2 duplicate records with the same fiscal year and project name.
I have another form called Notes that has a similar relationship, but doesn't have this problem. I think it might have something to do with the conversion. I originally created the database with Access 97 and we are now using Access 2007. Am I missing something that needs to be done in Access 2007 to link the 2 forms, so I can enter records in the subform without duplications?
Also, the database is split so there is a back end and front end. I have all the tables and relationships set in the back end.
I have form where I will show the details of one table(member details table). In same form I have subform where I can get the member family details (here I am using the relationship table), now my problem is when Member detail form open based on the member id, the below subform data should be fetched...
I have a main form with several linked by ID worksheet sub-forms. I would like to add a on-click function to delete the main form and all related record on each sub-form. I have tried several methods but nothing is working correctly. The cascading delete function is not available as part of the form relationships. How I can accomplish this?
I have a form that request the User to enter a Parcel ID number (99-9a-99-99aaa-aaa-aaaa). The sub form asks for the subdivision number, block number, and lot number. The subdivision number, block number and lot number are normally part of the Parcel ID number but sometimes they can be different. I want the sub form to fill in the Subdivision number, block number, and lot number as a default but allow the user to change the value if needed. currently the sub form will only show what is entered into the table itself if a change is made on the main form the sub form does not reflect the change.
I have a pretty simple form that includes subform. Subform's table is linked to main form's table with parent/child relation. Connecting fields are main table's ID field and corresponding field in child table. Subform is in datasheet view. This is pretty basic stuff so there should not be any problems, but every time I apply a quick filter in main form it causes data in subform become invisible. There is single row in subform, but all it's fields are empty.
productId ( which is filtered from combo boxes on main form) . productId contains two columns 1 bound column(id) ( hidden with width 0 ) 2 column (desription) ( shown with width 2 )
Now after entering the data when i come back to the form again . It hides the data from the productId field on subform , because data in combo box (on main form) doesn't match. Although the row is showing up , only the text in field (productId) is hidden.
Now when i change data in combo box on main form to match the ProductId , then the productId field text shows again. after changing combo box it hides . What i need is the existing data should not hide when i change the combo box selection.
see pic 1 where product matches and pic 2 where not. please note i am using master child relationship on the form/subform.
I have a report that tracks scores for our employees. From the report, you can click a button to add a new score in a form or edit an existing score(frmscoretracker). On this form there are two subforms, in a tabbed control to track additional information about the score; what areas were marked down(Trends), and was it a failing score(AutoFail).
When this form opens I have it programmed to only show the subform if there is data in it. The goal being, if I am adding a new score and there is no existing trends or Autofails for this new record, neither subforms will show - I will add an after update even to show either trends or autofail depending on the score recorded. Also, if someone chooses to edit the score, whatever subform with data, will show as well.
When someone clicks to add a new score, opening this main form to a new record, both of the tabs show. However, if the form opens to an existing record, the appropriate tab shows. Here is the code
Code:
Private Sub Form_Open(Cancel As Integer) 'If the subform has a record, the tab is visible, if not, the tab is not visible If Me.frmtrends.Form.Recordset.RecordCount > 0 Or IsNull(Me.Trends) Then Me.Trends.Visible = True Me.TabCtl33.Visible = True Else Me.Trends.Visible = False End If
[code]...
Both tabs are set as not visible in the default settings. Is there something in this code that is triggering then to be visible when there is no record in the main form?
I want to set the On Click Event of a button to unlock the current control a user is on. I know the code to set the property of a named control, but I don't want a button for every control I want to unlock. Is there a 'Me' option for controls?
How can I get the value from a field in one table (in the sub form) to copy/insert into a field in another table (in the main form) when adding a new record?The main form and sub form are linked using parent/child linking, and the sub form is in a tab.I have table A (Visit Dates) in the main form which is used to record the date of a visit to a church. Table B (Quarters and Peals) is used to record an event that took place at that church during that visit. Note that not all visits in table A require a record to be created in table B - but half or more do.
In tables A and B I have a field called "QuarterOrPealID" and these are both primary keys, though the field in table B is set to 'no duplicates' and in table A it's set to 'duplicates allowed', as table A has its own auto number/pk. They are both linked in the relationships.
So, when I add a new record to table A using the main form, I might then need to click on the tab in the sub form to create a new record in table B, which has to be linked to the same record in table A. When the "QuarterOrPealID" auto number/pk is generated in the sub form (table B), I need that value to update to the "QuarterOrPealID" field of the main form (table A), so that when I'm viewing these records the form pulls all the information nicely together.
Hi, Mainform and Subform are linked on field "barcode".
Barcode is the primary key for the table that Mainform is based on.
When user is entering a new record, I have the BeforeUpdate event of Mainform.barcode set to check to see if the barcode already exists in the table, and if so, to give the user some meaningful error message.
Works fine, except that the subform still updates to match the invalid, previously-used barcode. I want the subform to stay blank until the user has entered valid data in MainForm.
I have a main form that has some sub forms on it linked by master child on PK. When I select a new record on the sub forms I want to take a date value from the main form and populate the value into a field in the sub form. How do I do this as I cannot see a new record event on the sub form?
Hello, I'm trying to use information based on items selected in a combobox in the main form. Once the user selects the desired info in the comboboxes on the main form, I want the subform to display the data that matches the search criteria in the main form. For instance, if I select a job number, I want everything that matches that job number to be displayed the subform (along with the corresponding job name, version, etc.). I also want to get more specific in a search where I could select a job number, job name, and job version from the comboboxes in the main form to where all the data that matches the search criteria in the main form will be displayed in the subform.
Attached is what I have so far. The form I'm trying to perform this in is the SearchByResults form and the subform is the qryk95 form. I'm trying to populate this using the K95-Template table. I performed a query (qryk95) to select only the fields I want to display in my subform. Any help would be much appreciated.
I'm trying to create a report that contains data from a "main" form and a subform. However, I can't get the main form data to populate no matter what I try. I've been through all sorts of queries and just can't get it to work. The main form and subform are both separate tables, and there are no redundant fields.
Basically, the main form is an inventory of assets, and the subform is designed as a way to submit trouble tickets when one of those assets requires maintenance or repair.
I have an unbound mainform and an unbound subform(datasheet). The source of the subform is a query which is dynamic(I have many queries with diffrent columns). the subform source is change using a combo box selection. I have text boxes in my mainform..how do I get the data from subform to the text boxes of the mainform?
I have a mainform set to data entry to add records to TABLE1. I inserted a subform that shows fields from TABLE2. I have a field in TABLE2, let's say TBL2FLD1(NUMBER). I want to add TBL1FLD2(NUMBER) from TABLE1 in the mainform to TBL2FLD1 and update said field in TABLE2.
My problem is:
(1) I get a #TYPE! error when I try to add the two fields, which I have tried many, many ways (adding them together in the Default Value; creating an unbound field to hold the calculation and then putting that field into the Default Value of TBL2FLD1; ETC), and (2) I don't know how to get the subform to update TABLE2. It seems like the control gets stuck in the subform. Is there a way to accomplish this without coding it?
i have a main form for customers and a subform for sites which is set to locked so its displayed as just a list. I have a button on my main form to add a new site for the current customer displayed..How can i get the Main form '1CustomerDetails' with subform '2SiteList' to automatically refresh one i've clicked the save button on another form.
I have this form that contains subforms to hold different types of infomation for different clients. At the top of the form it has the case #, first name, last name, and then a tab element in which each of 6 pages (tabs) holds subforms for other information associated with the client.
I have a table that holds only client number and name.Then each of the six subforms operate off of a table, and all the tables are connected through relationships using client number, first name, and last name.
How could I make the subforms autofill their cooresponding tables using the case #, and name fields from the main form without having to repeatedly input the client #, names for each tab/subform?
I have a form that shows information on a person (think first name and last name) at the top and contains tab controls that hold subforms each containing a different set of info about that particular person. Ex: for person 1 the main form will display the persons first and last name as well as show the subject tabs. as you click on each tab you should see the information that pertains to that person. I would like to step through each ppeson one at a time using the horizontal record arrows (eventually I will change this process to using forward and back buttons) on the main form with the persons names changing as well as the information in the subforms changing to match the person on the main form. I can step through each separately (main form and each subform) but I cant get the subforms to change information when the main form changes persons. Seems simple enough but I cant get the subform to change when the main form changes. They are not connected. This is for an Access class that I am taking and we use Access 2013.
