GoToRecord Issue

[aziz rasul]

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.

 

[ErikSnoek]

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.

 

[aziz rasul]

OK. I will try that.