Export a form to excel along with the combo boxes

[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

 

[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.

 

[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.

 

[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.