Requery only working on second try

[NewTexican]

Ok, this one is driving me super nuts.
I have 2 pop ups. One pop is all plans. Other pop up is plans with a certain company. User selects plan from one pop up hits a command button and the plan shows up on the other pop up under the company name. I've done this before and not had any problems. Here is the deal: I pick a plan and my requery doesn't happen on the company pop up. I pick a second plan and my requery puts the first and the second plan on the company pop up together. It's like every other plan gets requeried. I've tried refresh, time delay and requery.

Private Sub Command29_Click()
On Error GoTo Err_Command29_Click

Dim ddb As Database
Dim rrs As DAO.Recordset
Dim pln1 As Long
Dim pln2 As String
Dim ccn As Long
Dim stdocname5 As String

Set ddb = DBEngine.Workspaces(0).OpenDatabase("...plan module I")
Set rrs = ddb.OpenRecordset("plan-company T", dbOpenDynaset)
pln1 = Me![plan numeric]
pln2 = Me![plan alpha]
ccn = Forms![common company].[Company Numeric]
stdocname5 = "plan f"

With rrs
.AddNew
![plan numeric] = pln1
![plan alpha] = pln2
![Company Numeric] = ccn
.Update
End With

rrs.Close
Set ddb = Nothing
Set rrs = Nothing

DoCmd.Save

Me.TimerInterval = 10000
DoCmd.Hourglass True



Forms![plan-company pop up].SetFocus
Forms![plan-company pop up].Requery
Forms![plan-company pop up].Refresh
DoCmd.Close acForm, stdocname5

DoCmd.Hourglass False

Exit_Command29_Click:
Exit Sub

Err_Command29_Click:
MsgBox Err.Description
Resume Exit_Command29_Click

 

[Blorf]

Sounds very annoying.

Any chance the setfocus coming before your requery is responsible?


ChaZ

 

[NewTexican]

No, I wish, but no. I put that there as a desparate attempt to figure this %&^** out. This is the strangest thing. The record get's saved to the table it just doesn't always show up after a requery on the pop up.

 

[willir]

NewTexican

Before you can requery, the data has to be written to the database. Perhaps if Refresh and then Requery, you may get the desired results.

Yea, I know, you add and update the rrs recordset before you get to the requery.

 

[NewTexican]

I gave both your suggestions a try. Refresh and then requery helps. Now the first addition doesn't show up. Subsequent additions do.

'add plan to company plan list

Private Sub Command29_Click()
On Error GoTo Err_Command29_Click

Dim ddb As Database
Dim rrs As DAO.Recordset
Dim pln1 As Long
Dim pln2 As String
Dim ccn As Long
Dim stdocname5 As String

Set ddb = DBEngine.Workspaces(0).OpenDatabase("\...path...")
Set rrs = ddb.OpenRecordset("plan-company T", dbOpenDynaset)
pln1 = Me![plan numeric]
pln2 = Me![plan alpha]
ccn = Forms![common company].[Company Numeric]
stdocname5 = "plan f"

With rrs
.AddNew
![plan numeric] = pln1
![plan alpha] = pln2
![Company Numeric] = ccn
.Update
End With

rrs.Close
Set ddb = Nothing
Set rrs = Nothing

Forms![plan-company pop up].Refresh
Forms![plan-company pop up].Requery
DoCmd.Close acForm, stdocname5

Exit_Command29_Click:
Exit Sub

Err_Command29_Click:
MsgBox Err.Description
Resume Exit_Command29_Click



End Sub

 

[NewTexican]

I'm thinking about entering code to close and reopen the form. It seems that if there is no records in the table that fills the first pop up adding a record and then requering the pop up doesn't work. If there are records in the table that fills the pop up then requery works. Is there something special about a query that returns no results? The form is a continuous form.