Do I need a DLookup for this?????

[WB786]

Here is what I am trying to do: I have a field on my form called Tracker which stores the user login id by default. I have the combo box with the employee names with full name. The combo box pulls the value from another database called Employee filtered with a query. So when a user tries to add a new record it should look at the login ID and then just fill in the Employee Full Name field accordingly. I tried to use Dlookup but couldn't get it to do what I want. Any ideas?? Thanks in advance.

-WB

 

[Kozia]

Dim dbs As Database
Dim rstSet As DAO.Recordset
Dim strSQL As String
Dim tmpstring As String, tmpSupervisor As String

Set dbs = CurrentDb()

tmpstring = Me![UserId]

strSQL = "SELECT [Employee].*"
strSQL = strSQL & " FROM [Employee]"
strSQL = strSQL & " WHERE (([Employee].ID)='" & tmpstring & "');"


Set rstSet = dbs.OpenRecordset(strSQL)
If rstSet.RecordCount > 0 then
Me![FullName]=rstSet![FullName]
end if
Assuming of course it returns one record only

Or you can link the tables and set the form on a query of the two tables linked

 

[WB786]

Now I am getting this error when it is trying to look for my name (I am using my name as a test of course).

Run-time error '3075':

Syntax error in string in query expression '(([tblEmployees].Emp_tracker)='begw'.
____________________________________________________
Here is my code after modification:

Private Sub Form_Current()
Dim dbs As Database
Dim rstSet As DAO.Recordset
Dim strSQL As String
Dim tmpstring As String, tmpSupervisor As String

Set dbs = CurrentDb()

tmpstring = Me![Tracker]

strSQL = "SELECT [tblEmployees].*"
strSQL = strSQL & " FROM [tblEmployees]"
strSQL = strSQL & " WHERE (([tblEmployees].Emp_tracker)='" & tmpstring & "');"


Set rstSet = dbs.OpenRecordset(strSQL)
If rstSet.RecordCount > 0 Then
Me![Counselor] = rstSet![Counselor]
End If
End Sub

What I am doing wrong? Thanks.

-WB

 

[Kozia]

I don;t see anything wrong. This is what I use. Sorry no help here.
"SELECT [tblEmployees].* FROM [tblEmployees]WHERE [tblEmployees].Emp_tracker)='" & tmpstring & "';"
Try putting this on one line make sure after Emp_tracker)=
put single quote double space & space tmpstring & double single

if it doesn;t work create a query select fields you want and under field Emp_tracker on criteria line put
Forms![NameofForm]![Emp_tracker]

than in your code put

Set dbs = CurrentDb()
set rstSet=dbs.OpenRecordset("NameofQuery&quot

if you run the query when your form is not open it will prompt you for that field.

 

[WB786]

I tried your option number 2 as it is easiest one to do and the query runs fine. But now I am getting error on the sql statement as follows:

Run-time error '3061':
Too few parameters. Expected 1.

Any ideas on this one.

-WB

 

[Kozia]

I was afraid of that. I get that msg in Access2000 everytime my query reffers to a field on a form and is triggerred from the code. I haven;t figured out why its giving me the problem. It used to work with Access 97.
Thy puting Dynaset but its doubtful.
set rstSet=dbs.OpenRecordset("NameofQuery", dbOpenDynaset)

If this doesn;t work than I really don't know. Try maybe looking at the SQL statement on the query and compare to the SQL I had you use at the beginning. Try a simple SQL just selecting from a table first, all records if that works add the "where" clause or there is another way. Checking every record in a set if it matches Me![Tracker].
drop me a line at sylviakozia@yahoo.com
There is anohter thing to see. What libraries you have selected. Under Tools references on your code screen window.

 

[WB786]

Dynaset didn't work. These are the libraries:

Visual Basic for App
MS Access 10.0 Object Library
OLE Automation
MS ActiveX DAta Objects 2.1
MS Outllok 10.0 Object
MS DAO 3.6 Object
MS VB for Apps Extensibility 5.3
MS Office XP Web Components
MS Data Source Interfaces

I am missing something? Thanks.

-WB