Access 2003 print preview doesn't show correct records

I have a form with a print preview command button on it that takes me to a report.  When I select a record and click on the button to see the report, the report shows other records instead of the one I selected.  Obviously, I don't know how to link the records of the form to the report.  Any and all help will be appreciated.

The code that the Wizard created is below.
Kelly
Private Sub ExternalReport_Click()
On Error GoTo Err_ExternalReport_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
    
    stDocName = "ExternalTraining"
    
    DoCmd.OpenReport stDocName, acPreview
 
Exit_ExternalReport_Click:
    Exit Sub
 
Err_ExternalReport_Click:
    MsgBox Err.Description
    Resume Exit_ExternalReport_Click
    
End Sub

Open in new window

mschmidt14Asked:
Who is Participating?
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
RodgerDjr is correct,

Don't you have a EmployeeID Numerical field as the key field?
You should be using that.

If you don't, I stongly reccomend that you add a Numeric Key field (Autonumber) in as the Primary key.
1. For the reason RodgerDjr mentioned (Most important)
2. It is far easier to refer to Number than it is to refer to Text in VBA code.

If you have text, you will have to use something like this:
    DoCmd.OpenReport stDocName, acViewPreview, , "EmployeeName=" & "'" & Me.EmployeeName & "'"

(Me.Employee surrounded by DoubleQuote,SingleQuote,DoubleQuote)
It gets more complex because even this will not work for names containing apostrophes, like O'Donell.
So get that number Key in there!
;-)

Jeff


0
 
RodgerSystems AnalystCommented:
So on your report do you have the record sourse set to one record?  Check that other wise no matter what we do you will always get the same record.
What you want to do is to open the record where the ID is equal to the ID of the form you are on.
Something like Customer_ID = me.Customer_ID
The above will filter the Customer_ID and get the other side will get the current ID.
Does this help?
0
 
Jeffrey CoachmanMIS LiasonCommented:
mschmidt14,

Unfortunately, the Button wizard will not create the Filter Criteria for you.

Unless you will be using the Report Name and the Criteria again in the same sub, I reccommend keeping it simple, something like this:

Private Sub cmdViewReport_Click()
    'Save the Current Record in case of any changes
    DoCmd.RunCommand acCmdSaveRecord
    'Preview the Report Synchronized to the Selected (current) Form Record
    DoCmd.OpenReport "rptDetailVideos", acViewPreview, , "MovieID=" & Me.MovieID
End Sub

Here is a sample, see the form named:  frmDetailVideos

I am sure you can adapt this this to work in your database.

;-)

JeffCoachman
Access-EEQ24338897-Report-Hide-P.mdb
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
mschmidt14Author Commented:
I tried both of the above solutions together and now I am getting this error message:

syntax error(missing operator) in query expression '(EmployeeName=KellyHofman)'

I am Kelly Hofman and am using myself for testing.  I will attach what code I have now to see if that will help any further.  I do have the fields of my form and my report looking to the record source.

Thanks,
Kelly
Private Sub ExternalReport_Click()
On Error GoTo Err_ExternalReport_Click
 
    Dim stDocName As String
        
    stDocName = "ExternalTraining"
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.OpenReport stDocName, acViewPreview, , "EmployeeName=" & Me.EmployeeName
    
Exit_ExternalReport_Click:
    Exit Sub
 
Err_ExternalReport_Click:
    MsgBox Err.Description
    Resume Exit_ExternalReport_Click
    
End Sub

Open in new window

0
 
RodgerSystems AnalystCommented:
I would not recommend you use the persons name what if you have two that are the same.  If you use Jeff's example and just use the ID  you have the right idea just use the ID.
0
 
mschmidt14Author Commented:
I do have an employee id but this form is for the end user to search by employee and they may or may not know the employee id.  I will try to rework it using a employee id/employee name combination field.

Thanks for your help.
0
 
Jeffrey CoachmanMIS LiasonCommented:
mschmidt14,

<the end user to search by employee and they may or may not know the employee id.>
They will not need to know, or even see, the ID Field.

You can use a combobox that can search by the Name, yet reference the ID Field (behind the scenes)

JeffCoachman
0
 
mschmidt14Author Commented:
How do you do that?  I was using a combobox to search by the Name.  The table is setup with the employee id as the key and I tried coding the command preview button as shown above, using employee id instead of employee name but that still didn't work for me.  I'm getting extremely frustrated here.  :-)

I have attached the code, again.  

Am I missing something somewhere else?
Thanks again,
Kelly
Private Sub InternalTraining_Click()
On Error GoTo Err_InternalTraining_Click
 
        
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.OpenReport "InternalTraining", acViewPreview, , "EmployeeNo=" & Me.EmployeeNo
    
 
Exit_InternalTraining_Click:
    Exit Sub
 
Err_InternalTraining_Click:
    MsgBox Err.Description
    Resume Exit_InternalTraining_Click
    
End Sub

Open in new window

0
 
Jeffrey CoachmanMIS LiasonCommented:
First you have to tell me what you mean by "Search"?

Do you want to "Filter" for the selected Employee, or do you just want to Select (Find) that employee.

You can use either method to print the Data.

If you just want a combobox to select and employee and Print their record, you can use something similar to the attached sample.
Study the Table, The form, the code on the from, and the combobox properties.

But again, I cannot see your form, nor do I know it's purpose.
So if this is not what you want, then you will have to post a sample.

JeffCoachman

0
 
Jeffrey CoachmanMIS LiasonCommented:
0
 
mschmidt14Author Commented:
Yours is close but to what I want to do but not quite.

I have an Employee table, Training table, Classes table, InternalTraining table and ExternalTraining table.  The employee table just has the employee info in it.  The classes table has the type of class(internal or external), and the class name in it.  And the InternalTraining and ExternalTraining has all the info needed when those types of training are utilized.

The form has a combobox that the end user will chose an employee and then clicks on the print preview button, which brings up a report that shows which classes that person took.  

Is it because there are several tables involved?  Should I have created the form based on one of the queries instead?

I am attaching my database.  I hope that will help you to understand more of what I am trying to do.  I don't know if I even know if I know what I'm trying to do.  :-)

Thanks,
Kelly
JX-Training1.mdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
Issue 1:
Your EmployeeNo is actually a Text Field (not a Numeric field)
This will probably cause similar misunderstandings in the future.
Can you explain why this is the case?

Issue 2:
You should be referencing the name of the control, not the field.

So try this:
    DoCmd.OpenReport "rptInternalTraining", acViewPreview, , "EmployeeNo=" & "'" & Me.Combo20 & "'"

;-)

JeffCoachman

0
 
mschmidt14Author Commented:
I assigned the EmployeeNo field as Text because I was taught that unless you were going to do calculations with a field, it should be text.  

What do you mean by referencing the name of the control and not the field?  That's what I thought I was doing.

I tried this solution and now my database is getting changed...EmployeeNos overwriting EmployeeNames.  I tried deleting this combobox and creating a new one and now I'm getting a "data type mismatch".  I think that would be because I tried your way of assigning the employee id as a number.  

I'm so confused...this was supposed to be easy.  :-)

Should I open another question?
Thanks!
Kelly

0
 
Jeffrey CoachmanMIS LiasonCommented:
mschmidt14,

<I was taught that unless you were going to do calculations with a field, it should be text.  >
That is just a generic general rule, for purely numeric fields.
The issue with Text Keys is that someone could enter a Employee ID of: 10O2, instead of 1002.
Since the majority of Primary Keys in Access will be AutoNumber, if I had a choice, I would try to always make my Primary Keys Numeric.
Besides, as you can see, Text Fields are harder to reference in VBA than a numeric field.
;-)


<I tried this solution and now my database is getting changed...EmployeeNos overwriting EmployeeNames.>
When you reference your Employee, (anywhere in the database) it should always reference the *Number* not the name.
You can always pull in the name from a query.

So in addition to changing the Datatypes in all the tables, you must also change the combobox code that finds the employee.

Private Sub Combo20_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[EmployeeNo] = " & Str(Nz(Me![Combo20], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

(For the record, you should also have two separate Name fields: FirstName and LastName)

"I'm so confused...this was supposed to be easy.  :-)"
Sez Who?
;-)
IMHO, Database Design and table relationships can never truly be called "Easy".
On the other hand, it can be made "easier" if your tables are designed with the basic principals of Database design in mind.
Here is a great reference:
Database Design for Mere Mortals:
http://www.amazon.com/Database-Design-Mere-Mortals-Hands/dp/0201694719

JeffCoachman
0
 
mschmidt14Author Commented:
I'll see if I can work it out.  Thanks for your extra help.

Kelly
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.