[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access 2003 print preview doesn't show correct records

Posted on 2009-04-22
15
Medium Priority
?
321 Views
Last Modified: 2013-11-28
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

0
Comment
Question by:mschmidt14
  • 7
  • 6
  • 2
15 Comments
 
LVL 4

Expert Comment

by:Rodger
ID: 24210604
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24212055
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
 

Author Comment

by:mschmidt14
ID: 24215969
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 4

Expert Comment

by:Rodger
ID: 24216003
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 1200 total points
ID: 24216314
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
 

Author Closing Comment

by:mschmidt14
ID: 31573492
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24221733
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
 

Author Comment

by:mschmidt14
ID: 24225361
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24228656
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24228664
0
 

Author Comment

by:mschmidt14
ID: 24229334
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24229972
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
 

Author Comment

by:mschmidt14
ID: 24242368
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24244332
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
 

Author Comment

by:mschmidt14
ID: 24245282
I'll see if I can work it out.  Thanks for your extra help.

Kelly
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question