stillnew2vb
Registered User.
- Local time
- Today, 02:53
- Joined
- Sep 20, 2010
- Messages
- 34
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
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.
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
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.