Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    Star Lounger
    Join Date
    Oct 2002
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how to change a subform's table (access 2000 9.0.2720)

    Hi. Sorry i am making my first database and have run in to a bit of trouble. (i don't know much about this prog at all)
    It is for a photographic studio, there is a table of customers. Linked to the customers, are the tables inquirys, accounts,sessions, orders, children etc. I have made a form so that we can easily see all the details/orders etc when a customer name/number is entered.

    although it would be great to show a table-style subform for all the sessions for the current customer, there are 3 or 4 types of sessions (weddings, model shoot, portraits etc.) which will need different information and hence different tables.


    My question is: can anyone tell me how i can get it to list each session for a customer in one table. Even if it was from one table and one of the fields specified "wedding" or "portrait" and there were buttons which popped up their record, that would be fine.

    For the same db, (and its a similar problem). the data flow is : a customer is created -> and inquiry is created, the inquiry specifys where the session will be "wedding" or "portrait" -> an entry is made in the "wedding sessions" table or "portrait sessions" table appropriately. my second question is: how do i make it so that when the user presses the button for turning an inquiry into a session entry, it reads the feild on the inquiry named "session type" and from that, it decides whether it makes a new "wedding session record" or "portrait..."
    file attached (probably wont help)

    thanks in advance

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

    Re: how to change a subform's table (access 2000 9.0.2720)

    The easiest solution would probably be to have one sessions table, with a field for the type (wedding, portrait, ...) and fields to store information for each type. For each individual session, fields not appropriate to that session would remain empty.
    On a form, you can use code to disable/enable or hide/unhide fields according to the session type. Or, you could create a separate subform for each session type, and use code to show the appropriate subform and hide the others.

    If you want to keep the separate tables for different session types, I would still create one Sessions table, with a unique SessionID (autonumber). It would also contain the info common to all sessions, such as the Customer ID, and also the session type. Each of the tables for the different types should also have a SessionID field (numeric, Long Integer), linked to the SessionID field in the Sessions table. The Sessions table makes it possible to display all sessions for a customer in a list, with the session type. Create a form or subform for each type, and use code to show the appropriate one when the user selects a session.

    I hope this will get you started. You will undoubtedly have more questions; you can post them in this thread.

    BTW, there was no file attached to your post. Please note that if you preview a post, you will have to re-enter the file name in the attachment box.

  3. #3
    Star Lounger
    Join Date
    Oct 2002
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to change a subform's table (access 2000 9.0.2720)

    Thank you, that makes sense.
    Is there any chance you could give me a hint as to the code which should be used to hide or unhide fields depending on the value of the "session type" then?
    (or show different forms)

    im guessing the best way to have it arranged would be to have a tabular subform with a "details" button at the end of every row. so in that case the best way would probably be to make different forms pop up depending on the "session type field". so what sort of code should i put on the event of the button?
    thanks HansV
    Adrian
    (have tried to attach file again)
    Attached Files Attached Files

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

    Re: how to change a subform's table (access 2000 9.0.2720)

    Hi Adrian,

    Unfortunately, I still work with Access 97, so I can't open the database you attached. Perhaps another Lounger will react; if not, can you save the database in Access 97 format and attach it?

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: how to change a subform's table (access 2000 9.0.2720)

    I tried to open it with Access 2000 and got an "unrecognised database format " error.
    Once unzipped the file had a size of 0 bytes, so I don't think you have managed to attached it!

    Controls have a "visible" property, so you can show or hide a control by settings its visible property to true or false.
    The code for this needs to be in two places- the afterupdate event for the "session type" field, and also in the oncurrent event for the form. The first is to show hide the right controls when entering new data, the other for when you are looking at old data. You could put all this code into a function and call the function in both places.

    The format would be like this
    if me!sessiontype = 1 then
    me!control1.visible = true
    me!control2.visible = true
    me!control3.visible = false
    me!control3.visible = false
    elseif me!sessiontype = 2 then
    me!control1.visible = false
    me!control2.visible = false
    me!control3.visible = true
    me!control3.visible = false
    else
    me!control1.visible = false
    me!control2.visible = false
    me!control3.visible = false
    me!control3.visible = true
    end if
    Regards
    John



  6. #6
    Star Lounger
    Join Date
    Oct 2002
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to change a subform's table (access 2000 9.0.2720)

    Another question is: I can get a field on a form to be a product of two other fields ie. total, deposit and balance, but it dosent seem to save the changes in the table. how do i make it save the changes, it doesnt seem to just be a matter of saving the record.
    thanks in advance again
    adrian

  7. #7
    Star Lounger
    Join Date
    Oct 2002
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to change a subform's table (access 2000 9.0.2720)

    <P ID="edit" class=small>(Edited by charlotte on 23-Oct-02 21:17. to activate link)</P>Thank you, that has worked wonderfully first try. i even used the a functiond for the first time.

    A new problem i find though is.. I have set up the main sessions table on the form so that it shows the "session id", "date created" and "Session type". then ive put a button next to each entry (tabular design) which brings up the corresponding "session details" record. Now the "session id" on the first table is autonumber, and the "session id" on the "session details" table is not. if i put corresponding numbers into the "session Id" field of the "session details" table then it comes up fine. but when i click the button on a record which doesnt already have a corresponding record, the form comes up with a blank field for the "session id". How do i make it so that the form which pops up inherits the "session id" from the other record when there is none matching already?
    I have tried forcing referential integrity between the two but that didnt make a difference.

    Also, records with the same "Session Id" should have maching "Session types" fields, so how should i do that.

    the file compressed is 200k so here it is http://www.hotshotz.com.au/newhsdb.zip

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: how to change a subform's table (access 2000 9.0.2720)

    Calculated controls are bound to the calculation, not to a field, so no, they don't insert the values into the table. Calculated values can be recreated at need, so they are not normally stored in tables.
    Charlotte

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: how to change a subform's table (access 2000 9.0.2720)

    AS Charlotte says, calculated values are not normally saved because they can re recalculated at any time.
    If you really did want to save it , you would bind the control the table field, but set its value via code.
    A control's control source is either a field in the underlying table or a calculation. If it is set to a calculation, then it is not connected to the table and so it is not saved
    So if I have three controls: total, deposit, balance, then balance = total + deposit ?
    If I want to save the balance bind it to the balance field, and in the "after update" event for both total and deposit put the code:
    me!balance = me!total + me!deposit.
    Regards
    John



  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: how to change a subform's table (access 2000 9.0.2720)

    What you need to happen is this: If there is a corresponding record show it, otherwise create it and set the values of the two relevant fields.
    This code does that
    <pre>DoCmd.RunCommand acCmdSaveRecord
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "SessionInformation"

    stLinkCriteria = "[SessionID]=" & "'" & Me![SessionID] & "'"
    If DCount("[sessionid]", "Sessioninformation", stLinkCriteria) = 1 Then

    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Else
    DoCmd.OpenForm stDocName, , , , acFormAdd
    Forms!SessionInformation![SessionID] = Me![SessionID]
    Forms!SessionInformation![SessionType] = Me![SessionType]

    End If
    </pre>

    However, as you have a 1-1 relationship between sessions and session information, it is not clear why you break this into a separate table. All the session info fields could be put into the session table without creating problems.
    If you want to keep separate tables, remove the session type field from the session info table. Only store each piece of data once! Whenever you want the session type get it from the sessions table.
    Regards
    John



  11. #11
    Star Lounger
    Join Date
    Oct 2002
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to change a subform's table (access 2000 9.0.2720)

    thankyou extra-very-much john, you're a master.

  12. #12
    Star Lounger
    Join Date
    Oct 2002
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to change a subform's table (access 2000 9.0.2720)

    Ok, then the next question is:

    with the orders tab, i have the balance already being calculated from the deposit and totalprice fields. But now I'm wondering how to make the total price field the sum of all the price values in the subform's table. Also, how do I make the price feild in each record of that tablr, the product of the unit price and quantity for the same record?
    Thirdly, the next tab accounts, should have the customers total oweings, so it needs to add the total field from each order record for a customer.

    heres the file again
    http://www.hotshotz.com.au/newhsdb.zip


    thanks in advance
    adrian

  13. #13
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: how to change a subform's table (access 2000 9.0.2720)

    Adrian

    There are quite a few issues here!
    One of the general principles of db design is not to store anything can be caclulated whenever you need it. (although sometimes this rule is broken if a lot of effort is required to make the calculation) So for instance, your orderinformation table contains unitprice and quantity fields. Multiply these together gives price, so price is not stored. So on the OrderInformation subform, a price field is included, but it is not bound to any field in the underlying table. Instead its control source is set to =[priceperUnit]*[quantity]

    To add these up to get a total price for the order, put an unbound control in the form footer and set its control source to be =sum([priceperUnit]*[quantity]). (Just copy the previous control to the footer and add the sum( ) bit into its control source. You won't be able to see this form footer unless you change the default view of the subofrm to continuous forms.
    The Total Price field can be calculated, so is not stored in the orders table either. So you can still have a total price field on the order form, but it is unbound, and set its control source to be =[OrderInformation].Form!total
    This just transfers the value from the subform footer to the main form.

    The balance field is then the difference between ptice and deposit, but as this field is set to a calculation, the value is not being stored.
    I couldn't find an accounts tab (in fact I couldn't find any tabs) so I couldn't look at that. but you don't seem to have a payments table. Ithink you need one.
    To work out total customer owings you will need a query that sums the individual owings over all orders.

    If you do want to store a calculated value, then the calculation is performed in code (either macro or vba) and the result put into the field. To do this, you then need to make sure that the value of the 'calculated' field is recalculated whenever it needs to be.

    I attach a cut down version with just the orders and ordersinfo forms and tables.
    Attached Files Attached Files
    Regards
    John



  14. #14
    Star Lounger
    Join Date
    Oct 2002
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to change a subform's table (access 2000 9.0.2720)

    John

    Perfect.
    I'd recently realised that the deposit field would be better replaced with a payments table, so i'll do that.
    the tabs i was talking about were all on the "CustomerActions" form.
    (i didn't know what foorters were for previously)
    thanks again!
    adrian

  15. #15
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: how to change a subform's table (access 2000 9.0.2720)

    I can now see the tabs on the customer actions form - I thought I checked every form earlier, but I couldn't find it then.
    Regards
    John



Page 1 of 3 123 LastLast

Posting Permissions

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