CODEWITHSUNDEEP
vbams-accessdaoms-access-2016recordset
Hassan2015
Hassan2015

Reputation: 13

Sql query not return data

I use this code for choose code number a procdut in VBA Access and test for found duplicate save product.

Dim db as dao.database
Dim rs as dao.recordset

Set db = currentdb

Sql_string = "SELECT code_number FROM table_product WHERE name_product ='Printer HP Color Laser Jet 550dn'"

Set rs = db.openrecordset(Sql_string)

If rs.recordcount > 1 then 
    Msgbox "Duplicate Product"
    db.close
    rs.close 'all seted to nothings
    Exit sub
Else:Text1.value =rs!code_number
End if

This code does not work to identify the repeat product name.

Although the product is repeated several times, it always returns the value of one, I check it for different product name but not detect repeat product name.

The following code works fine with a lot of similarity to the above code:

Dim db as dao.database
Dim rs as dao.recordset

Set db = currentdb

Sql_string = "SELECT product_name FROM table_product WHERE code_number ='INK001'"

Set rs = db.openrecordset(Sql_string)

If rs.recordcount > 1 then 
    Msgbox "Duplicate Product"
    db.close
    rs.close
    Exit sub
Else:Text2.value =rs!product_name
End if

I use Access 2016. Does anyone know what the problem is? Please guide me. I'm totally confused.

Check table structure and field name . Check in any database file with this code . Check sql_string in query work correctly but vba not work correctly. Ommm repaire office . Read dao documents. No result.

Upvotes: 1

Views: 64

Answers (2)

Hassan2015
Hassan2015

Reputation: 13

In the table, the problem was solved by specifying both fields as an index. I don't know the exact reason, there is a need to read more about DAO.

Upvotes: 0

Gustav
Gustav

Reputation: 55961

Try this:

Dim db As dao.database
Dim rs As dao.recordset

Set db = currentdb

Sql_string = "SELECT code_number FROM table_product WHERE name_product = 'Printer HP Color Laser Jet 550dn'"

Set rs = db.openrecordset(Sql_string)

If rs.recordcount > 0 then 
    Msgbox "Duplicate Product"
Else
    Me!Text1.value = rs!code_number
End if
rs.close

Or use DLookup to reduce to a bare minimum.

Upvotes: 0

Related Questions