Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    get data from form to subform/Masochist (2000)

    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
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: get data from form to subform/Masochist (2000)

    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
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

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

    Re: get data from form to subform/Masochist (2000)

    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.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: get data from form to subform/Masochist (2000)

    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
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

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

    Re: get data from form to subform/Masochist (2000)

    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.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: get data from form to subform/Masochist (2000)

    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
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

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

    Re: get data from form to subform/Masochist (2000)

    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.

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Now-Luanda, Angola, Originally - Anna Bay, New South Wales, Australia
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: get data from form to subform/Masochist (2000)

    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
    Dummy from Down Under <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

Posting Permissions

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