Records incorrect when new records added in other form

[kforeman]

I have form setup with two cascading combo boxes (facility and date) to select a record (other navigation, including mousewheel, is disabled). There is a button to open another form which allows addition/deletion of records by facility and date. When this form is closed, the combo boxes update accordingly on the initial form. However, if I select a new record whose facility/date I have just entered, it seemingly shows the wrong values for several of the other fields. If I check the corresponding table, the values are actually at default, though, and when I reopen the form they have reverted to default. Any ideas on how to solve this? I'm tempted to simply have the first form close and reopen when the second is closed, but couldn't figure out the syntax to close a form with a button on a different form. Let me know if there's any more information I need to provide. Thanks for any suggestions.

 

[RuralGuy]

It sounds like you have not saved the "new record" yet. You could also be up against a timing issue, but I doubt it.

 

[viperstingray]

your best bet would be to throw in a refresh or me.requery command into your code...probably upon close. I cannot be too sure of where you would put it because I need to see your database to do so. Your problem is that the data is not writing to the table right away. try putting in a refresh button or something that would requery the information and stick it into the tables.

 

[kforeman]

Thanks for the suggestions. At first I was getting write errors upon closing the second form, so I put in a save record on deactivate on both of them. And I have a requery on the close button of the second form. Those both seemed to have worked because it eliminated the write error and the combo boxes display the new values on the first form. Could it be a problem that I have the requery set to happen at the same time as the closing of the second form and the save record set on deactivate? Maybe I'll try moving those around tomorrow.

Thanks

 

[kforeman]

Both forms have "DoCmd.RunCommand acCmdSaveRecord" set to the deactivate event procedure (per msdn knowledgebase, so I don't know if that's the best place to put it). That got rid of the write errors, but the combo boxes were not accurate til I added the requery. I will try your suggestion tomorrow morning, though. Thanks

 

[kforeman]

Yes, I just have the two combo boxes requeried upon hitting the close button. I unfortunately don't have access to the db right now, but I'll take a look in the morning.

 

[kforeman]

Ok, so I stupidly had turned off the record navigation buttons before I had finished the form, so I didn't notice what my problem actually is: when I use the combo boxes to select one of the records added after the first form was opened, it always selects the first record.

Here's the code for selecting the record from the second combo box:

Sub datecombo_AfterUpdate()
    Me.RecordsetClone.FindFirst "[Report ID] = " & Me![datecombo]
    Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Here's the code for the close button on the second form:

Private Sub closewalkthrough_Click()
On Error GoTo Err_closewalkthrough_Click

DoCmd.RunCommand acCmdSaveRecord

Dim datecombosource As String
    datecombosource = "SELECT [Reports].[Report ID], [Reports].[Date] " & _
    "FROM Reports " & _
    "WHERE [Facility ID] = " & [Forms]![Report]![facilitycombo].Value

[Forms]![Report]![datecombo].RowSource = datecombosource
[Forms]![Report]![datecombo].Requery

    DoCmd.Close
    DoCmd.SetWarnings True
Exit_closewalkthrough_Click:
    Exit Sub
Err_closewalkthrough_Click:
    MsgBox Err.Description
    Resume Exit_closewalkthrough_Click
End Sub

Checking the table at the same time, new records are going in fine, and they should be being saved, but they aren't actually showing up for the primary form. Also, the "Record _ of __" isn't updated until the form is closed/reopened.

Thanks again for your help.

 

[kforeman]

Sorry to keep posting, but I added a

Private Sub Form_activate()
Requery
End Sub

and it's updating the records and letting me select the correct one now. Thanks for all your help. I know this probably isn't the most elegant way of accomplishing what I need to, but it works.

(edit: moved requery to close button of other form to avoid a conflict, but still works)