Auto fill in without one to many relationship.

[Sherwood Botsford]

Table Markers
ID (Primary Key)
This&That
PointClass (Combo box)
Points

Table PointClasses
PointClass (primary key)
Points (number)
Description (Text)

Many records in Markers will have a given pointclass.
What I want to happen, is when Pointclass is selected in Markers,
I get a drop down box of possible choices. (this works)
and then the Points box in Markers is automatically filled in by looking
up the value of Markers.PointCl ass in PointClasses, and returning the
single value points. (You can tell, I think in procedures...)

If I try to use select I get a raft of values from a join or a where
clause.

Ideas? I feel like I'm trying to change a light bulb with a pipe wrench

[Bruce Pick]

Re: Auto fill in without one to many relationship.

Ach, du lieber! Yes, I think you are truly changing a light bulb, and
probably with a whole box of wrenches.

I'll hit a few topics here, and I bet you already know some of this, but
it's hard to tell exactly which piece you're missing. I actually think
you'll need Point #3 most. Here goes - -

First point: In a database, if Value B is entirely determined by Value
A, there is no point in entering both Value B and Value A in the same
table. You enter A's value in the table, and use a link to get B
whenever you need to use or show that value. In your database, you
crreate a link from the PointClass field in Markers to the PointClass
field in PointClasses. Then the "Foints" value for any PointClass will
be available for display or calculations or anything else.

Second point: In Access forms, the form can be have a table (or a
query) as its data source. Text boxes AND combo boxes can be linked
directly to table fields. This goes for check boxes also (for Yes/No
fields). This is called the control's Control Sousrce (in the
Properties box, Data tab, first row). When you enter a value into one
of these controls it goes directly to the record's field, if the data is
valid for that field.

Third:
Re. the combo box, it's row source (the viewed rows) is not the table
field that is controls, but is often fed by a table or query. You've
set one up to be supplied by the PointClasses table.

The combo box can have multiple columns. A colunmn's width can be 0,
which makes the column invixible. Any column can be the one whose value
is fed to the table field. So, you can show PointClasses to the user,
and record Points in the table. Set the quantity of columns and their
widths in the Combo Box's Format tab, and it's column (#) which feeds to
the database field on the Data tab (Row Source). Remember that Control
Source is the name of the table's field that is being modified.

The table or query feeding the Combo Box's Row Source determines which
columns the box can have, and their sequence. If you don't like the
sequence, you can modify the table design or build a query using the
needed fields in any sequence, and supply that to the combo box.

Good luck!

Bruce Pick
+++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++++ ++
To send mail, remove all < > and [ ] and the junk ~ between:
[brucepick1]
< a~t >
[comcast.net]


Sherwood Botsford wrote:[color=blue]
> Table Markers
> ID (Primary Key)
> This&That
> PointClass (Combo box)
> Points
>
> Table PointClasses
> PointClass (primary key)
> Points (number)
> Description (Text)
>
> Many records in Markers will have a given pointclass.
> What I want to happen, is when Pointclass is selected in Markers,
> I get a drop down box of possible choices. (this works)
> and then the Points box in Markers is automatically filled in by looking
> up the value of Markers.PointCl ass in PointClasses, and returning the
> single value points. (You can tell, I think in procedures...)
>
> If I try to use select I get a raft of values from a join or a where
> clause.
>
> Ideas? I feel like I'm trying to change a light bulb with a pipe wrench[/color]

[Sherwood Botsford]

Re: Auto fill in without one to many relationship.

Bruce Pick <munged@email.n et> wrote in message news:<hKOdnUGSO NBjT7zdRVn-hA@comcast.com> ...[color=blue]
> Ach, du lieber! Yes, I think you are truly changing a light bulb, and
> probably with a whole box of wrenches.
>
> I'll hit a few topics here, and I bet you already know some of this, but
> it's hard to tell exactly which piece you're missing. I actually think
> you'll need Point #3 most. Here goes - -
>
> First point: In a database, if Value B is entirely determined by Value
> A, there is no point in entering both Value B and Value A in the same
> table. You enter A's value in the table, and use a link to get B
> whenever you need to use or show that value. In your database, you
> crreate a link from the PointClass field in Markers to the PointClass
> field in PointClasses. Then the "Foints" value for any PointClass will
> be available for display or calculations or anything else.
>[/color]

True, but I do most of my work in table views. Particularly when
prototyping
the aggravation of using forms and reports takes much time.

[color=blue]
> Second point: In Access forms, the form can be have a table (or a
> query) as its data source. Text boxes AND combo boxes can be linked
> directly to table fields. This goes for check boxes also (for Yes/No
> fields). This is called the control's Control Sousrce (in the
> Properties box, Data tab, first row). When you enter a value into one
> of these controls it goes directly to the record's field, if the data is
> valid for that field.
>[/color]

Yeah, I do this a lot.
[color=blue]
> Third:
> Re. the combo box, it's row source (the viewed rows) is not the table
> field that is controls, but is often fed by a table or query. You've
> set one up to be supplied by the PointClasses table.
>
> The combo box can have multiple columns. A colunmn's width can be 0,
> which makes the column invixible. Any column can be the one whose value
> is fed to the table field. So, you can show PointClasses to the user,
> and record Points in the table. Set the quantity of columns and their
> widths in the Combo Box's Format tab, and it's column (#) which feeds to
> the database field on the Data tab (Row Source). Remember that Control
> Source is the name of the table's field that is being modified.
>
> The table or query feeding the Combo Box's Row Source determines which
> columns the box can have, and their sequence. If you don't like the
> sequence, you can modify the table design or build a query using the
> needed fields in any sequence, and supply that to the combo box.[/color]

And this works for the selection of the pointclass. I've actually got
it displaying 3 columns, so that the user who's entering data, can see
how each
class is defined. BUT it won't fill in more than the one column.

In general is there a way to either calculate or lookup a value for
one
field based on one or more fields in the same record IN THE TABLE
VIEW.


Thanks for the help.[color=blue]
>[/color]

[Bruce Pick]

Re: Auto fill in without one to many relationship.

Yes, I recall there is a way to reference the values in different
columns of the dropdown list. Then you can set TextBoxX = data.

I think it's the column or columns property of the combo box. I haven't
used it in Access, only rarely and a year ago in VB, so I don't have the
detail.

Bruce

+++++++++++++++ +++++++++++++++ +++++++++++++++ +++++++++++++++ ++
To send email, remove all < > and [ ] and the junk ~ between:
[brucepick1]
< a~t >
[comcast.net]


Sherwood Botsford wrote:[color=blue]
> Bruce Pick <munged@email.n et> wrote in message news:<hKOdnUGSO NBjT7zdRVn-hA@comcast.com> ...
>[color=green]
>>Ach, du lieber! Yes, I think you are truly changing a light bulb, and
>>probably with a whole box of wrenches.
>>
>>I'll hit a few topics here, and I bet you already know some of this, but
>>it's hard to tell exactly which piece you're missing. I actually think
>>you'll need Point #3 most. Here goes - -
>>
>>First point: In a database, if Value B is entirely determined by Value
>>A, there is no point in entering both Value B and Value A in the same
>>table. You enter A's value in the table, and use a link to get B
>>whenever you need to use or show that value. In your database, you
>>crreate a link from the PointClass field in Markers to the PointClass
>>field in PointClasses. Then the "Foints" value for any PointClass will
>>be available for display or calculations or anything else.
>>[/color]
>
>
> True, but I do most of my work in table views. Particularly when
> prototyping
> the aggravation of using forms and reports takes much time.
>
>
>[color=green]
>>Second point: In Access forms, the form can be have a table (or a
>>query) as its data source. Text boxes AND combo boxes can be linked
>>directly to table fields. This goes for check boxes also (for Yes/No
>>fields). This is called the control's Control Sousrce (in the
>>Properties box, Data tab, first row). When you enter a value into one
>>of these controls it goes directly to the record's field, if the data is
>>valid for that field.
>>[/color]
>
>
> Yeah, I do this a lot.
>
>[color=green]
>>Third:
>>Re. the combo box, it's row source (the viewed rows) is not the table
>>field that is controls, but is often fed by a table or query. You've
>>set one up to be supplied by the PointClasses table.
>>
>>The combo box can have multiple columns. A colunmn's width can be 0,
>>which makes the column invixible. Any column can be the one whose value
>>is fed to the table field. So, you can show PointClasses to the user,
>>and record Points in the table. Set the quantity of columns and their
>>widths in the Combo Box's Format tab, and it's column (#) which feeds to
>>the database field on the Data tab (Row Source). Remember that Control
>>Source is the name of the table's field that is being modified.
>>
>>The table or query feeding the Combo Box's Row Source determines which
>>columns the box can have, and their sequence. If you don't like the
>>sequence, you can modify the table design or build a query using the
>>needed fields in any sequence, and supply that to the combo box.[/color]
>
>
> And this works for the selection of the pointclass. I've actually got
> it displaying 3 columns, so that the user who's entering data, can see
> how each
> class is defined. BUT it won't fill in more than the one column.
>
> In general is there a way to either calculate or lookup a value for
> one
> field based on one or more fields in the same record IN THE TABLE
> VIEW.
>
>
> Thanks for the help.
>[/color]

[Bruce Pick]

Re: Auto fill in without one to many relationship.

In the table view? Someone else will know, I don't.

Bruce