.source = multipe tables with innner join How

[cedtech23]

I have a form called "frmImmunizations" that has four subforms

frmHepB_Immunization, frmMMR_Immunization, frmTd_Immunization and
frmVaricella_Immunization

they each populate an individual table

frmHepB_Immunization --> tblHepB_Immunization
frmMMR_Immunization --> tblMMR_Immunization
frmTd_Immunization --> tblTd_Immunization
frmVaricella_Immunization --> tblVaricella_Immunization


I want to use a command Button called cmdOK that is located on form "frmImmunizations"
to populate the contents of text boxes and combo boxes that are located in the forms

txtGivenHepB, cboAntiProHepB --> tblHepB_Immunization
txtGivenMMR, cboAntiProMMR --> tblMMR_Immunization
txtGivenTD, cboAntiProTD --> tblTd_Immunization
txtGivenVaricella, cboAntiProVaricella --> tblVaricella_Immunization


Each of these tables have the same Fields which are

PatientID, Given, Due and AntibodyPro


this is my code I used to connect the database

Private Sub cmdOK_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Dim varSelected As Variant


Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

With cn
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .Properties("Data Source") = "C:\Documents and Settings\cspence\Desktop\PTDBVer1\PTDBVer1.mdb"
'  .Properties("User ID") = TheUserID
'  .Properties("Password") = ThePassword
'  .Properties("Persist Security Info") = False
  .Properties("Mode") = adModeShareDenyNone
  .Open
End With


With rs
   .ActiveConnection = cn
   .CursorLocation = adUseServer
   .CursorType = adOpenStatic
   .LockType = adLockOptimistic
   .Source = "SELECT * FROM tblPatientLanguage"
   .Open
End With

End Sub

for the .Source will I have to change this to a join to select all four tables?? If yes I started the SQL statement below but

I am lost on how to inner join for than two table tables. Can someone help me finish the SQL statement and tell me if the

statement I have so far has the correct syntax??

.Source = " SELECT tblHepB_Immunization.Given, tblHepB_Immunization.AntibodyPro, tblMMR_Immunization.Given, 

tblMMR_Immunization.AntibodyPro, tblTd_Immunization.Given, tblTd_Immunization.AntibodyPro, tblVaricella_Immunization.Given, 

tblVaricella_Immunization.AntibodyPro FROM tblHepB_Immunization INNER JOIN tblMMR_Immunization ON 

tblHepB_Immunization.PatientID = tblHepB_Immunization.PatientID INNER JOIN ??

Also will the code below add the values to the correct tables?

rs.AddNew

'Adds values stored in txtGivenHepB and cboAntiProHepB to the tblHepB_Immunization

rs("tblHepB_Immunization.PatientID") = Forms!frmPatientDemographics!frmImmunizations!frmHepB_Immunization!PatientID
rs("tblHepB_Immunization.Given") = Forms!frmImmunizations!frmHepB_Immunization!txtGivenHepB
rs("tblHepB_Immunization.AntibodyPro") = Forms!frmImmunizations!frmHepB_Immunization!cboAntiProHepB.ItemData

'Adds values stored in txtGivenMMR and cboAntiProMMR to the tblMMR_Immunization

rs("tblMMR_Immunization.PatientID") = Forms!frmPatientDemographics!frmMMR_Immunization!frmMMR_Immunization!PatientID
rs("tblMMR_Immunization.Given") = Forms!frmMMR_Immunization!frmMMR_Immunization!txtGivenMMR
rs("tblMMR_Immunization.AntibodyPro") = Forms!frmMMR_Immunization!frmMMR_Immunization!cboAntiProMMR.ItemData

'Adds values stored in txtGivenTD and cboAntiProTD to the tblTd_Immunization

rs("tblTd_Immunization.PatientID") = Forms!frmPatientDemographics!frmTd_Immunization!frmTd_Immunization!PatientID
rs("tblTd_Immunization.Given") = Forms!frmTd_Immunization!frmTd_Immunization!txtGivenTD
rs("tblTd_Immunization.AntibodyPro") = Forms!frmTd_Immunization!frmTd_Immunization!cboAntiProTD.ItemData

'Adds values stored in txtGivenVaricella and cboAntiProVaricella to the tblVaricella_Immunization

rs("tblVaricella_Immunization.PatientID") = 

Forms!frmVaricella_Immunization!frmTd_Immunization!frmVaricella_Immunization!PatientID
rs("tblVaricella_Immunization.Given") = Forms!frmVaricella_Immunization!frmVaricella_Immunization!txtGivenVaricella
rs("tblVaricella_Immunization.AntibodyPro") = 

Forms!frmVaricella_Immunization!frmVaricella_Immunization!cboAntiProVaricella.ItemData
		    		
rs.Update

I attached the database that has the form. P.S. access the Form through frmPatientDemographics then click on Immunizations

hyperlink