Page 2 of 2 FirstFirst 12
Results 16 to 25 of 25
  1. #16
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,917

    A form can have one and *only* one RecordSource. That can be a Table or a Query that involves one or more joined tables. It is unlikely to be as you described:
    The record source is all the tables that make up the database with the exception of the one table where there is a one-to-many relationship. That is in a subform.

  2. #17
    batowl is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2010
    Posts
    86
    This is what the record source line says: SELECT [Subject Information].[Person ID], [Subject Information].[First Name], [Subject Information].[Middle Initial], [Subject Information].[Last Name], [Subject Information].[Date of Birth], [Subject Information].Sex, [Subject Information].[Parent's Name], [Subject Information].[Name of School], [Subject Information].[Name of School Other Comment], [Subject Information].[Grade of School], [Subject Information].[Role at School], [Subject Information].[Home Address 1], [Subject Information].[Home Address 2], [Subject Information].City, [Subject Information].State, [Subject Information].[ZIP Code], [Subject Information].[Daytime Phone Number], [Subject Information].[Evening Phone Number], [Subject Information].[Cell Phone Number], [Follow-Up Information].[Name of PHN Assigned], [Follow-Up Information].[Date of Physical Exam], [Follow-Up Information].[Examining Physician Name], [Follow-Up Information].[Chest X-Ray Done?], [Follow-Up Information].[Date of X-Ray], [Follow-Up Information].[X-Ray Result], [Follow-Up Information].[IgRA Test?], [Follow-Up Information].[Date of IgRA Test], [Follow-Up Information].[Reason For IgRA Test], [Follow-Up Information].[IgRA Test Result], [Follow-Up Information].[Further Follow-Up Needed?], [Follow-Up Information].[Case Closed?], [General Medical Information].[Physician Name], [General Medical Information].[Physician Practice Name], [General Medical Information].[High Risk Condition?], [General Medical Information].[High Risk Description], [General Medical Information].[Hx of Positive TST?], [General Medical Information].[Hx of BCG?], [General Medical Information].[Current Symptoms?], [General Medical Information].[Cough?], [General Medical Information].[Other Symptoms Description], [Priority Level].[Priority Level], [Screening #1 Information].[Screening 1 Location], [Screening #1 Information].[Screening 1 Location Other Description], [Screening #1 Information].[Date TST 1 Administered], [Screening #1 Information].[Date TST 1 Read], [Screening #1 Information].[TST 1 Induration (mm)], [Screening #1 Information].[TST 1 Result], [Screening #1 Information].[TST 1 Referred to Medical Provider?], [Screening #1 Information].[TST 1 Referred to Provider Comment], [Screening #2 Information].[Screening 2 Location], [Screening #2 Information].[Screening 2 Location Other Description], [Screening #2 Information].[Date TST 2 Administered], [Screening #2 Information].[Date TST 2 Read], [Screening #2 Information].[TST 2 Induration (mm)], [Screening #2 Information].[TST 2 Result], [Screening #2 Information].[TST 2 Referred to Medical Provider?], [Screening #2 Information].[TST 2 Referred to Provider Comment], [Screening Consent Information].[Screening Consent?], [Follow-Up Information].[Latent TB Diagnosed?], [Follow-Up Information].[Treatment Started?], [Follow-Up Information].[Date Treatment Started] FROM ((((([Subject Information] INNER JOIN [Follow-Up Information] ON [Subject Information].[Person ID]=[Follow-Up Information].[Person ID]) INNER JOIN [General Medical Information] ON [Subject Information].[Person ID]=[General Medical Information].[Person ID]) INNER JOIN [Priority Level] ON [Subject Information].[Person ID]=[Priority Level].[Person ID]) INNER JOIN [Screening #1 Information] ON [Subject Information].[Person ID]=[Screening #1 Information].[Person ID]) INNER JOIN [Screening #2 Information] ON [Subject Information].[Person ID]=[Screening #2 Information].[Person ID]) INNER JOIN [Screening Consent Information] ON [Subject Information].[Person ID]=[Screening Consent Information].[Person ID];

    Did I do something wrong here?

  3. #18
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,917
    So when you press the "..." button on that property, can you then go to the Datasheet View of the query and change anything?

  4. #19
    batowl is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2010
    Posts
    86
    I can go to datasheet view, but when I look at it I do not see any data in there, just the fields.

  5. #20
    batowl is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2010
    Posts
    86
    This just does not make much sense. If one purpose of a form is data entry, suppose you enter data for a person on Day 1, then you gather additional data on Day 3 and go back into the form to add that data... under these circumstances, you cannot do it because that person does not show up in the form anymore...

  6. #21
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,917
    Quote Originally Posted by batowl View Post
    I can go to datasheet view, but when I look at it I do not see any data in there, just the fields.
    That usually means no records meet your selection criteria.

  7. #22
    batowl is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2010
    Posts
    86
    Ok, so I need to know where to go from here:
    I have a database with one main table. There are several tables that have a one to one relationship with that main table. There is also one table that has a one-to-many relationship with the main table. I have created a data entry form for all these tables by doing the following:
    Created a form (let's call it Form 1) using the wizard that included the main table and all the fields from tables with a one-to-one relationship.
    I then created another form (Form 2) using the main table and the table with the one-to-many relationship. This yielded a form (Form 2) and a subform (for the table with the many side). I then deleted form 2 and added the subform to form 1.
    Everything works fine except that this form (form 1) will not display the data in any field that I previously entered between exiting and re-entering.
    Like I wrote before, this is a bit of an emergency, so any help provided would be greatly appreciated.

  8. #23
    batowl is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2010
    Posts
    86
    If starting over and putting all the fields from tables with one to one relationships inside the main table and only having one other table with the one-to-many relationship inside the database will solve the problem, then at this point, that is fine. Thanks.

  9. #24
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,917
    If the DataEntry property of a Form is set to YES thrn you cannot see previous records, only newly created ones until they are saved.

  10. #25
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    Quote Originally Posted by batowl View Post
    The record source is all the tables that make up the database with the exception of the one table where there is a one-to-many relationship. That is in a subform.
    "The record source (for the form) is all??? the tables that make up the database???" - ??? (not a good idea).

    Again, ideally and to prevent problems where you CAN'T save data on the form, you want to have 1 table as the recordsource per form (ie. don't link any other tables). When looking at the form's properties, if you click the 3 dots (...) for the recordsource to edit it, you should be able to open the recordsource and add a record in the recordsource query itself. If you can't add (or edit) any records to the recordsource query, then you won't be able to do so on the form itself either.

    Quote Originally Posted by batowl View Post
    This is what the record source line says: SELECT [Subject Information].[Person ID], [Subject Information].[First Name], [Subject Information].[Middle Initial], [Subject Information].[Last Name], [Subject Information].[Date of Birth], [Subject Information].Sex, [Subject Information].[Parent's Name], [Subject Information].[Name of School], [Subject Information].[Name of School Other Comment], [Subject Information].[Grade of School], [Subject Information].[Role at School], [Subject Information].[Home Address 1], [Subject Information].[Home Address 2], [Subject Information].City, [Subject Information].State, [Subject Information].[ZIP Code], [Subject Information].[Daytime Phone Number], [Subject Information].[Evening Phone Number], [Subject Information].[Cell Phone Number], [Follow-Up Information].[Name of PHN Assigned], [Follow-Up Information].[Date of Physical Exam], [Follow-Up Information].[Examining Physician Name], [Follow-Up Information].[Chest X-Ray Done?], [Follow-Up Information].[Date of X-Ray], [Follow-Up Information].[X-Ray Result], [Follow-Up Information].[IgRA Test?], [Follow-Up Information].[Date of IgRA Test], [Follow-Up Information].[Reason For IgRA Test], [Follow-Up Information].[IgRA Test Result], [Follow-Up Information].[Further Follow-Up Needed?], [Follow-Up Information].[Case Closed?], [General Medical Information].[Physician Name], [General Medical Information].[Physician Practice Name], [General Medical Information].[High Risk Condition?], [General Medical Information].[High Risk Description], [General Medical Information].[Hx of Positive TST?], [General Medical Information].[Hx of BCG?], [General Medical Information].[Current Symptoms?], [General Medical Information].[Cough?], [General Medical Information].[Other Symptoms Description], [Priority Level].[Priority Level], [Screening #1 Information].[Screening 1 Location], [Screening #1 Information].[Screening 1 Location Other Description], [Screening #1 Information].[Date TST 1 Administered], [Screening #1 Information].[Date TST 1 Read], [Screening #1 Information].[TST 1 Induration (mm)], [Screening #1 Information].[TST 1 Result], [Screening #1 Information].[TST 1 Referred to Medical Provider?], [Screening #1 Information].[TST 1 Referred to Provider Comment], [Screening #2 Information].[Screening 2 Location], [Screening #2 Information].[Screening 2 Location Other Description], [Screening #2 Information].[Date TST 2 Administered], [Screening #2 Information].[Date TST 2 Read], [Screening #2 Information].[TST 2 Induration (mm)], [Screening #2 Information].[TST 2 Result], [Screening #2 Information].[TST 2 Referred to Medical Provider?], [Screening #2 Information].[TST 2 Referred to Provider Comment], [Screening Consent Information].[Screening Consent?], [Follow-Up Information].[Latent TB Diagnosed?], [Follow-Up Information].[Treatment Started?], [Follow-Up Information].[Date Treatment Started] FROM ((((([Subject Information] INNER JOIN [Follow-Up Information] ON [Subject Information].[Person ID]=[Follow-Up Information].[Person ID]) INNER JOIN [General Medical Information] ON [Subject Information].[Person ID]=[General Medical Information].[Person ID]) INNER JOIN [Priority Level] ON [Subject Information].[Person ID]=[Priority Level].[Person ID]) INNER JOIN [Screening #1 Information] ON [Subject Information].[Person ID]=[Screening #1 Information].[Person ID]) INNER JOIN [Screening #2 Information] ON [Subject Information].[Person ID]=[Screening #2 Information].[Person ID]) INNER JOIN [Screening Consent Information] ON [Subject Information].[Person ID]=[Screening Consent Information].[Person ID];
    WOW - this is DEFINATELY a problem. Again, make your recordsource so it's only 1 table per form. If you need to update relational table data, this is where you create a subform or popup form to edit this data. If you need to limit a field with certain data from another table, this is where you utilize a combobox (or listbox) on the form. But you can't link in all the tables as the recordsource on 1 form and expect that form to be updatable. This isn't a good idea and will only cause problems for your form or data entry. Even if you are able to update a record on the form, with this kind of recordsource, I'd be worried about the relational tables getting updated as they should (you may want to run a check for orphaned or unmatched records in your relational tables). Also make sure your relationships between the tables are setup correctly as this is key as well (edit this via the Relationships Diagram) - and make sure your joins are correctly setup up between the tables (ie. 1 to many or 1 to 1 - see note below)

    As another highly recommended suggestion, I'd remove any spaces in the field/table names and definately remove the # (ie. the field named: Screening #1 Information) as the # in a field (or table) name is often problematic. Do the same for any fields named with a ? in the name or any other non-alpha characters. (ie. Remove the ? from within any field or table names!)

    Note: as a general rule for naming fields/tables, no spaces or odd characters such as: !@#$%^&*? in any field or table names. Preferably, use _ instead of a space.

    Your recordsource should instead be something as simple like:
    "Select * from SubjectInformation" (notice there's no joins to other tables)
    or (using criteria)...
    "Select * from SubjectInformation Where PersonID = " & Forms!MySearchFormName!PersonID & "" (where the search form allows you to select a PersonID and then open the form based on the PersonID selected.)

    For a subform or popup form, your recordsource might look like this for that form: "Select * from ScreeningInformation where PersonID = " & Forms!SubjectInformation!PersonID & "". (and you would set the default value property for the PersonID field on the subform or popup form to: = Forms!SubjectInformation!PersonID.)

    Even if you copy or rename the mdb or copy the objects into a new mdb, you will still have problems with the recordsource you indicated for the form.

    If possible, take a snapshot of your relational diagram and post it. I have a feeling your relationships need a bit of tweaking. (note that a 1 to 1 relationship between 2 tables will then only show records where there is a matching record in BOTH tables. Typically you usually want a 1 to many relationship where the 1 is your main table and the many side is your relational table). If you want to limit a relational table so that it only has 1 record maching the PersonID in the main table, you can set the PersonID in the relational table as the Primary key (or indexed with no duplicates allowed) which will then not allow any duplicate PersonID values (and make it a 1 to many versus a 1 to 1 relationship between the 2 tables). This will allow you to show all records in the main table and only those that match in the relational table and also prevent multiple records for the same PersonID in the relational table. Otherwise if it's a 1 to 1 relationship and you've linked both these tables in the recordsource of a form, you'll most likely have problems updating/saving data you entered on the form (and the question would be why these fields aren't in the main table versus in a relational table.)

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 63
    Last Post: 01-25-2013, 05:20 AM
  2. Replies: 1
    Last Post: 11-12-2010, 06:33 PM
  3. Replies: 0
    Last Post: 03-11-2010, 02:11 AM
  4. Replies: 9
    Last Post: 02-19-2010, 12:07 PM
  5. Replies: 9
    Last Post: 03-24-2009, 09:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums