Solved

Continued question regarding script, previously answered by fyed

Posted on 2011-09-15
10
244 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
  • 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 39

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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 39

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

706 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now