Need help with an append query

[cdell7up]

Database is inconsistant on different computers. I test the database on my computers and it works great. When I email it to another tester, it will not "Add Questions To This Reviewer". I set "ALLOW ADDITIONS" to no on the property sheet for the subform. What come be the problem?

[jzwp11]

No code will work unless the database file is put in a trusted location, so the other user(s) will have to set up the folder where they put the database file as a trusted location (File-->Options-->Trust Center-->Trust Center Settings (button)-->Trusted Locations

[cdell7up]

checklistv5.31.zipThere is something wrong after I set the properties for the "Allow additions" for the frmRev form. Can you check to see if I did something wrong?

No code will work unless the database file is put in a trusted location, so the other user(s) will have to set up the folder where they put the database file as a trusted location (File-->Options-->Trust Center-->Trust Center Settings (button)-->Trusted Locations

[jzwp11]

When you click the button to add the questions to the subform, the record in the main form is not yet commited to the database, so you have to force Access to save the record. I added the code to do that in the attached database. Here is the code; the command to force the save is shown in red below:

Private Sub cmdAddQues_Click()
If IsNull(Me.cboCategory) Then
    MsgBox "You must select a category"
    Me.cboCategory.SetFocus
    Exit Sub
Else
     DoCmd.RunCommand acCmdSaveRecord
    Dim mySQL As String
    'determine if there are already answers assigned to the displayed review; if so do not add them again.  If not proceed with append query
    If DCount("*", "AnswTBL", "Reviewer=" & Me.Record_ID) = 0 Then
        mySQL = "INSERT INTO AnswTbl ( Reviewer, QuesNum )"
        mySQL = mySQL & " SELECT " & Me.Record_ID & ", QuesTbl.[QuestionNumber]"
        mySQL = mySQL & " FROM QuesTbl WHERE QuestionNumber in (SELECT fkQuestionNumber from categoryQuestionsTbl WHERE fkCat_ID =" & Me.cboCategory & ")"
        mySQL = mySQL & " ORDER by QuesTbl.[QuestionNumber]"
        CurrentDb.Execute mySQL, dbFailOnError
        'requery the subform to show the appended records
        Me.frmRevAnswers.Requery
        Me.frmRevAnswers.SetFocus
    Else
        MsgBox "Questions have already been assigned to this reviewer"
    End If
End If
End Sub

[cdell7up]

Help me to understand what was going on with this, so I can avoid it later. But thanks a million.

[jzwp11]

As long as you stay in the main form, the record is not yet saved to the database. So for the code to grab the recordID from the main form and use it in the append query you have to save the record first. This is also necessary because the recordID forms the relationship between the rev table and the answer table. Trying to add records to the answer table without the recordID value violates the referential integrity established in the relationship. If you just had a typical form/subform design, when you leave the main form and go into the subform to add records, Access automatically saves the record in the main form. Since we are using code to add the records to the table that underlies the subform, we had to force the save of the record in the main form.

[cdell7up]

I am so close now, I need help on reports. I have two reports RptNo and RptRev they seem to give me what I want. RptNo gives me any no response from the questions answered. The RptRev gives all the responese from a reviewer. My challenge is a comprehense report vs. and individual report. If I go to either report from the frmRev I get an individual report, and if I pull the report from the objects window I get the comprehensive report for all reviewers. This is great. But I want to be albe to pull the comprehensive report and have it titled differently than the individual report. Right now they both have the same title. What can I do as a solution for this? I will attach the database so you can see what I am talking about.checklistv5 4 1.zip

[jzwp11]

In order to do what you want, you have to open the report in design view, alter the title as appropriate, save it and then open it normally (either print or print preview mode). So of course whatever title change you make is what you would see the next time you would open the report from the navigation pane. You would have to control how a user opens the report at all times; in other words, you will have to deny access to the navigation pane and then control the title change via code tied to buttons on other forms. If the reports are run often this could impact database performance as the report is being redesigned each time it is opened.

Of course, you can just copy and paste the two reports as two new reports and make the appropriate title changes. You would use the original reports when you want the individual reports (filtered via your currrent form) and the new set for the comprehesive reports. Of course, if in the future you make changes to one report, you will have to make the corresponding changes to its twin.

[cdell7up]

I guess that makes sense. So another question, I want to be able to print the individual report as a pdf, and have it save the file as the contract number from the frmRev field contrk_num. I want all this to happen with a click of a button. Is this possible? if so how do I proceed. Maybe you can send me to a tutorial on a similar process.

[jzwp11]

I want to be able to print the individual report as a pdf, and have it save the file as the contract number from the frmRev field contrk_num. I want all this to happen with a click of a button. Is this possible? if so how do I proceed

Yes,it is possible although I have never done it myself. See this link for the basics of the code that you will need.

[cdell7up]

Ok I was trying to figure out how to save rptRev as a pdf file. I am not sure what I am doing wrong but if you click the command button on the frmRev you will see what message I get. Tell me what to do to get pass this.checklistv5 4 1 (2).zip

[cdell7up]

This is what I found at the link. Please help me to understand what to do here.

Private Sub SendReports_Click() Me.CurrentStatus = "Sending Reports..." & vbCrLf & vbCrLf & vbCrLf & "=========" & vbCrLf & Me.CurrentStatus
Me.Repaint
'1010_Report1
'Export to Shared drive
Dim MyFilter As String
Dim MyPath As String
Dim MyFilename As String
MyFilter = ""
MyPath = "S:\Systems\Reports\Report1\"
MyFilename = "Report1.pdf"
'Let's print and save. Once you see it works, you can change True to False so that the file created is not opened after completion.
DoCmd.OpenReport "1010_Report1", acViewPreview, , MyFilter
DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath & MyFilename, False
'Let's close our previewed report
DoCmd.Close acReport, "1010_Report1"

[jzwp11]

This what the code looks like once I adapted it to your database:

Dim MyPath As String
Dim MyFilename As String
MyPath = "c:\users\g5ctmce9\documents\"
MyFilename = Me.Record_ID & ".pdf"
    DoCmd.OpenReport "RptRev", acViewPreview, , "Record_ID=" & Me.Record_ID
    DoCmd.OutputTo acOutputReport, "RptRev", acFormatPDF, MyPath & MyFilename, True, "", 0, acExportQualityScreen
    DoCmd.Close acReport, "RptRev"

The variable MyFilename is where you need to introduce the record_ID value of the current record.

The first DoCmd opens the report filtered to the record shown on the form. It is opened in print preview mode.
The second DoCmd outputs that filtered reprot to PDF format and stores it as the file name specified in the MyFilename variable in the location specified in the MyPath variable
The third DoCmd just closes the print preview of the report so the user doesn't have to.

The amended DB is attached.

[cdell7up]

How can I make sure my reports, don't add blank pages between filled pages?

[cdell7up]

I thought I knew what I was doing by adding the category and Contract Type field to my rptRev and rptNo reports, but the results where not what I was looking for. Can you explain to me what I will need to do to add these fields to the reports.