Run Time Error when calling Function

G

Guest

Hi,

I'm totally new with VBA so please forgive my ignorance.... I am getting a
'Run Time Error: 7874 Microsoft Office Access can't find object; #" when
trying to call a function that creates the next value in a custom counter. It
appears to be working fine (validation is working and data is loaded into the
table) except that the run time error appears at the end when its not finding
the object and asks if I want to debug.....

I have placed the function within a sub procedure that is fired when a
submit button is clicked. The sub procedures determines if a checkbox is
checked and runs a SQL insert statement if the box is true and then calls the
counter function to update the counter value. The counter function works
fine when I call it in the onClick line (=Next_Custom_Counter() ) but not
when nested in the sub procedure.

Here is my code....

Here is the Next_Custom_Counter function Code (taken from MS website):

Option Compare Database

Function Next_Custom_Counter()
On Error GoTo Next_Custom_Counter_Err

Dim MyDB As Database
Dim MyTable As Recordset
Dim NextCounter As Long

Set MyDB = CurrentDb
Set MyTable = MyDB.OpenRecordset("COUNTER")

MyTable.Edit
NextCounter = MyTable("Next Available Counter")



MyTable("Next Available Counter") = NextCounter + 1
MyTable.Update

MsgBox "The selected files have been assigned to group: " & "G" &
Str$(NextCounter)
Next_Custom_Counter = NextCounter

Exit Function



Next_Custom_Counter_Err:
MsgBox "Error " & Err & ": " & Error$
If Err <> 0 Then Resume
End

End Function


Here is the sub procedure that I am trying to call:

Private Sub Command20_Click()

DoCmd.SetWarnings False
If newgroup = True Then
DoCmd.RunSQL "INSERT INTO SF135 ( GROUPNUMBER, FOLDERID, BOX, BOXOF,
DISPOSALDT, FRC_RESTRICTION ) SELECT [counter].[groupid] & [next available
counter] AS GroupNumber, SF135_TEMP.FOLDERID,
Format([Forms]![SF135_PREPARE_FILES02]![boxof]) AS box,
Format([Forms]![SF135_PREPARE_FILES02]![Box]) AS boxof,
Format([Forms]![SF135_PREPARE_FILES02]![DisposalDT]) AS disposaldt,
Format([Forms]![SF135_PREPARE_FILES02]![DisposalRestrictCode]) AS restriction
FROM SF135_TEMP, [Counter] WHERE (((SF135_TEMP.ADD)=True) AND
(([Forms]![SF135_PREPARE_FILES02]![newgroup])=True));"

DoCmd.OpenFunction Next_Custom_Counter()


DoCmd.Close
Else
DoCmd.RunSQL "INSERT INTO SF135 ( GROUPNUMBER, FOLDERID, BOX, BOXOF,
DISPOSALDT, FRC_RESTRICTION ) SELECT Forms!SF135_PREPARE_FILES02!groupid AS
GroupNumber2, SF135_TEMP.FOLDERID, Format(Forms!SF135_PREPARE_FILES02!boxof)
AS box, Format(Forms!SF135_PREPARE_FILES02!Box) AS boxof,
Format(Forms!SF135_PREPARE_FILES02!DisposalDT) AS disposaldt,
Format(Forms!SF135_PREPARE_FILES02!DisposalRestrictCode) AS restriction FROM
SF135_TEMP WHERE (((SF135_TEMP.ADD)=True) And
((Forms!SF135_PREPARE_FILES02!oldgroup)=True));"
DoCmd.Close
End If

End Sub


THANK YOU!!!!!
 
G

Guest

Because you have space in your field name, it is getting confused. A basic
rule of naming is field names should only have letters, numbers, and the
underscore and should beging with a letter. My personal practice is to use
all upper case letters and at least one underscore. This makes them easily
distinguishable from variables. With the spaces in the names, you will need
to use brackets to make it clear to Jet what they are. There are also some
syntax errors I have correct. In addition, you did not type your function.
I assumed Long, but you may need to change it. It is not required, but that
way VBA does not have to do type coersion which takes up time.

Function Next_Custom_Counter() As Long
Dim MyDB As Database
Dim MyTable As Recordset
Dim NextCounter As Long

On Error GoTo Next_Custom_Counter_Err

Set MyDB = CurrentDb
Set MyTable = MyDB.OpenRecordset("COUNTER", dbOpenDynaset)
With MyTable
.Edit
NextCounter = ![Next Available Counter]
![Next Available Counter] = NextCounter + 1
.Update
End With
MsgBox "The selected files have been assigned to group: G " _
& Str$(NextCounter)
Next_Custom_Counter = NextCounter

End Function

ftrujill0 said:
Hi,

I'm totally new with VBA so please forgive my ignorance.... I am getting a
'Run Time Error: 7874 Microsoft Office Access can't find object; #" when
trying to call a function that creates the next value in a custom counter. It
appears to be working fine (validation is working and data is loaded into the
table) except that the run time error appears at the end when its not finding
the object and asks if I want to debug.....

I have placed the function within a sub procedure that is fired when a
submit button is clicked. The sub procedures determines if a checkbox is
checked and runs a SQL insert statement if the box is true and then calls the
counter function to update the counter value. The counter function works
fine when I call it in the onClick line (=Next_Custom_Counter() ) but not
when nested in the sub procedure.

Here is my code....

Here is the Next_Custom_Counter function Code (taken from MS website):

Option Compare Database

Function Next_Custom_Counter()
On Error GoTo Next_Custom_Counter_Err

Dim MyDB As Database
Dim MyTable As Recordset
Dim NextCounter As Long

Set MyDB = CurrentDb
Set MyTable = MyDB.OpenRecordset("COUNTER")

MyTable.Edit
NextCounter = MyTable("Next Available Counter")



MyTable("Next Available Counter") = NextCounter + 1
MyTable.Update

MsgBox "The selected files have been assigned to group: " & "G" &
Str$(NextCounter)
Next_Custom_Counter = NextCounter

Exit Function



Next_Custom_Counter_Err:
MsgBox "Error " & Err & ": " & Error$
If Err <> 0 Then Resume
End

End Function


Here is the sub procedure that I am trying to call:

Private Sub Command20_Click()

DoCmd.SetWarnings False
If newgroup = True Then
DoCmd.RunSQL "INSERT INTO SF135 ( GROUPNUMBER, FOLDERID, BOX, BOXOF,
DISPOSALDT, FRC_RESTRICTION ) SELECT [counter].[groupid] & [next available
counter] AS GroupNumber, SF135_TEMP.FOLDERID,
Format([Forms]![SF135_PREPARE_FILES02]![boxof]) AS box,
Format([Forms]![SF135_PREPARE_FILES02]![Box]) AS boxof,
Format([Forms]![SF135_PREPARE_FILES02]![DisposalDT]) AS disposaldt,
Format([Forms]![SF135_PREPARE_FILES02]![DisposalRestrictCode]) AS restriction
FROM SF135_TEMP, [Counter] WHERE (((SF135_TEMP.ADD)=True) AND
(([Forms]![SF135_PREPARE_FILES02]![newgroup])=True));"

DoCmd.OpenFunction Next_Custom_Counter()


DoCmd.Close
Else
DoCmd.RunSQL "INSERT INTO SF135 ( GROUPNUMBER, FOLDERID, BOX, BOXOF,
DISPOSALDT, FRC_RESTRICTION ) SELECT Forms!SF135_PREPARE_FILES02!groupid AS
GroupNumber2, SF135_TEMP.FOLDERID, Format(Forms!SF135_PREPARE_FILES02!boxof)
AS box, Format(Forms!SF135_PREPARE_FILES02!Box) AS boxof,
Format(Forms!SF135_PREPARE_FILES02!DisposalDT) AS disposaldt,
Format(Forms!SF135_PREPARE_FILES02!DisposalRestrictCode) AS restriction FROM
SF135_TEMP WHERE (((SF135_TEMP.ADD)=True) And
((Forms!SF135_PREPARE_FILES02!oldgroup)=True));"
DoCmd.Close
End If

End Sub


THANK YOU!!!!!
Click to expand...
 
G

Guest

Klatuu,

I took your changes and updated my module and I am still getting the run
time error. Do you know of anything else that would cause that type of
error? THANKS!

Klatuu said:
Because you have space in your field name, it is getting confused. A basic
rule of naming is field names should only have letters, numbers, and the
underscore and should beging with a letter. My personal practice is to use
all upper case letters and at least one underscore. This makes them easily
distinguishable from variables. With the spaces in the names, you will need
to use brackets to make it clear to Jet what they are. There are also some
syntax errors I have correct. In addition, you did not type your function.
I assumed Long, but you may need to change it. It is not required, but that
way VBA does not have to do type coersion which takes up time.

Function Next_Custom_Counter() As Long
Dim MyDB As Database
Dim MyTable As Recordset
Dim NextCounter As Long

On Error GoTo Next_Custom_Counter_Err

Set MyDB = CurrentDb
Set MyTable = MyDB.OpenRecordset("COUNTER", dbOpenDynaset)
With MyTable
.Edit
NextCounter = ![Next Available Counter]
![Next Available Counter] = NextCounter + 1
.Update
End With
MsgBox "The selected files have been assigned to group: G " _
& Str$(NextCounter)
Next_Custom_Counter = NextCounter

End Function

ftrujill0 said:
Hi,

I'm totally new with VBA so please forgive my ignorance.... I am getting a
'Run Time Error: 7874 Microsoft Office Access can't find object; #" when
trying to call a function that creates the next value in a custom counter. It
appears to be working fine (validation is working and data is loaded into the
table) except that the run time error appears at the end when its not finding
the object and asks if I want to debug.....

I have placed the function within a sub procedure that is fired when a
submit button is clicked. The sub procedures determines if a checkbox is
checked and runs a SQL insert statement if the box is true and then calls the
counter function to update the counter value. The counter function works
fine when I call it in the onClick line (=Next_Custom_Counter() ) but not
when nested in the sub procedure.

Here is my code....

Here is the Next_Custom_Counter function Code (taken from MS website):

Option Compare Database

Function Next_Custom_Counter()
On Error GoTo Next_Custom_Counter_Err

Dim MyDB As Database
Dim MyTable As Recordset
Dim NextCounter As Long

Set MyDB = CurrentDb
Set MyTable = MyDB.OpenRecordset("COUNTER")

MyTable.Edit
NextCounter = MyTable("Next Available Counter")



MyTable("Next Available Counter") = NextCounter + 1
MyTable.Update

MsgBox "The selected files have been assigned to group: " & "G" &
Str$(NextCounter)
Next_Custom_Counter = NextCounter

Exit Function



Next_Custom_Counter_Err:
MsgBox "Error " & Err & ": " & Error$
If Err <> 0 Then Resume
End

End Function


Here is the sub procedure that I am trying to call:

Private Sub Command20_Click()

DoCmd.SetWarnings False
If newgroup = True Then
DoCmd.RunSQL "INSERT INTO SF135 ( GROUPNUMBER, FOLDERID, BOX, BOXOF,
DISPOSALDT, FRC_RESTRICTION ) SELECT [counter].[groupid] & [next available
counter] AS GroupNumber, SF135_TEMP.FOLDERID,
Format([Forms]![SF135_PREPARE_FILES02]![boxof]) AS box,
Format([Forms]![SF135_PREPARE_FILES02]![Box]) AS boxof,
Format([Forms]![SF135_PREPARE_FILES02]![DisposalDT]) AS disposaldt,
Format([Forms]![SF135_PREPARE_FILES02]![DisposalRestrictCode]) AS restriction
FROM SF135_TEMP, [Counter] WHERE (((SF135_TEMP.ADD)=True) AND
(([Forms]![SF135_PREPARE_FILES02]![newgroup])=True));"

DoCmd.OpenFunction Next_Custom_Counter()


DoCmd.Close
Else
DoCmd.RunSQL "INSERT INTO SF135 ( GROUPNUMBER, FOLDERID, BOX, BOXOF,
DISPOSALDT, FRC_RESTRICTION ) SELECT Forms!SF135_PREPARE_FILES02!groupid AS
GroupNumber2, SF135_TEMP.FOLDERID, Format(Forms!SF135_PREPARE_FILES02!boxof)
AS box, Format(Forms!SF135_PREPARE_FILES02!Box) AS boxof,
Format(Forms!SF135_PREPARE_FILES02!DisposalDT) AS disposaldt,
Format(Forms!SF135_PREPARE_FILES02!DisposalRestrictCode) AS restriction FROM
SF135_TEMP WHERE (((SF135_TEMP.ADD)=True) And
((Forms!SF135_PREPARE_FILES02!oldgroup)=True));"
DoCmd.Close
End If

End Sub


THANK YOU!!!!!
Click to expand...
Click to expand...
 
G

Guest

The things that cause this error are:
The name of the table is misspelled
The table doesn't exist
The Link to the table (If it is linked) is not correct

Go to your Tables window and see if you can open the table manually. If you
can, then go to the VB Editor Immediate window and type in the following:
set rst = CurrentDb.OpenRecordset("COUNTER")
If that doesn't work try putting brackets arount counter
set rst = CurrentDb.OpenRecordset("[COUNTER]")


ftrujill0 said:
Klatuu,

I took your changes and updated my module and I am still getting the run
time error. Do you know of anything else that would cause that type of
error? THANKS!

Klatuu said:
Because you have space in your field name, it is getting confused. A basic
rule of naming is field names should only have letters, numbers, and the
underscore and should beging with a letter. My personal practice is to use
all upper case letters and at least one underscore. This makes them easily
distinguishable from variables. With the spaces in the names, you will need
to use brackets to make it clear to Jet what they are. There are also some
syntax errors I have correct. In addition, you did not type your function.
I assumed Long, but you may need to change it. It is not required, but that
way VBA does not have to do type coersion which takes up time.

Function Next_Custom_Counter() As Long
Dim MyDB As Database
Dim MyTable As Recordset
Dim NextCounter As Long

On Error GoTo Next_Custom_Counter_Err

Set MyDB = CurrentDb
Set MyTable = MyDB.OpenRecordset("COUNTER", dbOpenDynaset)
With MyTable
.Edit
NextCounter = ![Next Available Counter]
![Next Available Counter] = NextCounter + 1
.Update
End With
MsgBox "The selected files have been assigned to group: G " _
& Str$(NextCounter)
Next_Custom_Counter = NextCounter

End Function

ftrujill0 said:
Hi,

I'm totally new with VBA so please forgive my ignorance.... I am getting a
'Run Time Error: 7874 Microsoft Office Access can't find object; #" when
trying to call a function that creates the next value in a custom counter. It
appears to be working fine (validation is working and data is loaded into the
table) except that the run time error appears at the end when its not finding
the object and asks if I want to debug.....

I have placed the function within a sub procedure that is fired when a
submit button is clicked. The sub procedures determines if a checkbox is
checked and runs a SQL insert statement if the box is true and then calls the
counter function to update the counter value. The counter function works
fine when I call it in the onClick line (=Next_Custom_Counter() ) but not
when nested in the sub procedure.

Here is my code....

Here is the Next_Custom_Counter function Code (taken from MS website):

Option Compare Database

Function Next_Custom_Counter()
On Error GoTo Next_Custom_Counter_Err

Dim MyDB As Database
Dim MyTable As Recordset
Dim NextCounter As Long

Set MyDB = CurrentDb
Set MyTable = MyDB.OpenRecordset("COUNTER")

MyTable.Edit
NextCounter = MyTable("Next Available Counter")



MyTable("Next Available Counter") = NextCounter + 1
MyTable.Update

MsgBox "The selected files have been assigned to group: " & "G" &
Str$(NextCounter)
Next_Custom_Counter = NextCounter

Exit Function



Next_Custom_Counter_Err:
MsgBox "Error " & Err & ": " & Error$
If Err <> 0 Then Resume
End

End Function


Here is the sub procedure that I am trying to call:

Private Sub Command20_Click()

DoCmd.SetWarnings False
If newgroup = True Then
DoCmd.RunSQL "INSERT INTO SF135 ( GROUPNUMBER, FOLDERID, BOX, BOXOF,
DISPOSALDT, FRC_RESTRICTION ) SELECT [counter].[groupid] & [next available
counter] AS GroupNumber, SF135_TEMP.FOLDERID,
Format([Forms]![SF135_PREPARE_FILES02]![boxof]) AS box,
Format([Forms]![SF135_PREPARE_FILES02]![Box]) AS boxof,
Format([Forms]![SF135_PREPARE_FILES02]![DisposalDT]) AS disposaldt,
Format([Forms]![SF135_PREPARE_FILES02]![DisposalRestrictCode]) AS restriction
FROM SF135_TEMP, [Counter] WHERE (((SF135_TEMP.ADD)=True) AND
(([Forms]![SF135_PREPARE_FILES02]![newgroup])=True));"

DoCmd.OpenFunction Next_Custom_Counter()


DoCmd.Close
Else
DoCmd.RunSQL "INSERT INTO SF135 ( GROUPNUMBER, FOLDERID, BOX, BOXOF,
DISPOSALDT, FRC_RESTRICTION ) SELECT Forms!SF135_PREPARE_FILES02!groupid AS
GroupNumber2, SF135_TEMP.FOLDERID, Format(Forms!SF135_PREPARE_FILES02!boxof)
AS box, Format(Forms!SF135_PREPARE_FILES02!Box) AS boxof,
Format(Forms!SF135_PREPARE_FILES02!DisposalDT) AS disposaldt,
Format(Forms!SF135_PREPARE_FILES02!DisposalRestrictCode) AS restriction FROM
SF135_TEMP WHERE (((SF135_TEMP.ADD)=True) And
((Forms!SF135_PREPARE_FILES02!oldgroup)=True));"
DoCmd.Close
End If

End Sub


THANK YOU!!!!!
Click to expand...
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

Having problem with MultiUser Counter 1
Run-Time Error 2501 The OpenForm action was cancelled 1
Custom Numbering driving me nuts!! Help!! 5
Run Time Error 1004 7
Run-Time error 5 2
Calling a Sub Function 1
"Data type mismatch" error 4
Expression Calling Public Function In Access 2007 2

Top