Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2005
    Posts
    15

    Unanswered: When to calculate the ID (key) field ?

    I've got a form with the following fields :

    PatientID
    PatientVoornaam
    PatientNaam

    example:
    PatientNaam = "Aniston"
    PatientVoornaam = "Jenifer"
    PatientID = "P-AJ00"

    The PatientID field contains the primary key and should be based on PatientNaam and PatientVoornaam. I want the user to enter these 2 fields and then the PatientID field should be automatically calculated. Since this field may not be recalculated when browsing the records, I am not entirely sure which event I should be using to calculate the PatientID field.


    Any ideas ?

    Wouter

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sounds like you are concerend at creating this number is when you create the record. in which case the forms before insert event would be a likely candidate

    do you ever reclassify the number - or is the id only created on insert.

    HTH

  3. #3
    Join Date
    Oct 2005
    Posts
    15
    If I am right, the new record is created whenever one of the different fields receives its first input so the form_insert event is triggered way too soon.

    In the ideal case (residing in my mind ), the user inputs 2 fields after which the ID is created and the record is saved.

    The ID's should never be reclassified (although I do not see any problems if they have to be reclassified)

    Wouter

  4. #4
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Yup, I agree ...

    Just like - user 1 registers Jenifer Aniston, who gets assigned P-AJ00. While working with this, she gets a phone call, so the record sit's unsaved for the 45 minutes it takes her to talk with her mother in law.

    In the mean time, user #2 and user #3 has registrated about 25 more patients, of which three are named something starting with J as first name and starting with A as last name.

    Now - guess what happens ...

    Some people tend to change name at a certain stage in life - would that matter?

    Try the before update event of the form (immediately before the save), then test for Me.NewRecord, and if true, assign the calculated Pk.

    But - if you have no natural key, wouldn't this be something better solved with a surrogate (Identity/Autonumber)?
    Roy-Vidar

  5. #5
    Join Date
    Oct 2005
    Posts
    15
    Quote Originally Posted by RoyVidar
    Some people tend to change name at a certain stage in life - would that matter?
    Doesn't matter, no one does it over here and in case anyone does, it is no problem that they keep their old ID.

    Quote Originally Posted by RoyVidar
    Try the before update event of the form (immediately before the save), then test for Me.NewRecord, and if true, assign the calculated Pk.
    That NewRecord property is something I should have known a looong time ago ! Very useful, tnx.


    I still have one problem. When I use the form_beforeupdate event, it looks like the only thing I can do is validate the data and cancel if the data is no good. If I use
    field.text = "Something"
    I get an error saying the object does not has the focus but I also cannot move the focus to it anymore. If I use
    field = "Something"
    I get no errors but the data isn't updated before the record is saved. The only solution I have found is to manipulate the recordset itself, something I would rather not do. Any ideas on this one ?

    Wouter

  6. #6
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    In Access, the .Value prorty of controls, are the "analogue" of the .Text property in VB. To use the .Text property here, the control needs to have focus, just use the .Value property (since it is default, some prefer not to specify)

    me!txtMyControl.Value = "somthing"

    - should work - you're sure it gets assigned to the control? Lot of developers use this very event to stuff the record with create/modified date/timestamp, and userinfo - so it does work, there must be something else going on
    Roy-Vidar

  7. #7
    Join Date
    Oct 2005
    Posts
    15
    Quote Originally Posted by RoyVidar
    In Access, the .Value prorty of controls, are the "analogue" of the .Text property in VB. To use the .Text property here, the control needs to have focus, just use the .Value property (since it is default, some prefer not to specify)

    me!txtMyControl.Value = "somthing"

    - should work - you're sure it gets assigned to the control? Lot of developers use this very event to stuff the record with create/modified date/timestamp, and userinfo - so it does work, there must be something else going on
    I don't know what I did but it seems to work now ! I guess I had a little too much workarounds which eventually broke the code. This is my first real Access project and it is quite complex. Since I am learning this from the help system, I guess I am a little too creative with the code sometimes . Tnx for the advice !

    Wouter

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Ghost5-2
    .....Since I am learning this from the help system, I guess I am a little too creative with the code sometimes
    You can never be too creative with the code, too complex: yes, overly intricate: yes but too creative:no

    nice to see someone else makes good use of the help system before posting though......

    Although the help system is a fine / greaty resource (and a behaviour to be encouraged) I still think people devloping applications for commercial / real world use will benefit from looking at a decent Access book.

  9. #9
    Join Date
    Oct 2005
    Posts
    15
    Quote Originally Posted by healdem
    Although the help system is a fine / greaty resource (and a behaviour to be encouraged) I still think people devloping applications for commercial / real world use will benefit from looking at a decent Access book.
    Yeah, I know. But this a bit of a favor to someone and I hardly get paid for this so I am not really inclined to invest in expensive books.

    BTW. Does anybody know what (professional) people pay to have a simple database developed ? In my case, a couple of tables, some nice forms with some custom made code (for treeviews etc.), some reports all made for a single user (no server side).

Posting Permissions

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