Populate combo box with record data when record is pulled by another combo box

  • Thread starter Thread starter Minikoop
  • Start date Start date
M

Minikoop

I have a form which gives the user the ability to pull data up from one
of two combo boxes, both pull the data perfectly. My problem is I
would like the other combo box to display the matching value of the
project pulled from the other combo box. For example: combo box 1
lists project codes and combo box 2 & 3 (linked to pull a record)
contain the PM and the project name. If a user selects the project
code to pull the record the PM (combo 2) and the project name (combo 3)
doesn't match the project displayed. Same problem exists the other
way, if a user selects the PM and project name to pull the record the
project code (combo 1) doesn't match the project displayed.
 
Hi,



In the onEnter event procedure handler of the combo box, check if the other
combo box has some value and if so, modify its rowsource:


for ComboB:

Dim strSQL = "SELECT ... " ' what is generally to be selected, if the
other combo has nothing
if 0 <> len( ComboA & vbNullString) then
strSQL=strSQL & " WHERE projectCode= FORMS!FormNameHere!ComboA"
end if

if strSQL <> ComboB.rowSource then ComboB.rowSource=strSQL


and, for ComboA:


Dim strSQL = "SELECT ... " ' what is generally to be selected, if the
other combo has nothing
if 0 <> len( ComboB & vbNullString) then
strSQL=strSQL & " WHERE PM= FORMS!FormNameHere!ComboA"
end if

if strSQL <> ComboA.rowSource then ComboA.rowSource=strSQL



Hoping it may help,
Vanderghast, Access MVP
 
It appears this code only allows the user to pull one project up by
combo 1 then they must close the form if they wish to pull another
record up with combo 2. Is there anyway around this or did I type the
code wrong?
 
Hi,


Have they tried to clear the combo box "text" box?

If ComboA does not display anything, then

if 0 <> len( ComboA & vbNullString) then


will fail, and the rowsource (of ComboB) will not be limited in anyway.


So, try to clear the combo box(es), or add a button (Clear) that will do it
(probably more user friendly too):

Me.ComboA = null
Me.ComboB = null



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top