Hello all,
In summary: I have two combo boxes in a subform, which enter into same column on a table, but I want to input a number relating to which combo box input the data.
Now for the details...
I have a form for data entry, which is based on a card used for taking notes about sampling sites in the field. The layout of the form is designed to be similar to the cards to aid data entry (this is the root of most of my problems!).
On the card is written the initials of the two collectors, first the one is who wrote the card, the second is who collected the sample (so order is important). Both of these fields are replicated on the mainform as a subform (linked to junction table tblCollectors) containing two combo boxes, cbxCollector1 and cbxCollector2. These both draw the data from a lookup table LtblCollectors (showing valid choices) and these are input into tblCollectors (juction table). tblCollectors is laid out as follows:
SiteID (Number) {from Main table}
CollectorID (Text) {from Lookup table}
Priority (Number)
Here is my problem:
When Collector 1 is entered, this value is transferred to cbxCollector2, clearly I need to create a new record in tblCollectors, but not in the main table. I would like to have a '1' entered into column 'Priority' automatically on entry from cbxCollectors1 and a '2' entered into column 'Priority' automatically on entry from cbxCollectors2. Both should obviously carry across the same SiteID from the main table (tblSiteData).
I would also like the original selection still visible in the first box after the second selection has been made so the 'inputter' can review the data as they go.
I don't want to have to have a column 'Collector1' and 'Collector2', as this repeats information, and I have a similar situation for numerous sections in the database, sometimes up to 6 similar fields requiring 'prioritisation'. I do not want to use Autonumbers as this will make queries difficult (e.g. Most abundant rock at each site = 1, second most = 2, etc...).
I have considered putting a hidden text box for each cbx, to hold the 'priority number' although I think use of a global variable would be a more appropriate solution, increasing by 1 for each cbx that requires filling, although I predict a few problems with this when it comes to reports and queries, as well as displaying the data in the control after entry…
I am wondering if I can concatenate the combo box value the following way, for cbx1 carry across ‘1’ appended to the value pulled from the lookup table, then split them into the two columns in the table, then move to the next control/record?
Both these methods seem to have problems with them, and I’ve spent days trying to find a solution, but with no luck – so any alternative ideas or guidance will be hugely appreciated!
In summary: I have two combo boxes in a subform, which enter into same column on a table, but I want to input a number relating to which combo box input the data.
Now for the details...
I have a form for data entry, which is based on a card used for taking notes about sampling sites in the field. The layout of the form is designed to be similar to the cards to aid data entry (this is the root of most of my problems!).
On the card is written the initials of the two collectors, first the one is who wrote the card, the second is who collected the sample (so order is important). Both of these fields are replicated on the mainform as a subform (linked to junction table tblCollectors) containing two combo boxes, cbxCollector1 and cbxCollector2. These both draw the data from a lookup table LtblCollectors (showing valid choices) and these are input into tblCollectors (juction table). tblCollectors is laid out as follows:
SiteID (Number) {from Main table}
CollectorID (Text) {from Lookup table}
Priority (Number)
Here is my problem:
When Collector 1 is entered, this value is transferred to cbxCollector2, clearly I need to create a new record in tblCollectors, but not in the main table. I would like to have a '1' entered into column 'Priority' automatically on entry from cbxCollectors1 and a '2' entered into column 'Priority' automatically on entry from cbxCollectors2. Both should obviously carry across the same SiteID from the main table (tblSiteData).
I would also like the original selection still visible in the first box after the second selection has been made so the 'inputter' can review the data as they go.
I don't want to have to have a column 'Collector1' and 'Collector2', as this repeats information, and I have a similar situation for numerous sections in the database, sometimes up to 6 similar fields requiring 'prioritisation'. I do not want to use Autonumbers as this will make queries difficult (e.g. Most abundant rock at each site = 1, second most = 2, etc...).
I have considered putting a hidden text box for each cbx, to hold the 'priority number' although I think use of a global variable would be a more appropriate solution, increasing by 1 for each cbx that requires filling, although I predict a few problems with this when it comes to reports and queries, as well as displaying the data in the control after entry…
I am wondering if I can concatenate the combo box value the following way, for cbx1 carry across ‘1’ appended to the value pulled from the lookup table, then split them into the two columns in the table, then move to the next control/record?
Both these methods seem to have problems with them, and I’ve spent days trying to find a solution, but with no luck – so any alternative ideas or guidance will be hugely appreciated!
