Problem with refresh? on main form after closing modal form.

[Phred]

Access 2007 front end, multi user. Back end SQL Server 2008 r2.

I have a main form which displays property information. The combination of County and PIN is unique.



I have a button on the above main form called "Add New Property" when clicked opens a modal form, below, called "Add New Property." Here the end user can create a new record by selecting County, PIN, and other fields of information. When all information regarding the new property has been entered the end user clicks a "Save This Record" button which runs a query adding the new property to the Property table, and then the "Add New Record" modal screen closes. The screen and code below works fine.





The new Property is successfully been added to the Property table which is the underlying table for the Main Form "Property".

If you go to the Table Property you can see that the record has been added and all data is visible.

Now we are back at the main property screen seen below.



Now you want to go to the property you just added and have it display in the Detail section of the Property form.

There are two cascading combo boxes at the top of the screen in the form header. The first Combo allows you to choose the County, 1 Select County from a lookup table listing counties. When you select a County the second combo box will only display the PINs in that county. All this works fine. The code below selects the county and then sets the RowSource for the next Combo box. This code works fine.

Private Sub CBOSelCounty_AfterUpdate()
'Sets RowSource for Select PIN combo box. Select Pin combo box shows only PINs in the selected County.
Dim stCounty As String
stCounty = Me.CBOSelCounty.Column(1)
CBOFindAPin.RowSource = "SELECT dbo_Property.KPIN, dbo_Property.PropertyID " & _
"FROM dbo_Property " & _
"WHERE dbo_Property.County = '" & stCounty & "' " & _
"ORDER BY dbo_Property.KPIN;"
End Sub

The next code for the second combo box Select a PIN the user selects the PIN he just added. It shows up in the combo box fine. All code works.

Private Sub CBOFindAPin_AfterUpdate()
If IsNull(Me.CBOFindAPin) Then Exit Sub
With Me.RecordsetClone
.FindFirst "[PropertyID]= " & Me!CBOFindAPin.Column(1)
If Not .NoMatch Then
If Me.Dirty Then Me.Dirty = False
Me.Bookmark = .Bookmark
Else
End If
End With
Me.CBOSelCounty = ""
Me.CBOFindAPin = ""
End Sub

When the user selects the PIN in the second combo box, the information for the selected property displays in the Detail section of the form. This code works fine.

HERE IS WHERE I RUN INTO A PROBLEM

When I add a NEW property using the above process, the "Add New Property" screen closes. I come back to the main screen and I want to see the property I just added. So I select County for the county the property is located in. I can see and select the PIN that I just added. Now I want to view the property in the Detail section of the Property screen. So I select the County and the PIN as seen below. When Select PIN combo box is updated the new Property should appear in the Detail section of the form.



If I select any other EXISTING property and PIN the property appears. If I select the NEW Property I just added, which exists in the Property Table, absolutely nothing happens. The detail portion of the screen does not update.

If I go to the menu bar and select HOME and then click Refresh All I can come back to the combo boxes and select the newly added property and it appears fine in the Detail portion of the screen.

I have added me.refresh to multiple locations such as (just a few)....

Got Focus of Select Pin combo box.
On click of the Select Pin combo box.
On load of the Property Form

Absolute nothing I have tried works except clicking Refresh on the Home menu. If I close the Property form and reopen it work fine for that property.

The screens work, the code works, I can see the new data in the table, the correct data shows in the combo boxes but it just doesn't refresh.

Any ideas?

Thanks, Fred

I DON'T KNOW WHY THE GRAPHIC BELOW MY NAME APPEARS. JUST IGNORE IT. THE POST ENDS HERE.

[June7]

Must Requery the form, not Refresh.

[Phred]

I have tried Requery from several locations. None of them work. Tried it on the "2 Select PIN" Combo Box, "On Got Focus", and tried it on the "Property Form", "On Got Focus".

Is there another Event I should be using for this from? Nothing happens from the me.requery.

fred

[June7]

What is the code that opens the modal form? I would open it in Dialog mode as well. This will suspend code execution from the calling form until the second form closes. Example:

DoCmd.OpenForm "reportname", acNormal, , , , acDialog
Me.Requery

[Phred]

That did it.
Thank you very much.
I would like to leave this open for a couple days while I test it.
Have a great New Year
Fred

[John_G]

Did you make sure to requery the combo boxes too? Requerying the form wil not automatically requery the combo boxes.

me!comboboxname.requery

[Phred]

Thanks John I did requery them.
I will close this post.
Thanks all,
Fred