Having fields in a form filled automatically (Access 2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote