Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    35

    Unanswered: Forcing Subform to only Capture 1 Record

    I have a subform "sbfrmProcedures" in the main form "Cases". I want the user to be able to enter at least 0 and at most 1 record in this subform. Is there any way to do this?

    Secondly, I want to be able to tab out of the subform to the main form (continuing with the tab order in the main form). I know there is Ctrl-Tab, but I'd rather have a VB solution to allowing the user to tab out of the last control in the subform that will setfocus to the next control in the main form. How can this be done? Is there a way to say "If the user hits tab while in the last control of the subform, move to the main form controlX?"
    Last edited by tkchung; 10-03-05 at 16:50.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    1) I would put something on the form current event. Check the form recordsets recordcount property. If 0 then dataentry = true.
    i.e.
    Code:
     
    Me.DataEntry = Me.Recordset.RecordCount
    2) Play around with the key down event for your last text box. TAB = 9 (Keycode). If the keycode is 9 then use the .SetFocus method.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    (Assuming that, the sub form is entering records on a separate table) I'm far lazier than that:-
    I'd change the join type to "one-to-one"on the tables to:-

    "Include ALL records from 'DTCountry' and only those records from 'countrycopy' where the joined fields are equal."
    option 2 - if my memory serves me right. Conceivably you may need to delete the exisitng RI link, check the data is valis for your new conditions and then create a one to one link

    BTW I'm not aware that you can ever have a negative (even in Access) number of records so you will have no more than 1 record.

    The danger of pootleflumps otherwise excellent approach is how you cater for the possibility that sime muppet may decide to add records in a direct tabel edit - something which despite best endeavours is virtually impossible to exclude within the access environement (unless you are using security imposed by a server).
    Last edited by healdem; 10-04-05 at 09:34.

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by healdem
    (Assuming that, the sub form is entering records on a separate table) I'm far lazier than that:-
    I'd change the join type to "one-to-one"on the tables
    I was just about to suggest the same. Although whenever I hear one-to-one I ask "Is this a candidate for putting all the data in one table" thus sorting the problem (I know there are good reasons for not always wanting to do this).

    Quote Originally Posted by healdem
    "Include ALL records from 'DTCountry' and only those records from 'countrycopy' where the joined fields are equal."
    option 2 - if my memory serves me right.
    This is only for queries surely ? I'd make sure that both tables have a Primary Key and that the Primary Key in both are the fields used for the relationship. Then a drag and drop in the relationship screen will automatically identify the relationship as one-to-one.

    Quote Originally Posted by healdem
    Conceivably you may need to delete the exisitng RI link, check the data is valis for your new conditions and then create a one to one link
    Yes, definately, although I wonder what would happen if we left it !?!

    Chris

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    The danger of pootleflumps otherwise excellent approach is how you cater for the possibility that sime muppet may decide to add records in a direct tabel edit - something which despite best endeavours is virtually impossible to exclude within the access environement (unless you are using security imposed by a server).
    That is ever so kind - especially as it was wrong

    Code:
    	If Me.Recordset.RecordCount = 0 Then
    		Me.AllowAdditions = True
    	Else
    		Me.AllowAdditions = False
    	End If
    I like your solution though - better to use RI.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by howey
    Although whenever I hear one-to-one I ask "Is this a candidate for putting all the data in one table" thus sorting the problem (I know there are good reasons for not always wanting to do this).
    Agreed. Also kind of makes the use of a subform a bit redundant too doesn't it?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by howey
    Although whenever I hear one-to-one I ask "Is this a candidate for putting all the data in one table" thus sorting the problem (I know there are good reasons for not always wanting to do this).Chris
    Maybe I work in a very limited field, but aside from a special case (where number of columns in the table exceeded the limits in Access [No don't ask it was a b!!!ard design inherited from a muppet unaware of normalisation or physical design]) I've never yet seen a physical design which justified one to one relationships. None of the references have ever demonstrated a logical reason for a one to one relationship that I could understand - mebbe its old age catching up with me.

  8. #8
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by healdem
    Maybe I work in a very limited field, but aside from a special case (where number of columns in the table exceeded the limits in Access [No don't ask it was a b!!!ard design inherited from a muppet unaware of normalisation or physical design]) I've never yet seen a physical design which justified one to one relationships.
    I agree that any 1-1 can be replaced by a single table. However, the two arguements I understood for 1-1 are:

    Simplicity of structure. Avoid having hundreds on unmanagable fields in one table by splitting them into "sensible" groups. Forms may only be interested in specific groups rather than the whole data set.

    Reduction in size. Suppose there are fields that aren't necessary for some of the data but necessary for the rest of the data e.g. database of students - there may be a different set of boxes to fill for foreign student to local students (who may have another set of boxes not relevant to foreign students). DO you simple have a single table containing all fields for all students or do you have 1-1 tables for the extra fields. If there were 10,000 students and only 100 were foreign then you are creating unnecessary "space" in your d/b i.e. 9,900 blank sections and 100 used sections compared to just 100 records if a seperate table were used.

    I've used this philosophy several times e.g. a memo "comment" field for customers where the reality is that very few customer have a comment. Also, delivery failure notification. A delivery either has no failure (no failure data required) or a single failure (and therefore the associated info). There are very few failures compared to deliveries. It's neat and easier to manage to have the failures in a separate table and I (maybe wrongly) assume queries will run quicker.

    I have no idea whether I gain any real benefit in terms of performance or space. And in terms of arguing for or against I would much rather see a single table. Truth is I really don't know but sometimes it seems more natural to split off the data in a sensible manner.

    That's how I see it.

    Chris

Posting Permissions

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