Data entry form to data entry issue on multiple form instance

Posted on 2012-08-25
Last Modified: 2012-08-29
I have a database that utilizes multiple instances of the same form.
The current form instance is stored in a variable and if data entry occurs, the form instance is referenced as part of the vba command. So far this works without a hitch. I mention this first in case it comes to bear... which I don't think it will! OK here's the issue.

I have a datasheet from which I do data entry. The last column of the datasheet holds user comments and tends to be lengthy. Since space is limited, I wrote a pop-up window for this field which populates with the data from the field and allows data entry and editing. Anything done here is loaded to the last column field. The code from the data entry form is below:

frm_instance.frm_comm_internal1.Form.comm_comment.Value = txt_comments.Value

Open in new window

This is run from the data entry form where comm_comment is the field on the data entry form and txt_comment is the field on the data entry form.

Sometimes, but not always the command will throw an error. Sometimes but not always, it updates the wrong field. Usually, it works just fine.

I think the reason for this is that Access doesn't know which field to update, but since I cannot reproduce it reliably it is hard to say. SO the answer seems to be to add a reference to the record that access is supposed to be updating. The problem is, the record reference is an autonumber that doesn't populate until the record is completed.

Is there some way to programatically refer to a the open record or some other cleverness to refer to this record. Thanks.
Question by:JP_TechGroup
    LVL 29

    Expert Comment

    How about updating the table that your datasheet is bound too and then Refresh your subform?
    LVL 24

    Accepted Solution


    I do not see any reason why Access should throw an error (which?) or update the wrong field. If you have a form reference to a specific form even if there are x other equal form instances of the same form you have a handle which is exact like a MAC address or a GUID. There is no other form which could be affected by the same reference. So I guess the problem is the way you set the form reference but without knowing the complete code there is no possibility to help you. You should try to create a new database, import the needed table, delete the contents and create some test data, then import all needed forms and code modules and then upload it here with a description what to do to create the error.

    Of course it is not possible to address a record which was not already written, as you said it has no ID and because of that it is not existent in the table, only in the form, so you need to address the form correctly.

    (By the way: There is also the possibility to press SHIFT-F2 on any field to edit the contents in a popup window.)

    I also made my own form for editing such fields, I made it with a edit button beside the lengthy field. The popup form has a public property which is defined as Access.Textbox and when I click the edit button I simply create a new form instance of the edit form, let it invisible and set the textbox property to the textbox of the form with the lengthy field. In the edit form there is a Save button which copies the contents of the unbound visible textbox to the own property textbox so the edit form never needs to know anything about the calling form and the text goes directly into the original form. As it is an instance the user could also theoretically open more than one edit form at the same time without disturbing each other and any Save button of any edit form instance writes to the right target textbox (a form should never directly change any control on any other form, if you need to rename a control you would have to rename it at x places, always use properties, subs, functions as interface to the "outer world").

    By the way: A requery doesn't make sense here, the same with refresh. Both reloads the form, while refresh only see the UPDATEs, no DELETEs or INSERTs. But if the user is working on a new record it is not saved so he would lost his input or it would be automatically saved so the user would not be able to undo his changes if he decides to do so. With the method I described above the edit form simply copies the text to the target textbox so it is like directly editing it.

    The public textbox property of the edit form should be written as "Property Set", not as public variable. That is needed because if the external form sets the textbox reference with this property you need to copy it's content (which the user already maybe began to write) to the edit form's unbound visible textbox. Here is the code like it is in my edit form:

    Public Property Get ctrlSaveTo() As Control
       On Error GoTo ctrlSaveTo_Error
        Set ctrlSaveTo = prv_ctlSaveTo
        Exit Property
        Select Case Err
            Case Else
                fnErr "Form_EditDescription->ctrlSaveTo", True
                Resume ctrlSaveTo_Exit
        End Select
    End Property
    Public Property Set ctrlSaveTo(ctlSaveTo As Control)
       On Error GoTo ctrlSaveTo_Error
        Set prv_ctlSaveTo = ctlSaveTo
        strOldValue = Nz(prv_ctlSaveTo, "")
        Me.txtDescription = strOldValue
        Exit Property
        Select Case Err
            Case Else
                fnErr "Form_EditDescription->ctrlSaveTo", True
                Resume ctrlSaveTo_Exit
        End Select
    End Property

    Open in new window

    "txtDescription" is the visible unbound textbox of the edit form.
    This edit form can of course also be used to edit existent records, not only new ones. To make it undoable it has the "strOldValue" variable.

    If this method solves your problem you don't need to upload a demo database of course.


    LVL 30

    Expert Comment

    "I wrote a pop-up window for this field ..."

    try pop-up as a single form filtered for the current record with only the referenced field enabled for input. Once finished it updates the intended field. No need for code to update.

    Author Closing Comment

    Lot's of good comments here, but the Shift+F2 suggestion is what I used to turn the trick.
    I got rid of the custom form and simply coded the zoom window to appear on the ON_Click event of this field. This satisfied the requirement for the project. See the command below. Thanks to all for some great comments!
    RunCommand acCmdZoomBox

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    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…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    758 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

    11 Experts available now in Live!

    Get 1:1 Help Now