PLEASE HELP with populating userform fields from access database

[sam]

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 OLEDBatabase
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

 

[Mike]

Try this

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

 

[sam]

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"

 

[Tim Williams]

Some problem in strSQL: what's in there ?

Tim

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"

 

[sam]

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 OLEDBatabase
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

 

[Mike]

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

 

[sam]

Still getting the same error...

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

am I suppose to mention the table and database name in the sql string??
or maybe open the connection and the recordset?

 

[Tim Williams]

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

You have to *use* the SQL statement you constructed.

Tim

 

[sam]

Hey Mike,

Here is my updated sqlStr and recordset code:

strSQL = "SELECT Student_Table.Student_Name, Student_Table.Student_Phone
FROM C:\Documents\Students_DB.accdb.Student_Table WHERE
Student_Table.Student_No = '" & Me.StudentNo.Value & "';"

rst.Open Source:=strSQL, _
ActiveConnection:=cnt, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockOptimistic, _
Options:=adCmdTable

And now I am getting an error message:

Syntax error(missing operator) in query expression:

'SELECT Student_Table.Student_Name, Student_Table.Student_Phone FROM
C:\Documents\Students_DB.accdb.Student_Table WHERE
Student_Table.Student_No = '32356''

here 32356 is the student Id that I inputted in the student_id field.

 

[Tim Williams]

If Student_No is a numeric field then you don't need quotes around it.
With ADO you can generally omit the ending semicolon.

Tim

 

[sam]

Hey Tim, I am already using the SQL that i created

Here is my updated sqlStr and recordset code:

strSQL = "SELECT Student_Table.Student_Name, Student_Table.Student_Phone
FROM C:\Documents\Students_DB.accdb.Student_Table WHERE
Student_Table.Student_No = '" & Me.StudentNo.Value & "';"

rst.Open strSQL, ActiveConnection:=cnt, _
CursorType:=adOpenForwardOnly, _
LockType:=adLockOptimistic, _
Options:=adCmdTable

And now I am getting an error message:

Syntax error(missing operator) in query expression:

'SELECT Student_Table.Student_Name, Student_Table.Student_Phone FROM
C:\Documents\Students_DB.accdb.Student_Table WHERE
Student_Table.Student_No = '32356''

here 32356 is the student Id that I inputted in the student_id field.

 

[Tim Williams]

Try this: if you get a SQL error then debug the SQL directly in Access.
Don't change the code around again: this should work as-is.

'############################################
Dim conn As Object, rst As Object, strSQL As String

Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=C:\Documents\Students_DB.accdb;" & _
"Jet OLEDBatabase Password=mystudents;"

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

Set rst=conn.Execute(strSQL)

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

Set rst = Nothing
Set cnt = Nothing
'###########################################

Tim

 

[sam]

Aaaaah... Finaly got it!!!!!!

Thanks a TON! TIM!!

its working great now!
thanks again!

HAPPY THANKS GIVING!!