getting data from different tables into tabbed panel from combobox

[kibje]

Hi all,

I designed a database for work and am now busy implementing it in Access, the only problem being I have no experience whatsoever with VBA. I did see a lot of the possibilities though and managed to figure out how to do the biggest part. I am just stuck with one problem which I hope you can help me with.

Let me explain:

My database contains a structure similar to this one: [i simplified a lot]

[ENROLMENT]
id (unique key)
patient_id (indexed, no duplicates)
name
hospital
etc

[BASELINE]
id (unique key)
patient_id (indexed, no duplicates)
consent_date
reg_date
birth_date
etc

[SURGERY]
id (unique key)
patient_id (indexed, no duplicates)
surgery_date
type_debulking
etc

The procedure is as follows:
In the beginning a patient is enrolled. Much data is collected in the two years afterwards. This data should be easily visible and editable. Thats why i chose for the following design:

In the header of the form a combobox called "SearchEnrolledPatient" which is filled with the different values from
- enrolment.patient_id

Below that a tabbed panel. The first tab is called Baseline and should show all the baseline values for the selected patient (if that patient has had baseline done and therefore exists in the baseline table). The second tab is called Surgery and when selected should show the pane that has all the values from the surgery table, for the currently selected (already enrolled) patient.

i got the combobox filled with the correct values without trouble but i couldn't get the connection with the tabbed panes working.
Can anyone explain to me how to do it ?

 

[kjv1611]

Hmm.. just guessing here, but I think you need something like this, possibly (assuming you mean you have a tab group on your form):

Private Sub cmbPatient_AfterUpdate()
  Dim db as DAO.Database
  Dim rs as DAO.Recordset
  Set db = CurrentDb
  Set rs = db.OpenRecordset("tblPatientInfo")

  Dim strPatient As String
  strPatient = DLookup("[Patient]", "tblPatientInfo", & _
               "[Patient] = " & cmbPatient)
  
  With rs
    Do While Not .EOF
      If rs.Fields("Patient") = strPatient Then
         tabWhateverTabName.Visible = True
      End If
    Loop
  End With

  Set rs = Nothing
  Set db = Nothing

Some changes may be necessary here to work, and not knowing exactly what you are trying to do, you may not need the If/Then statement. You may need to use a Select Case statement based upon the value in the combobox... maybe something like this:

Private Sub cmbPatient_AfterUpdate()
  Dim strPatientType as String
  strPatientType = cmbPatientType
  
  Select Case cmbPatientType
    Case "Heart"
      TabHeartPatient.Visible = True
      TabLungPatient.Visible = False
      TabBrainPatient.Visible = False
    Case "Lung"
      TabHeartPatient.Visible = False
      TabLungPatient.Visible = True
      TabBrainPatient.Visible = False
    Case "Brain"
      TabHeartPatient.Visible = False
      TabLungPatient.Visible = False
      TabBrainPatient.Visible = True
  End Select

Hope this helps to get you going...



Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV