I have a main form with a subform. On a subform I created a combobox which I want to populate with ClassIDs that match the main forms Individual. I know the criteria in VBA for the above would look something like this:
"SELECT tblClassesAttended.ClassID FROM tblClassesAttended WHERE blClassesAttended.IndividualID=" & Me.Parent!IndividualID
but when I type such statement in the row source of the control it does not work. What is the correct way of refering to the parent form when typing the SQL in the row source? BTW. I cannot use the main form's name
I added a new field to one of my tables and query, but when I try to add a control for that field into my form it is not recognising it and the field isn't appearing in the sources for the form, even though the table is sourced to the form? Or am I going to have to do the usual and redo the entire form because I made a minor adjustment?
I have a form that has five subforms on it. On Open all the subforms are unbound (so i can prevent the queries from running before the On Open event to speed up). Then i can enter my criteria for all five subforms in txtbox and click run. After I establish the source object for the subforms, i cannot establish the controlsource for several text boxes that pull the data from the subform, i get #Name?. her is a sample from the on click event... the top links the unbound subform and the bottom should then link a control in the subform to a control on the main form.
Me.Child167.SourceObject = "query.RP Sum Fuelman F1" Me.Text71.ControlSource = "=[RP Sum Fuelman F1 Subform]![cntRecords]"
is it possible to set 2 control source to a textbox.i have a textbox in a form where it gets the value from subform =[Child9].[Form]![Text25] and i need to save it in a table in field INV_TOTAL
I have a form that uses a certain table(tblDetails) as the control source and within this form I have 3 combo boxes. One combo box looks up a company name from another table and I was wanting my second combo box [LastName] to have its look up list based off of the first combo box choice.
In above example the criteria is based on [ProductID] on a form.Is it possible to change it, so criteria is based on a value from a specific query, i.e. similar as above?I have tried something like this, but it doesn't work:
I have created a table for installer invoicing, and have a field for invoice amount. I have created a user input form that allows a user to fill in certain pay rates for different aspects of installation, and would like to know how to make the invoice amount a calculated control that will auto update the field InvoiceAmt in my table. I can't figure out how to do this in the property sheet.
I have an unbound form which has 2 unbound combo boxes on it, both based on the same table, but I have used a query for each one to created the sort order, one alphabetically by item name (cmbA) and the other numerically by the item number (cmbN) . I also have a text field (ID) upon which is based a report when the user clicks a button. Currently the text box (ID) has a control source of column 1 of cmbA and I have the report working perfectly if one selects from the name combo box (cmbA) What I want to do is allow the user to select from cmbN and have the control source of the ID text box equal the number the selected so the report button will work then also.
I have tried to set the control source after update of cmbN but all that does is blank out the ID text box. I have not done this before so I am sure I have done it incorrectly. I tried again this morning using me.ID.ControlSource = Me.cmbN and stepping through the code shows that the cmbN ControlSource is equal to ID but I can't make the code that follows use the results.
I have a combo box control in my form named PayDateID, but I told that control to DISPLAY the associated field PayDate.
I now am trying to make a separate control in the same form whose control source is the DISPLAYED PayDate, but it only wants to reference the underlying PayDateID.
How can I make the control source be the displayed value in that combo box?
I currently have a DLookUp formula in a Text Box Control Source. It returns the value I am looking for, however, the value will not add to the record. I have tried putting the DLookUp formula in the Default Property, but it is not working.It is a multi-criteria DLookUp, where the criteria is entered into the form.
=DLookUp("[TBA CUSIPs]","[TBA CUSIPs]","[Settlement Month]=" & [Forms]![Enter Trade]![Settlement Month Text Box] & " AND [Coupon]=" & [Forms]![Enter Trade]![Coupon Text Box] & " AND [Instrument] = '" & [Forms]![Enter Trade]![Instrument Combo Box] & "'")
I'm getting a Name(hash) error when I try to set up a control source to a calculated query.
I'd like to just be able to create a form and the write in the control source as it look neater (and it the right way to do it) then subforming a query for one text box.
I presently have tried in the expression build =[qryCallRunningAverage]![Average]
I have a combobox with a value list as the control source. I have a user who clicks the drop-down, but there are no options. Every other user is able to view these options, and this user has no problem with any other combobox with a value list.
My form is based on a table (SOP TBL) and has several text boxes with control sources of the records in the table. I need to add a criteria to one field (SOP Number) so that only current versions of the number show up. I have the SQL for a query that I use in another form for a combo box as shown below. What this criteria does is find the version number which is the last 2 digits of the SOP Number and displays only that record for the SOP Number in the combo box. How can I apply this code to the text box with the control source directly from the table so only the current records are displayed on the form?
Code: In (select max([SOP Number]) from [SOP TBL] group by left([SOP Number], len([SOP Number]) - 2))
I am working with textbox [DispCD] (a date field) on form [Contract]. There is a dropdown on combobox [Searchbox] for selecting the desired record. I am writing VBA in the [Searchbox] OnChange event. The control source for [DispCD] would normally be "=Searchbox.column(11)". If "column(11)" has a date, I want [DispCD] to show that date and then be locked. If "column(11)" does NOT have a date, I want [DispCD] to allow entry of a date with the date picker showing. I've tried to assign the control source to [DispCD] with this:
CSVal = Format(Forms![Contract]!SearchBox.Column(11), "ddd m/d/yyyy") (this works) If CSVal = "" Then _ Forms![Contract]!DispCD.countrolsource = "=SearchBox.Column(11)"
I get "Object doesn't support this property or method" error.
On the form I need to give the user the option to select a 'from date' and 'to date' and for this I have put in 2 calender controls which have the same control source(same table column). The problem I am facing is when I select a date from one calendar, the other calendar control automatically populates with the same date and vice versa.
I need to have a functionality where 2 different date can be selected and then when the Search button is clicked, then data within the date ranges should be displayed.
what changes I need to make to acomplish this ? find a screenshot of the form attached.
is it possible to use a sum select query in the control source field of a text box. I have a query that works fine in a combo text box but I keep getting a 'syntax error with subquery parenthesis' when I try and build on in a normal text box.
I am building the query using query design view and the query works and then I'm copying the SQL code behind the query into the text box field and access reformats it s a bit so not sure it's that's the reason.
R_P_Data_P = Table approvalNosys = Field in the table status = Field in the table score = Field in the table cmrOverview = Form [approvalNoSys] = field in form 1 is the criteria
Code: SELECT R_P_Data_P.approvalNosys, R_P_Data_P.status, Sum(R_P_Data_P.score) AS SumOfscore FROM R_P_Data_P GROUP BY R_P_Data_P.approvalNosys, R_P_Data_P.status HAVING (((R_P_Data_P.approvalNosys)=[forms]![cmrOverview]![approvalNoSys]) AND ((R_P_Data_P.status)="1"));
I have a set of cascading combo boxes. When a value is selected in Cat 1 ID, it narrows down the selections in Cat 2 ID which narrows down the selections in Cat 3 ID.
However, the values don't show up for columns Cat 2 ID or Cat 3 ID until I enter the row. Then I have it set to requery the fields because the underlying data isn't populated. But then when I leave the row, the values go away.
Why doesn't it show the underlying value from the table? Each column is bound to a field in a table - I would assume that the recorded value would show up. Did I miss something easy?
I have a form, with a tab control on it. Each tab has a subform to display data. One tab is meant to be a summary tab of the rest, so I want to pull data from certain controls on each of the other tabs to display on the Summary tab.
If I set the ControlSource to Forms!subfrmLABOUR!txtTotalHours.Value the control just displays #Name?
I assume that means it can't reference the ControlSource. Is it just a syntax thing? I've tried various methods, but no luck.
I have an unbound form with three tabs. On each tab there is a sub form. Each tab is a search form and each sub is a results returned. I have made everything unbound and set the sub form recordsource and its controls control souce on afterupdate of the main form search criteria. Works fine except for after some use the db decides the sub forms are not unbound and sets the record source and control sources.
I have table that I had to add a new field to which we update with a form. I tried to add a control for the new field but the field does not show up in the list for the control source. I am trying to add a list box to the form with a blank and 5 options.
I have attached screen shots of the table design and the form. The table has the field in datasheet view and I have manually entered a few entries in it but it still will not show in the control source for the form control. The top section of the form is where we enter and select the data for the new records. The bottom section (circled in red) autofills the matching record, from separate tables, for updating with the new entries.
I have added form controls for modified fields in the past so I am confused about why this is happening.
I currently have a query that a subform uses to display some results. In this case, there are 3 columns displaying the name of a sandwich, the ingredients, and the number of servings of each ingredient. It looks like this:
That is the current output to the subform. The SQL statement I use to generate this list is here:
SELECT [TBL_Sandwich].[SandwichName], [TBL_Ingredient].[IngredientName], [TBL_SandwichContent].[SandwichTotalServings] FROM (TBL_Sandwich INNER JOIN TBL_SandwichContent ON TBL_Sandwich.SandwichID=TBL_SandwichContent.Sandwi chID) INNER JOIN TBL_Ingredient ON TBL_SandwichContent.IngredientID=TBL_Ingredient.In gredientID WHERE TBL_Sandwich.SandwichID=6;
I have bolded the last item the 'SandwichID'. It is this number I need to change dynamically based on a button being pressed on the main form. Essentially they enter a new sandwich, put the ingredients in, save it io the database, and I save the servings for each item as 1. Now I'd like the new sandwich to be displayed in the subform using the new ID of the sandwich which was just created.
If I try to put this long SQL statement in the RecordSource property I am told it's too long for the property to handle so I can't set this property dynamically. how to get the subform to requery with the new ID number of the item I just generated. I can get the ID number via DLookup, that's easy, but setting the subform to use this is my problem.
I want to use a query as the record source of a subform. I want to be able to actually write the query out, not refer to a query object, because I want to change the query on the fly using VB.
This subform displays records when I have it opened by itself, but will not return records when it's displayed as a subform. Does writing the query out not work for subforms?
I am trying to get a follow-on form to reference a subform. Here is what I currently have going:
I have two (2) tables: 00 Orders and 00 Products; both forms have primary keys "Orders ID" and "Product ID" respectively.
The "Orders ID" in "00 Orders" has a one-to-many relationship to "Orders ID" in "00 Products".
The user adds the main order details to "00 Orders" Table via "01 New Order" Form, and adds the ordered products to "00 Products" Table via a "00 Products Sub Add" Subform.
The user can then update the order via "02 Update Order" Form which has a "00 Products Sub Update" Subform.
This is working perfectly!
The "00 Products Sub Update" Subform has a "Contr Details" Button for each product ordered which opens a follow-up "00 Products Sub Update Contract Details" Form to allow the user to add additional details to the "00 Products" Table. I did this to reduce the size of the main form as the additional details are not needed to be viewed as the order process is going through the stages.
In the Record Source of this "00 Products Sub Update Contract Details" Form I have the criteria for the "Product ID" set to [Forms]![00 Products Sub Update]![Product ID]. This ensures only the additional details for the selected product come up on the form.
Everything works perfectly; I can add new orders, I can update orders, and the selected product comes up when I press the "Contr Details" Button on the "00 Products Sub Update" when it is a stand-alone Form.
What the problem is, Access asks for the user to "Enter Parameter Details" for "Forms!00 Products Sub Update!Product ID" when the "Contr Details" Button is selected in the "00 Products Sub Update" when it's a subform within the mainform.
So everything works when I use "00 Products Sub Update" as a Form by itself, but Access does not seem to be seeing the "Product ID" when "00 Products Sub Update" is a subform.