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.
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
Hi all,
I have Form F_CashSalesHead with a subform F_CashSalesInvFoot with one-2-many relationship on their tables. Subform contains a checkbox field that I use to lock the record set (On a command button click it runs one update query to add value 1 to each checkbox to make Enable=False all the records of current invoice on the form).
One-2-many relation ship is made on InvNum field in both tables.
When I open F_CashSalesHead form, bcz of some code line I wrote on On Load event of F_CashSalesHead , at the beginning it give massage how many invoices are pending to lock and would you like to see. If click “Yes” to see list, it opens a small form that called F_Count_Unlocked_Invoices showing invoice numbers and unmarked checkbox which is pending to lock. This small form is based on following query,
SELECT DISTINCTROW T_CashSalesInvFoot.InvNum, T_CashSalesInvFoot.CashSalesCustomerName, Sum(T_CashSalesInvFoot.Lock_Cash_Inv) AS [Sum Of Lock_Cash_Inv]
FROM T_CashSalesInvFoot
GROUP BY T_CashSalesInvFoot.InvNum, T_CashSalesInvFoot.CashSalesCustomerName
HAVING (((Sum(T_CashSalesInvFoot.Lock_Cash_Inv))=0));
This works fine.
What I am looking for is, I want to use the same F_Count_Unlocked_Invoices form for Credit Sales invoice also with the same trick. Because I don’t want to create another same form and write code that help to increase size of db.
Can it be done just by changing record source of form F_Count_Unlocked_Invoices? Or what is the way to do it?
With kind regards,
Ashfaque
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 RelatedI have created a system consisting of a data entry form etc. It was originally connected to one record source exported from a sharepoint site.I had to add a field to the sharepoint site and so i created this additional field and re-exported the data and changed the forms record source and all occurences in the code of previous to new record source.
However, this change of record source produces the following error on every single event.The expression On Load you entered as the event property setting produced the following error: member already exists in an object from which this object module derives.
I tried to break the code as soon as it hits the load function to track the error - but it doesn't even run this function so the code is not executing at all.When i connect back to the old record source it works fine.
I'm changing a record source on a form when the user clicks a "save" button.
I'm doing this to store a value from the current form in a table that is not part of the default form query.
This works fine.
I have a separate button on my form called "Home", when the user clicks this button it requerys the current form which triggers the before update event to run, this in turn brings up a message box which asks the user if they want to save or not. If they click yes then it runs the SaveButton click() code.
For some reason when the user presses the save button and then exits, everything works but if a user presses the “home” button which triggers the save button then it brings up the “2107 The Value you entered doesn’t meet the validation rule defined for the field or control.” Runtime error and stops on the change recordsource command.
Why triggering the same code directly from a button or indirectly from a before update event has two different outcomes.
I have a form (named Example) to create reports by selecting fields from tables or queries. there is a option box (name is KynkSec) with two options (Table, Query) and a combobox named as KynkTurSec.I want to change the data source of combobox either table or query. By afterupdate, that combobox is requering the listbox "ListKynkAlan" and I can see fields of selected table or query. (That is my dream))Unfortunately I can not do that. Combobox is showing only tables or both of tables and queries. But not only query.
Here is str source of my combobox:
SELECT MsysObjects.Name, MsysObjects.Type FROM MsysObjects WHERE (((MsysObjects.Type)=1) AND ((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys")) OR (((MsysObjects.Type)=5) AND ((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys")) ORDER BY MsysObjects.Name; That is showing both of tables and queries.
And I wrote a code for KynkSec option box;
Private Sub KynkSec_AfterUpdate()
' Populate rowsource of KynkTurSec
Dim strSQL As String
On Error GoTo HandleErr
Select Case KynkTurSec
Case 1
strSQL = "SELECT MsysObjects.Name FROM MsysObjects" _
& WHERE(((Left$([Name], 1)) <> "~") And ((MsysObjects.Type) = 1) And ((Left$([Name], 4)) <> "Msys"))
Order BY(MsysObjects.Name)
Case 2
strSQL = "SELECT MsysObjects.Name FROM MsysObjects" _
& WHERE(((Left$([Name], 1)) <> "~") And ((MsysObjects.Type) = 5) And ((Left$([Name], 4)) <> "Msys"))
Order BY(MsysObjects.Name)
Case Else
End Select
[code]...
But this code is not working and giving a warning messsage "Sub or function is not defined"..So How can I change the source of combobox, either table or Query?
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 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] .....
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?
Hi,
I have two Tables TA and TB. I have a set of queries based on TA. I want to substitute TB for TA (i.e. TB will stand in place of TA). I want the old TA queries remain unchanged but now be based on TB.
If I delete TA and then rename TB into TA, I will lose the queries. Therefore I want to 'point' the queries (in the most efficient way) to TB instead of to TA.
Then I can delete TA. Then I can rename TB into TA and the queries will follow along, I expect.
I have been trying in vain to find a way of pointing the queries to TA instead of TB? How do I do it?
I have done it with Forms (Design Views, Properties), where I can select a datasource. But where is the corresponding thing enabling me to select a datasource for Queries?
Thanks.
Adrian
I have a Query called Median and all it does is pulls all the fields and all the records from 1 table. Since the Query is connected to a lot of other queries, and the table it's connected to changes twice a month, I was wondering if there was a way to set this up in a form maybe using vb, so a user could maybe Click a new table from a combo box list of current tables in the database, then click a button that says "Change", and it change all the fields to the new table and removes the old table from the query.
Or what might be better is: Already on the form, i have part of the name of the new table already entered by the user (it bases the new name of the new table off this text box). So, what I can use is the Text in that box and an & " the rest of the tablename", and automatically change the table in the Median Query based on that criteria. If that is too complicated, then a combo box will be sufficient.
Another way i guess, would be to setup a hidden field that takes whatever's in my text box (which is a date) and adds " the rest of the tablename", then I could base the replacement table of the Median on that one text box.
Anyway, is there a way to do this?
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.
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.
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
My form with record source of query has stopped allowing edits.
I have reviewed property setting and do not understand what's going on.
I am setting up a generic form template that will enable me to maintain data in a standardised way. All generic code is stored in a module. There is very little code specific to the table being maintained. Much of the form functionality is determined by data held in supporting tables therefore data driven.
When a form is loaded a commonly named query is created that is used as the Record Source of the form. This query only ever returns one record and uses the value of textbox on the form as criteria in the unique id field.
Despite immediately creating the query in the Open event of the form, Access tries to process the form first and returns an error as the Record Source query applies to another form, the one last opened.
How can I create the query before anything on the form is referenced / processed without creating it before the form is opened?
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 the following tables:
Supplier
Supplier# [Other columns]
Material
Supplier# Material# [Other columns]
DeliveryHeader
Delivery# Supplier# [Other columns]
DeliveryDetail
Delivery# Supplier# Material# [Other columns]
I've created a form based on table DeliveryHeader with a subform based on table DeliveryDetail. The two are linked by Delivery# Supplier#.
On the form, Supplier# is a combobox that lists all suppliers from table Supplier.
On the subform, Material# is a combobox that lists only the materials supplied by the supplier selected with form's Supplier#.
The problem is that this second list always shows the materials of the first listed supplier. For instance, when the form is loaded, it shows delivery #1 with all its details on the subform. Suppose supplier loaded to form is #1.
1) If I use subform combobox, it shows correctly supplier's #1 materials. But then, whatever delivery I navigate, it always shows supplier's #1 materials.
2) If I navigate to another delivery without using subform combobox, suppose I stop on a delivery where the supplier is #4, then subform combobox shows correctly supplier's #4 materials. But then, once again, whatever delivery I navigate, it always shows supplier's #4 materials.
I've spread Me.Requery here and there but without any success. What trick must I apply to force the execution of the query of the subform combobox Material# each time I navigate to a new record on the form?
I have an application with a form where 20 subforms are used to add data to the same table. The 20 subforms are used to divide the data being added into categories ( think of a grocery store with categories of "Meat", "cereal', vegitables etc)
I use the same subform (with different names) and change the record source on the formOpen event to change the subforms properties. A portion of the code is shown below.
tempRecordSource = "SELECT [tSkills Assessment].Skill, [tSkills Assessment].Scale, [tSkills Assessment].Comments, [tSkills Assessment].AreaOfFocus, [tSkills Assessment].SubSectionTitle " & _
"From [tSkills Assessment] WHERE (([tSkills Assessment].SubSectionTitle)=" & Chr(34) & tempSubSectionTitle & Chr(34) & " and ([tSkills Assessment].SkillsAssessmentID)=" & [SkillsAssessmentID] & ") ORDER BY [skillOrder];"
Me(tempSubFormName).Form.RecordSource = tempRecordSource
I use an array to feed the code (which is in a loop) the variables needed to assign the correct properties to all the subforms.
And it works great. Yes I know I could have created 20 different subforms, but in an attempt to keep my application simple I am trying to have less objects.
The problem is in my output (the Report).
When I try to do the same thing on subreports it doesn't work. I figured the logic should be the same but the code that corresponds to the form code "Me(tempSubFormName).Form.RecordSource = ..." doesn't work. Either the logic is wrong or I am not using the correct event???
I have tried every combination I could find on the internet and $300 worth of Access manuals but no luck. Any ideas?????
I am trying to add a profile picture to a dog database. The way I have it is that the picture is a file and gets renamed the dog_id, on the query for the form I have added the extra coloum
[code]ImageFile: "C:Gundog Training DatabasesProfilePictures" & [Dog_ID] & ".jpg"[code]
But the image isn't showing....
I have a table and a simple query that pulls results from the table. Nothing too crazy. But, if I were to go in and change some of the data/values in the query results it will change the respective data in the table. I know that this cannot be right. What do I have to do to either prevent the ability to change query results and/or prevent any changes in the query from altering the original data in the table.
View 2 Replies View RelatedThis 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
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?
Thanks,
JGM
onesweetdude@hotmail.com
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.