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

Thanks a TON! TIM!!

its working great now!
thanks again!

HAPPY THANKS GIVING!!

"Tim Williams" wrote:

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 OLEDB:Database 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" wrote in message
...
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" wrote:

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

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

Tim


"sam" wrote in message
...
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?



"Mike" wrote:

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

"sam" wrote:

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



.



.



.