Restriciting User from using Enter Key in form field

[unislick]

I have an form that is used to export values to a text file. The address field is a 150 character field and I cannot have the user use the ENTER key in this field because it places a carriage return in the extract file causing displacement of data.

Is there anyway to restrict the user from using the Enter key or maybe even replacing all carriage returns with a space???

Any ideas?

Thanks!

 

[hwkranger]

goto optoins, keyboard, click on MOVE AFTER ENTER select, DON"T MOVE.

or you could write code to scan strings and replace crlf to a space...

Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[unislick]

Thanks for the help, but the Options Keyboard doesn't work to restrict the user from using ENTER...just tried...and also I think it is Access specific not form specific so each person who uses this form would have to change their Access specs and I do not want that.

Do you have any examples of code to cans strings and replace crlf to a space?

Thanks

 

[hwkranger]

shhhhhoooore!

this is untested, but this is something that should work, it's not the cleanest.

dim mystring as string
dim mydb as database
dim myrecord as recordset

set mydb = currentdb()
set myrecord = db.openrecordset("mytable/query&quot

if myrecord.bof = true then exit sub
with myrecord
if .recordcount then
.movefirst
do until myrecord.eof
mystring = !myfield
'object is to find the character, then build a new string using left, add a space, then right - 1 (b/c we took out the crlf) You might want to use variables to simplify this, it's rough on the eyes.
if instr(1, mystring, char(10), 0) then
mystring = left(mystring, instr(1, mystring, char(10), 0) & " " & right(mystring, len(mystring - instr(1, mystring, char(10), 0) - 1)
else if instr(1, mystring, char(13), 0) then
mystring = left(mystring, instr(1, mystring, char(10), 0) & " " & right(mystring, len(mystring - instr(1, mystring, char(13), 0) - 1)
else if instr(1, mystring, vbcrlf, 0) then
mystring = left(mystring, instr(1, mystring, vbcrlf, 0) & " " & right(mystring, len(mystring - instr(1, mystring, char(10), 0) - 1)
end if
.edit
!myfield = mystring
.update
loop
end if
end with
set myrecord = nothing
set my db = nothing

'have a nice day, i suggest testing this before using it ;-)






Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[hwkranger]

blatant mistake:

if instr(1, mystring, char(10), 0) then replace with:
if instr(1, mystring, char(10), 0) > 0 then Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[unislick]

Thanks!!

I will give it a whirl!

 

[hwkranger]

i took a peek at ths and it's entirely wrong. will post in about 15 minutes the correction. sorry! :-(

I was only thinking of 1 return.

you don't need to use char(10) or char(13) just vbcrlf

will post an ammendment, sorry for the misleading posts. Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[hwkranger]

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click


Dim mystring As String
Dim newstring As String
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim mystringleng As Integer
Dim length As Integer
Dim mydb As Database
Dim myrecord As Recordset

Set mydb = CurrentDb()
Set myrecord = db.OpenRecordset("mytable/query&quot

If myrecord.BOF = True Then Exit Sub
With myrecord
If .RecordCount Then
.MoveFirst
Do Until myrecord.EOF
mystring = !myfield
mystringleng = Len(mystring)
j = InStr(1, mystring, vbCrLf, vbTextCompare)
k = j
newstring = Left(mystring, k - 1)
If j <> 0 Then
Do While j <> 0
k = j + 1
j = InStr(k, mystring, vbCrLf, vbTextCompare)
If j <> 0 Then
length = j - k - 1
newstring = newstring & &quot; &quot; & Mid(mystring, k + 1, length)
i = i + 1
Else
length = mystringleng - k
newstring = newstring & &quot; &quot; & Mid(mystring, k + 1, length)
End If
Loop
End If
.Edit
!myfield = newstring
.Update
Loop
End If
Loop

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

sorry about the first post, it was horrible.. i got to thinking and realized i put you in the wrong direction. this works 100% i just tested it. Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[unislick]

THANKS A BUNCH!!!!!!!!!!!!!