COMPGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

zz function stopping delete of a record - function works fine otherwise

  • Follow


I am receiving error 2147352567 You entered an expression that has an
invalid reference to the property Form/Report
when trying to delete  a record from
form 1Cruise. The error is happening in a function that calculates and
sends a total weight to a text box. The delete never completes.

The code in the function is as follows and it errors when checking the
setid in the first if statement when attempting the delete but works
fine otherwise.

Public Function GetTotalWeight() As Double
    On Error GoTo ErrHandler

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim intCriteria As Long

    'If Not IsNull([Forms]![Form1]![1Cruise].[Form]![CruiseID]) Then

    If Not IsNull([Forms]![Form1]![1Cruise].[Form]![1Sets].[Form]!
[SetID]) Then
        strSQL = "SELECT Sum([1CatchComposition].[Weight (kg)]) AS
[SumOfWeight (kg)], [1CatchComposition].SetIDFK " & _
                    "FROM 1CatchComposition " & _
                    "GROUP BY [1CatchComposition].SetIDFK " & _
                    "HAVING [1CatchComposition].SetIDFK=" & _
                    [Forms]![Form1]![1Cruise].[Form]![1Sets].[Form]!
[SetID] & ";"

        Set db = CurrentDb
        Set rs = db.OpenRecordset(strSQL)

        If Not rs.EOF Then
            If Not IsNull(rs.Fields("[SumOfWeight (kg)]")) Then
                GetTotalWeight = rs.Fields("[SumOfWeight (kg)]")
            End If
        Else
            GetTotalWeight = 0
        End If
    End If
   'End If

ErrHandler:
    If Err.Number = 0 Then
    Else
        MsgBox Err.Number & "  " & Err.Description & "  " & "has
occurred in GetTotalWeight."
    End If

End Function
0
Reply davjoh123 3/15/2010 10:33:17 PM 

davjoh123@yahoo.com wrote:
> I am receiving error 2147352567 You entered an expression that has an
> invalid reference to the property Form/Report
> when trying to delete  a record from
> form 1Cruise. The error is happening in a function that calculates and
> sends a total weight to a text box. The delete never completes.
> 
> The code in the function is as follows and it errors when checking the
> setid in the first if statement when attempting the delete but works
> fine otherwise.
> 
> Public Function GetTotalWeight() As Double
>     On Error GoTo ErrHandler
> 
>     Dim db As DAO.Database
>     Dim rs As DAO.Recordset
>     Dim strSQL As String
>     Dim intCriteria As Long
> 
>     'If Not IsNull([Forms]![Form1]![1Cruise].[Form]![CruiseID]) Then
> 
>     If Not IsNull([Forms]![Form1]![1Cruise].[Form]![1Sets].[Form]!
> [SetID]) Then
>         strSQL = "SELECT Sum([1CatchComposition].[Weight (kg)]) AS
> [SumOfWeight (kg)], [1CatchComposition].SetIDFK " & _
>                     "FROM 1CatchComposition " & _
>                     "GROUP BY [1CatchComposition].SetIDFK " & _
>                     "HAVING [1CatchComposition].SetIDFK=" & _
>                     [Forms]![Form1]![1Cruise].[Form]![1Sets].[Form]!
> [SetID] & ";"
> 
>         Set db = CurrentDb
>         Set rs = db.OpenRecordset(strSQL)
> 
>         If Not rs.EOF Then
>             If Not IsNull(rs.Fields("[SumOfWeight (kg)]")) Then
>                 GetTotalWeight = rs.Fields("[SumOfWeight (kg)]")
>             End If
>         Else
>             GetTotalWeight = 0
>         End If
>     End If
>    'End If
> 
> ErrHandler:
>     If Err.Number = 0 Then
>     Else
>         MsgBox Err.Number & "  " & Err.Description & "  " & "has
> occurred in GetTotalWeight."
>     End If
> 
> End Function

Is
   If Not IsNull([Forms]![Form1]![1Cruise].[Form]![1Sets].[Form]![SetID])
the line it breaks on?

Are you attempting to reference a subform?

If one form with the mainform called Form1
   If Not IsNull([Forms]![Form1]![SetID]) then
If refering to SetID in a subform; 1cruise being mainform, 1Sets the subform
   If Not IsNull([Forms]![1Cruise][1Sets]![SetID])
0
Reply Salad 3/16/2010 12:13:33 AM 


Salad wrote:

> davjoh123@yahoo.com wrote:
> 
>> I am receiving error 2147352567 You entered an expression that has an
>> invalid reference to the property Form/Report
>> when trying to delete  a record from
>> form 1Cruise. The error is happening in a function that calculates and
>> sends a total weight to a text box. The delete never completes.
>>
>> The code in the function is as follows and it errors when checking the
>> setid in the first if statement when attempting the delete but works
>> fine otherwise.
>>
>> Public Function GetTotalWeight() As Double
>>     On Error GoTo ErrHandler
>>
>>     Dim db As DAO.Database
>>     Dim rs As DAO.Recordset
>>     Dim strSQL As String
>>     Dim intCriteria As Long
>>
>>     'If Not IsNull([Forms]![Form1]![1Cruise].[Form]![CruiseID]) Then
>>
>>     If Not IsNull([Forms]![Form1]![1Cruise].[Form]![1Sets].[Form]!
>> [SetID]) Then
>>         strSQL = "SELECT Sum([1CatchComposition].[Weight (kg)]) AS
>> [SumOfWeight (kg)], [1CatchComposition].SetIDFK " & _
>>                     "FROM 1CatchComposition " & _
>>                     "GROUP BY [1CatchComposition].SetIDFK " & _
>>                     "HAVING [1CatchComposition].SetIDFK=" & _
>>                     [Forms]![Form1]![1Cruise].[Form]![1Sets].[Form]!
>> [SetID] & ";"
>>
>>         Set db = CurrentDb
>>         Set rs = db.OpenRecordset(strSQL)
>>
>>         If Not rs.EOF Then
>>             If Not IsNull(rs.Fields("[SumOfWeight (kg)]")) Then
>>                 GetTotalWeight = rs.Fields("[SumOfWeight (kg)]")
>>             End If
>>         Else
>>             GetTotalWeight = 0
>>         End If
>>     End If
>>    'End If
>>
>> ErrHandler:
>>     If Err.Number = 0 Then
>>     Else
>>         MsgBox Err.Number & "  " & Err.Description & "  " & "has
>> occurred in GetTotalWeight."
>>     End If
>>
>> End Function
> 
> 
> Is
>   If Not IsNull([Forms]![Form1]![1Cruise].[Form]![1Sets].[Form]![SetID])
> the line it breaks on?
> 
> Are you attempting to reference a subform?
> 
> If one form with the mainform called Form1
>   If Not IsNull([Forms]![Form1]![SetID]) then
> If refering to SetID in a subform; 1cruise being mainform, 1Sets the 
> subform
>   If Not IsNull([Forms]![1Cruise][1Sets]![SetID])
     If Not IsNull([Forms]![1Cruise]![1Sets]![SetID])
0
Reply Salad 3/16/2010 12:41:02 AM 

On Mar 15, 5:13=A0pm, Salad <sa...@oilandvinegar.com> wrote:
> davjoh...@yahoo.com wrote:
> > I am receiving error 2147352567 You entered an expression that has an
> > invalid reference to the property Form/Report
> > when trying to delete =A0a record from
> > form 1Cruise. The error is happening in a function that calculates and
> > sends a total weight to a text box. The delete never completes.
>
> > The code in the function is as follows and it errors when checking the
> > setid in the first if statement when attempting the delete but works
> > fine otherwise.
>
> > Public Function GetTotalWeight() As Double
> > =A0 =A0 On Error GoTo ErrHandler
>
> > =A0 =A0 Dim db As DAO.Database
> > =A0 =A0 Dim rs As DAO.Recordset
> > =A0 =A0 Dim strSQL As String
> > =A0 =A0 Dim intCriteria As Long
>
> > =A0 =A0 'If Not IsNull([Forms]![Form1]![1Cruise].[Form]![CruiseID]) The=
n
>
> > =A0 =A0 If Not IsNull([Forms]![Form1]![1Cruise].[Form]![1Sets].[Form]!
> > [SetID]) Then
> > =A0 =A0 =A0 =A0 strSQL =3D "SELECT Sum([1CatchComposition].[Weight (kg)=
]) AS
> > [SumOfWeight (kg)], [1CatchComposition].SetIDFK " & _
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 "FROM 1CatchComposition " & _
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 "GROUP BY [1CatchComposition].S=
etIDFK " & _
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 "HAVING [1CatchComposition].Set=
IDFK=3D" & _
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 [Forms]![Form1]![1Cruise].[Form=
]![1Sets].[Form]!
> > [SetID] & ";"
>
> > =A0 =A0 =A0 =A0 Set db =3D CurrentDb
> > =A0 =A0 =A0 =A0 Set rs =3D db.OpenRecordset(strSQL)
>
> > =A0 =A0 =A0 =A0 If Not rs.EOF Then
> > =A0 =A0 =A0 =A0 =A0 =A0 If Not IsNull(rs.Fields("[SumOfWeight (kg)]")) =
Then
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 GetTotalWeight =3D rs.Fields("[SumOfWei=
ght (kg)]")
> > =A0 =A0 =A0 =A0 =A0 =A0 End If
> > =A0 =A0 =A0 =A0 Else
> > =A0 =A0 =A0 =A0 =A0 =A0 GetTotalWeight =3D 0
> > =A0 =A0 =A0 =A0 End If
> > =A0 =A0 End If
> > =A0 =A0'End If
>
> > ErrHandler:
> > =A0 =A0 If Err.Number =3D 0 Then
> > =A0 =A0 Else
> > =A0 =A0 =A0 =A0 MsgBox Err.Number & " =A0" & Err.Description & " =A0" &=
 "has
> > occurred in GetTotalWeight."
> > =A0 =A0 End If
>
> > End Function
>
> Is
> =A0 =A0If Not IsNull([Forms]![Form1]![1Cruise].[Form]![1Sets].[Form]![Set=
ID])
> the line it breaks on?
>
> Are you attempting to reference a subform?
>
> If one form with the mainform called Form1
> =A0 =A0If Not IsNull([Forms]![Form1]![SetID]) then
> If refering to SetID in a subform; 1cruise being mainform, 1Sets the subf=
orm
> =A0 =A0If Not IsNull([Forms]![1Cruise][1Sets]![SetID])

Form1 contains a tab control which in turn contains several forms some
contain a single form and some which contain a form and a subform.
Form 1Cruise has a subform 1Sets and 1Sets has a subform and form
1Cruise is a subform I guess of Form1.
0
Reply davjoh123 3/17/2010 7:48:23 PM 

davjoh123@yahoo.com wrote:
> On Mar 15, 5:13 pm, Salad <sa...@oilandvinegar.com> wrote:
> 
>>davjoh...@yahoo.com wrote:
>>
>>>I am receiving error 2147352567 You entered an expression that has an
>>>invalid reference to the property Form/Report
>>>when trying to delete  a record from
>>>form 1Cruise. The error is happening in a function that calculates and
>>>sends a total weight to a text box. The delete never completes.
>>
>>>The code in the function is as follows and it errors when checking the
>>>setid in the first if statement when attempting the delete but works
>>>fine otherwise.
>>
>>>Public Function GetTotalWeight() As Double
>>>    On Error GoTo ErrHandler
>>
>>>    Dim db As DAO.Database
>>>    Dim rs As DAO.Recordset
>>>    Dim strSQL As String
>>>    Dim intCriteria As Long
>>
>>>    'If Not IsNull([Forms]![Form1]![1Cruise].[Form]![CruiseID]) Then
>>
>>>    If Not IsNull([Forms]![Form1]![1Cruise].[Form]![1Sets].[Form]!
>>>[SetID]) Then
>>>        strSQL = "SELECT Sum([1CatchComposition].[Weight (kg)]) AS
>>>[SumOfWeight (kg)], [1CatchComposition].SetIDFK " & _
>>>                    "FROM 1CatchComposition " & _
>>>                    "GROUP BY [1CatchComposition].SetIDFK " & _
>>>                    "HAVING [1CatchComposition].SetIDFK=" & _
>>>                    [Forms]![Form1]![1Cruise].[Form]![1Sets].[Form]!
>>>[SetID] & ";"
>>
>>>        Set db = CurrentDb
>>>        Set rs = db.OpenRecordset(strSQL)
>>
>>>        If Not rs.EOF Then
>>>            If Not IsNull(rs.Fields("[SumOfWeight (kg)]")) Then
>>>                GetTotalWeight = rs.Fields("[SumOfWeight (kg)]")
>>>            End If
>>>        Else
>>>            GetTotalWeight = 0
>>>        End If
>>>    End If
>>>   'End If
>>
>>>ErrHandler:
>>>    If Err.Number = 0 Then
>>>    Else
>>>        MsgBox Err.Number & "  " & Err.Description & "  " & "has
>>>occurred in GetTotalWeight."
>>>    End If
>>
>>>End Function
>>
>>Is
>>   If Not IsNull([Forms]![Form1]![1Cruise].[Form]![1Sets].[Form]![SetID])
>>the line it breaks on?
>>
>>Are you attempting to reference a subform?
>>
>>If one form with the mainform called Form1
>>   If Not IsNull([Forms]![Form1]![SetID]) then
>>If refering to SetID in a subform; 1cruise being mainform, 1Sets the subform
>>   If Not IsNull([Forms]![1Cruise][1Sets]![SetID])
> 
> 
> Form1 contains a tab control which in turn contains several forms some
> contain a single form and some which contain a form and a subform.
> Form 1Cruise has a subform 1Sets and 1Sets has a subform and form
> 1Cruise is a subform I guess of Form1.

I was providing some syntax for you.  I was too confused by your command 
lines to provide an accurate response.

If I were in your shoes, I'd open the form, then I'd open a module from 
the database window, open the Immediate window, and try some of the 
syntax methods.  I used Form1, 1Cruise, 1Sets, etc as EXAMPLES ONLY. 
Substitute your real names.

You might want to review some topics
http://www.mvps.org/access/forms/frm0025.htm
http://www.mvps.org/access/forms/frm0031.htm
and if still confused Google referring to forms, subforms, and controls.
0
Reply Salad 3/17/2010 9:15:06 PM
comp.databases.ms-access 42136 articles. 8 followers.

4 Replies
231 Views

(page loaded in 0.094 seconds)

Similiar Articles:

Guide: Set handles out of closerequestfcn - comp.soft-sys.matlab ...
... the just closed figure no longer exists and delete ... > end The solution submitted by Walter works fine. ... GUIDE gui handles visibility to user defined function ...

top 10 uses for random data compression?? anyone? - comp ...
Otherwise, why wouldn't you simply generate more random ... else does! > >Phil As a some expert in patent works ... Every scottish contradiction or function, and she'll for ...

How to check whether malloc has allocated memory properly in case ...
It's exactly like the situation with impure functions ... the code has been redone, but not because it stopped ... This also worked fine on the univac and decsystem-20 ...

[comp.publish.cdrom] CD-Recordable FAQ, Part 1/4 - comp.publish ...
Archive-name: cdrom/cd-recordable/part1 Posting-Frequency: monthly Last-modified: 2008/10/09 Version: 2.71 Send corrections and updates to And...

PHP: setcookie - Manual
Examples follow how to delete cookies sent in ... Beware, the example below works fine on my testing ... Otherwise you have to stop attack. Sorry for my poor english!

javascript - Confirmation with Data when Deleting a row in ...
Your xx function is incorrect as well - I'm assuming ... You need to pass this through as a string otherwise you ... GridView ImageButton confirm and delete record

7/25/2012 3:35:00 AM


Reply:



Privacy Policy | All Times Are GMT(UTC) | powered by