Argh! on QueryDef.Execute getting error '3061'!

[CompAnalyst]

Alrighty... i used to use this method when we had Access 97 and now i can't find the solution in 2000 + XP. Basically what i am trying to do is when a user enters an employee ID in the form, it runs an update query which grabs other employee data from a different table and updates the fields in the form. The reason for this is that employee data changes from time to time and we need to maintain a historical record of information at the time that the information was input. (Don't ask, Human Resources is very silly)
SO... here is my code so far... and it doesn't want to cooperate. I am pulling my hair out. Sigh

Private Sub EmplID_AfterUpdate()
Dim db As DAO.Database
Dim qry As DAO.QueryDef

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Set db = CurrentDb()
Set qry = db.QueryDefs("qryUpdateEEReclass")
qry.Parameters("intEmplID") = Me.EmplID
qry.Execute dbFailOnError !!!! I get the error here !!!!
qry.Close
Set qry = Nothing
Me.Refresh
End Sub

Please help! Thanks!

Rachel, Compensation Analyst

 

[RickSpr]

Open the query from the Database Window. It should prompt for intEmplID only. If it prompts for anything else, you've probably misspelled something, probably in a criteria expression.

Wouldn't it have been cleaner to make it a SELECT query, open it as a recordset, and copy the fields to the form controls? The way you have it, you have to save the record before it's complete. Not only does that have the potential to leave a poor-quality record in the table, it means that after the form user completes it and moves to another record, if the user clicks Undo Saved Record it won't be completely backed out.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[CompAnalyst]

hmmm.... well i am the first one to admit i dont know how to go about doing your SELECT query suggestion. Would you mind terribly showing me an example? Thanks!

 

[RickSpr]

If you don't know about accessing tables using a Recordset, let me suggest an easier alternative first. Use the DLookup() function to retrieve the fields you need.

For example, you have the employee ID in the EmplID control, and you want to look up three fields named EmplName, EmplSSN, and EmplDOB from table Employees. The three fields will be loaded into three controls on the form as follows:
EmplName goes into the txtEmplName text box
EmplSSN goes into the txtEmplSSN text box
EmplDOB goes into the txtEmplDOB text box
Here's the code to do that (in the EmplID_AfterUpdate event procedure):

    Me.txtEmplName = DLookup("EmplName", "Employees", "EmplID=" & Me.EmplID)
    Me.txtEmplSSN = DLookup("EmplSSN", "Employees", "EmplID=" & Me.EmplID)
    Me.txtEmplDOB = DLookup("EmplDOB", "Employees", "Emplid=" & Me.EmplID)

The DLookup function is easy to code, but inefficient for more than just a few fields, because it has to read the table each time you call it.

Here's how to do it with a Recordset:

    Const MyQuery = "SELECT EmplName, EmplSSN, EmplDOB " _
        & "FROM Employees WHERE Emplid = |"
    Dim db As DAO.Database
    Dim rst As DAO.Recordset

    Set db = CurrentDb()
    Set rst = db.OpenRecordset(Replace(MyQuery, "|", EmplID))
    If rst.BOF And rst.EOF Then
        MsgBox "Invalid employee ID"
    Else
        Me.txtEmplName = rst!EmplName
        Me.txtEmplSSN = rst!EmplSSN
        Me.txtEmplDOB = rst!EmplDOB
    End If
    rst.Close
    Set rst = Nothing
    Set db = Nothing

Using the recordset is more efficient because all the fields you need are retrieved with a single search of the table.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[CompAnalyst]

thanks for the recordset stuff... i will look at it the scripting.

As far as DLookup goes, I can't use that because it would use whatever the most current information in the table instead of just grabbing the content at the time. For instance, employees come and go. If they are not on the current month's active report, then they wouldnt have any information for their job code, etc. Or if someone changes jobs. I would want to know what job they had at the time that the person would recieve the bonus.

BTW... i did get it to work using the querydef. U were right there was something wonky in my query! I will however try and learn how u did it with the recordset approach, as i am always trying to improve the old VBA skills. Thanks!

 

[RickSpr]

The DLookup and the Recordset approaches are functionally identical. I don't know why you say DLookup would be grabbing the most recent data. If the DLookup is in the AfterUpdate event, as I indicated, it only runs when the EmplID is updated. Otherwise, EmplID displays what is in the form's record source.

I've got a hunch, since you referred to this as "scripting" (it's not--it's VBA code, not VBS), that you're used to DLookup in a web environment, where everything is always dynamic.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein