Solved

MS Access 2013

Posted on 2014-07-27
4
301 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 37

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

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…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

752 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