vba - Returning multiple records using Recordset MS Access -

i have ms access form has combo box (which acts primary key) called cboprojectid. trying create form in way once selection made in cboprojectid combo box, triggers combo box called cboerrcod1 display error codes based on available project_id selected in cboprojectid combo box.

only error codes available project id's , defined in table. however, have created query called hdr_errcodes relates project_id, project_code , possible error_reason_code 's available specific project_id.

for example, project code (which selected cboprojectid combo box) these: "fi-01-05", "fi-01-01", "sy-02-02" etc). once selected, project_id stored in table project_dta_rev_t (this table form stores information) , project_id next number (1,2,3,4 etc. etc.).

i understand need use recordset return multiple values when doing vba code. code have started off doesnt seem working in "on change" command cboprojectid combo box field:

private sub cboprojectid_change()  dim varcombokey integer  dim dbs dao.database dim err1 dao.recordset  varcombokey = me.cboprojectid.value  set dbs = currentdb  set err1 = dbs.openrecordset("select distinct [error_reason_code] [hdr_errcodes] [project_id] = " & varcombokey)  while not err1.eof    me!cboerrcod1 = me!cboerrcod1 & err1!error_reason_code & " "    err1.movenext loop  err1.close set err1 = nothing  end sub 

i no vba expert , have been trying follow coding methodology have read on website recordset's. access displaying every type of error_reason_code, not specific error_reason_code 's pertaining project id selected in cboprojectid combo box.

can point me in right direction in getting code display on error_reason_code 's in cboerrcod1 combo box, pertaining project_id selected in cboprojectid combo box??

to change list of items displayed in combo box need modify row source. there 3 options row source type: table/query, value list , field list.

value list take semi colon separated list of values , display them selection. example if entered row source of "cat;dog;mouse" following when selecting drop down.

cat

dog

mouse

field list display field names of whatever query or table select row source.

table query want. populate combo box values table or query provide. doing code , line me!cboerrcod1 = me!cboerrcod1 & err1!error_reason_code & " " changing displayed value in combo box not affecting values available when expand it. have change row source of combo box query have created.

me!cboerrcod1.rowsource = "select distinct [error_reason_code] [hdr_errcodes] [project_id] = " & varcombokey

so method change below since not need modify record set.

private sub cboprojectid_change()     dim varcombokey integer      varcombokey = me.cboprojectid.value      me!cboerrcod1.rowsource = "select distinct [error_reason_code] [hdr_errcodes] [project_id] = " & varcombokey end sub 

edit: have tried duplicate setup see why results return nothing. please correct if have made errors in assumptions.

i made form combo called cboprojectid has source gets of project ids project table(select * projects;) , displays project code in drop down. made 1 called cboerrcod1.

i made 2 tables 1 called projects , other called errorcodes following set ups based on description.

projects                          hdr_errcodes projectid    projectcode(text)    error_reason_code(text)    project_id(integer) 1            fi-01-05             failure                    1 2            fi-01-01             testing                    1 3            sy-02-02             manual                     1 4            sy-01-01             failure                    2 5            sy-01-02 

with above set , on change event combo box cboerrcod1 gets populated correct error codes. there wrong setup?