I have a form (frmProduct&Distribution) which contains 5 tabs (Product,Item Codes, Territory, Internet, Licensee Product Reference).

Under tab 'Licensee Product Reference' I have a subform (frmPromotionsub2) which appears in single form view, containing in particular a field called DistributorRef.

When I initially go to the 'Licensee Product Reference' tab, I want it to go to a new record.

If the user selects an existing record and goes to the 'Product' tab and subsequently returns to the 'Licensee Product Reference' tab, I want it to go to the record that was previously chosen and NOT to a NEW RECORD.

Here's the code I have so far: -

Option Compare Database
Public strDistributorRef As String
Option Explicit

Private Sub tabProd_Dist_Change()

Dim strProductID As String
Dim strSQL As String
Dim strDistributorCode As String
Dim strLicenseeCode As String

Select Case Me![tabProd&Dist].Value
Case 0
strDistributorCode = Me.Form!frmPromotionSub2!DistributorCode & ""
strLicenseeCode = Me.Form!frmPromotionSub2!LicenseeCode & ""
strDistributorRef = strDistributorCode & strLicenseeCode
Case 1
Case 2
Case 3
Case 4
If strDistributorRef = "" Then
DoCmd.GoToRecord , , acNewRec
Else
' DoCmd.GoToRecord acActiveDataObject, , acGoTo, 1
Me.frmPromotionSub2.Filter = "[DistributorRef] = '" & strDistributorRef & "'"
Me.frmPromotionSub2.FilterOn = True
End If
End Select

End Sub


I'm trying to resolve the issue either using a filter or IDEALLY the GoToRecord method. I can calculate the actual value later.

Hello Aziz,

You could use this piece of code:
Dim rs As Object
Set rs = Me.RecordsetClone

rs.FindFirst = "[DistributorRef] = '" & strDistributorRef & "'"

If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
End If
Hope this helps.

OK. I will try that.