Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Jul 2005
    Location
    Pennsylvania, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sub form issue? (Access 2003)

    The attached word document explains breifly the problem that I am having along with illistrations. I've had to zip the file because of the print screens to get it under the required 100k. My Access experience is somewhat limited at this point as I am learning it on the fly. Maintenance and mods to this database are now part of my job responsibility. I would greatly appreciate any help or suggestions that anyone can provide.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sub form issue? (Access 2003)

    Create a query that selects all records from tblProjectTasks with Task number = 0.
    Open the subform in design view.
    Set the Record Source to the name of this query.
    Save and close the subform.
    Open the main form in design view.
    Click once on the subform (don't click a second time, for that would select something in the subform).
    Activate the Data tab of the Properties window.
    Set the Link Master Fields and Link Child Fields properties to the name of the JON field/control.

  3. #3
    Lounger
    Join Date
    Jul 2005
    Location
    Pennsylvania, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub form issue? (Access 2003)

    Hans -

    Thanks for getting back to me so quickly. I had the query on the sub form already set up the way you suggested, so I was good to that point. I'm going to play around with different variations on the field links in the main form. I'm already getting varied results depending on the link. If I don't get back to you today, don't give up on me. I'll be leaving work soon but will communicate to you tomorrow my results.

    Thanks again

  4. #4
    Lounger
    Join Date
    Jul 2005
    Location
    Pennsylvania, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub form issue? (Access 2003)

    Hans -

    I've linked the Master and Child fields as you suggested also, but my results are inadequate.

    When the form is first opened, the 2 records for the JON '05FMAJ' appear in the sub form, which are a result of the ascending sort order and the inclusion of these records in the query. When I select a JON in the main form, the sub form does not change in response to the selection (i.e. selecting JON '05MFAP' does not put the 3 zero task records for that JON in the sub form. The sub form data remains static). When I select a task number for that JON, the data in the main form is updated correctly, as it was previously, but the sub form data disappears entirely. Not just the data but the fields as well, leaving just the headings.

    When I change the query for the main form to eliminate the zero task records, nothing appears in the sub form at all, either upon opening the main form or after selecting a JON and task. The query in the sub form is extracting the correct five zero task records when run separately. It's just not reacting at all when run in conjunction with the main form.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sub form issue? (Access 2003)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  6. #6
    Lounger
    Join Date
    Jul 2005
    Location
    Pennsylvania, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub form issue? (Access 2003)

    Here you go. The switchboard is removed as well, so disregard the error when opening.The main form name is frmProjectTaskInfo.

    Thanks

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sub form issue? (Access 2003)

    The bound column of your combo box corresponds to the lngMachineID field, not to strMachineJON, so you should have

    rs.FindFirst "[lngMachineID] = " & Me![cboMSPInfoLookup]

    The tblProjectTasks shouldn't have a strMachineJON field, it's superfluous since it already has lngMachID that corresponds to lngMachineID. You should link the tables on lngMachineID vs lngMachID.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sub form issue? (Access 2003)

    Other tips:
    - Set DatasheetName to [None] for all tables.
    - Remove duplicate and superfluous indexes in tables, they take up space and have a negative impact on performance.
    - Turn off Track Name AutoCorrect Info in the General tab of Tools | Options...
    - Include lngMachID in the various queries.
    - The second combo box has 4 columns, column widths for 3 columns and a row source with 2 columns - try to be consistent.
    See attached version.

  9. #9
    Lounger
    Join Date
    Jul 2005
    Location
    Pennsylvania, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub form issue? (Access 2003)

    The reason the field strMachineJON is included in the table is because the table will be populated by an imported file and that's the only reference to the JON that comes across with the file (the string value '05FMAP' and not the numeric id equivalent '264'). The numeric id (lngMachID) is appended later with another query.

    I see what you mean with the bound column.

    - I linked the 2 tables as you suggested. (tblProjectTasks with tblMachineInformation linking 'lngMachID' and 'lngMachineID').

    - Added the field 'lngMachID' to the both the querys 'qryProjectTaskInfo' and 'qryProjectNOMTasks' which are read by the main form and sub form respectively.

    - Changed the lookup to: rs.FindFirst "[lngMachID] = " & Me![cboMSPInfoLookup], because lngMachID is the field in the queries.

    - Set the Link Master Fields and Link Child Fields properties for the sub form to the 'lngMachID' field.

    And ................ IT WORKS!

    The key was the referencing of the integers (lngMachID) instead of the strings (strMachineJON) in all the above areas. You were dead on as usual.

    Hans, I can't thank you enough. Once again ........... well you know the rest.

    Many Thanks!

  10. #10
    Lounger
    Join Date
    Jul 2005
    Location
    Pennsylvania, USA
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub form issue? (Access 2003)

    Hans -

    Thanks for all the additional tips and the examples that accompany them. I will look them over carefully. I can use all the tips and assistance I can get. I don't have the luxury with this relatively new client, that I had with previous ones, of other IT poeple to bounce suggestions and ideas off of. Your assistance when called for is a tremendous help to me.

    Thanks again!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •