Data Entry Form (1 Viewer)

[woodsy74]

I currently have a Form where the user inputs about 15 fields and hits a submit button. When they hit the button the 15 input fields populate an excel file which calculates and then returns about 5 output fields to the form to show the user the answer to their submission. All of this data is saved to 1 table in access and appears to be working fine.

My issue is when they hit the submit button again. If they do this it will go out to excel and return the updated calculations but it will overwrite the record that was just saved to the table.

I would like them to be able to hit the button again if needed (this way they can just modify an input instead of having to re-enter all of them) but if they do then I need a new record to be created and not overwritten.

Is this possible? If not then is it possible to have the submit button disabled after the initial run and therefore force them to go with a blank form?

Let me know if you have any questions. Thanks for your time.

 

[isladogs]

I would like them to be able to hit the button again if needed (this way they can just modify an input instead of having to re-enter all of them) but if they do then I need a new record to be created and not overwritten.

Is this possible? If not then is it possible to have the submit button disabled after the initial run and therefore force them to go with a blank form?

The 2nd option is in my view both easier & preferable.

At the end of the button click code you just need to add 2 lines of code

Me.SomeOtherControlName.SetFocus
Me.MyButtonName.Enabled = False

Obviously modify the names as appropriate
Then add code to re-enable the button when new data is entered creating a new record

 

[Minty]

Yes, but ...
It's entirely possible you could do those calculations in Access, and avoid that stage, unless they are particularly complicated.

If not then you can always check if the record they are on has those results already, and change the button code to update rather than create a new record.

 

[Pat Hartman]

Sounds like you are doing this the hard way. Why would you use Excel to do the calculations rather than doing them in the form?

You can also make a bound form and lock all the fields after the record has been saved the first time.

If you want to copy data from an existing form, you can use Cntl-' for each field or do it programatically.

Without seeing the code you are currently using, we have no way of advising you regarding your specific question.

 

[woodsy74]

Thanks Ridders that seems to do the trick. I just have to hope that everyone will be OK with having to re-enter all of the data when they need to make multiple requests.

I am using the excel step for a few reasons. Mainly because it was already built out and contains multiple sheets of lookup tables and calculations that would have been difficult to recreate within the db. Plus other folks are maintain the calculations and it was preferred for them to work in excel.

My issues so far have not been with the excel part of the process. It's getting around in access that is tripping me up.

I'm sure I will be back with another post because now that I have the data returned and stored I need to create a process for users to retrieve the data. One user group can see all of the data but another user group can only see certain fields.

Anyway, thanks for all your help. It's greatly appreciated.

 

[isladogs]

You're welcome
I guessed you wanted a quick fix rather than a complete change.

However, I agree that it should be possible to do it all in Access

 

[Minty]

In agreement about handling this in access as stated at the beginning.
Lookup tables are an access staple and much more efficiency than vlookups in excel, you can normally use a join and simply calculate your values in a query and export them if required, without ever needing to store them.