Simple Basic Access Form Solution Please HELP!

[alwayshouston]

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]

alwayshous...@y ahoo.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

[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>propert ies>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.Cl one
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?

[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>propert ies>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.Cl one
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?

[Strasser]

alwayshous...@y ahoo.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

[alwayshouston]

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!

[alwayshouston]

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

[Strasser]

alwayshous...@y ahoo.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: frmMainFormEmpl oyee

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

[alwayshouston]

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!

[Strasser]

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) frmMainFormEmpl oyee (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 frmMainFormEmpl oyee 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