Getting data from a form instead of a table

Posted on 2011-05-09
Last Modified: 2012-05-11
I have a form that displays a box with values taken form a table with a field named [Works Order No].
The box  is locked and the only way to change it is to use ‘Find’.
If a user searches for a value then it is successfully displayed in this box.

I have used the OnLoad event as me.Works_Order_No to initially read what value is in the [Works Order No] field. This works OK.

Once the form is loaded AND the user uses 'Find' to get a new value for [Works Order No] the new value is successfully loaded in to the box but I cannot read it with VBA.
That is the value me.Works_Order_No remains ‘out of context’ ( in the watch window).

In VBA, how can I get the value out of a box on my FORM once it is loaded?

Question by:usatrfe
    LVL 30

    Expert Comment

    Unuable to reproduce the issue.
    Attach a sample form and instructions to demonstrate the problem.

    Author Comment

    THe attached DB is only a test version as the original has too many dependencies upon other DBs.

    Have a look a the WorksOrder form.

    The VBA has a global variable that is succesfully loaded when I launch the form.

    But if the user changes the data within that form (by using the 'find' facility) then I cannot find any event to capture the new value that is in that Works Order box.

    I need this revised value to overwrite the prevous Forms_displayed_works_order_no value.

    I await your kind reply!

    LVL 31

    Expert Comment

    It isn't clear how you are doing the Find.  I made another textbox on the form that shows the current value in the original textbox.  The Load event code referenced a nonexistent control.  The control originally had the same name as its bound field (not a good idea), so I gave it the standard txt prefix.  You could set a global variable (or a custom database property) from the AfterUpdate event of the textbox, or some other event.  I am attaching the modified database.
    LVL 31

    Accepted Solution

    Perhaps you could add an unbound combo box to search for a value, and then update the variable from its AfterUpdate event.
    LVL 30

    Expert Comment

    Suply step by step instructions to reproduce the problem.
    Ex: Open form ..., you see ..., select Find, type ...and so on.

    Author Comment

    A combobox has worked partly. But the combobox is far clearer for the user than my 'Find' method.

    I have resolved the problem as follows:-
    The user does not need to use the 'Find' faciltiy (part of Access, at the bottom of the form).
    The user instead selects a value from the combo box (this is bound to a field specifically created to store any strings - I called it LiveMSG).
    The event 'BeforeUpdate' reads the current value of LiveMSG and loads into my Global variable.
    Then I clear the LiveMSG field so that the Table is tidy again.

    It may be a bit messy but it does works.

    Thanks to all that contributed.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    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.

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now