Forms :: Requerying Values In A Subform After Updating Combo Box In Main Form
Apr 21, 2015
Within my main form I have a combo box called "workgroup." Also in the main form is a subform called "sfrmSubmissionRecords," and within this subform is a combo box called "covermemo."
Each cover memo is assigned to a specific workgroup, so my intention is for each time a new workgroup is selected from the dropdown, the covermemo combo box in the subform becomes populated only with the covermemos associated with that workgroup. I'm almost finished except for the final step when I try to make the values regenerate...Access says that can't find the referenced form "sfrmSubmissionRecords"
This is the code that I'm using:
Private Sub cmbWorkgroup_AfterUpdate()
Forms![sfrmSubmissionRecords]![cmbCovermemo].Requery
End Sub
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...
I have a Suppliers database which contains a form that will allow me to place orders with Suppliers.The Main form has a combo box that allows me to select the supplier. The combo box is called SupplierID with the following:
Row source: SELECT Suppliers.SupplierID, Suppliers.CompanyName FROM Suppliers ORDER BY Suppliers.CompanyName;
The subform is called Stock Subform witha combo box called ProductID with the following:
Row source: SELECT DISTINCT Products.ProductID, Products.ProductName, Suppliers.CompanyName, Products.Discontinued FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID=Products.SupplierID WHERE (((Products.Discontinued)=0)) ORDER BY Products.ProductName; Event Procedure - AfterUpdate: Private Sub ProductID_AfterUpdate() On Error GoTo Err_ProductID_AfterUpdate Dim strFilter As String ' Evaluate filter before it's passed to DLookup function. strFilter = "ProductID = " & Me!ProductID
[code]..
The Link fields are done on the Purchase Order ID (PONoID).What I want to achieve is to select the supplier from the combo box (SupplierID) on the main form and then the combo box (ProductID) on the subform to filter to only show products directly supplied by the Supplier selected on the Main Form.
I'm trying to create a record for a table that has 2 attributes:
ContainsTracks +album +tracks
I have my main form, where the album is determined by a TextBox. My subform has a textbox that lists the tracks (in datasheet view). I have this set up and working fine.At the moment if I try to add a new record by typing a new entry into the subform I get the error: "Index or primary key can not contain a null value". This is because both album and tracks make the composite primary key of ContainsTracks.
My question is, how can I tell the subform to grab the value from TextBox1 in the main form as the +album when I create a new record.I have provided two screenshot that perhaps explain my predicament a bit better
I have a form, a subform and a master unique table. I need the unique table to updated in the following manner: Form has certain fields that need to be assigned to every record created in the subform[id] and [info1] Subform has a unique [caseid], and contains further [info2] but needs to be bound into [id] in the main form. Example of the master unique table:
I've managed to accomplish this with a linked table&subform structure, but the master unique table looks like crap because it collapses the subform values into sub-records in the master table when the [id] is similar. The data is in the rigth place, i just want it not to be collapsed/expanded, but every unique recors shows in the table in the regular format
I am having a problem with calculated fields that are populated on the main form from the subform not saving to the table the main form is linked to.
I created a Purchase Order table that has information on what emplyee created the record, reason for order, customer information if its ordered for a customer, creation date, eta date and notes while the Purchase Order details table is for information on the supplier, product item number, product name, quantity and cost. they are linked by the Purchase Order number which is the primary key in the Purchase order table. it all looked good so i made the form with the Purchase Order Detail as the subform, i got the form all working great and how i wanted it but then i decided i wanted the supplier name and the subtotal of the cost in the main form populated or calculated by the values in the subform.
I copied the fields i wanted populated in the main form to the subform footer and added the calculation for the subtotal then added the formula to the source code on those fields in the main form so the values would populate. it all worked great on the form and the values populated as they were supposed to so i saved the record and went to look at the tables and i found that while the values in the calculated fields that I populated from the subform showed up in the form they didnt save to the table while the non calculated fields saved fine. i did some experimenting and found that if i delete the code and put the data source back to the table in the form and just type something in the text box it would save to the table but not if i had the code in and let it populate.
an example of one of the codes i used to populate the data in the main form is:
productId ( which is filtered from combo boxes on main form) . productId contains two columns 1 bound column(id) ( hidden with width 0 ) 2 column (desription) ( shown with width 2 )
Now after entering the data when i come back to the form again . It hides the data from the productId field on subform , because data in combo box (on main form) doesn't match. Although the row is showing up , only the text in field (productId) is hidden.
Now when i change data in combo box on main form to match the ProductId , then the productId field text shows again. after changing combo box it hides . What i need is the existing data should not hide when i change the combo box selection.
see pic 1 where product matches and pic 2 where not. please note i am using master child relationship on the form/subform.
Hi, Mainform and Subform are linked on field "barcode".
Barcode is the primary key for the table that Mainform is based on.
When user is entering a new record, I have the BeforeUpdate event of Mainform.barcode set to check to see if the barcode already exists in the table, and if so, to give the user some meaningful error message.
Works fine, except that the subform still updates to match the invalid, previously-used barcode. I want the subform to stay blank until the user has entered valid data in MainForm.
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'm supporting some Access/VBA code which includes the following behavior:
When an update is made in a (linked) child form, the current date/time is written to a textbox on the main form via
Me.Parent!txtMod = Now
Likewise, the BeforeUpdate event of the main form updates the same field (and then does a bunch of form validity checks, possibly followed by a Save).
When we upgraded to Access 2003 from Access 2000, a problem erupted:
When the user returns to the main form (by clicking on any control outside the subform), the following message is issued: "This record has been changed by another user since you started editing it (etc.)..."
At this point the user must choose between "Save Record", "Copy to Clipboard" and "Drop Changes".
Conceptually, I see why this is happening (though I wonder why Access 2000 didn't flag it). The subform is the "other user". When the main form is returned to, it is "Dirty" (unsaved update), so the main form's BeforeUpdate event fires, and attempts to update the field again (Me.txtMod = Now). Since there is a pending change to the field, an error dialog pops up.
As a quick fix, I replaced the code in the subform with a flag ("Hey, I've been updated!"), which the main form checks on the Exit event of the subform control. But that seems a horrible solution. Surely there's a better way?
Notes:
1. It would be preferable to have the timestamp updated right away by the subform, though having it updated when the subform loses focus is better than nothing.
2. Since a subform is a control from the main form's perspective, I'm surprised that a change to that control doesn't render the main form "Dirty". I guess from a database standpoint it makes sense (the form's table isn't getting updated at all; just the subform's). But then, how does the main form know that the subform has been updated?
3. I tried catching the error via Form_Error. I can make the dialog not appear via "Response = acDataErrContinue", but unfortunately it defaults to dropping the changes (old and new), rather than saving the new.
I am having trouble requerying a combo box.I have created a Drawing Register database which allows users to create a new job as well as set up a drawing register for that job if the use checks a box in the job set-up form which indicates that particular job will have a register.Using the Job-Set up form I created a query which brings pulls the job number, job name and register check box and filtered it to show only those jobs that the box is checked. To this query I also added the relevant fields from the drawing register table. The Job Set Up table is linked to the Drawing Register table by the Contract No. field. Unfortunately I cannot set this field in the Job Set Up table.
Using this query I created a Drawing Register form with the sub-form Drawing Register Details. I added a combo box which shows the lists of all the drawings and allows me to view a specific drawing when selected. The problem I am getting is that the list is showing ALL drawings for EVERY job with a register and not just those drawings for the job I am currently updating. I tried using the following code with no luck
Private Sub Form_Load() Forms![DwgRegFRM].Form![Combo15].Requery End Sub
Private Sub Combo15_GotFocus() [Combo15].Requery End Sub
I want that when I choose to open the register for a particular job the combo box only lists the drawings for that particular job.
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?
I have a form with a subform that contains a combobox where I choose companies to add to a project that is on the main form. If the user types in a company name that is not in the database, I run code on the NotInList event that passes the company name using openargs to a company entry form.
After this form is closed, I return to the subform to choose the company name that I just added, only it is not there because I can't figure out where to automatically requery this combobox. My duct tape solution that's working, is a command button that runs this code when clicked:
I have tried putting this code in the OnCurrent, OnFocus, Onclick, OnEnter....etc on the subform and on the combobox itself. So far the only way it requeries is if I use the command button.if you want to see the NotInList code, and the code on my company entry form.
Hi every body . I got a a form and sub form. I want to disable sub form untill the user finishes selecting values from combo box drop downboxes. I be happy if some one show me how to do this.
when I open my Quotations form I'd like a set of services to be selected by default, ex: Brush and Pen. So the user only has to input the quote amount
is there anyway to have the form open with a script like: "on open (or on load). in subform SELECT refproduct where product from tblproducts = 1 and also SELECT refproduct where product from tblproducts = 4" Therefore when my main form opens, in the subform I have already Brush on the 1st line selected and pen on the 2nd. My user only has to type the price.
I have a main form with a subform. On my main form, I have a combo box set to "find a record on my subform, based on what I select in the combo box." This works great! At the same time, I want to use this main form combo box to synchronize with a combo box in the subform to display a value based upon the value selected in the main form's combo box. I have tried several things and searched Access newsgroups without success. I am using MS Access 2002, producing an Access 2000 database. Has anyone got any ideas? I could really use some help. Thank-You.
I have created a form "PAYMENTS" that include a sub form "PAYMENT DETAILS".
In the sub form control Policy Id is defined as combo box, and retrieving the list from Policies and Payments where Policies.Client=Payments.Client the query of Combo Box is as follows:
Code:
SELECT POLICIES.[POLICY ID] FROM POLICIES WHERE (((POLICIES.PAID)=False) AND ((POLICIES.CLIENT)=[Forms]![PAYMENTS]![ClientId]));
Only first time Combo Box display the Policy Ids according to the Client, but when I change the client and always displays the previous clients Policy Ids.
how to retrieve correct list of Policy Ids according to the Input Client in Payments.Client control
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 developed a database but have had difficulty with sorting data within subform of a main form. The subform displays the related tasks that correspond with main form that has been selected. Within the main form I have also created a text field that defines a particular sorting sequence of the tasks found within the subform which is titled Task Sequence. What I would like to do, is use the Task Sequence field to sort the order of tasks within subform. I have tried: IIf([ID] Is Null, 0, Val([Task Sequence])) within the Advanced filter/sort but either it shows only the first task defined in the Task Sequence or it wants to filter the main form and not the subform.
Any easiest solution so that After I Update the field called JobStatus in the subform, it changes the field called JobStatus in the main form to the value which was selected from the subform?.
I am stuck trying to figure out this problem. I have a main form "frm_tirelog_600" which has 4 combo boxes "cboleg", "cbocar", "cbopos", "cboserial" on it. The first 3 combo boxes are used as criteria on 1 of 3 subforms that I have. The fourth combo box "cboserial" is used for the criteria in a query on another subform that I have called "frm_mount_600_subform", which is independant from the main form, I hope I have explained that clearly enough. What I need to be able to do is have the subform fill in 3 of the fields on it "leg", "car", "pos" with the value from the 3 combo boxes on my main form. I can get it to show in the fields but not write to the table. I have searched the forum and have not been able to locate anything that would work and really need any assistance with this. Im not real strong in the coding department which is where I beleive this could be done.
I have attached a copy of my DB which I hope will better explain it. Any help would be greatly appreciated
i have a main form for customers and a subform for sites which is set to locked so its displayed as just a list. I have a button on my main form to add a new site for the current customer displayed..How can i get the Main form '1CustomerDetails' with subform '2SiteList' to automatically refresh one i've clicked the save button on another form.
I have a main form populated by Dlookups.When I click on a textbox I open another form which is used to change data in my main table. Once this is closed the main form is still displayed but the amended data isnt reflected in this form. How do i get the data to change, I have tried requerying the form and the query that is used for the dlookups but I cant get it to work.
I have this form that contains subforms to hold different types of infomation for different clients. At the top of the form it has the case #, first name, last name, and then a tab element in which each of 6 pages (tabs) holds subforms for other information associated with the client.
I have a table that holds only client number and name.Then each of the six subforms operate off of a table, and all the tables are connected through relationships using client number, first name, and last name.
How could I make the subforms autofill their cooresponding tables using the case #, and name fields from the main form without having to repeatedly input the client #, names for each tab/subform?
I have a form that shows information on a person (think first name and last name) at the top and contains tab controls that hold subforms each containing a different set of info about that particular person. Ex: for person 1 the main form will display the persons first and last name as well as show the subject tabs. as you click on each tab you should see the information that pertains to that person. I would like to step through each ppeson one at a time using the horizontal record arrows (eventually I will change this process to using forward and back buttons) on the main form with the persons names changing as well as the information in the subforms changing to match the person on the main form. I can step through each separately (main form and each subform) but I cant get the subforms to change information when the main form changes persons. Seems simple enough but I cant get the subform to change when the main form changes. They are not connected. This is for an Access class that I am taking and we use Access 2013.
