Hi ?
I might not express the problem as I should but pls bare with me, here it goes
I'm trying to create a user data entry form to capture services per patient and save entry to my spreadsheet (database)
The patient details gets populated automatically without user entry (prior to a pre-entry form, created in the app)
In the database, There's a single column to capture every service entered
So I decided that when a user enters multiple services and clicks on save
The spreadsheet (database) will capture the patient details per every service
That is, on one row .... there will be patient detail and then a single service
On the second row... the details will repeat but the service will be changed based on the second service provided by the user
And so on
Problem
(I don't know how to loop through the empty textboxes to identify the number of rows patient details will be repeated when user clicks save.
If I get that, then I'll work my way to populate the services by each count of rows)
Here's the code:
Ps: returned an error on "for each ccont In Me.Controls"
I might not express the problem as I should but pls bare with me, here it goes
I'm trying to create a user data entry form to capture services per patient and save entry to my spreadsheet (database)
The patient details gets populated automatically without user entry (prior to a pre-entry form, created in the app)
In the database, There's a single column to capture every service entered
So I decided that when a user enters multiple services and clicks on save
The spreadsheet (database) will capture the patient details per every service
That is, on one row .... there will be patient detail and then a single service
On the second row... the details will repeat but the service will be changed based on the second service provided by the user
And so on
Problem
(I don't know how to loop through the empty textboxes to identify the number of rows patient details will be repeated when user clicks save.
If I get that, then I'll work my way to populate the services by each count of rows)
Here's the code:
Ps: returned an error on "for each ccont In Me.Controls"
VBA Code:
Private Sub Save_Click()
Dim zh As Worksheet
Dim LaastRow As Long
Dim x As Integer
Dim ccont As Controls
Dim y As Long
Set zh = ThisWorkbook.Sheets("PublicDatabase")
LaastRow = [Counta(PublicDatabase!A:A)] + 1
For Each ccont In Me.Controls
If Me.txt_QtyAccomodation.Value = "" Or _
Me.txt_QtyConsultation.Value = "" Or _
Me.txt_QtyHaematology.Value = "" Or _
Me.txt_QtyAccomodation.Value = "" Or _
Me.txt_QtyHistopathology.Value = "" Or _
Me.txt_QtyNursingcare.Value = "" Or _
Me.txt_QtyOthers1.Value = "" Or _
Me.txt_QtyOthers2.Value = "" Or _
Me.txt_QtyMicrobiology.Value = "" Or _
Me.txt_QtyReviews.Value = "" Then
Exit For
Else
y = y + 1
End If
For x = 1 To LaastRow + y
With zh
.Cells(LaastRow, 1) = LaastRow - 1
.Cells(LaastRow, 2) = "DEFENCE HEALTH MAINTENANCE LTD (PUBLIC)"
.Cells(LaastRow, 3) = PubDefense.Txt_NameOfpDEFENSE.Value
.Cells(LaastRow, 4) = "Nil"
.Cells(LaastRow, 5) = PubDefense.Txt_RefferrinProviderDefense.Value
.Cells(LaastRow, 6) = PubDefense.Txt_NHISNoDefense.Value
.Cells(LaastRow, 7) = PubDefense.Txt_AuthorizCodeDefense.Value
.Cells(LaastRow, 8) = PubDefense.txt_HmoCodeDefense.Value
.Cells(LaastRow, 9) = PubDefense.Txt_DateOftreatmentDefense.Value
.Cells(LaastRow, 10) = PubDefense.Txt_DateOFadmDefense.Value
.Cells(LaastRow, 11) = PubDefense.Txt_DateOFdisDefense.Value
.Cells(LaastRow, 12) = "Nil"
.Cells(LaastRow, 13) = "Nil"
.Cells(LaastRow, 14) = "Nil"
.Cells(LaastRow, 15) = "Nil"
.Cells(LaastRow, 16) = "Nil"
.Cells(LaastRow, 17) = PubDefense.Txt_DiagnosisDefense.Value
.Cells(LaastRow, 18) = PubDefense.Txt_PatAddressDEfense.Value
End With
Next x
Next ccont
MsgBox "entered"
End Sub
Last edited by a moderator: