wrek, I Dl'd your '97 version and converted it '2000. I found common fields to relate the tables but no Relationships were set. (Conversion issue?) Anyway, I set the relationships and added .requery statements to the onCurrent events to the main and sub forms. The subForm requeries OK when the main record changes. I cannot get the Sub Sub to requery? Still trying.
I have exactly the same issue, on AXP, and with only a form and a subform. Reading various forums, there seems somewehre to be a thread that covers this but can I find it....
Thanks for that Rich, actually the code you posted was similar to mine - I guess the differences may be down to A97 vs AXP (?)
I've copied my code in here
SubForm Current Event:
Code:
Private Sub Form_Current()
Debug.Print "Subform Current Event"
CheckSubFormButtons
UpdateSubFormRecordInfo
End Sub
FormCurrent Event:
Code:
Private Sub Form_Current()
Debug.Print "MainForm Current Event"
FormatSellPrice
CalulateMarkupPercent
ShowProductImage
CheckButtons
CheckSubFormButtons
End Sub
Subroutine CheckSubFormButtons (Enables appropriate Next/Prev buttons on SubForm)
Code:
Sub CheckSubFormButtons()
Dim rst As DAO.Recordset
Set rst = Form_frmStockHeldsubform.Recordset
Debug.Print "CheckSubFormButtons Called with rst.RecordCount=" & rst.RecordCount
If rst.RecordCount > 1 Then
If rst.AbsolutePosition < 1 Then
Form_frmStockHeldsubform.cmdFirstRecord.Enabled = False
Form_frmStockHeldsubform.cmdPreviousRecord.Enabled = False
Else
Form_frmStockHeldsubform.cmdFirstRecord.Enabled = True
Form_frmStockHeldsubform.cmdPreviousRecord.Enabled = True
End If
If rst.AbsolutePosition = rst.RecordCount - 1 Then
Form_frmStockHeldsubform.cmdLastRecord.Enabled = False
Form_frmStockHeldsubform.cmdNextRecord.Enabled = False
Else
Form_frmStockHeldsubform.cmdLastRecord.Enabled = True
Form_frmStockHeldsubform.cmdNextRecord.Enabled = True
End If
Else
Form_frmStockHeldsubform.cmdFirstRecord.Enabled = False
Form_frmStockHeldsubform.cmdPreviousRecord.Enabled = False
Form_frmStockHeldsubform.cmdLastRecord.Enabled = False
Form_frmStockHeldsubform.cmdNextRecord.Enabled = False
End If
Set rst = Nothing
End Sub
Subroutine CheckButtons (Enables appropriate Next/Prev buttons on MainForm)
Code:
Sub CheckButtons()
Dim rst As DAO.Recordset
Set rst = Form_frmStockDescription.Recordset
Debug.Print "CheckButtons Called"
If rst.RecordCount > 1 Then
If rst.AbsolutePosition = 0 Then
Form_frmStockDescription.cmdFirstRecord.Enabled = False
Form_frmStockDescription.cmdPreviousRecord.Enabled = False
Else
Form_frmStockDescription.cmdFirstRecord.Enabled = True
Form_frmStockDescription.cmdPreviousRecord.Enabled = True
End If
If rst.AbsolutePosition = rst.RecordCount - 1 Then
Form_frmStockDescription.cmdLastRecord.Enabled = False
Form_frmStockDescription.cmdNextRecord.Enabled = False
Else
Form_frmStockDescription.cmdLastRecord.Enabled = True
Form_frmStockDescription.cmdNextRecord.Enabled = True
End If
Else
Form_frmStockDescription.cmdFirstRecord.Enabled = False
Form_frmStockDescription.cmdPreviousRecord.Enabled = False
Form_frmStockDescription.cmdLastRecord.Enabled = False
Form_frmStockDescription.cmdNextRecord.Enabled = False
End If
End Sub
Subroutine UpdateSubFormRecordInfo (Updates record X of Y textbox on subform - the Y text box is a count(*) function)
Code:
Sub UpdateSubFormRecordInfo()
Dim rst As DAO.Recordset
Set rst = Form_frmStockHeldsubform.Recordset
If rst.AbsolutePosition = -1 Then
Form_frmStockHeldsubform.txtCurRec = 1
Else
Form_frmStockHeldsubform.txtCurRec = rst.AbsolutePosition + 1
End If
End Sub
The code works as long as I have a breakpoint in; if I remove the breakpoint I get record 1 of Y in the subform and the subform buttons all disabled. On the basis that the 'Y' part of the X of Y does not appear on the form immediately and if I hold code execution up with a breakpoint things function as expected, I've got as far as concluding that the subform is taking it's time in getting the recordset from the database, and I'm guessing that I need the event that indicates that the subform data fetch is complete to trigger CheckSubFormButtons and UpdateSubFormRecordInfo. The probelm is that I can't find this event
Any suggestions on what the event is (if memory serves, it's available in ADO from VB, but this is a simple project that I shouldn't need to write a VB routine for...). If not, any ideas of alternative approaches that will do the job? Unfortunately the client doesn't want the standard Nav Bar on either Main or Subform, and this is getting silly!
After I wrote the last message I looked again at what I was doing and found the 'CurrentRecord' property. A combination of that and bits of your code works a lot better!
