Custom Inputbox

[Movian]

Hey,
I have jsut come upon a problem with the inputbox where some of my users are putting excesivly long strings (Greater than 255 chars). For custom drop down options.

I have a form that alows them to edit these items using an inputbox and am having problems as it is only showing 255 chars. I want to setup a custom inputbox form but i would like to be able to use it just like the inputbox

eg.

Dim Variable as string
Variable = MovianInput("Please enter your text below")


Now while i have no problem setting up the form im trying to figure out how to setup the function to open the form and return the final string value...

I am hoping its somthing simple and its just because i havn't had my coffe yet that the solution eludes me.

As always apriciate any help.
:beerchug:

[Movian]

Ok well i have somthing that seems to be working... its a botch job and im sure there is a better way of doing it. But now I am running into a different issue.

I am trying to get the text of a selected item to put as the default for the Custom Input Box. The SQL looks right and i know the values are correct but its returning 0 rows EVEN though there is a matching record. This seems to happen when the Value Field containts more than 255 characters (Its an Nvarchar(MAX) field). I also have the same problem when i atempt to delete these items. It seems that going over 255 characters breaks a lot of the built in controls and functions.. Any adive ?

Dim myrs As New ADODB.Recordset, tmp As String
Dim SQLtext As String, varItm As Variant

For Each varItm In Me.DropList.ItemsSelected
SQLtext = "SELECT * FROM tblLookupValues WHERE [Form1] = '" & txtForm & "' AND [Control] = '" & txtControl & "' AND [Value] like '" & Me.DropList.ItemData(varItm) & "'"
MsgBox SQLtext
myrs.Open SQLtext, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
MsgBox myrs.RecordCount
Next
tmp = CInputBox("Please alter the item selected", "Alter", myrs("Value"))
If Not IsNull(tmp) And Not tmp = "" Then
myrs("value") = tmp
myrs.Update
End If
myrs.Close
Set myrs = Nothing
Me.DropList.Requery


Also here is the function code i am using for the custom input box.


Option Compare Database
Option Explicit

Public TxtValue As Variant

Public Function CInputBox(labelText As String, titleText As String, Optional defVal As String)
TxtValue = vbNo
DoCmd.OpenForm "frmInputbox"
Forms("frmInputBox").lblDisplay.Caption = labelText
Forms("frmInputBox").Caption = titleText

If Not IsMissing(defVal) Then
Forms("frmInputbox").txtInput = defVal
End If
Forms("frmInputbox").Modal = True
Do While TxtValue = vbNo
DoEvents
Loop
CInputBox = TxtValue
End Function

Public Sub InputSaveText()
TxtValue = Forms("frmInputbox").txtInput
End Sub

[Tommy]

I think I would split the string into an array of strings with a limit of 255 in each element. Add a table with a join on it and place all of the strings in this table. Major PITA but that is the easiest and simplest way I can see to take care of it once and for all. :)