Split Forms Buggy

[David]

I'm having a number of issues with split forms in Access 2007, mostly around
adding new records coupled with code running in the form_current event.

If I start adding a new record (in either the split form or form parts of
the form) and then move to a new record, I get duplicate rows and incorrect
rows showing at the bottom of the split form. It's like it gets out of sync
and the display is incorrect. If I then close and reopen the form, the data
is OK and the form displays correctly.

This seems to be caused by some code I have which runs when the form_current
method is called. If I disable it, everything seems fine. But I can't see
for the life of me why it won't work.



Here is the code:

Private Sub UpdateStatusBar()
'This routine updates the contents of the status bar with the list of
attributes for this person.


Dim dbsPJ As DAO.Database
Dim QD1 As QueryDef
Dim rstAttributes As DAO.Recordset
Dim intAnswer As Integer
Dim strStatus As String

On Error GoTo ErrorHandler


'Check we're not adding a record.
If Me.NewRecord Then
'The user is adding a record, so just set to status bar to blank and
exit.
Forms!PersonalDetails.txtStatus.Value = ""
Exit Sub
End If


' Get attributes for this person.
Set dbsPJ = CurrentDb
Set QD1 = dbsPJ.QueryDefs!RetrieveAttributesQuery
'QD1.Parameters!PersonalIDParameter = txtPersonalID.Value
QD1.Parameters!PersonalIDParameter = Me.Recordset!PersonalID
Set rstAttributes = QD1.OpenRecordset


'Display attributes in status bar.
strStatus = ""
Do Until rstAttributes.EOF
strStatus = strStatus + rstAttributes!PersonalAttributeDescription +
" "
rstAttributes.MoveNext
Loop
Forms!PersonalDetails.txtStatus.Value = strStatus


'Cleanup
rstAttributes.Close
dbsPJ.Close

Set rstAttributes = Nothing
Set dbsPJ = Nothing

Exit Sub

ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

End Sub


The form contains details about individuals. Name, address etc. The idea
is that a status bar at the bottom of the form is updated with each cutomer's
'attributes'. These are stored in a table called 'PersonalAttributes' and the
query above just grabs the attributes for the customer, and builds a string
with them e.g. "Volunteer Board Member". A text box control at the bottom of
the form is then updated with this string.

Can anyone help?

Thanks very much

David

 

[Allen Browne]

David, there are bugs associated with split forms (e.g. records disappear on
undo), but I did not experience this one the way I tried it.

What I tied was a database with 3 tables:
- the Customers table imported from Northwind;
- tblRole, with one Text field named RoleID.
- tblCustomerRole = a junction table between Customers and tblRole
(fields CustomerID + RoleID as primary key.)

Then I created a Split form bound to the Customers table, added an unbound
text box named txtStatus, and put the code below in Form_Current. It seemed
to work okay when adding new records, so your issue must be related to
somthing else.

Your query seems to use a parameter, so I'm wondering if this could be a
dependency issue, where the query depends on text boxes on the form and the
form also depends on the query. If so, the code below might be a workaround
for you.

Anyway, this approach seemed to work okay.
Try it, and see if it solves your issue:

Private Sub Form_Current()
Dim rs As DAO.Recordset
Dim strSql As String
Dim strOut As String
Dim lngLen As Long
Const strcSep = ", "

If Not Me.NewRecord Then
strSql = "SELECT RoleID FROM tblCustomerRole WHERE CustomerID = """
& _
Me.CustomerID & """ ORDER BY RoleID;"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)
Do While Not rs.EOF
strOut = strOut & rs!RoleID & strcSep
rs.MoveNext
Loop
rs.Close
End If

With Me.txtStatus
lngLen = Len(strOut) - Len(strcSep)
If lngLen <= 0& Then
If Not IsNull(.Value) Then
.Value = Null
End If
Else
strOut = Left(strOut, lngLen)
If .Value = strOut Then
'do nothing
Else
.Value = strOut
End If
End If
End With

Set rs = Nothing
End Sub

FYI, the bugs I'm aware of in A2007 are listed here:
http://allenbrowne.com/Access2007.html#Bugs

 

[David]

Allen, I just wanted to thank you heaps for going to so much trouble and
replying.

I may not be able to try this for another 24 hours or so, so I wanted to let
you know I've seen your post. I hope it works. I'll definitely let you know
how I get on.

Cheers

David

 

[David]

Allen, that did indeed fix the problem.

Thanks very much, I had been trying to fix that for some time.

Cheers

David