Problem with Findfirst and bookmarks - please help

[stillnew2vb]

Greetings Gurus...

Please help before I completely lose it....

I have a subform whose underlying table is called Purchases.The 1st field in the subform comes from a table called Category, the 2nd & 3rd fields in the subform comes from a table called Products.

When a user goes to edit the form they select an "item" from a combobox (field 1) which then filters the second combobox called "product" so that only certain products are selectable.The "product" combobox (field 2) after-update does a Dlookup to the Products table comparing the ProductID and if the Product isn't in there does an add-new and writes the new product to the table.

The problem I am having is with the 3rd field - ProductCode. I am trying to find a way that, if a new product (field 2) is created, when the user enters the product's product code, that code gets written to the Products table where it corresponds to the product.

My current code

Private Sub ProductCode_LostFocus()

If Me.ProductCode.OldValue = Me.ProductCode Then
    MsgBox "no change" ' in case the productcode already exists
Else
    Dim OrderAdmin As Form
        Set OrderAdmin = Forms!OrdersEdit!OrderAdmin
    Dim strBookmark As String
    'Dim rs As Object ?
    'Set rs = Me.Recordset.Clone ?
    OrderAdmin.RecordsetClone.FindFirst "[ProductCode] = '" & Me.Recommendation.Column(3) & "'" ' Me.Recommendation is the product
        If Me.RecordsetClone.NoMatch Then
        MsgBox "no entry found"
        If Not rs.EOF Then OrderAdmin.Bookmark = strBookmark
        Else: OrderAdmin.Bookmark = OrderAdmin.RecordsetClone.Bookmark
        End If
          'ProductCode = Me.ProductCode
End If

I have been going round in circles trying different ways to get this to work and would greatly appreciate a solution to my problem even if I have to use a different method.

Many thanks in advance.

 

[stillnew2vb]

Problem solved.

Here's my code for interest's sake.

If Me.ProductCode.OldValue = Me.ProductCode Then
    MsgBox "no change"
Else

Dim strProduct As String, strTblProduct As String
strProductCode = Me.ProductCode
strTblProduct = Nz(DLookup("ProductCode", "dbo_Products", "[Type] = '" & strProduct & "'"))

If strTblProduct <> strProductCode Then
    Dim db As Database, tb As DAO.Recordset
    Set db = CurrentDb
    Set tb = db.OpenRecordset("dbo_Products", dbOpenDynaset, dbSeeChanges)
            tb.FindFirst ("[Type]= '" & Me.Recommendation & "'")
            tb.Edit
            tb!ProductCode = Me.ProductCode
            tb.Update
            tb.Close
End If
End If

I'm sure there are probably better ways of doing this but it gets the job done.

 

[gemma-the-husky]

that doesnt look right

find first in this exanple will find an existing record

you are then editing the productcode of an existing record. surely that isnt what you mean?

tb.Edit
tb!ProductCode = Me.ProductCode
tb.Update

 

[stillnew2vb]

Hi Dave,

That is exactly what I want to do. Column 2 from left to right in the datasheet is a combobox with a selection of existing products. If a product that is already in the products table is selected then the after-update for that field autocompletes the product code field and skips to the next field. If the product is not found in the products table then the after-update action writes it in.

The lost focus action of the product code (Column 3) then checks if a code exists and if not writes it to the product code field in the "products" table.

I agree it is not particularly elegant but it is short & sweet and it works. If you have the "right" way to do it I would like it if you would teach me what it is.

 

[gemma-the-husky]

no thats fine - i think i didnt quite understand exactly what you are doing.