SubSubForm ControlSource/RecordSource Problem
Nov 4, 2005
I have a form with two subforms. One of the subforms has five subforms (subsubforms).
The ControlSource and the (query sequence for each) RecordSource of each of the subsubforms are created on-the-fly. The subsubforms have no Master/Child relationships specified. The subsubform merely display various filtered and various grouped subtotals of the 1st subform. Summing and grouping on the subsubforms prevents my using Master/Child properties.
On the AfterUpdate event on any of the filter fields on the form the ControlSources and RecordSources are rebuilt and the form becomes invisible for some reason and appears to hang, even after stepping through all the (class module) code with the debugger. Repeadedly clicking both Maximize and Restore buttons on the form's button on the Status Bar followed by clicking a 2nd form's toolbar icon causes the form to become visible under the 2nd form. The tool bar are also not visible, just a large brow bar at the top of the form. Moving the mouse over the toolbars causes individual icons to become visible or raised.
What am I missing?
Maybe I should calculate the values using DAO recordsets and backfill unbound subsubforms.
Thank you in advance for your thoughts.
View Replies
ADVERTISEMENT
Mar 10, 2006
Hi... I see a lot of discussions about refreshing or requerying main forms and subforms in the forum but haven't found one about subsubforms. In my main form I have a subform with several tabs that contains subsubforms linked to the subform. After I enter a record in subsubform1, I have a VBA code written to insert some of the fields in subsubform1 to a table that subsubform2 is based on. The problem I am facing is I would have to click on the next record and come back to see the entry made to subsubform2. Please help!
View 7 Replies
View Related
Jul 5, 2005
Hello, After searching for many days I have given up and so here I am. The following code is the event associated with a button New Record on a subform.
Dim txtDepth As Integer
Me.txtDepth = Depth_To
Me.Refresh
DoCmd.GoToRecord , , acNewRec
Me!Depth_From = Me.txtDepth
Me!Depth_To = 0
Me!Depth_To.SetFocus
Exit_NewRecord_Click:
As you can see the record in Depth_To is copied to the variable txtDepth, the subform is refreshed and the value txtDepth is copied to Depth_From.
I have added a subsubform to the subform that allows users to enter information about an interval only when required, otherwise it reamins hidden. What I would like is once the user has finished entering data into the subsubform, a button New Record (on the subsubform) allows the user to go to the subform, hides the subsubform and then refreshes the subform as above. I have tried copying the code (above) to the subsubform button and using SetFocus to switch to the subform, but it appears to be not as simple as that.
In advance thank you.
View 3 Replies
View Related
Dec 13, 2007
I have a unbound text box being used as a label. In the control source of the text box I have a date ie. =#12/31/2008#. I am using this date for criteria in a dsum function, so whatever date is in this field effects the data being displayed. I need to be able to have the user edit this date when necessary. I created another unbound text box and put the following code in the after update: Me.ReestDate.ControlSource = "=#" & Me.EnterReestDate & "#"
This is working as it is displaying the correct change. My problem is that the control does not retain the date. When I exit out and go back in, the original date is still there. Is there some code that will save the field after I update it? Or maybe another suggestion to do what I need.
Thanks
Gregg
View 3 Replies
View Related
Aug 19, 2004
I am trying to set a ControlSource for a textbox to a string what is the correct syntax?? I am also trying to set a record source using DLookup, this is also not working. Could some look at my syntax an correct me.
Reports(!strReportName)!txtClientName.ControlSourc e = "All"
Reports(!strReportName)!txtClientName.Value = _
DLookup("[ClientName]", "CLientIDs", "ClientNumber = '" & _
Forms!frmReport!txtCustom1 & "'")
thanx
View 2 Replies
View Related
Jan 28, 2008
I am trying to set the controlsource propert within VBA for a combo box
S_SECTCOSTCENTRE is the field name I want to set.
My syntax below doesn't work can anyone help
cmbcostcentre.ControlSource = "S_SECTCOSTCENTRE"
thanks paul
View 1 Replies
View Related
Aug 29, 2005
I have a form. The form is bound to an underlying query which is a left join between two tables. (SELECT ... T1 LEFT JOIN T2 ON T1.RID = T2.RID ....)
Now, what I want to happen is the value in T1.RID does have a match in T2, then I want a sequence of text controls to be bound to fields from T2, otherwise I want them to be bound to fields in T1.
I almost have it working. I have Form_Current() and T1_RID_Change() both calling a routine
sub BindControls()
if isnull(T2_RID.value) then
txtField1.Controlsource = "T1.Field1"
txtField2.Controlsource = "T1.Field2"
' etc for each field
else
txtField1.ControlSource = "T2.Field1"
txtField2.ControlSource = "T2.Field2"
' etc for each field
end if
end sub
The problem is that when I change the value in T1.RID, most of the times it does change the controlsource appropriately. But other times it lags. It keeps the wrong controlsource until I try changin RID again, and sometimes the text boxes are just blank, so I am not sure what is going.
Am I ultimately going to be able to make this scenario work?
Thanks,
David
View 2 Replies
View Related
Feb 20, 2008
I want to design a common Search Form which should read records from different table sources and populate a sub-form depending on the user preference. The recordsource of the form should change to point different table or query name depending on user option.
Similarly, the underlying text controls should have their control sources changed to appropriate field in the table selected.
How is this done in run time.
For example, I have two tables and forms (1) for Currency (t_CurrcyMas) and (2) for User details (t_USerMas). I have search form named frmSearch. This has criteria defined as txtCri1, TxtCri2. Depending on the entry of criteria, matching records should be displayed as continuous form in two text controls named txt1, txt2 under sub-form frmSubSearch. The recordsource of the form.frmsearch should be changed to t_CurrcyMas and control sources of Txt1, Txt2 to be changed.
WHat is the syntax for this, and in what event do I insert the codes.
View 5 Replies
View Related
Mar 31, 2008
I have a combobox that selects the customer and shows related information on that customer such as phone number, cc#, etc..
now the trick is i need to allow the selected member to be added to the order information.
For instance, the user selects the customer "Bob" and bobs information is displayed to check for accuracy. After the info is approved the user will continue to process his order. In order to do this I need the customer ID to be the same as the selected customer in the combobox.
Then the user will proceed to fill out the order information, location, date, time, etc.
How can I go about doing this?
Thanks in advance
View 2 Replies
View Related
Feb 2, 2014
In my application I have continuous form with unbound textbox. In OnOpen form event I change ControlSource property of textbox to one of the fields (e.g. "Kol921") in forms recordset. In form footer I have another unbound texbox where I defined ControlSource property as "=Sum([Kol921])".
Values in fields shows correctly, but in "sum" textbox I get Error.
When I get through code in debugging mode (with F8 key), value in "sum" texbox shows correctly, but when I open form normally I get an Error in that textbox.
Here is part of code:
Me.Controls("mat1").ControlSource = "Kol921"
Me.Controls("mat1").Visible = True
Me.Controls("matSum1").ControlSource = "=Sum([Kol921])"
Me.Controls("matSum1").Visible = True
View 2 Replies
View Related
Jun 11, 2013
I have multiple reports that use similar IIF statements as the controlsource for four textboxes. Naturally, I don't want to have to update twelve controlsources if any of the calculations change, so I thought I'd make this a public function. However, I don't know how to pass along multiple textboxes as variables. Here's what I have so far:
Code:
Option Compare Database
Public Function txtColor(ByRef textbox As Control)
Dim str1, str2, str3, str4 As String
'The IIf statement is simplified for this example. It's not important.
str1 = "=IIf(IsNull([Inquiry start date]),'W',IIf([txtInquiry]<1 And IsNull([Inquiry end date]),'R'))"
[Code] ....
And this is in the report (where ??? is what I'm asking about)
Code:
Private Sub Report_Load()
Call txtColor(???)
End Sub
The error I get is "Runtime 424 Object Required"
View 7 Replies
View Related
May 18, 2006
Using this code as part of a module to open a report in print preview and set it's recordsource to the sql I have written earlier in the code. The problem is when I run this it only prints the report it won't show it to me in print preview like I want. Anybody know what's wrong? Thanks...
DoCmd.OpenReport "SitesIssues_qry", acViewNormal, "", "", acNormal
Me.RecordSource = strsql & " " & strWhere & "" & strOrder
View 2 Replies
View Related
Jan 26, 2005
This is driving me mad. I'm trying to copy a table and form from a template.
They both copy fine, but when I try to change the new forms recordsource property, nothing happens.
Private Sub CreateQuiz_Click()
'copy structure from existing BLANK table
a = InputBox("Name of new table", "Create new Quiz")
DoCmd.CopyObject , a, acTable, "BLANK"
DoCmd.OpenForm ("BLANK")
Forms!BLANK.RecordSource = a
Forms!BLANK.Close
DoCmd.CopyObject , a, acForm, "BLANK"
Forms!a.RecordSource = a
End Sub
Any help would be much appreciated.
View 1 Replies
View Related
Jan 27, 2005
Hi All
I have a Main Form, This Main Form has a Subform, the SourceObject for the subform is another form.
What I am trying to do is to set the RecordSource for the SourceObject Form from within the code behind the main form so i can use the same form with various queries.
Appreciate any help - thanks
View 2 Replies
View Related
May 27, 2005
How do I determine what value my listbox has?
Here is what generates the records from Form 2:
If Not IsNull(Me.[StartDate]) And Not IsNull(Me.[EndDate]) Then
strCriteria = strCriteria & "tblWO.Created Between #" & _
Format(Me.[StartDate], "m/d/yyyy") & "# And #" & _
Format(Me.[EndDate], "m/d/yyyy") & "#"
strSql = "SELECT DISTINCT [WOnumber],[Requested],[UDF1],[RequestedBy]," & _
"[Status],[property],[asset]" & _
"FROM tblWO " & strCriteria
Forms("frmFilter").Listbox1.RowSource = strSql
What I am trying to do is trigger a message box from the onclick of a command button on Form 2 and read the listbox # records on Form 1. It's gotta be something simple in my syntax. Search hasnt provided this scenario for me. I have tried these and more:
'If Forms("frmFilter").RecordsetClone.RecordCount = 0 Then
'If IsNull(Forms("frmFilter")).Listbox1 Then
'If Forms("frmFilter").Listbox1.RowSource = 0 Then
'If Forms("frmFilter").Listbox1.RowSource = Null Then
'If [Forms]![FrmFilter]![Listbox1.recordcount] = 0 Then
'If Forms("frmFilter").Listbox1.RecordCount = 0 Then
'If Forms("frmFilter").Listbox1.RecordCount = "" Then
'If Forms("frmFilter").Listbox1.RecordCount = Null Then
MsgBox "Sorry, no records meet your chosen dates. Change the dates and try again."
View 2 Replies
View Related
May 30, 2005
Hi everybody,
I have a form with its tabbed subforms' recordsource determined by the code:
Private Sub childTvl_Enter()
Me.childTvl.Form.RecordSource = "SELECT * FROM tblTvlInput WHERE tblTvlInput.Dept = """ & Forms.frmBudget.txtDept & """"
End Sub
This is to filter the huge data by department and only allowing the specified department to view their code. However, I find it slow and pesky as it only shows the required information when the user clicks on the child. I have tried putting the code above elsewhere but to no avail.
Is there a better way of speeding the filter as well as showing the user his own records when he clicks on the tab?
Thanks in advance. =)
You guys here are saviours!
View 3 Replies
View Related
Sep 26, 2005
I am trying to correctly code a Record Source value in a Form's 'On Open' Event . I first captured parm data from the calling Form, (Last Name, and First Name) to be used in the query as follows:
Private Sub Form_Open(Cancel As Integer)
Dim intI As Integer
intI = InStr(Me.OpenArgs, ";")
SearchFirstName = Left(Me.OpenArgs, intI - 1)
SearchLastName = Mid(Me.OpenArgs, intI + 1)
Me.RecordSource = "SELECT tblName.LastName, tblName.FirstName WHERE tblName.LastName = SearchLastName and tblName.FirstName = SearchFirstName"
End Sub
If I leave off the 'WHERE' clause, I get all the records in the table, so I am sure the syntax in that area is all wrong.
View 2 Replies
View Related
Nov 24, 2005
Is there a way to set the recordsource for a form before it opens? I've got Me.recordsource = "QryNewCaseForm" on the On Open event of the form but I toggle between recordsources once the form is open and if I close it while the recordsource is "QryCaseForm" it won't open as "qryNewCaseForm" the next time I open it.
View 2 Replies
View Related
Oct 31, 2006
Today my client asked me to do something strange.
He wanted a field to be in the subform which was originally part of the parent form and is still part of the parent table.
(Hence, when I change the field in one subform line it would change for all other subform lines related to the current parent record) He insists to put this in the subform and it should be Editable there.
I accomplished this by basically putting a 'Parent table' join 'Child table' query in the subform so I could have access to this one field. Orginially I just had a query with the Child Table but to get access to that one field (and to make it editable) I made this extra join.
I don't like this design eventhough it works..... Any suggestions on how to effectively implement this.
Also I don't understand how Access still knows what to do using the Parent Link And Child Link fields (Located in the Subform Control). How does Access know to create a new record in the Subtable and not in the Parent Table when both are present in my subform's record source now.
View 1 Replies
View Related
Mar 2, 2005
Hello,
I have a combo box that i would like to dynamically change the recordsource (what values are available in the dropdown) based off of the criteria in another field. Basically, I want to switch which query the combo points to.
I am looking to do something like...
If field1 = nulll
combo recordsource = query1 (a list of values from table1)
Else
combo recordsource = quer2 (a list of values from table2)
I am looking to switch which query the combo is populated with, not limit the rows based on field1.
Any Ideas for this? Thanks!
View 3 Replies
View Related
Sep 4, 2006
I have a form with 5 tabs. Each tab has 2 subforms.
When the form loads I am trying to select it's recordsource by doing this:
Me!Subformname_A.RecordSource = "SELECT FieldA, FieldB FROM TableA WHERE"
However, it throws an error.
Is there something wrong with this ?
View 1 Replies
View Related
Sep 26, 2005
I have a report that can be used for 3 different queries. I've been trying for the last hour or so to get three different buttons on a form to open this report but give it an appropriate record source.
I figured there's got to be a way that's easier than three queries, and three reports...?
I was trying to use the same format as with forms :
[Reports]![rptReportName].recordsource = "qryQueryName"
or variations of that. Am I way off? It keeps saying that I've misspelled the name or it doesn't exist.
View 7 Replies
View Related
Apr 10, 2006
Hello,
I'm trying to create navigation buttons that navigate in alphabetical order. I know that the easiest way to do this is by creating a query and using the query as the recordsource for my navigation buttons, but for the life of me I cannot figure out how to actually change to the recordsource to that query for only that navigation button.
I'd appreciate any help
Thank you
PRHC
View 2 Replies
View Related
Apr 18, 2005
Weird yet aggravating problem.
I am trying to setup a routine that will allow the user to change the RecordSource of a subform. The RecordSource is being changed yet once I move to another record the subform is reverting back to the original subform RecordSource. I am using the below command to change the subforms RecordSource...
Me.MySubformName.Form.RecordSource = MySQL
I even to make a copy of the subform and I changed the table it is linked to yet I got the same result
Me.MySubformName.SourceObject = "MyOtherSubForm"
There are no functions in the record selectors that would interfere with the subforms RecordSource.
Any suggestions as to why the RecordSource of my subform will not "stick" when I move to another record?
Thanks in advance for your help!
View 8 Replies
View Related
Jun 29, 2005
Hi
I have problems working with a 3 level form
I have one main form called Projects
Depending on the record on the form Projects, the subform called Products will display the correct info
And on the OnCurrent event of the form Products, it will display the correct info for another subform called Product_Info
The problem is, the subform called Product_Info is dependant on a field on the Projects form, and on a field on the Products form so I cannot directly link the Product_Info form with either the Projects of Products form
What I'm doing is changing the recordsource of the Product_Info subform on the OnCurrent event of the Products subform but for some reason it won't work
If I display the recordsource of the Product_Info subform, it displays the right recordsource triggered on the OnCurrent event of the Products subform, but the data doesn't display
Thanks
View 3 Replies
View Related
Aug 31, 2005
I have a form that pulls data from a big joined RecordSource query. This form needs to be editable. I need to add a field on the form that will contain a value, which is actually the sum of numbers from several records. Because that sum value includes arithmetic in the query itself, I'm assuming that is why the results of the query are not editable. I tried joining the Query that gets that sum value into the main RecordSource statement, but because the sum Query is not editable, doing that renders the whole form uneditable.
I am new to working with Access forms, and VBA in general. I have a lot of experience using VBscript, though. I was asked to get this done and now I have to fight my way through it. I'm hoping there is maybe a way to write a function that will query for the sum value based on the current ID on the Form. The main form cycles through records, and the sum value is a total of points for the record shown in the form. So as each record loads into the form, I need to get the points total that matches that record's ID and display it. The points total does not need to be editable.
Any suggestions for a newbie on how to solve this?
View 2 Replies
View Related
