473,704 Members | 2,935 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Simple Basic Access Form Solution Please HELP!

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
14 3103
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

Nov 13 '05 #2
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?

Nov 13 '05 #3
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?

Nov 13 '05 #4

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

Nov 13 '05 #5
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
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

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

Nov 13 '05 #8
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
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

Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
2321
by: Lodewijk van Haringhal | last post by:
I'am new with javascritping not with programming. Is there nobody who can help me with ths simple promblem? :) Please, please give me a hint. Please help me with this script. I have two lists in my form. One to choose a directory and one to choose a picture. The script bellow works perfect without the directory choose function. I tried to make the directory choose function myself but I did not succeed. I think I made a mistake here:...
1
6024
by: Randell D. | last post by:
HELP! I am determined to stick with this... I'm getting there... for those who haven't read my earlier posts, I'm createing what should be a simple function that I can call to check that required fields in a form have values. I'm writing the values to the client using document.write only so that I can confirm that the values are there to be played with - this is not the final result so please (unless it's leading to the script failure)...
4
1855
by: Volumstein | last post by:
Hello, I am spoiled coming from programming in word and excel which offer the "record macro" tool that eliminated most basic syntax confusions that I had. unfortunately msaccess doesn't offer that option, and my luck in the groups has yielded little results for manipulating tables via vb code in a form. Could anyone illustrate a few examples for me? 1.) How would I Modify a specific cell in a table called "Mytable?"
9
12332
by: Pete | last post by:
Does anyone have a simple html vbscript or other type of snippet they can share that appends a record to a access database via ADO or DAO? I would like to allow users that don't have Microsoft Access a way of adding records to a access database from a simple web page. I don't want to have to setup ODBC or anything like that I just want to put the web page on the network for anyone to access. Most of the users would use the local access...
4
1533
by: Typpo | last post by:
Hi, This question is somewhat basic. Is it possible to call a method in frmMain, using another class? Here's a snippet: partial class frmMain : Form { private void MessageThis(String text) { MessageBox.Show(text);
2
8398
by: Hazzard | last post by:
I just realized that the code I inherited is using all asp.net server controls (ie. webform controls) and when I try to update textboxes on the client side, I lose the new value of the textbox when submitting the form to update the database. The server doesn't have the client side value any more. It seems to me that as I begin to write the client side javacript code for form validation and client side editing capabilities in order to save...
4
7083
by: bob lambert | last post by:
Help I am trying to deploy to another pc a vb.net std 2002 windows form application. I am confused. I created a project - windows form I built form, compiled and debugged. I created a deployment project (myproj installer) I added my windows app to the installer under myproj installer properties, I set bootstrapper
24
6334
by: firstcustomer | last post by:
Hi, Firstly, I know NOTHING about Javascript I'm afraid, so I'm hoping that someone will be able to point me to a ready-made solution to my problem! A friend of mine (honest!) is wanting to have on his site, a Javascript Calculator for working out the cost of what they want, for example: 1 widget and 2 widglets = £5.00
0
16010
by: =?Utf-8?B?S29uc3RhbnRpbg==?= | last post by:
I am currently working on the application that need to simulate basic authentication programmatically using user's credentials that are known. Basically, the need is for a single sign on with a third party application. The scenario is the following: a third party application (iChain I believe, from Novell) is used to authenticate the user against Novell eDirectory. Once authentication is complete a user is taken to a portal from which they...
0
8767
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
1
9026
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8977
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7891
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6605
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5931
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4439
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4701
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3135
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.