Compile error: Argument not optional - Error 449

[Natasha]

Hi
I am trying to get a checkbox field in one table to equal
true based on a certain value in another table, where
applicable. The error comes up in the line reading:
....
Loop Until EOF
....
I do not know what the argument is that I'm supposed to
be using. My code is as follows:
*********************************
Option Compare Database

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim RecSet
Dim Temp1 As Boolean
Set RecSet = CurrentDb.OpenRecordset
("tblProspectInCategory")
With RecSet
.MoveFirst
Do
If CategoryName.Value = "Old Girl" Then
Temp1 = True
Else
End If

UpdateOG Temp1

.MoveNext
Loop Until EOF (error happening here)
.Close
End With

MsgBox "Finished", vbOKOnly, "Done"

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description & Temp1
Resume Exit_Command0_Click

End Sub
________________________________________________________
Function UpdateOG(OldGirl As Boolean)

Dim RecSet

Set RecSet = CurrentDb.OpenRecordset("tblProspect")
With RecSet
.MoveFirst
Do
.Edit
.OldGirl = True
.Update
Exit Do
.MoveNext
Loop Until EOF
.Close
End With
End Function

Any help would be appreciated. Thanks.

 

[Joshua A. Booker]

Natasha,

I think you're missing a dot before EOF as it is a property of the RecSet
object.

Try this:

Loop Until .EOF

HTH,
Josh

 

[Natasha]

Josh
Thanks, I have inserted the dot as you suggested and now
when I go to compile, Access highlights in blue the blank
line at the end of the Sub routine and pops up with the
error msg: Compile error - Syntax error.
I've quickly checked over spelling and haven't noticed
anything obvious to me. But then I'm not a programmer so
it doesn't take much to confuse me! Any suggestions?
Cheers, Natasha

 

[Joshua A. Booker]

Natasha,

Not sure why it highlighted the blank line, but try this:

Option Compare Database

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim db as DAO.Database
Dim RecSet as DAO.Recordset
Dim Temp1 As Boolean

Set db = CurrentDB
Set RecSet = db.OpenRecordset("tblProspectInCategory")
With RecSet
.MoveFirst
Do
If CategoryName.Value = "Old Girl" Then Temp1 = True
UpdateOG Temp1
.MoveNext
Loop Until .EOF
.Close
End With

MsgBox "Finished", vbOKOnly, "Done"

Exit_Command0_Click:
Exit Sub
Err_Command0_Click:
MsgBox Err.Description & Temp1
Resume Exit_Command0_Click
End Sub

________________________________________________________
Function UpdateOG(OldGirl As Boolean)

Dim db as DAO.Database
Dim RecSet as DAO.Recordset

Set db = CurrentDB
Set RecSet = db.OpenRecordset("tblProspect")

With RecSet
.MoveFirst
.Edit
.OldGirl = OldGirl 'True (did you want to use the oldgirl
variable that you passed?)
.Update
.Close
End With
End Function

It appears the UpdateOG function will update the first record every time.
Instead of updating the record which is related to record in
tblProspectInCategory. You may want to change the code to pass the
ProspectID along with the the OldGirl variable. I can help with this change
too if you need it.

Also, it seems like a simple update query might do this without any code.
Something like this:

UPDATE tblProspect INNER JOIN tblProspectInCategory ON
tblProspect.ProspectID = tblProspectInCategory.ProspectID SET
tblProspect.OldGirl = cbool(tblProspectInCategory.CategoryName = "OldGirl")

Assuming 'ProspectID' is the name of the Primary Key in tblProspect that
relates to tblProspectInCategory. This will update the OldGirl field in
tblProspect to true or false depending whether the the CategoryName is
"OldGirl".

PS... back up your data and test this stuff as I'm offering suggestions not
exact fixes.

HTH,
Josh

 

[Natasha]

Hi Josh
Thanks very much for that. I tried your query suggestion
and it worked but for one small issue. Out of 4,555
records with the 'Old Girl' category name, 4,528 now have
the OldGirl checkbox checked, which is great, but that
leaves 27 who were missed out.
There is a 1 to many relationship between Prospects and
Categories, i.e. a Prosepct may have 0, 1 or many
Categories that they belong to.
I think the query only looked in the first space of the
tblProspectInCategory to see if 'Old Girl' was there for
each Prospect; if another category name was there instead
and 'Old Girl' was say the 2nd or 3rd category name, it
seems to have skipped those Prospects (27 of them). I
know it's not huge in the scheme of things, but I'd
really like to pick up everyone who is an Old Girl in the
school's system.
Is there a way I can modify the query to pick up those
extra 27 records?

TIA (Thanks in advance - is that what that stands for?)
Natasha

-----Original Message-----
Natasha,

Not sure why it highlighted the blank line, but try this:

Option Compare Database

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim db as DAO.Database
Dim RecSet as DAO.Recordset
Dim Temp1 As Boolean

Set db = CurrentDB
Set RecSet = db.OpenRecordset("tblProspectInCategory")
With RecSet
.MoveFirst
Do
If CategoryName.Value = "Old Girl" Then Temp1 = True
UpdateOG Temp1
.MoveNext
Loop Until .EOF
.Close
End With

MsgBox "Finished", vbOKOnly, "Done"

Exit_Command0_Click:
Exit Sub
Err_Command0_Click:
MsgBox Err.Description & Temp1
Resume Exit_Command0_Click
End Sub

________________________________________________________
Function UpdateOG(OldGirl As Boolean)

Dim db as DAO.Database
Dim RecSet as DAO.Recordset

Set db = CurrentDB
Set RecSet = db.OpenRecordset("tblProspect")

With RecSet
.MoveFirst
.Edit
.OldGirl = OldGirl 'True (did you want to use the oldgirl
variable that you passed?)
.Update
.Close
End With
End Function

It appears the UpdateOG function will update the first record every time.
Instead of updating the record which is related to record in
tblProspectInCategory. You may want to change the code to pass the
ProspectID along with the the OldGirl variable. I can help with this change
too if you need it.

Also, it seems like a simple update query might do this without any code.
Something like this:

UPDATE tblProspect INNER JOIN tblProspectInCategory ON
tblProspect.ProspectID =

tblProspectInCategory.ProspectID SET

tblProspect.OldGirl = cbool

(tblProspectInCategory.CategoryName = "OldGirl")

 

[SteveS]

Hi, Natasha

PMFJI (Parden me for jumping in)

There is a 1 to many relationship between Prospects and
Categories, i.e. a Prosepct may have 0, 1 or many
Categories that they belong to.
I think the query only looked in the first space of the
tblProspectInCategory to see if 'Old Girl' was there for
each Prospect; if another category name was there instead
and 'Old Girl' was say the 2nd or 3rd category name, it
seems to have skipped those Prospects (27 of them).

What do you mean by "...looked in the first space.."

It sounds like your tables are not normalized. Would you post the table
structures (fields, field types, Primary Keys, Foreign Keys)?

TIA (Thanks in advance - is that what that stands for?)

Yes.


Steve

 

[Joshua A. Booker]

Natasha,

You are correct, if there is more than one record on the 'many' side of the
one-to-many, it's random as to whether it will set the flag to true or false
depending which category it checks last. You can restrict the query to just
those of the OldGirl Category but then the query will only be used to set
the OldGirl flag to true, not for setting it to false if they are removed
from the category for example. To set to false you'll need to use a second
query.

Try this:

UPDATE tblProspect INNER JOIN tblProspectInCategory ON
tblProspect.ProspectID = tblProspectInCategory.ProspectID SET
tblProspect.OldGirl = True WHERE tblProspectInCategory.CategoryName =
"OldGirl"

HTH,
Josh

 

[Natasha]

Hi Steve
By '...looked in the first space...' I just meant that it
seemed as if the query was looking only at the first
entry in the table tblProspectInCategory for the cateogry
of 'Old Girl' instead of looking at all entries in that
table for each Prospect. Does that make sense? Sorry, I
am very much a beginner programmer, so there may be a
term for what I'm trying to say, but I just don't know
it : )
Table structures are:
tblProspect:
ProspectID (Autonumber - primary key)
OldGirl (True/false Checkbox)
Name
Telephone
.... - about 40 more fields, but not relevant to this
problem.

tblProspectIn Category:
ProspectID (Concatenated primary key with CategoryName)
CategoryName (if the value here is 'Old Girl', I need the
checkbox in the above table to be true)
FromYear
ToYear
Notes

As stated in my first post, I only need to run this query
once to update the OldGirl checkbox for legacy data that
has been imported into the fundraising database. New
data that is entered from now on has code running on the
Category form that automatically checks the OldGirl
checkbox when a CategoryName = 'Old Girl', and unchecks
the box when the CateogryName is deleted. I thought I
had normalised my tables, but if there are places I
haven't there may be a reason why.

Cheers, Natasha

 

[SteveS]

Natasha,

I was wondering if the 27 records that weren't found were exactly like
"Old Girl". Were any that weren't found a different case ("old Girl",
"old girl",Old girl") ?

When doing string comparisons (field = string), I usually convert both
to upper or lower case UCase(field) = UCase("Old Girl").

Also, below is another version of code. I used 'LINKING_FIELD??'
I wasn't sure about about the linking fields between the tables....

My version..... (untested air code)
-----------------------------------
Option Compare Database
Option Explicit '<<<<<<<<< Added - helps to find code errors

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim db as DAO.Database
Dim rstCatagory as DAO.Recordset
Dim rstProspect as DAO.Recordset
Dim C_Count as Long
Dim P_Count as Long
Dim message as string

Set db = CurrentDB
Set rstCatagory = db.OpenRecordset("tblProspectInCategory")
Set rstProspect = db.OpenRecordset("tblProspect")

C_Count = 0 ' count of Category records found
P_Count = 0 ' count of Prospect records updated


With rstCatagory
.MoveFirst
Do
If .CategoryName = "Old Girl" Then

' now find the matching record in tblProspect
rstProspect.FindFirst = "ProspectID = " & LINKING_FIELD??
' matching record found
If Not rstProspect.NoMatch Then
rstProspect.OldGirl = True
' inc records modified
P_Count = P_Count+1
End If
' inc records found
C_Count = C_Count+1

End If

.MoveNext
Loop Until .EOF
End With

rstCatagory.Close
rstProspect.Close
Set rstCatagory = Nothing
Set rstProspect = Nothing
Set db = Nothing

message = "Finished" & vbcrlf & vbcrlf
message = message & C_Count & " Old Girl records found in "
message = message & "table tblProspectInCategory and" & vbcrlf
message = message & vbcrlf & P_Count & " records updated in "
message = message & "table tblProspect" & vbcrlf

MsgBox message , vbOKOnly, "Done"

Exit_Command0_Click:
Exit Sub
Err_Command0_Click:
MsgBox Err.Description & Temp1
Resume Exit_Command0_Click

End Sub