UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Horizontal Scrolling A Form, Access 2016    
 
   
isladogs
post Sep 11 2018, 03:52 AM
Post#21



Posts: 481
Joined: 4-June 18



It sounds like a crosstab form/report will largely do what you want as the price data won't be edited by you

The Notes section could be handled possibly by having an editable textbox in the footer section or by clicking a button to open a separate popup form

The appearance might be similar to my Currency Exchange Rate Tracker app which is available free here - at 2.5MB its too big to upload

The displayed data is based on a crosstab only showing the most recent values

Attached File  CERT_Tracker.PNG ( 83.7K )Number of downloads: 5


Attached File  CERT_Chart2.PNG ( 61.9K )Number of downloads: 4


Attached File  CERT_Chart1.PNG ( 62.81K )Number of downloads: 2


Hopefully that will give you some ideas
This post has been edited by isladogs: Sep 11 2018, 03:54 AM

--------------------
nil illigetimi carborundem est
Go to the top of the page
 
projecttoday
post Sep 11 2018, 06:18 AM
Post#22


UtterAccess VIP
Posts: 10,069
Joined: 10-February 04
From: South Charleston, WV


QUOTE
I would use in Excel but I want the data handling capabilities of Access


For five columns you might not want to go this route but you should be aware that you can program Access to display your data with Excel via a click. That way you have the data handling capabilities of Access as well as the added width and the powerful pivot table feature of Excel.

--------------------
Robert Crouser
Go to the top of the page
 
firlandsfarm
post Sep 13 2018, 12:56 AM
Post#23



Posts: 296
Joined: 28-April 02
From: Heathfield, England


Robert, very Interesting ... how? I know to download an Access dataset to an Excel spreadsheet and maybe I'm jumping the gun but are you saying you can embed an Excel spreadsheet based on Access data in an Access form or other?

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
firlandsfarm
post Sep 13 2018, 03:11 AM
Post#24



Posts: 296
Joined: 28-April 02
From: Heathfield, England


Thanks Isladogs, I've looked at your CERT. Forex is a major part of what I look at (the rest being Indices) but more the Indicators (RSI, SMA, MACD, Bollingers etc.) than just the price. The data feed would be of interest though! (Save some typing smile.gif )

I have to confess that Crosstabs is not something I have gone into in any depth, I can see their advantage in presenting data in an array format in certain (simple) situations but never had (or not identified) the need. I just tried to use the Query Wizard to create a crosstab and my thought process failed at the first hurdle! Choose a table it says, so I did ... the table with my daily data. Now choose a field, so I did ... the Date field because I want a date specific presentation, and it immediately wanted to put the dates down the first column! But I want the Date as column headings (with fields going down the columns). It looks like I want a transposed crosstab. Here is a screen capture of what I'm looking for ...

Attached File  HorizontalForm.JPG ( 81.51K )Number of downloads: 3


I only have 4 days data at the moment but it will be a daily exercise hence my requirement to scroll horizontally. It would be even better if in a version of the form I could have the left most data column appear blank for the day's data to be entered and when the Sec_ID is entered the records to the right populate. As you will realise it would not be possible to have the form fit on one screen if designed the usual way of having the fields as columns.

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
isladogs
post Sep 13 2018, 04:17 AM
Post#25



Posts: 481
Joined: 4-June 18



The data feed for my CERT app is a JSON file from an online provider.
As previously mentioned it is displayed as a crosstab form/report and is therefore read only.
Although it's possible to make editable crosstabs it requires a lot of code and is unlikely to be the best solution.
Suggest using a separate subform below the crosstab part for the field(s) you want to edit e.g. Notes.
It is fairly simple to synchronise these so they scroll horizontally together if you need that.

Although the CERT app is available free as an ACCDE, there is a small fee if you want the source code.
If interested, send me a PM or follow the links on the website.

Embedding an Excel spreadsheet in Access is also a good idea to explore which I hadn't considered.

Good luck
This post has been edited by isladogs: Sep 13 2018, 04:17 AM

--------------------
nil illigetimi carborundem est
Go to the top of the page
 
firlandsfarm
post Sep 13 2018, 06:13 AM
Post#26



Posts: 296
Joined: 28-April 02
From: Heathfield, England


Thanks for your input Isladogs ... most helpful.

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
projecttoday
post Sep 13 2018, 06:54 AM
Post#27


UtterAccess VIP
Posts: 10,069
Joined: 10-February 04
From: South Charleston, WV


I'm not really sure if you can embed a spreadsheet in a form. You can create a form with a button on it that, when clicked, will automatically create a new spreadsheet with a pivot table on it.

--------------------
Robert Crouser
Go to the top of the page
 
isladogs
post Sep 13 2018, 09:51 AM
Post#28



Posts: 481
Joined: 4-June 18



@projecttoday

You can embed various items including excel spreadsheets in an Access form using the ActiveX web browser control
The really neat thing about doing so is that the embedded object can be edited exactly as though it is the host application ....because it effectively is!!

Below are screenshots showing randomly selected Excel file & Word doc in a form

Attached File  ExcelFileInAccess.PNG ( 66.15K )Number of downloads: 5


Attached File  WordDocinAccessExample.PNG ( 82.37K )Number of downloads: 4


This can also be used with other file types including PPT/PDF and of course with web pages

Depending on the file size, there may be a delay in loading the file but it does work


@cheekybuddha
I'm just about to send you a PM

--------------------
nil illigetimi carborundem est
Go to the top of the page
 
projecttoday
post Sep 13 2018, 10:44 AM
Post#29


UtterAccess VIP
Posts: 10,069
Joined: 10-February 04
From: South Charleston, WV


QUOTE
You can embed various items including excel spreadsheets in an Access form using the ActiveX web browser control


Okay. Do you just drop the control on a form? Is there a sample in the code archive or the Wiki?

--------------------
Robert Crouser
Go to the top of the page
 
kfield7
post Sep 13 2018, 11:10 AM
Post#30



Posts: 875
Joined: 12-November 03
From: Iowa Lot


I think in Access 2007 you could put Excel on a form control...At least I've got some code I wrote in Jun 2007 that dumps a query to an Excel sheet in a control on a form.
But then again I think that feature has been removed from Access -- I don't think the code works in later versions.
Not something I frequently used, so memory's a bit fuzzy.

Actually I'm pretty sure I borrowed a lot of the code -- doesn't look like my style for a lot of the control stuff.
This post has been edited by kfield7: Sep 13 2018, 11:14 AM
Go to the top of the page
 
isladogs
post Sep 13 2018, 11:17 AM
Post#31



Posts: 481
Joined: 4-June 18



Attached is a very basic example showing how its done using a web browser control.
This is loosely based on an example by AWF member arnelgp who also used to post here I believe.

It will work for several different file types including csv/xlsx/docx/msg/pdf/json/txt.
It does so by opening the default application associated with that file type within the ActiveX web browser control

As previously stated it can be a bit slow loading some files initially

I have a different example with additional functionality which I'll upload when I have time in the next couple of days.

No idea whether anything exists in the code archive or wiki
This post has been edited by isladogs: Sep 13 2018, 11:36 AM
Attached File(s)
Attached File  SampleFileViewer.zip ( 33.08K )Number of downloads: 9
 

--------------------
nil illigetimi carborundem est
Go to the top of the page
 
projecttoday
post Sep 13 2018, 11:52 AM
Post#32


UtterAccess VIP
Posts: 10,069
Joined: 10-February 04
From: South Charleston, WV


When I click on Browse and select a spreadsheet, the spreadsheet opens normally, not embedded in the form.

--------------------
Robert Crouser
Go to the top of the page
 
isladogs
post Sep 13 2018, 02:19 PM
Post#33



Posts: 481
Joined: 4-June 18



The attached registry code will hopefully fix that issue
Just unzip and run in the usual way.

I've never needed to use it so would appreciate feedback on whether it fixes the issue.
This post has been edited by isladogs: Sep 13 2018, 02:20 PM
Attached File(s)
Attached File  WebBrowserControl.zip ( 473bytes )Number of downloads: 5
 

--------------------
nil illigetimi carborundem est
Go to the top of the page
 
projecttoday
post Sep 13 2018, 10:29 PM
Post#34


UtterAccess VIP
Posts: 10,069
Joined: 10-February 04
From: South Charleston, WV


This does not appear to be a .exe file.

--------------------
Robert Crouser
Go to the top of the page
 
isladogs
post Sep 14 2018, 12:06 AM
Post#35



Posts: 481
Joined: 4-June 18



It's a standard .reg file. Click Edit to view contents or Merge to add to Registry

--------------------
nil illigetimi carborundem est
Go to the top of the page
 
projecttoday
post Sep 14 2018, 01:50 AM
Post#36


UtterAccess VIP
Posts: 10,069
Joined: 10-February 04
From: South Charleston, WV


That worked.

This is very interesting. So could it be programmed so that once the user makes his/her selection the spreadsheet is created in not-visible mode, embedded in the form, and then made visible? The user wouldn't even necessarily know it's a spreadsheet especially since the task bar at the top is gone. We lose all those functions but for just viewing the spreadsheet I don't think that should be a problem. You still have the pivot table drill down.

--------------------
Robert Crouser
Go to the top of the page
 
isladogs
post Sep 14 2018, 04:36 AM
Post#37



Posts: 481
Joined: 4-June 18



Hi Robert

I don't see why not.
I've tended to use this method for embedding PDF help files in an Access form. Not really done much with Excel files in Access
You can certainly set the file to be used and eliminate the Browse button

Your suggestion might also help with the lengthy delay that can occur as the file can be loaded in the background
I'm fairly sure my other example gets rid of the warning message before the file opens - haven't had time to search for it yet.

One thing I've never understood. When a Word doc is opened you see the full toolbar but not for Excel/PowerPoint/AcrobatReader etc

One other thing to beware with the quick file viewer I uploaded.
If you switch files repeatedly it doesn't seem to clean up memory used cleanly & you can run low on CPU resources after a while

--------------------
nil illigetimi carborundem est
Go to the top of the page
 
projecttoday
post Sep 14 2018, 06:26 AM
Post#38


UtterAccess VIP
Posts: 10,069
Joined: 10-February 04
From: South Charleston, WV


So to embed a pdf, Word, Excel or other document on an Access form you drop the web browser control on the form, update the register in the computer with the above, create the desired file, and then

CODE
        DoCmd.Hourglass True
        DoEvents
        Me.WebBrowser0.Object.ExecWB OLECMDID_OPTICAL_ZOOM, _
            OLECMDEXECOPT_DONTPROMPTUSER, CLng(100), vbNull
        Me.WebBrowser0.Object.Navigate2 strFile
        DoCmd.Hourglass False

where strFile is the file's path/name.

?

QUOTE
Your suggestion might also help with the lengthy delay that can occur as the file can be loaded in the background

What suggestion is that?



--------------------
Robert Crouser
Go to the top of the page
 
2 Pages V < 1 2


Custom Search
RSSSearch   Top   Lo-Fi    26th September 2018 - 05:38 AM