Solved

MS Access 2013

Posted on 2014-07-27
4
295 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
  • 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 35

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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.

789 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