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.
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.