Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old May 4th, 2005, 05:36 AM
Authorized User
Join Date: Apr 2005
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default Subform allows end-user to make duplicate record

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.
When the form is opened, 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, so that the end-user can enter hours in the new row (hours
then populate the same recordsourse table as the original employee

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 'Add Employee Record' command button that will trigger the
functionality I've described above, must be on the Parent Form (I
think), or possibly on another child subform, which is only there to
show the exisitng record selected by the end-user. This other
subform is tied to the first subform via the Parent form.

I am somewhat experienced with MS Access and SQL yet am a novice with
VBA so if you are able to help, please be as specific as possible in
the code you provide.

Thank you in advance.
Old May 4th, 2005, 10:14 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

Again, the database structure is important here. It doesn't sound normalized.

Why are your users using datasheet view? That is a big no-no.

Each employee should have one record, and then many hours worked subrecords. Why create a new duplicate employee just to enter hours worked, when you should keep those hours in a seperate table with the employee as a FK?

Old May 4th, 2005, 04:45 PM
Authorized User
Join Date: Apr 2005
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts


Thank you for your response.

Just to clarify, the users are not using datasheet view the way you might think. They are using form view but in properties under the format tab, their default view is datasheet. That isn't the same as datasheet view.

Regarding your normalization concern:

(1) the flat file that this program will create must have one record, with the employee number in the record, for every type of hours worked.

(2) There are two tables being used here:
Reference Table - Employee #, Employee Name, address, etc.

Hours Table - Employee #, hours, amounts

Does this help you to help me?


Old May 5th, 2005, 06:16 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts


   How is datasheet view as the default not the same as datasheet view? Anyway, if it is a subform, that is okay.

What you need is this:

No Flat files.

Employee Table
   EmployeeID - PK
   LastName - text
   FirstName - text

Hours Table
   HoursID - PK
   EmployeeID - FK
   Hours - number
   Amounts - number

   If Amounts is how much the employee gets paid per hour, that should be in the Employees Table, not this table. Perhaps a third table called Rates, for Regular and OverTime, and then a calculation based on per hour from tblEmployee, and the rate from tblRate.

   Anyway, if you use this structure, and then just use autoform on tblHours or tblEmployee, you will get a lovely form, subform with your datasheet view for the hours, and the user will not have to select the employee every time they enter hours worked for an employee since it will happen automatically.

   I think this is what you want.


Old May 10th, 2005, 12:42 PM
Authorized User
Join Date: Apr 2005
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts

hi mmcdonal or anyone else who might want to tackle this monster: first, thank you so much for trying to help so far!! Please pardon my newbie confusion but I don't know what you mean (mmcdonal) when you say auto form. Also, please understand that the reason I must put the employee name in the subform HOURS is because the end-user needs to see that in the datasheet to enter hours. They must also see employee name in the subform header employee information because they are very particular. With respect to flat files, please know that the final output of this MS Access database must be an ASCII text file. There is other software that must pick up this ASCII text file and do more work with it.

Finally, in the event that I've totally confused everyone with my requirements, here they are again in more explicit detail in the event that anyone would still consider helping:

- I have one parent form and two child subforms.
- The only purpose of the parent form is to link together the two child subforms. The parent is essential since one of the child subforms must appear in single record view and the other child subform must appear in datasheet view.

- One child subform (named frmSINGLE) is tied to a table called EMPLOYEE and its form view is single record view. I won't provide the remaining structure for this table as it is not relevant to this specific question.

- The other child subform (named frmDATASHEET), in datasheet view, which is the place where I want to be able to duplicate records, is tied to a table called HOURS with the following structure:


The functionality I need is to be able to take the active record (meaning the record active because the cursor is on it and therefore the active arrow appears), having fields EMPLOYEE_ID (pulled from table Hours), EMPLOYEE_LASTNAME_FIRSTNAME (pulled from table Hours), REGULAR_HOURS (blank until end user enters hours), OVERTIME_HOURS (blank until end user enters hours) from the subform frmDATASHEET, and duplicate that record as many times as the end-user wants based on the end-user either pressing a command button on the parent form, or pressing a function key on the keyboard like F2, etc. The duplicate records should include all fields, yet only EMPLOYEE_ID and EMPLOYEE_LASTNAME_FIRSTNAME should contain duplicate data, as the remaining fields will be filled in by the user. Again, I am reasonably knowledgeable with using MS ACCESS and SQL yet am a novice with VBA so as much detail with VBA code as possible would be appreciated. Thank you.

Old May 10th, 2005, 09:01 PM
Friend of Wrox
Join Date: Nov 2004
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post

I haven't analyzed the whole situation here. But let me pose this as a start to more research.

If you put the form that is in datasheet view onto a "master" form then use the On Current event of that form to post the ID to the current record to a hidden field on the master, your button on the master form could use that hidden field to identify which data is to be duplicated.

The syntax in the On Current event is like:

me.parent.Form.txtHiddenIDField = me.idfield

When the button is clicked you'll use txtHiddenIDField to get the employee information and do something like DoCmd.RunSQL to insert whatever records are need into the table.

Don't forget to refresh the subform that is in DataSheet view so the added records show.

Hope that helps.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
Old May 25th, 2005, 10:15 AM
Authorized User
Join Date: Apr 2005
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts

Thanks rjweers, aka Randall. I'm using your solution again. I've run across a new, related issue and if you have a chance to comment on that one, I'd appreciate it greatly! So far, you've been a tremendous help, especially compared to advise I've received on other Access/VBA forums!

Old May 25th, 2005, 11:34 AM
Authorized User
Join Date: Apr 2005
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts

Again, thanks rjweers for the solution that is now actually working! I resolved the minor vba error I received just now, so all is well, at least for now!


Similar Threads
Thread Thread Starter Forum Replies Last Post
Duplicate Record insersion nishanthahe BOOK: Beginning ASP.NET 2.0 and Databases 1 November 5th, 2008 04:38 PM
Duplicate Data in Form, its Subform and SubSubForm Odeh Naber Access VBA 0 April 17th, 2008 03:01 AM
Stop duplicate record msbsam SQL Server 2000 1 March 10th, 2008 11:24 AM
Oracle Back End - MS Access Front End - Multi User ckaliveas Oracle 1 February 1st, 2007 06:00 AM
Checking for duplicate record in jsp Regornil JSP Basics 0 July 30th, 2004 01:55 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.