I am trying to change my code from storing data to excel spreadsheets to send to access database. This is the code
If qty.Value <> "" Then
PatientDetails(1, 1) = LastRow - 1
zh.Cells(LastRow, 1).Resize(, 22) = PatientDetails
zh.Cells(LastRow, 23) = servees.Value
zh.Cells(LastRow, 25) = qty.Value
zh.Cells(LastRow, 27) = cost.Value
zh.Cells(LastRow, 29) = ThisWorkbook.Sheets("Support").Range("AM1").Value
zh.Cells(LastRow, 30) = [Text(Now(), "DD-MM-YY HH:MM:SS")]
LastRow = LastRow + 1
End If
'Next
im only posting the code that relates to the help i need, zh is the sheet,LastRow is supposed to find the next empty row, this section of code runs in a loop, NOW "PatientDetails" is an array with data used to populate column1 to column22.
In changing this code to be able to send to access, since i used zh.Cells(LastRow, 1).Resize(, 22) = PatientDetails to populate the first 22 columns of my spreadsheet. If i want to send "PatientDetails" to access, how do i populate access fields/columns with "PatientDetails"
This is the array
Private Function PatientDetails() As Variant
Dim ar(1 To 1, 1 To 22)
With PrivGNI
ar(1, 1) = 0
ar(1, 2) = "GNI HMO (PRIVATE)"
ar(1, 3) = .Txt_EnrolleeNoGNI.Value
ar(1, 4) = "Nil"
ar(1, 5) = Private_PreEntryForm1.cmb_ReferFromPef2.Value
ar(1, 6) = .Txt_EnrolleeNameGNI.Value
ar(1, 7) = .Txt_AuthorizationCode.Value
ar(1, 8) = Private_PreEntryForm1.Txt_AuthorisingOfficerPEF.Value
If Private_PreEntryForm1.Opt_OutpatientPef = True Then
ar(1, 9) = "OUTPATIENT"
Else
ar(1, 9) = "Nil"
End If
If Private_PreEntryForm1.Opt_InpatientPef2 = True Then
ar(1, 9) = "INPATIENT"
Else
ar(1, 9) = "Nil"
End If
ar(1, 10) = Private_PreEntryForm1.Cmb_PatientPlan.Value
ar(1, 11) = "PRIVATE HMO"
ar(1, 12) = .Txt_HmoCodeGNI.Value
ar(1, 13) = .txt_DateOfserv2.Value
ar(1, 14) = .Txt_DateOfAdmGNI.Value
ar(1, 15) = .Txt_DateOfDisGNI.Value
ar(1, 16) = Private_PreEntryForm1.cmd_Genderpef2.Value
ar(1, 17) = .Txt_AgeGNI.Value
ar(1, 18) = .Txt_SexGNI
ar(1, 19) = .txt_drsNameGNI
ar(1, 20) = "Nil"
ar(1, 21) = .Txt_DiagnosisGNI.Value
ar(1, 22) = .txt_PhoneGNI.Value
End With
PatientDetails = ar
End Function
if i may add, does it make any sense to say "Recordset.Fields(1,2,3,4,5,6 to 22).Value = PatientDetails"
Its only this line of code i need help on. The ADO property or method that can refer to multiple columns/fields alongside the "fields" object to Send the array to access
