VBA Access, checkboxes

[TravelingCat]

On a form with few lines, where each line has a checkbox (the same one, with one name), when i check the checkbox, how can i make the code understand that this checkbox belongs to a specific line?
Because as it is now, if for example i check the third line's checkbox the code just takes the first line, it doesn't know what line exactly i'm referencing.
In short: how can i access a checkbox of a certain line?
Thanks for any help

 

[vbaInet]

Checkbox of a certain line? Are you referring to a continuous form?

 

[TravelingCat]

Yes, the form is continuous

 

[vbaInet]

What are you trying to do with the checkbox? When you say, access it, what do you mean?

 

[TravelingCat]

What i have is this: main form (frmEvent, single form), which has several assignments in a subform (frmAssignments, continuous forms). One of controls in the subform is checkbox "assignSend". Assume i have 3 assignments for a certain event, and i want to send mail regarding the third one, so i check the checkbox of this third line, but my code doesn't understand it, it always checks the first one.
What do i need to write so it will reference data of the assignment that i actually checked?
Hope it's more clear now, and thank you for replying

 

[vbaInet]

You would need to use a recordset clone of the current recordset and loop through it to check if that checkbox has been selected. Does that give you an idea to work on?

 

[TravelingCat]

Yeah, but i have no idea how to execute it..
Maybe you could take a glimpse on my code and suggest the needed change? In bold is the problematic stuff. I realize that these variables aren't attached to anything, so it can't possibly work..

....
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT assignInCharge, workerEmail, assignNum,
assignDescription FROM tblAssignments, tblWorkers WHERE assignInCharge = workerNum
AND assignSend = -1 AND eventNum = " & strEventNum)
    If Not rs.EOF Then
        Do While Not rs.EOF
           If [B]assignIsSent[/B] = False Then
                If rs(1) & "" <> "" Then
                            'send first notification
                DoCmd.SendObject acSendNoObject, , , rs(1), , ,
"U've got assignment " & rs(2) & " " & rs(3) & " ,the event num is " 
& strEventNum & " " & strEventDescription, strFilePath & "open_app.bat", False
                [B]assignIsSent[/B] = True
                End If
                Else
                If [B]assignIsSent[/B] = True Then
                   If MsgBox("AssignInCharge already got an email,
do u wish to send a reminder?", vbYesNo) = vbYes Then
                       If rs(1) & "" <> "" Then
                                     'send reminder
                            DoCmd.SendObject acSendNoObject, , , rs(1), , ,
"Reminder: " & assignNum & " " & assignDescription & " ,event num is " & strEventNum 
& " " & Forms(Parent.Name).Controls("eventDescription"), file_path & "open_app.bat", False
                       End If
                   End If
                End If
           End If
           rs.MoveNext
        Loop
        rs.Close
        Set rs = Nothing
        Requery
    End If
    [B]assignSend[/B] = False
End Sub

 

[vbaInet]

Slight amendments.

    Dim rs As DAO.Recordset
    
    Set rs = CurrentDb.OpenRecordset("SELECT assignInCharge, workerEmail, assignNum, assignDescription FROM tblAssignments, tblWorkers WHERE assignInCharge = [COLOR=Red]workerNum[/COLOR] AND assignSend = -1 AND eventNum = " & strEventNum)
    With rs
        While Not .EOF
           If [COLOR=Red][COLOR=Black]![/COLOR]assignIsSent[/COLOR] = False Then
                If .Fields(1) & "" <> "" Then
                    'send first notification
                    DoCmd.SendObject acSendNoObject, , , rs(1), , , "U've got assignment " & rs(2) & " " & rs(3) & " ,the event num is " & strEventNum & " " & strEventDescription, [COLOR=Red]strFilePath[/COLOR] & "open_app.bat", False
                    .Edit
                    !assignIsSent = True
                    .Update
                End If
            Else
                [COLOR=Red]If MsgBox("AssignInCharge already got an email, do u wish to send a reminder?", vbYesNo) = vbYes Then[/COLOR]
                    If .Fields(1) & "" <> "" Then
                        'send reminder
                        DoCmd.SendObject acSendNoObject, , , rs(1), , , "Reminder: " & assignNum & " " & assignDescription & " ,event num is " & strEventNum & " " & Forms(Parent.Name).Controls("eventDescription"), [COLOR=Red]file_path[/COLOR] & "open_app.bat", False
                    End If
                End If
           End If
           .MoveNext
        Wend
        .Close
    End With
    Set rs = Nothing

Also notice the bits in red:

1. Is workerNum a variable? If it is you should be concatenating it to your sql string.
2. I hope the assignIsSent checkbox is also a field in your recordset
3. Is strfilepath a global variable that has already been set or has been set locally (within the routine)?
4. For the msgbox() what would happen if you they were 50 + checkboxes ticked?
5. Is file_path also a global variable or local that has already been set?

 

[TravelingCat]

I tried your code and it gave me the following error: Runtime error 3027, Cannot update. Database or object is read only.
I stumbled upon this mistake before, when i was trying to update my recordset. Is there a way to make it updatable and maybe this will solve my problem?..

workerNum is an access variable from tblWorkers; assignIsSent was not in the recordset, now i added it; strfilepath is a variable i defined within this function (sendMail) and it is set in the beginning, but that part is working fine. About the msgBox, if you send to a lot of people, it will send automatically to all those people in the loop, so i don't see a problem. At least i didn't, until you asked

 

[vbaInet]

If you're looping through the whole continous form's recordset not usethe Recordsetclone?

set rs = Me.RecordsetClone

Your SELECT statement is already filtering those marked as TRUE so maybe you want to only amend that appropriately or as mentioned earlier use the Recordsetclone.

With regards the msgbox, I was actually referring to it popping-up 50+ times hehe!

 

[TravelingCat]

First of all, thank you for staying with me this far
But i'm pretty new to this, so i'm afraid i need direct instructions.. Like you did the first time, amending my code
Did you mean i should replace my rs (Select ".......") with set rs = Me.RecordsetClone? I tried that and it doesn't throw errors but returns not what i need (i printed the content)
Otherwise, how can i set rs more than one time?

P.s., the msgbox only pops out when it's a reminder, i'm sure it will be much less than 50 at a time, so it's the least of my worries for now

 

[vbaInet]

What does it return? And what do you want it to return?

 

[TravelingCat]

Good morning
After replacement of my rs by yours (Set rs = Me.RecordsetClone), here's what's happening: i'm in event that has 4 assignments, ticking the 2 middle ones assignSend. The loop goes over all the 4, and ticks the assignIsSent, so when it's is done they're all sent, allegedly. When i print the rs content, i see that the value of "assignSend" = false, it means he doesn't recognize the condition assignSend = -1, that's why he goes over each one. How do i change that?
Also, even if it worked, i need to know workerEmail, so i could actually send, and now it's not in the rs
Thanks...

 

[TravelingCat]

And of course, i want it to return only the ones that i want to send mail to, and not all of them.

 

[TravelingCat]

Before you answer.. i think i got it working! I'm really happy, thank you so much! But if anything else pops up, as it always does, hope you will be able to help Honestly you are the only one who took the time to answer, and i asked on several forums.

 

[vbaInet]

Excellent job there TravellingCat. Glad you got it working. Do alot of testing though to be totally satisfied.

You're welcome.