TechHelp

Very Difficult form Upload Images Link
Maarten Brusselers
12 months ago

I hope someone can point me in the right direction here but I might be going all wrong about this. My staff uses an excel sheet to keep track of which days each of the farm staff works every month. I have attached a screenshot of the excel sheet they use. I have created a form to simulate the excel sheet as they are used to it and like it. The dates are now completely automated with =DATESERIAL. Our salary month runs from the 29th till the 28th of every month as shown in the screenshot. This part works fine. My problem is the bit where you enter the ones, halves and zeros for having worked that day in full, half or not. It is no problem to create the text boxes but how do I get the right date with the right employee and work value into my table. I can't see how to query this thing.

Maarten Brusselers
12 months ago

I've put empty text boxes in the form but when I enter a number it the top box the whole column takes on that value. When I then enter a new value in the box below the same thing happens. It is a continuous form

Maarten Brusselers
12 months ago

The staff fields come from a query.

Juan C Rivera
12 months ago

Take a look at Project Management Template.
Richard even has a sample database that you can try and see if you like.

Kevin Yip
12 months ago

Hi Maarten, you need a crosstab query, which transposes some of the rows into columns, so you can create that look with ease. But a crosstab query is uneditable, so you can't use it on a form for data entry. A crosstab query is strictly for display purposes. You need a more conventional form for data entry. See pictures below.

Kevin Yip
12 months ago

Maarten Brusselers
12 months ago

Hi Kevin,
Thanks once again.for your help.
I'm at expert 15 now so I didn't get to Crosstab Queries yet. So there is no way that I can use this form format for data entry. I was hoping that with the the right SQL and VBA it could be made to work. Using a conventional form for for data entry is a lot more labor intensive.

Kevin Yip
12 months ago

You can use VBA and/or append queries to automate entries of some of the repeated data, such as names. But yes, Access tables must be normalized, and a crosstab format is not.

Maarten Brusselers
12 months ago

Hi Kevin,
I've made a new form that should work but now run into another problem. The Day field in unbound with a default value of 1. However, whenever I change the value in the field all the values in this field for the other employees change as well. I cannot give them an individual value. I want to use this form with an append query.

Maarten Brusselers
12 months ago

Kevin Yip
12 months ago

An unbound field can only have one value for all the rows. It's like a label; it can only be one thing. You need a bound field if you want the rows to show different Day values.

Maarten Brusselers
12 months ago

So I can basically bound it to anything as I will be using an append query based on the form values, right?

Kevin Yip
12 months ago

If it's bound, that means it needs to be an actual field in a table or a query, so not exactly anything. Sometimes I would create a dummy field (or even dummy table) just to make certain tasks possible. Form design is an art, not a science, and it should be done to make data entry as easy as possible.

This thread is now closed. If you wish to comment, start a NEW discussion, below.

Back to TechHelp Comments

Subscribe to TechHelp
Get notifications when this page is updated