The below vba code successfully duplicates records in a table and properly refreshes the subform tied to the table via query) with the dup records in the requested alpha order. However, it errors at Do.Cmd.GoToRecord line, shown in below code (the purpose of which is only to go to the subform and activate the record just duplicated) with the following Microsoft Visual Basic error message:

Run-time error ‘2105’: You can’t go to the specified record.


Please help me activate the record just added after query sort is complete. Here is my vba code:

Option Compare Database

Private Sub DuplicateActiveRow_Click()

Dim fldEmployeeID As Long
Dim fldLastName As String

fldEmployeeID = Me.txtHiddenID
fldLastName = Me.txtName

DoCmd.RunSQL "INSERT INTO tblHours (fldEmployeeID, fldName) VALUES (" & fldEmployeeID & ",'" & fldLastName & "');"


DoCmd.Close acForm, "frmPayEntryScreen"
DoCmd.OpenForm "frmPayEntryScreen", acNormal, "", "", , acNormal

With CodeContextObject
DoCmd.GoToRecord acForm, "frmPayEntryScreen", acGoTo, fldEmployeeID
End With

End Sub

Design is as follows:
I have a subform that will appear to the end-user in a datasheet view so that she may hide/unhide columns at will. When opened, this form will contain over 800 records appearing alphabetically by employee name, each with employee number, employee name, and hours worked. Each employee will have one row on which the end-user may enter hours. However, the end-user wants the ability to add a new row (or 2 or three new rows), for any employee she presently has active with her cursor, to the existing datasheet view of the form right below the existing record for that employee. That new row should contain that same employee's employee number and
employee name with the same hours columns available as the previous
record (but the hours should be blank), so that the end-user can enter hours in the new row (hours then populate the same recordsourse table named tblHours as the original employee record).

Since the form we are talking about is a subform that must be in datasheet view, and therefore will not show command buttons in form view, the ‘DuplicateActiveRow’ command button that will trigger the functionality I've described above, must be on the Parent Form.

The the last parameter of "GoToRecord" is an Offset. Which means that it will move forward or backward from the current record. Apparently, fldEmployeeID is a number that is bigger than the number of records you can move.

Perhaps you could use the WhereCondition parameter of the "OpenForm". Something like:

DoCmd.OpenForm "frmPayEntryScreen", acNormal, , "fldEmployeeID = " & fldEmployeeID, , acNormal

Frankly you may be going overboard here. You seem to be using a Main form only to provide the button to duplicate the current row. Why don't you use the main form to allow the user to select an employee and use the subform to display only the hours for that employee.

In that situation all you really need to do is add the records then requery the subform like:

Me.SubFormControlName.Requery

Of course your subform will have to be sorted in the appropriate order so the new (blank) records will appear at the top. Alternatively, after you have inserted the blank rows, you could just set the filter on the subform like:

Me.SubformControlName.Form.Filter = "[Hours] is null"
Me.SubformControlName.Form.FilterOn = True

I think those statements will work. You may have to do some tweaking to get it right. Also you should probably provide a way to unset that filter so the user can toggle between seeing all records in the subform and seeing only those blank ones for data entry.

If your user really wants to see all of the hours for all of the employees, you could also provide an option that doesn't restrict the subform to a single employee. I think that simply means removing the Master/Child link fields on the subform and requerying it.

Just curious about your data structure too...

Why does tblHours include fldName? Can't you derive the name of the person by linking fldEmployeeID to tblEmployees (or whatever you call that table)? If the employee's name changes later do you want tblHours to keep a record of the name of the employee at the time the hours were recorded? If tblHours includes the name for displaying in this Datasheet View subform, just change the record source of the subform to a query that combines tblHours and tblEmployee. You'll have to be careful how you build that query so it will be an updatable recordset. But that can be done.

Also, when you INSERT to tblHours, why don't you include a date? It seems to me you would want a Unique index on fldEmployeeID+HoursPeriodDate in tblHours to prevent the user from recording duplicate hours for a particular employee and period combination. I realize that that code would be pretty involved. But your database should be responsible for the integrity of the data -- not the user.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

Hi rjweers, If you have a moment, I've responded to some of your comments (my responses are in bold) and would be interested in your follow-up thoughts.

"Perhaps you could use the WhereCondition parameter of the "OpenForm". Something like:

DoCmd.OpenForm "frmPayEntryScreen", acNormal, , "fldEmployeeID = " & fldEmployeeID, , acNormal"

I tried the above solution but it doesn't seem to activate the record I just duplicated.

"Frankly you may be going overboard here. You seem to be using a Main form only to provide the button to duplicate the current row."

I'm using the main form to (1) link the two subforms to eachother: one subform acts as an employee header providing details regarding the activated record of the second datasheet subform. The second subform must be in datasheet view to allow hiding/unhiding of columns by end-user. AND (2) to provide the button to duplicate the current row.

"Why don't you use the main form to allow the user to select an employee and use the subform to display only the hours for that employee."

The customer requires the layout to be as I've described where she can see all employees in a list when she's entering hours for any given employee. She is very unflexible on this point. I understand what you're saying and your point is well taken by me. There are just some design requirements that I can't negociate.

"Just curious about your data structure too...

Why does tblHours include fldName? Can't you derive the name of the person by linking fldEmployeeID to tblEmployees (or whatever you call that table)? If the employee's name changes later do you want tblHours to keep a record of the name of the employee at the time the hours were recorded?"

I know my design in inappropriate from the normalization perspective. However, the end-user will never be able make changes to the employee name in my database. She will see the employee # and employee name, and will be allowed only to enter hours and payroll dollar amounts. She will make name changes in another system and that system will send a refresh to my employee names table every two weeks.

"If tblHours includes the name for displaying in this Datasheet View subform, just change the record source of the subform to a query that combines tblHours and tblEmployee. You'll have to be careful how you build that query so it will be an updatable recordset. But that can be done."

I tried to change the record source of the subform to a query that combines tbleHours and tblEmployee so that fldname doesn't have to be duplicated in both tables. However, when my subform is displayed in datasheet view thereafter, and I try to enter hours, I receive a message that I can't update the hours fields in my datasheet subform. I'm sure this reveals what a newbie I am but I don't know how build such a query (having two tables linked to eachother) so it will be an updatable recordset. Can you tell me how to do this?

I understand your design. This is the one you were asking about not long ago when you needed to have the subform in datasheet view.

Generally, a recordset is updatable when multiple tables have a unique one-to-one relationship. I usually get this to work through trial and error myself.

When you join the employees to tblHours, it's either left join tblhours to tblEmployee by the employee id (the arrow points from tblHours to tblEmployee) or inner join (there is no arrow). To test, open the query in datasheet view and simply type over a field other than employee id. If it changes, you got it.

As to scrolling to the newly created record, if you are filtering the hours by an employee you could use something similar to the code automatically generated when you add a lookup combo box on a bound form:

Set rs = Me.Recordset.Clone
rs.FindFirst "[SampleID] = " & Str(Nz(Me![Combo12], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

All you need to do is change the "FindFirst" to have the appropriate condition. Something like "[Hours] is null" to find that blank record you created.

Of course "Me.Recordset" is going to have to be something like "Me.Subformcontrolname.Form.Recordset".

Sorry I don't have time to work through the solution more thoroughly. I'll try to get back to this tonight or tomorrow morning.

I set up a similar situation for the query. (I was close in my guess.) I think you will have an updatable query if you do something like this:

Select fields from employee right join tblHours on employee.id = tlbhours.employeeid

As far as having the two subforms, it seems like you're doing this backward. But whatever the user wants, the user gets. Damn them! :D

The FindFirst idea should work to move to the empty hours record that you created. Your search argument should include "(employeeid = " & fldEmployeeID & ") and ([Hours] is null)", or some such.

Thanks rjweers aka Randall! I'll try this solution now; you've really been saving my skin on this project which is obviously way over my skill level and which I'd never survive without generous forum folks like you!