Access 2000: Unbound Text Box saved in Same Table

[john]

I have to find a way to create real-time Confirmation # from information that
is optained from a form. I created a Unbound Text box which does what I
want; Code, = left([Stat lab],2)& right(Year(Date()) which gives me a value
of XX09 from the currently entered information.

Now that I have this I need to have the text created (XX09) to be saved with
the current form. How do I do this??

Like I mentioned this is an Unbound Test Box which I would like to link or
save within the same table which the form was created from. Do I need to
make it a Bound Test Box?? How do I do that?? Everytime I do that it asks
me which forum or query do I want to use.

I tried to use a query when creating the form but the coding I put into the
query keeps stating I have an error with the code; mainly the Left function.

Thanks
John

 

[Ken Snell [MVP]]

You can bind the textbox to the desired field. You can then use VBA code to
calculate and write the "Confirmation #" value into that textbox when you
create a new record. This can be done with the form's Current event, when
you have a new record:

Private Sub Form_Current()
If Me.NewRecord = True Then _
Me.NameOfTextBox.Value = left([Stat lab],2)& right(Year(Date())
End If

 

[john]

Sorry I am new to access so let me see if I got this right.

First --> How do I bind it to the field I want??

Then I go into the builder of the created Text box and write the code.

This will allow me to have a real-time confirmation # for the informaton
that was just entered into the fields of [Stat Lab] and the current year.

You can bind the textbox to the desired field. You can then use VBA code to
calculate and write the "Confirmation #" value into that textbox when you
create a new record. This can be done with the form's Current event, when
you have a new record:

Private Sub Form_Current()
If Me.NewRecord = True Then _
Me.NameOfTextBox.Value = left([Stat lab],2)& right(Year(Date())
End If
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I have to find a way to create real-time Confirmation # from information
that
is optained from a form. I created a Unbound Text box which does what I
want; Code, = left([Stat lab],2)& right(Year(Date()) which gives me a
value
of XX09 from the currently entered information.

Now that I have this I need to have the text created (XX09) to be saved
with
the current form. How do I do this??

Like I mentioned this is an Unbound Test Box which I would like to link or
save within the same table which the form was created from. Do I need to
make it a Bound Test Box?? How do I do that?? Everytime I do that it
asks
me which forum or query do I want to use.

I tried to use a query when creating the form but the coding I put into
the
query keeps stating I have an error with the code; mainly the Left
function.

Thanks
John

 

[Marshall Barton]

I have to find a way to create real-time Confirmation # from information that
is optained from a form. I created a Unbound Text box which does what I
want; Code, = left([Stat lab],2)& right(Year(Date()) which gives me a value
of XX09 from the currently entered information.

Now that I have this I need to have the text created (XX09) to be saved with
the current form. How do I do this??

Like I mentioned this is an Unbound Test Box which I would like to link or
save within the same table which the form was created from. Do I need to
make it a Bound Test Box?? How do I do that?? Everytime I do that it asks
me which forum or query do I want to use.

You can't make it a bound text box because it's an
expression. To save the value you would need to use VBA
code, probably in the form's BeforeUpdate event procedure
(unless it has already been set).

BUT, unfortunately(?), if you use code to refer to a
calculated text box, then you must use code to calculate the
value. This is because Access evaluates control expressions
at a different priority level than VBA code and displaying
the form's controls. That is rather confusing, but it boils
down to your code to save the value might run before the
text box expression has been calculated.

So, after all that, you do need to make it a bound text box
and use code to do the calculation. It looks like you want
the calculation to be done as soon as the user enters a
value into the [Stat lab] text box or ??? If so, then you
could use the [Stat lab] text box's AfterUpdate event to
calculate the code and set the value of the bound text box.

So much for what you said you wanted to do. Now to the
wisdom of your goal. It is a really BAD idea to save
multiple values in a single field so at least you should
save the two parts in two fields. OTOH, you should not save
a value that can be calculated from other values in a
record. Since the first two characters are available in the
[Stat lab] field, you do not need to save those in a
separate field and if you also have a field for the
date/time the record was created (usually a good idea), then
you do not need to save the last two digits of the year.
You can just use a query with a calculated field or a
form/report text box expression (much like the one you
posted) to display the four characters any place you want to
see it.

 

[john]

Got it working.. Thanks for your help

John

You can bind the textbox to the desired field. You can then use VBA code to
calculate and write the "Confirmation #" value into that textbox when you
create a new record. This can be done with the form's Current event, when
you have a new record:

Private Sub Form_Current()
If Me.NewRecord = True Then _
Me.NameOfTextBox.Value = left([Stat lab],2)& right(Year(Date())
End If
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I have to find a way to create real-time Confirmation # from information
that
is optained from a form. I created a Unbound Text box which does what I
want; Code, = left([Stat lab],2)& right(Year(Date()) which gives me a
value
of XX09 from the currently entered information.

Now that I have this I need to have the text created (XX09) to be saved
with
the current form. How do I do this??

Like I mentioned this is an Unbound Test Box which I would like to link or
save within the same table which the form was created from. Do I need to
make it a Bound Test Box?? How do I do that?? Everytime I do that it
asks
me which forum or query do I want to use.

I tried to use a query when creating the form but the coding I put into
the
query keeps stating I have an error with the code; mainly the Left
function.

Thanks
John

 

[Ken Snell [MVP]]

Good job! You're welcome.