Forms :: Using Subform As Record Source Criteria
Apr 16, 2013
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.
View Replies
ADVERTISEMENT
May 19, 2015
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:
SandwichName IngredientName Servings
Accordian Lettuce 1
Accordian Ham 1
Accordian Cheddar 1
Accordian Mayonnaise 1
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.
View 8 Replies
View Related
Apr 4, 2013
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?
View 9 Replies
View Related
Nov 2, 2014
Can I change the Records Source of a subform from within that subform and do a requery to have a different set of records displayed'
I want to be able to refine the records displayed in the subform
View 8 Replies
View Related
Aug 16, 2013
I would like to know how i can have a continuous form or subform with dynamic record source.
Before I was using a list box with dynamic row source and i had not problem.
Now about the continuous form i have problem when i define record source of form on the fly how we have to add fields to the form.
View 3 Replies
View Related
Apr 24, 2014
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?
View 2 Replies
View Related
Oct 26, 2006
I am having some problems with an Access database, obviously.. relatively new at the whole thing, so it's quite a patched-up mishmash, and I would be happy to expand on the intended structure, but it would all come together if there's a way to use a form-displayed GUID as a record source criteria.
Essentially, I can do the following:
Form 1 with autonumber GUID 1 has a text field on it which is equal to the autonumber GUID 1 field for the current record. This looks like a series of random characters, nothing like the long number, and it's set to be invisible. It then works, on a subform, to set record source criteria to include this text field as a way to delimit subform 2 by (non-autonumbered) GUID1.
However, what does not work is:
Form1 has an unbound lookup list that includes GUIDs as one of the things displayed. These are displayed properly, as a long number. I can set a text field equal to the value of this on the main form, and see the same long number. But I cannot then reference this text field as a criteria for the subform record source, and neither can I reference the lookup list directly. Essentially, it works when the text field is showing squiggly numbers, but not when showing the 'real' GUID.
Is there a way to convert the nice-and-tidy looking GUID from an unbound lookup list to something usable as a record source criteria? Or is this always possible, and I am just missing something elsewhere? I can do this through VBA for a _filter_ criteria with the ={guid " & guidvariable & "}))" thing, but haven't found a way for record sources.
The structure is rather convoluted.. would be happy to explain if there's no other way to do it. Replies greatly appreciated.
View 1 Replies
View Related
Mar 25, 2005
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.
View 1 Replies
View Related
Mar 19, 2013
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))
View 1 Replies
View Related
Mar 30, 2013
I have a subform called DD subform
I have an unbound combo box named Target
How to I set the control source of Target to reflect my DD subform?
In control source, my only choices are the fields related to the subform, not the subform itself.
View 1 Replies
View Related
Feb 16, 2014
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.
View 1 Replies
View Related
Jun 19, 2014
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
frm.RecordSource = "SELECT * , [orders_maint_detail]![woisnr] & ' / ' & [orders_maint_detail]![Ordernummer] AS Expr1 " _
& "FROM orders_maint_detail " _
& "WHERE (((orders_maint_detail.woisnr)='" & gekozenwo & "'));"
Me.Requery
wonr = gekozenwo
View 2 Replies
View Related
Jul 10, 2015
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?
View 9 Replies
View Related
Sep 21, 2004
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?
Thanks,
JGM
onesweetdude@hotmail.com
View 1 Replies
View Related
May 17, 2013
i have a form that has a record source
Code:
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.
View 7 Replies
View Related
Oct 18, 2014
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.
Here is the SQL:
SELECT [Quote_ID] & "-" & [Version] AS QuoteIDv, tblQuoteID.Quote_Type,
tblQuoteID.Quoted_By, tblQuoteID.Agency, DLookUp("[Contact]",
"tblContacts","[QuoteID] = '" & [Quote_ID] & "' And [Version] = '" & [Version] & "'") AS contact,
tblQuoteID.Quote_Date, tblQuoteID.FileName, tblQuoteID.FileLocation, tblQuoteID.Amount,
tblQuoteID.Active, tblQuoteID.Quote_ID, tblQuoteID.Version, tblQuoteID.Selected
[Code] .....
View 2 Replies
View Related
Jan 22, 2014
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,
View 6 Replies
View Related
Nov 4, 2013
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.
View 1 Replies
View Related
Jan 15, 2014
Access 2013 on a Window 8.1 system.
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.
View 5 Replies
View Related
Aug 20, 2013
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?
View 3 Replies
View Related
Sep 19, 2005
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'm trying to do a
Me.PollingPlacesResults.Form.RecordSource = ""
Me!PollingPlacesResults!PollID.ControlSource = ""
but this does not seem to work in actualy removeing the record source and control source.
View 14 Replies
View Related
Jul 27, 2005
In short can I display a field on a form that is not in the forms field list without using a sub-form? I am trying to show a value from a query on this form. I tried this in the control source:
[qry_op500_entry_delta]![countofphone model]
I get an error stating that this is not in my field list for the form.
If the answer is No or if needed I can give more information on what I am trying to do.
View 6 Replies
View Related
Mar 29, 2014
In my database I have a form who's record source is a query. That query is based on a table, and have set a criteria on one of the fields.
When I use the form to add a new record I want the value of the field in question to automatically be what I have specified in the query.
Instead, the record is added but without having that value in the field, and when I go back to the form it doesn't display the record because it doesn't meet the criteria specified in the query that the form is based on.
View 2 Replies
View Related
Apr 27, 2013
Is it possible to assign a query to the recordsource of a form dynamically? I assume the answer is yes. Can this action be done when the form is not open? If yes what is the correct syntax to use when the code is in a normal module as a public sub? I tried many possibilities and could not get it to work.
Code:
Dim ForNm as string
Dim Qry as string
ForNm = "PersonalFm"
Forms(ForNm).Recordsource = Qry
Forms![PersonalFm].Recordsource = Qry
[Forms]![PersonalFm].Form.Recordsource = Qry
View 14 Replies
View Related
Aug 7, 2014
I have a navigation form that has a tabbed form on one of its tabs. The tabbed form has a 'current client' query record source and allows one to choose from a list of current clients and when a client is selected - details relating to client are displayed on various tabs on tabbed form.
I want to duplicate tabbed form and change record source to a query selecting 'exited clients' so I can see same information but for exited clients.
I have created a new tab on navigation form for my new exited clients tabbed form and changed record source by creating a new exited client query but when I change record source on exited client tabbed form it automatically changes record source of current client tabbed form to the exited client query and visa versa.
View 3 Replies
View Related
Apr 10, 2013
My form with record source of query has stopped allowing edits.
I have reviewed property setting and do not understand what's going on.
View 2 Replies
View Related
