Hi All,

I have an excel userform which is connected to access database, I want to
auto populate certain fields based on what I input in one textbox.

For eg: If I input Students Id, I want Students name and students phone to
populate in the userform.

So far I have got the form to populate the fields from the database, BUT it
only populates the first entry from the database.
No matter what I put in the student ID textbox it always populates detailf
form the first row only.

Here is the code I have so far.

Private Sub StudentNo_AfterUpdate()

Dim cnt As Object, rst As Object, strSQL As String

Set cnt = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")

strSQL = "SELECT Student_Name, Student_Phone FROM Student_Table WHERE
Student_No = " & Me.StudentNo.Value

cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=C:\Documents\Students_DB.accdb; Jet OLEDB:Database
Password=mystudents; "

rst.Open "Student_Table", cnt, 1, 3, 2

Me.StudentName.Value = rst.Fields(7)
Me.StudentPhone.Value = rst.Fields(9)

Set rst = Nothing
Set cnt = Nothing

End Sub

Thanks in advance

Try this

rst.Open strSQL ,cnt, 1, 3, 2


"sam" wrote:

Hi All,

I have an excel userform which is connected to access database, I want to
auto populate certain fields based on what I input in one textbox.

For eg: If I input Students Id, I want Students name and students phone to
populate in the userform.

So far I have got the form to populate the fields from the database, BUT it
only populates the first entry from the database.
No matter what I put in the student ID textbox it always populates detailf
form the first row only.

Here is the code I have so far.

Private Sub StudentNo_AfterUpdate()

Dim cnt As Object, rst As Object, strSQL As String

Set cnt = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")

strSQL = "SELECT Student_Name, Student_Phone FROM Student_Table WHERE
Student_No = " & Me.StudentNo.Value

cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=C:\Documents\Students_DB.accdb; Jet OLEDB:Database
Password=mystudents; "

rst.Open "Student_Table", cnt, 1, 3, 2

Me.StudentName.Value = rst.Fields(7)
Me.StudentPhone.Value = rst.Fields(9)

Set rst = Nothing
Set cnt = Nothing

End Sub

Thanks in advance

Thanks for helping mike,

When i change it to "rst.Open strSQL ,cnt, 1, 3, 2"

I get an error: "Syntax error in FROM clause"
on the new line "rst.Open strSQL ,cnt, 1, 3, 2"


"Mike" wrote:

Try this

rst.Open strSQL ,cnt, 1, 3, 2


"sam" wrote:

Hi All,

I have an excel userform which is connected to access database, I want to
auto populate certain fields based on what I input in one textbox.

For eg: If I input Students Id, I want Students name and students phone to
populate in the userform.

So far I have got the form to populate the fields from the database, BUT it
only populates the first entry from the database.
No matter what I put in the student ID textbox it always populates detailf
form the first row only.

Here is the code I have so far.

Private Sub StudentNo_AfterUpdate()

Dim cnt As Object, rst As Object, strSQL As String

Set cnt = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")

strSQL = "SELECT Student_Name, Student_Phone FROM Student_Table WHERE
Student_No = " & Me.StudentNo.Value

cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=C:\Documents\Students_DB.accdb; Jet OLEDB:Database
Password=mystudents; "

rst.Open "Student_Table", cnt, 1, 3, 2

Me.StudentName.Value = rst.Fields(7)
Me.StudentPhone.Value = rst.Fields(9)

Set rst = Nothing
Set cnt = Nothing

End Sub

Thanks in advance

Some problem in strSQL: what's in there ?

Tim

"sam" wrote in message
...
Thanks for helping mike,

When i change it to "rst.Open strSQL ,cnt, 1, 3, 2"

I get an error: "Syntax error in FROM clause"
on the new line "rst.Open strSQL ,cnt, 1, 3, 2"


"Mike" wrote:

Try this

rst.Open strSQL ,cnt, 1, 3, 2


"sam" wrote:

Hi All,

I have an excel userform which is connected to access database, I want
to
auto populate certain fields based on what I input in one textbox.

For eg: If I input Students Id, I want Students name and students phone
to
populate in the userform.

So far I have got the form to populate the fields from the database,
BUT it
only populates the first entry from the database.
No matter what I put in the student ID textbox it always populates
detailf
form the first row only.

Here is the code I have so far.

Private Sub StudentNo_AfterUpdate()

Dim cnt As Object, rst As Object, strSQL As String

Set cnt = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")

strSQL = "SELECT Student_Name, Student_Phone FROM Student_Table WHERE
Student_No = " & Me.StudentNo.Value

cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=C:\Documents\Students_DB.accdb; Jet OLEDB:Database
Password=mystudents; "

rst.Open "Student_Table", cnt, 1, 3, 2

Me.StudentName.Value = rst.Fields(7)
Me.StudentPhone.Value = rst.Fields(9)

Set rst = Nothing
Set cnt = Nothing

End Sub

Thanks in advance

Hey Tim,

Heres the code:

Private Sub StudentNo_AfterUpdate()

Dim cnt As Object, rst As Object, strSQL As String

Set cnt = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")

strSQL = "SELECT Student_Name, Student_Phone FROM Student_Table WHERE
Student_No = " & Me.StudentNo.Value

cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=C:\Documents\Students_DB.accdb; Jet OLEDB:Database
Password=mystudents; "

rst.Open "Student_Table", cnt, 1, 3, 2

Me.StudentName.Value = rst.Fields(7)
Me.StudentPhone.Value = rst.Fields(9)

Set rst = Nothing
Set cnt = Nothing


"Tim Williams" wrote:

Some problem in strSQL: what's in there ?

Tim

"sam" wrote in message
...
Thanks for helping mike,

When i change it to "rst.Open strSQL ,cnt, 1, 3, 2"

I get an error: "Syntax error in FROM clause"
on the new line "rst.Open strSQL ,cnt, 1, 3, 2"


"Mike" wrote:

Try this

rst.Open strSQL ,cnt, 1, 3, 2


"sam" wrote:

Hi All,

I have an excel userform which is connected to access database, I want
to
auto populate certain fields based on what I input in one textbox.

For eg: If I input Students Id, I want Students name and students phone
to
populate in the userform.

So far I have got the form to populate the fields from the database,
BUT it
only populates the first entry from the database.
No matter what I put in the student ID textbox it always populates
detailf
form the first row only.

Here is the code I have so far.

Private Sub StudentNo_AfterUpdate()

Dim cnt As Object, rst As Object, strSQL As String

Set cnt = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")

strSQL = "SELECT Student_Name, Student_Phone FROM Student_Table WHERE
Student_No = " & Me.StudentNo.Value

cnt.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=C:\Documents\Students_DB.accdb; Jet OLEDB:Database
Password=mystudents; "

rst.Open "Student_Table", cnt, 1, 3, 2

Me.StudentName.Value = rst.Fields(7)
Me.StudentPhone.Value = rst.Fields(9)

Set rst = Nothing
Set cnt = Nothing

End Sub

Thanks in advance



.