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

.



.



.