Key into form text box and update record immediately

G

Guest

Hi,

Probably bnot a good description but I'm finding it hard to put into words
what my requirements are.

I have a form with many labels and one free text field users can key into.
Rather than having a button to 'Update' after completing the comments I was
intending to update after every keystroke. I have been able to do this BUT
the cursor always positions itself at the left-hand side of the text, not at
the end of the last character entered. I have stepped through the code and
just before the Exit Sub the value of Starting_Point is equal to the length
of the text (eg 22) yet the cursor remains at position 1.



I have used the following code:

Private Sub Buying_Office_Comment_Change()
Starting_Point = Me![Buying Office Comment].SelStart
If From_Buying_Office_Comment_Change = True Then
From_Buying_Office_Comment_Change = False
Me![Buying Office Comment].SelStart = Starting_Point
Exit Sub
End If
My_Count = 0
Me![LaybyNo].SetFocus
The_LaybyNo = Me![LaybyNo].Text
Me![ItemCode].SetFocus
The_ItemCode = Me![ItemCode]
The_Concat = The_LaybyNo & The_ItemCode
Me![Buying Office Comment].SetFocus
The_Comment = Me![Buying Office Comment].Text
Call Check_If_There_Is_A_Previous_Comment
DoCmd.SetWarnings False
From_Buying_Office_Comment_Change = False
If The_Count = 0 Then 'No Previous Comment
DoCmd.RunSQL "INSERT INTO Tbl_Rainchecks_Comments(Concat, [Buying
Office Comments], [Buying Office Comments Date]) VALUES ('" & The_Concat &
"', '" & The_Comment & "', now);"
DoCmd.RunSQL "UPDATE Tbl_Rainchecks_Comments SET
Tbl_Rainchecks_Comments.[Buying Office Comments Date] = """ & Now & """ WHERE
((Tbl_Rainchecks_Comments.[Concat])=""" & Me![LaybyNo] & Me![ItemCode] &
""");"
Else
If My_Count = 1 Then Exit Sub
Me![Buying Office Comment].SetFocus
DoCmd.RunSQL "UPDATE Tbl_Rainchecks_Comments SET
Tbl_Rainchecks_Comments.[Buying Office Comments] = """ & The_Comment & """
WHERE ((Tbl_Rainchecks_Comments.[Concat])=""" & Me![LaybyNo] & Me![ItemCode]
& """);"
DoCmd.RunSQL "UPDATE Tbl_Rainchecks_Comments SET
Tbl_Rainchecks_Comments.[Buying Office Comments Date] = """ & Now & """ WHERE
((Tbl_Rainchecks_Comments.[Concat])=""" & Me![LaybyNo] & Me![ItemCode] &
""");"
My_Count = 1

From_Buying_Office_Comment_Change = True
Me![Buying Office Comment Date].SetFocus
Me![Buying Office Comment].SetFocus
Me![Buying Office Comment].Enabled = True
Me![Buying Office Comment].Text = The_Comment
End If
End Sub

Public Sub Check_If_There_Is_A_Previous_Comment()
Me![LaybyNo].SetFocus
The_LaybyNo = Me![LaybyNo].Text
Me![ItemCode].SetFocus
The_ItemCode = Me![ItemCode]
The_Concat = The_LaybyNo & The_ItemCode
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Tbl_Rainchecks_Comments.Concat,
Tbl_Rainchecks_Comments.[Buying Office Comments],
Tbl_Rainchecks_Comments.[Buying Office Comments Date] FROM
Tbl_Rainchecks_Comments WHERE (((Tbl_Rainchecks_Comments.Concat)='" &
The_Concat & "'));")

The_Count = 0
With rst
Do Until .EOF
The_Count = The_Count + 1
The_Concat = Val(!Concat)
The_Buying_Office_Comments = ![Buying Office Comments]
The_Buying_Office_Comments_Date = ![Buying Office Comments Date]
Me![Buying Office Comment] = The_Buying_Office_Comments
Me![Buying Office Comment Date] = The_Buying_Office_Comments_Date
.MoveNext
Loop

Found_Last:
.Close
End With
End Sub
 
M

Marshall Barton

Andrew said:
Probably bnot a good description but I'm finding it hard to put into words
what my requirements are.

I have a form with many labels and one free text field users can key into.
Rather than having a button to 'Update' after completing the comments I was
intending to update after every keystroke. I have been able to do this BUT
the cursor always positions itself at the left-hand side of the text, not at
the end of the last character entered. I have stepped through the code and
just before the Exit Sub the value of Starting_Point is equal to the length
of the text (eg 22) yet the cursor remains at position 1.
Click to expand...

I have no idea what you are trying to accomplish with all
that code and you didn't explain WHY you feel the need to go
through all that on every keystroke. I would expect the
AfterUpdate event would be the proper place to process the
value.

As for your code moving the cursor, it looks like it's
because you are setting the focus all over the place. There
is no need to do that because you should not be using the
..Text property of any control except the one you are
processing, which already has the focus.
--
Marsh
MVP [MS Access]

I have used the following code:

Private Sub Buying_Office_Comment_Change()
Starting_Point = Me![Buying Office Comment].SelStart
If From_Buying_Office_Comment_Change = True Then
From_Buying_Office_Comment_Change = False
Me![Buying Office Comment].SelStart = Starting_Point
Exit Sub
End If
My_Count = 0
Me![LaybyNo].SetFocus
The_LaybyNo = Me![LaybyNo].Text
Me![ItemCode].SetFocus
The_ItemCode = Me![ItemCode]
The_Concat = The_LaybyNo & The_ItemCode
Me![Buying Office Comment].SetFocus
The_Comment = Me![Buying Office Comment].Text
Call Check_If_There_Is_A_Previous_Comment
DoCmd.SetWarnings False
From_Buying_Office_Comment_Change = False
If The_Count = 0 Then 'No Previous Comment
DoCmd.RunSQL "INSERT INTO Tbl_Rainchecks_Comments(Concat, [Buying
Office Comments], [Buying Office Comments Date]) VALUES ('" & The_Concat &
"', '" & The_Comment & "', now);"
DoCmd.RunSQL "UPDATE Tbl_Rainchecks_Comments SET
Tbl_Rainchecks_Comments.[Buying Office Comments Date] = """ & Now & """ WHERE
((Tbl_Rainchecks_Comments.[Concat])=""" & Me![LaybyNo] & Me![ItemCode] &
""");"
Else
If My_Count = 1 Then Exit Sub
Me![Buying Office Comment].SetFocus
DoCmd.RunSQL "UPDATE Tbl_Rainchecks_Comments SET
Tbl_Rainchecks_Comments.[Buying Office Comments] = """ & The_Comment & """
WHERE ((Tbl_Rainchecks_Comments.[Concat])=""" & Me![LaybyNo] & Me![ItemCode]
& """);"
DoCmd.RunSQL "UPDATE Tbl_Rainchecks_Comments SET
Tbl_Rainchecks_Comments.[Buying Office Comments Date] = """ & Now & """ WHERE
((Tbl_Rainchecks_Comments.[Concat])=""" & Me![LaybyNo] & Me![ItemCode] &
""");"
My_Count = 1

From_Buying_Office_Comment_Change = True
Me![Buying Office Comment Date].SetFocus
Me![Buying Office Comment].SetFocus
Me![Buying Office Comment].Enabled = True
Me![Buying Office Comment].Text = The_Comment
End If
End Sub

Public Sub Check_If_There_Is_A_Previous_Comment()
Me![LaybyNo].SetFocus
The_LaybyNo = Me![LaybyNo].Text
Me![ItemCode].SetFocus
The_ItemCode = Me![ItemCode]
The_Concat = The_LaybyNo & The_ItemCode
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Tbl_Rainchecks_Comments.Concat,
Tbl_Rainchecks_Comments.[Buying Office Comments],
Tbl_Rainchecks_Comments.[Buying Office Comments Date] FROM
Tbl_Rainchecks_Comments WHERE (((Tbl_Rainchecks_Comments.Concat)='" &
The_Concat & "'));")

The_Count = 0
With rst
Do Until .EOF
The_Count = The_Count + 1
The_Concat = Val(!Concat)
The_Buying_Office_Comments = ![Buying Office Comments]
The_Buying_Office_Comments_Date = ![Buying Office Comments Date]
Me![Buying Office Comment] = The_Buying_Office_Comments
Me![Buying Office Comment Date] = The_Buying_Office_Comments_Date
.MoveNext
Loop

Found_Last:
.Close
End With
End Sub
Click to expand...
 
G

Guest

Hi Marsh,

Firstly thanks for your reply. I've tried many differenct things to do what
I consider to be pretty simple. I'm hoping you can assist me with the basics
and I'll go from there.

In simple terms I have a table (Tbl_Data). Each week data is refreshed in
this table is refreshed. Fields in this table are Code & Description.
Comments are in a table called Tbl_Comments. It contains fields Code,
Comment & Date.

In a form I'm showing Code, Description, Comment and Date (data source is a
query). I have done all of that and now I run into trouble.

I want to allow the user to key into the Comment field on the form and as
they do update the Tbl_Comments.Comments field. (i was kind of able to do
that before but the cursor kept going to the left side of the text meaning
the user had to use the mouse to go to the right).

If that is bad practise I would be happy to do it as an afterupdate event.

I have created a sample of the database and could email it if you are
willing. It is 164KB.

PLEASE HELP as I'm going mad!








--
Andrew


Marshall Barton said:
Andrew said:
Probably bnot a good description but I'm finding it hard to put into words
what my requirements are.

I have a form with many labels and one free text field users can key into.
Rather than having a button to 'Update' after completing the comments I was
intending to update after every keystroke. I have been able to do this BUT
the cursor always positions itself at the left-hand side of the text, not at
the end of the last character entered. I have stepped through the code and
just before the Exit Sub the value of Starting_Point is equal to the length
of the text (eg 22) yet the cursor remains at position 1.
Click to expand...

I have no idea what you are trying to accomplish with all
that code and you didn't explain WHY you feel the need to go
through all that on every keystroke. I would expect the
AfterUpdate event would be the proper place to process the
value.

As for your code moving the cursor, it looks like it's
because you are setting the focus all over the place. There
is no need to do that because you should not be using the
..Text property of any control except the one you are
processing, which already has the focus.
--
Marsh
MVP [MS Access]

I have used the following code:

Private Sub Buying_Office_Comment_Change()
Starting_Point = Me![Buying Office Comment].SelStart
If From_Buying_Office_Comment_Change = True Then
From_Buying_Office_Comment_Change = False
Me![Buying Office Comment].SelStart = Starting_Point
Exit Sub
End If
My_Count = 0
Me![LaybyNo].SetFocus
The_LaybyNo = Me![LaybyNo].Text
Me![ItemCode].SetFocus
The_ItemCode = Me![ItemCode]
The_Concat = The_LaybyNo & The_ItemCode
Me![Buying Office Comment].SetFocus
The_Comment = Me![Buying Office Comment].Text
Call Check_If_There_Is_A_Previous_Comment
DoCmd.SetWarnings False
From_Buying_Office_Comment_Change = False
If The_Count = 0 Then 'No Previous Comment
DoCmd.RunSQL "INSERT INTO Tbl_Rainchecks_Comments(Concat, [Buying
Office Comments], [Buying Office Comments Date]) VALUES ('" & The_Concat &
"', '" & The_Comment & "', now);"
DoCmd.RunSQL "UPDATE Tbl_Rainchecks_Comments SET
Tbl_Rainchecks_Comments.[Buying Office Comments Date] = """ & Now & """ WHERE
((Tbl_Rainchecks_Comments.[Concat])=""" & Me![LaybyNo] & Me![ItemCode] &
""");"
Else
If My_Count = 1 Then Exit Sub
Me![Buying Office Comment].SetFocus
DoCmd.RunSQL "UPDATE Tbl_Rainchecks_Comments SET
Tbl_Rainchecks_Comments.[Buying Office Comments] = """ & The_Comment & """
WHERE ((Tbl_Rainchecks_Comments.[Concat])=""" & Me![LaybyNo] & Me![ItemCode]
& """);"
DoCmd.RunSQL "UPDATE Tbl_Rainchecks_Comments SET
Tbl_Rainchecks_Comments.[Buying Office Comments Date] = """ & Now & """ WHERE
((Tbl_Rainchecks_Comments.[Concat])=""" & Me![LaybyNo] & Me![ItemCode] &
""");"
My_Count = 1

From_Buying_Office_Comment_Change = True
Me![Buying Office Comment Date].SetFocus
Me![Buying Office Comment].SetFocus
Me![Buying Office Comment].Enabled = True
Me![Buying Office Comment].Text = The_Comment
End If
End Sub

Public Sub Check_If_There_Is_A_Previous_Comment()
Me![LaybyNo].SetFocus
The_LaybyNo = Me![LaybyNo].Text
Me![ItemCode].SetFocus
The_ItemCode = Me![ItemCode]
The_Concat = The_LaybyNo & The_ItemCode
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT Tbl_Rainchecks_Comments.Concat,
Tbl_Rainchecks_Comments.[Buying Office Comments],
Tbl_Rainchecks_Comments.[Buying Office Comments Date] FROM
Tbl_Rainchecks_Comments WHERE (((Tbl_Rainchecks_Comments.Concat)='" &
The_Concat & "'));")

The_Count = 0
With rst
Do Until .EOF
The_Count = The_Count + 1
The_Concat = Val(!Concat)
The_Buying_Office_Comments = ![Buying Office Comments]
The_Buying_Office_Comments_Date = ![Buying Office Comments Date]
Me![Buying Office Comment] = The_Buying_Office_Comments
Me![Buying Office Comment Date] = The_Buying_Office_Comments_Date
.MoveNext
Loop

Found_Last:
.Close
End With
End Sub
Click to expand...
Click to expand...
 
M

Marshall Barton

Andrew said:
Firstly thanks for your reply. I've tried many differenct things to do what
I consider to be pretty simple. I'm hoping you can assist me with the basics
and I'll go from there.

In simple terms I have a table (Tbl_Data). Each week data is refreshed in
this table is refreshed. Fields in this table are Code & Description.
Comments are in a table called Tbl_Comments. It contains fields Code,
Comment & Date.

In a form I'm showing Code, Description, Comment and Date (data source is a
query). I have done all of that and now I run into trouble.

I want to allow the user to key into the Comment field on the form and as
they do update the Tbl_Comments.Comments field. (i was kind of able to do
that before but the cursor kept going to the left side of the text meaning
the user had to use the mouse to go to the right).
Click to expand...


It seems to me that you should bind your form to tblData.
Then add a subform bound to the comments table and set the
Link Master/Child properties to the Code field. No code
needed.
 
G

Guest

Hi Marshall,

I'm try your recomendation but I can't put a sub-form on a continuous form.
Any suggestions?
 
M

Marshall Barton

Andrew said:
I'm try your recomendation but I can't put a sub-form on a continuous form.
Click to expand...


It doesn't work in all situations, but try adding the
comments table's Primary key field to the record source
query. This is necessary because Access can't figure out
which comments table record to update without the PK to
identify the record.
 
G

Guest

Hi Marshal,

It doesn't seem to work (defining Primary keys) on the data sheet because it
is possible to have duplicates in some of the fields (including the code
field).

I deleted all of my dupplicate records for the purpose of trying your
suggestion but when I do try to change to continuous I am prompted that I
can't.

I don't think I'm asking Access to do anything complicated. Amy I going
about it the wrong way? I can change my design if required.
 
M

Marshall Barton

Andrew said:
It doesn't seem to work (defining Primary keys) on the data sheet because it
is possible to have duplicates in some of the fields (including the code
field).

I deleted all of my dupplicate records for the purpose of trying your
suggestion but when I do try to change to continuous I am prompted that I
can't.

I don't think I'm asking Access to do anything complicated. Amy I going
about it the wrong way? I can change my design if required.
Click to expand...


A primary key field can not be a duplicate. Are you sure
you included it in the form's record source query?

Well, you are asking how to edit two tables in one query.
This is always an iffy affair, so I hate to put many eggs in
that basket.

My usual approach is to use the subform I mentioned earlier,
but, since the form is continuous, put it in the form's
header or footer section, It won't show the comment for
every record, but the comment for the current record will
follow the Code field.
 
Top