Forms: not working right with tables.

I am not totaly good with access. The problem is: I have many tables
setup and working like I need them to. This is a church project. Table
1 is linked to table two. Table one stores only address, home phone
and stuff related to family's that live at the same address and table
two stores names, cell phone, birthdays and stuff related to a singel
person. When I create a form to enter this information it works good
like a charm. The problems start with table two and three. Table three
will be used to keep a recoded of the attendaince. All tables are
setup and working and linked with each other correctly. Here Is the
problem when I create the form and have it to show the ID, First Name,
Last Name from table two it works with out any problems but then when
I add the fields from table three which is date and attendaince status
then that's when things stop working cause it will no longer pull any
information from table two as if there is no information in the table.
But as soon as I remove the 2 items from table three then it starts to
work right by pulling info from table two. What I need it two do is to
pull information from table two and push information to table three.

This is what I am trying to get the form to look like:



Sunday Morning Attendanice                 "date" to auto fill all
records for the current day.


ID           name1            "Check Box" to enter present or absent
for each person.
ID           name2            "Check Box"
ID           name3            "Check Box"
ID           name4            "Check Box"

Someone please help.
Thanks-God Bless,
Brandon "scott15597"

0
Scott15597
11/19/2009 7:09:21 AM
access.forms 6864 articles. 2 followers. Follow

7 Replies
639 Views

Similar Articles

[PageSpeed] 13

Brandon,

Look in the Help file and learn about using Sub Forms.

Anytime you have a one-to-many relationship between your tables, you will 
want to use a Sub Form that is embeded in your main form that will display 
the "many" side of the relationship.  Using a sub form is not restricted to 
having only one. You can have more than one sub form.

There is a Master and Child link property for a sub form that tells the sub 
form how it relates to records in the main form.

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page: 
http://www.askdoctoraccess.com/DownloadPage.htm


"Scott15597" wrote:

> I am not totaly good with access. The problem is: I have many tables
> setup and working like I need them to. This is a church project. Table
> 1 is linked to table two. Table one stores only address, home phone
> and stuff related to family's that live at the same address and table
> two stores names, cell phone, birthdays and stuff related to a singel
> person. When I create a form to enter this information it works good
> like a charm. The problems start with table two and three. Table three
> will be used to keep a recoded of the attendaince. All tables are
> setup and working and linked with each other correctly. Here Is the
> problem when I create the form and have it to show the ID, First Name,
> Last Name from table two it works with out any problems but then when
> I add the fields from table three which is date and attendaince status
> then that's when things stop working cause it will no longer pull any
> information from table two as if there is no information in the table.
> But as soon as I remove the 2 items from table three then it starts to
> work right by pulling info from table two. What I need it two do is to
> pull information from table two and push information to table three.
> 
> This is what I am trying to get the form to look like:
> 
> 
> 
> Sunday Morning Attendanice                 "date" to auto fill all
> records for the current day.
> 
> 
> ID           name1            "Check Box" to enter present or absent
> for each person.
> ID           name2            "Check Box"
> ID           name3            "Check Box"
> ID           name4            "Check Box"
> 
> Someone please help.
> Thanks-God Bless,
> Brandon "scott15597"
> 
> .
> 
0
Utf
11/19/2009 1:13:02 PM
Hi,

Using sub-forms work but is there a way to have the list of names from
table two to show up all at once in a list and then have a check box
to tell table three that there present or absent along with entering
the date only once to take effect on all names or do I have to do one
record at a time?

Thanks-God Bless,
Brandon "scott15597"
0
Scott15597
11/19/2009 1:55:21 PM
I'm having trouble sorting out just what you are trying to do, but in general
the only information from Table2 that needs to be in Table3 is the linking
field value, which you do with the Link Master and Link Child properties of
the subform control (assuming the tables are properly related).

If I understand correctly you want a list of all parishoners from which to
select the names of those who attended.  You say that all relationships are
set up correctly, but you have not been specific.  You probably should have a
Services table, with a related Attendance table.  I will give the tables
names, as it is difficult to think about which table number goes with which
entity.


tblPeople
   PersonID (primary key, or PK)
   LastName
   FirstName
   etc.

tblService
   ServiceID (PK)
   ServiceDate
   etc.

tblAttendance
   AttendanceID (PK)
   ServiceID (Linking field to tblService)
   PersonID (Linking field to tblPeople)

Note that you need relationships from tblAttendance to both tblService and
tblPeople.  One Service may be attended by many people, and one person may
attend many services.

The main attendance form is based on tblService, with a subform based on
tblAttendance.  The Link Master and Link Child properties of the subform
control are ServiceID.  A combo box or list box bound to PersonID gets its
Row Source from a query based on tblPeople.  Typically this would be a two-
column query:  the first column is PeopleID, and the second something like:
FullName:  [LastName] & ", " & [FirstName].  The combo box Column Count is 2,
the Column widths are something like 0";1.5", and the Bound Column is 1.  You
are storing only the PersonID; however you are selecting a name, not a number,
from the combo box.

Scott15597 wrote:
>Hi,
>
>Using sub-forms work but is there a way to have the list of names from
>table two to show up all at once in a list and then have a check box
>to tell table three that there present or absent along with entering
>the date only once to take effect on all names or do I have to do one
>record at a time?
>
>Thanks-God Bless,
>Brandon "scott15597"

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200911/1

0
BruceM
11/19/2009 3:13:04 PM
Hi,

This is the way I have my tables setup.

Table 2                            Table 3                   Table 4
AttendainceID---------------AttendainceID
                                       VistiorsID-------------
VistiorsID
First Name               Attendaince Status          First Name
Last Name                       Date                       Last Name

I need a form that will show table 2, enter information in table 3,
table 4 I need to be able to enter new information and/or use exsting
information.

There are more tables like table three. Table three rebasents Sunday
Morning, Sunday Night, Wednesday Night, Special Services.

Or should I redo my tables and if so how should I?

Thanks-God Bless,
Brandon "scott15597"
0
Scott15597
11/19/2009 5:01:15 PM
Earlier you wrote "table two stores names, cell phone, birthdays and stuff
related to a singel person".  Now you are describing AttendanceID, which is
not an attribute of a person.  Remember, tables store information about a
single type of entity.  Address and Phone Number may be thought of as
attributes of a person.  Attendance at an event is not.

I do not know where VisitorsID came from or how it fits into the picture.

The description in my previous posting describes how to set up the tables and
the form/subform you need to make this work, from what I understand of your
needs.  The Services table can have an extra field to describe the service
(morning, evening,etc.)

If a form "shows" a table, that table is the one into which data are entered.
You can push data into another table, but that is rarely the best approach.

You stated that your tables are "setup and working and linked with each other
correctly", but that is difficult to evaluate without knowing how they are
set up and related.  However, I suspect the database structure is not as
solid as it could be.

Again, I suggested a way to make this work.  If it does not, more details are
needed about the tables and structure.

Scott15597 wrote:
>Hi,
>
>This is the way I have my tables setup.
>
>Table 2                            Table 3                   Table 4
>AttendainceID---------------AttendainceID
>                                       VistiorsID-------------
>VistiorsID
>First Name               Attendaince Status          First Name
>Last Name                       Date                       Last Name
>
>I need a form that will show table 2, enter information in table 3,
>table 4 I need to be able to enter new information and/or use exsting
>information.
>
>There are more tables like table three. Table three rebasents Sunday
>Morning, Sunday Night, Wednesday Night, Special Services.
>
>Or should I redo my tables and if so how should I?
>
>Thanks-God Bless,
>Brandon "scott15597"

-- 
Message posted via http://www.accessmonster.com

0
BruceM
11/19/2009 6:10:04 PM
Hi,

Can I send you an email with my access file so you can look at it and
be able to better help me.

If so where can I send it to?

Thanks-God Bless,
Brandon "scott15597"
0
Scott15597
11/19/2009 8:53:47 PM
You could send it to bamoob_AT_Yahoo_DOT_com, but I can't promise I will be
able to look at it carefully any time soon.

Scott15597 wrote:
>Hi,
>
>Can I send you an email with my access file so you can look at it and
>be able to better help me.
>
>If so where can I send it to?
>
>Thanks-God Bless,
>Brandon "scott15597"

-- 
Message posted via http://www.accessmonster.com

0
BruceM
11/19/2009 9:14:00 PM
Reply:

Similar Artilces:

Special Pasting a work book with many sheets and formulas
I have a workbook with many sheets that all have formulas and links to other data. I want to save the workbook as another name with all the worksheets keeping the values only (no links or formulas). Is there a quick way to do this for everysheet without having to special paste every sheet in the workbook. So can I save everysheets data values at workbook level. See this page for a code example http://www.rondebruin.nl/values.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "lex63" <lex63@discussions.microsoft.com> wrote in message news:ED708...

Proper Table(s) Layout
I have created a couple of Dbs which the table structure seemed to simply fall into place, it was just logical in my head. That said, I am working on a new db and for some reason I am doubting myself and wanted a second opinion. The db is basically a contract db to input all the info, and there is a lot of info, for each contract. Where I am 'lost' is the fact that the contracts are broken into categories: clients, components,engineering, warantee... For all of the components (with the exception of clients) there are a number of fields but only 1 entry per contract....

backup file won't work!!
OK, now I've really had it! This is my last resort! Several years ago, while using Money 97, our computer crashed. We had backed up every time we used money, so I thought we were OK. However, we could never get the backup file to pull up after fixing the computer and everything was lost. I vowed that this would not happen again, so I tried backing up on a ZIP disk instead, and then on CDs. I also got on this newsgroup and got the advice that I needed to first copy the backup file onto the desktop and open it from there. I tried doing that with the old program, and it seeme...

Can't create Organizational Forms Library in Exchange 2003 with SP
Hello, I cannot create an Organizational Form in EFORMS REGISTRY folder (from First Administrative Group->Folders->Public Folders->EFORMS REGISTRY in ESM). When I right-click the EFORMS REGISTRY folder and select New, there is no Organization Form. Instead, I only see Public Folder in the popup menu. Do you have any idea why Organizational Form menu does not show? My Exchange Server is Exchange 2003 with SP2. The login user is Administrator. Could you please help me? Thank you very much. Yang Is that account member of "Enterprise Admins" group? Yang Zhang wrote: &...

Table link documentation
I am having trouble trying to locate A/P check data that has project related costs. I found the check data but it is does not indicate the projects, I found the project data but can not determine thye logical link between the two tables, I may be using the wrong tables the tables I am using are PM80500 and PA31102. Is there any documentation of how all the tables in the system are logically link. I am trying to write reports in MS Access, but there are 1500+ tables in GP (version 10) -- Dave F In an effort to find the correct table you can do a number of things (believe me I do)....

Pass parameter from FORM to QUERY
I have an append query that I trying to call from a cmd btn...but it prompts me for the parameter(ie QuoteID)... How do I tye it into my call: Dim stQueName As String stQueName = "Quote Query" DoCmd.OpenQuery stQueName, acNormal, acEdit On Apr 13, 12:56 pm, jlt...@hotmail.com wrote: > I have an append query that I trying to call from a cmd btn...but it > prompts me for the parameter(ie QuoteID)... > How do I tye it into my call: > > Dim stQueName As String > stQueName = "Quote Query" > DoCmd.OpenQuery stQueName, acNormal, acEd...

Office 2007 forms
I am creating a form with office 2007, will those people who do not use office 2007 be able to fill in my form? should I save it in a particular format? thanks Provided you start from the normal template, don't use fonts that were introduced with Word 2007, and save the form in Word 97-2003 document format, anyone with Word 97 or later should be able to open it. Use only the legacy form fields, to which end http://gregmaxey.mvps.org/Classic%20Form%20Controls.htm will make things easier. -- <>>< ><<> ><<> <>>< ><<...

How to make Run Integration work in GP 10
Can anyone tell me how to make the Run Integration tool work in GP 10. We just did our first client upgrade to GP 10 and the client has only 1 integration that they used to be able to run via the RUN INTEGRATION Tool. Any help would be greatly appreciated. -- Tammy Chavez I have the same probelm...did you get any answers to this? I have to go into Integration Manager to run it b/c Tools>Run Integration does nothing. I even checked the ini files to make sure the path is correct. "Tammy Chavez" wrote: > Can anyone tell me how to make the Run Integration tool work in...

Can the data in a chart table be right justified?
Ecxel 2003 and previous versions of the product center the data in the data columns. Can the data in the columns of a chart table be right justified? In article <DABF738B-6C0D-458B-B082-FA9BD8F126A7@microsoft.com>, =?Utf- 8?B?c2FtIGVhZ2xl?= <sam eagle@discussions.microsoft.com> says... > Ecxel 2003 and previous versions of the product center the data in the data > columns. Can the data in the columns of a chart table be right justified? > Have you tried to format the table? If yes, and you haven't been successful it is probably because XL allows very limited cust...

Duplicate record in RM tables
We experienced an issue in Apply Sales Document that may have caused a duplicate record somewhere. We found this when running Paid Sales Transaction Removal and received this message: Violation of PRIMARY KEY contraint PKRM3101. Cannot insert duplicate key in object RM30101. I ran the RM duplicate tool found in the automated help area of this website and found the following: --- Begin copy here ---- Duplicates between RM Open and RM History Document #: 07-003021-17 Customer #: 079100 RMDTYPAL #: 7 --- End copy --- It looks like the duplicte tool also logs the qu...

Data migration - Adventure Works
Hiya... I have a company where the adventure works db has been used and had a lot of data populated into the system. We have now purchased MSCRM and have obtained the company reg keys. What is the easiest way to get the data from the 1 system to the next? We will be establishing a new AD domain and users for the new system.... Data Migration Framework? Redeployment Framework? ;) redeploment tools http://www.microsoft.com/downloads/details.aspx?FamilyID=bfced393-61db-49af-9a50-4a90b311fa7d&DisplayLang=en -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "funboy...

How to track ActiveControl.Name when switching records in form with multiple subforms
I need to have a global variable always contain the name of the current form field. This bit of code is attached to the GotFocus event of all fields and the Enter event of all subforms: gxCurrentField = Me.ActiveControl.Name However it doesn't work properly when changing records in a subform. My parent form contains two subforms in a many-to-many relationship. The above variable usually ends up containing the name of the first field in the second subform when switching records in the first subform. How to correctly code this? Or is there some native variable I'm not aware of? I...

140 MB file went to 5.08 MB after editting 1 table
Hello All - I need some ACCESS insight...please... Several years ago, I built an access db to track my business scheduling and accounts payable/receivable. So this database is EXTREMELY IMPORTANT TO ME. The file has grown to 140 MB. Today I made a copy of the file and then edited my calendar table. I removed all columns which had 2006 data (72 totals columns) - the table had about 144 columns originally. I then added 72 columns with 2008 headers. These columns are now blank since I have not added any 2008 data yet. Afterwards, I looked around and everything looks good - my 2007 data is the...

cursor missing from form textboxes
I believe that I know 98% of the little intricacies of Publisher, but I'm stumped on this one. I created a website with a contact page and none of the standard textboxes will show a cursor to indicate with field you are in (I've even made the site live to double check). If you tab between textboxes, the tabbing works, as does typing, there is just no visual cursor to indicate where you are. If you click on the first textbox and type, all is good you can tab to the next box and type, and it's fine. I'd like to have the visual cursor show and can't get it. I also have ...

Pivot Table Defaults
In the pivot table field list, whenever I create a new pivot table and I am inserting fields into the value area, I generally get as default field setting the 'Count' value. Is there a way to format the spreadsheet to make Excel recognize the data as all numbers so it defaults to the "Sum" function as opposed to "text"? Hi The rule that the PT Wizard adopts is, If all the values in the field being added to the data area are Numeric, then it uses Sum. If any of the values are Text or BLANK, then it uses Count. It sounds as though you have defin...

Like a pivot table
Hello every body I'm first time requesting in this group, so I opologize in advance for any mistakes or something annoying I repeat what I have sent before 10 min because I see it unclear when it goes to news group If any one can help me I'm working with data which most of it comes like a table with feilds as columns and records as rows. I want it to be as many rows with each feild an example what is exist name age Joining Date Tele John 20 Jun-90 4321251 Iqbal 30 Jul-95 6583752 George 40 Sep-85 7843125 What I wa...

VBA form
Im using a form/macro that i linked to through another thread and a tailoring it to my needs. I basically have it doing what i need but cant get a button to do what i want. When the user starts the macro i brings up the form with a message and 2 buttons (Continue/Cancel). Whe continue is pressed i want the message on the form to change to aniothe message and so on until all messages are shown. Ive attacehed what i working on and apprecaite any help offered. Thank yo +------------------------------------------------------------------- |Filename: Excel.zip ...

Duplicating one Field from One table to Another
Hi - I have two tables - one position, one personnel - which has a 1-to-many relationship (1 position record to many personnel records). The department had a new requirement which made it necessary to change some coding (I inherited this). I'm using tab forms so that when a position is pulled up, you can click on the tab that has the personnel information (if there is any). There is a button on the Personnel form that allows the user to add a new Personnel record. Since I am using an Auto-number field in the Position table (which doubles as the PK) the functionality is fine. Wh...

Scroll Wheel effecting a form
Is there any way to keep a mouse scroll wheel from having an effect on a form? This is a single form for data entry and if are part way thru the form and for what ever reasom use the scroll wheel all the fields on the form are effected (they dissapear - like going to the next new record). My suggestion to get a mouse without a scrool wheel was not met with enthusiasm. :) Any help here will be appreciated. Thanks in advance The scroll wheel is moving you through the records. Nothing except your view of the records is disappearing. The records are still there. I wouldn't think y...

Bank One Download is not working
We are trying to download transaction data directly from Bank One and the file is not being recognized by Money - i.e. the Bank One website states that the download is complete, but Money does not recognize it. Note that a file is being saved on our hard drive but we can't figure out how to read it. It is entitled mnyimprt.exe-(numerous numbers and letters follow). In microsoft.public.money, Miki wrote: >We are trying to download transaction data directly from >Bank One and the file is not being recognized by Money - >i.e. the Bank One website states that the downloa...

Pivot Table Summary Error?
Greetings, Sorry, wasn't sure which subforum to post this in, as there's nothing dedicated to pivot table. My dilemma is as follows: I have a spreadsheet where data is entered daily. For my example, just imagine a spreadsheet with three columns: date, date, delay. Two date columns are used for the pivot table. The pivot table is displayed with month and weekly ranges as the row headers. There is a field in the pivot table that is summarized as a MAX of one of the source data columns. Now, the problem I am coming across is the monthly summary MAX is not c...

Compact Now feature doesn;t seem to work...
I still have a 600+ MB in-box. ANyone know hwta might keep it from compacting? I did empty the trash. Compacting only works when there is a certain percentage of white space in the file (I think it's something like 4%)...if there isn't, Outlook doesn't think there's anything to compact. Also, if you leave Outlook open long enough, background compaction will be taking place. -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:5feb01c474ce$23bb29d0...

How can I get the trial balloon to work?
I've downloaded the Works program trial balloon over and over, but when I try to use the program it tells me to use a product key and the key I was given (every time) doesn't work. ...

One table with two date fields returning records in a date range
I have a table that has several columns, two of which are date fields. A start_date and comp_date for start and completion date. I need to run a query that will return all the records between two dates selected by a user. Not hard, piece of cake. The problem I am having is that my query isn't returning all the records that overlap a given date range. For example: I select a date range: June 1 to September 1. My query will return 10 records that start and stop between June 1 and September 1. This is good and correct, but.... ...I have several records that start before June ...

custom forms #3
Hello, I would like to use forms customised on outlook on a pocket pc. I wish to send an e-mail using a customised form, and have the customised e-mail appear correctly on a pocket pc once it has downloaded the e-mail via gprs. So, my question is, is it possible to install customised forms on a pocket pc? many thanks in advance. Mike. Not Outlook forms. Maybe something else; ask in a Pocket PC group. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx...