Runetime Error with SQL

[lotharious]

when trying to run this vba code I get this error on the rs.open statement.

Run-time Error -2147217904(80040e10)
No value given for one or more required parameters.

[code
Private Sub Completed_by_Change()

Dim cn As ADODB.Connection
Dim rs As New ADODB.Recordset
Set cn = CurrentProject.Connection
Dim sSQL As String

Me![Date Completed 2] = Date

sSQL = "SELECT [Employees].[Firstname],[Employees].[LastName] FROM [Employees] "
sSQL = sSQL & "WHERE [Employees].[Emp ID]=" & Me![Completed By] & ";"

Debug.Print sSQL

rs.Open sSQL, cn
Me![Who Comp] = rs![Emp ID]
rs.Close

End Sub
[/code]

As this is just the beginning of the change of my form with this Im quite perturbed with this error. I imagine its something quite easy but it has eluded me for a couple hours now.

 

[dhookom]

You are not selecting [Emp ID] in your recordset. Your rs contains only two fields, first and last names.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lotharious]

Yes I had noticed that a bit ago, however that is not the issue I have. I get the error on the line before that as I said. the rs.open statement.

 

[lespaul]

The rs.open statement is AFTER the sql statement that duane is saying is your problem:

Private Sub Completed_by_Change()
    
    Dim cn As ADODB.Connection
    Dim rs As New ADODB.Recordset
    Set cn = CurrentProject.Connection
    Dim sSQL As String
    
    Me![Date Completed 2] = Date
    
    sSQL = "SELECT [Employees].[Firstname],[Employees].[LastName] FROM [Employees] "
    sSQL = sSQL & "WHERE [Employees].[Emp ID]=" & Me![Completed By] & ";"

    Debug.Print sSQL
        
    [b]rs.Open sSQL, cn[/b]
    Me![Who Comp] = rs![Emp ID]
    rs.Close

End Sub

The

rs.Open, sSQL

statement is trying to run the query.

I'm pretty sure that Access requires that any fields you are using as criteria, have to be in the SELECT statement. Have you tried adding EMployeeID to the SELECT statement and seeing if that works?

sSQL = "SELECT [b][Employees].EmpID, [/b][Employees].[Firstname],[Employees].[LastName] FROM [Employees] "
    sSQL = sSQL & "WHERE [Employees].[Emp ID]=" & Me![Completed By] & ";"

Leslie

 

[PHV]

I'm pretty sure that Access requires that any fields you are using as criteria, have to be in the SELECT statement
Really ?
I don't think so (in any RDBMS)

 

[lespaul]

Well, I know if you are building it in the query builder there's a special option to not make it visible.



Leslie

 

[dhookom]

Lotharious,
What is the result of the debug.print sSQL? Did you attempt to copy and paste the result into the sql view of a blank query?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

I guess that Me![Completed By] is not populated.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

I think the AfterUpdate event is more appropriate than the Change one.
I you insist with the Change event, then use Me![Completed By].Text

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lotharious]

The SQL Statement comes out fine.
And it works when I try it as a normal query as you asked.
See Below..

The actual code is this that I have been using.

Private Sub Completed_by_Change()
    
    Dim cn As ADODB.Connection
    Dim rs As New ADODB.Recordset
    Set cn = CurrentProject.Connection
    Dim sSQL As String
    
    Me![Date Completed 2] = Date
    
    sSQL = "SELECT [Employees].[Firstname],[Employees].[LastName] FROM [Employees] "
    sSQL = sSQL & "WHERE [Employees].[Emp ID]=" & Me![Completed By] & ";"

    Debug.Print sSQL
        
    rs.Open sSQL, cn
    Me![Who Comp] = rs![Firstname] & " " & rs![Lastname]
    rs.Close

End Sub

Only change is the statement below the rs.open.

The ME![Completed By] is being updated...
The SQL Statement that returns from the debug.print is

SELECT [Employees].[Firstname],[Employees].[LastName] FROM [Employees] WHERE [Employees].[Emp ID]=1;

The AfterUpdate wont work in this case. As there is more data they have to fill out in 2 more fields of the form. At least not the way Im understanding. It may in practical use.

I have also check the cn, and it looks to be correct as I expect it to be.

I have also been having another problem with this form that may be related. Im not sitting at the DB at this time so I cant give exact details.

The Error I get is something like, The field or object you are accessing does not allow this specific event.

I am currently rebuilding the form from scratch as well as about everything else in the database to reorganize it. However with the rebuilding of the form I do not get this error.
So, could these two things be related to maybe something corrupt in the db?

 

[dhookom]

PHV might have been hinting that the On Change event fires every time you press a key in the text box. I doubt this is what you want. You should use the after update as suggested which fires after the text box is updated. I don't know why you mention "they have to fill out in 2 more fields of the form".

You can also get rid of the ";" in the sSQL.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lotharious]

Ah at least I have learned something even if everything doesnt work for me..

Its a Combo Box that is the control for the code.
however, I thought on change only fired when there was a change in the box. Im still learning these things here and relearning some so.. I shall try this in the morning and see what I can come up with.

 

[lotharious]

Ok...

Made those changes and it didnt work.
Checked my table and and form and found my problem.
Quite annoying I must add..

I thank all of you for your help.

 

[PHV]

and found my problem
Thanks for sharing.
Can you please explain the members how you solved your issue ?

 

[lotharious]

Somewhere along the line, assuming after I had originallly tested my SQL Statement I frigged up the table in which I was using. The Emp ID field in the table had been renamed to Enp ID.

The only reason I found the error was I went to add somthing to another form right away this morning and I got another error. Which led me to this one. Simple fix that tired eyes kept missing.