Continuous Form & Combo box question

[Peter]

I have a continuous form which displays timesheet records.
employee,date,time,jobnumber,workcode,notes. employee and workcode are both
ID fields which link to other tables to get their text equivalent. These ID
fields I display as limit to list combo boxes, the other fields are text
boxes. The employee combo box works perfectly ok. But the workcode combo
box is giving me grief.

What should happen: When the timesheet opens the workcode combo should
display the description for the code associated with the underlying ID field.
However a workcode has an associated link with the job number. So when a
user clicks the combo box they only see a subset of workcodes. To facilitate
this I have created a query select jobnumber, workcode, workcodedesc where
jobnumber = Forms!frm_timesheet!jobnumber.

What does happen: When the timesheet is opened, only the workcode desc for
the top record displays. The rest of the entered rows have the workcode
blank, if I drop out the where clause in the above query all the data
displays perfectly but my combo box is full of junk not just the ones
associated with the current record. I tried adding a requery when I enter
the combox box but no luck.

I have searched this forum and the web but cannot get very far.

Any help would be really appreciated.

Office 2003

 

[davecass]

I have a continuous form which displays timesheet records.  
employee,date,time,jobnumber,workcode,notes.  employee and workcode are both
ID fields which link to other tables to get their text equivalent.  These ID
fields I display as limit to list combo boxes, the other fields are text
boxes.  The employee combo box works perfectly ok.  But the workcode combo
box is giving me grief.  

What should happen:  When the timesheet opens the workcode combo should
display the description for the code associated with the underlying ID field.
 However a workcode has an associated link with the job number.  So when a
user clicks the combo box they only see a subset of workcodes.  To facilitate
this I have created a query select jobnumber, workcode, workcodedesc where
jobnumber = Forms!frm_timesheet!jobnumber.

What does happen:  When the timesheet is opened, only the workcode desc for
the top record displays.  The rest of the entered rows have the workcode
blank, if I drop out the where clause in the above query all the data
displays perfectly but my combo box is full of junk not just the ones
associated with the current record.  I tried adding a requery when I enter
the combox box but no luck.

I have searched this forum and the web but cannot get very far.

Any help would be really appreciated.

Office 2003

One of the drawbacks of this control is that you can't address
individual lines independently. To do what you're looking to do, you
can't have a single query that applies to the whole form. You'll need
to update the way the workcodes control works every time the record is
changed.

Place the following in the form's OnCurrent event:

me.WorkCodes.rowsource = "select jobnumber, workcode, workcodedesc
where
jobnumber = " & str(me.jobnumber)
me.WorkCodes.requery
if me.workcodes.listindex < 1 then
me.workcodes.value = null
end if

By placing the code in the OnCurrent event, the list behind your
workcodes field will be updated every time the user moves from one
record to the next. The last three lines of code check to see if the
current value of the field is one of the choices in the list. If it
isn't, it sets the value to null. If you don't do this, the control
may display as blank, but actually still have the old value.