ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   PLEASE HELP with populating userform fields from access database (https://www.excelbanter.com/excel-programming/436598-please-help-populating-userform-fields-access-database.html)

Sam
PLEASE HELP with populating userform fields from access database
 
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

Mike
PLEASE HELP with populating userform fields from access database
 
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

Sam
PLEASE HELP with populating userform fields from access databa
 
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

Tim Williams[_2_]
PLEASE HELP with populating userform fields from access databa
 
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

Sam
PLEASE HELP with populating userform fields from access databa
 
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



.

Mike
PLEASE HELP with populating userform fields from access databa
 
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



.

Sam
PLEASE HELP with populating userform fields from access databa
 
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



.

Tim Williams[_2_]
PLEASE HELP with populating userform fields from access databa
 
***********************
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



.

Sam
PLEASE HELP with populating userform fields from access databa
 
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.


"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



.

Tim Williams[_2_]
PLEASE HELP with populating userform fields from access databa
 
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" wrote in message
...
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.


"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



.

Sam
PLEASE HELP with populating userform fields from access databa
 
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



.



.

Tim Williams[_2_]
PLEASE HELP with populating userform fields from access databa
 
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



.



.

Sam
PLEASE HELP with populating userform fields from access databa
 
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



.



.



.


All times are GMT +1. The time now is 01:56 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com