Avoid duplicate values 2

[djeeten]

Hi,

I have this main form frmOrders and subform sfrmOrders.

On the main form you can choose products from a listbox. If you click on a button next to the listbox, this product is added to a table called tblOrderlines. The products that have been choosen are seen in the subform. I get an error from Access when I choose the same product twice. There should be an error but I would like to give it myself by showing a simple message.

This is my code so far:
-------------------------------------------
Private Sub cmdChooseProduct_Click()
Dim con As Object
Dim stSql As String
Dim rs As Object
Dim stSql2 As String
Dim found As Integer

Set con = Application.CurrentProject.Connection

found = 0
stSql = "SELECT tblOrderlines.ProductId FROM tblOrderlines "
stSql = stSql & "WHERE tblOrderlines.OrderId =" & Me!OrderId
Set rs = CreateObject("ADODB.Recordset&quot
rs.Open stSql, con, 1 ' 1 = adOpenKeyset

While (Not (rs.EOF)) And found = 0
'cboId is the listbox
If rs![ProductId] = Me!cboId Then
Me.Undo
found = 1
End If
rs.MoveNext
Wend

If found = 1 Then
MsgBox "This product has already been choosen!"
Else
'Add product to the order
stSql2 = "INSERT INTO tblOrderlines (OrderId, ProductId)" & _
" VALUES (" & Me.OrderId & ", " & cboID & &quot "
Me.Refresh
con.Execute (stSql2)

sfrmOrders2.Requery
sfrmOrders2.SetFocus
End If
End Sub
-----------------------------------------------
The problem is that found always has the value 0. But I don't know why.

Could someone please help me?

Thanks in advance,

dj.

 

[gerard1979]

The most easy way to solve this is to set the docmd.runsql between :
Docmd.setwarnings false
Docmd.runsql strQL
docmd.setwarnings true

If adding a dup. value is invalid by the database rule (dup. your key) then the insertion is ignored and nothing happens.

 

[djeeten]

Thanks gerard1979, that helped a lot.

But I would still like to give the user a little message why the product is not inserted or it might also be nice to put the focus on the product(in the subform) that has been choosen in the combobox.

Any suggestions?

Thanks,

dj.

 

[gerard1979]

I'll give u the code I appreciate, maybe it works,
Ur code looks fine, but I never use it this way so I can't say anything about it, try this
Private Sub cmdChooseProduct_Click()
Dim con As adodb.connection
Dim stSql As String
Dim rs As new adodb.recordset
Dim stSql2 As String
Dim found As Integer

Set con = CurrentProject.Connection

found = 0
stSql = "SELECT tblOrderlines.ProductId, OrderID FROM tblOrderlines "
stSql = stSql & "WHERE tblOrderlines.OrderId =" & Me!OrderId
rs.Open stSql, con, 1, adlockreadonly ' 1 = adOpenKeyset

While Not rs.EOF
'cboId is the listbox
If rs![ProductId] = Me!cboId Then
found = 1
goto EndWhile
End If
rs.MoveNext
Wend
endWhile:
If found = 1 Then
MsgBox "This product has already been choosen!"
Else
'Add product to the order
stSql2 = "INSERT INTO tblOrderlines (OrderId, ProductId)" & _
" VALUES (" & Me.OrderId & ", " & cboID & &quot "
docmd.setwarnings false
docmd.runsql stSQL2
docmd.setwarnings true
sfrmOrders2.Requery
sfrmOrders2.SetFocus
End If
End Sub

Normally this should work, when it doesn't give me the structure of the form and subform,
is the cbo in the form, is orderID in the Form?

Now I see the fault, in the select tblOrderlines u need to give the orderID, else nothing is selected.

Good Luck!
P.S. I think u can hold ur own structure and only give OrderId in the StSQL