Solved

MS Access 2013

Posted on 2014-07-27
4
306 Views
Last Modified: 2014-07-30
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
0
Comment
Question by:dawber39
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 

Author Comment

by:dawber39
ID: 40222953
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
 
LVL 30

Expert Comment

by:hnasr
ID: 40223040
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
 
LVL 38

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40223070
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
 

Author Closing Comment

by:dawber39
ID: 40229866
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

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question