Hi All,

I am trying to pull some information from access database and populate some
fields in excel userform. So for example, if a user inputs his "User Id:" in
User Id Textbox, certain fields like Name, Address, ect. associtated to that
user will populate on the userform

Here is the code I have worked on so far, But it is not working

Private Sub UserId_AfterUpdate()
Dim r As Long
Dim cn As Object
Dim rs As Object

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=C:\My Documents\User_Info.accdb; Jet OLEDB:Database
Password=User1234; "

rs.Open "Loan_Presentation", cn, 1, 3, 2

Set Rng = Range("User_Id")

For Each Dn In Rng
On Error Resume Next

If Dn.Value = CLng(UserId.Value) Then

Name.Value = Dn.Next
Address.Value = Dn.Next.Next

End If
Next Dn

Application.DisplayAlerts = False

rs.Close
Set rs = Nothing

cn.Close
Set cn = Nothing

End Sub

Thanks in Advance

Can you explain in words what you are trying to do. Opening a databae
like you are doing you probably want to use SQL (string Query Language).
the SQL provides filtering of the database to return only certain
record from the database. You don't have any SQL statements so it
appears you are returning all the records.

You are using on the worksheet a named range "User_Id". does this
range contain one or more than one ID. I'm not usre from your code if
you are retuning one or more than one user ID from the database.

What may help is if you record a macro while manually performing a
query of the database which will get the correct syntac of the required
SQL. From the worksheet in excel perform the following after starting
to record a macro

Data - Import external data - new Databasequery

Setup the database filters using the wizard and post the recorded
macro.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=148050

Hi Joel, Thanks for your help.

Basically I dont want to open the access database, I just want to pull some
information form it and populate some form fields. Regarding your UserId
question, The UserId's are unique and I want to pull information related to a
particular UserId that user will input in the excel userform.

So once if I input Sam111 in the userid field in excel userform, I want to
pull information about Sam111 from the access database.

I would appreciate any help on this as I am struggling with it since long.

Hope I am clear.

Thanks in advance.

"joel" wrote:


Can you explain in words what you are trying to do. Opening a databae
like you are doing you probably want to use SQL (string Query Language).
the SQL provides filtering of the database to return only certain
record from the database. You don't have any SQL statements so it
appears you are returning all the records.

You are using on the worksheet a named range "User_Id". does this
range contain one or more than one ID. I'm not usre from your code if
you are retuning one or more than one user ID from the database.

What may help is if you record a macro while manually performing a
query of the database which will get the correct syntac of the required
SQL. From the worksheet in excel perform the following after starting
to record a macro

Data - Import external data - new Databasequery

Setup the database filters using the wizard and post the recorded
macro.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=148050

.

Opening a database you don't need to open a file like opening an excel
file. You need to do 3 tihings


1) Open a connection to provide a path from the excel application to
the database. The connection could be over an internet or on a PC just
indicating where the file is located

2) Open a recordset which is a set of instructions of what data you
want returned. This includes a SQL (Script Query Language).

3) Move the Recordset data to the workbook. All the items in the
recordset contains the data you required becasue you provided the
filtering in the SQL.


What I usually recommmend to get the SQL corrrect is to perform a
manual query while recording a macro. The Command Text portion of the
query contains the SQL.


First start a macro recorder.

Tools - Options - Start Recording

Second perform a manual query

Data - Import External Data - New Database Query

Select the type of database and located the file on your PC

Using the Wizard
menu 1 - Select the table a fields you want returned
menu 2 - Select the filtering you want applied like the ID
menu 3 - select any sorting you want
menu 4 - Press Finish. You can examine the SQL by selecting the Edit
query button and pressing Finish. The locate the SQL button in the
Query Editor.

When done post the record macro and I will make the necessary changes.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=148050

Hi Joel,

Here is what I have so far... What I want it to do is.. On excel userform..
when I input the studentId, I want certain other fields like, Student name,
Subjects to auto populate,, and all this details are in access database. Hope
you can help me with this.

Private Sub StudentId_AfterUpdate()

'1) Open a connection to provide a path from the excel application to
'the database. The connection could be over an internet or on a PC just
'indicating where the file is located

Dim r As Long
Dim cn As Object
Dim rs As Object

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")


cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=C:\Documents\Students.accdb"

'2) Open a recordset which is a set of instructions of what data you
'want returned. This includes a SQL (Script Query Language).

rs.Open "Loan_Presentation", cn, 1, 3, 2

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=C:Documents\Students.accdb;DefaultDir =C:\Documents\" _
), Array( _
";DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;;UID=admin ;" _
)), Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT Students.Roll_No, Students.Name" & "FROM
`C:\DOCUMENTS\Students.accdb`.Students Students" _
& "WHERE Students.Roll_No = StudentId.value")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_MS_Access_Database"
.Refresh BackgroundQuery:=False
End With
End Sub

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub


"joel" wrote:


Opening a database you don't need to open a file like opening an excel
file. You need to do 3 tihings


1) Open a connection to provide a path from the excel application to
the database. The connection could be over an internet or on a PC just
indicating where the file is located

2) Open a recordset which is a set of instructions of what data you
want returned. This includes a SQL (Script Query Language).

3) Move the Recordset data to the workbook. All the items in the
recordset contains the data you required becasue you provided the
filtering in the SQL.


What I usually recommmend to get the SQL corrrect is to perform a
manual query while recording a macro. The Command Text portion of the
query contains the SQL.


First start a macro recorder.

Tools - Options - Start Recording

Second perform a manual query

Data - Import External Data - New Database Query

Select the type of database and located the file on your PC

Using the Wizard
menu 1 - Select the table a fields you want returned
menu 2 - Select the filtering you want applied like the ID
menu 3 - select any sorting you want
menu 4 - Press Finish. You can examine the SQL by selecting the Edit
query button and pressing Finish. The locate the SQL button in the
Query Editor.

When done post the record macro and I will make the necessary changes.


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=148050

.

Try this. You query din't have a "Loan Table" parameter so I didn't
include in the SQL. I basically took the SQL from the query, made
Student.ID a variable, added a vbCRLF before the WHERE. I made the
recordset options the same as your "1,3,2" except use the parameter
names for the options.

Remeber you have to add the references to your VBA
1) Microsoft Access 11.0 Object library
2) Microsoft ActiveX Data Object 2.8 library.



Private Sub StudentId_AfterUpdate()

'1) Open a connection to provide a path from the excel application to
'the database. The connection could be over an internet or on a PC
just
'indicating where the file is located

'Original Source: The Code Cage Forums
'http://www.thecodecage.com/forumz/excel-vba-programming/148050-help-please-populate-certain-userform-fields-access-database.html#post549735
Dim r As Long
Dim cn As Object
Dim rs As Object

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")


cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=C:\Documents\Students.accdb"

'2) Open a recordset which is a set of instructions of what data you
'want returned. This includes a SQL (Script Query Language).

'note: StudentID is a variable from userform
MYSQL = "SELECT Students.Roll_No, Students.Name " & _
"FROM `C:\DOCUMENTS\Students.accdb`.Students Students" & vbCrLf & _
"WHERE Students.Roll_No = " & StudentId.Value

'rs.Open "Loan_Presentation", cn, 1, 3, 2
rs.Open Source:=MYSQL, _
activeconnection:=cn, _
cursortype:=adOpenForwardOnly, _
locktype:=adLockOptimistic, _
Options:=adCmdTable

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=148050

Microsoft Office Help

Hey joel,

So far I have got the form to populate the fields from the database, BUT it
only populates the first entry from the database into the userform fields.
No matter what I put in the student ID textbox it always populates details
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


"joel" wrote:


Try this. You query din't have a "Loan Table" parameter so I didn't
include in the SQL. I basically took the SQL from the query, made
Student.ID a variable, added a vbCRLF before the WHERE. I made the
recordset options the same as your "1,3,2" except use the parameter
names for the options.

Remeber you have to add the references to your VBA
1) Microsoft Access 11.0 Object library
2) Microsoft ActiveX Data Object 2.8 library.



Private Sub StudentId_AfterUpdate()

'1) Open a connection to provide a path from the excel application to
'the database. The connection could be over an internet or on a PC
just
'indicating where the file is located

'Original Source: The Code Cage Forums
'http://www.thecodecage.com/forumz/excel-vba-programming/148050-help-please-populate-certain-userform-fields-access-database.html#post549735
Dim r As Long
Dim cn As Object
Dim rs As Object

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")


cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=C:\Documents\Students.accdb"

'2) Open a recordset which is a set of instructions of what data you
'want returned. This includes a SQL (Script Query Language).

'note: StudentID is a variable from userform
MYSQL = "SELECT Students.Roll_No, Students.Name " & _
"FROM `C:\DOCUMENTS\Students.accdb`.Students Students" & vbCrLf & _
"WHERE Students.Roll_No = " & StudentId.Value

'rs.Open "Loan_Presentation", cn, 1, 3, 2
rs.Open Source:=MYSQL, _
activeconnection:=cn, _
cursortype:=adOpenForwardOnly, _
locktype:=adLockOptimistic, _
Options:=adCmdTable

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=148050

Microsoft Office Help

.

the code below will move through the recordset put will keep putting the
data in the same location so when it is done you will have the last
item. You somehow have to move the destination to another location
after each retrieval.


With rst
Do While Not .EOF
Me.StudentName.Value = rst.Fields(7)
Me.StudentPhone.Value = rst.Fields(9)
..MoveNext
Loop
End With


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=148050

Microsoft Office Help

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

should be

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

Tim

"sam" wrote in message
...
Hey joel,

So far I have got the form to populate the fields from the database, BUT
it
only populates the first entry from the database into the userform fields.
No matter what I put in the student ID textbox it always populates details
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


"joel" wrote:


Try this. You query din't have a "Loan Table" parameter so I didn't
include in the SQL. I basically took the SQL from the query, made
Student.ID a variable, added a vbCRLF before the WHERE. I made the
recordset options the same as your "1,3,2" except use the parameter
names for the options.

Remeber you have to add the references to your VBA
1) Microsoft Access 11.0 Object library
2) Microsoft ActiveX Data Object 2.8 library.



Private Sub StudentId_AfterUpdate()

'1) Open a connection to provide a path from the excel application to
'the database. The connection could be over an internet or on a PC
just
'indicating where the file is located

'Original Source: The Code Cage Forums
'http://www.thecodecage.com/forumz/excel-vba-programming/148050-help-please-populate-certain-userform-fields-access-database.html#post549735
Dim r As Long
Dim cn As Object
Dim rs As Object

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")


cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=C:\Documents\Students.accdb"

'2) Open a recordset which is a set of instructions of what data you
'want returned. This includes a SQL (Script Query Language).

'note: StudentID is a variable from userform
MYSQL = "SELECT Students.Roll_No, Students.Name " & _
"FROM `C:\DOCUMENTS\Students.accdb`.Students Students" & vbCrLf & _
"WHERE Students.Roll_No = " & StudentId.Value

'rs.Open "Loan_Presentation", cn, 1, 3, 2
rs.Open Source:=MYSQL, _
activeconnection:=cn, _
cursortype:=adOpenForwardOnly, _
locktype:=adLockOptimistic, _
Options:=adCmdTable

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=148050

Microsoft Office Help

.

Hey tim, when I change it to rst.Open strSQL, cnt, 1, 3, 2
it give me an error: "Syntax error in FROM clause"
on that line: rst.Open strSQL, cnt, 1, 3, 2

Thanks for helping.

"Tim Williams" wrote:

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

should be

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

Tim

"sam" wrote in message
...
Hey joel,

So far I have got the form to populate the fields from the database, BUT
it
only populates the first entry from the database into the userform fields.
No matter what I put in the student ID textbox it always populates details
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


"joel" wrote:


Try this. You query din't have a "Loan Table" parameter so I didn't
include in the SQL. I basically took the SQL from the query, made
Student.ID a variable, added a vbCRLF before the WHERE. I made the
recordset options the same as your "1,3,2" except use the parameter
names for the options.

Remeber you have to add the references to your VBA
1) Microsoft Access 11.0 Object library
2) Microsoft ActiveX Data Object 2.8 library.



Private Sub StudentId_AfterUpdate()

'1) Open a connection to provide a path from the excel application to
'the database. The connection could be over an internet or on a PC
just
'indicating where the file is located

'Original Source: The Code Cage Forums
'http://www.thecodecage.com/forumz/excel-vba-programming/148050-help-please-populate-certain-userform-fields-access-database.html#post549735
Dim r As Long
Dim cn As Object
Dim rs As Object

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")


cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=C:\Documents\Students.accdb"

'2) Open a recordset which is a set of instructions of what data you
'want returned. This includes a SQL (Script Query Language).

'note: StudentID is a variable from userform
MYSQL = "SELECT Students.Roll_No, Students.Name " & _
"FROM `C:\DOCUMENTS\Students.accdb`.Students Students" & vbCrLf & _
"WHERE Students.Roll_No = " & StudentId.Value

'rs.Open "Loan_Presentation", cn, 1, 3, 2
rs.Open Source:=MYSQL, _
activeconnection:=cn, _
cursortype:=adOpenForwardOnly, _
locktype:=adLockOptimistic, _
Options:=adCmdTable

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=148050

Microsoft Office Help

.



.

Debug.Print the SQL and try running it directly in Access.

Tim

"sam" wrote in message
...
Hey tim, when I change it to rst.Open strSQL, cnt, 1, 3, 2
it give me an error: "Syntax error in FROM clause"
on that line: rst.Open strSQL, cnt, 1, 3, 2

Thanks for helping.

"Tim Williams" wrote:

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

should be

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

Tim

"sam" wrote in message
...
Hey joel,

So far I have got the form to populate the fields from the database,
BUT
it
only populates the first entry from the database into the userform
fields.
No matter what I put in the student ID textbox it always populates
details
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


"joel" wrote:


Try this. You query din't have a "Loan Table" parameter so I didn't
include in the SQL. I basically took the SQL from the query, made
Student.ID a variable, added a vbCRLF before the WHERE. I made the
recordset options the same as your "1,3,2" except use the parameter
names for the options.

Remeber you have to add the references to your VBA
1) Microsoft Access 11.0 Object library
2) Microsoft ActiveX Data Object 2.8 library.



Private Sub StudentId_AfterUpdate()

'1) Open a connection to provide a path from the excel application to
'the database. The connection could be over an internet or on a PC
just
'indicating where the file is located

'Original Source: The Code Cage Forums
'http://www.thecodecage.com/forumz/excel-vba-programming/148050-help-please-populate-certain-userform-fields-access-database.html#post549735
Dim r As Long
Dim cn As Object
Dim rs As Object

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")


cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=C:\Documents\Students.accdb"

'2) Open a recordset which is a set of instructions of what data you
'want returned. This includes a SQL (Script Query Language).

'note: StudentID is a variable from userform
MYSQL = "SELECT Students.Roll_No, Students.Name " & _
"FROM `C:\DOCUMENTS\Students.accdb`.Students Students" & vbCrLf & _
"WHERE Students.Roll_No = " & StudentId.Value

'rs.Open "Loan_Presentation", cn, 1, 3, 2
rs.Open Source:=MYSQL, _
activeconnection:=cn, _
cursortype:=adOpenForwardOnly, _
locktype:=adLockOptimistic, _
Options:=adCmdTable

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile:
http://www.thecodecage.com/forumz/member.php?userid=229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=148050

Microsoft Office Help

.



.

Hey Joel,

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.


"joel" wrote:


the code below will move through the recordset put will keep putting the
data in the same location so when it is done you will have the last
item. You somehow have to move the destination to another location
after each retrieval.


With rst
Do While Not .EOF
Me.StudentName.Value = rst.Fields(7)
Me.StudentPhone.Value = rst.Fields(9)
.MoveNext
Loop
End With


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=148050

Microsoft Office Help

.

Hey Tim,

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" wrote:

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

should be

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

Tim

"sam" wrote in message
...
Hey joel,

So far I have got the form to populate the fields from the database, BUT
it
only populates the first entry from the database into the userform fields.
No matter what I put in the student ID textbox it always populates details
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


"joel" wrote:


Try this. You query din't have a "Loan Table" parameter so I didn't
include in the SQL. I basically took the SQL from the query, made
Student.ID a variable, added a vbCRLF before the WHERE. I made the
recordset options the same as your "1,3,2" except use the parameter
names for the options.

Remeber you have to add the references to your VBA
1) Microsoft Access 11.0 Object library
2) Microsoft ActiveX Data Object 2.8 library.



Private Sub StudentId_AfterUpdate()

'1) Open a connection to provide a path from the excel application to
'the database. The connection could be over an internet or on a PC
just
'indicating where the file is located

'Original Source: The Code Cage Forums
'http://www.thecodecage.com/forumz/excel-vba-programming/148050-help-please-populate-certain-userform-fields-access-database.html#post549735
Dim r As Long
Dim cn As Object
Dim rs As Object

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")


cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=C:\Documents\Students.accdb"

'2) Open a recordset which is a set of instructions of what data you
'want returned. This includes a SQL (Script Query Language).

'note: StudentID is a variable from userform
MYSQL = "SELECT Students.Roll_No, Students.Name " & _
"FROM `C:\DOCUMENTS\Students.accdb`.Students Students" & vbCrLf & _
"WHERE Students.Roll_No = " & StudentId.Value

'rs.Open "Loan_Presentation", cn, 1, 3, 2
rs.Open Source:=MYSQL, _
activeconnection:=cn, _
cursortype:=adOpenForwardOnly, _
locktype:=adLockOptimistic, _
Options:=adCmdTable

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=148050

Microsoft Office Help

.



.

Hey Tim,

The SQL statement seems to be working fine in access, I do see the results
if I run the query in access, But when i try and user the userfrom i get that
error: "syntax error in FROM clause"

can there be something wrong with this line: rst.Open strSQL, cnt, 1, 3, 2

I used to use the 1, 3 and 2 when i used to put the table name instead of
strSQL, now that i have changed the table name with the SQL string name will
the statement "rst.Open "Student_Table", cnt, 1, 3, 2" still be the same?

Thanks in advance





"Tim Williams" wrote:

Debug.Print the SQL and try running it directly in Access.

Tim

"sam" wrote in message
...
Hey tim, when I change it to rst.Open strSQL, cnt, 1, 3, 2
it give me an error: "Syntax error in FROM clause"
on that line: rst.Open strSQL, cnt, 1, 3, 2

Thanks for helping.

"Tim Williams" wrote:

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

should be

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

Tim

"sam" wrote in message
...
Hey joel,

So far I have got the form to populate the fields from the database,
BUT
it
only populates the first entry from the database into the userform
fields.
No matter what I put in the student ID textbox it always populates
details
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


"joel" wrote:


Try this. You query din't have a "Loan Table" parameter so I didn't
include in the SQL. I basically took the SQL from the query, made
Student.ID a variable, added a vbCRLF before the WHERE. I made the
recordset options the same as your "1,3,2" except use the parameter
names for the options.

Remeber you have to add the references to your VBA
1) Microsoft Access 11.0 Object library
2) Microsoft ActiveX Data Object 2.8 library.



Private Sub StudentId_AfterUpdate()

'1) Open a connection to provide a path from the excel application to
'the database. The connection could be over an internet or on a PC
just
'indicating where the file is located

'Original Source: The Code Cage Forums
'http://www.thecodecage.com/forumz/excel-vba-programming/148050-help-please-populate-certain-userform-fields-access-database.html#post549735
Dim r As Long
Dim cn As Object
Dim rs As Object

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")


cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=C:\Documents\Students.accdb"

'2) Open a recordset which is a set of instructions of what data you
'want returned. This includes a SQL (Script Query Language).

'note: StudentID is a variable from userform
MYSQL = "SELECT Students.Roll_No, Students.Name " & _
"FROM `C:\DOCUMENTS\Students.accdb`.Students Students" & vbCrLf & _
"WHERE Students.Roll_No = " & StudentId.Value

'rs.Open "Loan_Presentation", cn, 1, 3, 2
rs.Open Source:=MYSQL, _
activeconnection:=cn, _
cursortype:=adOpenForwardOnly, _
locktype:=adLockOptimistic, _
Options:=adCmdTable

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile:
http://www.thecodecage.com/forumz/member.php?userid=229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=148050

Microsoft Office Help

.



.



.