Data type conversion error

[Mattias]

Hi

In the two commeented lines below I get Runtime error 3421, Data type
conversion error.
The datatypes on both side are the same!

Mattias

Dim db As Database, rs As Recordset, L As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("SäljredovisningTillUtskrivnaUbetaldaFråga")
If rs.RecordCount > 0 Then
rs.MoveFirst
While Not rs.EOF
If IsNull(rs("Betalningsnr")) Then
L = L + 1
While DCount("*", "[SäljredovisningTillUtskrivnaUbetaldaFråga]", _
"[Betalningsnr]=" & L) > 0
L = L + 1
Wend
rs.Edit
rs("Betalningsnr") = L
'rs("Betalningar.Säljredovisningnr") =
("Säljredovisninghuvud.Säljredovisningnr")
'rs("Betalningsbelopp") = ("BeräknadAttBetalaUt")
rs("Betalningssättnr") = 1
rs("Betalningskategorinr") = 3
rs.Update
End If
rs.MoveNext
Wend
Else
Set rs = Nothing
Set db = Nothing
End If

 

[HSalim]

Mattias,
I am having difficulty understanding what you are doing: Your code is not
commented and column names are in Swedish(?) so I cant even make too much
sense of what it is.
Still, I think i have a clue, and it appears that you are doing some things
wrong.

1. rs("Betalningar.Säljredovisningnr") =
("Säljredovisninghuvud.Säljredovisningnr")
Access is trying to set the value of Säljredovisningnr, which appears to be
a Number (from the nr at the end)
the value "Säljredovisninghuvud.Säljredovisningnr" which is a string. If
this is a column name - which I think is what you want it to be,
Where is this value coming from? It does not appear to be from the
current recordset!

- but I could be wrong - also because I suspect a recordset doesnot offer
the ability to set qualified names
in te format "a.b" as you have used above.

Similarly, in rs("Betalningsbelopp") = ("BeräknadAttBetalaUt") you are
psssing the String "BeräknadAttBetalaUt" to your
recordset field Betalningsbelopp. Perhaps that should have been
rs("Betalningsbelopp") = rs("BeräknadAttBetalaUt")



2. In the nested loop
While DCount("*",
"[SäljredovisningTillUtskrivnaUbetaldaFråga]", _
"[Betalningsnr]=" & L) > 0
L = L + 1
Wend
rs("Betalningsnr") = L

it appears that you are trying to find ampty slots and fill a record
number...
IF that is the case, you have a questionable design for your table.
Further, this process of repeatedly
counting records in a table will not scale well. You will slow down
performance once the number of records increases.


HS

 

[Mattias]

Thanks, it works well now...se below

rs("Betalningar.Säljredovisningnr") =
rs("Säljredovisninghuvud.Säljredovisningnr")
rs("Betalningsbelopp") = rs("BeräknadAttBetalaUt")

Mattias

Mattias,
How can I help you if you don't provide the information needed?
They are both field names in WHICH recordset?
It appears that you want to copy data from one table to another.

rs("Betalningar.Säljredovisningnr") should be rs("Säljredovisningnr")
("Säljredovisninghuvud.Säljredovisningnr") ' where is this data coming from

Perhaps you need a Dlookup to say
rs("Säljredovisningnr") = DLookup( ["Säljredovisningnr"],
["Säljredovisninghuvud"], and whatever criteria..)

For the next value, perhaps
rs("Betalningsbelopp") = rs("BeräknadAttBetalaUt")

HS

Hi
Thanks for the reply.

You opened my eyes thanks...they shall not be strings, they are both
fieldnames in the recrodset
Can you please help me to the correct syntax to use in those two rs.edit
lines.

Thank you

Mattias

Mattias,
I am having difficulty understanding what you are doing: Your code is not
commented and column names are in Swedish(?) so I cant even make too much
sense of what it is.
Still, I think i have a clue, and it appears that you are doing some things
wrong.

1. rs("Betalningar.Säljredovisningnr") =
("Säljredovisninghuvud.Säljredovisningnr")
Access is trying to set the value of Säljredovisningnr, which appears

to
be

a Number (from the nr at the end)
the value "Säljredovisninghuvud.Säljredovisningnr" which is a string. If
this is a column name - which I think is what you want it to be,
Where is this value coming from? It does not appear to be from the
current recordset!

- but I could be wrong - also because I suspect a recordset doesnot offer
the ability to set qualified names
in te format "a.b" as you have used above.

Similarly, in rs("Betalningsbelopp") = ("BeräknadAttBetalaUt") you are
psssing the String "BeräknadAttBetalaUt" to your
recordset field Betalningsbelopp. Perhaps that should have been
rs("Betalningsbelopp") = rs("BeräknadAttBetalaUt")



2. In the nested loop
While DCount("*",
"[SäljredovisningTillUtskrivnaUbetaldaFråga]", _
"[Betalningsnr]=" & L) > 0
L = L + 1
Wend
rs("Betalningsnr") = L

it appears that you are trying to find ampty slots and fill a record
number...
IF that is the case, you have a questionable design for your table.
Further, this process of repeatedly
counting records in a table will not scale well. You will slow down
performance once the number of records increases.


HS

Hi

In the two commeented lines below I get Runtime error 3421, Data type
conversion error.
The datatypes on both side are the same!

Mattias

Dim db As Database, rs As Recordset, L As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("SäljredovisningTillUtskrivnaUbetaldaFråga")
If rs.RecordCount > 0 Then
rs.MoveFirst
While Not rs.EOF
If IsNull(rs("Betalningsnr")) Then
L = L + 1
While DCount("*",

"[SäljredovisningTillUtskrivnaUbetaldaFråga]",

_
"[Betalningsnr]=" & L) > 0
L = L + 1
Wend
rs.Edit
rs("Betalningsnr") = L
'rs("Betalningar.Säljredovisningnr") =
("Säljredovisninghuvud.Säljredovisningnr")
'rs("Betalningsbelopp") = ("BeräknadAttBetalaUt")
rs("Betalningssättnr") = 1
rs("Betalningskategorinr") = 3
rs.Update
End If
rs.MoveNext
Wend
Else
Set rs = Nothing
Set db = Nothing
End If