Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Vienna, Austria
    Posts
    97
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Having fields in a form filled automatically (Access 2000)

    I have a data entry form for Books with fields like AuthorID, Title, ISBN, OrigTitle, OrigLanguageID, OrigPubDate, OrigPublisher, OrigCountryID, ActualPubDate, ActualLanguageID, ActualPublisher, ActualCountryID, FormatID, StatusID and so forth. I retrieve the original language, the original country, actual language and actual country from combo-boxes.

    The ISBN is always 10 digits long and broken into four parts:

    - group/country identifier,
    - publisher identifier,
    - title/format identifier,
    - a check digit.

    I'd like to know, since my brainstorming s

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Having fields in a form filled automatically (Access 2000)

    This kind of question occurs frequently - the general issue is storing the same information in two different places. If I were starting from scratch, I would only store the ISBN and then (only) display the original language and country, and actual language and country using calculated and unbound combo boxes. That assumes that you have lookup tables to find the language and country of course. If you already have information in separate fields, you may not be able to take that approach, but the challenge comes when you an ISBN that is different from the language or country fields and you have to figure out which is right. Hope this makes sense - if not post back.
    Wendell

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

    Re: Having fields in a form filled automatically (Access 2000)

    I don't think it would be a good idea to update the ActualCountry field etc. while the user is typing. I would use the BeforeUpdate event of the text box bound to the ISBN field to test if the check digit is correct (set Cancel to True if it isn't), and the AfterUpdate event of this text box to update the ActualCountry etc.

    You'd have to write a parser for ISBN numbers, consisting of SELECT CASE and IF THEN ELSE statements - not trivial, since the constituent parts of the ISBN number have varying lengths. Something like the following incomplete code:

    Private Sub ISBN_AfterUpdate()
    Select Case Left(ISBN, 1)
    Case "0", "1"
    ' Handle English-speaking countries here
    Case "2"
    ' Handle French-speaking countries here
    Case "3"
    ' Handle German-speaking countries here
    Case "4"
    ' Handle Japan here
    Case "5"
    ' Handle Russian-speaking countries here
    Case "7"
    ' Handle China here
    Case "8"
    Select Case Mid(ISBN, 2, 1)
    Case "2"
    ' Handle Norway here
    Case "3"
    ' Handle Poland here
    ...
    End Select
    Case "9"
    Select Case Mid(ISBN, 2, 1)
    Case "0"
    ' Handle Dutch-speaking countries here
    ...
    End Select
    End Select
    End Sub

    The total number of ramifications seems horrible, it might not be worthwile to pursue this. Separate fields are probably easier.

  4. #4
    Star Lounger
    Join Date
    May 2002
    Location
    Vienna, Austria
    Posts
    97
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Having fields in a form filled automatically (Access 2000)

    Wendell and Hans, thank you both for answering.

    Wendell, I'm very new at Access so I'm not sure what you mean by "lookup tables". I didn't explicitely create lookup tables, just separate tables with the lookup entries, linked to the fields in the main table. I didn't use the Lookup Wizard and I still don't understand why I should use Lookup fields in tables. It seems to me they are complicating things, but I've only begun designing and using my Library database a few months ago, so I'm just learning and enjoying it. But I see what you mean: it seems the only sure thing about the ISBN is the publisher, so I'll try to create an ISBN - publisher table with the second part of ISBN as a primary key.

    Hans, you and Wendell were right of course, I have to enter the whole ISBN number and fill the publisher's field only AfterUpdate. It just seemed a fancy thing, to have a field fill up while you're still typing in another one. But since my family and I are the sole users, who will stare in wonder?

    I had started to outline a SELECT CASE code but as you say, the seemingly endless ramifications made me despair; I even found ISBN-numbers starting with a 3-digit group and there are rumours about the transition to a new 13-digit book code, so it's really not worthwile.

    Have a nice day and thanks again,
    Oana

Posting Permissions

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