Forms :: Updating Record Source Of A Subform

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 Replies


ADVERTISEMENT

Forms :: Using A Query As Record Source Of Subform

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

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 14 Replies View Related

Forms :: Changing The Record Source From Within A Subform

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

Forms :: Continuous Form Or Subform With Dynamic Record Source

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

Forms :: Possible To Sort Filter Column Without Subform Record Source Being A Query?

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

Forms :: Datasheet Subform Not Updating From Main Form Add Record?

Jun 23, 2015

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...

View 14 Replies View Related

Changing Record Source On Subform

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

Forms :: Automatically Updating Fields In A Subform From Another Subform

Feb 11, 2014

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:

Class1-Student1-Topic1-GradeX
Class1-Student1-Topic2-GradeX
Class1-Student2-Topic1-GradeX
Class1-Student2-Topic1-GradeX

View 6 Replies View Related

Forms :: Set Source Of Control Box To Subform

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

Updating Subform Creates New Record In Parrent

Jan 12, 2007

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?

Thanks!

View 1 Replies View Related

Updating Main Form When Deleting Record From Subform Datasheet

Dec 21, 2006

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.

View 1 Replies View Related

Forms :: Clear Source Object On Subform Back To Unbound

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

Forms :: Record Source Set From Another Form

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

Forms :: Two Queries As Record Source

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

Forms Record Source For Multiple Queries

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

Forms :: Record Source Not Allowing Edits?

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

Forms :: Record Source Query Is Empty?

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

Forms :: Highlight Unbound Subform Record That Matches Other Subform Record

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

Forms :: Changing Record Source Of Query Profile

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

Forms :: Split Form Ignoring Record Source?

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

Forms :: Filter Form Using A Query Not In Record Source

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

Forms :: Updating Navigation Subform From Another Form

Feb 12, 2014

I'm using Access 2010 and need to update a sub form in a form that is within a navigation form. So it goes:

Navigation Form > Main Form > Subform

How do I do this? I've attached the following to a click event of a cmd button but I just can't get it to work:

Forms!frmMain.NavigationSubform.Form.frmJobHeadSub Form.Requery

View 10 Replies View Related

Forms :: Filter Subform By Updating Combo Box

Dec 14, 2014

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.

View 1 Replies View Related

Forms :: Updating Subform With Query Results

Feb 21, 2014

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.

View 2 Replies View Related

Remove Record Source/Control Source

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







Copyrights 2005-15 www.BigResource.com, All rights reserved