Using CommandButton to add Fields to a Form

[gastoff]

I am trying to build a form that will serve as a timesheet and equipment tracker for end users.


Since the end user will have zero experience with Access, I am trying to make the form as intuitive and streamlined as possible.
The end user will be a foreman/jobsite supervisor that will need to input his crew members hours and equipment usage on a daily basis.

Using the CreateControl(acComboBox) command, I figured out how to insert a combo box at a desired location on my form, but is there a way to insert another combo box below the existing one using the same command button?

Im envisioning a form that has an "Add Employee" button that can be clicked and will create a combobox (fields to enter start/stop time will also be generated), and when clicked again, will add another combobox below the initial one, and so on for each employee.

Is it possible to have a CommandButton key off of the objects it creates to make a dynamic form?

I should probably throw out the caveat that I am a novice when it comes to vba...I've been using google to try and teach myself what I can, but I've run into a wall on this one.

[June7]

I suspect your db is not normalized. A process that requires routine modification of design is not optimal. What you want requires opening form in design view, do the mods, and then saving the new design. This can be done programmatically but is a bad idea.

A time and attendance db is one of the more difficult to build. This is because 'timesheet' paper forms seldom resemble normalized data entry form.

[gastoff]

Instead of building a button that adds new fields to the form as it is clicked, would it be easier to have a bunch of unpopulated fields that are set to not being visable, and have a button that reveals one at a time?

I am trying to avoid having a final form that has 15 blank fields for employee entry if only 3 people worked that day. But since the employees per day will shift and could reach as many as 15-20 for any one job, I will need the final result to be capable of having entry for everyone if needed.

[ItsMe]

What I usually do is create new records vs. creating a new field/column.

[June7]

You need a db structure that allows form design for creating records, not fields. This would mean a combobox that lists employees to choose and a textbox to enter the hours.

[gastoff]

The form I am creating will not be used to add new records. On the backend, I have separate tables for employee records, equipment records, and Active/Inactive Jobs.
The end user that will be using the final form will not have to add any new records. I am trying to build a form that they can use dropdown boxes for almost everything...the end users are construction foremen/supervisors that will likely have limited experience with Access/Excel or even computers, so it needs to be user friendly and almost entirely point-and-click interaction.

Since each foreman may work on multiple jobs in a day that have changing crews and equipment, I am attempting to design a sheet that will allow them to add employees one a time from a dropdown menu. After selecting an employee, they can add that employee's time for the project, any equipement used, and then move on and input another employee's time.

After inputting employee and equipment times, a "Submit" button at the bottom will export all the inputted data and send it off to HR for payroll processing and job cost allocation.

Am I going about this the right way or is this dynamic timecard concept too much for Access to handle?

[gastoff]

Here is a snapshot of what I'm working on so far:

[June7]

What you want could probably be done but will likely involve a LOT of VBA.

I think it would be easier to build a normal db and educate the users. I suspect they know more than you give them credit for.

You already have them selecting employee from dropdown - that really isn't any different from what I describe.

What do you mean by 'send it off' - they aren't inputting data direct to central database - who does? How do you plan to 'send'? Even if this data entry isn't direct to central db, still should use tables in normalized structure to hold input.

[gastoff]

The information they are inputting on the form will collate in a backend db that payroll can draw from.
Every day, anywhere from 10-20 project leaders/foremen are active on as many as 5 projects each, and they have dynamic crews each day. Sometimes, they may swap crews multiple times each day. The same hold for equipment. Each piece of equipment may be use on multiple projects each day: the same dump truck may haul material to 20 sites in a day, etc.
Each project will be tracking equipment and employee times each day, so my final time card will need to be dynamic as every day will have its own unique makeup of crews/equipment.
The crew leaders for most projects have been fieldmen for the last 15-20 years and are more comfortable with a shovel in their hand than a laptop, so the final system will need to be streamlined.

I can build the layout pretty well and know how I want everything to link up in the background. Im just wondering if there is a way to use VBA to add blank input fields, that way I dont have 20 blank rows filling up a page that may go unused.

[June7]

With a normalized db, there would not be '20 blank rows' - there would be only 1 - the NEW RECORD row.

[gastoff]

I am so new to access that I guess I am just not understanding what you mean. I wish the forum allowed me to upload what Im working on so you could more easily point out where my train is derailing

[ssanfu]

Read the signature for June7. I tells you how to attach files.

[gastoff]

Here is a .zip of my db.

[June7]

You need 2 more tables in your db. 1 for the selected employees and 1 for the selected equipment. Use a form/subforms arrangement for data entry. Review https://support.office.com/en-us/art...8-C266FE084102

Your main form would be bound to Projects and two subforms for binding to the two new tables.