Object required error

G

Guest

Hi

I have a function designed to provide new values for PupilID field where
there are duplicates. It works fine until I add the Do...Loop.
With the loop I get an object required error for the counter.
Any ideas please on fixing this error.
I've also tried using a recordset to run the function but then got an
endless loop!
--------------------------------
Function UpdateNewGuestPupilID() As Integer
On Error GoTo UpdateNewGuestPupilID_Err

Dim counter As Integer

' qryCountDuplicateGuestPupilID
DoCmd.OpenQuery "qryCountDuplicateGuestPupilID", acViewNormal, acEdit

counter = [qryCountDuplicateGuestPupilID]!CountOfPupilID

Do Until counter < 2
' Find duplicates for GuestPupilUPN
DoCmd.OpenQuery "Find duplicates for GuestPupilUPN", acViewNormal, acEdit
' qryCheckMaxGuestPupilID
DoCmd.OpenQuery "qryCheckMaxGuestPupilID", acViewNormal, acEdit
' qryLastGuestPupilDuplicatePupilID
DoCmd.OpenQuery "qryLastGuestPupilDuplicatePupilID", acViewNormal, acEdit
' qryUpdateLastGuestPupilID
DoCmd.OpenQuery "qryUpdateLastGuestPupilID", acViewNormal, acEdit
' qryUpdateLastGuestPupil
DoCmd.OpenQuery "qryUpdateLastGuestPupil", acViewNormal, acEdit
counter = counter - 1
Loop

UpdateNewGuestPupilID_Exit:
Exit Function

UpdateNewGuestPupilID_Err:
MsgBox Error$
Resume UpdateNewGuestPupilID_Exit

End Function
 
G

Guest

Hi ridders

Your coding seems over complex by using SQL queries.

I would use DAO recordsets along the lines of:

Set rst = CurrentDB.OpenRecordset("SELECT...your find duplicates SQL goes
here....")

With rst
Do Until .EOF
.Edit
.....your coding to amend duplicate entries goes here
..... call the fields using !field1 etc.
.Update
.MoveNext
Loop
End With

You say you went into an endless loop. This is almost certain because you
didn't have the .MoveNext statement resulting in the loop continuously
processing the same record.

Hope this helps.
 
G

Guest

hi there

You are right. I left out the MoveNext ...Oops!
However before receiving your reply I fixed it as follows:

DLookup used for initial Counter value
Do until Loop stops when Counter =1 (i.e. no duplicates).
However, error with Null value in situation where no duplicates in first
place.
Solved by If Counter>0 before Do loop!

Not very elegant but it works. Amended code below
However, will retry recordset solution later
Thanks for your advice
--------------------------------------
Function AssignNewGuestPupilID()
On Error GoTo AssignNewGuestPupilID_Err

Dim Counter

'Assigns unique PupilID to all new guest pupils in table GuestPupilUPN

'Count number of new guest pupils with duplicate PupilID
Counter = DLookup("CountOfPupilID", "qryCountDuplicateGuestPupilID")
'If counter > 0, run procedure to reassign each with unique PupilID
If Counter > 0 Then
Do Until Counter = 1
'Check maximum value of guest pupilID
DoCmd.OpenQuery "qryCheckMaxGuestPupilID", acViewNormal, acEdit
'qryLastGuestPupilDuplicatePupilID
DoCmd.OpenQuery "qryLastGuestPupilDuplicatePupilID", acViewNormal,
acEdit
'qryUpdateLastGuestPupilID
DoCmd.OpenQuery "qryUpdateLastGuestPupilID", acViewNormal, acEdit
'Update PupilID in table GuestPupilUPN
DoCmd.OpenQuery "qryUpdateLastGuestPupil", acViewNormal, acEdit
Counter = Counter - 1
Loop
End If

AssignNewGuestPupilID_Exit:
Exit Function

AssignNewGuestPupilID_Err:
MsgBox Error$
Resume AssignNewGuestPupilID_Exit

End Function

BeWyched said:
Hi ridders

Your coding seems over complex by using SQL queries.

I would use DAO recordsets along the lines of:

Set rst = CurrentDB.OpenRecordset("SELECT...your find duplicates SQL goes
here....")

With rst
Do Until .EOF
.Edit
.....your coding to amend duplicate entries goes here
..... call the fields using !field1 etc.
.Update
.MoveNext
Loop
End With

You say you went into an endless loop. This is almost certain because you
didn't have the .MoveNext statement resulting in the loop continuously
processing the same record.

Hope this helps.



ridders said:
Hi

I have a function designed to provide new values for PupilID field where
there are duplicates. It works fine until I add the Do...Loop.
With the loop I get an object required error for the counter.
Any ideas please on fixing this error.
I've also tried using a recordset to run the function but then got an
endless loop!
--------------------------------
Function UpdateNewGuestPupilID() As Integer
On Error GoTo UpdateNewGuestPupilID_Err

Dim counter As Integer

' qryCountDuplicateGuestPupilID
DoCmd.OpenQuery "qryCountDuplicateGuestPupilID", acViewNormal, acEdit

counter = [qryCountDuplicateGuestPupilID]!CountOfPupilID

Do Until counter < 2
' Find duplicates for GuestPupilUPN
DoCmd.OpenQuery "Find duplicates for GuestPupilUPN", acViewNormal, acEdit
' qryCheckMaxGuestPupilID
DoCmd.OpenQuery "qryCheckMaxGuestPupilID", acViewNormal, acEdit
' qryLastGuestPupilDuplicatePupilID
DoCmd.OpenQuery "qryLastGuestPupilDuplicatePupilID", acViewNormal, acEdit
' qryUpdateLastGuestPupilID
DoCmd.OpenQuery "qryUpdateLastGuestPupilID", acViewNormal, acEdit
' qryUpdateLastGuestPupil
DoCmd.OpenQuery "qryUpdateLastGuestPupil", acViewNormal, acEdit
counter = counter - 1
Loop

UpdateNewGuestPupilID_Exit:
Exit Function

UpdateNewGuestPupilID_Err:
MsgBox Error$
Resume UpdateNewGuestPupilID_Exit

End Function
Click to expand...
Click to expand...
 
G

Guest

Hi

If you do look to the recordset solution, it looks like you could simplfy
quite a bit of the coding. I am correct in satying that PupilID is an
Integer, and, if you have duplicates you want to re-assign the duplicates
with the next ID number.

If so then you could exctract the recordset using

rst = CurrentDB.Openrecordset("SELECT PupilID, Count(ID) AS CountOfID FROM
GuestPupilUPN GROUP BY PupilID HAVING (Count(ID))>1")

this assumes you aklso have a field called ID in the table. If not just
substitute any other field other than PupilID. The>1 will ensure only entries
with duplicate fields are found.

You could identify the maximum value of the whole table using
MaxID = DMax("PupilID", "GuestPupilUPN")

The spin around the recordset and add 1, 2 etc. to the maximum.

Something like:

Set rst = CurrentDB.Openrecordset("SELECT PupilID, Count(ID) AS CountOfID
FROM GuestPupilUPN GROUP BY PupilID HAVING (Count(ID))>1")

With rst
Do Until .EOF
Set rst2 = CurrentDB.Openrecordset("SELECT PupilID FROM GuestPupilUPN
WHERE PupilID = " & .PupilID)
MaxID = DMax("PupilID", "GuestPupilUPN")
rst2.MoveNext ' ensures the first of the
duplicate set is not changed
Do Until rst2.EOF
rst2.edit
rst2!PupilID = MaxID + 1
rst2.Update
rst2.Movenext
MaxID = MaxID + 1
Loop
.MoveNext
Loop


This would be more 'elegant' and, more importantly, keeps all the coding
within the module as it doesn't use external queries.
Cheers.

BW

ridders said:
hi there

You are right. I left out the MoveNext ...Oops!
However before receiving your reply I fixed it as follows:

DLookup used for initial Counter value
Do until Loop stops when Counter =1 (i.e. no duplicates).
However, error with Null value in situation where no duplicates in first
place.
Solved by If Counter>0 before Do loop!

Not very elegant but it works. Amended code below
However, will retry recordset solution later
Thanks for your advice
--------------------------------------
Function AssignNewGuestPupilID()
On Error GoTo AssignNewGuestPupilID_Err

Dim Counter

'Assigns unique PupilID to all new guest pupils in table GuestPupilUPN

'Count number of new guest pupils with duplicate PupilID
Counter = DLookup("CountOfPupilID", "qryCountDuplicateGuestPupilID")
'If counter > 0, run procedure to reassign each with unique PupilID
If Counter > 0 Then
Do Until Counter = 1
'Check maximum value of guest pupilID
DoCmd.OpenQuery "qryCheckMaxGuestPupilID", acViewNormal, acEdit
'qryLastGuestPupilDuplicatePupilID
DoCmd.OpenQuery "qryLastGuestPupilDuplicatePupilID", acViewNormal,
acEdit
'qryUpdateLastGuestPupilID
DoCmd.OpenQuery "qryUpdateLastGuestPupilID", acViewNormal, acEdit
'Update PupilID in table GuestPupilUPN
DoCmd.OpenQuery "qryUpdateLastGuestPupil", acViewNormal, acEdit
Counter = Counter - 1
Loop
End If

AssignNewGuestPupilID_Exit:
Exit Function

AssignNewGuestPupilID_Err:
MsgBox Error$
Resume AssignNewGuestPupilID_Exit

End Function

BeWyched said:
Hi ridders

Your coding seems over complex by using SQL queries.

I would use DAO recordsets along the lines of:

Set rst = CurrentDB.OpenRecordset("SELECT...your find duplicates SQL goes
here....")

With rst
Do Until .EOF
.Edit
.....your coding to amend duplicate entries goes here
..... call the fields using !field1 etc.
.Update
.MoveNext
Loop
End With

You say you went into an endless loop. This is almost certain because you
didn't have the .MoveNext statement resulting in the loop continuously
processing the same record.

Hope this helps.



ridders said:
Hi

I have a function designed to provide new values for PupilID field where
there are duplicates. It works fine until I add the Do...Loop.
With the loop I get an object required error for the counter.
Any ideas please on fixing this error.
I've also tried using a recordset to run the function but then got an
endless loop!
--------------------------------
Function UpdateNewGuestPupilID() As Integer
On Error GoTo UpdateNewGuestPupilID_Err

Dim counter As Integer

' qryCountDuplicateGuestPupilID
DoCmd.OpenQuery "qryCountDuplicateGuestPupilID", acViewNormal, acEdit

counter = [qryCountDuplicateGuestPupilID]!CountOfPupilID

Do Until counter < 2
' Find duplicates for GuestPupilUPN
DoCmd.OpenQuery "Find duplicates for GuestPupilUPN", acViewNormal, acEdit
' qryCheckMaxGuestPupilID
DoCmd.OpenQuery "qryCheckMaxGuestPupilID", acViewNormal, acEdit
' qryLastGuestPupilDuplicatePupilID
DoCmd.OpenQuery "qryLastGuestPupilDuplicatePupilID", acViewNormal, acEdit
' qryUpdateLastGuestPupilID
DoCmd.OpenQuery "qryUpdateLastGuestPupilID", acViewNormal, acEdit
' qryUpdateLastGuestPupil
DoCmd.OpenQuery "qryUpdateLastGuestPupil", acViewNormal, acEdit
counter = counter - 1
Loop

UpdateNewGuestPupilID_Exit:
Exit Function

UpdateNewGuestPupilID_Err:
MsgBox Error$
Resume UpdateNewGuestPupilID_Exit

End Function
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi

Thanks for this.
I won't be able to try it out for a few days but it all makes sense & I
should be able to adapt if it isn't quite right

Thanks for your time on this

BeWyched said:
Hi

If you do look to the recordset solution, it looks like you could simplfy
quite a bit of the coding. I am correct in satying that PupilID is an
Integer, and, if you have duplicates you want to re-assign the duplicates
with the next ID number.

If so then you could exctract the recordset using

rst = CurrentDB.Openrecordset("SELECT PupilID, Count(ID) AS CountOfID FROM
GuestPupilUPN GROUP BY PupilID HAVING (Count(ID))>1")

this assumes you aklso have a field called ID in the table. If not just
substitute any other field other than PupilID. The>1 will ensure only entries
with duplicate fields are found.

You could identify the maximum value of the whole table using
MaxID = DMax("PupilID", "GuestPupilUPN")

The spin around the recordset and add 1, 2 etc. to the maximum.

Something like:

Set rst = CurrentDB.Openrecordset("SELECT PupilID, Count(ID) AS CountOfID
FROM GuestPupilUPN GROUP BY PupilID HAVING (Count(ID))>1")

With rst
Do Until .EOF
Set rst2 = CurrentDB.Openrecordset("SELECT PupilID FROM GuestPupilUPN
WHERE PupilID = " & .PupilID)
MaxID = DMax("PupilID", "GuestPupilUPN")
rst2.MoveNext ' ensures the first of the
duplicate set is not changed
Do Until rst2.EOF
rst2.edit
rst2!PupilID = MaxID + 1
rst2.Update
rst2.Movenext
MaxID = MaxID + 1
Loop
.MoveNext
Loop


This would be more 'elegant' and, more importantly, keeps all the coding
within the module as it doesn't use external queries.
Cheers.

BW

ridders said:
hi there

You are right. I left out the MoveNext ...Oops!
However before receiving your reply I fixed it as follows:

DLookup used for initial Counter value
Do until Loop stops when Counter =1 (i.e. no duplicates).
However, error with Null value in situation where no duplicates in first
place.
Solved by If Counter>0 before Do loop!

Not very elegant but it works. Amended code below
However, will retry recordset solution later
Thanks for your advice
--------------------------------------
Function AssignNewGuestPupilID()
On Error GoTo AssignNewGuestPupilID_Err

Dim Counter

'Assigns unique PupilID to all new guest pupils in table GuestPupilUPN

'Count number of new guest pupils with duplicate PupilID
Counter = DLookup("CountOfPupilID", "qryCountDuplicateGuestPupilID")
'If counter > 0, run procedure to reassign each with unique PupilID
If Counter > 0 Then
Do Until Counter = 1
'Check maximum value of guest pupilID
DoCmd.OpenQuery "qryCheckMaxGuestPupilID", acViewNormal, acEdit
'qryLastGuestPupilDuplicatePupilID
DoCmd.OpenQuery "qryLastGuestPupilDuplicatePupilID", acViewNormal,
acEdit
'qryUpdateLastGuestPupilID
DoCmd.OpenQuery "qryUpdateLastGuestPupilID", acViewNormal, acEdit
'Update PupilID in table GuestPupilUPN
DoCmd.OpenQuery "qryUpdateLastGuestPupil", acViewNormal, acEdit
Counter = Counter - 1
Loop
End If

AssignNewGuestPupilID_Exit:
Exit Function

AssignNewGuestPupilID_Err:
MsgBox Error$
Resume AssignNewGuestPupilID_Exit

End Function

BeWyched said:
Hi ridders

Your coding seems over complex by using SQL queries.

I would use DAO recordsets along the lines of:

Set rst = CurrentDB.OpenRecordset("SELECT...your find duplicates SQL goes
here....")

With rst
Do Until .EOF
.Edit
.....your coding to amend duplicate entries goes here
..... call the fields using !field1 etc.
.Update
.MoveNext
Loop
End With

You say you went into an endless loop. This is almost certain because you
didn't have the .MoveNext statement resulting in the loop continuously
processing the same record.

Hope this helps.



:

Hi

I have a function designed to provide new values for PupilID field where
there are duplicates. It works fine until I add the Do...Loop.
With the loop I get an object required error for the counter.
Any ideas please on fixing this error.
I've also tried using a recordset to run the function but then got an
endless loop!
--------------------------------
Function UpdateNewGuestPupilID() As Integer
On Error GoTo UpdateNewGuestPupilID_Err

Dim counter As Integer

' qryCountDuplicateGuestPupilID
DoCmd.OpenQuery "qryCountDuplicateGuestPupilID", acViewNormal, acEdit

counter = [qryCountDuplicateGuestPupilID]!CountOfPupilID

Do Until counter < 2
' Find duplicates for GuestPupilUPN
DoCmd.OpenQuery "Find duplicates for GuestPupilUPN", acViewNormal, acEdit
' qryCheckMaxGuestPupilID
DoCmd.OpenQuery "qryCheckMaxGuestPupilID", acViewNormal, acEdit
' qryLastGuestPupilDuplicatePupilID
DoCmd.OpenQuery "qryLastGuestPupilDuplicatePupilID", acViewNormal, acEdit
' qryUpdateLastGuestPupilID
DoCmd.OpenQuery "qryUpdateLastGuestPupilID", acViewNormal, acEdit
' qryUpdateLastGuestPupil
DoCmd.OpenQuery "qryUpdateLastGuestPupil", acViewNormal, acEdit
counter = counter - 1
Loop

UpdateNewGuestPupilID_Exit:
Exit Function

UpdateNewGuestPupilID_Err:
MsgBox Error$
Resume UpdateNewGuestPupilID_Exit

End Function
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

Run Time Error #3021 1
VBA Code 1
object required 1
run-time error 2467 2
Access Module 15
Error Trapping Problem 2
Constructive advice wanted 5
How to call a Module. 2

Top