combo box lookup

  • Thread starter Thread starter barry statham
  • Start date Start date
B

barry statham

Hi, Can anybody help me?
I have a data entry form with a combo box cboIndustry filled by the SQL
SELECT Industries.IndustryID, Industries.IndDescription FROM Industries;
The field IndustriesID contains multiple entries in each row
e.g. Mining of coal, extraction of Peat
This makes the look up function complex, so I am needing a lookup table to
be triggered by the not in list property.
Private Sub cboIndustry_NotInList(NewData As String, Response As Integer)
DoCmd.OpenForm "frmIndustryLookup",,,,,acDialog, NewData
Response = 0
End Sub
The form IndustryLookup has an unbound combo box cboIndustLook, row source
SELECT Industries.IndustryID, Industries.IndDescription FROM Industries
WHERE IndDescription like "**";
frmIndustryLookup has the following code attached:
Private Sub Form_Load()
cboIndustLook.RowSource= "SELECT Industries.IndustryID,
Insustries.IndDescription FROM Industries WHERE IndDescription like "*" &
OpenArgs & "*"
cboIndustLook.Requery
cboIndustLook.SetFocus
cboIndustLook.Dropdown
End Sub
cboIndustLook has the following code
Private Sub cboIndustLook_Change()
Forms![Patient information].cboIndustry = cboIndustLook.Column(2,
cboIndustLook.ListIndex)
DoCmd.Close
End Sub
On entering an industry not on list item the code functions as far as
opening the frmIndustryLook and selecting possible matches for the data
entered in [Patient information].cboIndustry but at that point it fails to
set this combo box to the item you select in the frmIndustryLook
Any Suggestions???
Thanks Barry
 
-----Original Message-----
Hi, Can anybody help me?
I have a data entry form with a combo box cboIndustry filled by the SQL
SELECT Industries.IndustryID, Industries.IndDescription FROM Industries;
The field IndustriesID contains multiple entries in each row
e.g. Mining of coal, extraction of Peat
This makes the look up function complex, so I am needing a lookup table to
be triggered by the not in list property.
Private Sub cboIndustry_NotInList(NewData As String, Response As Integer)
DoCmd.OpenForm "frmIndustryLookup",,,,,acDialog, NewData
Response = 0
End Sub ....
....
On entering an industry not on list item the code functions as far as
opening the frmIndustryLook and selecting possible matches for the data
entered in [Patient information].cboIndustry but at that point it fails to
set this combo box to the item you select in the frmIndustryLook
Any Suggestions???
Thanks Barry


.
Click to expand...
Hi Barry,

usually the line...

Response = acDataErrAdded

..... is included after an insert of new data to instruct
Access that the combobox is to be requeried as the item
has been successfully added to the rowsource.

That is,
DoCmd.OpenForm "frmIndustryLookup",,,,,acDialog, NewData
Response = acDataErrAdded

The line...

Response = acDataErrContinue

..... is included after a prompt to confirm the intention
of
the user for the new data to be added and the user has
responded 'no'. This instructs Access that the item has
not been added to the rowsource and therefore to display
system message.


Luck
Jonathan
 
The field IndustriesID contains multiple entries in each row
e.g. Mining of coal, extraction of Peat
Click to expand...

If by this you mean that the field has two or more different values "Mining
of coal", "extraction of Peat"
then you have made a serious error in the design of your table.
No field should contain more than a single "thing".
This will cause problems that will only be solved by a lot of coding (or
switching to Pick where such things are allowed)

Your ID field should be a field in another table that relates to the
particular company.
 
copyIndustries IndustryID IndDescription
01 Agriculture, hunting, and related service activities
02 Forestry, logging, and related service activities
05 Fishing, operation of fish hatcheries and fish farms, service
incidental to fish
10 Mining of coal and lignite, extraction of peat
11 Extraction of crude petroleum and natural gas, service activities
incidental to oil and gas extraction excludying surveying

Thanks Mike,
I am stuck with the table industries. There are 96 industryID fields similar
to those above. Any other suggestions?
Barry
 
barry statham said:
copyIndustries IndustryID IndDescription
01 Agriculture, hunting, and related service activities
02 Forestry, logging, and related service activities
05 Fishing, operation of fish hatcheries and fish farms, service
incidental to fish
10 Mining of coal and lignite, extraction of peat
11 Extraction of crude petroleum and natural gas, service activities
incidental to oil and gas extraction excludying surveying

Thanks Mike,
I am stuck with the table industries. There are 96 industryID fields similar
to those above. Any other suggestions?
Barry
Click to expand...
It depends on the users. You might consider adding a key word field to the
table "fish", "mining", "extraction"
In any event the code 01,02, 03 etc should be an exposed field on the table
that is linked to the industry table.

If the user uses the program a lot chances are good they will learn the
codes in short order. This is based on my experience in the early 80's with
a report card program I wrote. The people entereing classes found it easier
to learn the numbers rather than look them up.

I would suggest a list box rather than a combo box in this case unless the
keyword idea works.
Also rememeber that a typed list taped to a wall still works.

OR
It is possible to write VBA code that will index the words in the
descriptions.
You would use what is called a stop list to skip indexing common words like
"and", "of" , "to" and any other words that don't help in your search.

OR
Switch to Pick :)
It is almost trivial in Pick Basic to write the code. It is trivial if you
don't bother with error checking and duplicate elimination.
 
Back
Top