Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Data entry form to data entry issue on multiple form instance

Posted on 2012-08-25
4
Medium Priority
?
530 Views
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
frm_instance.frm_comm_internal1.Form.comm_comment.Requery

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.
0
Comment
Question by:JP_TechGroup
4 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38333535
How about updating the table that your datasheet is bound too and then Refresh your subform?
0
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 2000 total points
ID: 38333694
Hi,

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

ctrlSaveTo_Exit:
    Exit Property

ctrlSaveTo_Error:
    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

ctrlSaveTo_Exit:
    Exit Property

ctrlSaveTo_Error:
    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.

Cheers,

Christian
0
 
LVL 31

Expert Comment

by:hnasr
ID: 38334275
"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.
0
 
LVL 1

Author Closing Comment

by:JP_TechGroup
ID: 38345896
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

0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

580 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