data add edit problem

[Poweruser99]

I have made a custom form in access. Basically the user enters data about various IT assets. However if there is a duplicate asset in there the program crashes. Somehow I want to trap that error or if that record already exists in there then I want it to be edited. Below is the code I have and following is the error I am recieving from it.




Private Sub Command92_Click()




If IsNull(Me.Combo86) Or Me.Combo86 = "" Then
MsgBox "A YEAR is required in order to proceed"
ElseIf IsNull(Me.Combo89) Or Me.Combo89 = "" Then
MsgBox "A DIVISION is required in order to proceed"
ElseIf IsNull(Me.cbo_Desktop) Or Me.cbo_Desktop = "" Then
MsgBox "A Product is required in order to proceed"
ElseIf IsNull(Me.Text65) Or Me.Text65 = "" Then
MsgBox "A Quantity is required in order to proceed"
Else

rs.Find ("year" = Me.Combo86 & "division" = Me.Combo89 & "prod_description" = Me.cbo_Desktop

If rs.EOF Then
rs.AddNew
End If

rs.Fields("year&quot = Me.Combo86
rs.Fields("division&quot = Me.Combo89
rs.Fields("Prod_description&quot = Me.cbo_Desktop
rs.Fields("new units&quot = Me.Text65
rs.Fields("total price&quot = Me.Text76

rs.Update
rs.Requery
End If


End Sub

 

[RoyVidar]

Sorry, where's the error?

I've never used .find with more than one criteria, and I'm not sure it supports using a complex criteria. So there (and the concatination;-)), I think the error resides, even if you didn't tell.

You could however try the .filter property of the ADO recordset which supports more complex criterias. Just build the criteria as an ordinary SQL where clause without the keyword Where, perhaps something like this:

[tt]"year =" & Me.Combo86 & " and division =" & Me.Combo89 & " and prod_description =" & Me.cbo_Desktop[/tt]

Note - here you're concatinating a criteria string where all the fields are numeric. To allow for text, you'll need text qualifiers - apostrophe, for instance:

[tt]...division ='" & Me.Combo89 & "' and...[/tt]

- also check the help files

Roy-Vidar

 

[Poweruser99]

Thanks for the help so far.

I have entered the following.

rs.Filter = year & " = '" & Me.Combo86 & "'" And Division & " = '" & Me.Combo89 & "'" And Prod_description & " = '" & Me.cbo_Desktop & "'"

It still says type mis-match.

 

[RoyVidar]

[tt]rs.filter = "[year] =" & Me.Combo86 & " and division ='" & Me.Combo89 & "' and prod_description ='" & Me.cbo_Desktop & "'"[/tt]

Also note the brackets on [year] - it's a function, and shouldnt be used as field name.

 

[RoyVidar]

When I say shouldn't, I mean that it might still work just by surrounding the field name with [brackets].