please help with adding records

[tweetyng]

Please help... with this one, I'm very appriciated.

table named: "tblCustomer", Primary key: CustomerID and VOH_ID.

If there r new records then I can use this button for adding new. However, if there are some records in this table such as:
CustomerID VOH_ID OptID
1 1 2
1 3 2
Then I get an error.

So I would like to add some codes but don't know how. Please help.


Private Sub cmdAdd_Click()
Dim rst As Recordset
Dim wsp As Workspace
Dim VioID As Integer

On Error GoTo Err_cmdAdd_Click
DoCmd.Hourglass True

Set wsp = DBEngine.Workspaces(0)
wsp.BeginTrans

Set rst = CurrentDb.OpenRecordset("tblCustomer")
VioID = 1

With rst
For VioID = 1 To 10
.AddNew
![CustomerID] = Me.txtCustomerID
![VOH_ID] = VioID
![OptID] = "2"
.Update
Next VioID
.Close
End With
Set rst = Nothing

wsp.CommitTrans

DoCmd.Hourglass False

Exit_cmdAdd_Click:
Exit Sub

Err_cmdAdd_Click:
DoCmd.Hourglass False
MsgBox Err.Description
Me.cmdAdd.Visible = False
wsp.Rollback
Resume Exit_cmdAdd_Click
End Sub

TN (USA). I'm using Windows XP, Access 2003, Crystal Reports 11 and SQL Server

 

[PHV]

Then I get an error
Well, fine.
We now know that you get an error.
Any chance you could post some more meaningful info like, say, which error message at which line of code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tweetyng]

It said "ODBC--call failed". But if in table I had:
CustomerID VOH_ID OptID
1 5 2

Then it would add CustomeID = 1 , VOH_ID = 1, then 2, 3, 4 when adding VOH_ID =5, getting an error with "ODBC -call failed" because there is alreay had CustomerID =1 and VOH_ID =5.

TN (USA). I'm using Windows XP, Access 2003, Crystal Reports 11 and SQL Server

 

[PHV]

So, you want to avoid creating dupes ?
Have a look at either the DLookUp function or the FindFirst method of the DAO.Recordset object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pbrodsky]

I usually use seek and nomatch to check whether record already exists. This requires setting and index (rst.index="PrimaryKey").

 

[BobRodes]

You could also just trap the error...?