ACCESS FEED

Kamis, 28 Februari 2019

[MS_AccessPros] Unique values in 1 query

Hello all,

I have a table with reports to information. the table has columns with each of the levels of Reporting (see below).

I need to create a query that will get the unique values of all the levels into one column. It will be used as the row source for a combo box.

Is there a way of doing it in one query or do i need to create multiple queries?

Thank You

Jim Wagner

for example

Level 5 has 2 employees which is the executive leaders

Level 4 has 9 employees which are Directors

Level 3 has 20 employees which are the Supervisors

Level 2 has 40 employees which are the Assoc Supervisors

Level 1 has 33 employees which are the Leads.

__._,_.___

Posted by: luvmymelody@yahoo.com

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (1)

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

__,_._,___

Re: [MS_AccessPros] Placing 30,000 images. One more question

Thank you very much Crystal, I am impressed by you energy.
I will now stick to the method in your samples, they can give the result I want.
I am now trying to implement the method in a test version of my DB, which I first converted from Access 2003 to 2013.
In the main table "AlleSamlere" I made a column "picURL" (with some random URLs for practice).
In one of the forms I placed the webbrowser with AlleSamlere.picURL as control element, and copied your VBA code to the relevant place for the form.
The form is opened with input from a combo box with a query as post source.
However, when I try to open the form the VBA code stops at "With Me.AllleSamlere.picURL".
It says "Compile error. Methods or data member not found"
But after Stop/Reset/Run the form is displayed correct showing the page for the URL in the tabel "AlleSamlere". Strange!
If I type the VBA code line "With Me. " a drop box appears with several options, but "picURL" or "AlleSamlere.picURL" are not in the listing. This might be the problem?
The only thing I can think of for the time being is, that the transfer from mba (2003) to accdb (2013) has some errors. Is that possible?
Do you have any suggestions?
Best regards

Peter

Den 27-02-2019 kl. 01:15 skrev crystal 8 strive4peace2008@yahoo.com [MS_Access_Professionals]:

hi Peter,

> "send" the image ID (like AB1234 or AB1234.jpg) to the server "

I believe so. I cannot help you do that without researching and experimenting, which is above expectations for a free forum. I suggest you look at getting professional help. Hopefully what has been done so far will be a good foundation.

have an awesome day,
crystal

free code you can use in your projects
https://msaccessgurus.com/code.htm

On 2/26/2019 9:58 AM, crystal [MS_Access_Professionals] wrote:
hi Peter,

> " images are placed in 90 directories"

that is what I wanted to know, thanks! Your English is very good!

By the way, take care not to include personal contact information like phone and address when you post -- I've been removing it when I respond. Perhaps it is a saved signature and you can choose an option not to show it?

~crystal

On 2/26/2019 2:26 AM, Peter Randløv randlov [MS_Access_Professionals] wrote:
Hi Crystal
I am not sure I understand your question concerning categories.
The posts in the database have an ID like AB1234 (and the associated image will be AB1234.jpg).
The images are placed in 90 directories
...\RODS\AB
...\RODS\CD
etc.

Thank you for your tip concerning Filezilla, it will be useful.
However, my question was not file transfer as such. It was about avoiding to make 30,000 htm files - one for each image.
So I am hoping that there is a way to "send" the image ID (like AB1234 or AB1234.jpg) to the server when the browser window in Access looks for e.g. http://www.myhomepage.dk/.../RODS/AB/AB1234.htm.
English is not my mother tongue, so my questions might not be correct formulated.

Best regards
Peter

Den 25-02-2019 kl. 22:25 skrev crystal [MS_Access_Professionals]:
hi Peter,

to keep images path shorter, I am going to use "img" for the directory name. It will take a few days to put the code together because I have other things to do too. Are your images in categories?

~crystal

On 2/23/2019 2:19 PM, crystal [MS_Access_Professionals] wrote:
Peter, I'm not sure how you transfer files. I use FileZilla, which lets you right-click on a folder and transfer it, as well as everything below, to the web server. So creating folders for categories would be pretty easy to transfer too -- it will just take some time for the computer to do it, but you can do something else while that happens.

FileZilla is a free FTP (File Transfer Protocol) program.
https://filezilla-project.org/

here is a screenshot showing how I propose you structure your images and HTML files where Category1, Category2, etc, would hold the HTML files and the images would be in the respective image folder below. Maybe you already have them in separate folders?

~crystal

On 2/23/2019 11:03 AM, crystal [MS_Access_Professionals] wrote:
hi Peter,

you're welcome and thank you.

Rather than using www.myhomepage.dk/RODS/PR/PR1234.htm, it would be better to preface the link with http:// or https:// for a secure link, if your web service allows that. Put one of them on your website and then click in the URL bar to copy the link to see what it actually says, such as http://myhomepage.dk/RODS/PR/PR1234.htm

I will make an example to generate the HTML files on your computer. Then you would only need to transfer them to your web site. You should consider putting the images into categories so you don't have 30,000 files in the same directory. It will be a lot easier to manage.

~crystal

On 2/23/2019 9:35 AM, Peter Randløv [MS_Access_Professionals] wrote:
Crystal
Thanks again, it has been most rewarding to study your samples.
I will now try following:
1. One of the queries generates a new table with a column with the image ID for the selected images e.g. PR1234, AD5678, ...
2. The query opens a form with all information and the image. The present picture box is replaced by a controlelement for webbrowser with the path for the htm code as controelement. The path is generated by the associated module and will for the first image be: www.myhomepage.dk/RODS/PR/PR1234.htm
3. On the server there will be a htm code for each image, in this case PR1234.htm with the text <html><img src="PR1234.jpg">.
This code is only 28 bytes, so it's not a problem having 30,000 of them, but it is not the most "elegant" solution?
Is it possible to pass the image ID (e.g. PR1234) on to the server, or is the small htm file neccesary for each image?
Regards
Peter

Den 22-02-2019 kl. 17:19 skrev crystal [MS_Access_Professionals]:
perfect, Peter! Then you're set!

I updated the example and put it here:
http://www.msaccessgurus.com/tool/WebBrowserControl/WebBrowserControl_DisplayImages_s4p.zip

This one also shows the simplest example of the HTML you could create. The longer HTML examples were done in case you want all the metadata, although I left out keywords in the Head, which you may want

When you are looking at an HTML page, you can often press Ctrl-U to see the page source ... not in Access, but if you put the link it is rendering into your browser.

~crystal

On 2/22/2019 9:47 AM, Peter Randløv randlov [MS_Access_Professionals] wrote:
Hi Crystal
I first now realise that the mail below was to me.
All the 30,000 images are seperate jpg-files. They are not in the database, because I want the users to be able to use the images for other purposes.
Did I understand your comment correct?
Best regards
Peter Randløv  
Den 21-02-2019 kl. 02:22 skrev crystal [MS_Access_Professionals]:

you're welcome, Dave (Peter!)

the next step would be to save the images from the database as files. If
you upload a sample with a couple images, I can see what code can work.

~crystal

On 2/20/2019 5:52 PM, Dave Williams
[MS_Access_Professionals] wrote:
> Thank you Crystal, Access 2010 opens it OK.
> Regards,
> Dave W
>
>
> On 20/02/2019 16:28, crystal
> [MS_Access_Professionals] wrote:
>> You should be able to open it with 2010. Access 2003 didn't have a web
>> browser control.
>>
>> If folks don't have Access, then Runtime can be used, which could be
>> included in a download for them.
>>
>> On 2/20/2019 5:43 AM, Dave Williams
>> [MS_Access_Professionals] wrote:
>>> What version of Access is that? I can't open it with Access 2003, like
>>> the OP has. Can Access 2010 open it?
>>> Regards,
>>> Dave W
>>>
>>> On 18/02/2019 18:43, crystal
>>> [MS_Access_Professionals] wrote:
>>>> Hi Peter,
>>>>
>>>> I put an example database showing how to display an image from the web
>>>> using Access here:
>>>>
>>>> http://www.msaccessgurus.com/Downloads/WebBrowser_ImagePages_s4p.zip
>>>>
>>>> There are two types of web browser controls you can create.. I made a
>>>> form for each one. The first is on the ribbon. The second,
>>>> Class=Shell.Explorer.2, is created by choosing ActiveX from the
>>>> control list and then getting the Web Browser control from there.
>>>>
>>>> the HTML files to display an image look like this:
>>>>
>>>> '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>>> <html lang="en">
>>>> <head> 
>>>>    <meta charset="UTF-8">
>>>>    <meta http-equiv="Content-Language" content="en-us">
>>>>    <title>         Form and Popups </title>
>>>>    <META name="description" content="Picture of Form and Popups for
>>>> MyContacts" />
>>>>    <meta name="Author" content="crystal (strive4peace)" />
>>>>
>>>>    <META name="copyright" content="© 2019 crystal long
>>>> (strive4peace)">
>>>>
>>>> <body>
>>>>    <img src="../MyContacts_Popups.png"
>>>>    title="Form and Popups for MyContacts"
>>>>    alt="Picture of Form and Popups for MyContacts"
>>>>    /> 
>>>> </body>
>>>> </html>
>>>> '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>>>>
>>>> ~crystal
>>>>
>>>> VBA code you can use in your projects
>>>> http://msaccessgurus.com/code.htm
>>>>
>>>>
>>>
>>> ------------------------------------
>>> Posted by: Dave Williams
>>> ------------------------------------
>>>
>>>
>>> ------------------------------------
>>>
>>> Yahoo Groups Links
>>>
>>>
>>>
>>
>
>
> ------------------------------------
> Posted by: Dave Williams
> ------------------------------------
>
>
> ------------------------------------
>
> Yahoo Groups Links
>
>
>

__._,_.___

Posted by: Peter =?iso-8859-1?q?Randl=F8v?= <randlov@post9.tele.dk>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (26)

Have you tried the highest rated email app?

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

__,_._,___

Rabu, 27 Februari 2019

Re: [MS_AccessPros] How to use web browser control to display pdf in form.

ps. take away the extra space before the name ... it was hard to tell if one was there, guess there isn't

Twice again, sorry ... one of these days, I'll realize that something is selected before I post and it won't chop the message!

On 2/27/2019 4:42 PM, crystal [MS_Access_Professionals] wrote:

hi Patty,

again, don't use single quote delimiter for property setting. To apply Uncle Bill's suggestion for embedding a double quote mark to what you posted:
ShowPDF2.ControlSource = "=" & chr(34) & " " & Me.Documents_Subform_List.Form.DocLocation & " " & chr(34)

BUT! it would be better to rename all those files!

Here is code to loop and rename files that you can customize:
http://msaccessgurus.com/VBA/Code/File_LoopRenameDate.htm

~crystal

On 2/27/2019 3:08 PM, patty.mapes [MS_Access_Professionals] wrote:

After all my happiness over getting the web browser control to work the other day and am trying to implement a second control with a slightly different string:

This string works if there are no spaced in the DocLocation field but it fails, of course, when there are spaces:

ShowPDF2.ControlSource = "=' " & Me.Documents_Subform_List.Form.DocLocation & " ' "

I may have looked at this too long but everything I know about doing this is failing me. Does anyone know how I can create this path and retain the blank spaces in the DocLocation field?

I have tried splitting it into two fields but I cannot get it to work.

Any expertise you want to share will be very appreciated.

Thank you,

Patty

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, February 26, 2019 1:53 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] How to use web browser control to display pdf in form.

great! thank you, and you're welcome

have an awesome day,
crystal

free code you can use in your projects
https://msaccessgurus.com/code.htm

On 2/26/2019 12:31 PM, Patricia Mapes [MS_Access_Professionals] wrote:

Yes, I'm good. Just wanted to share in case anyone else ever encountered the same issue.

Thank you again,

On Feb 26, 2019, at 11:04 AM, crystal [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

hi Patty,

you're welcome. Try this:

ShowPDF.ControlSource = "=""" & "C:\123\Name.pdf" & """"

I don't think a single quote will work as a delimiter for a property setting. To get one double quote in a string delimited with double quotes, use 2 of them.

~crystal

On 2/26/2019 8:19 AM, patty.mapes [MS_Access_Professionals] wrote:
> In the end this came down to a syntax issue. I used the WebBroswer Control and this line of code:
>
>
>
> ShowPDF.ControlSource = "='" & "C:\123\Name.pdf" & "'"
>
>
>
> In life, it is a fine line between being a genius and muddling around like a blind person!
>
>
>
> Thanks for your help and support and hope someone else can use this one day!
>
>
>
> Patty
>
>
>
> From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com>
> Sent: Saturday, February 23, 2019 3:21 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] How to use web browser control to display pdf in form.
>
>
>
>
>
> thanks, Patty, and you're welcome ~
> miss John's presence (and presents) here too
> ~crystal
>
> On 2/22/2019 8:52 PM, Patty Mapes [MS_Access_Professionals] wrote:
>
> You are brilliant! This is worth some Saturday hours.
>
> You remind of the old days when John V would just find these answers in the air!
>
> Thank you so much.
>
>
>
> Sent from my iPhone
>
>
> On Feb 22, 2019, at 8:24 PM, crystal [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals@yahoogroups.com> > wrote:
>
>
>
> ps
>
> there are a few more steps too -- the first time you use it in the database (I'm guessing -- or maybe its the form, not sure yet), you actually end up installing the ActiveX control ... what your client didn't want to use, but obviously has to be used ....
>
> there is a link that pops up that you click. I didn't do it yet though because I want to record it when I do
>
>
>
> ~crystal
>
> On 2/22/2019 7:10 PM, crystal 8 wrote:
>
> hi Patty,
>
> I was wrong, there IS a way. Asked someone else today. You have to change settings in the PDF viewer to allow it to display the PDF in a browser. Use the Shell.Explorer.2 web browser control.
>
> In Foxit, it is:
>
> Preferences, File Associations, check: Display PDF in Browser
>
> ~crystal
>
> <anklidhnpnglhnmo.png>
>
> On 2/22/2019 10:27 AM, crystal [MS_Access_Professionals] wrote:
>
> you're welcome, Patty ~ happy to help
>
> This was something I wanted to know too. I think if it can be done, you would need to use JavaScript, which isn't something I would dig into without a paying client. I did find a link that mentioned using a free JS library .... but whether Access would support it is another story.
>
> ~crystal
>
> free code you can use in your projects
> https://msaccessgurus.com/code.htm
>
> On 2/22/2019 3:10 AM, Patty Mapes [MS_Access_Professionals] wrote:
>
> It is possible that it was 2007. I have had that thought. I need to find out what client is thinking as this is a big gap. They are using this app in an office network as well as smaller version (not split) on laptops for specific remote tasks so I need more info on that aspect of things.
>
> Years ago (20) I had a commercial mob app that I distributed with addition files in the install. Potentially I could do that here if I can run down the activeX file.
>
> You are a great help!
>
> Thank you,
>
> Patty
>
>
>
> Sent from my iPhone
>
>
> On Feb 22, 2019, at 3:18 AM, crystal [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals@yahoogroups.com> > wrote:
>
>
>
> hi Patty,
>
> you're welcome
>
> I'm using 2013. It doesn't work -- at least not in the couple hours I tried.. I read it quit working in 2010 -- so the version to try would be 2007? Or get the ActiveX control ...
>
> ~crystal
>
> -----------
>
> [later responses -- trying to keep everything in the thread]
>
> Patty:
>
> Very interesting. The good news here is I might not be loosing it!
>
> Their original development was done in 2003, but they said they were up to 2013,
>
> Their motivation was get bloat out of the file which didn't entirely make sense to me..
>
> On the machine I have it on it is about 350 MB which they are expecting to reduce to about 20 MB. Which I'm not about to do either. I think I have a lot to learn about 2016.
>
>
>
>
>
>
> On Feb 22, 2019, at 12:16 AM, crystal <MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals@yahoogroups.com> > wrote:
>
> hi Patty,
>
> I also tried showing an HTML file pointing to a PDF using <object> but Access blocks the content because it could be "dangerous" -- so perhaps Microsoft is just keeping us "safe" by not allowing it anymore.
>
> "To help protect your security, your web browser has restricted this file from showing active content that could access your computer"
>
> also tried using <embed> instead of <object> but I couldn't get it to work either
>
> ~~~
>
> Displaying a PDF with a Web Browser Control might still work with Access 2007.
>
> Sorry I don't have better news for you. I did some searching and others are disappointed this doesn't work anymore too.
>
> ~crystal
>
> -----------------------------
>
> On 2/22/2019 1:36 AM, Patty [MS_Access_Professionals] wrote:
>
> This is extremely helpful. I was working on a machine that I have 2016 on (2013 previously) . It's 2:30 AM and I've woken up thinking I should try this on a machine with only 2013.
>
> I think I'll take this link and open it on another machine first.
>
> Thank you very much!
>
> Sent from my iPhone
>
>
> On Feb 21, 2019, at 11:37 PM, crystal [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals@yahoogroups.com> > wrote:
>
>
>
> hi Patty,
>
> If there is a way to do it, I couldn't figure it out. I do see OLD examples of doing it -- but the web browser control also used to play videos and doesn't do that anymore, so maybe this doesn't work anymore either. Neither web browser control will render a PDF inside Access (not saying it isn't possible, just that I couldn't do it -- if you figure it out, share!) You can try it yourself and show the client. There is a sample database here:
>
> http://www.msaccessgurus.com/tool/WebBrowserControl/WebBrowserControl_DisplayImages_s4p.zip
>
> This example was created to show how to display images on the web in Access, but you can add another record (in the only table that is there) with the path and filename of a PDF, or the URL of a PDF.
>
> There are 2 forms, one for each of the web browser control types you can create.
>
> Now if the client wants to convert PDFs to JPG, PNG, or other image format, it could work this way ...
>
> have an awesome day,
> crystal
>
> free code you can use in your projects
> https://msaccessgurus.com/code.htm
>
> On 2/21/2019 9:17 PM, Patty Mapes [MS_Access_Professionals] wrote:
>
> Sadly, the client is insisting on using MS Web browser. I'm so stuck!
>
> Sent from my iPhone
>
>
> On Feb 21, 2019, at 7:19 PM, crystal [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals@yahoogroups.com> > wrote:
>
>
>
> you're welcome, Patty. Please let us know if this works for you
>
> ~crystal
>
> free code you can use in your projects
> https://msaccessgurus.com/code.htm
>
> On 2/21/2019 6:08 PM, Patty Mapes [MS_Access_Professionals] wrote:
>
> I will look at this!
>
> Thank you!
>
> Sent from my iPhone
>
>
> On Feb 21, 2019, at 6:55 PM, crystal [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com <mailto:MS_Access_Professionals@yahoogroups.com> > wrote:
>
>
>
> hi Patty,
>
> perhaps the ActiveX control from Foxit reader?
>
> https://developers.foxitsoftware.com/kb/article/use-foxit-activex-visual-basic-application/
>
> https://developers.foxitsoftware.com/pdf-sdk/windows/activex/
>
> ~crystal
>
> On 2/21/2019 3:41 PM, Patricia Mapes [MS_Access_Professionals] wrote:
>> This used to be done with the adobe activeX control but that is no longer available.
>> I am new to this project and I've been pulling my hair out over this..
>>
>> I can open a pdf (in adobe reader) but that is not the requirement... It does help me know that I have the path, etc. In this case I'm using "Application.FollowHyperlink Me.Documents_Subform_List.Form.DocLocation,,True,False"
>>
>> Q1 - All the MS documentation says to open form in Layout view, add the (activeX control?) and then click on the control and it will open "Insert Hyperlink.
>> When I go to layout view I can only see "Use Control Wizards" and it will not open or display any wizard. Does anyone else have this experience?
>>
>> Q2 If I go to design view, I can add the activeX control "Microsoft Web Browser" which is what the client specified, but I have tried many ways to get it to find my pdf but it will not.
>> Does anyone know how to do this?
>>
>> I'm using Win8 MS Access 2016.
>>
>> If anyone can help I will be very grateful!
>>
>> Thank you,
>>
>> Patty Mapes
>>
>>
>>
>>
>>
>> ------------------------------------
>> Posted by: Patricia Mapes
>> ------------------------------------
>>
>>
>> ------------------------------------
>>
>> Yahoo Groups Links
>>
>>
>>
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
> ------------------------------------
>
> Yahoo Groups Links
>
>
>