vba cannot update (1 Viewer)

exaccess · Sep 9, 2016

Hi Folks,
I have two tables. tblTele has two columns and 300 rows. I use it as input. tbl.telephone has 5 columns, 0 rows. I must copy the 300 rows into the second table so that I have N rows. N<300. The copy process depends on specific values of input fields. Now when a row is complete I add it to the tbl. telephone. But this adding process does not work. Error 13. The essential code is below. Help appreciated.

Code:

Set rs = db.OpenRecordset("tblTelel")
    Set rt = db.OpenRecordset("tblTelephone")
    rs.MoveFirst
While Not rs.EOF
............statements preparing the fields suffixed fn............
    rt.AddNew
    Debug.Print "addnew"
    rt.Fields("ADI").Value = Nz(fnADI, "")
    rt.Fields("SOYADI").Value = Nz(fnSOYADI, "")
    rt.Fields("ADRES").Value = Nz(fnADRES, "")
    rt.Fields("TEL").Value = Nz(fnTEL, "")
    rt.Fields("SEL").Value = Nz(fnSEL, "")
    Debug.Print "sel"
    rt.Update
 EndofLoop:
    rs.MoveNext
    Debug.Print "Loop ended I="; I
Wend

Ranman256 · Sep 9, 2016

you dont need vb to add records.
use an append query. Thats why they exist.

exaccess · Sep 10, 2016

Ranman256 said:
you dont need vb to add records.
use an append query. Thats why they exist.

I tried that also I am getting the same error. All my fields in target table are strings.

pbaldy · Sep 10, 2016

I would also use an append query, but your mistake is probably due to not referring to the source recordset:

Nz(rs!fnADI, "")

exaccess · Sep 10, 2016

pbaldy said:
I would also use an append query, but your mistake is probably due to not referring to the source recordset:

Nz(rs!fnADI, "")

The reason I am using these variables prefixed fn (Correction the first post was a typo) is that the data in one record of tblTelephone is extracted from 10 to 15 tblTele records. This is quite difficult logic necessitating calculations, scans for strings on field values of consecutive records of tblTele. I considered using these variables in the code to simplify the implementation of the logic. In any case I tried also the SQL version it does not work. Error 13. Help is appreciated. Code is below:

Code:

SQL = "INSERT INTO [tblTelephone] ([tblTelephone].[ADI], [tblTelephone].[SOYADI], " & _
        "[tblTelephone].[ADRES], [tblTelephone].[TEL], " & _
        "VALUES (fnADI, fnSOYADI, fnADRES, fnTEL")
    DoCmd.RunSQL SQL

pbaldy · Sep 10, 2016

If they're variables, you'd have to concatenate each into the SQL string. You'll see the problem using this:

http://www.baldyweb.com/ImmediateWindow.htm

If each record has to be prepared, I'd use the recordset method. Have you set a breakpoint and checked all the values? What is the text of the error?

exaccess · Sep 11, 2016

pbaldy said:
If they're variables, you'd have to concatenate each into the SQL string. You'll see the problem using this:

http://www.baldyweb.com/ImmediateWindow.htm

If each record has to be prepared, I'd use the recordset method. Have you set a breakpoint and checked all the values? What is the text of the error?

Yes I have a breakpoint and many debugprints in all critical stages. It fails exactly on the rt.update statement. Message Run-time error 13 Type mismatch.
By the way I tried the following test code it failed in the same way.

Code:

SQL = "INSERT INTO [tblTelephone] ([tblTelephone].[SOYADI]) " & _
        "VALUES '(" & fnSOYADI & ");'"
    DoCmd.RunSQL SQL

pbaldy · Sep 11, 2016

In the SQL you've got the single quotes outside the parentheses. For the recordset method to fail with that error, I'd guess you're putting a text value in a numeric field.

exaccess · Sep 12, 2016

Hi PBaldy. As you suggested I continued debugging with recordsets. The funny thing is now I managed to get new records into the table, but I cannot write down the last one. Same error 13. An ideas which may help resolve such issues? Thanks

pbaldy · Sep 12, 2016

What values are being inserted? What are the data types of the fields?

vba cannot update (1 Viewer)

exaccess

Registered User.

Ranman256

Well-known member

exaccess

Registered User.

pbaldy

Wino Moderator

exaccess

Registered User.

pbaldy

Wino Moderator

exaccess

Registered User.

pbaldy

Wino Moderator

exaccess

Registered User.

pbaldy

Wino Moderator

Users who are viewing this thread