Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Opening a form from another form (2000)

    New to Access development but I'm excited about the challenge. I'm creating a small sales database for a property developer. It's very basic at this point but I'm hoping to extend it some once I get the basic functionality sorted. I have a main form for a client profile and have opted to create a separate table for phone numbers--since potential buyers tend to have so many numbers--and a client e-mail address if they have it. I've created a relationship between the two tables based on a ClientID field.

    On the main form, I've included a button to click to open up the phones form. My problem--and it's a big one--is this: since I've yet to enter any data, there are no ClientID's in either table so clicking on the button just gives me an error message. I'm assuming there's no record to go to because, well, because there isn't. How can I fix this code so that a user can click on the button and launch the client's details or, if creating a new record, enter the client's details in the phone form? I'm guessing it has something to do with querying the field first for data and, if none exists, opening a blank form for entry. I'm not sure how to right that statement (though I have Alison Balter's book at my side!).

    I'll attach the code that Access wrote, just in case someone can help me modify it. Thanks for your help and advice.

    S.O.
    Attached Files Attached Files

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

    Re: Opening a form from another form (2000)

    You can test if ClientID is null and open the other form for editing or for adding a new record. Replace the lines

    stLinkCriteria = "[ClientID]=" & Me![ClientID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    by

    If IsNull(Me![ClientID]) Then
    DoCmd.OpenForm stDocName, , , , acFormAdd
    Else
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If

    Alternatively, you could warn the user that a new client must be created before phone numbers can be entered.

  3. #3
    Star Lounger
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a form from another form (2000)

    Haris:

    Thanks for your reply. I will give it a go tom'w. I hate to complicate things but I'm thinking maybe I need a separate form for data entry. I want to keep things simple--but they must be functional.

    Thanks again.

    S.O.

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

    Re: Opening a form from another form (2000)

    If you established the appropriate relationships between the tables, you still can't create a phone number until you have a client to link it to. It doesn't matter how many separate forms you have. The data is only useful if it relates to the person it belongs to. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  5. #5
    Star Lounger
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a form from another form (2000)

    charlotte:

    I completely agree--my design is, perhaps, flawed. I created a one-to-one relationship between the tblClientMain and tblClientPhones based on the ClientID field which is the primary key in both tables. Unfortunately, I've not got far enough along just yet to know if that relationship will work. Will the child table automatically take the ClientID field from the parent table or, as I fear, will they both end up with different ClientID's (generated from autonumber)?

    Newbie, newbie, newbie. Your advice is much appreciated.

    Thanks.

    S.O.

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

    Re: Opening a form from another form (2000)

    If you have a one-to-one relation, why do you need two tables? From your original description, I had assumed that you had multiple records in tblClientPhones for one record in tblClientMain. Unless you are in danger of exceeding the maximum number of 255 fields in a table, or the maximum record size of 2 KB, having two tables with a on-to-one relation will force you to do a lot of extra work.

    Having said that, if you decide to keep two separate tables, ClientID shouldn't be an AutoNumber field in both - if ever the tables get out of step with each other, it's impossible to correct that with two AutoNumber fields. You can leave the ClientID field in tblClientMain an AutoNumber field, but you should change the corresponding field in tblClientPhones to Numeric (Long Integer). Since you want a one-to-one relation, there should still be a unique key on it (it can be the primary key). You will have to delete the relation between the tables before you do that (you can't change the type of a field that is part of a relation) and re-create it afterwards.

    One way to ease the creation of a new record is to put the form based on tblClientPhones on the main form as a subform. If this makes the main form too crowded, you can use the tab control - put the "main" controls on one tab page, and th

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

    Re: Opening a form from another form (2000)

    As Hans pointed out, the relationship between the two tables can't be one-to-one if you have multiple phone numbers for the client in multiple records in the second table. If you have multiple *fields* for phone numbers in the second table, it will work but it is not a good design because it lacks flexibility (need a new kind of phone number, have to add a new field) and is harder to deal with in queries and reports.

    The parent key normally goes into a one-to-many child table as a foreign key, so it is NOT the primary key of the child table. The usual thing is to give the child table its own unique primary key, which may include the foreign key or may be an autonumber or any value that will not change. If you create a relationship in the relationships window and enforce referential integrity between the tables with cascading updates, that will insure that you cannot create a child record without a parent record and if the parent key is changed for some reason, the child table foreign key will be updated.
    Charlotte

  8. #8
    Star Lounger
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a form from another form (2000)

    Well, my original idea was to separate out phone numbers because potential buyers in luxury residential tend to have so many points of contact (primary number, home, business--sometimes two of each--and mobiles) plus an e-mail address. I realise now, of course, that I can create all of those fields in one record and then separate them out into different forms. I like the tabbed form idea because that sounds very tidy.

    I will get on with this--thank you for all of your helpful advice and information. It's much appreciated.

    S.O.

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

    Re: Opening a form from another form (2000)

    It's a better idea to have a phone number table with one record for each phone number for each person. You can record the type of number (home, work, fax, etc) in a separate field in that record and display as many or few as they have in a continuous subform on your form. That way, you don't have to add a new field if they come up with something like a second work phone number or an alternate fax number, you just add a new record for that person. This is the kind of stuff that makes relational design such a powerful tool.
    Charlotte

  10. #10
    Star Lounger
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a form from another form (2000)

    charlotte:

    this sounds very interesting. now that I've got my tab controls working (and there will be three of them, I think), a subdatasheet on the second page sounds like a good idea--especially since we don't always retreive every single number for each buyer. i'll work on this today.

    in the interest of not starting another thread (though maybe I should), what's up with colour in Access forms? there doesn't seem to be any colour options with tab controls. i've also noticed from viewing my prototype on different machines, that even with the same OS and the same version of Office, my colour scheme disappears. i've been reading about colour palettes and the palette property but it's not made a lot of sense yet. i'm using two colours, btw.

    thanks!

    S.O.

    P.S. Charlotte- I've come back and attached a screen grab of my relationships window. The lower left corner is still sketchy as I think I'll have to use some Event Procedure to show specific options once a ReferralSource has been chosen (if it's Newspapers, I'll want to show names of newspapers, etc.). I'm not too worried about that right now as that will come after basic functionality is established (methinks). I rethought the tblClientPhones table as you suggested above but I'm using the ClientID--established in the tblClientMain table--as the primary key in the tblClientPhones table. I'm hoping there's a way to pass that key once it is established in the Main table to the Phone table. There's probably a lot of holes in my thinking (equal to the holes in knowledge about this stuff) which is why I thought I'd post the relationships layout now--hopefully you'll point them out. I am trying and I have most of the weekend to work on this but I have to provide something functional by Monday or Tuesday. I'd really appreciate your advice. Thanks! S.O.
    Attached Files Attached Files

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

    Re: Opening a form from another form (2000)

    It's always a good idea to start a new thread for a new question and you've actually asked several in this post, but since you ask, you can't change the color of tab controls, you can only make the control transparent so that it shows whatever form background and rectangle color behind it. I don't know what you mean by a color scheme "disappearing", though. If you set a color for an Ancess object when you design it, the color will be there on another machine, although it may not look quite the same depending on the video setup on the other machine. If you're talking about a Windows color scheme, of course, that is peculiar to the individual machine.

    You've set the tblClientPhones up one-to-one with tblClient. That means you can only have a single clientphone record, which isn't what you want. You can't use the client ID as the primary key for tblClientPhones, you have to use it as a foreign key and allow duplicate values. You'll probably want to set the ClientID, PhoneNumer and PhoneType up as a multifield unique key in order to keep out duplicates, and you could make that the primary key as well. As far as populating the clientID in tblClientPhones, that depends on how you're creating the new record in it. If you have a subform for the phone records (use a continuous for, not a datasheet, and definitely not a subdatasheet) and use the clientID as the linking field, it's a simple matter to set the default value of the clientID control on the subform to point to the clientID on the parent form.
    Charlotte

  12. #12
    Star Lounger
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening a form from another form (2000)

    Charlotte:

    As always, thanks for your reply. I will mind my posting in the future.

    I set up the tables as you said and I hope it's fine. I've started linking parent and child objects, etc., so I'm sure that will get me into all kinds of trouble.

    Thanks again and have a good weekend.

    S.O.

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

    Re: Opening a form from another form (2000)

    About the disappearing colour scheme: if you use system colours, what you see depends on the user's settings in the Appearance tab of Display Properties. For instance, the default background colour in the standard format for Access forms is -2147483633, which is the symbolic value for the "Button Face" or "3D Objects" system colour. This is not a fixed colour, but changes with Windows display settings. It may be gray on your system, but magenta on another (yuck). All system colors are in the range of -2147483616 to -2147483648.

    On the other hand, if you set a colour by using the background/fill colour dropdown on the toolbar, you're setting a "fixed" colour - e.g. 255 should be red on every system. Still, monitors set to 256 colours can display fewer intermediate shades than monitors set to full colour.

Posting Permissions

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