How to fix run time error 3075 through VBA MS ACCESS?

0
votes

I am encountering a "Run-Time error 3075" with my code. I think I pretty much write the code right, the weird thing is, when I check the value of my variable, it's cut-off/shorten from what was supposed to be the full value of it, but when I tried outputting it in the "immediate window", it shows the full value.

I supposed to get all of this/my immediate window outputs this:

The quick brown fox jumps over the lazy dog.

But my variable only contains this:

The quick brown fox jumps o

The length of the real output that I'm trying to get is 611.

And then after this output, I'll get the "Run-Time Error 3075".

Here's a sample of my code:

           Dim rs2 As DAO.Recordset
           Set rs2 = CurrentDb.OpenRecordset("Select * FROM 2011_50K_ServiceText", dbOpenDynaset)

           If DCount("*", "2011_50K_ServiceText") = 0 Then 'checks if table is empty

           Else
           rs2.MoveFirst
           While rs2.EOF = False
           i = i + 1
           i2ndSetInterval = Format(i, "00")
           s50KServiceText = rs2!Field1

                        If InStr(s50KServiceText, "See") > 0 Or InStr(s50KServiceText, "See") = 0 Then

                           If InStr(s50KServiceText, "See") = 0 Then

                           Else
                               s50KServiceText = Left(rs2!Field1, InStr(rs2!Field1, "See") - 1)
                           End If
                                sT = "50000"
                                sFrequency = CStr(getFrequencyInterval(sT))
                                sFrequency = sFrequency & "_" & CStr(i2ndSetInterval)
                                sLength = Len(s50KServiceText)
                                Debug.Print (s50KServiceText)
                                DoCmd.SetWarnings (WarningsOff)
                                DoCmd.RunSQL "INSERT INTO 2011_VehicleDistanceBased([Vehicle],[Frequency],[Service_Text]) values ('" + LabelVehicle.Caption + "', '" + sFrequency + "', '" + s50KServiceText + "')"
                                DoCmd.SetWarnings (WarningsOn)
                        End If
           rs2.MoveNext
           Wend
           i2ndSetInterval = 0 'set it back to default
           i = 0
           rs2.Close
           Set rs2 = Nothing
           End If

Does anyone have any idea? It'll be much appreciated. Thank you

2
vbams-access
"when I check the value of my variable" when and how are you checking this? – Tim Williams
@TimWilliams mostly, I checked it at the point in which I encounter the error which is in the line where I am using the INSERT clause. I checked my variable there by hovering through it and adding it to "my watch" and see that it's cut-off/shorten but when I try reading the output on "Immediate Window", it shows the full output. – Silverbells
The Watch window will truncate long variables, so your problem is likely not that your variable is getting cut off, but that there's something wrong with your Insert statement. What's the text of the error message? – Tim Williams
do any of the values you're inserting contain single quotes? – Tim Williams
@TimWilliams I got it Sir. Indeed there is a sneaky single quote in the value I'm trying to fetch. I fixed it by replacing it with a null value before inserting it using the SQL clause. Thank you very much! – Silverbells

2 Answers

1
votes

I got a database where some lastnames got single quotes, and we need them. In case you need the single quote, what worked for me was using single quotes twice, and that fixed everything.

So try s50KServiceText = Replace(s50KServiceText, "'", "''") in case you need to save the '.

NOTE: Please,note is not the same '' than ". That's why I said single quotes twice

0
votes

I know there are different situation where this can occur but mine was solved when I found out that the value that I'm trying to fetch contains "single quote". The SQL clause causes an error because it reads it as well.

So the fixed that worked for me was this:

If InStr(s50KServiceText, "'") = 0 Then

Else

s50KServiceText = Replace(s50KServiceText, "'", "") <<<<--- this was the fixed

End If

DoCmd.SetWarnings (WarningsOff)

DoCmd.RunSQL "INSERT INTO 2011_VehicleDistanceBased([Vehicle],[Frequency],[Service_Text]) values ('" + LabelVehicle.Caption + "', '" + sFrequency + "', '" + s50KServiceText + "')"

DoCmd.SetWarnings (WarningsOn)

I hope it helps.