Old question but new twist.... (1 Viewer)

mgmercurio

Member
Local time
Today, 19:04
Joined
Jan 18, 2021
Messages
55
hmmmmm...nope didn't like that either....
 

Attachments

  • 17.PNG
    17.PNG
    17.4 KB · Views: 132

mgmercurio

Member
Local time
Today, 19:04
Joined
Jan 18, 2021
Messages
55
it compiles...but it does throw one error regarding an email button...but it is totally unrelated
 

mgmercurio

Member
Local time
Today, 19:04
Joined
Jan 18, 2021
Messages
55
Ok I found the issue. It was a typo. I had placed =fncUpdateTblAttachment() in the AfterUpdate Control. The correct code is =fncUpdateTblAttachments()

It works...however, I am getting one error message when I "uncheck" the box.

1611599196000.png


I am suspecting that it has something to do with the number of iterations within the function.

I have 5 checkboxes total and I believe Anrelgp said it was built for only 4. I did make the following adjustment to the function hoping to clear it up...but it did not work.

Dim i As Integer
'the sample also assume you have 4 checkbox
For i = 1 To 5 Step 1
Me("Chkbox" & Format(i, "00")) = DLookup("AttachFile", "tbl_Attachments", "AttachmentID = " & i)
Next

Thoughts?
-mgm
 

bastanu

AWF VIP
Local time
Today, 16:04
Joined
Apr 13, 2010
Messages
1,401
Try:
Code:
Me.Dirty=False
Call fncUpdateTblAttachments()
Me.Requery
Cheers,
 

mgmercurio

Member
Local time
Today, 19:04
Joined
Jan 18, 2021
Messages
55
Ill give it a shot....stand by....
Vlad, This didn't work...I tried it in the AfterUpdate code and also tried putting it in the function....still didnt work. I can tell you that it is definitely has something to do with the number of checkboxes and the Function code. I also tried many variations in the function as well as changing the chkbox names...some examples below....nothing worked.

The first snipit is original:
Dim i As Integer
'the sample also assume you have 4 checkbox
For i = 1 To 4 Step 1
Me("Chkbox" & Format(i, "00")) = DLookup("AttachFile", "tbl_Attachments", "AttachmentID = " & i)

Next


Dim i As Integer
'the sample also assume you have 4 checkbox
For i = 0 To 4 Step 1 <-----Also tried 1 to 5 Step 1
Me("Chkbox" & Format(i, "00")) = DLookup("AttachFile", "tbl_Attachments", "AttachmentID = " & i) <------ Tried many combinations of 00 i.e. 01, 11, 1, etc....

Next

Tried changing the chkBox names to chkbox00,chkbox01,chkbox02,chkbox03,chkbox04 or chkbox11, chkbox21, chkbox31, chkbox41, chkbox51

Nothing worked here either.

I realize my attempts to fix it was just completely off track...but not knowing how to code made me just take shots in the dark at it.

However, I do understand it does have something to do with the iterations and number of checkboxes. I also understand Anrelgp's code is calculating the number of boxes starting at 00....I just don't know what the calculation is....

-mgm
 

bastanu

AWF VIP
Local time
Today, 16:04
Joined
Apr 13, 2010
Messages
1,401
Vlad, This didn't work...I tried it in the AfterUpdate code and also tried putting it in the function....still didnt work. I can tell you that it is definitely has something to do with the number of checkboxes and the Function code. I also tried many variations in the function as well as changing the chkbox names...some examples below....nothing worked.

The first snipit is original:
Dim i As Integer
'the sample also assume you have 4 checkbox
For i = 1 To 4 Step 1
Me("Chkbox" & Format(i, "00")) = DLookup("AttachFile", "tbl_Attachments", "AttachmentID = " & i)

Next


Dim i As Integer
'the sample also assume you have 4 checkbox
For i = 0 To 4 Step 1 <-----Also tried 1 to 5 Step 1
Me("Chkbox" & Format(i, "00")) = DLookup("AttachFile", "tbl_Attachments", "AttachmentID = " & i) <------ Tried many combinations of 00 i.e. 01, 11, 1, etc....

Next

Tried changing the chkBox names to chkbox00,chkbox01,chkbox02,chkbox03,chkbox04 or chkbox11, chkbox21, chkbox31, chkbox41, chkbox51

Nothing worked here either.

I realize my attempts to fix it was just completely off track...but not knowing how to code made me just take shots in the dark at it.

However, I do understand it does have something to do with the iterations and number of checkboxes. I also understand Anrelgp's code is calculating the number of boxes starting at 00....I just don't know what the calculation is....

-mgm
I don't think it has anything to do with looping and/or iterations, the error is caused by Access seeing that the displayed data has been changed by another process (the update statement in the function). Try please to remove the code you have in the Form Load event (which is the one that initially populates the checkboxes and add the lookup expressions you had for each of the checkbox control sources. In the AfterUpdate events leave the latest code I've sent you (the one with me.requery at the end).
Cheers
Vlad
 

mgmercurio

Member
Local time
Today, 19:04
Joined
Jan 18, 2021
Messages
55
ahhhh now we are back to the check box being bound to the record and uneditable
 

bastanu

AWF VIP
Local time
Today, 16:04
Joined
Apr 13, 2010
Messages
1,401
Sorry, my bad, forgot those were the unbound checkboxes used to edit the records. Lets try this:

1.Make the checkboxes unbound again (sorry)

2.In the form's module add this sub:
Code:
Private Sub arlRefreshCheckBoxes()
Dim i As Integer
For i = 1 To 5 Step 1 'adjust for your checkbox names and field
    Me("Chkbox" & Format(i, "00")) = DLookup("TheCheckboxField", "tbl_Attachments", "AttachmentID = " & i)
Next

3.In the Form's Load Event add:
Code:
Call arlRefreshCheckBoxes

4.In the AfterUpdate events have:
Code:
Me.Dirty=False
Call fncUpdateTblAttachments()
Call arlRefreshCheckBoxes

Please let me know how you make out. Ideally if you could prepare a sample db with just this form and the attacnments table and upload it here it could be much easier to help.

Cheers,
 

mgmercurio

Member
Local time
Today, 19:04
Joined
Jan 18, 2021
Messages
55
*****UPDATE*******
It works. Three things fixed it.

1. I adjusted Arnelgp's function code from:
Dim i As Integer
'the sample also assume you have 4 checkbox
For i = 1 To 4 Step 1
Me("Chkbox" & Format(i, "00")) = DLookup("AttachFile", "tbl_Attachments", "AttachmentID = " & i)

Next

to read

Dim i As Integer
'the sample also assume you have 4 checkbox
For i = 1 to 5 Step 1
Me("Chkbox" & Format(i, "00")) = DLookup("AttachFile", "tbl_Attachments", "AttachmentID = " & i)

Next

2. I went back in and removed all the DLookup references and put Arnelgp's code back into the OnLoad Property.


3. Then I added VLad's requery code:
Me.Dirty = False
Call fncUpdateTblAttachments
Me.Requery

to each of the chkbox01-05 afterupdate control property.

It is now working.

Next task is to follow through with Arnelgp and Vlad's instructions to set OutlookAutomation, get the recordset of all the YES/NO flags (focusing on the YES's), loop through the mutilist box and gather all the email addresses clicked then and attach the PDF's to the email.

Thank you! EVERYONE for your input...it has been invaluable!
-mgm
 

mgmercurio

Member
Local time
Today, 19:04
Joined
Jan 18, 2021
Messages
55
Sorry, my bad, forgot those were the unbound checkboxes used to edit the records. Lets try this:

1.Make the checkboxes unbound again (sorry)

2.In the form's module add this sub:
Code:
Private Sub arlRefreshCheckBoxes()
Dim i As Integer
For i = 1 To 5 Step 1 'adjust for your checkbox names and field
    Me("Chkbox" & Format(i, "00")) = DLookup("TheCheckboxField", "tbl_Attachments", "AttachmentID = " & i)
Next

3.In the Form's Load Event add:
Code:
Call arlRefreshCheckBoxes

4.In the AfterUpdate events have:
Code:
Me.Dirty=False
Call fncUpdateTblAttachments()
Call arlRefreshCheckBoxes

Please let me know how you make out. Ideally if you could prepare a sample db with just this form and the attacnments table and upload it here it could be much easier to help.

Cheers,
Sorry Vlad...I am just now seeing you had posted this earlier! I made an update above and it is now working. Thank you for everything brother...I certainly appreciate it ! -mgm
 

bastanu

AWF VIP
Local time
Today, 16:04
Joined
Apr 13, 2010
Messages
1,401
Do the checkboxes get updated in the form after you run the updated function? I would recommend trying my latest suggestion as it would force a refresh of the checkboxes after the update.

Cheers,
Vlad
 

mgmercurio

Member
Local time
Today, 19:04
Joined
Jan 18, 2021
Messages
55
Do the checkboxes get updated in the form after you run the updated function? I would recommend trying my latest suggestion as it would force a refresh of the checkboxes after the update.

Cheers,
Vlad
Yes they do update. But I will still implement your code if you think I should.
 

mgmercurio

Member
Local time
Today, 19:04
Joined
Jan 18, 2021
Messages
55
Sorry, my bad, forgot those were the unbound checkboxes used to edit the records. Lets try this:

1.Make the checkboxes unbound again (sorry)

2.In the form's module add this sub:
Code:
Private Sub arlRefreshCheckBoxes()
Dim i As Integer
For i = 1 To 5 Step 1 'adjust for your checkbox names and field
    Me("Chkbox" & Format(i, "00")) = DLookup("TheCheckboxField", "tbl_Attachments", "AttachmentID = " & i)
Next

3.In the Form's Load Event add:
Code:
Call arlRefreshCheckBoxes

4.In the AfterUpdate events have:
Code:
Me.Dirty=False
Call fncUpdateTblAttachments()
Call arlRefreshCheckBoxes

Please let me know how you make out. Ideally if you could prepare a sample db with just this form and the attacnments table and upload it here it could be much easier to help.

Cheers,
Im getting a compile error stating function or sub is not defined with regards to Call arlRefreshCheckBoxes
 

bastanu

AWF VIP
Local time
Today, 16:04
Joined
Apr 13, 2010
Messages
1,401
Do you have it in the form's module (notice the Private in the declaration)? It cannot go in a standard module as Arnel's did.
 

mgmercurio

Member
Local time
Today, 19:04
Joined
Jan 18, 2021
Messages
55
I made a new module call Private_Module and put it in there and I still get the sub/function not defined error. And...I was wrong earlier...it is NOT updating the the form like I thought originally.

I have attached screenshots....I am working on creating a sandbox database and I will upload it. ;)
 

Attachments

  • 19.PNG
    19.PNG
    58.1 KB · Views: 146
  • 20.PNG
    20.PNG
    89.6 KB · Views: 127

Users who are viewing this thread

Top Bottom