Never figured this out - naming subform fields

[dcavaiani]

I am in VBA and trying to reference a subform field but get an compile error.

The table in the subfile is being queried as an rs.

The comparison field name in the table is saturdaystart. In the table, saturdaystart is defined as short date. IN the VBA, saveend is DIM as Date.

This is the code that errors:

savestart = rs!SaturdayStart
saveend = rs!FridayEnd
If count > 1 And (Forms![Rates Subform]!SaturdayStart <= saveend) Then
MsgBox ("Start Date must be Greater Than prior End Date")

 

[pbaldy]

See if this helps:

http://www.mvps.org/access/forms/frm0031.htm

 

[dcavaiani]

Gonna take some time studying this - but I bet it will help! Thanks.

 

[pbaldy]

Happy to help!

 

[dcavaiani]

I don't understand:

Forms![Rick - Customers Table Update NEW]![Rates Subform]!SaturdayStart

Checking the debug value of the above - I am getting a value of 'true' ????

 

[dcavaiani]

Option Compare Database
Public Sub EditTableData()
Dim count As Single
Dim savestart As date
Dim saveend As date
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM rates")
count = 0
'Check to see if the recordset actually contains rows
If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst 'Unnecessary in this case, but still a good habit
        
    Do Until rs.EOF = True
        'Perform an edit
        rs.Edit
If rs!Customer = Forms![Rick - Customers Table Update NEW]!Customer Then
    GoTo Next2
    Else
count = 0
GoTo Skip2
  End If
Next2:
count = count + 1
If count > 1 And ((Me!SaturdayStart) <= saveend) Then  ' Invalid use of Me Keyword *** ERROR ***
MsgBox ("Start Date must be Greater Than prior End Date")
Else
End If
savestart = rs!SaturdayStart
saveend = rs!FridayEnd
Skip2:
        'Move to the next record. Don't ever forget to do this.
        rs.MoveNext
    Loop
Else
    MsgBox "There are no records in the recordset."
End If
rs.Close 'Close the recordset
Set rs = Nothing 'Clean up
    
End Sub

 

[pbaldy]

If the sub is in a standard module, you can't use Me.

 

[dcavaiani]

I changed it to a Private class and that seems to solve it!

 

[pbaldy]

Glad you got it sorted.

 

[Simon_MT]

You can use With CodeContextObject. The dot replaces the parent form. Can be used on controls as well.

Function FeedDataAdditivesValues()
    With CodeContextObject
        If .[Feed Data Entry Additives].[Form].Recordset.RecordCount > 0 Then
            DoCmd.RunCommand acCmdSaveRecord
            Call FeedDataAdditivesToInvoice
            DoCmd.GoToControl "Medication Cost"
        End If
    End With
End Function

Simon