Column Assistance (1 Viewer)

[kevin_e]

For starters, I am using a very basic form for data input. The reason for this is that I need it to match a specific excel data form that we have created for several existing projects.

The form is a series of 11 columns. Column one is a drop down list of plant species and columns 2-11 are different plots where these plant species may be encountered. These species are given a percent cover within each of the 10 plots.

My data form is just a "subform" insert of the table.

The table columns looks like such ("-" indicates column break):

"Species" - "1" - "2" - "3" - "4" - "5" - "6" - "7" - "8" - "9" - "10"

The each row under the "species" column heading is a list of auto populating species. Each row under the last 10 columns heading is a manual number input relating to the species selected.

i.e.

"Species" - "1" - "2" - "3"....
Species ID - 25% - 15% - 10%....

I have two questions:

Question 1 is if I can have all cells self populate as "0" unless otherwise manually changed to a number that we designate it as.

Question 2 requires a little more explanation. So with each species, there is a 6 letter code given in a second, but adjacent column.

i.e.

"Species" - "6 letter code"
Pinus elliotti (pine tree) - pin ell

Is there a way to have each column autopopulate in my combobox dropdown menu (both columns are listed, but on the "species" column autopopulates)?

Secondly, is there a way to select either the "species" column or "6 letter code" column and have the adjacent column recorded as well. If I need to put an additional column in my form table, that is fine. I can hide it.

Basically, whether I select the 6 letter code or the species, I need both to record together in the table. The end goal is to remove processing time. It's easier to type in the 6 letter code in the field, but we don't need to waste time converting that into the species name back in the office. We need to copy and paste it as is into our excel form.

I understand there is a better way of creating a form in Access, but the way the query records data is opposite format as the way our data table is set up. This means we have to copy and paste each species and percent as one. It defeats the purpose of digital data entry.

I hope I was clear. Thank you.

 

[Ranman256]

Q1, set the default value to the fields = 0
Q2, in the AFTERUPDATE event of the combo box, populate a field with the 2nd col. :
NOTE: in code , the columns start with zero. So your 2nd column in the list will be
column(1)

 sub cboBox_Afterupdate()
    [field2] = cboBox.column(1)
 end sub

 

[kevin_e]

Q1, set the default value to the fields = 0
Q2, in the AFTERUPDATE event of the combo box, populate a field with the 2nd col. :
NOTE: in code , the columns start with zero. So your 2nd column in the list will be
column(1)

 sub cboBox_Afterupdate()
    [field2] = cboBox.column(1)
 end sub

Like this? I want to type the 6 letter code into column one and have the full species populate in the next column over based on the drop down menu.

Having the ability to type in either the 6 letter code or species and self populate in column one would be nice too. Right now, only the first column in the drop down self populates.

 

[kevin_e]

Still have not resolved this issue. Thank you.

 

[Minty]

I'm not sure I grasp what you want to achieve. If the data is in column(1) (Second column) on the drop down it must already be associated with column (0) (First Column) , so if you are trying to simply copy the contents of into another field , why not just look them up in a query? The query result can be copied / exported to excel which would save you having to even have the second columns data saved.

Combo boxes do not allow you to "set" the values in other columns within that combo control. They are there to display data or can be used to set or assist with choosing or displaying other values in other controls within your form.