Well, you have a few problems here. The reason nothing is showing in the
coverage field is because nothing get saved in it. A multi select list box
does not return a value like a combo or a single select list box. The
selections are in a collection property of the list box named ItemsSelected.
VBA Help has a good example of how to loop through the ItemsSelected
collection. If the coverage field in tblUHC is bound to the Listbox, you
will have to unbind it and create a text box to bind the field to.
It sounds like you are trying to put multiple values in one field in a
table. That is a violation of basic database normalization rules. If a
client can have more than one coverage type, you need a child table that
lists the coverage types for each client. Then you would use a subform
rather than a list box to display the coverages or add or delete coverages.
You can write some VBA that will concatenate the coverages selected into one
string and populate the text box bound to the coverage field, but then
keeping the text box and the list box in sync will raise even more issues.
My recommendation would be to create the child table, change your form to
include this table on a subform, and forget about the list box. To make it
easy for the user to select a new coverage type, a combo box with the same
row source as you are using for the list box would work well.
:
Yes.. this is a multi select list box. The user will choose EE for Employee
only coverage, CH for coverage of a child, etc. That table is called tblType
(for type of coverage). The table being updated is the tblUHC. The main
problem is the table will not update if I mouse to the correct choice rather
than choosing the first letter unless after choosing I click on the field.
Also when I call the form back up, the list box is blank. I created a
refresh macro to requery the lstType on the form, but it still won't show up
on the form without clicking in the list box after choosing the correct
option.
:
Yes, it makes sense. The problem is, the code
If Me.Dirty Then
Me.Dirty = False
End If
has nothing to do with the list box. It is only updaing the table tblUHC
with the current record.
Which table are you trying to update?
Again, is this a multi select list box?
:
The form record source is a different table called tblUHC. The after update
code is copied from the form's code. The event procedure is under lstType
for the "type" list box. Does that make sense?
:
I don't think you are updating anything except the current record of the
form's record source. Is your form's record source the same as the list box
row source?
Is this a multi select list box?
With a little more info, this can be fixed.
:
I have a list box that is getting its contents from a table for the type of
insurance coverage (CH, EE, EE&SP, EE&CH, EE&FAM) I attached an after update
event procedure to update the table as follows:
Private Sub lstType_AfterUpdate()
If Me.Dirty Then
Me.Dirty = False
End If
End Sub
The trouble is when I reopen the form, the change is not apparent. My row
source is as follows:
SELECT [tblType].[Type] FROM tblType;
Any suggestions on how to get the form to display the update would be
greatly apprecated!
The coverage codes annotate