REFRESH Combo boxes for records on continous form

[Guest]

Background:
I have a continous form linked to a table and I have 3 combo boxes on the
form (linked to fields on the table). The combo boxes are called JOB TITLE,
SCHEDULE and SHIFT. The source for the combo boxes are queries named
respectively as
LKUPJOB, LKUPSCHEDULE and LKUPSHIFT. For the three combo boxes listed abpve,
the bound column is the first field of the underlying query and the column
width of the first field is set to 0" (so that the second field of the query
is displayed).

The way things should work is that for each record on the form, the
LKUPSCHEDULE and LKUPSHIFT queries (queries for the SCHEDULE and SHIFT combo
boxes), the queries are filtered based on the bound field of the JOB TITLE
combo box. The name of the bound field for the JOB TITLE combo is called
Labor_Rate_ID.


Problem:
Unfortunately, It seems like the value of the labor-rate_ID in the first
record (i.e. first job title combo box) determines the query list used by the
SCHEDULE and SHIFT comboboxes for all the other records on the form. That is,
even though the form has lots of records displayed, the combo boxes for each
record don't have a complete list to work with.

Hence, the SCHEDULE and SHIFT combo boxes for some records are showing a
blank (since available otions in the combo query list don't match the value
in teh table for that record.

Can anybody tell me how to get a continous form to refress the list for each
record on the form.

I also don't know whether the Requery command for a macro could work and how
to use it. I think though, that this problem can't be solved by a macro.

Pele

 

[Wayne Morgan]

One work around is to place a textbox over the textbox portion of the combo
box. Place it so that the borders match exactly. Set the textbox Enabled =
No and Locked = True. Set the Control Source of the textbox to have it
display the value that should be in the combo box using a DLookup() or other
statement. Since the textbox can't get the focus, when you move to that
control, the textbox will fall behind the combo box and the combo box will
work. The controls can't have a transparent background since you need them
to hide each other.

It's been a while since I've tried this, so I may have missed a couple of
things.

 

[Steve Demby]

If I understand the question correctly I have the same problem. Access does
not provide an event as each record is filled from the database to allow you
to fill other dependent controls. I believe in order to do this you would
need to sub class the underlying recordset in ado and take action when the
"fill" event takes place. Does anyone have an example of this type of code?

 

[Guest]

I was having the same problem. Based on Wayne's input, I wrote the following
events:

Private Sub cmbRpt_Change()

' Change the value of the overlaid text box when selecting a different report.

If Not IsNull(cmbRpt.Value) Then
txtMod.Value = DLookup("rpt_display_name", "dbo_reports", "rpt_id =
" & cmbRpt.Value)
txtMod.SetFocus
End If

End Sub


Private Sub txtMod_AfterUpdate()

Dim intValue As String

' Update the report name based on changes in the overlaid text box.

DoCmd.SetWarnings False

intValue = cmbRpt.Value

If MsgBox("Do you want to change the name of this report?", vbYesNo) =
vbYes Then
' Create the SQL statement to update the report name.
mySQL = "UPDATE dbo_reports set rpt_display_name = '" & txtMod.Value
& "' where rpt_id = " & intValue
' Set the row source to nothing for a moment.
cmbRpt.RowSource = ""
DoCmd.RunSQL mySQL
' Now set the row source to what it should be.
cmbRpt.RowSource = "SELECT dbo_reports.rpt_id,
dbo_reports.rpt_display_name, dbo_reports.program_id FROM dbo_reports ORDER
BY dbo_reports.rpt_display_name; "
Else
' User really didn't want to change the report name; put it back.
txtMod.Value = DLookup("rpt_display_name", "dbo_reports", "rpt_id =
" & intValue)
End If

DoCmd.SetWarnings True

End Sub

There are only two events here. The objects they represent are cmbRpt, a
combo box with report ID numbers and names; and txtMod, a text box that
overlays cmbRpt as Wayne suggested. This works just spiffy.