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.
I have a form that shows multiple rows of linked/child records.The form in question is the "frmFilterNumberTypeView" form. When a particular filter is used (installed on an automobile), I would like to be able to click on the corresponding "Installed 1" button left of the filter number, so that by code, I can reduce the Qty on Hand by One, and insert the corresponding filter ID to the History table, along with the date/time the filter was used.
how to reduce the Qty on Hand, nor how to do the updates to the History table, I'll figure that out myself over the next week or two (hopefully it won't take that long). What I'm trying to figure out here is how I can associate the red "Installed 1" button with the filter to the right of whichever button I press.
how to sort the filter column on this same form. I'm fairly certain that this would be very easy to do if the subform in question was populated by means of a corresponding query, but I'm afraid that if I go that route that I'll spend another 20+ hours trying to figure out how to get the proper filter records to match the filter manufacturer, not to mention having to next figure out how to link the table so that if I modify any particular filter record, that I'll be able to have the associated table update accordingly.
Is it possible to sort the filter column without the subform record source being a query?
I have a navigation tab with 6 sections, of the 6, 5 of them are self-made split view with a form view on top and datasheet on bottom and upon adding records through a command button on form view I would like the datasheets to update.
On other forms it would (I believe) update to the bottom of the datasheet, now on the ones I've been banging my head against it either replaces the top record or doesn't show up unless I switch tabs and switch back. From my searches I assume this is in need of a requery...
Hello, I have a form with a subform. I want to change the record source on the subform during an OnClick event. I am not sure what I'm doing wrong, but I get a "object does not support this method" error. Can anyone help? Thanks in advance.
I am having trouble figuring out the method to automatically update some fields in SubForm from 2 other SubForms.I have attached 2 pics, the first GradeEntry1 shows what the tblTopic_Class_Grade form looks like after I manually enter everything into it. GradeEntry2 is what the form looks like when I fill out the Form starting at the top.
I'd like the tblTopics_Class_Grade form auto-populate the TrainingClassID (it currently does this), TopicClassID, StudentID, TrainingTopicID based off the entry from the above forms.My end goal is that I need to have a grade for each student on each training topic for each class. Like:
I have a form that has a main for, a subform, and then another subform inside the 1st subform. The form works like this:
Conact info --->Call info and notes ---> Orders worked
Each of those being nested as they appear. The problem is, the user usually jumps strait to putting the order number in "Orders worked" before entering in any notes or info... So no record exists in it's parent form to tie to. How can I have the database create a new record in its parent form when the user types in the subform?
Hi, I have a problem I don't know how to solve it. I have a subform in datasheet view, linked to the main form. There is a running total on the main form based on a dsum on a field of the subform. When I delete a row on the subform datasheet, the main form calculation is not automatically updated. I had to create a "update" button to do the refresh. Is there a "on record delete" event available for a datasheet sub form? Thank you for any help.
I have a listbox "lbxMP" that holds names of forms and a subform "ctrlMP" that opens selected form (ctrlMP.sourceobject = lbxMP). I also have some various master cbx that control criteria in each form. I am trying to create a button that will not only clear/null all the cbx values (which works fine), but I am having a hard time telling me.ctrlMP to go back to null or unbound (i want it to blank out the subform to make it look fresh). I have tried requry and also me.ctrlMP = null and ctrlMP=unbound with no luck.
This code loads a new subform in the frame. It then scopies a value to an input field but then i need to set the record source and display the data. This latest pasrt does not work.I've been trying for some time now by changing the part before recordsource but no luck.
Code: gekozenwo = Me.Keuzelijst0 Forms!switchboard.subfrmWindow.SourceObject = "frmInformation" Dim frm As Form Set frm = Forms!switchboard.subfrmWindow.Form frm.DataEntry = True Forms!switchboard.subfrmWindow.Form!Tekst33 = gekozenwo MsgBox gekozenwo
I know my form is large and got too many fields for a standard form but trying to expand it anyway and don't really want to go to sub-forms...as then cannot expand it without re-creating forms/queries/tables structure. (or that what i believe)
My form and subsequent table is nearing the 255 limit, so I created a second table and just used a query to put both in form, but then I found out that a query also has the limit...
So if have two queries set up from each table but to same form?
Currently I have over 600 columns I need to use in a query and since the limit is 255 columns per query, I need to create multiple queries. Currently the form shows the record source of the single query I have created. Now that I need to create the other queries, how do I have the forms record source to recognize the other queries as sources as well?
SELECT tblClientDetails.FirstName, tblClientDetails.Surname, tblClientDetails.MobileTelephoneNumber, tblClientDetails.SentTextMarketing FROM (tblCategories INNER JOIN tblItems ON tblCategories.CategoriesID = tblItems.CategoriesID) INNER JOIN ((tblClientDetails INNER JOIN tblOrders
[Code] .....
this does not allow edits on a continuous form. the edit i am trying to do is the check box. it will allow edits if the grouping is not on but if it is not on then i will get many duplicates of client details.
so how do i set it up to allow edits but still only get one instance of each client detail.
I have a main form with two unbound text boxes that filter a subform with a select query in the record source. The problem is that it only works sometimes.when I copy and paste the SQL into a query instead of the form it works all the time. for example when I open the query and the form side by side with the same SQL the form does not show results but the Query does, and yes I have "requeried" both of them after entering criteria into the unbound text boxes.
I have two unbound unlinked subforms residing on a 3rd unbound main form. When I enter the current record on Subform1 I would like the matching record(s) on Subform2 to be highlighted or otherwise formatted. I can get this to work for only the first record on subform2 due to the way I have my code setup on Subform1:
Code: Private Sub Accounting_Unit_Enter() 'find where AUs match. only works for first Subform2 record If Me.[Accounting Unit] = Forms!MainForm.[Subform2]!AccountingUnit Then msgbox "Match" End If End Sub
My thought was that I needed to reference the Recordset of Subform2 and search for all AccountingUnits that match the current AccountingUnit of Subform1,
My form has a Record Source of qry_Profile, it is a query that shows the user the records of the dogs that they currently own. I want to put a button in the form footer to allow the user to show the records of all the dogs that they have ever owned.
So my question is how do I change the record source with vb.
I have a combo box based on a look-up table which I use to filter the records in the form. Seems after closing the form and reopening it, the filter remains.
I'm not sure if this is referred to filtering or restricting the records.
The value in the record source doesn't get changed but the records displayed don't reflect this. The records shown are the last filter I selected in the combo box.
The only work around I have found is I must explicitly set the record source in the on open of the form.
The was not occurring with access 2010. And I have made no changes what-so-ever.
I have one form which houses all my product information via the use of a query and subforms where appropriate. I would like to filter it in order to find records missing certain information.
I want to filter the information using a combo box.
The combo box has a user friendly name (Product Dimensions) and the name of a query related to that name (qryfilter_missing_prod_dims - this query is the record source query for the form plus the query that has the missing info, the query is set to return all records that match ie. all records that are missing info).
I want to write a vba code to filter my form based on that query.
And i can't get it to work using the cmd.applyfilter (docmd.applyfilter me.cbo_missing)
I've also tried to use me.recordsource. when i do it filters perfectly, but i can no longer edit any of my data in the form.
How to filter my form using a query that is not in the native form's record source?
I have a table contains 5/6 column. now i need to filter the table on a form randomly.
Suppose I have 6 different combo box for 6 column. Now on the sub form the control shows the whole table. i this case i have clicked the 3rd combo box, then it will show the whole column in its list. if i select any one of it the table will show the common values below. then i will click the second combobox which will populate the present data shown into the subform table after the first filter.
After that i wish to filter the fifth column where the fifth combobox will populate the data after the second filtering.
By all means the active data into the column will ony populate into the combobox.
I have a basic DB for recording supplier records, you enter a supplier ID and it performs Dlookups to run and display query results on the same form. That part works fine.
I've added a subform and linked it to an existing query which returns the suppliers top selling lines and the query picks up the supplier from the ID field on the main form, however when I enter a new id the subform doesn't update with the results specific to that supplier.
So I think I need a way of getting the subform to re-run the query each time I update the supplier ID field, but I can't see an option for this in the subform properties.
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.