• get data from form to subform/Masochist (2000)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » get data from form to subform/Masochist (2000)

    Author
    Topic
    #403256

    I have a form with subform (form =riders and sub form=the races they are entered in). Whilst some data is constant, other data changes for each record. For example, a rider may have a default motor bike (details recorded in the riders table) but for one race, he may use a different bike (eg sidecar event). Is there a way I can
    1 automatically copy the default info (eg bike details) from the main form to the sub form
    2 but still allow that “suggested” data to be overtyped with the info for this particular event

    Mascochist bit . I am trying to rewrite a database that is used to record details of motor bike meetings. It needs to record who was entered for each of a series of races for different meetings, calculate the costs of that riders participation for that meeting, record receipts for payments made and a whole raft of reports including list of riders for events, numbers to be pasted on their bikes, summaries for banking etc. Basically I have the process working but am fine tuning and not only can I see some inefficiencies in the original design (eg manually calculated “sort Key” as the major link though the whole database, but I’m sure I am doing some dumb things too. Would this database be complicated enough to be used as a training exercise here but simple enough so as to not drag on forever. ie I would start from scratch and ask dumb questions about everything starting from design of the database right though to the finished product and implement suggestions with feed back as I went or is this a really dumb idea. If it is really dumb, I would appreciate any help on the form sub form idea
    TIA
    Steve

    Viewing 1 reply thread
    Author
    Replies
    • #809628

      Got the forms thing sort of sorted (lets not mention tautologies)
      Most people probably know this but for the few dummies like me: (I figure I have gotten so much help from the group, if I can feed back at the dummies level, it may help other dummies like me. Please correct any errors you gurus)
      Assume your main form is called Mainform and the field you want to copy from is field1 and your sub form is called subform and the field you want to put the data into is field2
      Firstly choose if you want to be able to overwrite the data (ie have Access present a suggested entry which you can override)
      Assume you DO NOT want to override:
      On the subform, right click on field 2, choose Properties and change the control source to “={Forms].[Mainform].[field1]” obviously not putting in the quotes. This will copy the data from mainform field1 to subform field2 – Note I think this only puts the data onscreen and does not place it into the underlying Table. In my case this is not an issue as I am linking the forms-subforms using a different field
      Assume you DO Want to override the suggested answer (or if my guess above is correct, want to save it to the table):
      do the same as above but put the =[Forms][Mainform][field1] into the default value for field 2. In this case, it appears that overriden or not, the data is saved in the underlying table

      Help gurus – where have I described this wrongly or what is the better method/ or traps for young players

      Steve

      • #809646

        The method using the Control Source property does not make much sense, since it doesn’t populate a field in the table, only a control on the subform. You would just be displaying a value from the main form in the subform, taking up real estate unnecessarily.

        The method using the Default Value property is excellent: it will fill a field automatically for new records, but leave existing records unchanged, and it will allow the user to change the value. The Default Value property will be updated correctly as the user moves from record to record in the main form.

        • #809654

          Thanks Hans – i realised the flaw not long after trying to test so have set all to default values and its working well – you’re right whiel the other method works it actually doesnt do anything useful
          Is there an equivalent method of getting default info from elsewhere – eg Bank Account details when my form only has costing but I want to enter receipts in a sub form from costings) and have the system automatically enter the bank account number from the Riders table
          Steve

          • #809660

            Do you have the same situation here, i.e. you want the system to fill in a default value for the bank account number, but the user can overwrite this with another number? If so, you can use the same method.

            • #810211

              Same situation in that I want defaults that can be overridden but the data is not available on the master, sub or sub-sub form I am working on. I do have a field that links to the table in which the data resides
              2 questions
              1 Can I link to a form which is not open or directly to a table or query and if so what is the format. While I can create an appropriate query, I usually get stuck with an non updateable form
              2 In the case of the form/subform approach I am using, does the data that I am making the default for the field in the subform actually have to appear in the main form or is it sufficiient to be available as a “potential” field on that form ie one available from the list of fields associated with that form
              TIA
              Steve

            • #810320

              1. You can NOT refer to a closed form. You can refer to a table using DLookup, but that is less efficient than having a field available in the record source. The syntax is:

              =DLookup(“FieldName”, “TableName”, “ID = ” & [ID])

              where ID is a unique key field (in this case assumed to be numeric.) Of course, you must substitute the appropriate names.

              2. If the Bank Account number is in the Riders table, it should be available in the field list of the main form, if I understand your setup correctly. You can put an invisible text box on the main form bound to this field if you don’t want to display it. That should enable you to refer to it on the subform.

            • #810394

              Thanks again – works like a charm
              back to an earlier question – I set up the default = for fields on my form and it worked well. As a test, I then deleted some of my fields from the form and the default still seemed to work – Have I just not tested thoroughly enough or is it just sufficient for the field I want to be available to the form rather than actually on it
              Steve

            • #810395

              Thanks again – works like a charm
              back to an earlier question – I set up the default = for fields on my form and it worked well. As a test, I then deleted some of my fields from the form and the default still seemed to work – Have I just not tested thoroughly enough or is it just sufficient for the field I want to be available to the form rather than actually on it
              Steve

            • #810321

              1. You can NOT refer to a closed form. You can refer to a table using DLookup, but that is less efficient than having a field available in the record source. The syntax is:

              =DLookup(“FieldName”, “TableName”, “ID = ” & [ID])

              where ID is a unique key field (in this case assumed to be numeric.) Of course, you must substitute the appropriate names.

              2. If the Bank Account number is in the Riders table, it should be available in the field list of the main form, if I understand your setup correctly. You can put an invisible text box on the main form bound to this field if you don’t want to display it. That should enable you to refer to it on the subform.

            • #810212

              Same situation in that I want defaults that can be overridden but the data is not available on the master, sub or sub-sub form I am working on. I do have a field that links to the table in which the data resides
              2 questions
              1 Can I link to a form which is not open or directly to a table or query and if so what is the format. While I can create an appropriate query, I usually get stuck with an non updateable form
              2 In the case of the form/subform approach I am using, does the data that I am making the default for the field in the subform actually have to appear in the main form or is it sufficiient to be available as a “potential” field on that form ie one available from the list of fields associated with that form
              TIA
              Steve

          • #809661

            Do you have the same situation here, i.e. you want the system to fill in a default value for the bank account number, but the user can overwrite this with another number? If so, you can use the same method.

        • #809655

          Thanks Hans – i realised the flaw not long after trying to test so have set all to default values and its working well – you’re right whiel the other method works it actually doesnt do anything useful
          Is there an equivalent method of getting default info from elsewhere – eg Bank Account details when my form only has costing but I want to enter receipts in a sub form from costings) and have the system automatically enter the bank account number from the Riders table
          Steve

      • #809647

        The method using the Control Source property does not make much sense, since it doesn’t populate a field in the table, only a control on the subform. You would just be displaying a value from the main form in the subform, taking up real estate unnecessarily.

        The method using the Default Value property is excellent: it will fill a field automatically for new records, but leave existing records unchanged, and it will allow the user to change the value. The Default Value property will be updated correctly as the user moves from record to record in the main form.

    • #809629

      Got the forms thing sort of sorted (lets not mention tautologies)
      Most people probably know this but for the few dummies like me: (I figure I have gotten so much help from the group, if I can feed back at the dummies level, it may help other dummies like me. Please correct any errors you gurus)
      Assume your main form is called Mainform and the field you want to copy from is field1 and your sub form is called subform and the field you want to put the data into is field2
      Firstly choose if you want to be able to overwrite the data (ie have Access present a suggested entry which you can override)
      Assume you DO NOT want to override:
      On the subform, right click on field 2, choose Properties and change the control source to “={Forms].[Mainform].[field1]” obviously not putting in the quotes. This will copy the data from mainform field1 to subform field2 – Note I think this only puts the data onscreen and does not place it into the underlying Table. In my case this is not an issue as I am linking the forms-subforms using a different field
      Assume you DO Want to override the suggested answer (or if my guess above is correct, want to save it to the table):
      do the same as above but put the =[Forms][Mainform][field1] into the default value for field 2. In this case, it appears that overriden or not, the data is saved in the underlying table

      Help gurus – where have I described this wrongly or what is the better method/ or traps for young players

      Steve

    Viewing 1 reply thread
    Reply To: get data from form to subform/Masochist (2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: