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.
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!
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 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...
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?
using access 2000+ anyone know if there is a limit to the number of fields that are allowed in a table I have currently got up to 150 but may need to go up to 300+ Most of these are number fields
I have a query that returns a description value depending on the system code provided. One of my codes is '?M' and the ? is crapping out the whole query with Invalid Syntax. How can I get around this and still have the '?M' as criteria? Please advise.:)
Thanks for any guidance, suggestions, and/or answers.
On my form I need to limit the number of employees that can be entered using OnCall as the criteria or use ExpName or Employee ID
If this criteria is NOT met then disable On Call. In other words if they try and enter a 5th employee then the field On Call/checkbox is disabled
name of field is OnCall and actual control is named On Call
I need this to fire on the OnCurrent Event for the form which is named frm_Employees_on_Call_What_Order
In other words, no more than 4 employees are allowed to be on call at the same time. The fields for the query the form is based on are:
All are form the Employees table
Employee ID Employee autonumber primary key ExpName name of employee, includes first and last name On Call Yes/No checkbox used for if a employee is on call or not
I have a table field where Null is not allowed, how do I use a user freindly message to tell the user to add data to this field. When a user tries to leave the field Null (ie by tabbing out), they get a message, "tried to assign NUl value to vaiarble which is not variant datatype.
What I want to appear is more like "Please enter a name" or some other custom message.
Any ideas - I have tried before anfd aftyer update events and vaidation rule on the form field of Not Null, but the underlying message always appears first.
I am on Access 2000. I would like to know how could I define the number of symbols that can be input in a certain field? For example in Home Phone field I would like to be able input more than 30 characters, but database doesn’t let me input more than 16. How can I change that? Thanks a lot. Regards.
The database's core is 3 tables: a table for service trips that students can go on, students who may go on those trips, and a junction table linking the two in a many-to-many relationship. Data entry is done literally from the trip table with a +/- node allowing addition of students to the trip detail table.
About 6 months ago, the user's computer was replaced and their office suite was upgraded from 2003 to 2010. Since then, periodically they have called me to say that they are not being allowed to add students to trips. Checking and discovering that I, accessing the same database from another machine, am able to add students to trips;Compacting and Repairing the databse, which did NOT resolve the issue;Opening a brand new database and importing all the objects into it, which DID resolve the issue, temporarily.I just received an email today stating that it has just happened for the 4th time. I can repeat my above-mentioned fix which will probably resolve the issue - again - temporarily.
I keep on getting this error. The weird part is that I have exactly the same stored procedure but insted of FinancialYear it has a calendar year. So instead of of having something like '2013', it has 'FY2012 - 2013'. The calendar year SP works perfectly, but this does not.
Option Compare Database Dim objConn As ADODB.Connection 'Use this code to run the SP and extract all the records Public Sub LoadVRData() Dim rsData As ADODB.Recordset
I have had a form working for ages. It has a main form and eight subforms at the bottom of the form. The main form can be edited in all fields.
I have taken a copy, modified the form added new fields to the main source table and updated the query that feeds the form.
Now (in the copy environment) it won't allow me to edit any of the fields. All of the form control properties are the same as the working model (in the live environment) I can run the query by itself and all is OK
There is obviously some little control that I have inadvertently changed and can't see it.
