listbox & form coding problem

[dchendrickson]

John,

Here are a few thoughts, but by no means a complete
solution. Also keep in mind that the code is using ADO
methods, not DAO. Someone else will have to answer your
DAO questions. Regardless of which one you use, the
approach should work for either.

You want to create code for the OnChange event of your
combo box. When a selection is made in the combo box,
this code will run.

The code will be a few simple lines that requery your
listbox. Something like:

Dim lst As ListBox
Set lst = Me!lstPatientDenials
lst.Requery
OR
Me!lstPatientDenials.Requery

Next, you want to set the Row Source for your list box to
a SQL statement that will pull in the data you are
looking for. Perhaps something like:

SELECT Account, Name,... FROM tblAccounts WHERE
TransactionCode = Forms!frmCustServTest!cboDenialCodes;

Make sure that the bound column of the combo box is the
same type of data as the transaction code in your table.

Next you want to create code for the OnClick event of
your list box. This code is simple again. It causes the
subform to requery. Something like:

Dim sfr as Form
Set sfr = Me!sbfrmCustServDenials.Form
sfr.Requery

Set the form Record Source for the sbfrmCustServDenials
form to an SQL statement that is something like:

SELECT * FROM tblAccountDetails WHERE Account = Forms!
frmCustServTest!lstPatientDenials;

I don't know if you need help with your Save button or
not and it is unclear if your check box is on the main
form or the subform.

In general, you would create code for the OnClick event
of your save button. That code would create a recordset
associated with your UpdatedInfo table. Then you would
either use the .find or .seek methods to locate the
record to update, or the .AddNew method to create a new
one. Once the record you want is the current one in the
recordset, you would set the individual fields equal to
the values of the matching controls in the subform.
Something like (and by no means is this syntax complete):

Dim rst As New ADODB.Recordset
Dim cnn as ADODB.Connection
Dim sfr as Form
Set cnn = currentproject.connnection
Set sfr = Me!sbfrmCustServDenials.Form
rst.open "tblUpdatedInfo",cnn,...
rst.find "Account = " & Me!lstPatientDenials
OR
rst.AddNew
With rst
!detail1 = sfr!txtDetail1
!detail2 = sfr!txtDetail2
.....
!Worked = sfr!chkAccountWorked
OR
!Worked = Me!chkAccountWorked (if on the main form)
End With
rst.Update

Hope this gives you a few ideas.

-dc