rsanglim
asked on
Refresh subform on different tab control
Greetings, Experts...can somebody please help me resolve this? I have a form with 23 tabs, each with a subform linked to its own SQL table. Let's just deal with two for now (subfrm_00_MAIN and subfrm_01_TopDogHouse). Using the following code, I'm trying to refresh the Record_Number of the subform on the second tab, and see it when I click on that tab. This works great if I close the form and reopen, but I get Run-time error '3021': no current record when clicking the 2nd tab. I've tried for a day to resolve this, but can't seem to dial it in. Here's the code:
Private Sub cmdNew_Click()
On Error GoTo Err_cmdNew_Click
Dim strSQL As String
DoCmd.GoToRecord , , acNewRec
DoCmd.SetWarnings False
Me.Record_Number.Value = Format(Now(), "mmddhhnnss")
strSQL = "INSERT INTO dbo_T_01TopDogHouse (Record_Number) VALUES ('" & Me.Record_Number.Value & "');"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Me.Record_Date.SetFocus
Exit_cmdNew_Click:
Exit Sub
Err_cmdNew_Click:
MsgBox Err.Description
Resume Exit_cmdNew_Click
End Sub
--------------------------
Private Sub TabCtl0_Change()
Select Case Me.TabCtl0.Value
Case 0
Me.subfrm_00_MAIN.Form.Rec
Me.subfrm_00_MAIN.Form.Rec
Case 1
Me.subfrm_01_TopDogHouse.F
Me.subfrm_01_TopDogHouse.F
End Select
End Sub
Private Sub cmdNew_Click()
On Error GoTo Err_cmdNew_Click
Dim strSQL As String
DoCmd.GoToRecord , , acNewRec
DoCmd.SetWarnings False
Me.Record_Number.Value = Format(Now(), "mmddhhnnss")
strSQL = "INSERT INTO dbo_T_01TopDogHouse (Record_Number) VALUES ('" & Me.Record_Number.Value & "');"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Me.Record_Date.SetFocus
Exit_cmdNew_Click:
Exit Sub
Err_cmdNew_Click:
MsgBox Err.Description
Resume Exit_cmdNew_Click
End Sub
--------------------------
Private Sub TabCtl0_Change()
Select Case Me.TabCtl0.Value
Case 0
Me.subfrm_00_MAIN.Form.Rec
Me.subfrm_00_MAIN.Form.Rec
Case 1
Me.subfrm_01_TopDogHouse.F
Me.subfrm_01_TopDogHouse.F
End Select
End Sub
ASKER
I tried the subforms current event, without success :(
My current code works great if I close and reopen, but I don't want to have to do that each time when inserting a new record.
My current code works great if I close and reopen, but I don't want to have to do that each time when inserting a new record.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Steve...EXCELLENT! Simple and quick...don't know why I didn't see that earlier (what a knucklehead!).
...Thanks...Scott
...Thanks...Scott
When I use subforms on tabs I have found that to get any recordsourse information the code needs to be either in the subform or I use code in the main form such as:
intAns = Me.subformName.Form.Record
If intAns = 0 Then
What I want if there is no recordecount
Else
What I want if their is a recordcount
End If
I also used an if statement in the change event instead of a select case.
If TabCtlPO = (1 or 2) Then
What you want to do if the tab you are on equals this number
End If
hope this helps
gemost :)