Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55

    Unanswered: Two form with common field does not operate the same

    Hi,
    I have a table: tblPropertyList
    I have two forms: frmAllFields frmUpdates
    Each form has a Text Box that is 'control sourced' by the same field... INTERNALCOMMENTS.

    The text box is a free text field used for makeing notes on a particular record... I have the following code in both form's VBE code...

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    INTERNALCOMMENTS = Format$(Date, "Short Date") + " " + INTERNALCOMMENTS
    End Sub

    This code inserts todays date preceeding any data entry so the user knows whan a preveiouse note is entered... the INTERNALCOMMENTS Field is suppose to stay updated regardless if the entry is made from either form.

    In the frmUpdates it works fine.. the date does enter properly upon exiting the text box... no matter where you go, ie, <CR> or click to another field, or go to another record(and return)

    Here's the problem: In the frmAllFields the date does not appear unless I go to another record and then return.... it does not update with a <cr> or clicking on another field... I have to exit that record and return...
    It appears I must go to another record and return, It does not update if I simply go to the other form and return.

    In one form it works... the other form.. 'almost' works

    I have tried everything in my feeble ability. I have removed all the code except the above, thinking something was effecting it, any suggestions?

    Am I clear..?? I am not a programer but I do take directions well... thanks in advance.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Maybe a refresh (or Forms!MyForm.Refresh) command or me.MyFieldName.requery (or Forms!MyForm!MyFieldName.requery) command somewhere in your routine?
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55
    Hi pkstormy, thanks for your assistance.
    ((remember I am not a programer, but I try!!)

    I put in the following lines into my routine, but the debugger keeps comming up... here are the lines I attempted with the debugger comment
    BTW, the debugger comes up when I try to leve that record, not when clicking on another field or entering a <cr>

    '--attempt 1 --------
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    INTERNALCOMMENTS = Format$(Date, "Short Date") + " " + INTERNALCOMMENTS
    Forms!frmAllFields!INTERNALCOMMENTS.Requery
    End Sub
    'Dubugger comment: Run-time Error '438'
    'Object doesn't suport this property or method

    '--- attempt 2 -------
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    INTERNALCOMMENTS = Format$(Date, "Short Date") + " " + INTERNALCOMMENTS
    Me.INTERNALCOMMENTS.Requery
    End Sub
    'Results: window - Microsoft Visual Basic
    ' Compile Error
    ' Method or data member not found ((debugger highlights .Requery))

    '--- attempt 3 ----
    Regarding the line (Forms!MyForm.Refresh), since I am not a programmer, I am not sure where that goes... do I need a special 'sub' command routine to put it in?

    Thanks again for your help <and patience>

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    The refresh command would probably go before your INTERNALCOMMENTS = Format$(Date, "Short Date") + " " + INTERNALCOMMENTS
    command. Try also putting the requery command before this line. I think MSAccess might be getting confused on that you're trying to set the same field value before the field (and record) is actually updated and you're doing it on the form's BeforeUpdate event verses the field's BeforeUpdate. You may want to try putting your commands in the AfterUpdate verses the BeforeUpdate. The refresh command sort of "sets" all the values in the form (like pushing the enter key on all the fields). But I think the AfterUpdate might work better for you and on the field verses the form. Try putting your command in the field's AfterUpdate event (or even try it on the BeforeUpdate for the field).
    Last edited by pkstormy; 09-15-06 at 12:52.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55
    Hi PK
    This is what I have... I have tried various combinations of commenting in and out the refresh and requeries but noting seems to work

    Private Sub Form_BeforeUpdate(Cancel As Integer) 'this is the original, and closest to working-must go to another record and return however
    'Private Sub Form_AfterUpdate(Cancel As Integer)
    'Forms!frmAllFields!INTERNALCOMMENTS.Requery
    'Me.INTERNALCOMMENTS.Requery
    'Forms!frmAllFields.Refresh
    INTERNALCOMMENTS = Format$(Date, "Short Date") + " " + INTERNALCOMMENTS
    End Sub

    I think I tried all the combinations but to no avail.

    The most annoying thing is, I have this same routine on the other form (frmUpdates), and it works fine.

    On this form (frmAllFields), it only works if I leave the record and return back then the date is entered fine...

    I will keep beating on it and If I make any headway I will let you know if I get any Epiphanys ;-)

    Thanks for your help.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Do you still need to do this on the FORMs BeforeUpdate or can it be done on the FIELD's BeforeUpdate/AfterUpdate? I'm still guessing that if it only updates when you leave the record and come back again that it's got to be something with a refresh or requery of that field. Also, Is INTERNALCOMMENTS a fieldname? If so, then you should use me!INTERNALCOMMENTS or Forms!frmAllFields!INTERNALCOMMENTS = Format$(Date,"Short Date") + " " + me!INTERNALCOMMENTS (or Forms!frmAllFields!INTERNALCOMMENTS or Forms!frmUpdates!INTERNALCOMMENTS). I'm not sure if you have a variable named INTERNALCOMMENTS or not. Lastly, if you're dealing with a MainForm/Subform, the syntax will may be a little different. Also, if you do this on a FORM event, do you want it to put the date still in the field with no comments if none are entered? If you can't get it to work, maybe if you upload a mini portion of the mdb, maybe I can take a look and see it better and fix it quickly. It's difficult to envision how you're doing this (don't give up though - others might have a few suggestions).
    Last edited by pkstormy; 09-15-06 at 15:56.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55
    Hi again Paul... lotsa good questions, let me try to answer as best I can.

    Yes. INTERNALCOMMENTS is the field name on the Table (tblPropertyLists)

    The big picture is, I have two forms that have the same field INTERNALCOMMENTS. I want the field to be edited from either form. One form is simply a cut down version of the other with fewer fields INTERNALCOMMENTS is suppse to operate the same in either form.

    Regarding the statement:
    INTERNALCOMMENTS = Format$(Date, "Short Date") + " " + INTERNALCOMMENTS

    I dont thinks that is causing the problem although I did try one of your suggestions and it worked the same as far as this problem goes.

    I am not using sub forms, it is a very simple format with the forms being a bit complicated on how the data is used...

    All I am trying to do in this INTERNALCOMMENTS field is make a date stamp that puts the date at the top when ever anything is entered in the field... simple as that... so the user knows when it was last edited.

    Does this make sense?
    Thanks again amigo.

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yes - it kind of makes sense although if I saw it, it'd probably be easier to solve. Other than that, all I can do is make suggestions from the clues you've supplied. I wonder if a setfocus command might work (although I still think a refresh or requery seems to be the problem - because usually when you go to another record and come back it involves that kind of solution). Again, I'm kind of guessing without seeing it. I'll try to think on it some more and maybe you might get some other suggestions in the meantime. Are both forms open simultaneously? That might direct it to a setfocus command where you would set the focus to the form you're on. Again, I'm kind of stabbing at solutions here. I thought you might get some other suggestions but it looks like no one else is up to tackling this. I do think I could solve it pretty quickly though if you sent an mdb with the specific forms and just a few sample records. I'll see if I can come up with anything in the meanwhile and re-reading your clues.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55
    Hi Paul,

    For what it's worth, I just found out that both forms function the same.. (got mixed up on what was the difference between changing fields and changing records)

    The date appears correctly when exiting the INTERNALCOMMENTS field to another record, but does not appear if I exit INTERNALCOMMENTS to another field within the same record... I must leave to another record

    Still beating it...

  10. #10
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55
    I think our posts crossed.

    Your Idea of a setfocus is probably the way to do it...
    How would I put that in the routine? (not a programmer)
    should it have its own sub, or should it be embedded in this sub?

    I would be happy to cut down the table and post the Forms, have not done that, the mdb is very large(400K+-) even though there is only about 200 records in it.

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Setfocus is used like Forms!frmAllFields.setfocus or you can set it to a specific field (i.e Forms!MyForm!MyField.setfocus or me.MyField.setfocus). Where you'd put it is the question. I'm still not sure though if that's the route to go. Another question - do you have the form's Cycle property set to All Records or Current Record?
    Last edited by pkstormy; 09-15-06 at 22:18.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55
    Cycle property is set to all records (default)
    Not sure why that would make a difference.
    Would like the date to appear <ideally> when the you enter the first character into the field... that way you could tab through the field without the date updating or click into the field then click out of the field (without entering a character) and not have a date update. bottom line is I want the date entered whenever there is new data entered into the field.

    Make sense?

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    And running your commands under the Field AfterUpate or Before Update events verse the Form AfterUpdate or BeforeUpdate events doesn't make a difference? Still wondering....
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  14. #14
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55
    Im not sure I understand the question..
    I have tried running
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    and have tried
    Private Sub Form_AfterUpdate(Cancel As Integer)

    I do not know what you mean "Field AfterUpdate or Before Update"
    (sorry for being so dumb)
    and thanks for your perisitance.

  15. #15
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You are doing these commands on the Form's properties (i.e. FORM_BeforeUpdate). If you click on the INTERNALCOMMENTS specific Field in your form (looking at the properties) and put the code in the AfterUpdate or BeforeUpdate event of that Field verses the Form itself. Then the code you posted would then look like Private Sub INTERNALCOMMENTS_AfterUpdate.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •