Stumped on listbox process.

  • Thread starter Thread starter Fysh1
  • Start date Start date
F

Fysh1

Good morning everyone. Hopefully some of you experts can
help to point me in the right direction or give me some
advice on how to do this.

I have the following tables tables set up.
tblClinic tblAppointmentType ClinicType
ClinicID TypeID ClinicTypeID
Clinic Type ClinicID
TypeID
I have queries with the same info.
Now I have form with A combobox for the Clinic and a
listbox for AppointmentType both unbound. I also have
another list box for Clinic AppointmentType. I want to be
able to pick the Clinic then pick the AppointmentTypes
from the list box. Press Update and have the
AppointmentType assigned to the clinic. Sounds simple,
but I am confused on how to get this to happen. If I can
get this working then I can use it to assign other info.
Hopefully someone can help. Thanks in advance for all
replys.
 
OK I am stumped, can someone help? This is what I think I
need to do after reading the help files. I am trying to
assign a value to ClinicID and TypeID in the tblClinicType
table. Does this make sense?

Private Sub List1_AfterUpdate()

Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer
Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblClinicType")
Set ctlSource = frm!List1
Set ctlDest = frm!List1
rst![ClinicID] = Forms![frmClinic].[Clinic] And rst!
[TypeID] = frm!List1

For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & ctlSource.Column(0, _
intCurrentRow) & ";"
End If
Next intCurrentRow

' Reset destination control's RowSource property.
ctlDest.RowSource = ""
ctlDest.RowSource = strItems

Set ctlSource = Nothing
Set ctlDest = Nothing

End Sub
 
I have the following tables tables set up.
tblClinic tblAppointmentType ClinicType
ClinicID TypeID ClinicTypeID
Clinic Type ClinicID
TypeID
I have queries with the same info.
Now I have form with A combobox for the Clinic and a
listbox for AppointmentType both unbound. I also have
another list box for Clinic AppointmentType. I want to be
able to pick the Clinic then pick the AppointmentTypes
from the list box. Press Update and have the
AppointmentType assigned to the clinic.
Click to expand...

Well, you could do this, but that's the hard way to go about it!

Why not use a Form based on tblClinic with a Subform based on
ClinicType; on the Subform you'ld use ClinicID as the master/child
link field, and have a combo box based on tblAppointmentType bound to
TypeID. No code, no buttons needed...
 
Back
Top