How to "Commit" data to Text Box Programatically?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with a save button. The user fills out several fields in text
boxes, then clicks "save". But an odd thing happens - if I fill out the last
text box, then click "save" - a new record is saved but there is nothing in
the field "fed" by that last text box.

You can see the data in the text box - but it doesn't get written to the
table.

HOWEVER, if I hit my "Enter" button THEN click Save - the data is saved.
Somehow my vba code isn't "seeing" the data in the text box unless I hit
"enter" first.

Users aren't going to do that - they'll enter the data then click "Save".

How can I get vba to "see" the data without having the user hit "Enter"?

TIA - John D
 
Oh - the code!

_________________________________________
Private Sub btnSaveNewLocType_Click()

....

Dim strSQLInsert As String

strSQLInsert = "INSERT INTO TYbf_LocType(LocActivityType, LocType) " & _
"SELECT [Forms]![FYbf_Locations_NewType_3].[ACTIVITY_CODE], " & _
"[Forms]![FYbf_Locations_NewType_3].[txtNewType]"

DoCmd.RunSQL strSQLInsert

....

End Sub
__________________
The problematic Text Box is:

"[Forms]![FYbf_Locations_NewType_3].[txtNewType]"

Data is there - but vba doesn't see it unless I hit Enter.

Thanks - John D
 
John said:
Private Sub btnSaveNewLocType_Click()
...
Dim strSQLInsert As String

strSQLInsert = "INSERT INTO TYbf_LocType(LocActivityType, LocType) " & _
"SELECT [Forms]![FYbf_Locations_NewType_3].[ACTIVITY_CODE], " & _
"[Forms]![FYbf_Locations_NewType_3].[txtNewType]"

DoCmd.RunSQL strSQLInsert
...
End Sub
__________________
The problematic Text Box is:
"[Forms]![FYbf_Locations_NewType_3].[txtNewType]"

Data is there - but vba doesn't see it unless I hit Enter.
Click to expand...


Seems like that should work. An alternative that might be
better:

strSQLInsert = "INSERT INTO TYbf_LocType " & _
(LocActivityType, LocType) " & _
"SELECT " & Me.ACTIVITY_CODE & "," & _
Me.txtNewType
 
Marsh - Stranger all the time.

I changed the references to the Form to "Me." as you suggested:
___________________
strSQLInsert = "INSERT INTO TYbf_LocType (LocActivityType, LocType) " & _
"SELECT Me!ACTIVITY_CODE, Me!txtNewType"
_______________________

When I click the Save button, system asks me for the Parameter Value
"Me!ACTIVITY_CODE" !!! As if it doesn't "see" that control on the form "Me!".
When I watch the Locals Window stepping through the procedure the SQL string
gets written "correctly" - but won't execute without asking the Parameter
questions.

And - as I say - if I switch back to the full name reference (ie -
[Forms]![FYbf_Locations_NewType_3].[ACTIVITY_CODE]) the SQL executes -
elicits the "sure you want to append ...", saves a new record with a value
for (LocActivityType0 from [ACTIVITY_CODE], but with nothing in (LocType)
even though data is visible in [txtNewType].

Can you think of any property settings I may have for the form - or
something - that would cause this behavior?

Thanks - John D
 
John said:
Marsh - Stranger all the time.

I changed the references to the Form to "Me." as you suggested:
___________________
strSQLInsert = "INSERT INTO TYbf_LocType (LocActivityType, LocType) " & _
"SELECT Me!ACTIVITY_CODE, Me!txtNewType"
_______________________

When I click the Save button, system asks me for the Parameter Value
"Me!ACTIVITY_CODE" !!! As if it doesn't "see" that control on the form "Me!".
When I watch the Locals Window stepping through the procedure the SQL string
gets written "correctly" - but won't execute without asking the Parameter
questions.

And - as I say - if I switch back to the full name reference (ie -
[Forms]![FYbf_Locations_NewType_3].[ACTIVITY_CODE]) the SQL executes -
elicits the "sure you want to append ...", saves a new record with a value
for (LocActivityType0 from [ACTIVITY_CODE], but with nothing in (LocType)
even though data is visible in [txtNewType].

Can you think of any property settings I may have for the form - or
something - that would cause this behavior?
Click to expand...


No, I can't think of any properties that can have that
effect. If the value is actually in the text box and not
just displayed as its default value, I can't see how you
would have the problem.

You did not follow my alternative code correctly. You can
not use Me inside the SQL statement. You must concatenate
the **values** into the string so the result is the SQL
statment. The query that actually execites would then look
something like:

INSERT INTO TYbf_LocType (LocActivityType, LocType)
SELECT 123, 456

According to Help, that is not the recommended syntax. I
don't think it matters, but it's supposed to be

INSERT INTO TYbf_LocType (LocActivityType, LocType)
VALUES (123, 456)

Or back to your VBA:

strSQLInsert = "INSERT INTO TYbf_LocType " & _
(LocActivityType, LocType) " & _
"VALUES (" & Me.ACTIVITY_CODE & "," & _
Me.txtNewType & ")"
MsgBox strSQLInsert

The MsgBox is just so you can see the actual query that will
be executed, remove it once it's working.
 
This time I entered the code exactly - and when I click "Save" without
hitting the Enter key in [txtNewType] the MsgBox shows a value in the first
field but is still blank in the second.

Sorry the below is kind of long - don't know how else to explain it.

Just had a thought. This whole process is for a user to add a new type of
facility to the system. The flow is a little complex - let me explain:

The db manages data about nonprofit organizations and Special Districts. One
aspect is facilities.

Facilities are first categorized by major organizational function - such as
Fire Prevention, Water Utility, Harbor, etc. There is also a General category
for types of facilities that many organizations have - such as Offices,
Warehouses, etc.

Then within each major function are specific types of facilities - Fire
Prevention has Fire Stations, Water Utilities have Water Treatment Plants,
Wells, Pumping Stations, etc.

When a user needs to add a new definition of Location Type, my first form
asks if the Location is a "General" facility (Offices, Warehouses, etc.) or
Specific (Fire Prevention, etc.).

If they select General they go directly to the 3rd form. If they select
Specific they go to a 2nd form on which they chose one of about 40 types of
organizational functions (Fire Prevention, Water, etc.). Then they go to the
3rd form (the one at issue).

The 3rd form shows the user the Major Function they chose (General or one of
the Specific) and a list of types of facilities that have already been
entered for that Major Function. It asks a question in the form of an Option
Group "Is the Facility Type You Want To Add Listed Above?" (2 buttons - Yes &
No). If they click Yes they are told since it already exists they don't need
to add it again.

IF THEY CLICK NO - and I wonder if the problem is in here somewhere - I have
code that converts both the control [txtNewType] AND the "Save New Facility
Type" button from invisible to visible. Until they get to that point those
controls are invisible.

They are then supposed to enter the New Facility Type into [txtNewType] then
click the Save Button. Here's where the problem occurs.

NOW - while the form is in Form View and after I've clicked "No" in the
Option Group, I right click [txtNewType] and open Properties and it shows
"Visible = Yes". If I change to Design View, the Property Sheet shows
"Visible = No" (which is the original setting for the field - to be changed
if the user clicks "No" in the Option Group). When I click back to Form View
the form reverts to how it looks when first opened - showing the Major
Category of facilities and any specific types that have already been listed -
BUT the Option Group choice is not made and therefore [txtNewType] and the
Save button are not visible; I have to clicik "No" again to make the 2
controls visible again.

It still seems to me vba somehow can't see data in the control until I hit
Enter. Could this have to do with the "underlying - original" invisible
property?

BUT - again, if I hit my Enter key after typing data into [txtNewType] then
click the Save Button, the MsgBox indeed HAS DATA for the second field in the
table.

This stuff can get complicated, can't it?

Thanks for your help - John D
 
John said:
This time I entered the code exactly - and when I click "Save" without
hitting the Enter key in [txtNewType] the MsgBox shows a value in the first
field but is still blank in the second.

Sorry the below is kind of long - don't know how else to explain it.

Just had a thought. This whole process is for a user to add a new type of
facility to the system. The flow is a little complex - let me explain:

The db manages data about nonprofit organizations and Special Districts. One
aspect is facilities.

Facilities are first categorized by major organizational function - such as
Fire Prevention, Water Utility, Harbor, etc. There is also a General category
for types of facilities that many organizations have - such as Offices,
Warehouses, etc.

Then within each major function are specific types of facilities - Fire
Prevention has Fire Stations, Water Utilities have Water Treatment Plants,
Wells, Pumping Stations, etc.

When a user needs to add a new definition of Location Type, my first form
asks if the Location is a "General" facility (Offices, Warehouses, etc.) or
Specific (Fire Prevention, etc.).

If they select General they go directly to the 3rd form. If they select
Specific they go to a 2nd form on which they chose one of about 40 types of
organizational functions (Fire Prevention, Water, etc.). Then they go to the
3rd form (the one at issue).

The 3rd form shows the user the Major Function they chose (General or one of
the Specific) and a list of types of facilities that have already been
entered for that Major Function. It asks a question in the form of an Option
Group "Is the Facility Type You Want To Add Listed Above?" (2 buttons - Yes &
No). If they click Yes they are told since it already exists they don't need
to add it again.

IF THEY CLICK NO - and I wonder if the problem is in here somewhere - I have
code that converts both the control [txtNewType] AND the "Save New Facility
Type" button from invisible to visible. Until they get to that point those
controls are invisible.

They are then supposed to enter the New Facility Type into [txtNewType] then
click the Save Button. Here's where the problem occurs.

NOW - while the form is in Form View and after I've clicked "No" in the
Option Group, I right click [txtNewType] and open Properties and it shows
"Visible = Yes". If I change to Design View, the Property Sheet shows
"Visible = No" (which is the original setting for the field - to be changed
if the user clicks "No" in the Option Group). When I click back to Form View
the form reverts to how it looks when first opened - showing the Major
Category of facilities and any specific types that have already been listed -
BUT the Option Group choice is not made and therefore [txtNewType] and the
Save button are not visible; I have to clicik "No" again to make the 2
controls visible again.

It still seems to me vba somehow can't see data in the control until I hit
Enter. Could this have to do with the "underlying - original" invisible
property?

BUT - again, if I hit my Enter key after typing data into [txtNewType] then
click the Save Button, the MsgBox indeed HAS DATA for the second field in the
table.

This stuff can get complicated, can't it?
Click to expand...


Sorry, but I don't see anything terrribly unusual there.

The only way I can see where hitting the Enter key and
clicking a button would make a difference is if your button
is NOT really a button. You would have this problem if you
are faking a button with a label control, because labels can
not receive the focus and the text box would not know you
have finished entering the new value.
 
DARN - got that "website is too busy ..." message again and lost my posting.
Herein recreated:


: - \

LOL - you're right. I am using a label to "fake" a command button. The
"normal" command button's colors clash with the palette I'm using - the
"corporate colors" of a statewide association. (Seems silly to me that Access
doesn't allow you to control the style of command buttons to some extent.)

I didn't realize - or remember - that freestanding labels can't receive the
focus - much less what the impact would be on vba's ability to "see" data in
the previous control.

I added a line before the strSQLInsert = ...

Me.ACTIVITY_CODE.SetFocus

And - voila - it works.

Do you see any problem with doing this?

Thanks so much for your help.

John D
 
John said:
LOL - you're right. I am using a label to "fake" a command button. The
"normal" command button's colors clash with the palette I'm using - the
"corporate colors" of a statewide association. (Seems silly to me that Access
doesn't allow you to control the style of command buttons to some extent.)

I didn't realize - or remember - that freestanding labels can't receive the
focus - much less what the impact would be on vba's ability to "see" data in
the previous control.

I added a line before the strSQLInsert = ...

Me.ACTIVITY_CODE.SetFocus

And - voila - it works.

Do you see any problem with doing this?
Click to expand...


Amy thing that moves the focus away from the txtNewType text
box will indicate the data entry has completed and the text
box can finish up. I don't know what moving the focus to
the ACTIVITY_CODE text box wiil do to your tab order,
probably unimportant in this case. Personally, I would use
a locked text box instead of a label because it can receive
the focus, but that's your call.
 
You know, I came to the same conclusion while I was out cutting grass before
it starts to rain.

Thanks for your help Marsh.

John D
 
Back
Top