MS Access 2013

I have a form that is opened up for Vender Transaction review - before initiating cutting the checks for such vendors. The Main Menu stays open in the background. The financial coordinator uses the database's internal program to cut the vendor checks using pre-printed checks. Due to occasional errors, it Sometimes becomes necessary to change the check number. Within the menu form, there is a control to bring up the settings form, attached to the settings table. Here she can change the check number. To make it an easier process, I am trying to put a text control on the Vendor Trx review form in which I put the expression (=[Forms]![Settings]![NextCheckNum]). It pulls up the NextCheckNum fine, but it will not let it be edited there. I have been up and down the properties of the control, but I cannot find a property that will allow the text to be edited. I am thinking I need to use VBA - or something else I am missing. I have tried a query pulling NextCheckNum from the settings table - but I get a #NAME error. Any help would be appreciated
dawber39Database Analyst / Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dawber39Database Analyst / Application DeveloperAuthor Commented:
Also I might add that making the form data entry enabled - is not an option. And changing the check number has to be updated in the table as well - sorry for the omission
0
hnasrCommented:
Sorry, unable to comprehend the problem.
Can you reproduce the issue in a sample database and upload. Explain the steps to demonstrate the issue.
0
PatHartmanCommented:
Only Bound controls are tied to the underlying RecordSource. and therefore only bound controls will update a table directly. To be bound, the ControlSource must be the name of a column in the Form's RecordSource.  What you are doing is showing the value of a control in a different form.  The "=" sign at the start of a ControlSource indicates a "calculated" value rather than a bound field and that doesn't tell Access what table/field you want to update.

There are at least two ways to solve this problem.
1. Replace the control with a subform that is bound to the Settings table.  I'm assuming this is a one-record table and all you'll need to show is the LastCheckNumber field.  You can format the subform so that it just looks like any other control rather than like a subform.  This doesn't require any code except what you might want to make sure the checknumber isn't being updated accidentally since I'm guessing that would cause quite a mess.  You might even want to ask for a password before allowing it to be changed.
2.  Add a second unbound control where the user can enter the replacement starting number.  Again, I would add code to make sure that it isn't updated accidentally.  Then in the AfterUpdate event of the new control, you would add DAO code to update the LastCheckNumber in the Settings table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dawber39Database Analyst / Application DeveloperAuthor Commented:
Thank you - I didn't think of that, but either work around makes perfect sense. I will let you know which one I use, and the results. If I need more - there'll be another question. Thank you much - you people are awesome
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.