F
fysh
I posted this yesterday in forms(coding), but got no
response. Hopefully someone here can point me in the
right direction.
I have a form frmSignIn which allows the person to enter
name and other info along with a combobox (ClinicList)
which the customer can pick the clinic I also have a
Listbox (TypeList) after the clinic is chosen then they
pick reason for visit. I have a query that is the Record
Souce for the form. The query is from 2 tables
tblCustomer and tblVisit (one-to-many-relationship). Now
here is the problem and I hope I don't lose you. I want
the customer to fill in the name, etc. pick clinic and
reason. Then the customer info should go into the
tablCustomer, which it does. Also, the info should go
into the tblVisit, which it doesn't. What I am trying to
do is have the code look at Combobox and Listbox have it
match another table tblClinicType, grab the ClinicTypeID
and assign it to the CustomerID in the tblVisit for each
reason for the visit. Does this make sense? Here is my
lousy code I think I am on the right track, but keep
getting lost in the code. Can someone help me straighten
this one out?
Private Sub cmdDone_Click()
On Error GoTo Err_cmdDone_Click
Dim i As Integer
Dim vClinic As Variant
Dim vType As Variant
Dim ctl As Control
Dim varItm As Variant
Dim db As Database
Dim rst As Recordset
Set ctl = Me.TypeList
vClinic = Me.ClinicList
Dim vClinicType As Variant
If Me.ClinicList < 1 Or Me.TypeList < 1 Then
MsgBox "Select a Clinic and Appointment to Link to
Visit", vbCritical, "SELECT A CLINIC AND APPOINTMENT TYPES"
Else
Set db = CurrentDb()
For Each varItm In ctl.ItemsSelected
Set rst = db.OpenRecordset("tblVisit")
rst.Index = "PrimaryKey"
rst.Seek "=", CustomerID, ClinicType
With rst
If .NoMatch Then
.AddNew
rst![CustomerID] = (tblcustomer.CustomerID)
rst![ClinicType] = ("SELECT
tblClinicType.ClinicTypeID, from tblClinicType WHERE
(((tblClinicType.ClinicID)=[Forms]![frmSignIn].
[ClinicList]) AND ((tblClinicType.TypeID)=[Forms]!
[frmSignIn].[TypeList]))")
rst.Update
End If
End With
Next varItm
rst.Close
db.Close
Me.ClinicList.Requery
For i = 0 To TypeList.ListCount - 1
Me.TypeList.Selected(i) = False
Next
End If
Exit_cmdDone_Click:
Exit Sub
Err_cmdDone_Click:
MsgBox Err.Description
Resume Exit_cmdDone_Click
End Sub
response. Hopefully someone here can point me in the
right direction.
I have a form frmSignIn which allows the person to enter
name and other info along with a combobox (ClinicList)
which the customer can pick the clinic I also have a
Listbox (TypeList) after the clinic is chosen then they
pick reason for visit. I have a query that is the Record
Souce for the form. The query is from 2 tables
tblCustomer and tblVisit (one-to-many-relationship). Now
here is the problem and I hope I don't lose you. I want
the customer to fill in the name, etc. pick clinic and
reason. Then the customer info should go into the
tablCustomer, which it does. Also, the info should go
into the tblVisit, which it doesn't. What I am trying to
do is have the code look at Combobox and Listbox have it
match another table tblClinicType, grab the ClinicTypeID
and assign it to the CustomerID in the tblVisit for each
reason for the visit. Does this make sense? Here is my
lousy code I think I am on the right track, but keep
getting lost in the code. Can someone help me straighten
this one out?
Private Sub cmdDone_Click()
On Error GoTo Err_cmdDone_Click
Dim i As Integer
Dim vClinic As Variant
Dim vType As Variant
Dim ctl As Control
Dim varItm As Variant
Dim db As Database
Dim rst As Recordset
Set ctl = Me.TypeList
vClinic = Me.ClinicList
Dim vClinicType As Variant
If Me.ClinicList < 1 Or Me.TypeList < 1 Then
MsgBox "Select a Clinic and Appointment to Link to
Visit", vbCritical, "SELECT A CLINIC AND APPOINTMENT TYPES"
Else
Set db = CurrentDb()
For Each varItm In ctl.ItemsSelected
Set rst = db.OpenRecordset("tblVisit")
rst.Index = "PrimaryKey"
rst.Seek "=", CustomerID, ClinicType
With rst
If .NoMatch Then
.AddNew
rst![CustomerID] = (tblcustomer.CustomerID)
rst![ClinicType] = ("SELECT
tblClinicType.ClinicTypeID, from tblClinicType WHERE
(((tblClinicType.ClinicID)=[Forms]![frmSignIn].
[ClinicList]) AND ((tblClinicType.TypeID)=[Forms]!
[frmSignIn].[TypeList]))")
rst.Update
End If
End With
Next varItm
rst.Close
db.Close
Me.ClinicList.Requery
For i = 0 To TypeList.ListCount - 1
Me.TypeList.Selected(i) = False
Next
End If
Exit_cmdDone_Click:
Exit Sub
Err_cmdDone_Click:
MsgBox Err.Description
Resume Exit_cmdDone_Click
End Sub