Populating a date field according to Customer name 1

[Triacona]

Dear All,

Thank you for any forthcoming help.

I have a report, an acknowledgement letter called PolAcknowledgementLetter

I have a database with Customer Name, Surname, DateAckSent fields.

The report can be printed in a batch or per customer name.

How would I populate the DateAckSent field with only the customers I print from the report?

FYI the report uses an a4 portrait per customer, it is a letter.
So 1 pg per customer.

Any help would be greatly appreciated.

Thank you,

Kind regards

Triacona

 

[dhookom]

Why would the DateAckSent field be any different from any other field? If you can print the Customer Name, why can't you print the DateAckSent?

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Dear Duane,

Thank you for your response
I think I may not have been clear...

I don't want the DateAckSent to appear in the report.

I want it to update the table CustomersMainTable field DateAckSent with the current date (Date()) which is also a field in CustomersMainTable.

Each customer has a DateAckSent field...
I know I'll need VB code behind the report, but where and what.

It is so the user will print off one or many acknowledgements, and then DateAckSent will be filled in for the null values of DateAckSent, as well as checking which Customer names where printed and only update those with the date of printing.

I hope this further explains my problem.

Thank you again for all your help!





Thank you,

Kind regards

Triacona

 

[dhookom]

I would not do this in the report. I think this all depends on how you set the filter for the report. Use the same filter to run an update query either before or after the reports have been printed.

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Dear Duane,

Thank you for your reply.
Eerm, I would not know where to start...
Could you please enlighten me as to how I create these filters and an update query?

Thank you again for all your help!

Thank you,

Kind regards

Triacona

 

[dhookom]

Can you enlighten us as to how "The report can be printed in a batch or per customer name"?

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Dear Duane,

Thanks for your response
Well the report printed in a batch is where DateAckSent IS NULL.
So the report will then print a letter with the address, name etc. for each user that has a NULL DateAckSent field.
The trick I want to do is when these have printed out, I want the DateAckSent filled in automatically for reports just printed.
So when the next batch is printed it does not have duplicates being sent out to people we have already replied to, and saves the user having to go through each record and fill this field out.

Thanks again for all your help!

Thank you,

Kind regards

Triacona

 

[dhookom]

I would run code after the report has been printed that prompts the user with MsgBox() like "Do you want to mark these records as Acknowledgement sent?"

If the user clicks yes then run an update query like:

Dim strSQL as String
strSQL = "UPDATE ...YourTableName... Set DateAckSent = Now() WHERE DateAckSent is Null"
Currentdb.Execute strSQL, dbFailOnError

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Dear Duane,

Thank you very much for your response

This has given me a few ideas, one of which I have implemented, and works.

It is the following.
In the report - PolAcknowledgementLetter
I have the following code:

Private Sub Report_Close()

Dim stDocName As String
    stDocName = "PolMsgBoxDatAckFill"

    DoCmd.OpenForm stDocName, acNormal
    
End Sub

I then created a form - PolMsgBoxDatAckFill
With the lable:
As you have printed off the acknowledgement letters do you want the all blank dates for Date Acknowledgement sent filled in?
and caption: All Blank Date Acknowlegement Sent fields to be filled in?

With the following code:

Private Sub Yes_Click()'Yes Button
On Error GoTo Err_Yes_Click

    Dim stDocName As String
        stDocName = "PolUpdateAckPrint"
 
        DoCmd.Close
        DoCmd.OpenQuery stDocName, acNormal, acEdit
    

Exit_Yes_Click:
    Exit Sub

Err_Yes_Click:
    msgbox Err.Description
    Resume Exit_Yes_Click
    
End Sub
Private Sub No_Click()'No button
On Error GoTo Err_No_Click

    DoCmd.Close

Exit_No_Click:
    Exit Sub

Err_No_Click:
    msgbox Err.Description
    Resume Exit_No_Click
    
End Sub

I then created an Update query - PolUpdateAckPrint
Fields:
DateAckSent
Update To:
Now()
Criteria
Is Null

I originally tried a VB message box but didn't get very far.

Private Sub Report_Close()

Dim msgbox As VbMsgBoxResult
Dim vbYes As object

msgbox("As you have printed off the acknowledgement letters do you want the all blank dates for Date Acknowledgement sent filled in?", vbYesNo, "Fill Blank Acknowledgement Dates") As VbMsgBoxResult

If vbYes then
   DoCmd.OpenQuery stDocName, acNormal, acEdit
else 
docmd.OpenForm "MainScreen"
End if

End Sub

The above code doesn't seem to work...
Any light on it would be great!

Thank you again for all your help!

Thank you,

Kind regards

Triacona

 

[dhookom]

Private Sub Report_Close()

  Dim msgbox As VbMsgBoxResult
  Dim strMsg as String
  Dim vbYes As object
  Dim stDocname as String
  stDocName = "PolUpdateAckPrint"
  strMsg = "As you have printed off the acknowledgement letters " & _
  "do you want the all blank dates for Date Acknowledgement sent filled in?"
  IF MsgBox(strMsg, vbYesNo, "Fill Blank Acknowledgement Dates") = vbYes Then
     DoCmd.OpenQuery stDocName, acNormal, acEdit
   else 
     docmd.OpenForm "MainScreen"
  End if

End Sub

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Dear Duane,

Thank you so very much!
The code helps me understand on how to implement and use msg boxes

I am getting one error on compile though...

Compile error: Expected array

Private Sub Report_Close()


Dim msgbox As VbMsgBoxResult
Dim strMsg As String
    strMsg = "As you have printed off the acknowledgement letters " & _
            "do you want the all blank dates for Date Acknowledgement sent filled in?"
Dim vbYes As Object
Dim stDocname As String
    stDocname = "PolUpdateAckPrint"
    
  If msgbox(strMsg, vbYesNo, "Fill Blank Acknowledgement Dates") = vbYes Then
     DoCmd.OpenQuery stDocname, acNormal, acEdit
   Else
     DoCmd.OpenForm "MainScreen"
  End If


End Sub

Any further help would be greatly appreciated!
Thank you!

Thank you,

Kind regards

Triacona

 

[dhookom]

I didn't catch your earlier mistake.

 Dim vbYes As object

I believe this vbYes is a constant so don't Dim it.

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Dear Duane,

Thanks again for your help!
As you can see below I first removed the Dim and left
vbYes as Object
This gave me the following error:

Statment invalid outside type block

I then commented outvbYes As Object
and it gave me the error:

Expected array

Private Sub Report_Close()


Dim msgbox As VbMsgBoxResult
Dim strMsg As String
    strMsg = "As you have printed off the acknowledgement letters " & _
            "do you want the all blank dates for Date Acknowledgement sent filled in?"

Dim stDocname As String
    stDocname = "PolUpdateAckPrint"
'vbYes As Object
    
  If msgbox(strMsg, vbYesNo, "Fill Blank Acknowledgement Dates") = vbYes Then
     DoCmd.OpenQuery stDocname, acNormal, acEdit
   Else
     DoCmd.OpenForm "MainScreen"
  End If


End Sub

Thank you for any forthcoming help!

Thank you,

Kind regards

Triacona

 

[dhookom]

You still have extra stuff you don't need. You must like typing ;-)

Private Sub Report_Close()
  Dim strMsg As String
  strMsg = "As you have printed off the acknowledgement letters " & _
    "do you want the all blank dates for Date Acknowledgement sent filled in?"
  Dim stDocname As String
  stDocname = "PolUpdateAckPrint"
  If msgbox(strMsg, vbYesNo, "Fill Blank Acknowledgement Dates") = vbYes Then
     DoCmd.OpenQuery stDocname, acNormal, acEdit
   Else
     DoCmd.OpenForm "MainScreen"
  End If
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[Gammachaser]

You don't need to dimension the Msgbox (you don't really need to use strMessage or strDocName either -- it just makes things easier to read.)

Just use:

Private Sub Report_Close()

Dim strMsg As String

strMsg = "As you have printed off the acknowledgement letters " & _
         "do you want the all blank dates for Date Acknowledgement sent filled in?"

If msgbox(strMsg, vbYesNo, "Fill Blank Acknowledgement Dates") = vbYes Then
   DoCmd.OpenQuery "PolUpdateAckPrint", acNormal, acEdit
Else
   DoCmd.OpenForm "MainScreen"
End If

End Sub

My guess is that you really want to go back to the "MainScreen" after you are done, whether you want to fill in the dates or not, so it should really be:

Private Sub Report_Close()

Dim strMsg As String

strMsg = "As you have printed off the acknowledgement letters " & _
         "do you want the all blank dates for Date Acknowledgement sent filled in?"

If msgbox(strMsg, vbYesNo, "Fill Blank Acknowledgement Dates") = vbYes Then
   DoCmd.OpenQuery "PolUpdateAckPrint", acNormal, acEdit
EndIf

DoCmd.OpenForm "MainScreen"

End Sub

Personally, I like Select Case better than If -ElseIf - EndIf because I find it easier to read and sort out what is happening. I would probably use:

Private Sub Report_Close()

Dim strMsg As String

strMsg = "As you have printed off the acknowledgement letters " & _
         "do you want the all blank dates for Date Acknowledgement sent filled in?"

Select Case msgbox(strMsg, vbYesNo, "Fill Blank Acknowledgement Dates")
  Case vbYes
     DoCmd.OpenQuery "PolUpdateAckPrint", acNormal, acEdit
End Select

DoCmd.OpenForm "MainScreen"

End Sub

Obviously there are many ways to get the same results... some are faster, some more 'elegant,' some easier to read & interpret, some more portable, etc. You will develop your own preferences as you learn. I recommend you use a method that is easy to interpret. You may or may not be the next one to edit it.

 

[Gammachaser]

I knew I have should have looked back to see if Duane had posted before I clicked 'Submit.'

 

[Triacona]

Dear Duane,

Thanks for your help!
I removed

Dim msgbox As VbMsgBoxResult

and it works brillaintly!
Have a star!

@Gammachaser Thank you for you input it is appreciated!
It is always helpful to look at things from several perspectives, it help broaden the mind.

Thank you all!

Thank you,

Kind regards

Triacona