0

Please assist with adding the value of Text34 from the user into thisParcel expression on MS ACCESS. I get a 3075 run time error when I run the code. I have rechecked my indentation, missing operators, but I cannot find the problem.

Private Sub Command10_Click()

DoCmd.SetWarnings (WarningsOff)

i = Val(Text2.Value)
w = Val(Text34.Value)

    If Text8 = "" Or IsNull(Text8) Then
        TrueOrFalse = 0
    Else
        TrueOrFalse = -1
    End If
    
    If IsNull(Len(List0.Value)) Then
        MsgBox "Choose allotment area"
        Exit Sub
    End If
    
    thisParcel = List0.Value & Left("00000000", 8 - Len(Trim(Str(i)))) & Trim(Str(i)) + 
                 Left("00000", 5 - Len(Trim(Str(w)))) & Trim(Str(w))
    
           
    DoCmd.RunSQL "INSERT INTO Schedule ( DiagramNo, OnGP, GPNo, ParcelCode, DiagramDeedNo ) VALUES ( '" & Text6.Value & "', " & TrueOrFalse & " , '" & Text8.Value & "' , '" & thisParcel & " , '" & Text22 & "');"
Zinhle
  • 1
  • It would help a lot with some sample values and expected results. – Gustav Nov 06 '21 at 07:59
  • Try a `Debug.Print` to see the value of `thisParcel`. Also it can help to build the SQL statement into a string (`strSQL="INSERT INTO Schedule......`) so that you can `Debug.Print` that as well. – Applecore Nov 06 '21 at 08:54
  • Hi Gustav, the result must look like this x06700240000000100000. The First 8 digits are from List0, the next 8 are from textbox2, and the last 5 are from textbox34. – Zinhle Nov 06 '21 at 09:22

1 Answers1

0

You have some typos, miss a line continuation, and the code can be reduced a little:

If IsNull(List0.Value) Then
    MsgBox "Choose allotment area"
    Exit Sub
End If
    
TrueOrFalse = (Nz(Me!Text8.Value, "") <> "")
    
thisParcel = List0.Value & _
    Left("00000000", 8 - Len(CStr(i))) & CStr(i) & _
    Left("00000", 5 - Len(CStr(w))) & CStr(w)
               
DoCmd.RunSQL "INSERT INTO Schedule ( DiagramNo, OnGP, GPNo, ParcelCode, DiagramDeedNo ) VALUES ( '" & Text6.Value & "', " & TrueOrFalse & ", '" & Text8.Value & "', '" & thisParcel & "', '" & Text22 & "');"

Also, consider my function CSql.

Gustav
  • 53,498
  • 7
  • 29
  • 55