Database problems

[Robert Rieth]

I have had a number of errors doing SQL programming with in my Visual Basic
Application. This is the latest one and it seems to have finaly stoped me.
If anyone could give me the solution as to what is wrong I would be very
greatful. Here is the Code to both the Application and Module that uses
the Database.
APPLICATION:

Private Sub SaveData_Click()
Dim con As adodb.Connection
Set con = New adodb.Connection
con.Open "dsn=LifeLinePD"
Dim rstAdmission As adodb.Recordset
Set rstAdmission = New adodb.Recordset
Dim SQLstr As String
Dim Last_Name As String, First_Name As String, Middle_Initial As String,
PhoneNum As String
Dim PAddress As String, RCity As String, RState As String, RZip As Integer,
RMonth As String
Dim RDate As Integer, RYear As Integer, SSNumber As Integer, MaritalStat
As String
Dim ReligousStat As String, Church_Name As String, CCity As String, RFuneralHome
As String
Dim RRace As String, ADate As Date, AdmitText As String, AdmitAdressTxt
As String, AdmitCityTxt As String
Dim AdmitCombo As String, OMRecordNum As Integer, RHospitalPref As String,
RVAClaim As String
Dim RMedicadeNo As String, RPrivateI As String, AllergieList As ListBoxConstants
Dim PLastName As String, PFirstName As String, PRelation As String, ELName
As String
Dim EFName As String, EHCA As String, EDREP As String, ERelation As String
Dim EAddress As String, EHPhone As String, EWPhone As String, ECPager
As String
Dim ECellPhone As String, NOK1LName As String, NOK1FName As String
Dim NOK1HCA As String, NOK1DREP As String, NOK1Rel As String, NOK1Address
As String
Dim NOK1HPhone As String, NOK1WPhone As String, NOK1PNumber As String,
NOK1Cell As String
Dim NOK2LName As String, NOK2FName As String, NOK2Rel As String, NOK2Address
As String
Dim NOK2HPhone As String, NOK2WPhone As String, NOK2PNumber As String,
NOK2Cell As String
Dim AttPhyLN As String, AttPhyFN As String, AttPhyAddress, AttPhyHPhone,
AttPhyWPhone
Dim AttPhyPNum As String, AttPhyCNumber, AltPhyLN As String, AltPhyFN
As String
Dim AltPhyAddress As String, AltPhyHPhone As String, AltPhyWPhone, AltPhyPNumber
As String
Dim AltPhyCNumber As String, PPodiatrist As String, PDentist As String,
SQLQUERY As QueryDef, LifeLinePD As Database
Set QueryDef
rstAdmission.Open "Select * from LifeLinePD WHERE LastName = '123456789'",
con, adOpenStatic
With Admission
SQLstr = "INSERT INTO LifeLinePD (LastName, FirstName, MiddleInitial,
" & _
"PPhoneNumber, Address, City, State, Zip) " & " values ('" & Last_Name
& _
"','" & First_Name & "','" & Middle_Initial & "','" & PhoneNum &
"','" & _
PAddress & "','" & RCity & "','" & RState & "','" & RZip & "')"
SQLpro SQLstr, rstAdmission
SQLstr = "INSERT INTO LifeLinePD(BDay,BMonth,BYear,SocialSecurityNumber,
" & _
"MaritalStatus, ReligousStatus) " & " values ('" & RMonth & "','"
& RDate & _
"','" & RYear & "','" & SSNumber & "','" & MaritalStat & "','" &
ReligousStat & "',"
SQLstr = "INSERT INTO LifeLinePD (ChurchName,ChurchCity,FuneralHome,Race,
" & _
"AdvancedDirectives) " & " values ('" & Church_Name & "','" & CCity
& "','" & RFuneralHome & _
"','" & RRace & "')"
SQLpro SQLstr, rstAdmission
SQLstr = "INSERT INTO LifeLinePD.LifeLinePD (AdmitDate,AdmitFrom,AdmitAddress,
" & _
"AdmitCity) " & " values ('" & ADate & "','" & AdmitText & "','"
& AdmitAdressTxt & _
"','" & AdmitCityTxt & "')"
SQLpro SQLstr, rstAdmission
SQLstr = "INSERT INTO LifeLinePD (AdmitState,MedicalRecordNum,HospitalPrefrence,
" & _
"VAClaaimNum,MedicadeNum) " & " values ('" & AdmitCombo & "','" &
OMRecordNum & _
"','" & RHospitalPref & "','" & RVAClaim & "','" & RMedicadeNo &
"')"
SQLstr = "INSERT INTO LifeLinePD (Private,Allergies) " & " values
('" & RPrivateI & "','" & AllergieList & "')"
SQLpro SQLstr, rstAdmission
SQLstr = "INSERT INTO LifeLinePD (LPaymentPerson,FPaymentPerson,PatientRelation,
" & _
"ECLastName,ECFirstName) " & " values ('" & PLastName & "','" & PFirstName
& _
"','" & PRelation & "','" & ELName & "','" & EFName & "')"
SQLpro SQLstr, rstAdmission
SQLstr = "INSERT INTO LifeLinePD (ECHCA,ECDRep,ECRelation,ECAddress
" & _
"ECHomePhone,ECWorkNumber) " & " values ('" & EHCA & "','" & EDREP
& _
"','" & ERelation & "','" & EAddress & "','" & EHPhone & "','" &
EWPhone & "')"
SQLpro SQLstr, rstAdmission
SQLstr = "INSERT INTO LifeLinePD (ECPagerNumber,ECCellPhone,NextofKin1LN,NextofKin1FN
" & _
"NextofKin1HPA,NextofKin1DRep) " & " values ('" & ECPager & "','"
& ECellPhone & _
"','" & NOK1LName & "','" & NOK1FName & "','" & NOK1HCA & "','" &
NOK1DREP & "')"
SQLpro SQLstr, rstAdmission
SQLstr = "INSERT INTO LifeLinePD (NextofKin1Relation,NextofKin1Address,NextofKin1HPhone,NextofKin1WPhone
" & _
"NextofKin1PNumber,NextofKin1CNumber) " & " values ('" & NOK1Rel
& "','" & NOK1Address & _
"','" & NOK1HPhone & "','" & NOK1WPhone & "','" & NOK1PNumber & "','"
& NOK1Cell & "')"
SQLpro SQLstr, rstAdmission
SQLstr = "INSERT INTO LifeLinePD (NextofKin2LN,NextofKin2FN,NextofKin1HPA,NextofKin2DRep
" & _
"NextofKin2Relation,NextofKin2Address) " & " values ('" & NOK2Rel
& "','" & NOK2Address & _
"','" & NOK2HPhone & "','" & NOK2WPhone & "','" & NOK2PNumber & "','"
& NOK2Cell & "')"
SQLpro SQLstr, rstAdmission
SQLstr = "INSERT INTO LifeLinePD (AttPhyciaianLN,AttPhyciaianFN,AttPhyciaianAddress,AttPhyciaianHPhone
" & _
"AttPhyciaianWPhone,AttPhyciaianPNumber) " & " values ('" & AttPhyLN
& "','" & AttPhyFN & _
"','" & AttPhyAddress & "','" & AttPhyHPhone & "','" & AttPhyWPhone
& "','" & AttPhyPNumber & "')"
SQLpro SQLstr, rstAdmission
SQLstr = "INSERT INTO LifeLinePD (AttPhyciaianCNumber,AltPhyciaianLN,AltPhyciaianFN,AltPhyciaianAddress
" & _
"AltPhyciaianHPhone,AltPhycciaianCNumber) " & " values ('" & AttPhyCNumber
& "','" & AltPhyLN & _
"','" & AltPhyFN & "','" & AltPhyAddress & "','" & AltPhyHPhone &
"','" & AltPhyWPhone & "')"
SQLpro SQLstr, rstAdmission
SQLstr = "INSERT INTO LifeLinePD (AltPhyciaianPNumber,AltPhycciaianCNumber,PodiatristName,DentistName)
" & _
" values ('" & AltPhyPNumber & "','" & AltPhyCNumber & "'," & PPodiatrist
& "','" & PDentist & "')"
SQLpro SQLstr, rstAdmission
End With
LifeLinePD.Execute SQLQUERY
con.Close
Set con = Nothing

End Sub

MODULE:

Option Explicit
Public Sub SQLpro(SQLstr As String, Record_Set As adodb.Recordset)
Dim con As adodb.Connection
Set con = New adodb.Connection

con.Open "dsn=LifeLinePD"

Dim SQLCmd As New adodb.Command
Set SQLCmd = New adodb.Command 'initialize the command object
With SQLCmd
Set .ActiveConnection = con 'set the active connection
.CommandType = adCmdText 'set command type as text (SQL)
.CommandText = SQLstr 'set the command = the passed SQL string
ADOCmd.Execute 'Execute the command
End With
con.Close
Set con = Nothing
Record_Set.Close
Set Record_Set = Nothing
'SQLCmd.Close
'Set SQLCmd = Nothing
End Sub

The error that I am getting is...
Compile error:
user-defined type not defined

It highlights this part of the Code in the Application
SQLQUERY As QueryDef

Someone told me I didn't need to Dim it, but VB disagrees.
What can I do to fix the error and finaly get the Application to start saving
data to my LifeLinePD Database.

Any help would be appreciated. : )
Thank You.

[Andrew Grillage]

You must use Dim, Private, Public or Static when declaring variables. There
is no avoiding it.

--
Andrew Grillage
http://vbdata.iwarp.com


"Robert Rieth" <paparieth1@juno.com> wrote in message
news:38f0f19e$1@news.devx.com...
>
> I have had a number of errors doing SQL programming with in my Visual
Basic
> Application. This is the latest one and it seems to have finaly stoped
me.
> If anyone could give me the solution as to what is wrong I would be very
> greatful. Here is the Code to both the Application and Module that uses
> the Database.
> APPLICATION:
>
> Private Sub SaveData_Click()
> Dim con As adodb.Connection
> Set con = New adodb.Connection
> con.Open "dsn=LifeLinePD"
> Dim rstAdmission As adodb.Recordset
> Set rstAdmission = New adodb.Recordset
> Dim SQLstr As String
> Dim Last_Name As String, First_Name As String, Middle_Initial As
String,
> PhoneNum As String
> Dim PAddress As String, RCity As String, RState As String, RZip As
Integer,
> RMonth As String
> Dim RDate As Integer, RYear As Integer, SSNumber As Integer,
MaritalStat
> As String
> Dim ReligousStat As String, Church_Name As String, CCity As String,
RFuneralHome
> As String
> Dim RRace As String, ADate As Date, AdmitText As String,
AdmitAdressTxt
> As String, AdmitCityTxt As String
> Dim AdmitCombo As String, OMRecordNum As Integer, RHospitalPref As
String,
> RVAClaim As String
> Dim RMedicadeNo As String, RPrivateI As String, AllergieList As
ListBoxConstants
> Dim PLastName As String, PFirstName As String, PRelation As String,
ELName
> As String
> Dim EFName As String, EHCA As String, EDREP As String, ERelation As
String
> Dim EAddress As String, EHPhone As String, EWPhone As String, ECPager
> As String
> Dim ECellPhone As String, NOK1LName As String, NOK1FName As String
> Dim NOK1HCA As String, NOK1DREP As String, NOK1Rel As String,
NOK1Address
> As String
> Dim NOK1HPhone As String, NOK1WPhone As String, NOK1PNumber As String,
> NOK1Cell As String
> Dim NOK2LName As String, NOK2FName As String, NOK2Rel As String,
NOK2Address
> As String
> Dim NOK2HPhone As String, NOK2WPhone As String, NOK2PNumber As String,
> NOK2Cell As String
> Dim AttPhyLN As String, AttPhyFN As String, AttPhyAddress,
AttPhyHPhone,
> AttPhyWPhone
> Dim AttPhyPNum As String, AttPhyCNumber, AltPhyLN As String, AltPhyFN
> As String
> Dim AltPhyAddress As String, AltPhyHPhone As String, AltPhyWPhone,
AltPhyPNumber
> As String
> Dim AltPhyCNumber As String, PPodiatrist As String, PDentist As
String,
> SQLQUERY As QueryDef, LifeLinePD As Database
> Set QueryDef
> rstAdmission.Open "Select * from LifeLinePD WHERE LastName =
'123456789'",
> con, adOpenStatic
> With Admission
> SQLstr = "INSERT INTO LifeLinePD (LastName, FirstName,
MiddleInitial,
> " & _
> "PPhoneNumber, Address, City, State, Zip) " & " values ('" &
Last_Name
> & _
> "','" & First_Name & "','" & Middle_Initial & "','" & PhoneNum &
> "','" & _
> PAddress & "','" & RCity & "','" & RState & "','" & RZip & "')"
> SQLpro SQLstr, rstAdmission
> SQLstr = "INSERT INTO
LifeLinePD(BDay,BMonth,BYear,SocialSecurityNumber,
> " & _
> "MaritalStatus, ReligousStatus) " & " values ('" & RMonth & "','"
> & RDate & _
> "','" & RYear & "','" & SSNumber & "','" & MaritalStat & "','" &
> ReligousStat & "',"
> SQLstr = "INSERT INTO LifeLinePD
(ChurchName,ChurchCity,FuneralHome,Race,
> " & _
> "AdvancedDirectives) " & " values ('" & Church_Name & "','" &
CCity
> & "','" & RFuneralHome & _
> "','" & RRace & "')"
> SQLpro SQLstr, rstAdmission
> SQLstr = "INSERT INTO LifeLinePD.LifeLinePD
(AdmitDate,AdmitFrom,AdmitAddress,
> " & _
> "AdmitCity) " & " values ('" & ADate & "','" & AdmitText & "','"
> & AdmitAdressTxt & _
> "','" & AdmitCityTxt & "')"
> SQLpro SQLstr, rstAdmission
> SQLstr = "INSERT INTO LifeLinePD
(AdmitState,MedicalRecordNum,HospitalPrefrence,
> " & _
> "VAClaaimNum,MedicadeNum) " & " values ('" & AdmitCombo & "','" &
> OMRecordNum & _
> "','" & RHospitalPref & "','" & RVAClaim & "','" & RMedicadeNo &
> "')"
> SQLstr = "INSERT INTO LifeLinePD (Private,Allergies) " & " values
> ('" & RPrivateI & "','" & AllergieList & "')"
> SQLpro SQLstr, rstAdmission
> SQLstr = "INSERT INTO LifeLinePD
(LPaymentPerson,FPaymentPerson,PatientRelation,
> " & _
> "ECLastName,ECFirstName) " & " values ('" & PLastName & "','" &
PFirstName
> & _
> "','" & PRelation & "','" & ELName & "','" & EFName & "')"
> SQLpro SQLstr, rstAdmission
> SQLstr = "INSERT INTO LifeLinePD
(ECHCA,ECDRep,ECRelation,ECAddress
> " & _
> "ECHomePhone,ECWorkNumber) " & " values ('" & EHCA & "','" & EDREP
> & _
> "','" & ERelation & "','" & EAddress & "','" & EHPhone & "','" &
> EWPhone & "')"
> SQLpro SQLstr, rstAdmission
> SQLstr = "INSERT INTO LifeLinePD
(ECPagerNumber,ECCellPhone,NextofKin1LN,NextofKin1FN
> " & _
> "NextofKin1HPA,NextofKin1DRep) " & " values ('" & ECPager & "','"
> & ECellPhone & _
> "','" & NOK1LName & "','" & NOK1FName & "','" & NOK1HCA & "','" &
> NOK1DREP & "')"
> SQLpro SQLstr, rstAdmission
> SQLstr = "INSERT INTO LifeLinePD
(NextofKin1Relation,NextofKin1Address,NextofKin1HPhone,NextofKin1WPhone
> " & _
> "NextofKin1PNumber,NextofKin1CNumber) " & " values ('" & NOK1Rel
> & "','" & NOK1Address & _
> "','" & NOK1HPhone & "','" & NOK1WPhone & "','" & NOK1PNumber &
"','"
> & NOK1Cell & "')"
> SQLpro SQLstr, rstAdmission
> SQLstr = "INSERT INTO LifeLinePD
(NextofKin2LN,NextofKin2FN,NextofKin1HPA,NextofKin2DRep
> " & _
> "NextofKin2Relation,NextofKin2Address) " & " values ('" & NOK2Rel
> & "','" & NOK2Address & _
> "','" & NOK2HPhone & "','" & NOK2WPhone & "','" & NOK2PNumber &
"','"
> & NOK2Cell & "')"
> SQLpro SQLstr, rstAdmission
> SQLstr = "INSERT INTO LifeLinePD
(AttPhyciaianLN,AttPhyciaianFN,AttPhyciaianAddress,AttPhyciaianHPhone
> " & _
> "AttPhyciaianWPhone,AttPhyciaianPNumber) " & " values ('" &
AttPhyLN
> & "','" & AttPhyFN & _
> "','" & AttPhyAddress & "','" & AttPhyHPhone & "','" &
AttPhyWPhone
> & "','" & AttPhyPNumber & "')"
> SQLpro SQLstr, rstAdmission
> SQLstr = "INSERT INTO LifeLinePD
(AttPhyciaianCNumber,AltPhyciaianLN,AltPhyciaianFN,AltPhyciaianAddress
> " & _
> "AltPhyciaianHPhone,AltPhycciaianCNumber) " & " values ('" &
AttPhyCNumber
> & "','" & AltPhyLN & _
> "','" & AltPhyFN & "','" & AltPhyAddress & "','" & AltPhyHPhone &
> "','" & AltPhyWPhone & "')"
> SQLpro SQLstr, rstAdmission
> SQLstr = "INSERT INTO LifeLinePD
(AltPhyciaianPNumber,AltPhycciaianCNumber,PodiatristName,DentistName)
> " & _
> " values ('" & AltPhyPNumber & "','" & AltPhyCNumber & "'," &
PPodiatrist
> & "','" & PDentist & "')"
> SQLpro SQLstr, rstAdmission
> End With
> LifeLinePD.Execute SQLQUERY
> con.Close
> Set con = Nothing
>
> End Sub
>
> MODULE:
>
> Option Explicit
> Public Sub SQLpro(SQLstr As String, Record_Set As adodb.Recordset)
> Dim con As adodb.Connection
> Set con = New adodb.Connection
>
> con.Open "dsn=LifeLinePD"
>
> Dim SQLCmd As New adodb.Command
> Set SQLCmd = New adodb.Command 'initialize the command object
> With SQLCmd
> Set .ActiveConnection = con 'set the active connection
> CommandType = adCmdText 'set command type as text (SQL)
> CommandText = SQLstr 'set the command = the passed SQL string
> ADOCmd.Execute 'Execute the command
> End With
> con.Close
> Set con = Nothing
> Record_Set.Close
> Set Record_Set = Nothing
> 'SQLCmd.Close
> 'Set SQLCmd = Nothing
> End Sub
>
> The error that I am getting is...
> Compile error:
> user-defined type not defined
>
> It highlights this part of the Code in the Application
> SQLQUERY As QueryDef
>
> Someone told me I didn't need to Dim it, but VB disagrees.
> What can I do to fix the error and finaly get the Application to start
saving
> data to my LifeLinePD Database.
>
> Any help would be appreciated. : )
> Thank You.