How do I reference a new field in an existing form?

[Tom Gettys]

I built a form, and now wish to use a field that wasn't called out when it
was designed. RIght now, when I click on the Field List button all that is
shown are the fields I selected at design time.

I created a combo box using the wizard, and it is shown as unbound. I would
like to bind it to a field in the underlying table that does not show up in
the Field List.

 

[tina]

sounds like your form is based on a query, or a SQL statement in the
RecordSource property. if the former, open the query in Design view and add
the new field to the grid. if the latter, open the form in Design view,
click on the RecordSource property "line" in the Properties box, click the
Build (...) button at the right side, and add the new field to the grid.
then close the view, saving the changes at the prompt, and save the form.

hth

 

[Tom Gettys]

Is that too cool or what - thanks a bunch Tina! I had no idea about the
RecordSource propery - VERY helpful info.

 

[tina]

you're very welcome, Tom.
sounds like you're a new Access user (newbie is the common term here, used
with affection and respect always, since all of us - even the MVPs - started
as newbies). you might find it helpful to take a look at my website, which
is geared specifically to newbies, and includes links to some key sites in
the huge world of online Access resources.
http://home.att.net/~california.db/tips.html

hth

 

[Tom Gettys]

Thanks again Tina!

I have question related to my original one. You told me how to make
additional fields available for use on my form. I now wish to present in a
text box a string derived from other fields on the form.

I made a query that constructs to string I want, but I don't know if I
should somehow use that query with the new text box, or recreate the string
within the properties of the text box itself (I hope I have asked my question
correctly)?

 

[tina]

if the fields that you need in the string, are already in the Fields list in
form Design view, then you may be able to construct the string directly in
the form without using a query. i can't say for sure, because i don't know
what the query is doing, but you can try it out. if you do need to use the
query, then take a look at the DLookup() Function topic in Access Help; you
can use the function to pull the string value (a concatenated field, i
assume) out of the query. post back if you get stumped.

hth

 

[Tom Gettys]

Um, I guess I am unclear what you mean Tina; sorry. You said that I may be
able to construct the string directly, but not how or where!

Yes, the needed fields are available; basically I wish to simply concatenate
the the lastname initial with the recordID and display that value in the
textbox. Which property of the textbox does the function go in?

 

[tina]

well, if that's all you're doing, then a query shouldn't be necessary. just
add an unbound textbox control to the form and put the concatenated string
in the control's ControlSource property, preceded by the equal (=) sign. an
example would be

=LastNameInitial & recordID

hth

 

[Tom Gettys]

Sigh, yes that works perfectly. Thanks again Tina. I figured it out right
after I left that question - it was the equal sign that was my problem before.

I have a textbox that I enter the number of items into, but that number
could be calcuated from the items in a subform that is on my form. I guess
it is very much like an order form on which is a subform containing a list of
the items in the order, and how many of each are in the order.

So, I wish to somehow sum all the quantity fields in the subform and display
that number on the main form. How is such a thing accomplished?

 

[tina]

you can do that by adding an unbound textbox control to the subform's Footer
section and setting its' Visible property to False (this will work even if
your subform is Datasheet view). set the ControlSource of the control to

=Sum([QuantityFieldName])

okay, i'll call the textbox control in the subform footer "txtTotal". and
i'll call the subform "container" control name "ChildDetails". now, in the
*main* form, add an unbound textbox control and put the following reference
in the control's ControlSource property, as

=[ChildDetails].[Form]![txtTotal]

note: make sure you're using the name of the subform "container" control
within the main form, not the name of the subform form object as it appears
in the database window. the two names may be the same, or they may be
different.

hth

 

[Tom Gettys]

Thanks Tina, I will try this.

I am sorry to keep bugging you, but it is like mastercard to connect with
someone that knows what they are talking about (priceless)!

Certainly, the first order of business is getting the thing working, but I
would like to understand also. Why must the summation function be invoked in
the footer section?

you can do that by adding an unbound textbox control to the subform's Footer
section and setting its' Visible property to False (this will work even if
your subform is Datasheet view). set the ControlSource of the control to

=Sum([QuantityFieldName])

okay, i'll call the textbox control in the subform footer "txtTotal". and
i'll call the subform "container" control name "ChildDetails". now, in the
*main* form, add an unbound textbox control and put the following reference
in the control's ControlSource property, as

=[ChildDetails].[Form]![txtTotal]

note: make sure you're using the name of the subform "container" control
within the main form, not the name of the subform form object as it appears
in the database window. the two names may be the same, or they may be
different.

hth

Sigh, yes that works perfectly. Thanks again Tina. I figured it out right
after I left that question - it was the equal sign that was my problem before.

I have a textbox that I enter the number of items into, but that number
could be calcuated from the items in a subform that is on my form. I guess
it is very much like an order form on which is a subform containing a list of
the items in the order, and how many of each are in the order.

So, I wish to somehow sum all the quantity fields in the subform and display
that number on the main form. How is such a thing accomplished?

 

[Tom Gettys]

Oops! I guess I misspoke slightly. Once I worked my way through what you
said and got it working, I realized that it was displaying the total count,
but not stuffing that value into the associated field in the main table.

Let's see - before, I was typing the sum into a textbox, and its control
source recieved the value. Now, the textbox has its control source
referencing the computed sum from the subform, so I still need to somehow
cause that value to be placed in the table.

you can do that by adding an unbound textbox control to the subform's Footer
section and setting its' Visible property to False (this will work even if
your subform is Datasheet view). set the ControlSource of the control to

=Sum([QuantityFieldName])

okay, i'll call the textbox control in the subform footer "txtTotal". and
i'll call the subform "container" control name "ChildDetails". now, in the
*main* form, add an unbound textbox control and put the following reference
in the control's ControlSource property, as

=[ChildDetails].[Form]![txtTotal]

note: make sure you're using the name of the subform "container" control
within the main form, not the name of the subform form object as it appears
in the database window. the two names may be the same, or they may be
different.

hth

Sigh, yes that works perfectly. Thanks again Tina. I figured it out right
after I left that question - it was the equal sign that was my problem before.

I have a textbox that I enter the number of items into, but that number
could be calcuated from the items in a subform that is on my form. I guess
it is very much like an order form on which is a subform containing a list of
the items in the order, and how many of each are in the order.

So, I wish to somehow sum all the quantity fields in the subform and display
that number on the main form. How is such a thing accomplished?

 

[tina]

a field in the form's underlying RecordSource, exists in each record in the
dataset returned by the recordsource. the Detail section of a form (or a
report) can only handle the data from one record at a time - that's why a
form has a Current property, because only one record is "current" at any
given time. in order to sum (or count) a field in *all* the records in the
dataset, you must move "outside" the scope of the current record - in other
words, out of the Detail section.

hth

Thanks Tina, I will try this.

I am sorry to keep bugging you, but it is like mastercard to connect with
someone that knows what they are talking about (priceless)!

Certainly, the first order of business is getting the thing working, but I
would like to understand also. Why must the summation function be invoked in
the footer section?

you can do that by adding an unbound textbox control to the subform's Footer
section and setting its' Visible property to False (this will work even if
your subform is Datasheet view). set the ControlSource of the control to

=Sum([QuantityFieldName])

okay, i'll call the textbox control in the subform footer "txtTotal". and
i'll call the subform "container" control name "ChildDetails". now, in the
*main* form, add an unbound textbox control and put the following reference
in the control's ControlSource property, as

=[ChildDetails].[Form]![txtTotal]

note: make sure you're using the name of the subform "container" control
within the main form, not the name of the subform form object as it appears
in the database window. the two names may be the same, or they may be
different.

hth

Sigh, yes that works perfectly. Thanks again Tina. I figured it out right
after I left that question - it was the equal sign that was my problem before.

I have a textbox that I enter the number of items into, but that number
could be calcuated from the items in a subform that is on my form. I guess
it is very much like an order form on which is a subform containing a

list
of

the items in the order, and how many of each are in the order.

So, I wish to somehow sum all the quantity fields in the subform and display
that number on the main form. How is such a thing accomplished?

:

well, if that's all you're doing, then a query shouldn't be

necessary.
just

add an unbound textbox control to the form and put the concatenated string
in the control's ControlSource property, preceded by the equal (=)

sign.
an

example would be

=LastNameInitial & recordID

hth

 

[tina]

okay, i didn't understand that you want to store, rather than just display,
the calculated value. i really recommend against doing that. storing
calculated values in table fields is a violation of normalization rules, and
generally a *bad* idea. if a value in the base data changes, then the stored
calculated value is no longer correct, and there's no way detect that error
automatically. it's also a waste of storage space. as long as the base data
is stored, you can calculate totals on the fly whenever needed, and be
assured that the calculated value is current and accurate.

hth

Oops! I guess I misspoke slightly. Once I worked my way through what you
said and got it working, I realized that it was displaying the total count,
but not stuffing that value into the associated field in the main table.

Let's see - before, I was typing the sum into a textbox, and its control
source recieved the value. Now, the textbox has its control source
referencing the computed sum from the subform, so I still need to somehow
cause that value to be placed in the table.

you can do that by adding an unbound textbox control to the subform's Footer
section and setting its' Visible property to False (this will work even if
your subform is Datasheet view). set the ControlSource of the control to

=Sum([QuantityFieldName])

okay, i'll call the textbox control in the subform footer "txtTotal". and
i'll call the subform "container" control name "ChildDetails". now, in the
*main* form, add an unbound textbox control and put the following reference
in the control's ControlSource property, as

=[ChildDetails].[Form]![txtTotal]

note: make sure you're using the name of the subform "container" control
within the main form, not the name of the subform form object as it appears
in the database window. the two names may be the same, or they may be
different.

hth

Sigh, yes that works perfectly. Thanks again Tina. I figured it out right
after I left that question - it was the equal sign that was my problem before.

I have a textbox that I enter the number of items into, but that number
could be calcuated from the items in a subform that is on my form. I guess
it is very much like an order form on which is a subform containing a

list
of

the items in the order, and how many of each are in the order.

So, I wish to somehow sum all the quantity fields in the subform and display
that number on the main form. How is such a thing accomplished?

:

well, if that's all you're doing, then a query shouldn't be

necessary.
just

add an unbound textbox control to the form and put the concatenated string
in the control's ControlSource property, preceded by the equal (=)

sign.
an

example would be

=LastNameInitial & recordID

hth