Automatically populating values

G

Guest

The user is allowed to pick a vegetation code (VEGCODE) from a look-up table
on the form. I want 2 other controls on the form to be automatically
populated with the common name and latin name for the vegetation species (so
the user knows which species the vegcode belongs...sometimes people know
common names...others latin names). The latin and common names are values
already contained within the look-up for the veg code.

The code I have works....the latin/common names associated to the vegcode
are written to the table when the veg species is added.....howvever I want
these names to appear in the form controls before the user writes the new
record (just in case they're not sure which species the code belongs to....

I don't understand why the latin/common names are written to the table but
are not visibly populated on the form!!!

the code is as follows:

Private Sub frmVEG_CODE_AfterUpdate()

Dim dbs As Database
Dim qryveg As QueryDef
Dim recset As Recordset
Dim prm As Variant

Set dbs = CurrentDb
Set qryveg = dbs.CreateQueryDef("")
prm = Forms("UNDERSTORY").Controls("frmVEG_CODE").Value
sqlout "SELECT [LOOK_VEGSPP].[VEG_SPPCODE], [LOOK_VEGSPP].[LATIN],
[LOOK_VEGSPP].[COMMON], [LOOK_VEGSPP].[VEG_TYPE] " & _
"FROM [LOOK_VEGSPP] " & _
"WHERE ((([LOOK_VEGSPP].[VEG_SPPCODE]) = '" & prm & "'));", qryveg
End Sub

Function sqlout(strsql As String, qryveg As QueryDef)
Dim recset As Recordset
qryveg.SQL = strsql
Set recset = qryveg.OpenRecordset
'Debug.Print strsql
With recset
Forms("UNDERSTORY").Controls("frmLATIN").Value = !LATIN
Forms("UNDERSTORY").Controls("frmVEG_TYPE").Value = !VEG_TYPE
Forms("UNDERSTORY").Controls("frmCOMMON").Value = !COMMON
End With

End Function

Does anyone know why my form controls do not automatically/visibly populate???

any clarification would be appreciated....
 
J

Joseph Meehan

hmmmm? said:
The user is allowed to pick a vegetation code (VEGCODE) from a
look-up table on the form. I want 2 other controls on the form to be
automatically populated with the common name and latin name for the
vegetation species (so the user knows which species the vegcode
belongs...sometimes people know common names...others latin names).
The latin and common names are values already contained within the
look-up for the veg code.

The code I have works....the latin/common names associated to the
vegcode
are written to the table when the veg species is added.....
Click to expand...

Let's stop and fix this first. You should not be populating a table
with information from a look up table. You just look it up anytime you need
it. Using the capabilities of a query is one straight forward way. Just
set up the relationship for the tables and use both tables as a data source
for the query.
 
G

Guest

You should not store the Latin and Common names in the referencing table,
only in he referenced LOOK_VEGSPP table. The VEGCODE column's value maps to
the relevant row in the LOOK_VEGSPP table so to store the other values in the
referencing table is redundant and the table is not correctly normalized.
This leaves it open to possible update anomalies.

On the form you should have a combo box bound to the VEGCODE column with a
RowSource:

SELECT VegCode, Common, Latin
FROM Look_Vegspp
ORDER BY VegCode;

Set the combo box's BoundColumn property to 1, its ColumnCount to 3 and its
ColumnWidths to something like 3cm;3cm;3cm (experiment to get the best fit;
they can each be different dimensions). Set the ListWidth property to the
sum of the ColumnWidths dimensions, i.e. 9cm with my example. The user will
see all three columns in the drop down list, but just the VEGCODE will show
in the control when a selection is made.

Add two unbound text boxes to the form with ControlSource properties of:

=cboVegCode.Column(1)
and
=cboVegCode.Column(2)

where cboVegCode is the name of the combo box. The Column property is
zero-based, so Column(1) is the second column and Column(2) the third.

When the user selects a VegCode from the list the two text boxes will show
the Common and Latin names respectively.

In a report do it slightly differently. Don't use a combo box and unbound
text boxes but base the report on a query which joins the referencing table
and the referenced LOOK_VEGSPP table on the VEGCODE columns and bind text
boxes to the columns from the referenced table.

Ken Sheridan
Stafford, England

hmmmm? said:
The user is allowed to pick a vegetation code (VEGCODE) from a look-up table
on the form. I want 2 other controls on the form to be automatically
populated with the common name and latin name for the vegetation species (so
the user knows which species the vegcode belongs...sometimes people know
common names...others latin names). The latin and common names are values
already contained within the look-up for the veg code.

The code I have works....the latin/common names associated to the vegcode
are written to the table when the veg species is added.....howvever I want
these names to appear in the form controls before the user writes the new
record (just in case they're not sure which species the code belongs to....

I don't understand why the latin/common names are written to the table but
are not visibly populated on the form!!!

the code is as follows:

Private Sub frmVEG_CODE_AfterUpdate()

Dim dbs As Database
Dim qryveg As QueryDef
Dim recset As Recordset
Dim prm As Variant

Set dbs = CurrentDb
Set qryveg = dbs.CreateQueryDef("")
prm = Forms("UNDERSTORY").Controls("frmVEG_CODE").Value
sqlout "SELECT [LOOK_VEGSPP].[VEG_SPPCODE], [LOOK_VEGSPP].[LATIN],
[LOOK_VEGSPP].[COMMON], [LOOK_VEGSPP].[VEG_TYPE] " & _
"FROM [LOOK_VEGSPP] " & _
"WHERE ((([LOOK_VEGSPP].[VEG_SPPCODE]) = '" & prm & "'));", qryveg
End Sub

Function sqlout(strsql As String, qryveg As QueryDef)
Dim recset As Recordset
qryveg.SQL = strsql
Set recset = qryveg.OpenRecordset
'Debug.Print strsql
With recset
Forms("UNDERSTORY").Controls("frmLATIN").Value = !LATIN
Forms("UNDERSTORY").Controls("frmVEG_TYPE").Value = !VEG_TYPE
Forms("UNDERSTORY").Controls("frmCOMMON").Value = !COMMON
End With

End Function

Does anyone know why my form controls do not automatically/visibly populate???

any clarification would be appreciated....
Click to expand...
 
G

Guest

WOW....talk about doing things the hard way.....

Thanks for the help!

Ken Sheridan said:
You should not store the Latin and Common names in the referencing table,
only in he referenced LOOK_VEGSPP table. The VEGCODE column's value maps to
the relevant row in the LOOK_VEGSPP table so to store the other values in the
referencing table is redundant and the table is not correctly normalized.
This leaves it open to possible update anomalies.

On the form you should have a combo box bound to the VEGCODE column with a
RowSource:

SELECT VegCode, Common, Latin
FROM Look_Vegspp
ORDER BY VegCode;

Set the combo box's BoundColumn property to 1, its ColumnCount to 3 and its
ColumnWidths to something like 3cm;3cm;3cm (experiment to get the best fit;
they can each be different dimensions). Set the ListWidth property to the
sum of the ColumnWidths dimensions, i.e. 9cm with my example. The user will
see all three columns in the drop down list, but just the VEGCODE will show
in the control when a selection is made.

Add two unbound text boxes to the form with ControlSource properties of:

=cboVegCode.Column(1)
and
=cboVegCode.Column(2)

where cboVegCode is the name of the combo box. The Column property is
zero-based, so Column(1) is the second column and Column(2) the third.

When the user selects a VegCode from the list the two text boxes will show
the Common and Latin names respectively.

In a report do it slightly differently. Don't use a combo box and unbound
text boxes but base the report on a query which joins the referencing table
and the referenced LOOK_VEGSPP table on the VEGCODE columns and bind text
boxes to the columns from the referenced table.

Ken Sheridan
Stafford, England

hmmmm? said:
The user is allowed to pick a vegetation code (VEGCODE) from a look-up table
on the form. I want 2 other controls on the form to be automatically
populated with the common name and latin name for the vegetation species (so
the user knows which species the vegcode belongs...sometimes people know
common names...others latin names). The latin and common names are values
already contained within the look-up for the veg code.

The code I have works....the latin/common names associated to the vegcode
are written to the table when the veg species is added.....howvever I want
these names to appear in the form controls before the user writes the new
record (just in case they're not sure which species the code belongs to....

I don't understand why the latin/common names are written to the table but
are not visibly populated on the form!!!

the code is as follows:

Private Sub frmVEG_CODE_AfterUpdate()

Dim dbs As Database
Dim qryveg As QueryDef
Dim recset As Recordset
Dim prm As Variant

Set dbs = CurrentDb
Set qryveg = dbs.CreateQueryDef("")
prm = Forms("UNDERSTORY").Controls("frmVEG_CODE").Value
sqlout "SELECT [LOOK_VEGSPP].[VEG_SPPCODE], [LOOK_VEGSPP].[LATIN],
[LOOK_VEGSPP].[COMMON], [LOOK_VEGSPP].[VEG_TYPE] " & _
"FROM [LOOK_VEGSPP] " & _
"WHERE ((([LOOK_VEGSPP].[VEG_SPPCODE]) = '" & prm & "'));", qryveg
End Sub

Function sqlout(strsql As String, qryveg As QueryDef)
Dim recset As Recordset
qryveg.SQL = strsql
Set recset = qryveg.OpenRecordset
'Debug.Print strsql
With recset
Forms("UNDERSTORY").Controls("frmLATIN").Value = !LATIN
Forms("UNDERSTORY").Controls("frmVEG_TYPE").Value = !VEG_TYPE
Forms("UNDERSTORY").Controls("frmCOMMON").Value = !COMMON
End With

End Function

Does anyone know why my form controls do not automatically/visibly populate???

any clarification would be appreciated....
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to creat a Query in Code 20
Help with Query Code 6
Transfer spreadsheet error 4
Filtered Records as source for appending records to a table 1
Error 3061 - produced by 2 out of 3 same reports 5
How to loop through controls and forms? 5
Use access to rename / overwrite image files. 2
Using values from combo box in subform 2

Top