Main form update to subform value Results

Hi,

Im having an issue that I'm hoping somebody could help with?

I have a main form called frmDetails, on this form there are a number of textfields, with one in particular that I use quite a bit, this is called Total, with adds values together from the other textfields and displays the total.

I also have a sub form on the frmDetails, called SubDetails which is based on a query., the query allows me to enter a code on frmDetails and populates the form and subform with the data with the matching code. so for example if i use code 321, it brings back all data related to that code (which is unique)


On the subform I need to divide the Total from the main form and display the answer in the sub form subTotal, I can get this to work one row at a time but the OnClick event in the SubTotal textfield event.

But I need to allow the subform to update all rows under the column SubTotal at the same time depending on the quantity in the quantity column. this is the code i have at the moment for the OnClick event:


	Code:
	me.SubTotal = Forms!frmDetails!Total / me.Quantity

I've tried inserting the calculation on the OnExit event for Total (frmDetails) but again this only updates row 1 instead of all rows. My subform is as follows:

	Code:
	ID | Quantity | SubTotal | Cost
1      1200                 15
2      1300                 16
3      1400                 17

Can anybody help me?

Hello, working in access 2003, been looking at various solutions for this problem, but can't seem to get any of them to work.
I have a main form EmployeeTask where the user picks an employee, a task, and some other fields from comboboxes. Then the user can update or insert these fields into the Tasks table. But what I also need to be able to do is show the user the tasks for the employee that he picks that are currently in the Tasks table- i.e. prior to his update. I tried setting up a button whose on-click event would populate a textbox control source with the fields of a select statement whose parameters are the values the user picks from the form comboboxes. This doesn't work- gives me an object required error. So I'm sure that a textbox would not accomplish what I need here- unless I'm doing it totally wrong.

Dlookup doesn't work for me because I need all the fields displayed, not just one.

I am looking into setting up a subform based on the parameter query, but not sure how to do that either. Which property of the subform do I put the parameter sql statement into?

I would appreciate any help on this. I don't need any code written for me- if someone could just set me on my way to the best way to accomplish this, that would be awesome.

Thank you in advance and happy holidays!

I am running Access 2003 and need some advice regarding a continuous subform. The main form is unbound and the continuous subform is bound to a temporary table. When the user clicks the "submit" button, the code writes the data to a "permanent" table. There are times, however, when there will be no data on the subform, so as a work around, I am using an If IsNull....Then statement:


	Code:
	    If IsNull(Forms![frmProblemInvestigation]!subfrmPITempCM.Form!TempCM.Value) Then
        'Do Nothing
        Else
        HoldPITempRS.MoveFirst
        Do While Not HoldPITempRS.EOF
            PITempRS.AddNew
            PITempRS.Fields("ProblemInvestigationID").Value = HoldProblemInvestigationID
            PITempRS.Fields("TempCM").Value = HoldPITempRS!TempCM
            PITempRS.Fields("Responsible").Value = HoldPITempRS!Responsible
            PITempRS.Fields("TargetDate").Value = HoldPITempRS!TargetDate
            PITempRS.Fields("ActualDate").Value = HoldPITempRS!ActualDate
            PITempRS.Update
        HoldPITempRS.MoveNext
        Loop
        End If
        PITempRS.Close

However, and I suppose this is due to the nature of subforms, the code sometimes "skips" when the value in the TempCM field is NOT null. Does anyone have any advice and or a work around to this issue? Thank you.

Hello,

I have searched for quite a while for an answer to this question, but no luck yet.

I have two tables, one identifies open items and one identifies section numbers of an industry code document. A third table links those two together, so an open item [ID] can be linked to a specific [CodeSection].

I want to be able to assign or unassign a [CodeSection] to an [ID] on the fly using a form. What I envision is a user looking at a ComboBox next to a ListBox, with two buttons in the middle. If the user selects a [CodeSection] from the ComboBox and clicks an "add" button, that value will appear in the ListBox, and the table linking the two values will be updated. Likewise, selecting a [CodeSection] in the ListBox and pressing a "remove" button will put the value back into the ComboBox. I want to call up this form as a popup from another main form that will be displaying a unique [ID], such that when this new form opens only the data for that [ID] will be shown/manipulated.

Here's what I don't know and can't figure out:
1) What should the form's record source be? Or should it be unbound?
2) Should the ListBox be bound to something? Or unbound? I've successfully gotten the ListBox to show the values that I manually type into the table, I just don't know how to get it to work in form space.

I've done this successfully before by putting the listbox on one form and the combobox on a subform that is a sub to the form with the listbox, but it seems to me there should be a much simpler solution in this case.

Any info would be much appreciated! Thanks!

OK,

So I have a 3 Tables, Events, People, PeopleAtEvents

I have a form to create people(works great)
I have forms to create Events - formCreateEvent, On this form I have a subform so that you can add which people attended this event- subformPeopleAtEv

If the event is a certain distance(like over 50 miles away) it is called a long distance trip, where the member will get paid extra (Perdiem). To get paid extra they have to do travel Vouchers....

So on the formCreateEvent I have a checkbox to select Long distance or not, if it is longdistance it brings up(makes visible) a combo box, as well as 2 things in the Subform... (DTS_complete, and DTS_comments).

My problem is, on the DTS complete I have 3 available options on the combobox.... N/A (this is the Default and what I want to be stored in the event that the trip is not long distance).... COMPLETE and NOT COMPLETE... so i can later run reports to see if the members have completed thier paperwork to get paid.

What I want to happen is, if the trip is not longdistance, then DTS_Complete = N/A and not visible... (I got the visible/notvisible part to work.

The problem I have is when I add more than 1 person (I click next record on the subform) to the event. and I make a change, then decide oh wait it wasn't actually a long distnace and go back to uncheck the longdistancetrip checkbox.... the code I have is to set the DTS_Voucher = N/A.... however, it only sets the currently visible record(so which ever person is showing.... ) I need it to be able to set DTS_Voucher = N/A to all the people's records ( on that event). that I add on the subform....

The code I am currenlty using is below.


I have a checkbox with the following code. (also have this code in the Current so when form is opened for updates).


	Code:
	Private Sub LongDistanceTrip_AfterUpdate()
     If Me!LongDistanceTrip = False Then
 
     Me!cboPerdiem.Value = Null
     Me!cboPerdiem.Visible = False
     Me!lblPerdiem.Visible = False
     Me.SubformPeopleatEv!DTS_Complete.Visible = False
     Me.SubformPeopleatEv!DTS_Comments.Visible = False
     Me.SubformPeopleatEv!DTS_Complete = "N/A"
 
    ElseIf Me!LongDistanceTrip = True Then
 
     Me!cboPerdiem.Visible = True
     Me!lblPerdiem.Visible = True
     Me.SubformPeopleatEv!DTS_Complete.Visible = True
     Me.SubformPeopleatEv!DTS_Comments.Visible = True
    End If
End Sub



Hello,
I have to aapologize in adavance. If this is repeat topic- I am sorry- but I haven't been able to find the answer, most likely because I am using the wrong search terms. I am not very adept at creating forms and the form terminology is beyond me.
Here is the problem that I need help with:

I have a Transactions table which holds vendor name, invoice number, and amount.
I have an InvoiceDetails table which holds the details of the invoices.
The two tables have a relationship defined on InvoiceNum.
I set up an Invoices form based on 'select vendor, invoicenum, amount from transactions'. The records in this main form are updatable, as I expect.
I also set up a subform called InvoiceDetails and linked it to the main Invoices form through Vendor Name and InvoiceNum.
Ok, I can scroll through the vendors and the related records in the child form come up.
Now, I want the user to be able to edit the invoice numbers in the main form. I can do that since it's updatable.
What I cannot figure out is how to update all the related records in InvoiceDetails with the updated invoice number. Is this even possible? Or does the user have to go line by line in the InvoiceDetails subform and change the invoice numbers manually?
If this is possible, which events of the InvoiceNum control on the mainform do I have to use? I looked at posts about before and after update events, but I cannot figure out how to reference both the before-update value and the after-update value of the InvoiceNum field so that I can run an update query on the InvoiceDetails table to match what the user has just put into the InvoiceNum field of the main form.

Thank you! I really appreciate any insight or guidance anyone has to offer.

I am posting in desperation, I have scoured Google and multiple forum boards over the last two days looking for a solution to my problem.

Issue: The primary key to my tables is a user's email address. In the user overview Form (similar to a profile page) the user/admin should have the ability to modify the primary key. Whenever this is attempted the following message is displayed -
"Write Conflict:
This record has been changed by another user since you started editing it.
If you save the record, you will overwrite the changes the other user made.

Copying the changes to the clipboard will let you look at the values the
other user entered, and then paste your changes back in if you decide to
make changes. "

Database Setup:
This particular form is a form with multiple subforms which contain aspects of the participant's profile such as addresses and phone numbers. This is setup this way due to the structure of our SQL server tables. Access is one of our two frontend platforms, the other being a web application.
It is possible to change the primary key directly in the linked table and the change is evident through all of the other tables whose primary keys tie to the primary key of this main table.

I have searched Google extensively. Making the following changes and observing the following checks:

- There are no bit datatypes with null values in any tables
- I have experimented with making sure there is a Timestamp datafield in the main table where I am attempting to change the primary key.
- Only attempting to change the primary key generates this "write conflict"
- I have attempted to add "me.dirty = false" into several of the "on update" actions to no avail.
- All tables use a query as their record source.
- A few other odd fixes that did not work.

I hope someone has a resolution for me while I still have hair, many thank yous in advance!

Due to reasons beyond my understanding, I have compiled a Diary-type database for a Counselling clinic. As such, they need to record what the current session number is when that session is booked. However, because this number is pulled from a collated query (where all Attendance records are kept), if I add the Session Number to the Main Booking Form, it will not allow the form to be edited (due to the link with the 'Group By' query for Attendance rates).

To get around this, I have created a Subform that calculates the Session Number for me. It works great. It automatically calculates the Session Number whilst allowing me to edit the Appointment details. But when the Main form saves, it has to save the 'SessionNumber' value from the Subform to the Table that records all the information from the Main form (Client, Counsellor, Date, Time, etc...).

How do I do this?

I've tried:
Me.Subform.FieldName
Form.Subform.FieldName
Forms!Subform.FieldName
and probably more along that line, but none of them work. The save has to run passed an "UPDATE" SQL code because the Counsellor they are booking the appointment with may not necessarily be their assigned Counsellor. The SQL code is currently:


	Code:
	Dim nwNumber As Integer
Dim nwCounsellor As String

Me.frmSessionNumber.Form.Requery

nwNumber = ************
nwCounsellor = Me.ApptWith

DoCmd.SetWarnings False

If Me.Dirty Then
   Me.Dirty = False
   
   strSQL = "UPDATE TBL_Diary_Booking SET [Counsellor] = '" & nwCounsellor & "' , [SessionNumber] = " & nwNumber & ""
   strSQL = strSQL & " WHERE (TBL_Diary_Booking.BookingID = " & Me.BookingID & ");"
   DoCmd.RunSQL strSQL
End If

DoCmd.Close

DoCmd.SetWarnings True

What value do I assign at the above Asterix for 'nwNumber'?

Hi everyone!

I have a combo box in a subform that AutoFills textboxes in the subform.

The subform combo box also has a Dlookup procedure on "after update" event that has a criteria on what the AutoFill procedure will populate based on the mainform textboxes that is AutoFilled by a mainform combo box.

Everything works fine, until I change the value of the Mainform combobox, which changes the value of the textboxes on the mainform from which the Subform combobox gets its criteria for the Dlookup procedure.

My problem is that the Subform combobox does not re-populates the subform textboxes when I change the value of the mainform combobox. I still need to select the same item in the subform combobox list to initiate the AutoFill procedure of the subform textboxes.

I would like to re-run the AutoFill procedure of the subform combobox when I update the main form combobox. I tried the following procedures on "afterupdate" event of mainform combobox, but did not re-populate the subform text boxes.

Forms!MainformName!SubformName!SubformComboBoxName .Requery

Forms!MainformName!SubformName.Requery

Me.SubformName.Requery

Thanks!

My form has a subform called subformDestinations that has one field Destinations. When I type in that field the record that is the record source for the main table gets updated correctly. Outside the subform there is a button to add the new value to a lookup table. I tried the following code in the click event:

Private Sub Command219_Click()
Dim strData As String '' Declare a String Variable
Dim sql As String '' sql String
strData = Me.Destinations
sql = "Insert into LUtblDestinations (Column1,FKID) Values (""" & strData & """);"
DoCmd.SetWarnings False
DoCmd.RunSQL sql
DoCmd.SetWarnings True
End Sub

However, the Me.Destinations causes Compile error: Method or data member not found. How do I refer to the data I want to use to update the other table?

Hi All-

I have researched this and understand I need to change my query critera to account for the subform, but it is still asking for parameter values after this update..

Summary:
I have two Date Picker Boxes on the sub form which tells the query the date ranges which populates a pivot chart. The form works perfectly as a stand alone with the critera of:


	Code:
	 >=[Forms]![frmDateChart]![StartD] And =[Forms]![frmCostTracker]![frmDateChart].[Form]![StartD] And

Explanation:
In Access 2010 I've made a Main form1 with a linked subform1 (the subform1 linked using “Link Child Fields, Link Master Fields”).
In form1 I’m able to select records from a query and display related records in subform1.

My question:
Is it possible to bind a control (Text Box) of form1 to “subform1 field” to be able not only to display*, but also to enter and update values for the records selected in subform1.
Or I can only bind control to fields in the “Record Source” of the Form on which this control (Text Box) resides. In this case I'll need a help finding a solution of my problem:

I'm making a foreign language dictionary where 1 record (selected in form1) has several related records (displayed in subform1) and I need to be able to view and edit this related records enlarged in Text Boxes of main form1 (especially "Memo" fields with several lines of text).

* - I'm able to display subform1 value in form1 control by writing Control Source:

	Code:
	=[subForm1].[Form]![fieldName1]

But in this case I got an alert (error) on the Access status bar:
"Control can't be edited; it's bound to the expression ..."

Hi everyone,

I have a form with tabbed controls: The first tab is the mainform and the second tab is a subform. Mainform and subform are related one-to-many.

I have a button to duplicate the data in the mainform (changing the ID, of course) and it works well BUT automatically a new record is also added in the subform, which I don't want at all.

I am using the code provided here
allenbrowne.com/ser-57.html
but I am NOT using the part related to the subform.

---------
Private Sub cmdDupe_Click() 'On Error GoTo Err_Handler 'Purpose: Duplicate the main form record and related records in the subform. Dim strSql As String 'SQL statement. Dim lngID As Long 'Primary key value of the new record. 'Save any edits first If Me.Dirty Then Me.Dirty = False End If 'Make sure there is a record to duplicate. If Me.NewRecord Then MsgBox "Select the record to duplicate." Else 'Duplicate the main record: add to form's clone. With Me.RecordsetClone .AddNew !CustomerID = Me.CustomerID !EmployeeID = Me.EmployeeID !OrderDate = Date 'etc for other fields. .Update 'Save the primary key value, to use as the foreign key for the related records. .Bookmark = .LastModified lngID = !OrderID 'CUT - NOT USED - Duplicate the related records: append query. End If 'Display the new duplicate. Me.Bookmark = .LastModified End With End If
----------
I guess that it makes sense that after update of the mainform a new record on the subform is added as well, but I don't necessarily want to add it immediately.

Would it help if I disabled the subform while adding the new record on the Mainform?

This is driving me crazy, really.
Before I used to have separate forms and everything was working fine, now with the new gui I have this unwanted behaviour.
I think it depends on the fact that if the subform is open in the nearby tab, then the new record must be added, but I want to prevent this.

Any suggestions?

Thanks!
Erika

I am using the following statement to pass the value from a ComboBox (Combo38) on the main form to a field on a subform (BridgeMaintItems). The statement below only seems to modify the StrID field in the first record on the subform. For my append query to work correctly, I need to update the StrID field in every record (+/- 40 records).

Me.BridgeMaintItems_subform1.Form![StrID] = Me.[Combo38]

Any thoughts? Seems like it should be easy, but I feel like the syntax is only setting the value in the subform's first record since it is the default "active" record when the subform is open.

Many thanks for your time!

Hello all,

Firstly, let me say that I am hugely impressed by the community here. I've been reading the forums for a while and have had many questions solved thanks to you.

Now, I do find myself stumped. I've read a bunch of posts and nothing quite deals with the mess I'm in.

I have a form with several tabs (10). This form and 2 of the tabs are populated by a query (Q1). On 8 of the 10 tabs there are subforms, each subform has two unbound list boxes, populated by queries. (ie. Sub1Q1, Sub1Q2 etc.).


The challenge is how to get all the tabs' subforms updated when moving from record 1 to record 2. I've tried setting event triggers on current for the main form, tried every other combination I could think of and nothing. Everything loads fine, the subforms just show the values for the first loaded result, and if I want to move to another result, I first have to click in the subform then hit the refresh all button. I'd like for this to happen automatically.

The tabs and subforms do not have a datasource on them, I've tried adding one and linking it to the main forms datasource, but that doesnt seem to help.

Help!

Let me know if you need more clarification.

Hi There,

I have some experience with sql, but limited experience with access. I am trying to create duplicate parent and child records using a form. I have added the following VB code and I keep getting a syntax erro in insert into statement. I'm hoping (praying) it's something simple that I am just not seeing.

The Parent record is created correctly.

Thanks

Here is the code:

Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the subform.
Dim strSql As String 'SQL statement.
Dim lngID As String 'Primary key value of the new record.


'Save any edits first
If Me.Dirty Then
Me.Dirty = False
End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else

'Pass in new Key
myValue = InputBox("Enter Forecast Date", "Inputbox")
MsgBox myValue
'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!PPod = Me.PPod
!Engineer = Me.Engineer
!Current_Product = Me.Current_Product
!Bag_size_kg = Me.Bag_size_kg
!Ppod_downtime = Me.Ppod_downtime
!Date = Me.Date
!Key = Me.PPod & "-" & myValue

.Update

'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = .LastModified
lngID = !Key


'Duplicate the related records: append query.
If Me.[RE_Forecast_Inj subform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [RE_Forecast_Inj] ( Key, Pattern, Ppod, Date, Qinj_m3d, Cppm, Bags_Week ) " & _
"SELECT " & lngID & " As NewID, Pattern, Ppod, Date, Qinj_m3d, Cppm, Bags_Week " & _
"FROM [RE_Forecast_Inj] WHERE Key = " & Me.Key & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click()"
Resume Exit_Handler
End Sub

I have two forms in question. Invoices and Invoice Details subform. The records are stored in another table called Inventory Transactions. Default value of InvTransDate = Invoice date on the main form. My problem is that when I change the date on the main form, only the first record on the subform reflects the change. I need all of the records in the subform to be updated at the same time.

Thanks

I have a subform in which I enter the [Order Intake Value] per [Year] for a [Product] (Main form is products form and subform has values) I have another subform based on a query which is SUM([Order Intake Value]) for a given [Product] How can I get this SUM([Order Intake Value]) field to update immediately after I update the [Order Intake Value] field i.e. without having to close and then reopen the main form?

After I enter numbers into one subform how can I update info in another subform that performs a calculation based on that entered data? I want the calculated field, based on a query, to update in the "after update" event of my data entry field.

I have a subform based on a table to enter some numbers. I have another subform in the same main form that is based on a query. Subform no. 2 calculates the sum value of the numbers entered in subform no1. I have tried using code on the subform no. 1 field and on the subform itself to update the calculation in subform no 2. in the afterupdate event for subform no 1 data. I keep running into errors with trying to reference the control, form field or underlying query.

I've heard of "screen.active form" Is this something I need to use or not? If so how?

Hi,
I am trying to figure out how to do this.

I have a Form that has different controls i.e. TextBoxes, ComboBoxes, Labels etc and it is my Main Form also. The value in the controls are filled by running a Query and with NavigationButtons can Navigate through the data i.e. Record Source Property of my Main Form is set to a Query. Lets's say I get 15 records when the Form pops-up and the first record is shown in the controls by default, to get to different record can use the NavigationButtons.

Now what I also want is that to show the same result in a Dataseet view also so that the user can see all the records at one time also. So for this I have a subform whose Record Source Property is also set to the same Query that's set to Main Form and I have added this subform to my Main Form. Now when my Form pops-up it shows all the records in a subform Datasheet and also the individual records in the controls of my Main Form and can use NavigationButtons to move to different record. On my my Main Form I also have a button that when clicked searches a table against any value the user selects in the Main Form control and returns the result e.g. it returns 5 records so my controls in my Main Form are updated and the NavigationButtons show 5 and can move arround. But my subform doesn't show those five records returned by search. How can I update my subform also.

In my buttons clicked event I get the value of the selected control and wrote a SQL statement and create a query and now I assign the Record Source property of my Main Form to this new query to filter through the results. Now I want to assign the same query to my subform also so that I get the same records and show them in the subform Datasheet view. I think I can't assign a Record Source property of my subform to this new query now because it only have Source Object property. How can I show the filtered records in my subform also. Filtering is only done on the Main Form's controls and my subform is there only to show the results in a Datasheet view and I also don't show the NavigationButtons for the subform as it is a Datasheet with scrolls. Is there any way I can change the Record Source Property of my subform to some other Query in my Main Form

I'll really appreciate for any help.
Thanks.


Not finding an answer? Try a Google search.