Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2007
    Posts
    34

    Question Unanswered: Problem with subform...

    Hi guys/gals

    Okay really struggling with this one using Access 2007:

    I have a button which opens up a form on a new record (and also initially write a value to the table).

    Now there is a subform which needs to take the new automatically generated primary key (order number) that the new record has created. So I've told it to SaveRecord and Refresh. The order number is showing in the main form but the subform remains blank. Doesn't matter what I click in the subform.

    So doing some investigation I find that if I go into the main form and do something like enter a value into a box and press tab that then makes the subform update.

    I think what happens (from looking at the table it populates) is that the new data is stored in cache at first until I do something in the main form which makes it write the data to the table at which point the subform works.

    Since the main form is not accessed directly I think I need a way to make the database write the value to the table so that the subform loads straight away. Other suggestions welcomed.

    Is my diagnosis correct and more importantly does anybody know a way around it?

    Thanks

    Nick

  2. #2
    Join Date
    Jun 2007
    Posts
    34
    Update... thought I'd worked it out but it didn't actually work so I've deleted my solution.
    Last edited by n_s_simpson; 11-08-07 at 21:12.

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I think you have diagnosed it correctly. Are you doing a:

    docmd.RunCommand acCmdSaveRecord

    In code to save the record? Another trick that I have used in the past is to change the Master/Child link. Assume your Order Number appears in the textbox txtOrderNumber, change the Link Master Fields to txtOrderNumber.

    I do this frequently with combo boxes to lookup detail info for an order or something like that. The trick is that the data does not have to be stored before the link works.

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    NOTE: The key with using a control instead of data, the subform wizard won't let you do it, you have to do it after you put the subform on the main form then change the properties.

  5. #5
    Join Date
    Jun 2007
    Posts
    34
    Hi

    I will firstly try doing docmd.RunCommand acCmdSaveRecord in VB.

    I used the macro feature to do the SaveRecord request and that doesn't seem to work.

    I made a button within the subform which makes the main form create a new record, then go back (which removes the new record as it doesn't save it). Then the data populates inside the subform correctly. Tried putting that code in the OnLoad section of the main (and subform) but it doesn't work for some reason.

    Going to have another play tonight when I get home from work.

  6. #6
    Join Date
    Jun 2007
    Posts
    34
    Update:

    Well using docmd.RunCommand acCmdSaveRecord in code didn't make any difference. Think it's the same as doing it in a macro.

    Can't understand why there's no simple update command to force the system to write the cache to the table and then refresh.

  7. #7
    Join Date
    Jun 2007
    Posts
    34
    solved it...

    Basically the button that created the new record and populate it with the order number then allowed me to create a new record, which saves the current record. Then I told it to select the last record (which deletes the cache and doesn't save that record). Doing this shows the record as it should be with the subform populated.

    Very odd how I had to get around this. You would think MS would put a function in to do this. Oh well.

Posting Permissions

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