Updating Text Box?

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

Guest

I have two text boxes in a form: "quoteendleadtime" and
"quotestartleadtime". In the event AfterUpdate for "quotestartleadtime" I am
automatically incrementing "quoteendleadtime" by 1. Here's the formula for
the event:

Me.quoteendleadtime = Me.quotestartleadtime + 1

The problem is that the form field "quoteendleadtime" is showing the correct
number, but when I try to append the form data to a table via a query, the
field is showing zero. Only when I MANUALLY update either field does it
append correctly.

I tried to requery the text box with no success. Any suggestions?

Thanks in advance. Gary
 
First of all, let's straighten out the terminology. A form has controls on
it, not fields; fields are in a table, and are in a recordset or query. The
form's RecordSource (and its Recordset) has fields; controls on the form may
be bound to fields in the form's Recordset, and if they are, that is how you
can use a textbox to enter or edit data in a field.

Now, for what you're doing.... is quoteendleadtime bound to a field? if yes,
what is the name of the field?

How are you creating/running the query? If you're running a query that is
based on the table that has the field to which quoteendleadtime is bound,
and you've not yet saved the record in the form before you run the query,
then the query won't see the new data yet. Or, if you're running the query
in the quotestartleadtime textbox's AfterUpdate event, the data update and
the query run may overlap.

Please give us more details about what you're doing, including posting code
and the query's SQL statement.
 
My bad ... I'll be more specific (correct) with terminology in future posts
to reduce the chance for confusion. My response:

The control quoteendleadtime is NOT bound to a field. Here's the SQL code
for the query:

INSERT INTO tblSalesJobMastQuotes ( quotestartleadtime, quoteendleadtime )
SELECT Forms!frmSalesJob!quotestartleadtime AS setstartlead,
Forms!frmSalesJob!quoteendleadtime AS setendlead;

As I mention below, when I MANUALLY type the data into these controls, the
append query works fine ... when I run this VB code (Me.quoteendleadtime =
Me.quotestartleadtime + 1) for the AfterUpdate Event on quotestartleadtime,
the form shows the correct number, but the append query shows zero for
quoteendleadtime.

Thanks, Ken.

Gary
 
You have not told us when you run the append query? Is it run in the
AfterUpdate event procedure? Or at some other point? How are you running the
query?
 
I run the append query at some point after the AFTERUPDATE event (this is a
data entry form so other controls are populated after these two controls).
Before I close the form, I run the append query.
 
What type of data field is quoteendleadtime in the table
tblSalesJobMastQuotes? Date/time? Text?

It's possible that the query isn't recognizing that the quoteendleadtime
textbox is a date/time value. Are you building and running the query in
code? Or is it a saved query?

If it's a saved query, open the query in design view, then click on Query |
Parameters (in menu bar), and enter this into the two columns that you'll
see there:

Under Parameter:
Forms!frmSalesJob!quoteendleadtime

Under Data Type:
Date/Time

If you're building the query in VBA code and then running it, when you
concatenate the value of the quoteendleadtime control into the query,
delimit that value with # characters in the string.
--

Ken Snell
<MS ACCESS MVP>
 
More information ... as a test, I've taken the append query and made it a
SELECT query, open the form enter "1" into the control quotestartleadtime,
the control quoteendleadtime is updated to "2" and I view the Select query
and 1 appers (quotestartleadtime), but 2 (quoteendleadtime) does not.

As I mentioned earlier, when I manually enter the number(s) into the
control(s), they appear/append correctly. I've also deleted/re-created the
text boxes with no success.

Hope this helps.

Gary
 
By any chance, does the frmSalesJob form's RecordSource have a field named
quoteendleadtime in it? If yes, then the form is writing that value to that
field, and not to the control that is named the same as this field.

If this isn't the answer, then post back.
 
If the field itself does update the table, and the query does not than :
or 1) there is an error in your query
or 2) the value isn't saved yet

The 2nd you could resolve by adding the line :

docmd.runcommand AcCmdSaveRecord

in your code before starting the query.
If that doesn't work, something is wrong with your query, and you might
post the code you use then...
 
No, there's no recordsource for the form ... just controls that I used to
append/update tables. Why would the control show the correct value, but not
appear when I run a select query for this control (unless I manually type it)?
 
I don't believe it's a problem with the query, but with the form. When I
MANUALLY enter data into the control, the query works fine. When I run VB
code to update the control, the control APPEARS to update correctly, but the
query doesn't show the updated control data.
 
This is a puzzle. If you can prepare a sample of this database, zip it up
and email it to me (my email address can be obtained by removing this is not
real from my reply address), along with clear instructions on how to
reproduce the error, and I'll take a look at it.
 
Back
Top