How to use data from other field in a found record

[A.J.M. van Rijthoven]

I have a table instrumenten (INSID Instrumentname, CATID), a table
Categorie (CATID, Categorydescrip tion), Netten (NETID, description of
net) and a table (kpltblinstrume nt) that links the instruments to a
specific net (NETID, INSID, amount).
I have a form which displays the name of the net and it holds a the
subform from table lnknetins. I have two unlinked fields (combobox) on
the form (voorlopigcateg orie and voorlopiginstru ment). When I select a
category, the following code provides a query for the
voorlopiginstru ment combobox.

If voorlopigcatego rie.Text = "-" Then
PublicSql = "SELECT
Instrumenten.in strumentnaam,in strumenten.insi d, Instrumenten.ca tegorie
FROM Instrumenten;"
Else
PublicSql = "SELECT
instrumenten.In strumentnaam,in strumenten.insi d, instrumenten.Ca tegorie
FROM instrumenten WHERE
(((instrumenten .Categorie)=[Forms]![Inhoud_van_net]![voorlopigcatego rie].[value]));"
End If
voorlopiginstru ment.RowSource = PublicSql

Now I want to fill the field INSID on my subform with the INSID from
the selected instrument in voorlopiginstru ment.
Voorlopiginstru ment.value only gives me the name of the instrument and
not the INSID.
How do I do this??

Simplified:
I've looked up a record in a table based on a name and now I want to
use another field of the found record and place its value in another
record. How do I do this??

[MacDermott]

Re: How to use data from other field in a found record

If I understand your needs correctly, one of the simpler ways to do this is
to set the BoundColumn property of your Voorlopiginstru ment combobox to 2.
Voorlopiginstru ment.value will then return the value from the combobox's 2nd
column (make sure the ColumnCount property is set to at least 2 and the
ColumnWidths give widths for at least 2 columns), which should be the ID,
while the box will continue to display the value from the first column.

HTH
- Turtle

"A.J.M. van Rijthoven" <toine@xs4all.n l> wrote in message
news:pfqk80hcdv 921orbt10kgsm1p jl0f6alli@4ax.c om...[color=blue]
> I have a table instrumenten (INSID Instrumentname, CATID), a table
> Categorie (CATID, Categorydescrip tion), Netten (NETID, description of
> net) and a table (kpltblinstrume nt) that links the instruments to a
> specific net (NETID, INSID, amount).
> I have a form which displays the name of the net and it holds a the
> subform from table lnknetins. I have two unlinked fields (combobox) on
> the form (voorlopigcateg orie and voorlopiginstru ment). When I select a
> category, the following code provides a query for the
> voorlopiginstru ment combobox.
>
> If voorlopigcatego rie.Text = "-" Then
> PublicSql = "SELECT
> Instrumenten.in strumentnaam,in strumenten.insi d, Instrumenten.ca tegorie
> FROM Instrumenten;"
> Else
> PublicSql = "SELECT
> instrumenten.In strumentnaam,in strumenten.insi d, instrumenten.Ca tegorie
> FROM instrumenten WHERE
>[/color]
(((instrumenten .Categorie)=[Forms]![Inhoud_van_net]![voorlopigcatego rie].[va
lue]));"[color=blue]
> End If
> voorlopiginstru ment.RowSource = PublicSql
>
> Now I want to fill the field INSID on my subform with the INSID from
> the selected instrument in voorlopiginstru ment.
> Voorlopiginstru ment.value only gives me the name of the instrument and
> not the INSID.
> How do I do this??
>
> Simplified:
> I've looked up a record in a table based on a name and now I want to
> use another field of the found record and place its value in another
> record. How do I do this??[/color]