timing issues with DCount() before/after update

[wingforward]

I have a subform that shows Email addresses. Email addresses have the option of being marked "Newsletter". Users can enter as many e-mail addresses per contact as they want, but only one can be selected as "Newsletter".

I'm trying to enforce this by using an event trigger on the field. I'm using VBA and DCount() to find the number of times that person has an e-mail addresses marked Newsletter.

Problem is DCount() doesn't return the correct number when using the before or after Update trigger. I think I need to commit the record somehow before grabbing the count, but don't know how to do that.

Access 2007.

Any help is appreciated.

Thanks,
David

 

[dcb]

Not really a help here but why not set the other associated addresses to false rather than doing a check?

 

[dcb]

I think I need to commit the record somehow before grabbing the count, but don't know how to do that.

DoCmd.RunCommand acCmdSaveRecord

 

[wingforward]

Thanks dcb, that's an easy enough command.

But I like your other idea too. I'm still new to using recordsets, but I think to do that I'd have to load the recordset using a SQL statement to find other records with the same MembershipID and then Loop through them updating each individually.

Is that right?

 

[dcb]

Its one in the morning here so I am gonna try and type this as I envisage it

rst or rstclone would work however maybe a really simple sql could work....

sql = [COLOR=Red]update youremailtable[/COLOR] Set newsletter = false 
where 
personid = " & personid & " and
emailaddress not like '" & emailaddressbox & "';"

DoCmd.runsql sql method or db.execute --- your preference