Auto Update Fields After Edit

G

Guest

Hello All:

I have a main form where all geographic information is keyed for a contract.
I then have several subforms connected through Command buttons. I have events
setup on click that auto-populates those fields on the subform with the same
info but my problem is if i go back into a record and edit one of those
fields, the change is in the subform's table but not on the subform itself.
So it will end up looking like this:

Main form: keyed "Thomas" into ContractSpecialist field
Sub form: auto populates with "Thomas" in ContractSpecialist field

Main form: edit "Thomas" replace with "George" in ContractSpecialist field
Sub form: "Thomas" remains in ContractSpecialist field after update
Sub form Table: updates "Thomas" replace with "Geroge: in ContractSpecialist
field after update

Here is some background on the forms if it helps:

Main Form Name: Contract Profile

Main form has the following events:
Private Sub Form_Load()
Dim i As Integer
For i = 1 To CommandBars.Count
CommandBars(i).Enabled = False
Next i

End Sub
'END OF SUBROUTINE

Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
End Sub
Command Button used to open subform has this event:
Private Sub Review_Assessment_Click()
On Error GoTo Err_Review_Assessment_Click
If IsNull(Me![ID]) Then
MsgBox "Enter Contract information before viewing Contract Review
Assessment form."
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenForm "Contracts Review Assessment", , , "[Contracts Review
Assessment]![ID] = Forms![Contract Profile]![ID]"
End If

Exit_Review_Assessment_Click:
Exit Sub

Err_Review_Assessment_Click:
MsgBox Err.Description
Resume Exit_Review_Assessment_Click
End Sub

ContractSpecialist field has the following expression in table and form
properties

Table- Combo set to Table/Query with the following expresion:
=[Forms]![Contract Profile]![ContractSpecialistID]

Form-
=[Forms]![Contract Profile]![ContractSpecialistID]

Subform has the following properties and events:
Record Source- SELECT DISTINCTROW [Contract Review Assessment].* FROM
[Contract Review Assessment] WHERE (([Contract Review
Assessment].ID=forms![Contract Profile]!ID));

Filter- [Contracts Review Assessment]![ID] = Forms![Contract Profile]![ID]

Private Sub Form_Activate()
On Error GoTo Err_Form_Activate
Me.Requery

Exit_Form_Activate:
Exit Sub

Err_Form_Activate:
MsgBox Err.Description
Resume Exit_Form_Activate
End Sub
 
K

kingston via AccessMonster.com

Add a procedure to the AfterUpdate event of the field ContractSpecialist in
your main form. This procedure should check for changes so that it
propagates the change to the subforms and requeries the subforms. Check for
Nulls too so that you can delete subform records. This might help:

Me.SubForm.Requery
If Me.ContractSpecialist <> Me.ContractSpecialist.OldValue then...

Hello All:

I have a main form where all geographic information is keyed for a contract.
I then have several subforms connected through Command buttons. I have events
setup on click that auto-populates those fields on the subform with the same
info but my problem is if i go back into a record and edit one of those
fields, the change is in the subform's table but not on the subform itself.
So it will end up looking like this:

Main form: keyed "Thomas" into ContractSpecialist field
Sub form: auto populates with "Thomas" in ContractSpecialist field

Main form: edit "Thomas" replace with "George" in ContractSpecialist field
Sub form: "Thomas" remains in ContractSpecialist field after update
Sub form Table: updates "Thomas" replace with "Geroge: in ContractSpecialist
field after update

Here is some background on the forms if it helps:

Main Form Name: Contract Profile

Main form has the following events:
Private Sub Form_Load()
Dim i As Integer
For i = 1 To CommandBars.Count
CommandBars(i).Enabled = False
Next i

End Sub
'END OF SUBROUTINE

Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
End Sub
Command Button used to open subform has this event:
Private Sub Review_Assessment_Click()
On Error GoTo Err_Review_Assessment_Click
If IsNull(Me![ID]) Then
MsgBox "Enter Contract information before viewing Contract Review
Assessment form."
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenForm "Contracts Review Assessment", , , "[Contracts Review
Assessment]![ID] = Forms![Contract Profile]![ID]"
End If

Exit_Review_Assessment_Click:
Exit Sub

Err_Review_Assessment_Click:
MsgBox Err.Description
Resume Exit_Review_Assessment_Click
End Sub

ContractSpecialist field has the following expression in table and form
properties

Table- Combo set to Table/Query with the following expresion:
=[Forms]![Contract Profile]![ContractSpecialistID]

Form-
=[Forms]![Contract Profile]![ContractSpecialistID]

Subform has the following properties and events:
Record Source- SELECT DISTINCTROW [Contract Review Assessment].* FROM
[Contract Review Assessment] WHERE (([Contract Review
Assessment].ID=forms![Contract Profile]!ID));

Filter- [Contracts Review Assessment]![ID] = Forms![Contract Profile]![ID]

Private Sub Form_Activate()
On Error GoTo Err_Form_Activate
Me.Requery

Exit_Form_Activate:
Exit Sub

Err_Form_Activate:
MsgBox Err.Description
Resume Exit_Form_Activate
End Sub
Click to expand...
 
G

Guest

Thanks, I'm not sure of the code involved but i'll give it a try.

kingston via AccessMonster.com said:
Add a procedure to the AfterUpdate event of the field ContractSpecialist in
your main form. This procedure should check for changes so that it
propagates the change to the subforms and requeries the subforms. Check for
Nulls too so that you can delete subform records. This might help:

Me.SubForm.Requery
If Me.ContractSpecialist <> Me.ContractSpecialist.OldValue then...

Hello All:

I have a main form where all geographic information is keyed for a contract.
I then have several subforms connected through Command buttons. I have events
setup on click that auto-populates those fields on the subform with the same
info but my problem is if i go back into a record and edit one of those
fields, the change is in the subform's table but not on the subform itself.
So it will end up looking like this:

Main form: keyed "Thomas" into ContractSpecialist field
Sub form: auto populates with "Thomas" in ContractSpecialist field

Main form: edit "Thomas" replace with "George" in ContractSpecialist field
Sub form: "Thomas" remains in ContractSpecialist field after update
Sub form Table: updates "Thomas" replace with "Geroge: in ContractSpecialist
field after update

Here is some background on the forms if it helps:

Main Form Name: Contract Profile

Main form has the following events:
Private Sub Form_Load()
Dim i As Integer
For i = 1 To CommandBars.Count
CommandBars(i).Enabled = False
Next i

End Sub
'END OF SUBROUTINE

Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
End Sub
Command Button used to open subform has this event:
Private Sub Review_Assessment_Click()
On Error GoTo Err_Review_Assessment_Click
If IsNull(Me![ID]) Then
MsgBox "Enter Contract information before viewing Contract Review
Assessment form."
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenForm "Contracts Review Assessment", , , "[Contracts Review
Assessment]![ID] = Forms![Contract Profile]![ID]"
End If

Exit_Review_Assessment_Click:
Exit Sub

Err_Review_Assessment_Click:
MsgBox Err.Description
Resume Exit_Review_Assessment_Click
End Sub

ContractSpecialist field has the following expression in table and form
properties

Table- Combo set to Table/Query with the following expresion:
=[Forms]![Contract Profile]![ContractSpecialistID]

Form-
=[Forms]![Contract Profile]![ContractSpecialistID]

Subform has the following properties and events:
Record Source- SELECT DISTINCTROW [Contract Review Assessment].* FROM
[Contract Review Assessment] WHERE (([Contract Review
Assessment].ID=forms![Contract Profile]!ID));

Filter- [Contracts Review Assessment]![ID] = Forms![Contract Profile]![ID]

Private Sub Form_Activate()
On Error GoTo Err_Form_Activate
Me.Requery

Exit_Form_Activate:
Exit Sub

Err_Form_Activate:
MsgBox Err.Description
Resume Exit_Form_Activate
End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Repost: Requery To Update Issues 1
Linking Fields in Multiple Tables 1
Form/Subform Merge 3
Update subform with combobox change on main form 1
Set focus in a specific record within a subform 1
Access Passing data from a form to another form! 0
Trouble Filtering a Sub Form With Combo Box Options 2
Search working in one form but not another 4

Top