Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    HMEpartsmanager is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2009
    Posts
    85

    Date Calculation When Switch From Form View to Datasheet View; Take Two

    I have had this issue for the past few years, and I am taking another stab at trying to find out how to fix it. If you care to review the old thread;

    The original thread is located here:
    https://www.accessforums.net/forms/d...alue-4926.html

    ... and happen to see something I am not seeing, please comment on it.

    This time, as well as referencing the original thread from a couple of years ago, I am showing pictures of what I mean. I have 'blued' out anything that is internal, but the rest is visible.

    PMS-Clip01
    Click image for larger version. 

Name:	PMS-Clip01.jpg 
Views:	10 
Size:	92.0 KB 
ID:	9054

    This is after I open the database, close the switchboard, and then press double-click the "PMS Purchase Orders - Main Form". This defaults to the first purchase order ever written. Notice how if I was to add a line item to the sub-form, that it already has a Task ID entered, and also has the ETA date for this line? That ETA date is taken from the Order ETA date from above.

    Form: PMS Purchase Orders - Main Form
    TextBox: ordETA
    Control Source: ordEta
    Format: mm/dd/yyyy
    Default Value: =Date()+14

    Form: PMS Purchase Orders - Subform
    TextBox: ordlineETA
    Control Source: ordlineEta
    Format: mm/dd/yyyy
    Default Value: =[Forms]![PMS Purchase Orders - Main Form]![ordEta]


    PMS-Clip02
    Click image for larger version. 

Name:	PMS-Clip02.jpg 
Views:	9 
Size:	83.2 KB 
ID:	9055

    This is when I advance the record to the last record in the database.
    Notice again how the individual line item has the correct ordlineEta that was grabbed from the record's ordEta above.


    So with just those things, you would think that all will go smoothly, but this is where it gets 'weird'.

    PMS-Clip03
    Click image for larger version. 

Name:	PMS-Clip03.jpg 
Views:	9 
Size:	187.7 KB 
ID:	9056

    This is when I switch to Datasheet View. Notice how the cursor is highlighted on the ACB. The arrow is indicating that line representing that record we were just viewing in Form View.

    PMS-Clip04
    Click image for larger version. 

Name:	PMS-Clip04.jpg 
Views:	9 
Size:	189.7 KB 
ID:	9057

    Now if I go to ANY OTHER RECORD, in this case, Record of ordId # 10819. This would represent another record, which is another purchase order. So I scroll to a new record that I want to work with.

    PMS-Clip-5
    Click image for larger version. 

Name:	PMS-Clip05.jpg 
Views:	11 
Size:	85.6 KB 
ID:	9058

    When I am in Datasheet View, scroll to a different record, and switch back to FORM VIEW, this is what you get. The individual line item 'ordlineEta' does not grab the current form's ordEta from the record I am working with.
    At this point, if I was to add a line item, it would use the date it has set up for me, so I have to change it. I can advance the record forward or backward, and it will show up just fine. It only happens when I do the sequence I described in the five pictures.


    It is really odd, and I am ready to try and tackle this issue again.

    Tim

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,046
    So the Order and each LineItem can have different ETA dates?

    In that last image, is the ETA value you are concerned about the default date showing in the new row? My guess is the DefaultValue property captures date when the form first opens. No other event with code to alter that captured value is triggered. Use code, maybe in main form Current event, to reset the DefaultValue property of the control in the subform.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    HMEpartsmanager is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2009
    Posts
    85
    Quote Originally Posted by June7 View Post
    So the Order and each LineItem can have different ETA dates?

    In that last image, is the ETA value you are concerned about the default date showing in the new row? My guess is the DefaultValue property captures date when the form first opens. No other event with code to alter that captured value is triggered. Use code, maybe in main form Current event, to reset the DefaultValue property of the control in the subform.
    June,

    Yes, the dates can be different for each line item. The goal here was when I create a purchase order, it takes today's date and adds 2 weeks for a delivery ETA. Now, when I enter each line item, this default date given for the purchase order, carries over for each new line item added to this purchase order. As I talk to the vendor, I will most likely get a real ETA and change it manually to what each line item will be. The first line item may have an ETA in a few days, the second line item a week, and maybe even a third line item have 3 weeks. Those ETA's are changed as I find out what the real ETA is. Should I not get an ETA, most things will arrive in 2 weeks, and it gives some form of ETA on reports of what is on order.

    As each line item arrives, I change the status of the line item to indicate it has been received, as well as enter the date the item arrived in the ETA field.

    The reason this is an issue, is that when I order an item, I do not know shipping costs most of the time; so when I get an invoice to enter to match up against this purchase order, I open up the purchase order form, switch to Datasheet View, scroll to the purchase order to match up, then swtich to the Form View of that purchase order. When I open into Form View to add the shipping line and it's cost, it will default to the year 2005. I try to catch this when I enter the line item of freight, but sometimes the A/P gal will enter the invoices and she will miss this date. I then later have to go in and change all these dates from 2005 to the date the item arrived. If the default value of 2 weeks was used, at least the line item would have a date associated with it; that is in this YEAR. :-)


    Yes, the last row of the subform where I would add a new line item, is the area I am concerned with. I think you are indicating something I came across as well. That when a form opens, the subform will run first, then form will fill in it's data from the record. As the line item needs data from the form (grabbing the ETA), it has nothing to grab yet as the subform is opening and filling it's data in first. At least that is what I am thinking the heirarchy of events is happening. You seem to think the same thing?

    Where would you suggest I add code to get the ETA date from the form that has not opened yet, instead of the subform grabbing data from record number one? If I understand the chain of events of what gets triggered, I almost need to go with;

    Subform events trigger
    Form events trigger
    ....add event here that sets focus back to the line item ETA
    ....add event here that now grabs the real ETA from the form than is now loaded
    ....add event here that switches focus back to the main form

    Do I have that correct?


    Tim

  4. #4
    Join Date
    Aug 2012
    Posts
    2
    I agree with Tim.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,046
    Don't know about the subform running first, I would have thought the reverse because how can the DefaultValue grab value from main form record if that record is not available? Would possibly see an #ERROR in the new row.

    I don't think focus needs to be set back and forth.

    Just an idea, not tested, but try code in the main form Current event to set DefaultValue property of the subform control. Me.subformcontainerName.Form.ETAcontrolname.Defaul tValue = Me.ETAcontrolname.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    HMEpartsmanager is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2009
    Posts
    85
    Quote Originally Posted by June7 View Post
    Don't know about the subform running first, I would have thought the reverse because how can the DefaultValue grab value from main form record if that record is not available? Would possibly see an #ERROR in the new row.

    I don't think focus needs to be set back and forth.

    Just an idea, not tested, but try code in the main form Current event to set DefaultValue property of the subform control. Me.subformcontainerName.Form.ETAcontrolname.Defaul tValue = Me.ETAcontrolname.

    June,

    The subform running it's events first was something I thought I read a while back of how events are triggered, and who goes first. That is what lead me to believe that the issue is related to how I wanted something that wasn't known yet; so it defaults to the only record it knows, and that would be record number one; not the record I am on and want the information from. But that read was quite a while back and I could be a bit mistaken.

    I was about to try your idea, but it got me thinking, that if I put that in the Main Form's current event, wouldn't that mean it would trigger each and every time a new record was advanced to the next record, or when you back in record numbers? If so, I think it would overwrite the date that it currently holds, even records where the value is already correct. Is there a way to word the code so that it would not run if an existing date is already set?

    Me.subformcontainerName.Form.ETAcontrolname.Defaul tValue = Me.ETAcontrolname

    Do I need to enter something like this?

    Me."PMS Purchase Orders - Subform".Form.ordlineEta.DefaultValue = Me.ordEta

    What I am not clear on is using quotes around a form name that has 'spaces' in the name. Can you help with the proper way to enter a form name when there are spaces in it? Also, would like your comment if you think there would be an issue with a continual reset of the line item field each time the main form record is changed. If so, I might need to add some form of IF NOTHING IN FIELD / Then Add This Statement / Else End.

    Thank you for reviewing this issue.

    Tim

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,046
    Setting the DefaultValue property will not overwrite data in existing records.

    Enclose names that have spaces, special characters, punctuation (underscore is exception), or are reserved words with []. Is why I avoid these elements in names.

    Me.[PMS Purchase Orders - Subform].Form.ordlineEta.DefaultValue

    Also, I always give subform/subreport container control name different from the object it holds, like ctrPO, then:
    Me.ctrPO.Form.ordlineEta.DefaultValue
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    HMEpartsmanager is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2009
    Posts
    85
    June,

    I will try your suggestions tomorrow. I will make a copy of the database to work on and see what happens using your strategy.

    Right now, it is 77 degrees, I have all my work done, and want to go home and mow the yard while drinking a beer. :-)

    Calling it an early day today!

    Tim

  9. #9
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,998
    Quote Originally Posted by June7 View Post

    ...Don't know about the subform running first...
    As odd as it sounds, this is correct! The order of events is

    1. Subform Opens
    2. Subform Loads
    3. MainForm Opens
    4. Mainform Loads

    One has to remember that we're talking about the Land of MS Access, here, and the Access Gnomes, unlike Spock, are not always logical!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  10. #10
    HMEpartsmanager is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2009
    Posts
    85
    Quote Originally Posted by Missinglinq View Post
    As odd as it sounds, this is correct! The order of events is

    1. Subform Opens
    2. Subform Loads
    3. MainForm Opens
    4. Mainform Loads
    I thought I had read that somewhere.

    I am going to try June's suggestion this morning. Hopefully that might work.

    Tim

  11. #11
    HMEpartsmanager is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2009
    Posts
    85
    Trying this now;

    Me.[PMS Purchase Orders - Subform].Form.ordlineEta.DefaultValue = Me.ordEta

    That returns a debug error when trying to open the form.

    Run-time error '2465':
    PMS - Project Management System can't find the field "l" referred to in your expression.

    I will keep going though and see what I can find.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,046
    From post 7:
    Also, I always give subform/subreport container control name different from the object it holds, like ctrPO, then:
    Me.ctrPO.Form.ordlineEta.DefaultValue

    I find that the naming can sometimes make a difference, seems to cause confusion if the container and its object have same name. Along the same line, I always give controls a name different from the fields they are bound to.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    HMEpartsmanager is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2009
    Posts
    85
    June,

    Shy of renaming my forms and/or controls; there must be a proper way to put together the correct statement, here is what I have.

    Form: PMS Purchase Orders - Main Form
    Name: ordEta
    Control Source: ordEta

    Form/Subfrom: PMS Purchase Orders - Subform
    Name: ordlineEta
    Control Source: ordlineEta

    I know I am missing a crucial, but simplistic, piece of this puzzle; can you help me code this line?

    Access 2003; using Access 2000 File format


    Tim

  14. #14
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,931
    Can you post a copy of your db.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,046
    The code you posted looks good to me and should work, however, as I said, sometimes I have encountered errors that seem to be fixed when I change control names and refer to those names. Have nothing else to suggest other than to echo Bob, if you can provide the db maybe someone can analyse and find solution. Follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Datasheet view sub form
    By Irishlad1967 in forum Forms
    Replies: 2
    Last Post: 05-10-2012, 09:23 PM
  2. Datasheet view of Split Form in a Tab??
    By tylerg11 in forum Forms
    Replies: 3
    Last Post: 12-19-2011, 04:37 PM
  3. BeforeUpdate in a Form in Datasheet view
    By fitshase in forum Forms
    Replies: 7
    Last Post: 07-29-2011, 08:53 AM
  4. Form opens table in datasheet view
    By franklbl in forum Forms
    Replies: 9
    Last Post: 03-23-2011, 09:43 PM
  5. Form - Datasheet View
    By kalove in forum Forms
    Replies: 0
    Last Post: 06-20-2007, 11:29 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums