Member Avatar for ashwin149

Hi.I am working on a project(patient management system).I want a code for this.
Once I enter the name of the patient whose details want to know, I want the next form to be loaded and those specific details of that particular patient(already stored in a database) to be displayed in a text box.
I use Vb6 and MS access

  • 3 Contributors
  • 6 Replies
  • 95 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by AndreRet

Dani AI

Generated

Brief summary and a safer pattern to use here. wants to type a patient/food name in VB6 and show that record on a second form. demonstrated ADO recordset use and called out the need to treat text correctly and suggested the Data Control. The key points missing in earlier replies are: avoid string concatenation for SQL, always check for no-results, prefer passing a key (ID) to the details form, and close/clean ADO objects.

Example ADO workflow (use parameterized command, then hand the recordset to the detail form):

' In search form
If Trim(txtSearch.Text) = "" Then
  MsgBox "Please enter a name": Exit Sub
End If

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset

cn.Open myConnString

With cmd
  .ActiveConnection = cn
  .CommandText = "SELECT PatientID, FullName, Age, Room FROM Patients WHERE FullName = ?"
  .CommandType = adCmdText
  .Parameters.Append .CreateParameter("pName", adVarChar, adParamInput, 100, Trim(txtSearch.Text))
  Set rs = .Execute
End With

If rs.EOF Then
  MsgBox "No match found"
Else
  frmDetails.LoadFromRecord rs
  frmDetails.Show vbModal
End If

rs.Close: cn.Close
Set rs = Nothing: Set cmd = Nothing: Set cn = Nothing

Populate the details form via a public routine so UI code stays in one place:

' In frmDetails
Public Sub LoadFromRecord(rs As ADODB.Recordset)
  If rs.EOF Then Exit Sub
  If IsNull(rs!FullName) Then txtFullName.Text = "" Else txtFullName.Text = CStr(rs!FullName)
  If IsNull(rs!Age) Then txtAge.Text = "" Else txtAge.Text = CStr(rs!Age)
  If IsNull(rs!Room) Then txtRoom.Text = "" Else txtRoom.Text = CStr(rs!Room)
End Sub

Quick tips:

  • Add reference to "Microsoft ActiveX Data Objects x.x" in Project->References.
  • Use parameters to avoid problems with apostrophes and SQL injection.
  • Prefer searching by a unique numeric ID (store/lookup name → get ID → open details by ID).
  • Always check rs.EOF before reading fields; close and set objects to Nothing.
  • If you must use the VB6 Data control, bind the control and use the Recordset search/filter methods instead of building SQL strings manually.

Recommended Answers

All 6 Replies

Member Avatar for abelingaw

Try this:

Dim con as new ADODB.Connection
Dim rs As New ADODB.Recordset 


If txtSEARCH.Text = vbNullString Then
  MsgBox " The search field is empty.", vbExclamation 	'Change message if you want
  txtSERACH.SetFocus
End If

With rs

    If .State = adStateOpen Then .Close

    'TABLENAME - replace with your table name 
    'txtSEARCH - replace: name of your textbox where you enter the
    'patient name of the patient that records you want 
    
    .Open "Select * from TABLENAME where NAME =" & txtSEARCH.Text & ";", con, adOpenKeyset, adLockPessimistic
    
    
    Do While .EOF = False
    
    With Form5		'Change Form5 to the name of the form which to be loaded on click
    

    'Change "NAME" and "AGE" with your own Table Column names
    'from the table where the records are located 
    'Arrange them according to where textbox the records would be displayed

    .txtNAME.Text = rs.Fields("NAME").Value
    .txtAGE.Text = rs.Fields("AGE").Value
    .txtROOM.Text = rs.Fields("Roomnumber").Value
           

    End With

   .MoveNext
   Loop
  
  .Close

Form5.Show	'Change Form5 to the name of the form which to be loaded on click

Put this code on a Command Button - Click Event

Hope this helps :)

Member Avatar for AndreRet

Abe, your statement is incorrect. The returned data is a string so you have to enclose it in hyphens -

.Open "Select * from TABLENAME where NAME = '" & txtSEARCH.Text & "'", con, adOpenKeyset, adLockPessimistic

:)

Member Avatar for abelingaw

Oh, didn't see that one.:$

Member Avatar for AndreRet

, please mark this thread as solved found at the bottom of this page, thanks.:)

It has been open for some time now.

Member Avatar for ashwin149

Hi.
I need another code where once I enter the name of a food item, I want the details of that food item to be displayed in a text box(Details stored in a database).THE PROBLEM IS I have to use a data control or do it by pure coding (I'm not allowed to use ADO.net)Pls give me the code for both

Member Avatar for AndreRet

I then suggest that you have a look at THIS link. It gives you everything you need on the data control.

The code will be something like -

Text1.Text = "Apple"

datFood.Recordset.FindFirst "YourFoodFieldNameHere = " & Text1.Text
commented: I guess no one in your "home" forum gives you rep.... you've earned it. +6
commented: Thank you for helping us fight spam! +16
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.