type mismatch passing values to function

[alaric]

Hi,

I have trouble passing my calculated values to my function. Every time I get the message Type Mismatch:

this is the code I have:

Dim X As Byte
Dim InschrijfId As String
Dim Cnt4 As Byte

'berekening van STARTnummer lezen van txtPunt velden (start: 1(1-8), 9(9-16), 17(17-24), 25(25-33) etc
'X = stand teller -1 *8 (aantal punten per cursist) +1(startnummer reeks van 8)
'tel tot max. het aantal opgegeven cursisten
'zet variable InschrijfId door de tekst in txtCursist te verkorten totdat alleen
'het inschrijfid overblijft (functie RemoveIt.)
'roep functie saveIt aan met de variabele InschrijfId, om de punten op te slaan met variable punten veld start
For Cnt4 = 1 To Me.txtNrCursisten.Value
InschrijfId = RemoveIt(Me("txtCursist" & CStr(Cnt4)))
X = ((Cnt4 - 1) * 8) + 1
MsgBox "inschrijfid: " & InschrijfId & " En X: " & X
SaveIt "InschrijfId", "X <-- pressing the F8 gives the type mismatch message
Next Cnt4​

my function definition looks as follows:

Function SaveIt(InschrijfId As String, X As Byte)

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset 'Instantiate a new recordset
With rst
.Open "TAfdansen", cnn, adOpenDynamic, adLockOptimistic 'Open recordset against table
.AddNew 'Add new record
'.Fields("FieldName") = some Value 'Set values of the various fields
.Fields("InschrijvingsID") = InschrijfId
.Fields("Uitstraling1") = "Me.txtPunt" & X + 0
.Fields("Houding1") = "Me.txtPunt" & X + 1
.Fields("Techniek1") = "Me.txtPunt" & X + 2
.Fields("Maat1") = "Me.txtPunt" & X + 3
.Fields("Uitstraling2") = "Me.txtPunt" & X + 4
.Fields("Houding2") = "Me.txtPunt" & X + 5
.Fields("Techniek2") = "Me.txtPunt" & X + 6
.Fields("Maat2") = "Me.txtPunt" & X + 7
.Update 'Commit the change
End With

rst.Close '--- close the recordset
Set rst = Nothing '--- reclaim the memory the recordset was using

End Function​

I think access means that the values I pass to my functions are not the types my functions expects.
To my simple thinking.... access is wrong but is probably smarter then me

hope someone can help me out

thx
Al

 

[KenHigg]

For starters:

SaveIt(InschrijfId, X)

kh

 

[alaric]

thx Ken

I tried but with no luck:
get the message: compile error: expected: =

ideas

Al

 

[pono1]

Al,

Change

SaveIt "InschrijfId", "X

to

SaveIt "InschrijfId", X

and

Function SaveIt(InschrijfId As String, X As Byte)

to

Sub SaveIt(InschrijfId As String, X As Byte)

Regards,
Tim

 

[KenHigg]

I tried but with no luck:
get the message: compile error: expected: =

What line?

 

[alaric]

thx guys

I chaged my code to:

SaveIt InschrijfId, X

as soon as i walk through the code (F8) I get (hoovering the mouse over the value) following values:
inschrijfid = "774"
X = 1
this looks strange to me. shouldnt the 774 also not between the ""? because Im going to write records
The "774" is returned by the function removeIt

These values are passed use into the SUB (this was function):

Sub SaveIt(InschrijfId As String, X As Byte)

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset 'Instantiate a new recordset
With rst
.Open "TAfdansen", cnn, adOpenDynamic, adLockOptimistic 'Open recordset against table
.AddNew 'Add new record
'.Fields("FieldName") = some Value 'Set values of the various fields
.Fields("InschrijvingsID") = InschrijfId
.Fields("Uitstraling1") = "Me.txtPunt" & X + 0
.Fields("Houding1") = "Me.txtPunt" & X + 1
.Fields("Techniek1") = "Me.txtPunt" & X + 2
.Fields("Maat1") = "Me.txtPunt" & X + 3
.Fields("Uitstraling2") = "Me.txtPunt" & X + 4
.Fields("Houding2") = "Me.txtPunt" & X + 5
.Fields("Techniek2") = "Me.txtPunt" & X + 6
.Fields("Maat2") = "Me.txtPunt" & X + 7
.Update 'Commit the change
End With
rst.Close '--- close the recordset
Set rst = Nothing '--- reclaim the memory the recordset was using

End Sub​

The code stops at:
.Fields("InschrijvingsID") = InschrijfId

pressing F8 now... gives the error message

thx in advance for helping me out

Al

 

[pono1]

Al,

Here's my best guess of the moment...

Change

.Fields("InschrijvingsID") = InschrijfId

to

.Fields("InschrijvingsID") = clng(InschrijfId)

Regards,
Tim

 

[alaric]

oke
I did change it and it goes 1 rule further;
now it stops at;
.Fields("Uitstraling1") = "Me.txtPunt" & X + 0

is my syntax wrong? when I try to write a field with the value on my form in txtpunt1 txtpunt2 etc. or is it wrong the way I try to add a value to my variable and attach it to txtpunt?

Still dont get why, and whats the function of clng(inschrijfid)?

thx
Al

 

[Rich]

= "Me.txtPunt" & X + 0
should be
= Me.txtPunt & X + 0
no quotes

 

[alaric]

I found the solution:

the right way was:
.Fields("Uitstraling1") = Me.Controls("txtPunt" & X + 0).Value

thanks for putting me in the right direction!! You really helped me. thx again

still want to know what "CLng" does

Al