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?
I think you have diagnosed it correctly. Are you doing a:
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.
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.
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.