By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,644 Members | 2,439 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,644 IT Pros & Developers. It's quick & easy.

Simple Basic Access Form Solution Please HELP!

P: n/a
Hi All!
I am working on this very small database and I am confused in the
designing a simple form. I only have three tables in the database.

First Table: tblExpense
Columns: ExpenseID ; ExpenseType
Data:
1 ; FOOD
2 ; AIRLINE
3 ; FARE
.....
Second Table: tblEmployee
Columns: EmpID ; EmpName
Data:
1 ; Jack Thomas
2 ; Jenny Smith
.....

Third Table: tblActivity
Columns: EmpID_FK ; ExpenseID_FK; Amount

Now, the challenge is that I need to create a form that will capture
tblActivity record for every expense types for individual employees. I
have created a combo box to select the employee on the form, but how
can I populate all the Expense types in a text boxes along with Amount
Type? My form should look like below:

Jack Thomas <<< Combo Box (selected Jack Thomas)

Food (text box) Amount (text box)
Airline (text box) Amount (text box)
Fare (text box) Amount (text box)

How can I create a form that would add above text boxes and once user
enters the information it will save it to tblActivity? I can create a
subform based on tblActivity, but I need to show all the expense types
rather than user selecting the expense type.

Please help!
Thanks in Advance!

Nov 13 '05 #1
Share this Question
Share on Google+
14 Replies


P: n/a
alwayshous...@yahoo.com wrote:
Hi All!
I am working on this very small database and I am confused in the
designing a simple form. I only have three tables in the database.

First Table: tblExpense
Columns: ExpenseID ; ExpenseType
Data:
1 ; FOOD
2 ; AIRLINE
3 ; FARE
....
Second Table: tblEmployee
Columns: EmpID ; EmpName
Data:
1 ; Jack Thomas
2 ; Jenny Smith
....

Third Table: tblActivity
Columns: EmpID_FK ; ExpenseID_FK; Amount

Now, the challenge is that I need to create a form that will capture
tblActivity record for every expense types for individual employees. I have created a combo box to select the employee on the form, but how
can I populate all the Expense types in a text boxes along with Amount Type? My form should look like below:

Jack Thomas <<< Combo Box (selected Jack Thomas)

Food (text box) Amount (text box)
Airline (text box) Amount (text box)
Fare (text box) Amount (text box)

How can I create a form that would add above text boxes and once user
enters the information it will save it to tblActivity? I can create a
subform based on tblActivity, but I need to show all the expense types rather than user selecting the expense type.

Please help!
Thanks in Advance!

Strasser:
This is one way to accomplish your very reasonable objective.
In fact, you are dealing with a basic concept that will appear
repeatedly and thus is very important.
You are describing a simple case, but the solution is essential if
you are going to design relational databases.

Just to make sure we are on the same page, these are assumptions I'm
making:
1) tblEmployee: Primary Key (PK) = EmpID and is autonumber type field.
2) tblExpense: PK = ExpenseID and is autonumber type field.
3) tblActivity: has one more field, ActivityID, which is PK and is
autonumber type.
4) Relationships: tblEmployee and tblActivity are related "1:many",
based on link between PK EmpID and Foregin Key (FK) EmpID_FK, and
referential integrity is enforced (means no orphan records allowed).
5) Relationships: tblExpense and tblActivity are related "1:many" based
on link between based on PK ExpenseID and FK ExpenseID_FK and
referential integrity is enforced.

Before I continue, are these assumptions correct?
If not, change your database to reflect my assumptions please.
I will then show you step by step how to get to your objective.
OK?
Strasser

Nov 13 '05 #2

P: n/a
ste
Hi, try this:
first of all I apologize for my english... but i'll try to be as clear
as possible

1) Make a standard form from tblemployee using Autoform
2) In this form you should have a standard Subform of tblActivity and 2
textboxes ID and Name
3)Select txtName>properties>data>delete the field control source
4) Turn it in a combo and give it a row source like SELECT empid,
empname FROM tblemp;
5) go to vb and type on the click event
Dim rs As Object

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

....it is quite basic, the result also depends how you built the tables
but it should work...

let me know!
bye,
ste

OT: is there anyone of you, dear coders, that lives in london?

Nov 13 '05 #3

P: n/a
ste
Hi, try this:
first of all I apologize for my english... but i'll try to be as clear
as possible

1) Make a standard form from tblemployee using Autoform
2) In this form you should have a standard Subform of tblActivity and 2
textboxes ID and Name
3)Select txtName>properties>data>delete the field control source
4) Turn it in a combo and give it a row source like SELECT empid,
empname FROM tblemp;
5) go to vb and type on the click event
Dim rs As Object

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

....it is quite basic, the result also depends how you built the tables
but it should work...

let me know!
bye,
ste

OT: is there anyone of you, dear coders, that lives in london?

Nov 13 '05 #4

P: n/a

alwayshous...@yahoo.com wrote:
Hi All!
I am working on this very small database and I am confused in the
designing a simple form. I only have three tables in the database.

First Table: tblExpense
Columns: ExpenseID ; ExpenseType
Data:
1 ; FOOD
2 ; AIRLINE
3 ; FARE
....
Second Table: tblEmployee
Columns: EmpID ; EmpName
Data:
1 ; Jack Thomas
2 ; Jenny Smith
....

Third Table: tblActivity
Columns: EmpID_FK ; ExpenseID_FK; Amount

Now, the challenge is that I need to create a form that will capture
tblActivity record for every expense types for individual employees. I have created a combo box to select the employee on the form, but how
can I populate all the Expense types in a text boxes along with Amount Type? My form should look like below:

Jack Thomas <<< Combo Box (selected Jack Thomas)

Food (text box) Amount (text box)
Airline (text box) Amount (text box)
Fare (text box) Amount (text box)

How can I create a form that would add above text boxes and once user
enters the information it will save it to tblActivity? I can create a
subform based on tblActivity, but I need to show all the expense types rather than user selecting the expense type.

Please help!
Thanks in Advance!


Strasser:
I was going to add to my suggestions that you make the key in
tblActivity a Combination Primary Key (CPK) instead of using a new
field as I previously suggested. The two fields that should each be
part of the CPK in tblActivity are the two Foreign Keys, EmpID_FK and
ExpenseID_FK.
Do you know how to create a CPK?
I see someone else is suggesting that you accomplish your objective
using code, which I'm NOT good at.
Want to go that route instead or do you want to create your form
without using code, in which case I'll continue.
Strasser

Nov 13 '05 #5

P: n/a
Thanks Strasser for your prompt feedback!I have designed the database
as you have suggested. I would like to create forms without using
code.

Also, thanks to ste!

Nov 13 '05 #6

P: n/a
Strasser, please give me your solution. You have understood my problem
and I would like to create forms without code (if possible)

Nov 13 '05 #7

P: n/a

alwayshous...@yahoo.com wrote:
Strasser, please give me your solution. You have understood my problem and I would like to create forms without code (if possible)


Strasser:
Yes, you can create very effective relational databases without using
any code at all (but better more powerful databases require using
code).

OK, I have time to start the explanation, but not to finish it right
now.
Make sure to ask questions as we go along.

The solution will be to create a main form and a subform.
The main form will be linked to tblEmployee
The subform will be linked to two tables, tblExpense AND
tblActivity
Each form will be created from a query.
Using a query will allow you to sort records, use prompts to find
employees quickly and other good things.

First, you may as well change tblEmployee by changing the name field.
Instead of EmpName (which held both first and last name),
create 2 fields: EmpFirstName and EmpLastName.
Don't have a "multi-part" field.

QryMainForm
This will be the name of the query from which you will create the main
form.
Create this query from only tblEmployee.
Should contain all 3 fields, left to right:
EmpID
EmpLastName (sorted ascending)
EmpFirstName (sorted ascending)
Save this query.
Use autoform to create a form from the query.
Name the new form: frmMainFormEmployee

Ooops. My alarm went off.
Do this.
I'll be back tomorrow morning (EST)

Next step quickly is create a second query, with the other two tables
as the sources for the query > inclue ALL the fields from each of the
two tables > create a form from that query.

I'm assuming your tblActivity has a CPK (made up of the 2 FKs).
Tell me if you have trouble doing this and I will explain how to do it.
Strasser

Nov 13 '05 #8

P: n/a
Thanks! I have incorporated your logic and design the forms
accordingly. I have created the composite primary keys for
tblActivity. Further, I have created two queries that include all
field from tblEmployee and tblExpense. Please let me know what do to
next.

Thanks!

Nov 13 '05 #9

P: n/a
Strasser
Good!
One question:
The query including all fields from tblEmployee sounds fine.
The query including tblExpense MUST (in addition to all fields from
tblExpenses) also include tblActivity and all its fields.
I'm assuming you did this and just didn't mention it.
I'm assuming you created the two forms, each linked through the queries
that formed them to the underlying tables.
1) frmMainFormEmployee (linked to tblEmployee thru the query)
2) name the 2nd form frmSubform, just so we are using the same
names. frmSubform is linked to both tblExpenses and tblActivity.

The next step is to incorporate the subform into the design of the main
form in such a way that there is a link between a record in the main
form (a particular employee) and the the records in the subform (a
particular amount for a particular type of sale).
When you are done, you will be able to page through the records of
the main form (one employee after another). As each employee appears,
there will be rows in the subform, showing the amount and type of sale
for each activity.
For any employee, you will be able to add a record to the next row
to create a new activity (new amount and type of sale), edit an old
activity, or delete an old activity.
The number of rows per employee will indicate the number of
activities for that employee.
So, a main form with employee name and a subform window, with a
row for each activity is the goal.
Sound right to you?

Access is going to link the main form records to the correct subform
records for you automatically, although, if this fails, you can do it
manually.
Access will only do this if you have established a relationship
between the tables, which you have done.

Here we go:
Open frmMainFormEmployee in design mode.
You have to have room to insert the subform onto this main form.
Stretch out the background grid (the detail) to the right and drag
it down.
Detail should now fill most of your screen.
This is where the subform will be placed.
Open the tool box > locate the icon for "Subform/Subreport" > click on
the icon and release your finger > go to the upper left of where you
want the subfrom to be located on the main form > click and drag out a
rectangular shape for the subform > the Subform Wizard window opens,
which is a big help!

It may NOT open if you have not installed this wizard, which is not
part of the typical Access installation. It is such a big help, that
you should go back to your MS Office CD and install the subform wizard,
if, like most times, it was not previously installed.

Follow the prompts of the wizard, making these choices:
Use an existing form.
Choose from list > choose the default (not "none")
name for subform = frmSubform

I'm out of time for now.
Write and tell me how this part went.
If you can't get the subform wizard installed, you can do the
equivalent manually, but it is more tricky.
Good luck. You will be able to DO this!
Strasser

Nov 13 '05 #10

P: n/a
Thank you very much Strasser for your detail explanation. I did
incorporate your above mention logic in my form design, but my
problem/issue still exists. It still does not show all the expense
types for all the employee (like a spreadsheet). I do not want user to
click the datasheet row and fill in the information. For e.g., if user
selects Jack Thomas as employee, the subform should populate with all
expense types (regardless of whether tblactivity contains the related
records). The form should like this:

Jack Thomas <<< combo box
SubForm:
FOOD $10
AIRLINE $35
FARE $48
.....
Once the user enters the Amount in above subform, it should get saved
in tblActivity. Please note it should list all the expense items as
textboxes and user should not have to select rows and then select
expense type.
I hope this make sense. I come up with the solution for the problem,
but I don't think my solution is appropriate. I would like to know your
solution.
Again, THANK YOU for helping me!

Nov 13 '05 #11

P: n/a

al***********@yahoo.com wrote:
Thank you very much Strasser for your detail explanation. I did
incorporate your above mention logic in my form design, but my
problem/issue still exists. It still does not show all the expense
types for all the employee (like a spreadsheet). I do not want user to click the datasheet row and fill in the information. For e.g., if user selects Jack Thomas as employee, the subform should populate with all
expense types (regardless of whether tblactivity contains the related
records). The form should like this:

Jack Thomas <<< combo box
SubForm:
FOOD $10
AIRLINE $35
FARE $48
....
Once the user enters the Amount in above subform, it should get saved
in tblActivity. Please note it should list all the expense items as
textboxes and user should not have to select rows and then select
expense type.
I hope this make sense. I come up with the solution for the problem,
but I don't think my solution is appropriate. I would like to know your solution.
Again, THANK YOU for helping me!


Strasser
It's a pleasure, only I haven't helped you YET!
I THINK we are talking about the same thing, but you tell me if this
would suit your needs:
Where I am headed would feel like the following to the user:

The user would have to know 3 things to record an expense "type" and
the amount of the expense for an employee: 1) Name of employee 2)what
"type" of expense is to be recorded 3)how much is the amount of the
expense.
If the user knows these three things, he/she can record the expense.

What does the user do?

1) User selects Jack Thomas as employee
Main form displays Jack Thomas in the main form.

2) Assuming there has been no previous activity for Jack, there would
be a single empty row in the subform, indicating no activity yet
recorded.

3) In the empty row of the subform, there would be three visible
columns(with column headings): 1) a narrow column which would only be
used during the actual data entry process [after data entry this narrow
column would appear blank, even though it was NOT blank]; 2) a column
with the heading "Expense type"; 3) a column with the heading "Expense
amount".

4) The user would be directed to click in the blank, narrow column of
the row of the subform.

5) Upon clicking the narrow column in the empty subform row, a combo
box window would open, displaying all the choices of expense "types"
previously recorded in the database.

This combo box "window" would only open briefly, for the user to make a
choice of expense type. It would not be open when the main form opened
showing Jack's name.
The combo box "window" would only open when the user clicked in the
narrow column.
As soon as the user selected a choice OR clicked in any place in the
main form or subform, the combo box "window" would close and be
invisible.
So, drop down "list" of expense types, from which user would
select an expense type would only be visible to the user when the user
wanted it to be visible.
User would know "to see a choice of expense "types", he/she
should click in the narrow column and "pop", all the available expense
types would be visible, from which the user would select one expense
type. As soon as the expense type was chosen, the combo box window
would close.

Up to 30 choices of expense "types" could be displayed on one
screen (I've done this with schools, where there were over 220 choices
displayed, one under the the other, in which case the user must either
scroll down or, more commonly, just start entering the school name, in
which case the combo box jumps to that part of the drop down list).

Is this design you desire, where the expense "type" options are
displayed in a column, one under the other, when you write "(like a
spreadsheet)" and "the subform should populate with all expense types,
regardless of whether tblactivity contains the related records"?

So, the column of expense types displayed in the drop down list of the
combo box would display:
a) all the possible expense types
b) the subform design would allow a NEW expense type to be
entered, though not via this combo box.
c)the column of expense types would be DYNAMIC
(that is, if a NEW type of expense was added, the next
time the combo box was displayed, the new expense type would appear in
the drop down list of expense types, inserted in alphabetical order)

6) On viewing the drop down list of expense "types", the user would
click on one.
Two things would happen immediately:
a) the drop down list window would close
b) the expense "type" would "pop" into the subform row, under
the column heading "Expense type"

The user would see the expense type appear and realize they now had
a) the correct employee
b) the correct "type" of expense

7) The last step for the user would be to enter a dollar amount for the
expense in the same row and under the column heading of Expense Amount
(or whatever I called it at the start of this session).

8) The user could then either close the main form/subform or, if there
were more expenses to record for this employee, go to the next empty
row and repeat the process:
a) display the drop down combo box list of expense types.
b) select the expense type
c) enter the new expense amount

So, how does that suit your needs?
Strasser
PS: is anyone other than AlwaysHous...@ yahoo following this thread?
Please make your presence know by "replying" to this discussion.
Otherwise, since the topic is growing old, perhaps AlwaysHous and I
should finalize the discussion off the group discussion site.

I'm very happy to continue it here even if just one other person is
following along.
Thanks, Strasser

Nov 13 '05 #12

P: n/a
Your solution is ideal, but the requirement by the user of the software
is a bit weird. They would like to see all three rows in the subform
rather than the user going and selecting each expense type. The reason
is because the application anticipates 30-40 expense types and it would
be too much clicking by the user to the subform. Is there anyway to
create the subform with all the possible expense types listed? My
solution to this problem is that when the user clicks the employee, I
can invoke an event (afterupdate on employee combo box) to insert all
the possible expense types in the tblEvents for the employee. By this
way, the subform is populated with all the possible records with blanks
in the amount. My solution does not seem to me an efficient solution.
Please give me feedback on my solution and let me know of your solution
to my problem.
Again, THANK YOU very much for all the help. You have certainly help
me in many respects with this system development.

Nov 13 '05 #13

P: n/a
al***********@yahoo.com wrote:
Your solution is ideal, but the requirement by the user of the software is a bit weird. They would like to see all three rows in the subform
rather than the user going and selecting each expense type. The reason is because the application anticipates 30-40 expense types and it would be too much clicking by the user to the subform. Is there anyway to
create the subform with all the possible expense types listed? My
solution to this problem is that when the user clicks the employee, I
can invoke an event (afterupdate on employee combo box) to insert all
the possible expense types in the tblEvents for the employee. By this way, the subform is populated with all the possible records with blanks in the amount. My solution does not seem to me an efficient solution. Please give me feedback on my solution and let me know of your solution to my problem.
Again, THANK YOU very much for all the help. You have certainly help
me in many respects with this system development.


Strasser
No one else seems to be following this discussion, so I suggest we
continue via email.
Why?
I will email you a a small Access database showing my suggestion.
It's much easier for you to "see" my solution and for me to "see" yours
(than it is to describe it).
What might seem "weird" right now will appear to be quite
intuitive to the user when you open the application and go through the
steps the user would go through.
OK?
If OK, email ME at wi******************@hotmail.com and we will
continue.
Also, please "reply" here, so any observer will realize this is
the end of the Google Discussion Group. Anyone who comes upon this
discussion at a later date can email me and I'll forward the conclusion
of our discussion.

Also:
If OK, give me a list of 30-40 expense types, so I will
incorporate seeing all of them for the user in the example I send you.

After viewing your solution, I'll give you feedback on it.
My first reaction, I'm sorry to say, is negative, but I would like
to see it.

Strasser

Nov 13 '05 #14

P: n/a

Strasser wrote:
Strasser
Good!
One question:
The query including all fields from tblEmployee sounds fine.
The query including tblExpense MUST (in addition to all fields from
tblExpenses) also include tblActivity and all its fields.
I'm assuming you did this and just didn't mention it.
I'm assuming you created the two forms, each linked through the queries that formed them to the underlying tables.
1) frmMainFormEmployee (linked to tblEmployee thru the query)
2) name the 2nd form frmSubform, just so we are using the same
names. frmSubform is linked to both tblExpenses and tblActivity.
The next step is to incorporate the subform into the design of the main form in such a way that there is a link between a record in the main
form (a particular employee) and the the records in the subform (a
particular amount for a particular type of sale).
When you are done, you will be able to page through the records of the main form (one employee after another). As each employee appears, there will be rows in the subform, showing the amount and type of sale for each activity.
For any employee, you will be able to add a record to the next row to create a new activity (new amount and type of sale), edit an old
activity, or delete an old activity.
The number of rows per employee will indicate the number of
activities for that employee.
So, a main form with employee name and a subform window, with a
row for each activity is the goal.
Sound right to you?

Access is going to link the main form records to the correct subform
records for you automatically, although, if this fails, you can do it
manually.
Access will only do this if you have established a relationship
between the tables, which you have done.

Here we go:
Open frmMainFormEmployee in design mode.
You have to have room to insert the subform onto this main form.
Stretch out the background grid (the detail) to the right and drag it down.
Detail should now fill most of your screen.
This is where the subform will be placed.
Open the tool box > locate the icon for "Subform/Subreport" > click on the icon and release your finger > go to the upper left of where you
want the subfrom to be located on the main form > click and drag out a rectangular shape for the subform > the Subform Wizard window opens,
which is a big help!

It may NOT open if you have not installed this wizard, which is not
part of the typical Access installation. It is such a big help, that
you should go back to your MS Office CD and install the subform wizard, if, like most times, it was not previously installed.

Follow the prompts of the wizard, making these choices:
Use an existing form.
Choose from list > choose the default (not "none")
name for subform = frmSubform

I'm out of time for now.
Write and tell me how this part went.
If you can't get the subform wizard installed, you can do the
equivalent manually, but it is more tricky.
Good luck. You will be able to DO this!
Strasser


Stasser
I'm sorry but something has come up and I can't participate any more.
But I wish you luck.

Nov 13 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.