Changing form field according to entries in subform field 1

[doctorswamp]

I'm trying to get update a field on a form depending on entries into a subform field.

Example :

- text field called Action in subform sfmHistory
- yes/no field called MakeContact in main form frmCompanies

If Action field value is 'Phone' I'd like to trigger 'yes' in the MakeContact field.

Have experimented with After Update event along the lines

If me.Action = "Phone" then
forms!frmCompanies.MakeContact = -1
end if

but it gives an error.

Is this just bad syntax or am I way off beam?

Thanks in anticipation

 

[Remou]

This works for me. What error are you getting?

 

[TheAceMan1]

How are ya doctorswamp . . .

Since the code seems fine, by any chance when you set MakeContact to True, [blue]are you expecting an event to fire?[/blue]

See Ya! . . . . . .

 

[doctorswamp]

Thanks guys - hope you also get some sleep ...

Some progress. First, the MakeContact field doesn't fire anything.

Using the code I sent does actually work, I now discover, so perhaps some finger trouble. Since the subform fields already contain entries I tried attaching the code to On Current for the subform - nothing happens when moving from record to record, ie no tick appears in the main form field. Which event should I use?

Bigger question is turning the thing round, where I'd like a tick in a main form field to add ticks to all entries in the subform for the current record.

The actual form displays delivery note items, and has a check-box for DeliveryCompleted. When ticked I'd like all the delivery items in the subform that make up the delivery note to have DeliveryItemCompleted ticked too.

Code for main form tick-box is

Private Sub DeliveryCompleted_AfterUpdate()
If Me.DeliveryCompleted = -1 Then
Forms!sfmDeliveryNoteList.DeliveryItemCompleted = -1
End If
End Sub

This returns error 2450 '..can't find the form sfmDeliveryNoteList ...

 

[Remou]

Events do not fire when controls are updated with code. You must include .Form after the name of the subform control.

I think the best thing to do is to create an update query or loop through the recordset.

A Very Rough Update Query:

If MsgBox ("Update records?",vbYesNo) = vbYes Then
'Either update 
   strSQL = "Update tblOrderDetail Set DeliveryItemCompleted = True Where OrderID = " & Me.OrderID
   DoCmd.RunSQL strSQL
Else
'Or set the main form tick / option to false
   Me.DeliveryItemCompleted = False
End if

A Very Rough Loop:

Dim rs a DAO.Recordset
'Subform recordset
Set rs=me.Forms!sfmDeliveryNoteList.Form.RecordsetClone
'Count
rs.MoveLast
rs.MoveFirst
If MsgBox ("Update " & rs.Recordcount & " records?",vbYesNo) = vbYes Then
'Either update the subform
   Do While Not rs.EOF
       rs.Edit
       rs!DeliveryItemCompleted = True
       rs.Update
       rs.MoveNext
   Loop
Else
'Or set the main form tick / option to false
   Me.DeliveryItemCompleted = False
End if
me.Forms!sfmDeliveryNoteList.Form.Requery

 

[doctorswamp]

Thanks Remou

Have assumed I'm inserting either suggestion into After Update for the main form's checkbox.

Update Query - this gives Error 3464 'Data type mismatch' in DoCmd.RunSQL strsql line; added Dim strsql as String but got same error.

Loop - gives error 'method or data member not found' in the line Set rs=me.Forms!sfmDel... with Forms! highlighted

 

[TheAceMan1]

doctorswamp . . .

As to your origional code try the following in the [blue]AfterUpdate[/blue] event of Action:

[blue]   If Me.Action = "Phone" Then
      Me.Parent!MakeContact = True
   Else
      Me.Parent!MakeContact = False
   End If[/blue]

See Ya! . . . . . .

 

[Remou]

DoctorSwamp
Please post the code as modified to suit your set-up.

 

[doctorswamp]

Remou

Two versions tried;

- the main form's checkbox is called Completed

- the subform is called sfmDeliveryNoteList
- the table source for the subform is called PartDeliveries
- the checkbox in the table that I want to update via the
subform is called DeliveryItemCompleted

Private Sub Completed_AfterUpdate()
Dim rs As DAO.Recordset
'Subform recordset
Set rs = Me.Forms!sfmDeliveryNoteList.Form.RecordsetClone
'Count
rs.MoveLast
rs.MoveFirst
If MsgBox("Update " & rs.RecordCount & " records?",
vbYesNo) = vbYes Then
'Either update the subform
Do While Not rs.EOF
rs.Edit
rs!DeliveryItemCompleted = True
rs.Update
rs.MoveNext
Loop
Else
'Or set the main form tick / option to false
Me.Completed = False
End If

Me.Form!sfmDeliveryNoteList.Form.Requery

End Sub

-------------------

Private Sub Completed_AfterUpdate()

Dim strsql As String
If MsgBox("Update records?", vbYesNo) = vbYes Then
'Either update
strsql = "Update PartDeliveries Set
DeliveryItemCompleted = True Where DeliveryNoteNo = "
& Me.DeliveryNoteNo

DoCmd.RunSQL strsql

Else
'Or set the main form tick / option to false
Me.Completed = False
End If

End Sub

 

[Remou]

I did not explain properly about the number of forms needed. The Me. bit means Forms!TheFormThisIsRunningIn. After that, you have a subform control, which contains a form. It is the contained form you are taliking to, not the control, so you need a Form after the name of the subform control. Try:

Private Sub Completed_AfterUpdate()
Dim rs As DAO.Recordset
'Subform recordset
Set rs = Me.sfmDeliveryNoteList.Form.RecordsetClone
'Count
  rs.MoveLast
  rs.MoveFirst
  If MsgBox("Update " & rs.RecordCount & " records?",  
  vbYesNo) = vbYes Then
'Either update the subform
   Do While Not rs.EOF
       rs.Edit
       rs!DeliveryItemCompleted = True
       rs.Update
       rs.MoveNext
   Loop
Else
'Or set the main form tick / option to false
   Me.Completed = False
End If

Me.sfmDeliveryNoteList.Form.Requery

End Sub

As for the Update Query, is DeliveryNoteNo a text field? If so, you need some single quotes:
[tt]strsql = "Update PartDeliveries Set
DeliveryItemCompleted = True Where DeliveryNoteNo = '"
& Me.DeliveryNoteNo & "'"[/tt]

 

[doctorswamp]

Remou

Bingo, the Update Query option works. Is there now a way of suppressing the second message 'You are about to update x rows' to avoid having to click twice?

Still got same error with the loop method but don't want to take any more of your time as the first does the job.

Many thanks

 

[PHV]

DoCmd.SetWarnings False
DoCmd.RunSQL strsql
DoCmd.SetWarnings True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[doctorswamp]

Remou

Trying to give you a star but get 'Error on page' message. Will try next time.