Subform error - No link in related field

[asteadman]

Hi all,

I have a simple Ordering DB with tables:
Products - Order-line (one-many)

I have an Order form with an order-line subform based on a query which pulls out a description from Products and displays on the form.

When a user scans a product into the Order-line and the Product isn't in the Product table I get the error:
""The Microsoft Access database engine cannot find a record in the table "Products" with key field(s) "Product_code".

This s fine and what I would expect. The issue is that they use the Runtime and instead of bringing up the error the DB crashes. Any ideas how I can handle this better so the runtime doesn't crash?

 

[asteadman]

I need to do this:

Private Sub Product_code_BeforeUpdate(Cancel As Integer)
IF product_code exists in products table THEN
'Transfers the price of the product to the OrderLine table
Me.txtPrice = Me.ProductsPrice
ELSE
MsgBox "Enter valid product code"
Me.Undo
End Sub

Can anyone hep me with the syntax?

 

[asteadman]

Tried this but no joy:

Private Sub Product_code_AfterUpdate()
Dim search As Variant
search = DLookup("[Product_code]", "Products", "[Product_code] = " _
    & Me.Product_code)

If search < 0 Then
    MsgBox "Enter valid product code"
    Me.Undo
Else
    'Transfers the price of the product to the OrderLine table
    Me.txtPrice = Me.ProductsPrice
End If
End Sub

 

[asteadman]

Found a solution. Who said talking to yourself isn't a good thing?

Private Sub Product_code_AfterUpdate()
Dim code_search As Variant
code_search = DLookup("[Product_code]", "Products", "[Product_code] = " _
    & Me.Product_code)

If IsNull(code_search) Then
    MsgBox "Enter valid product code"
    Me.Undo
Else
    'Transfers the price of the product to the OrderLine table
    Me.txtPrice = Me.ProductsPrice
End If
End Sub

 

[bob fitz]

Perhaps you just need to trap the error with some error code.
Alternatively you could try the following code in the Before Update event

Dim search As Variant
search = DLookup("[Product_code]", "Products", "[Product_code] = " _
    & Me.Product_code)

If search < 0 Then
    MsgBox "Enter valid product code"
   [B] Cancel = True[/B]
Else
    'Transfers the price of the product to the OrderLine table
    Me.txtPrice = Me.ProductsPrice
End If

 

[asteadman]

Perhaps you just need to trap the error with some error code.
Alternatively you could try the following code in the Before Update event

Dim search As Variant
search = DLookup("[Product_code]", "Products", "[Product_code] = " _
    & Me.Product_code)

If search < 0 Then
    MsgBox "Enter valid product code"
   [B] Cancel = True[/B]
Else
    'Transfers the price of the product to the OrderLine table
    Me.txtPrice = Me.ProductsPrice
End If

Thanks for that Bob, that would work too I seem to be having more success on the After_Update for some strange reason and after 3 hours of trying to figure it out I think it is time for a coffee

 

[arnelgp]

you have a Dlookup there, should be DCount:

search = Nz( DCount("*", "Products", "[Product_code] = " _
& Me.Product_code), 0)

 

[asteadman]

you have a Dlookup there, should be DCount:

search = Nz( DCount("*", "Products", "[Product_code] = " _
& Me.Product_code), 0)

Hi there, thanks for this, I have tested it and indeed it works.

Could you give me a quick overview of the difference and benefts for future reference?

 

[arnelgp]

Hi there, thanks for this, I have tested it and indeed it works.

Could you give me a quick overview of the difference and benefts for future reference?

again i see, some changes:

search = Nz( DCount("*", "Products", "[Product_code] = " _
& Me.Product_code), 0)

if search = 0 then
...

function Dlookup returns the field value you specified in your first parameter if the condition is met. Otherwise it will return Null. it is therefore advice to enclose it in Nz() function to ensure that correct datatype is passed when a Null is being returned from our lookup function.

DCount on the other hand, returns the count of how many records, the condition on your where parameter is met. It also returns Null value if no condition is met.

since we expect a number to return from DCount, we enclosed it to Nz() function with 0 as the last argument to return 0 if nothing is returned.
for DLookup, if you expect a string to be returned, put a vbNullstring ("") as its last parameter to return string, 0 for numeric.

 

[Minty]

If you can only enter products from your product table you would be better off make a combo box linked to your Products table and only let users select from that - if it's not there they can't select it.
This also has the benefit of you being able to display a description, and possibly a price in the drop down list, but store the ID hidden which makes for a nicer end user experience.

 

[asteadman]

If you can only enter products from your product table you would be better off make a combo box linked to your Products table and only let users select from that - if it's not there they can't select it.
This also has the benefit of you being able to display a description, and possibly a price in the drop down list, but store the ID hidden which makes for a nicer end user experience.

Thanks for the input. I avoided a combo because the Products table has around 2000 records and they often use a barcode scanner, it's just the odd occassion where they have forgot to register the product in the table that the error used to occur and annoy them.