Export a form to excel along with the combo boxes (2 Viewers)

[REZ]

I've created a small database for a small organisation. They don't have a server, only the boss uses the database. Some reports need to be filled in by other, so I created a button to export to excel, and then import it back.
Now they are requesting that there be combo boxes on the excel export, to prevent wrong data types from being input.
How do I go about doing that?

 

[June7]

Cannot export Access forms to Excel.

Build controls in Excel. Either directly on sheet or an Excel form.

Better to set up data entry via Access. If users don't have full Access, can install runtime version which is free.

 

[theDBguy]

I am thinking maybe you could create an Excel template and export your data there. That way, you can add all the data validation you need only once (hopefully) and don't have to do it all using code.

 

[REZ]

Thanks DBguy, I was thinking along those lines.
I've never done that before though.
Do I have to save the excel spreadsheet as an .xls or could I use the newer.xlsx?
Could you give me the code I would use to export to the template?

 

[theDBguy]

Thanks DBguy, I was thinking along those lines.
I've never done that before though.
Do I have to save the excel spreadsheet as an .xls or could I use the newer.xlsx?
Could you give me the code I would use to export to the template?

It depends on your version of Excel. Nowadays, it's normal to use the XLSX format. As for the code, look up the CopyFromRecordset method.

 

[June7]

Review https://accessmvp.com/KDSnell/EXCEL_Export.htm

 

[Navy Ken]

You could split the database and have one Front end for the Boss that has all the current functionality, and a second front end for everyone that is not the boss that only provides a form for the required data entry.

 

[CJ_London]

Excel does have the useful ability to build macros (the vba)

So start recording your macros to create the way you want the workbook to work

You can then copy/ paste these macros to an acccss vba module

They will require some adjustment to allow for the fact that access uses different objects to excel - include the excel library to make these easier to do

Edit - don’t forget you can create forms in excel - depends on wether that meets your needs or not

 

[Pat Hartman]

I would create a form in Excel. That allows you to lock down the data entry and provide the combos. Since the combo values might have to change over time, I would include a version number on the form. Then when you import the data from the form, you can check the version and tell the user he needs to use the latest template and provide a link.

The code behind the Excel form can capture the user's ID and include that as well. Then for the receiving app, you can set up a separate folder in your email so that these forms automatically get placed in the inputForm folder. The app can search this folder when it opens and also by a button click if it needs to be done more frequently. The process links to the spreadsheet, checks the version, validates the data and imports it. Then it logs the file in an import table with file name, date, and who it came from and moves the file from the inputForm folder to the processedForm folder.

You can include validation in the spreadsheet form as a pre-flight check to try to get the best data you can, but you really need to do validation in your Access app also JIK.

 

[Navy Ken]

I would create a form in Excel. That allows you to lock down the data entry and provide the combos. Since the combo values might have to change over time, I would include a version number on the form. Then when you import the data from the form, you can check the version and tell the user he needs to use the latest template and provide a link.

The code behind the Excel form can capture the user's ID and include that as well. Then for the receiving app, you can set up a separate folder in your email so that these forms automatically get placed in the inputForm folder. The app can search this folder when it opens and also by a button click if it needs to be done more frequently. The process links to the spreadsheet, checks the version, validates the data and imports it. Then it logs the file in an import table with file name, date, and who it came from and moves the file from the inputForm folder to the processedForm folder.

You can include validation in the spreadsheet form as a pre-flight check to try to get the best data you can, but you really need to do validation in your Access app also JIK.

Is there an advantage to using two different application? I may be off my mark but Splitting and having two front ends avoids importing and exporting data. Just seems like a lot of opportunity for data corruption.

 

[Gasman]

You could split the database and have one Front end for the Boss that has all the current functionality, and a second front end for everyone that is not the boss that only provides a form for the required data entry.

I would not do that, but just have logic to determine who sees what.

 

[Pat Hartman]

Is there an advantage to using two different application? I may be off my mark but Splitting and having two front ends avoids importing and exporting data. Just seems like a lot of opportunity for data corruption.

Only the manager has the master database. The problem is getting data from the other users into his database. If he exports the data to the import/export database which is unsplit since it is single use and transferring one file is easier than getting two sync'd, he still uses email to send the database and receive it. The database comes as an attachment in this case and you move the file around using FSO instead of the Outlook object model. The manager's master database then needs the same type of process as what I described for the email so it can import the data from the import/export database and move the database to the processed folder.

The processing logic is very similar. And Outlook is involved regardless. I've never tried to import data from an Outlook form so I'm not sure how different it is from importing manually one cell at a time from a spreadsheet. I like the Outlook form because it means the users don't need Access at all and are probably more familiar with working within Outlook. If you use a database as the transfer mechanism, the users have to download the database to work with it and then send back the database they just changed. Given how most people manage their folders, this is a process that would terrify me. Of course, when the manager exports the database for Import/Export, he could use a naming scheme that makes each unique and he can then match them when they are returned to make sure he is only getting the one he most recently sent out. With the emails, people would respond to it rather than having to download a file and keep track of it.

Using the Outlook form approach also segregates the actions so that you get one email for each record that needs to be changed. I don't know what the process is but if you import a file from Suzie and one from Sam it is harder to see that they've both changed the same record.

We actually don't know anywhere near enough about the whole process to make an informed decision so sending monolithic databases around might end up better. We'll probably never know.

 

[Navy Ken]

Only the manager has the master database. The problem is getting data from the other users into his database. If he exports the data to the import/export database which is unsplit since it is single use and transferring one file is easier than getting two sync'd, he still uses email to send the database and receive it. The database comes as an attachment in this case and you move the file around using FSO instead of the Outlook object model. The manager's master database then needs the same type of process as what I described for the email so it can import the data from the import/export database and move the database to the processed folder.

The processing logic is very similar. And Outlook is involved regardless. I've never tried to import data from an Outlook form so I'm not sure how different it is from importing manually one cell at a time from a spreadsheet. I like the Outlook form because it means the users don't need Access at all and are probably more familiar with working within Outlook. If you use a database as the transfer mechanism, the users have to download the database to work with it and then send back the database they just changed. Given how most people manage their folders, this is a process that would terrify me. Of course, when the manager exports the database for Import/Export, he could use a naming scheme that makes each unique and he can then match them when they are returned to make sure he is only getting the one he most recently sent out. With the emails, people would respond to it rather than having to download a file and keep track of it.

Using the Outlook form approach also segregates the actions so that you get one email for each record that needs to be changed. I don't know what the process is but if you import a file from Suzie and one from Sam it is harder to see that they've both changed the same record.

We actually don't know anywhere near enough about the whole process to make an informed decision so sending monolithic databases around might end up better. We'll probably never know.

Wow this is so 1992. There is no web based option? I know that Access has the ability to make web based stuff. Never messed with it but, man, this sounds archaic.

 

[June7]

Seems Access 2016 was last version to incorporate web design. https://support.microsoft.com/en-us...-the-web-cca08e35-8e51-45ce-9269-8942b0deab26

 

[The_Doc_Man]

Wow this is so 1992. There is no web based option? I know that Access has the ability to make web based stuff. Never messed with it but, man, this sounds archaic.

These days, you can have an Access database talking to an active-SQL backend like SQL Server or MySQL or several other such engines. Then you CAN have both web pages and Access frontend files that talk to the common backend. In that case, you would probably be talking through ODBC drivers for Access-to-SQL - but it's been done a gazillion times.

 

[Pat Hartman]

There is no web based option?

With modern technology, it is quite easy to use Access remotely. Charities rarely have such capability.

Access is a desktop application. It cannot be all things to all people. It is simply not a web app. MS tried and failed because they kept trying to solve the wrong problem. When people say they want Access to be a "web app", they don't mean they want it to run in a browser. They mean they want it to be able to connect to remote databases. This it can already do but it is extremely slow due to the way Access interacts with the database server. THAT was the problem that MS needed to address, not putting forms into web pages.

If a web product is what you want, use some other tool. The cost of redeveloping two of my Access apps into Web apps ran into the millions and 2+year time frames each. The Access apps, which the users loved, cost around $100,000 each initially. One grew for another year and so was ultimately closer to $300,000. The web apps did not include all the features of the desktop apps. The applications were never intended to interact with the public as this forum does. The apps were always internal use only. Therefore, the cheapest, method of making them "web apps" was to use a Citrix server. No changes are required to the Access app and the next day, you can use the app from anywhere in the world. The money spent to "upgrade" them was a total waste. The client got nothing for the money spent except less satisfied users. One was actually already used via citrix and had users from San Francisco to Paris. The goal in that case was to bring the app under IT control because it rapidly became mission critical and IT didn't use Access. Another much smaller app that cost closer to $10,000 to develop did surveys. The web people spent 2.5 years and 3 MILLION taxpayer dollars to make it a web app. They also forced the state to buy iPads and hot spots for their phones costing another half million. Then because the developers didn't understand the concept of normalization, the app required a half million a year for maintenance since the users could not add/change survey questions. Each change required table/web page/query changes because the developers saw each question as a separate data point and there were a lot of questions. The original app used one table to define questions and a second table to hold answers. The web app had no definition table but it had a hundred + answer tables with many fields each. Although the surveys were conducted remotely at client locations, there was no reason for this to be a web app since no one looked at the survey immediately. The survey could be entered into the Access database using the nurse's laptop. So, no remote connection was ever required and no new hardware either. The file could be uploaded from the office. All the analysis was done by people back in the home office who used the answers to help to develop a care plan for the client.

My guess is that the cost differential between developing an Access database and a web app is greater than 7 to 1. Much more if you have to interact with word, excel, and outlook. This is one of the reasons why Access is alive and well and still the best option for people with small pocketbooks - even if they also need to spring for Citrix.

 

[CJ_London]

I may be off my mark but Splitting and having two front ends avoids importing and exporting data. Just seems like a lot of opportunity for data corruption.

Your db should be split anyway, even for one user.

Your issue is your client does not have a server.

Also not clear where your other users are - in the office? working remotely?

No reason why you can't send a copy of the BE to each user who have their own version of the front end to do what they need to do, then send it back. You then need to synchronise the returned BE with the managers 'master' BE. In the days of mdb's this was called replication. However it was dropped in 2007 although existing mdb's could still run the replication process in 2007 and later.

You can still emulate this process but have to write the routines yourself.

With regards using excel, I had a client who worked in global financial markets. The Access app generated an excel file including macros, necessary data and all formatting as a template and was totally locked down so users could only enter valid data. This was sent to offices around the world. The excel files were returned once a month and then simply imported back into the db. The reason for doing this was because their global network was too slow, in particular for those offices that were based in countries with a low investment in their internet infrastructure. The data movement was all one way and consisted of new data only - no updates to existing data.

There is no web based option?

Only terminal server, citrix, SQL Azure and similar. Assuming you had a web based option where would you put the data? Your client would need to invest in a server, one way or another.