Hi,
I got this piece of code but it's giving me error 13 : Types don't match..
Pointer is set to Set rec =

Anyone sees what's wrong with this ?

Private Sub Kapitaal_AfterUpdate()
Dim intKap As Currency
Dim SQL As String
Dim rec As DAO.Recordset


intKap = Me.Kapitaal.Value

SQL = "SELECT Verzekering.v_ID, Verzekering.maatschappij, Verzekering.soort, " _
& "Verzekering.ProvisiePerc, Polissen.VerzId, " _
& "([Polissen].[Kapitaal]*[Verzekering].[ProvisiePerc]) AS Prov " _
& "FROM Verzekering " _
& "INNER JOIN Polissen ON Verzekering.v_ID = Polissen.VerzId;"

Set rec = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)

If Not rec.EOF Then
MsgBox rec!Prov, vbOKCancel, "testing"
End If

Select Case Me.VerzId
Case 1
Me.Provisie = intKap * (rec!ProvisiePerc)
Case 2:
Me.Provisie = intKap * (rec!ProvisiePerc)
Case 4:
Me.Provisie = intKap * (rec!ProvisiePerc)
Case Else
Me.Provisie = 0
End Select

rec.Close
Set rec = Nothing
End Sub

Are [Polissen].[Kapitaal] and [Verzekering].[ProvisiePerc] number fields that can be multiplied? Are Verzekering.v_ID and Polissen.VerzId both the same type of field?

Hi RuralGuy,
[Polissen].[Kapitaal] = Currency (Euro)
[Verzekering].[ProvisiePerc]= number (double precision)

[Verzekering].[v_ID] = (Auto)number (PK)
[Polissen].[VerzId] = number

ProvisiePerc is supposed to hold a percentage value.
Is the number propertie wrong ?

Is [Polissen].[VerzId] a LongInteger? An AutoNumber is a LongInteger.

Is [Polissen].[VerzId] a LongInteger? An AutoNumber is a LongInteger.
Yes, [Polissen].[VerzId] is a LongInteger

Hmmm...
As a test, remove:
& "([Polissen].[Kapitaal]*[Verzekering].[ProvisiePerc]) AS Prov " _
from the SQL and see if the problem goes away.

Have you checked to make sure you don't have any MISSING references?

Hi RuralGuy,
You're the man !
First I removed the line from query, didn't make a difference, then I checked the references while I knew I set the DAO reference.. But I set it to DAO 3.51 and when I changed it to DAO 3.6 the problem was gone..!
Problem solved !

Thanks !!

Got one more question.
Now I have to change the Kapitaal field to let the calculation do it's thing, and fill the value for Me.Provisie.
Is it possible to put in the code that if Field1 OR Field2 OR Field3 are editted the calculation has to run again?
Or would this kill the possibility to manually set a value for Me.Provisie ?

I'm sorry but I don't understand what you are asking. Start a new thread with the question and others will see it too.