Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sub Forms (2000)

    I put the attached database together last year, with much help from the Lounge and it

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

    Re: Sub Forms (2000)

    A subform will only list records linked to the main record, it will not and can not display non-existing records. If you want records for each MaterialsID, you should create them for each SiteID. That would mean a lot of unused records!

    Alternatively, you could change the first text box in the subform to a combo box, so that the user can easily select a material. Only those used will be displayed. See attached (frontend only)

    Note: I recommend setting a unique index on the combination of SiteID and MaterialsID in TblInstalled, otherwise the user can enter the same combination several times.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Forms (2000)

    Hans

    Sorry for the delay in replying. I thought the boss would be happy with the manually entered, spreadsheet solution!

    What's happening here is that the database has been successfully recording quantities of materials ordered and quantities of materials collected for almost a year. Now the boss has calculated figures representing quantities of materials installed by measuring the cable route on the as built drawings.

    What I'm trying to do is create a form which will display the site name and all the material descriptions, irrespective of whether an item of materials has been used at the displayed site. A blank field/text box on the form, against each materials description will enable the boss to enter his calculated figures representing quantities of materials installed. So I don't want to display non-existing records, I want to display existing records and add additional information to them. I would like to list the materials descriptions rather than use a combo, to allow for situations where there is an installed quantity but no ordered/uplifted quantity. (It could happen!)

    I could just enter these figures directly into the table and display the results on forms and in reports. But I would like to develop the database so that the users enter the data and I just do the design, the fun bit!

    So firstly, am I right to put the new InstalledQuantity field in a table of it's own? (Not shown in the relationships in the quickly knocked up example database attached above 'cos I forgot to add it) or can it go in the TblItem table? Secondly, how can I make a blank data entry form display existing data too? Finally, further to your note above, do you mean a field in addition to the InstalledID field in the TblInstalled table?

    Hope this makes sense!

    Regards

    Graeme

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

    Re: Sub Forms (2000)

    See the attached (you'll have to relink the tables in the frontend). You will have to run the update query ONCE to create records in tblInstalled for all existing sites.

    The new form frmSite lets the user define a new site. When the user clicks OK, a record will be created in tblSite and corresponding records will be created in tblInstalled.

  5. #5
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Forms (2000)

    Cheers Hans

    By a strange quirk of fate or maybe the company firewall, I can't open Lounge thread attachments! so I'll have a look tonight at home. (Don't tell IT but the firewall thinks it stops all web based email too. Nobody seems to have told them about Gmail yet though!)

    Regards

    Graeme

  6. #6
    2 Star Lounger
    Join Date
    Sep 2007
    Location
    Chatham, Kent, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub Forms (2000)

    Hans

    Cheers, I

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

    Re: Sub Forms (2000)

    Create a totals query based on TblInstalled to calculate the sum of InstalledQuantity for each MaterialsID.
    Next, create a query based on this query and on TblItem, linked on MaterialsID.
    Create a calculated column in this query that subtracts SumOfInstalledQuantity from UpliftedQuantity.

Posting Permissions

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