Solved

Continued question regarding script, previously answered by fyed

Posted on 2011-09-15
10
287 Views
Last Modified: 2012-05-12
I’ve been working on this project so long now, I really feel like giving up.  Now I am told I need more than one ProvID per report, so I decided to create a Reference#, hopefully to link to use the ProvID number.  

I need to modify the existing script to make this happen.  Can anyone help?   I have also attached a copy of the most current database.
Test-EFT-08-24-11--4.accdb
0
Comment
Question by:Delores_C
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36545078
<Now I am told I need more than one ProvID per report,>
You have not really defined the issue here... or what your ultimate goal is here.

When referencing a previous question, please post the link to that question.

Finally, you posted no details on how to see this "issue"
0
 
LVL 40

Expert Comment

by:als315
ID: 36555593
Agree with Jeff, we can only suppose expected result.
Test this sample. I've supposed you like to have separate report for each Reference#.
Please, be careful with file name, where DraftDate is used. This date can be different for same Reference#. In this case you will have many files for one reference#. Test-EFT-08-24-11--5.accdb
0
 

Author Comment

by:Delores_C
ID: 36570106
The example works for producing by reference#, but it is now generating a blank pdf file, which was an issue I was dealing with earlier.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36570565
FWIW...

Both DBs (as posted) produced PDFs just fine for me...


If you are using acc2007 try re-installing the PDF add-in...?
Plan-92-1001-07.08.2011.pdf
Plan-92-Provider-1-07.08.2011.pdf
0
 

Author Comment

by:Delores_C
ID: 36574527
The file works fine.  However, I need to change the data type for the Reference# to a Text field, because I need this name to display as part of the pdf file name.

I changed it in the table to text, but I am getting an error in the script.

Any help is appreciated.  Thanks in advance
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36575216
OK
I'm confused...?

First you stated:
<The example works for producing by reference#, but it is now generating a blank pdf file,>
I thought this was the issue?
Is this resolved?
Yes or No..?

If the file is blank, then why do you now state:
<The file works fine. >
How is the file fine if it is blank?

<However, I need to change the data type for the Reference# to a Text field>
Why?, you can insert a number into a file name as simply as you can text.
Besides, ...
<I need to change the data type for the Reference# >
I don't see a "Reference#" in your code anywhere... so I am confused as to where/how you are trying to do this...

...and you never stated what this mysterious error is...

Finally you never posted the link to the previous question as I requested...
    ("When referencing a previous question, please post the link to that question.")

So again, this is all very confusing


Can you post the database as it stand now?
You know the drill:

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the VBA code window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
13. Post the explicit steps to replicate the issue.
14. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.



JeffCoachman




0
 

Author Comment

by:Delores_C
ID: 36575733
I am not sure what cause it to produce a blank file, but it is working fine now.

Also, I was initially told to group by Vendor#, which I did.  

Later I was told I need to have a group linked to a Reference# since there is more than one vendor connected to a location, so I this was done!

Now I am required to have the ReferenceName in the title of the pdf file when it is saved, whereas I created the Reference# as a number, which is now causing the error message I am getting when I changed the Reference# to a text file.  

I am currently trying to convert the Reference# to a text file, so I can have it saved with the name in the pdf file.

The file is attached.  

Also, I tried to delete your lookup box, and it give me errors also.  I have over 400 Reference numbers, which would not make it feasible to have a lookup box.  
Test-EFT-08-24-11--5-New.accdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36576266
Still no link to the previous question....eh...?

"I was initially told to group by Vendor#, which I did."
"Later I was told I need to have a group linked to a Reference#"
For what?, Why.


It is still not clear what the heart of the issue is here...

This is why I always ask that you:
13. Post the explicit steps to replicate the issue.
...and:
post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

JeffCoachman






0
 
LVL 40

Accepted Solution

by:
als315 earned 500 total points
ID: 36578458
1. When you are changing field type from number to text, you should also change filter form
StrFilter = "[Reference#] = " & rst![Reference]
to
StrFilter = "[Reference#] = '" & rst![Reference] & "'"
2. If you are changing field name in table, you should also change it anywhere: in code, in forms, in queries and in reports
3. You can delete combobox, if you don't need it - comment this if in form's code:
If IsNull(Me.Reference) = False Then
    strSQL = strSQL & "HAVING (((tblProviderAccount.[Reference]) = Clng(" & Me.Reference & "))) "
End If

Test-EFT-08-24-11--5-New.accdb
0
 

Author Closing Comment

by:Delores_C
ID: 36579779
The information and solution provided was excellent.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

615 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