Access A Controls Value In One SubForm From Another
Jan 26, 2005
Hi,
I have two subForms on separate pages of a Tab Control on a main form. I want to create a Textbox on one Form to display the month of a Date field on another form, using DatePart.
Using the On Current event, this works ok, if I have the 'other' subForm open in its own right, but I get message telling me it can't be found if i don't. Here is what I have...
Private Sub Form_Current()
If Not IsNull(Forms![Reviews]!SelectLessee) Then
Dim revDate As Date
revDate = Forms![subReviewPEST]!DateOfNextCreditReview
Me.Text118.Value = DatePart("m", revDate)
End If
End Sub
What syntax do I need, or how then can I access the control without having the subForm open itself, by getting to the page on the tab control, then to the control on the subform...
This code checks to see if the last three characters on a ControlSource.Tag property are "Req" and if they are, tests if it's null or = 0 and if it is then it outputs a msg box stating that the control.ControlSource is required. It does it for every control on the main form.
This has worked.
Now, i need to allow for multiple subforms to be located on the main form along with their controls being tested as well. I want to start another loop for each time the conrol on the mainform is a subform.
I have that done so far by using "If TypeOf ctl Is SubForm Then..."
This is the part i need help with. I want to search all of the controls on the subform when it is a subform. I need to search all of the controls on the subform then, and i'm not quite sure of the syntax.
I've tried various somethings like SUBFORMCONTROL.Controls but that hasn't seemed to work...
If someone could help me, that would be wonderful!
Hello, my problem looks so stupid, but I'm not able to solve it.. I've a form with two subforms in it and one of them is behaving in a strange way. I open the form and type something in a text box in the form. Now I can type something else in a text box in the first subform but I'm not able to write anything in a textbox in subform n°2. It's textboxes (and also other controls like combo,...) look like they are not enabled (but they are enabled!!). This subform has also got a Button, and it works (It deletes current record).
If I close the form and reopen it and I go straight to subform n°2, now I'm able to insert text in it's controls, but I'm not able to edit the controls in the main form and in subform n°1.
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 have a subform (in form view) in a form that is used for data entry. When I open the form the subform is blank except for the navigation controls. The navigation controls are disabled though. If I switch to datasheet view I will see the column headers but there won't be any place to type data (no white boxes). This doesn't happen if my master and child fields aren't set up, but obviously that will show the wrong data and create other kinds of entry problems. Right now the tables are empty so that could be part of the problem, but I shouldn't have to manually enter a record so my form will look right.
I have a form with 3 tabs...each tab has the same subform embedded in it.
There are 3 sequential records in the database, and my desire is to have each record display on its' proper tab.
How can I set filter criteria for each subform to append the record sequence identifier to the link master/child fields to ensure that only the appropriate record displays on each tab?
Basically, I trying to use tab controls with embedded subforms to display the many side of a one to many relationship. The parent form contains the data for the parentrecord, and I want the subform, with a tab for each related record, to show the child side of the data relationship. One child record per tab, utilizing the same identical sub-form for each.
I have a main form, lets call it FrmMain. In addition, I have a subform on FrmMain that has a tab control on it (frmSubform). What I would like to do is on form load, reference the tab control that is on the subform, loop through the controls/textboxes, lock them and change their backcolor. The code I am attempting is listed below, however, I am getting a "type mismatch error" highlighting this line:
Dim ctl As ControlDim Frm As FormSet Frm = Forms!frmmain!FrmSubformFor Each ctl In Frm.Controls    If (TypeName(ctl) = "Textbox" Or (TypeName(ctl) = "combobox") Or (TypeName(ctl) = "listbox")) Then        ctl.Value = Null        ctl.BackColor = vbYellow        ctl.Locked = True    End IfNext ctlÂ
I have a library database with a form called Author and a subform from a junction table. The junction table connects Title/Genre. It's many to many because I usually have a few genres for one book.
This is my problem. I want to add more information to the subtable that is in the junction table. For every record I would like to add also Book Group (I am a member of several groups and want to remember which one I read the book with) and Purchased From.
So I am assuming I have to have 2 lookup controls to be able to add Book Group and Purchased from since neither one is on the junction table (and they both come from tables). Or do I add them to the junction table?
I have a sub form that allows users to add staff to a project team, once added it populates a table which updates the subform showing the selected employee. I am trying to enable a delete function that allows users to remove an employee from the project team in the subform showing selected staff. Here is the code I have so far, but it doesn't work;
Code: Private Sub Command4_Click() Dim dbs As Database Dim rs As Recordset Dim sqlstr As String Set dbs = CurrentDb sqlstr = "DELETE tbl_CapexStaff.* FROM tbl_CapexStaff WHERE CAP_ID = Forms!frm_Switchboard.CAP_Live" dbs.Execute (sqlstr) End Sub
I have a continuous subform with multiple controls (textboxes) per row.
I'm trying to set up conditional formatting such that certain textboxes are highlighted based on comparisons with other textboxes within the same row/record.
What's bothering me is, I have managed it for one, but struggling to do it for others!
For example - I want to highlight 'similar' records (i.e. possible duplications) - and have managed this by adding a textbox across the length of the row and applying the following CF expression :
Quote:
DCount("[ID]","[tblMyTable]","[ID] <> " & "txtID" & " And [Field1]=" & "txtField1" & " And [Field2]=" & "txtField2" & " And [Field3]=" & "txtField3" & " And [Field4]=" & "txtField4" & " And [Field5]=" & "txtField5")>0
Works brilliantly - highlights all records where fields 1 thru 5 hold common values.
Now I want to highlight where two fields within the same record are different (i.e. they are driven by different data but should hold the same value)
Am trying this :
Quote:
"txtField3"<>"txtField4"
But it's not working - it's highlighting pairs of fields even though they do hold the same value.
Even tried switching it for a DLookup (which seems like doubling up on effort, but just to see if it worked)
I have placed an unbound subform on a form. I have the following code which loads in different datasheet to the subform:
'Loads in NewQuery Me.data.SourceObject = "query.NewQuery"
'Loads in NewQuery2 Me.data.SourceObject = "query.NewQuery2"
etc.... this works fine to show these queries but i want to know how to reference the fields on the datasheet.
for example one of the fields in NewQuery is "ID"... When clicked i want to run code... normally on bound subform i could go for example ID_Click()... msgbox(me.ID)
how can i reference these other fields on unbound???
I have a subform which lists a set of records. In the footer section of the subform, I have a number of controls which display calculations based on the records present.
The number of records in the subform will change (reduce). Basically, this is a review / approval function, the idea being that each record will be checked and approved, which will then remove it from the list.
So eventually, all of the records in the subform should disappear (the subform is requeried each time the user approves a record)
I want to be able to hide the controls in the footer section once the subform has been cleared of all records.
But not sure
a) how to determine when the subform's recordset reaches zero and
b) what event to use to fire the code to hide the footer controls.
This is my basic attempt :
Code: Private Sub Form_Current()Dim ctl As Control For Each ctl In Me.FormFooter.Controls ctl.Visible = Not (Me.Recordset.EOF) Next ctl End Sub
But it doesn't work (it only seems to fire when the subform is loaded, not when it is requeried after each approval?)
I've tried Form_Query, Form_DataChange and Form_DataSetChange but no joy with those either.
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
I have a form and a subform in MS Access 2003. I have made some changes to database structure, so I decided to change the subform also. When I changed the Link child and link master fields, the controls of the subdatasheet dissapear- they show only in design view. If I clear the contents of Link child and link master fields they appear again, but the records are not binded. Is there a setting on the parent form that also has to be changed, to make the new binding?
I am looking for a book or poster or something that can show me all the access controls and what they do. My VB.NET version came with several posters. I have looked at many books but can not find one that enumerates and explains all of them. I would also like to get similar information for the Active X controls included with access. Thanks
Hi all, I have been given a database from a small business who just want some menial report editing. Their problem is the supplier of this simple access database has disappeared and in over a year of reporting the problem has not resolved it.
The database is locked interface-wise, i.er. if you load you cannot access tables, queries, reports etc. I was wanting to know how you do this. If the database is passworded I have a revealer, but actually getting to the edit area seems to be the problem at the moment.
I m trying to create a form with tab controls but it keeps on disappearing in the form view but showing up in design view. Even when I delete the tab control and drag existing fields onto a blank form it is still not showing up in form view but showing in design view.
I have several new employess on my facilites Access Database however their Monitor Displays are on differnet settings and the databse window is to large for their monitor, how do I set the database to open to their personel settings?
I'm trying to find out if its possible to add multiple pictures to 1 record.
For instance..
If I had a row containing information on a person
First Name, Last name, Address, Picture
Currently I have used the Image option in Design view in my form. But when I go to insert Object it only allows me to choose 1 picture for that particular person.
How can I change this to allow multiple images per person. I would also like to have a next and previous button to view multiple pictures for a particular person.
Hi All, I am learning heaps and constantly wrestling with access, this is a good thing. However at the moment i recently added tabs to my form to restructure the appearance and content and thus far i am happy and its working fine. Except that moving the mouse cursor accross the from causes controls and textboxes to flicker / refresh. I find this annoying and given all the effort going into the presentation of the application i have done. Is there anyway to eleviate this, is there something i have done wrong perhaps.
I wish to host various controls and inputs from other tables on the other tabs. Will this complicate things or should 1 form with its Tab controls be particular to 1 table........ or can / should a form serve as an input or display to several tables? Is this a bad database practice?
thanks for any assistance you may be able to provide.
I'm not entirely new to Access, but I don't have the formal training or experience to accomplish what I'm attempting. And, after hours of research, I haven't been able to find anything that quite works. I know it's do-able, I just can't figure it out. Any help is much appreciated. I'm using Access 97.
In a form (MasterBadgeForm), all fields require completion. However, I want to force the user to complete 2 fields before any of the others are visible.
The 1st 2 required fields are: - HostLastName: ComboBox (user's last name based on a simple query called HostMasterQuery) - Visitor_Guest: Bound option group frame with option buttons "Visitor" (option value = 1) and "Guest" (option value = 2).
Note: The logic behind using an option group frame (instead of check boxes, etc.) is to force the user to choose "Visitor" or "Guest", but not both or none.
After both fields ("HostLastName" and "Visitor_Guest") are completed, I want 2 things to happen:
First: Depending on the choice selected in the Visitor_Guest option group frame, I want respective combo boxes to either be visible/available, or not. For example, if the user selects the "Visitor" option button, I want to make visible a combo box named VBadgesCombo (based on a simple query that has pre-defined "Visitor" numbers) and "hide" the combo box named GBadgesCombo. The same concept applies if the user selects the "Guest" option button.
Second: I want all the other required fields to become visible.
I've done a ton of research, but I just can't quite figure it out. Any help is much appreciated.
I wrote a custom ActiveX Control in C#.NET using a guide (Google ".net activex control step by step", first link on CodeProject). The control is compiled as a .dll and registered in Access 2007.
When I place the ActiveX control on a Form or Report, I can call its methods from VBA and see its output just fine.
When I open a Print Preview, only the top left corner of the control is shown, the rest is a blank white box. This does not happen with built-in ActiveX Controls (e.g. Calendar control), which print as they should.
I also tried a basic ActiveX Control build using VC++ (the sample control created by the MFC wizard), and that displays and prints correctly.
I am working on a massave aplication that has been running in Access 2000, but recently several of the file sharing users have installed Access 2003 because of the limited availability of Access 2000. All the users are using the same file off the server.
The problem we are having is that when we reference a subform in the "[Forms]![FormName]![SubformName]![FeildName]" Access 2003 does not recognize it and returns an error. I have found that if I will modify it to "[Forms]![FormName]![SubformName].[Form]![FeildName]" it is recognized in both 2000 and 2003.
To try and change every instance of a subform reference will take forever and I am garuteed to overlook something. We reference subforms all over our program, missing any one of them would be a disaster. Before I went to the tedious task of looking through everything I just wanted to throw the situation out there and see if any of you had any great ideas on how to get it fixed efficiently. I would apreciate any ideas.
Obviously "SupplierOrderCreateNewSubForm" is a sub forms on "SupplierOrderCreateNew".
There is a text box on "SuppierOrderCreateNew" called "supplierOrderNumber".
There is a text box on "SuppierOrderCreateNewSubForm" called "productNumber".
I then have a second form: "ProductSuppliers". I want to return a value from "ProductSuppliers" when a button is clicked on. When the button is clicked on, I want to put the value into "productNumber". However, I dont know how to refer to the text box.
I can put the value into "supplierOrderNumber" but not into "productNumber".
