help with script

[emandel]

I have a form in a MS Access 2003 database.

Each time I get a donation, I'm supposed to hit the button that says "Create Receipt". After that, I'm supposed to hit the button that says "Print Receipt" and it's supposed to give me a receipt of the most recent donation(s) i.e. of donations that were not yet receipted.

The "create Receipt" button and script is working well. However when I "print Receipt", instead of it printing the most recent donation, it prints the first receipt for that donor.

Here is the script on the form. I know nothing about programming so please give me the instructions in plain english.

Option Compare Database
Option Explicit

Private Sub btn1_Click()
 
Dim sql1 As String
Dim sql2 As String
Dim sql3 As String
 
 
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim rst3 As DAO.Recordset
Dim varnewid  As Integer
 
 
 
 
 
sql1 = "SELECT * FROM Donations LEFT JOIN Receipts ON Donations.Receipt = Receipts.ID WHERE Donations.Donor = " & Nz(Form_frmDonations2.cbx1) & " AND Receipts.ID Is Null"
sql2 = "SELECT * from RECEIPTS"
sql3 = "Select * from DONATIONS WHERE Donations.Donor = " & Nz(Form_frmDonations2.cbx1) & " and (Donations.Receipt = 0 or Donations.Receipt is null) "
 
 
 
Set rst1 = CurrentDb.OpenRecordset(sql1)
Set rst2 = CurrentDb.OpenRecordset(sql2)
Set rst3 = CurrentDb.OpenRecordset(sql3)
 
 
    If (rst1.RecordCount > 0) Then
        rst1.MoveLast
        MsgBox (rst1.RecordCount & " Donations will be assigned a new receipt")
 
        With rst2
            .AddNew
            .Fields("timestamp") = Now()
            .Update
            .MoveLast
 
            varnewid = .Fields("ID")
 
        End With
 
 
        With rst3
            Do While Not .EOF
            .Edit
            .Fields("Receipt") = varnewid
            .Update
            .MoveNext
            Loop
        End With
 
    Else
 
        MsgBox ("There are no donations available")
    End If
 
 
 
rst1.Close
rst2.Close
rst3.Close
 
Form_frmDonations2.subform2.Requery
 
 
End Sub

Private Sub cbx1_AfterUpdate()
Form_frmDonations2.subform2.Requery
 
End Sub

Private Sub btnprintpreview2_Click()
On Error GoTo Err_btnprintpreview2_Click
 
    Dim stDocName As String
 
    stDocName = "rptDonations"
    DoCmd.OpenReport stDocName, acPreview
 
Exit_btnprintpreview2_Click:
    Exit Sub
 
Err_btnprintpreview2_Click:
    MsgBox Err.Description
    Resume Exit_btnprintpreview2_Click
 
End Sub

Private Sub btnexportword2_Click()
On Error GoTo Err_btnexportword2_Click
 
    Dim stDocName As String
 
    stDocName = "mcrExportWord"
    DoCmd.RunMacro stDocName
 
Exit_btnexportword2_Click:
    Exit Sub
 
Err_btnexportword2_Click:
    MsgBox Err.Description
    Resume Exit_btnexportword2_Click
 
End Sub

Private Sub btnprintreceipt_Click()
On Error GoTo Err_btnprintreceipt_Click
 
    Dim stDocName As String
 
    stDocName = "rptDonations2"
    DoCmd.OpenReport stDocName, acPreview
 
Exit_btnprintreceipt_Click:
    Exit Sub
 
Err_btnprintreceipt_Click:
    MsgBox Err.Description
    Resume Exit_btnprintreceipt_Click
 
End Sub

Private Sub btnexportword_Click()
On Error GoTo Err_btnexportword_Click
 
    Dim stDocName As String
 
    stDocName = "mcrExportWord"
    DoCmd.RunMacro stDocName
 
Exit_btnexportword_Click:
    Exit Sub
 
Err_btnexportword_Click:
    MsgBox Err.Description
    Resume Exit_btnexportword_Click
 
End Sub

Private Sub btnopendoners_Click()
On Error GoTo Err_btnopendoners_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "Doners"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
 
    Form_Doners.Filter = "Doners.ID = " & Me.cbx1 & ""
    Form_Doners.FilterOn = True
 
 
Exit_btnopendoners_Click:
    Exit Sub
 
Err_btnopendoners_Click:
    MsgBox Err.Description
    Resume Exit_btnopendoners_Click
 
End Sub

Private Sub btnexport2word_Click()
On Error GoTo Err_btnexport2word_Click
 
Call wordreceipts
 
Exit_btnexport2word_Click:
    Exit Sub
 
Err_btnexport2word_Click:
    MsgBox Err.Description
    Resume Exit_btnexport2word_Click
 
End Sub

Private Sub Command15_Click()
On Error GoTo Err_Command15_Click
 
 
    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
 
Exit_Command15_Click:
    Exit Sub
 
Err_Command15_Click:
    MsgBox Err.Description
    Resume Exit_Command15_Click
 
End Sub

Private Sub Command16_Click()
On Error GoTo Err_Command16_Click
 
 
    DoCmd.GoToRecord , , acFirst
 
Exit_Command16_Click:
    Exit Sub
 
Err_Command16_Click:
    MsgBox Err.Description
    Resume Exit_Command16_Click
 
End Sub

[zmbd]

emandel
(...)
Here is the script on the form. I know nothing about programming so please give me the instructions in plain english.

Emandel,
You must understand, you MUST have a basic understanding of VBA before we can effectively help you.

I'll leave this thread open for a little while to see if anyone is willing to wade thru all of the code, I know that I'll take a look at what appears to be the event code behind your command button... HOWEVER this WILL take quite a while to do... so... a tad of homework for you:

>> Before Posting (VBA or SQL) Code.
>> How to ask "good" questions -- READ BEFORE SUBMITTING A QUESTION!.
>> POSTING_GUIDELINES: Please Read Carefully Before Posting to a Forum.

[Rabbit]

If it's the report that is incorrect, then we need to know what the report's source is. Not just the code that opens the report.

[emandel]

The report looks good, but has the wrong record. Where can I find the code that will dictate which record is used for the report?

[zmbd]

Press [F11] so that the Access Object Navigation Pane (AONP) is open.

You will need to locate the report group and "rptDonations2."
You will now need to open "rptDonations2" in design view. The easiest method is to right-click on the report in the AONP, when the popup menu opens, select the "Design View" option.
When the report opens, right-click on the report, anywhere except a control, when the popup menu opens, select the "Report Properties" option.
Select the Data Tab. You should see the following:
Record Source :: name of query/table and or some code.
Filter :: might be blank or may have information
Filter On Load :: default is "No"
Order BY :: might be blank or may have information
Order BY On Load :: default is "No"
Allow Filters :: default is "Yes"

We will need all of that information.
Additionally, take note of the "Record Source" value. If this is a query (most likely) we will need the SQL behind this query - to get this is much like what we did for the report...

Find the query name shown in the "Record Source" ine the ANOP.
Right-Click... select "Design View"
Once the pretty GUI for query design opens:
In the top section there is an area where the tables/queries are shown in boxes - right click on a BLANK area - not in the grided section.
In the popup menu, "SQL View"
<Ctrl<A>>, <Ctrl<C>> (windows select all, copy).
Now to post this code/sql, make sure that you click on the [CODE/] button in the formatting bar FIRST. This will insert a pair of code tags, [code]..............[/code], you will (replace the dots :) ) use the standard windows paste (<Ctrl<V>>) to place the sql between the tags.

We may also need to open the form in "Design View" to get the control name that is generating your receipt...

[emandel]

Hello and thank you for helping out.

The report properties:
Record source: qryDonationReport
filter - blank
filter on load: no
order by: blank
Order by on load: no
There is no line for Allow filters

The sql from the qryDonationReport is below

SELECT Doners.ID, Doners.Title
   , Doners.[First Name], Doners.[Last Name]
   , Doners.Address, Doners.City
   , Doners.Province, Doners.[Postal Code]
   , Donations.ID, Donations.Amount
   , Donations.Cheque, Donations.Date
   , Donations.Year, Donations.Details
   , Donations.Receipt, Receipts.timestamp
FROM Receipts 
   RIGHT JOIN (Doners 
         RIGHT JOIN Donations 
            ON Doners.ID = Donations.Donor) 
      ON Receipts.ID = Donations.Receipt
WHERE (
        (
         (Doners.ID)=[Forms]![frmDonations2]![cbx1])
          AND 
            ((Donations.Receipt)
               =[Forms]![frmDonations2]![subform2].[Form]![Receipt]
         )
      );
 

[zmbd]

In the picture you attached, it shows that the first record is selected in the subform.

Please try selecting the last record in the subform and then click on your print receipt button.

This portion of the SQL

((Donations.Receipt) 
               =[Forms]![frmDonations2]![subform2].[Form]![Receipt]

is looking at the current value of the [Receipt] field in the subform; thus, when you have the first record selected, the first record is the receipt (looks like #14? and the last record a #34?????) that should be reported...

[emandel]

Wow, all I can say is thank you thank you thank you. Am I really that stupid?! I've been braking my head on this for a couple of weeks now. You're the best.

Eli

[zmbd]

No, You are not that stupid.

The programmer should have provided better user feedback.
The form should have indicated that you should select the record...
Or
Should have indicated which record the receipt would be printed for...

The first via a simple lable control saying.. hey tigger, select the donation you need a receipt for...
The second has a dozen ways to solve; however, they all have one thing in common, there would be a control on the form that indicates which receipt will be printed.

I'm a Chemist by trade (think lab not pharm); however, we had to take quite a bit of the compsci for my program. The compsci prof was a killer on user feedback. You could get an 100% on the codeing and still fail the assignment because the user feedback failed.