Can you help an old has been with a simple filter??

[unclerico]

Ok, so I haven't coded VBA for years (going back to version 97) and am now trying to get back in the swing of things for a project. Basically, I have an Access 2003 front end using linked views from a SQL Server 2000 back end. I have a form with some employee information on it and a command button. All I want to do is simply enable a person to type in an employee number, click the button and have it filter the recordset for the information:

Option Compare Database
Private rs1 As ADODB.Recordset

Private Sub Form_Load()
    Dim strSql As String
    
    Set rs1 = New ADODB.Recordset
    
    strSql = "SELECT emplid, lastname & ',' & firstname & ' ' & midnameinit AS empname, jobcd FROM dbo_vw_EmployeeInfo_TDS"
    With rs1
        .ActiveConnection = CurrentProject.Connection
        .Open strSql
    End With
    
    Call SetControls
    
    'rs1.Close
    
End Sub

Private Sub cmdFilter_Click()
On Error GoTo Err_cmdFilter_Click
    Dim lngEmpId As Long
    
    lngEmpId = txtEmpId
    
    rs1.Filter = "emplid = " & lngEmpId
    
    Call SetControls

Exit_cmdFilter_Click:
    Exit Sub

Err_cmdFilter_Click:
    MsgBox Err.Description
    Resume Exit_cmdFilter_Click
    
End Sub

Private Sub SetControls()
    Me.txtEmpId = rs1("emplid").Value
    Me.txtName = rs1("empname").Value
    Me.txtJobCode = rs1("jobcd").Value
End Sub

Now the above code works just fine; however, you might see that the rs1.Close is commented out basically keeping the connection to the DB open the entire time...not good. Also, when I try and set the Form's Record Source property at design time (along with each of the textboxes Control Source) and then try and filter the results I keep getting:

ODBC - Update on linked table 'dbo_vw_EmployeeInfo_DTS' failed
[Microsoft][ODBC SQL Server Driver][SQL Server]Update or insert of view or function 'dbo.vw_EmployeeInfo_TDS' failed because it contains a derived or contant field(#4406)

I don't understand since I am only trying to filter the result set, not update or insert a new record
Can anyone offer some suggestions?? Thanks a lot.

 

[unclerico]

anyone??

 

[Remou]

When I looked at this post before, it seemed to me that it is a mixture between:
How to bind Microsoft Access forms to ADO recordsets

http://support.microsoft.com/kb/281998

and simply selcting a single record from a recordset. However, I do not use SQL Server, so it is just a thought, which also may serve to keep the thread lively.

 

[unclerico]

thanks for the link, it provided me with some good info...now, another question, whenever I specify as a query that references my linked view

Me.RecordSource = "qryEmployeeInfo"

and then try and apply a filter to it

Me.Filter = "emplid = " & lngEmpId
Me.FilterOn = True

I get the error at the bottom of my first post above
however, when I specify the exact same sql statement as in my query above

Me.RecordSource = "SELECT emplid, lastname FROM dbo_vw_EmployeeInfo_TDS"

and then apply the filter

Me.Filter = "emplid = " & lngEmpId
Me.FilterOn = True

I get no errors

 

[Remou]

Just guessing again, but it could be due to combining:
[tt]lastname & ',' & firstname & ' ' & midnameinit AS empname[/tt]

Does the query work with 'straight' fields?

 

[unclerico]

yes it does; however, the wierd thing is that when the form first opens it populates the form just fine with the first record and if I navigate the records by using the control at the bottom of the form (i believe they are called Navigation Controls) it works just fine, it only errors out once I try applying the filter...