Character limit for Unbound Textbox on Form

[KrisDdb]

I have a form with a couple of unbound text boxes and combo boxes. I can edit the text boxes and select different options in the combo boxes, and save with these updates in a table. For this I use an update query, that runs whenever I click on the save button on the form.
The problem: when I try to put large amount of text (exceeding 255 characters) in one textbox, say ‘txtNote’ and save it, I get a “runtime error - 3271 : Invalid property value”.
Points to note:
· The field in the table (say ‘Note’) where it saves the txtNote entry is a ‘memo’ datatype. So that’s not the reason for the problem!
· Our software application reflects the data from this table in its fields.


· When I input the text in the software in the concerned field directly (even more than 255 chars can be typed in the software side) and then try to display it in the form, it does so in the txtNote! It doesn’t truncate the extra characters.
So now the question is, how do I make the textbox on the form accept more characters than 255 and save it?? Let me know if I should provide more information.

Thanks for your time and thoughts.

[RuralGuy]

Why are your controls *not* bound?

[KrisDdb]

Thanks for your reply.

As for your question, the field that i am dealing with here is a multi-user field. And i believe, if I use a bound control, it would keep the record locked until it is totally written. Whereas with the unbound control, it locks the record only at the moment of saving it. I wouldnt want to lose this functionality for my users.

Not sure if this might help: I have tried using the bound controls too, to do the updates. In this case I noticed something that I haven't seen before! After I hit the 'save' button, I still get the "runtime error 3271". However, after I close the error window, I see that the data is saved back to the table, and it even reflects in the software application fields. Though this method is doing the updates, its pretty annoying if you have to see an error pop-up everytime you try to add a note longer than 255 chars, which is quite often in my case. I would be relieved if there were a way to bypass this error at least!!

Any suggestions?

Note: Pls correct me if I am wrong about my impression of bound/unbound controls.

[KrisDdb]

Is there atleast a way to trap the error? some kind of error handling so the users dont get to know about this?? Even that should suffice if it wont put any breaks in the execution!
()

[RuralGuy]

How about a temporary table that is local to the PC and then an UPDATE or APPEND.

[KrisDdb]

@ RuralGuy: I got the problem solved And I apologise for the late reply.

Since the original code was working, I was trying to just set a trap for the error it was throwing up. Also it was a runtime error. So I could see that the text box was taking up the data.. only it couldnt pass it on to the table. Creating a temp table and update query seemed extra work. Also, I wanted to use as little code as possible. But it sure was an interesting thought.

So this is what I did. I put the below line of code before the execution of the code for the textbox

On Error Resume Next

which trapped the error. That might not be the best way to do it, but it serves the purpose. I never had to use an error trap before, so I was breaking my head on it But with a little googling and 'forumming', I got the fix. Thanks for all your thoughts and time.

[RuralGuy]

You would be better to trap the error you want to ignore only. You are now basically ignoring *any* error then you get.

[KrisDdb]

hmm! I see what you mean. I did not realise for a second that I was just ignoring it and not actually trapping it. So I should rather be calling it 'error handling' instead of 'error trapping'
But anyways, I could not find a way to trap it. And eventually I had to come up with this.

[RuralGuy]

What is the code you have under your save button starting with Private Sub ... on thru End Sub. Copy and paste here so we can see it if you can. Using the Code Tags helps too (the # sign in advanced posting)

[KrisDdb]

This is the code:

 
Private Sub Command4_Click()
    
    Dim st As String
    st = "frmDetailed"
    
    
    Dim db As DAO.Database
    Set db = CurrentDb
    
    Dim qd As DAO.QueryDef
    Set qd = db.QueryDefs("updateQuery")
    
    qd.Parameters("note_key") = Me.Text14
    qd.Parameters("Staff") = Me.List16
    qd.Parameters("Topic") = Me.List18
       On Error Resume Next
    qd.Parameters("Note") = Me.Text12
 
    
    qd.Execute
    
    DoCmd.Close acForm, st
    
End Sub

When I click the save button, the update query runs using the parameters given and thus saves in the table behind. That is where I was facing the error when I was tryin to pass more than 255 chars through Text12. I would rather like to use an error trap that would trap only the error 3151 instead of using On Error Resume Next. But I couldnt come up with one.

[RuralGuy]

Try this code:

Private Sub Command4_Click()
   On Error GoTo ErrorHandler
   '-- I don't think you need the following 2 lines
   '   Dim db As DAO.Database
   '   Set db = CurrentDb
   Dim qd As DAO.QueryDef
   Set qd = CurrentDb.QueryDefs("updateQuery")
   qd.Parameters("note_key") = Me.Text14
   qd.Parameters("Staff") = Me.List16
   qd.Parameters("Topic") = Me.List18
   qd.Parameters("Note") = Me.Text12
   qd.Execute
ExitLabel:
   On Error Resume Next
   Set qd = Nothing
   DoCmd.Close acForm, Me.Name, acSaveNo
ErrorHandler:
   If Err.Number <> 3271 Then
      '-- Show all other errors
      MsgBox "Error No:    " & Err.Number & vbCr & _
             "Description: " & Err.Description
   End If
   Resume ExitLabel
End Sub

[KrisDdb]

Try this code:

Private Sub Command4_Click()
On Error GoTo ErrorHandler
'-- I don't think you need the following 2 lines
' Dim db As DAO.Database
' Set db = CurrentDb

Yep! I agree I do not need them. I put them in so that I dont have to repetitively type CurrentDb in the future, if need be
And coming to the code, after I tried your code, I used to get the msg box with Error: 0 and Description: (nothin in here)
I tweaked it a bit, and now it works like a charm. Would really appreciate it if you could let me know if there are still any discrepancies with it.
Heres my code:

    On Error GoTo ErrorHandler
    
    Dim qd As DAO.QueryDef
    Set qd = CurrentDb.QueryDefs("uqryNote")
    qd.Parameters("note_key") = Me.Text14
    qd.Parameters("Staff") = Me.List16
    qd.Parameters("Topic") = Me.List18
    qd.Parameters("Note") = Me.Text12
    qd.Execute
    
ErrorHandler:
    If Err.Number = 3271 Then
    
        Resume ExitLabel
        
    Else
        
        MsgBox "Error No:     " & Err.Number & vbCr & _
               "Description:  " & Err.Description
           
    End If
    
ExitLabel:
    On Error Resume Next
    Set qd = Nothing
    DoCmd.Close acForm, Me.Name, acSaveNo

This prevents from getting the error prompt everytime the error is 3271. Also, I believe this would throw up the prompt when I get a different error.

Thankyou again for your valuable input.

[RuralGuy]

Your code always falls through the ErrorHandler and mine does not.

[KrisDdb]

Hmm.. that makes sense. But I did not want the msgbox to pop up evn when there was no error. After considering your last input, I am using this:

On Error GoTo ErrorHandler
 
Dim qd As DAO.QueryDef
Set qd = CurrentDb.QueryDefs("uqryNote")
qd.Parameters("note_key") = Me.Text14
qd.Parameters("Staff") = Me.List16
qd.Parameters("Topic") = Me.List18
qd.Parameters("Note") = Me.Text12
qd.Execute
DoCmd.Close acForm, Me.Name
 
Exit Sub
 
ErrorHandler:
If Err.Number = 3271 Then
 
Resume ExitLabel
 
Else
MsgBox "Error No: " & Err.Number & vbCr & _
"Description: " & Err.Description
 
End If
ExitLabel:
On Error Resume Next
Set qd = Nothing
DoCmd.Close acForm, Me.Name

I believe now it doesnt go to the error handler always. Opinions/suggestions?
p.s: thanks to you, I have realised and learnt some simple but important things

[RuralGuy]

The code I posted would not have poped up a MsgBox unless there was an error other than 3271.