I am building a debt management program in A2k. I have created a tabbed control which contains 6 pages based on a set of queries. These pages are a budget template and the intention is that when this form is inserted as a subform into the customers form the budget will open as a blank template with the budget categories displayed. The clients budget will then be entered and saved. The Budget table contains 56 budget categories.
At the moment when I insert the budget form and use it the only records that change are the template. I want the first 56 records to remain with zero values so that they display as blank for a new client but I want the total 56 new records to have the customer ID associated so that when that client record is accessed again their budget will appear. I have attached the SQL code for the query that displays a sub portion of the budget.
SELECT Budget.BudgetID, Budget.ClientID, Budget.SectionNumber, Budget.BudgetTitlesID, Budget.Description, Budget.Cash, Budget.Credit, Budget.Frequency, IIf([frequency]=1,([cash]*52/12),IIf([frequency]=2,([cash]*26/12),IIf([frequency]=3,([cash]),IIf([frequency]=4,([cash]/12),0)))) AS Totalscash, IIf([frequency]=1,([credit]*52/12),IIf([frequency]=2,([credit]*26/12),IIf([frequency]=3,([credit]),IIf([frequency]=4,([credit]/12),0)))) AS Totalcredit
FROM Budget
WHERE (((Budget.ClientID)=[forms]![customers].[clientid]) AND ((Budget.BudgetTitlesID)=1)) OR (((Budget.ClientID) Is Null) AND ((Budget.BudgetTitlesID)=1));
My thought was to do an after update event that would copy the clientID from the main form to the sub form and populate all the records in the budget. However, I have only succeeded in populating the individual record that has changed.
I probably haven't explained the problem sufficiently to get some help but would appreciate any thoughts.
I have an orders form and subform, ie one order can have several product records with the following firlds Product Ordered Received At the bottom I have a command button 'ReceiveOrder' What I want is on click of this button that the received field (that is 0 by default) will equal Ordered. ie If I had orderd 2 and I hit the button it would change the recieved to 2 as well. This much I have done with the following code Private Sub ReceiveButton_Click() Me.frmReceivingSubform.Form!QtyReceived = Me.frmReceivingSubform.Form!QtyOrdered End Sub
This work fine if there is only one product, but if I have more than one it will only update the selected record, is there an ammendment I can do that means all the product records on that particular order will update.
I've searched the forum for the answers but can't find the answer, can anyone help, Thanks
I have a form that contains a subform. I want to make a button that on click updates all the records listed in the subform. This is the best I came up with.
Dim rs As DAO.Recordset Set rs = Me.SubList_for_Billing_Center_Form.Form.RecordsetC lone With rs .MoveFirst Do While Not .EOF .Edit UPDATE Billing SET Billing.Billing_Declined = True, Billing.Billing_Declined_Date = Date()
My subform consists of a list of tasks that are waiting to be verified. in order to verify tasks, the user scrolls through the list of tasks and checks a checkbox (discrepancyverified) on each record they wish to verify. After the user has finished checking all the records they wish to verify, they click a verify button on the main form which should then go back through each record and update the verifieddate value of any that are checked to today.
This is what I have so far:
Code:
Private Sub Command19_Click() Dim db As DAO.Database Dim rs As DAO.Recordset Dim ctl As Control Dim varItem As Variant
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
I have got a query that updates details from one table2 to table1, "Reference" is the primary key and this is what the query uses to determine which need updating.
It all works great but if table2 contains a record in "Reference" that is not in table1 i just want it to ignore it, currently it just seeems to add them.
In my simple database (attached), I need to mass duplicate Tasks and their Notes.
I have three tables: tbTasks (PK: Task_ID), tbNotes (PK: Note_ID), jtbTaskNotes (FKs: Task_ID and Note_ID). jtbTaskNotes is my many-to-many junction table that ties Tasks to Notes.
The main form (fmTasks), bound to tbTasks, has a subform (sbfm_TaskNotes) that displays notes associated with each Task. On themain form,you select which Tasks you want duplicated via a checkbox. The append query (quCopyTasks) will duplicate all tasks that have the checkbox checked. All good there. However, I can't figure out how to also duplicate each task's Notes.
I found Allen Browne's solution [URL] ....., but that only handles duplication of one record at a time, whereas I need to duplicate many records at a time (sometimes 10+ records). How do I go about duplicating multiple Tasks and their associated Notes?
Before you ask "why are you duplicating records?": There are times when tasks need to be re-accomplished and therefore need to have a new record. It's easier to duplicate records than it is to hand-jam everything again.
I have a table called tblCompanies. When a company acquires another company, I need a method by which the acquired company's CompanyID (PK) can be updated to the new company's CompanyID (PK). I also need to be able to update all related CompanyIDs (FKs) to the new value in related tables.
In cases in which the new company does not have an existing record, there is no problem: the company name simply gets changed to the new company and the existing CompanyID is maintained. I then use an audit table and Track Changes function to keep track of the company name data and a union query to keep the old names in the selection lists.
The problem is when both companies already have existing records in the table.
So, let's say I have records for Company A and Company B. Company A merges with Company B and Company B is now the main record. What is the best, simplest and easiest way to update the CompanyID (PK) from A to B and change the CompanyID (FK) to the new value in all related tables?
I am envisioning a pop-up form that directs the user to select the new company and then an update query happens behind the scenes... but exactly how does the criteria for the update query get selected and how do all the related tables get updated? My vba skills are pretty basic, will I need extensive coding to do something like this?
I moved this to it's own thread since it wasn't the same question as was posed by the thread creator. Pat
Not really the same issue but I thought to save us all from another thread...I have three subforms on a main form which are linked master/child. When I go through records on my first sub other two should be showing the additional info. I'm using requery as an form_current sub and works well with one sub, but doesn't work right with the other. The thing is that it shows the data on the form only if there is some data in the table already, but not if you want to add some new.
this is the code: ------------------ Private Sub Form_Current() Dim ParentDocName As String
On Error Resume Next ParentDocName = Me.Parent.Name
If Err <> 0 Then GoTo Form_Current_Exit Else On Error GoTo Form_Current_Err Me.Parent![tblInfoSub].Requery Me.Parent![tblInfo2Sub].Requery Me.Parent![tblKategorije subform].Requery End If -----------------------------------
since I am actually getting requery to work...what else might be the issue
I have a subform which asks a user to confirm the update before it happens - if the user selects no - then the update is cancelled.
When the parent (they are not related record wise) form is closed the update on the subform is attempted - if the user selects no - how can I cancel the close of the parent form. At the moment a message saying "Cannot save the record at this time - changes may be lost". Which I don't want to see.
Hello all! I'm using a form in data entry mode to add new orders into a table. When the Save button is pressed, an append query posts the order number and date to another table, and then enables a subform for data in that table. The subform is linked to the main form by the order and date to get only the record that was just entered/created.
While the subform does display the desired record, the user cannot update other fields in that table through the subform. I've searched all over the forums, and I'm probably missing something really simple. I've tried turning data entry on and off, changing different subform properties but to no avail. I can filter out the proper record on the subform, I just can't update it! Any help would be much appreciated. Thanks!
I have a form called NewRequisitions that had a combobox in which the users select the Supplier (name, address, shipping details). I created a button to open a subform called NewSupplier so that they can add a new supplier to the Supplier table as needed. My code works okay for the first "NewSupplier" entry - the table and combobox are both updated and the new supplier is available for selection in the dropdown list. However, when there is a 2nd record that needs a NewSupplier added the combobox list doesn't get refreshed. The record is added into the table okay but you can only view it if the NewRequisitions (main) form is closed and then reopened.
Here is the code in my subform:
Private Sub Form_Close()
If CurrentProject.AllForms(NewRequisitions).IsLoaded Then
'save the record if there have been any changes If Me.Dirty Then Me.Dirty = False
'requery combobox on other form Forms!NewRequisitions!ComboSupplier.ReQuery
'yield to other events - and put the new list into use DoEvents
I wonder if anyone could offer some advice on a problem with a form & subform?
I have a main form, based on table PublicationOrders: POrderID (pk) EventID(fk)
And a subform, EventDisplay, based on table Events: EventID (pk) EventName
The main form and subform are linked on the field EventID and my master and child links are correct, as far as I can tell.
The field EventID on the main form is a combobox, bound to field EventID in the table PublicationOrders. It allows the user to select which event an order is being sent to. In the Afterupdate property of this combobox, I have the code: Forms!PublicationOrders!EventDisplay.Requery
I would like the subform to update to show the details of the event that has just been selected from the combobox. However, for some reason, on afterupdate, the subform displays whichever event was selected when the previous record was entered. If I close and reopen the form, it will right itself and show the correct event information that corresponds with the order.
Any ideas why it's doing this? I have searched here for a solution but can't seem to find anything that relates.
Hi everyone. This probably sounds simple, but I cannot find the answer here, google, or my books (don't know if I'm searching the right words). I have a main form with five parmeter values, any of these acn be filled in, to filter the results in a query. I created a subform to display these results, however I cannot get the results to come up in the subform inside the main form. All I can get is the subform opening up in its own window, and even when this happens the subform inside the mainform stays the same. The only way I get the subform inside to update is by going into design mode and then back into formview mode. What have I missed or what am I doing wrong?
Hi got a good help with this and it's near a solution. I attached a form that has checkboxes to show and hide fields. This is a subform in a search form and bound to ID. So if a person is checked for "eget initiativ" it sticks to him/her. but the problem is that this should hide 2 textboxes, but if a person before has the bos checked for showing them, they doesn't hide when I filter the next person that has "eget initiativ check that's supposed to hide them.
If you uncheck it manually and check it again, they disapear. But that's not good enough. Any suggestions?
I have a private sub on a subform called txtsearchstring_change.
The txtsearchstring box is = to the client_id on the main form.
Basically when the user clicks on the next record button (which is on the main form) it should update the txtsearchstring_change on the subform and then display the new results in the list box.
Im not to sure how to do this. I understand im calling a sub from another form, so i did try a public not a private sub. And i have added on the next and previous button - txtsearchstring_change.
I have a form that has a related pop up from. I have an IF/Then/else statment. I want to up date a field in the orginal form from that if/then/else statement. I can get my IF/Then/Else statement to work on the subform but I can get it to update the orginal form. I might have a problem with my forms![xxxx]![yyyyy] syntax.
This is so simple but I can't seem to make it work. I've poured through about 30 posts and tried all kinds of AfterUpdate event strings to no avail. Help!
Here is the simple setup. I have one table that lists employees and their corresponding teamleader. A grouped query provides the record source for a combo box. The idea here is that you select a teamleader from the combo box and a subform updates to show all the corresponding employees. This subform is based on a simple query of all fields on the table.
After a scary recovery I have a new problem: Intially I changed the 'Data Entry' property on a subform, in a multi-tab form :eek: My tabs all dissappeared. (My Bad!:o ) Now that I have recovered I get a message when I close the form, asking do I want to save the changes to the design of the query. This only seems to happen when I have read a second or third etc record that displays a different set of records in the subform. This is a HR mdb with leave records in the subform. Any help would be appreciated. PS I thought I had posted this 2 hrs earlier, but can't seem to find it. Hoping this isn't a second posting ...:confused:
Hi, I am trying to update a subform by selecting a record in a combo-box that corresponds to a particular test when i select the said test i want an adjacent subform to change to the form displaying the test data in the subform window i have tried to use VBA to achieve this but so far have been unsucessful does anybody know how this can be done?
I'm trying to pass a value from my main form to my subform for the related records when the user clicks a button. The problem is that it either wants to update the entire table or can't "see" the value on the main form.
Here is my code
DoCmd.RunSQL "UPDATE [tbl_req_order] SET [process_status_rec_id] = [req_process_status_rec_id] WHERE req_rec_id = Me!req_rec_id;"
I'm a novice, so please bear with me. It works, but it always asks me what the value of Me!req_rec_id is. That's the primary key in the parent table that is linked to the subform. How can I tell it to use the value for the form the user is on?
I want to limit the values in a subform using a combo box. Basically I've got a Main Form that has name field etc. Selecting a different record shows me a different entry on the subform relating to a different name but now I want to use a combo box to limit what I see in the sub form another level. Any help would be wonderful.
I would like the subform in the lower left corner to be blank when I open the program. It is populated from my tblClients table. How it works in general is you select "x" number of items from the list box...hit the search button and the results go through a query and then show up in the subform. everything works ok but, like I stated I would like the subform to be blank when the program is first started up. It seems to be retaining information from the last start up in the subform box.
Thanks for your help...it's coming along great so far... see attached doc...
