HELP PLEASE: Populate certain userform fields from access database

[sam]

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

 

[sam]

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.

 

[sam]

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=Cocuments\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

 

[sam]

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

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/e...erform-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/showthread.php?t=148050

Microsoft Office Help

.

 

[Tim Williams]

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

should be

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

Tim

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

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/e...erform-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/showthread.php?t=148050

Microsoft Office Help

.

 

[sam]

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.

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

should be

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

Tim

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

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/e...erform-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/showthread.php?t=148050

Microsoft Office Help

.

.

 

[Tim Williams]

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

Tim

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.

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

should be

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

Tim

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


:


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/e...erform-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/showthread.php?t=148050

Microsoft Office Help

.

.

 

[sam]

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.

 

[sam]

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.

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

should be

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

Tim

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

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/e...erform-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/showthread.php?t=148050

Microsoft Office Help

.

.

 

[sam]

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

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

Tim

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.

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

should be

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

Tim

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


:


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/e...erform-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/showthread.php?t=148050

Microsoft Office Help

.



.

.