One last question

J

Jim

One last question, what is the syntax of the control source for a Company ID
that is stored in my combo box, yet the text box is on my main form? I lied,
I have another question, my main form is based on a select query that I want
to add records to; how do I get a blank record to show up instead of the
first record in the table?

Thanks again
 
J

John Vinson

One last question, what is the syntax of the control source for a Company ID
that is stored in my combo box, yet the text box is on my main form?
Click to expand...

You lost me. An ID is not "stored in" a combo box; a combo box is not
a data storage medium, it's a tool which allows you to select data
from one table, display up to ten fields (typically just one though)
and store that field or a different field into another table.
Similarly, a textbox is not a data storage medium, it's another
display tool which displays the value of a table field.

Your data resides IN YOUR TABLES. The Form is just a window!

A Combo Box has several interrelated properties:

- The Row Source is a table, or (more often) a Query selecting one or
more fields from a table. For instance you might have a Query selcting
the CompanyID and the CompanyName.
- The Bound Column selects one of these fields to become the combo
box's value. In this example, you can use 1 as the Bound Column and
the value of the combo box will be the selected CompanyID.
- The Control Source is the name of the field (in the form's
recordsource) into which that CompanyID will be stored. Typically this
will be the CompanyID foreign key field in some table other than the
Company table.
- The ColumnWidths property is a series of numbers separated by
semicolons, indicating the width (in inches or centimeters) of each
field's display. A zero width means "keep this value around but don't
show it to the user" - so you could use a ColumnWidths of

0;1.25

to show the user the company name in a 1.25 inch wide box. The
computer knows about the ID but since it's zero width the user doesn't
see it (and doesn't need to see it!)
I lied,
I have another question, my main form is based on a select query that I want
to add records to; how do I get a blank record to show up instead of the
first record in the table?
Click to expand...

Two ways:

- Set the Form's DataEntry property to True. This will let you add
records but not view existing records.
- Or, view the Form's properties; on the Events tab click the ... icon
by the "Open" event. Choose the Code Builder. Edit the result to
something like

Private Sub Form_Open(Cancel as Integer) << Access gives you this
DoCmd.GoToRecord acNewRecord
End Sub << and this


John W. Vinson[MVP]
 
J

Jim

Thanks for the reply. I have a sub form that contains the business
information i.e. name, address etc. How would I go about taking one of these
fields and populate a text box on the main form; the parent form of which the
sub form is a part of?
 
J

John Vinson

Thanks for the reply. I have a sub form that contains the business
information i.e. name, address etc. How would I go about taking one of these
fields and populate a text box on the main form; the parent form of which the
sub form is a part of?
Click to expand...

Generally, you WOULDN'T. Typically a Form is for the "one" side of a
relationship, and the Subform for the "many". For each record on the
main form you might have any number of businesses on the subform; if
the subform has 218 records, which ONE of them do you want to copy to
the mainform? For that matter, are you sure you want to store this
field redundantly in the first place, or do you just want to display
it?

Please explain the context. What's the recordsource of the main form?
of the subform? What do you want to transfer to the mainform? What's
the control source of that textbox?

I just think you may be going the long way around to do something
which may be possible much more simply!

John W. Vinson[MVP]
 
J

Jim

Basically, I need to give the user the ability to select a business from the
subform, then on the main form they can enter the pertinent information for a
job that is related to the selected business. The record source is the
one-to-many relationship between the Rolodex and Jobs tables. I hope this
helps.
 
J

John Vinson

Basically, I need to give the user the ability to select a business from the
subform, then on the main form they can enter the pertinent information for a
job that is related to the selected business. The record source is the
one-to-many relationship between the Rolodex and Jobs tables. I hope this
helps.
Click to expand...

Ok... but typically if you want to enter a business on the mainform
you'ld just use a combo box to select the BusinessID. I still don't
really see how the subform is helping matters, nor what information
about the business you want to store (ideally it shouldn't be anything
other than the ID).

That said... you can put the following VBA code in the AfterUpdate
event of the business combo box on the subform (cboBusinessID I'll
call it). If you want to store the selected BusinessID in the control
named txtBusinessID on the mainform you'ld use:

Private Sub cboBusinessID_AfterUpdate()
Parent!txtBusinessID = Me!cboBusinessID
End Sub

If you want to store a different field from the business table,
include it in the Combo's RowSource query and use the Column property
to select it:

Parent!txtSomeTextBox = Me!cboBusinessID.Column(x)

where x is the *zero based* subscript of the field you want to copy
into the mainform control.

John W. Vinson[MVP]
 
Top