Member Avatar for Krs13

Hi,

In vb i use Data Form Wizard and connect access database and create a form which automatically creates update,add,delete,etc buttons.

Now what i need is when i open the form and enter (say some student id)in a field then the next field is the student name which should be automatically filled based on the Id.

Can somebody help me on this please.

Krs

  • 2 Contributors
  • 5 Replies
  • 179 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by AndreRet

Dani AI

Generated

This thread asks how to auto-fill the student name when an ID is entered. described the Data Form Wizard output; correctly pointed out that the built-in data control / recordset search can work for simple cases. For a more robust, maintainable solution that avoids moving the main bound recordset and gives clearer error handling, run a short SELECT against the Access file from the ID control’s AfterUpdate event and populate the name field programmatically.

Example (VB6, requires a reference to "Microsoft ActiveX Data Objects x.x Library"):

Private Sub txtStudentID_AfterUpdate()
    Dim cn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rs As ADODB.Recordset

    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Path\Students.mdb;"

    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = cn
        .CommandText = "SELECT StudentName FROM Students WHERE StudentID = ?"
        .CommandType = adCmdText
        .Parameters.Append .CreateParameter("pID", adVarChar, adParamInput, 50, Trim$(txtStudentID.Text))
        Set rs = .Execute
    End With

    If Not rs.EOF Then
        txtStudentName.Text = rs!StudentName
    Else
        txtStudentName.Text = ""
    End If

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

Notes and troubleshooting

  • Place the code in AfterUpdate (fires only when the value changed); LostFocus fires more often and can cause extra queries.
  • If the form controls are bound by the Data Form Wizard, either unbind the name textbox to fill it manually or keep them bound and reposition the wizard-created recordset instead of writing to bound controls directly. ’s data-control approach is fine when staying fully bound.
  • Use the ACE provider string for .accdb files ("Provider=Microsoft.ACE.OLEDB.12.0;...").
  • Validate/trim the ID before querying; ensure the ID field is unique to avoid ambiguous results.
  • Confirm the Access file path and ADO reference; enable error handling around DB calls to surface connection or SQL issues.

This approach keeps the main form recordset stable, gives clearer control over missing/duplicate IDs, and is easier to extend (validation, partial lookup, autocomplete) than relying solely on the generated data-control behavior.

Recommended Answers

All 5 Replies

Member Avatar for AndreRet

I am assuming that you are using a data control with the form? If so then you would search as follow -

datBooks.Recordset.FindFirst "'MySearchCriteria'" 'Notice the hyphens ', this for finding the first record if there might be more than one, or -
datMembers.Recordset.FindNext "'MySearchCriteria'" to go to the next record

Read more on this on -

http://www.vb6.us/tutorials/database-access-vb6-data-control-tutorial

Member Avatar for Krs13

Am using data form wizard to create forms automatically by Vb with code.

What i need is to retrieve the value of the textbox based on the previous textbox value.(ie)if i enter student id then the next textbox should automatically retrieve the name from access database and display it rather than typing the name manually by user.

Member Avatar for Krs13

I am assuming that you are using a data control with the form? If so then you would search as follow -

datBooks.Recordset.FindFirst "'MySearchCriteria'" 'Notice the hyphens ', this for finding the first record if there might be more than one, or -
datMembers.Recordset.FindNext "'MySearchCriteria'" to go to the next record

Read more on this on -

http://www.vb6.us/tutorials/database-access-vb6-data-control-tutorial

Am using data form wizard to create forms automatically by Vb with code.

What i need is to retrieve the value of the textbox based on the previous textbox value.(ie)if i enter student id then the next textbox should automatically retrieve the name from access database and display it rather than typing the name manually by user.

Member Avatar for AndreRet

Set your textbox datasource to your data control. Set the datafield property to your student name or surname or whatever field you have in the table. In your text search box's Lost Focus event you will add the code I supplied above for search. Once a record is found, the textboxes set to your data control will be automatically filled with the respective data.

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.