Modules & VBA :: Passing All Values From Subform To Another Subform
May 26, 2015
I know how to use openargs to pass values between forms but im not sure how to go about passing all the values from one subform to another.
To be specific I have an order form (frmCustomerOrders) that has fields like CustomerName, EmployeeName, OrderDate and so forth, the subform (frmCustomerOrdersSubform) contains information on the Products the customer is ordering which usually has multiple records with fields like ProductName, PartNumber, Quantity and ItemNumber. I want to pass the values from the CustomerOrdersSubform form to another subform (TransferSubform) My initial thought was to use something like the openform command through openargs but im not sure if that will work or if there is another method I should use.
I got a little problem wich i'm trying to solve for several days now. My situation is like this. I got a form called "Frm_File_List" and in there a subform "Frm_List_Sub" as in a datasheet. In here there are several records. Im trying that when u double click on the record that a new form "Frm_View_file" will be opened, but according the record I selected in the subform. So when I doubleclick on ID 7 it opens the form with record matching ID 7.
I tried in vba the following
Private Sub Form_Click() DoCmd.OpenForm "Frm_View_File", , , "Files_ID = Forms!Frm_List_File!Frm_List_Sub.Form!Txt_Files_ID " End Sub
But when I double click on the record it gives me this popup screen asking for the ID. When I fill in this manually then it does give me the wanted record in the form. But how do I get his ID passed automaticly?
my parent form has two subforms. when the user double clicks a value on one subform it populates the first blank field in the other subform. I created a code for the double click on the subform
The forms in the code are subforms on a TabControl on a main form.
I have this code which needs to add together only the areas [Area] of records with a FloorNumber field value of 1 or 2 or 3 or 4.
At the moment the code works for one entry of 1 in the form frmRoomDetails. I'm guessing i need For Next or something like that but i don't know and also unsure of how to code it.
Private Sub FloorNumber_AfterUpdate() If [Forms]![frmSiteDetails]![frmRoomDetails].[Form]![FloorNumber] = 1 Then [Forms]![frmSiteDetails]![frmFloorsDetails].[Form]![Text8].Value = [Forms]![frmSiteDetails]![frmRoomDetails].[Form]![Area] End If End Sub
I am looking to check 3 sub forms for values and if they contain any then delete them. After a bit of googling I discovered that you should use recordset.recordcount - to which I tried but I get method or data not found - is this a valid method?
My code to which Im using is:
If Me.NoneChargeable_Admin_subform.Recordset.RecordCount >= 1 And Me.NoneChargeable_Manufact_subform.Recordset.RecordCount >= 1 And Me.NoneChargeable_Research_subform.Recordset.RecordCount >= 1 Then DoCmd.RunSQL "DELETE NoneChargeable_Admin.*, NoneChargeable_Manufact.*, NoneChargeable_Research.* " & vbCrLf & _ "FROM NoneChargeable_Admin, NoneChargeable_Manufact, NoneChargeable_Research;" End If DoCmd.Close acForm, "NoneChargeableHrs_frm", acSaveNo End Sub
I am a bit of a novice when it comes to Access, but have managed to create a form with a subform embedded and various filters to show different data within the subform, including a date range filter. The code I have used for these filters is as follows:
Private Sub Command40_Click() Dim strCriteria As String
This all works fine, but I'm wondering what I need to add to this code to make it so that if the date boxes are left blank, records from all dates are displayed. At the moment I have to enter dates in order for it to work properly.
I have a form called chartinfo which contains a subform called testgroup. For ever new record in the main chartinfo form I need the testgroup subform to display all records from the testgroup table. see attachment it is access 02
I would like to transmit several parameters from my main form to the subform when I open it.
Parameters could be used in the subform by onload event, for eample, to define form caption, hide or show buttons, enable or disable edit boxes, give them a color, ... It could also allow to use the same form in different application contexts, which would reduce so the development time.
More than one technique might be possible. I used the following one : - I put the parameter value I want to pass into myParameter - when click on the button to open the subform : DoCmd.OpenForm myDocument, acNormal, , , , , myParameter - when loading the subform, retreive the parameter value in Me.OpenArgs and using it (eg disable a button)
It works fine. But I can pass only a single value with this technique and it's not enough. I tried to give an array as myParameter and fill it with my parameters values, but Access refuses an array to be in the DoCmd statement for OpenArgs option.
Any idea about passing several parameters to the subform ? Or may be an alternative to the DoCmd technique ?
I am in no way shape or form familiar with access beyond the very basics. I am however the house programmer and some of the employees here like to create access forms (no programming knowledge though). So from time to time I am asked to help out with the more difficult tasks.
The issue I have run into is that we built a form(that has no connection to a table or query) that has a subform on it (that is connected to a table). Basically the subform information is used to help fill out the form which is then printed.
Problem is that when the form is printed all of the records of the subform are also printed. I am struggling with finding a solution.
Is there a way to just have the subform print the current record shown?
I tried having the form grab the current record from the subform to print the info out on the main form but I can't seem to get the two to talk. How do I use subform info on the form? I have tried something like Me.mysubform.control.text or [Forms]![Me]![mysubform].whatever but nothing seems to work.
One work around I am currently trying to implement is to have the subform visibility set to false which allows me to just print the main form. Problem is that I need the info off the subform also. I try to say something like myformlabel.caption = mysubformtextbox.text but nothing seems to work.
Any help or direction would be greatly appreciated.
I will have a ListBox on the form with “Multi Select” set to either simple or extended. I have a collection of documents which must be recorded in a database. Some documents may have only one author, but could have more. Same with the recipient or copied-to.
Ideally it would be nice to have the ListBox on the left, selected one or more from the list and then pass them to one of the three fields by a command button, sort of like:
I would like to have all of the names selected from the ListBox as a string, but fully understand the problems associated with normalization. Other posts have suggested subforms. So, the question is how to select from the ListBox and then pass the possible multiple selections to discrete fields on a subform that would display, say three fields and which would get away from the normalization issues:
The secondary issue will be that there will be subsequent names which are not found until actually in the process of document review which will create a need to update the ListBox, which I know is not akin to a ComboBox NotInList function.
If there are any thoughts or coding out there which will help, it’d be appreciated. Lawguy
Hi--I am working on a database which is a modification of the following MS Access 2003 Order Management template: (URL address blocked: See forum rules)=CT101426031033
In my database, I have a subform called sbfOrderDetails, which in its Form Footer has a text box that sums the items in the order and is called OrderDetailsTotal.
I am trying to pass that value to a text box in the main form called OrderSubtotal.
I have three questions:
1) What is the proper syntax to use to get the value of the subform control into the OrderSubtotal control on the main form? I have tried various permutations and continue to get a #Name? error.
2) If you look at the expression in the Order Subtotal control of the Add and Manage Orders form in the original Order Management template, it looks like this:
=[Order Details Subform].Form!OrderDetailsTotal
When I try to replicate that in my database, I cannot--if I add brackets around my subform name and press Enter, Access adds brackets around everything in the expression, i.e. =[sbfOrderDetails].[Form]![OrderDetailsTotal] (which returns a #Name? error). If I edit out the brackets I don't want and then press Enter, it puts the brackets back in. In other words, Access keeps trying to "fix" my expression by either having brackets around each item, or no brackets at all! This is really annoying; is there any way to turn it off??
3) When sbfOrderDetails is set to Single Form view, I can see the Form Footer (and the value in the OrderDetailsTotal text box as well). When it is set to Datasheet view, I cannot. Why?
Thank you very much, lcnlit
PS>I cannot attach my database since I am a new poster, and the forum is disabling the URL of the Microsoft template as well.
i have a main form with three sub forms on it. when i update subform 1 i want subform 2 and 3 to update..currently i have an after update event in a combo box subform 1
How can I pass two (2) values to a saved query ? These values are in a form that has a listbox with two (2) columns. The name of the form is 'Previous Evaluation Form'. I'm able to retrieve the values from both columns of the listbox in the form and I've already created the query. Both are working fine, but can figure out how to pass the query's criteria to select records for 'Name' and 'Date' columns of the query. Below is what I had in the 'Criteria:' of the query. What wrong with the code that is placed in the query's 'Criteria:' ?
I would like passing values from first form until third form.
In the first form I have a list box after selecting items (For each selected item in first form I have 4 values) and pressing button (or right click of mouse) the second form will be open, then in the second form I have 2 option (inserting, deleting), when I select inserting or deleting in the second form, third form will be open, in the third form there is a "OK" button, when I press that, passed values from first form will be used for inserting or deleting records to the table.
I want to check a listbox value (the listbox is in the parent form) after updating a subform field to see if the listbox value is greater than 8.5 after updating. If it is, I want an error message to pop up and I want the field that the user is on cleared.
I think I might be referencing the control from the subform incorrectly:
This may be more a VBA question than a forms question.
I have a main form and a subform. The suform contains several fields and there are several records on it for any given record on the main form.
I also have a command button on the main form that creates a new record. I would like to have the default records to disply be based on the values from the previous subform.
I don't know if that is clear. An example the main form would contain a location and date and the subform would have sales figures for Tom, Jane and George for items a, b, and c. Tom, Jane and George would be records and a, b and c would be fields.
Generally a weeks worth of data would be entered and generally new values would be added for the same group of records from one day to the next for any given week, however, there might be times when more or less records were entered.
I would like to set it up so that the subform is populated with the same records as are in the subform before the command button is pressed to create a new record on the main form. For instance if on day one items sold are entered for Tom and Jane when a new record is created it would display Tom and Jane on the subform with blank values for items sold.
I thought I could use min or max to get a default value, but, the table that the values go into is by date so max might get the value for Tom but not Jane and George (I think).
I have this problem. I've made a table called "producers" and a main form based on this table. I've also made a table called "orders", with several data regarding orders, and a secondary table based on the "orders" table. These tables are linked. So these forms show the orders made by each producers. So, I want to show in the textbox "total" (in the footer of the sub form) the result of this function sum([kath_dik])-sum([pistosi]) for each company. [kath_dik] and [pistosi] are text boxes in the sub-form. (scroll the sub form to the right end) I tried the formula above end get an error message. Thanx in advance
Hi guys, Im working on a DatABase at the mo that is heavily featured around forms - the problem is I dont have a clue about how the code side of it is structured in access. I know a lot about visual basic, and I've been having pot shots at how to do this, but with no success...
How do I read the value of an object from a subform with macros? Lets say on my main form I have a textbox called 'ExampleTXT' - to read a value from the text box would be simple...
Code:msgbox Me.ExampleTXT
but on this form I also have a subform, which contains an array of textboxes (they link to a seperate table, so there can be any number of these textboxes) and they have the name 'ExampleARR'. How do I read the values from these? I have tried the following code (like I siad, these are random stabs in the dark) but I have had no success...
I have a sub for which is set to sort by ascending order for a given field which happens to be a text field with vales set something like abc1.1,abc1.2 ect. The problem is, is that when you get to abc1.10, and more these are then sorted as so abc1.1 abc1.10 abc1.11 abc1.2 abc1.3 ect
How can I stop this and sort it: abc1.1 abc1.2 abc1.3 ....... abc1.10 abc1.11
I'm having problems trying to control default values on a continuous subform. I need to be able to have the continuous subform look up values based on a CBO on the subform, populate controls on the subform with those values, and then store the values in a table that has a foreign key linked to a control on the main form. I'm not a complete newbie at this, but for some reason I can't figure this out. The closest I've gotten to this was using a Dlookup in the afterupdate of the CBO, but that only returns one value from the table regardless of what value is selected in the control. Any suggestions?
Hello, I'd like to know how to set values to a subform when opening a main form I have a quotation form called FQuotation with a subform on it. This subform is the link between the products and quotation it is called SFProdQuote and has the fields: refproduct, quantity, quote and PQTotal. Once I have filled the info on the main form i pick the products on the subform using a combo box on the refproduct field, input the quantity and give it a quote. All this works fine it calculates quantity x quote = total.
My issue is I'd like to create a quotation form that opens with a set of products already selected in the subform. let say 10 of them, since my quotations always start with the same 10 products or so. I do not want the users to have to select over and over the same things.
Is it possible to open a form with set values selected in the combox of its subform?
1) show the number of records in the subform on the main form 2) count the number of records in the subform where a value [Public] is True 3) count the number of records in the subform where another value [Analyst] is True.
I can achieve the first two by using the following VBA on the Main form current event -
Quote:
Private Sub Form_Current() Dim rst As DAO.Recordset Dim lngCount As Long
[Code]....
when i try to get number 3 done I get the same value as for [Public] (using lngCount for both...not surprising really!)
how i can get a count done for [Analyst] = True in the same event?
I am fairly new to using access and am currently creating a database for a small bakery. They have new legislation whereby they have to list all their product ingredients and any allergens in the ingredients must be shown in bold.
one to input ingredients and a checkbox if it is an allergen
The other has a main form and subform to get ProductName, Photo and then a combo box in the subform to add ingredients.
Everything appears to be working ok but I have an issue with my subform. The combo box has no duplicates and lists the ingredients in alphabetical order as I require. However, if I view the subform separately from the main form I see that it is storing all the ingredients selected for each product. Therefore it is storing a lot of duplicate information.
