Getting addressability to a Table record.

[sts023]

Hi guys, time for another newbie question.
I'm "improving" an existing Table
It's a table of books where the author is unfortunately specified in one of two ways - "Joe Soap", or "Soap, Joe" (it's a historical thing!).
I decided to tidy up the Book Table by running down the table extracting the author's forename(s) and surname, using this data to create a brand new Author Table with an unique primary key , then adding the Author's primary key to the existing Book Table as a foreign key.
All reasonable so far.
I used the "if I can't find an Author entry using SQL, then add a new Author" technique.
Then I did something silly - I started thinking!
Why don't I record the number of Books by each author?
The amended technique was as before, but with additional processing so that if I DID match an Author, I could update a "Hit Count".

Hmm!

I'm using DAO to open the Book and Author tables as Recordsets.

  Set dbs = CurrentDb           'Use the current database.
  Set rstBook = dbs.OpenRecordset("Book", dbOpenTable)
  Set rstAuthor = dbs.OpenRecordset("Author", dbOpenTable)

Note - in the code snippets below, anything not "Dim"med in the code has already been declared "universally".

I'm reading the Book table sequentially, and getting the data into AuthorFname and AuthorSname (both strings). Then I get a bit giddy (and I suspect that this is where the problems start), 'cos I use a Function to get hold of the (possibly existing) Author record.

'*
'** See if the Author already exists.
'*
      critStr = "Surname = """ & AuthorSname & _
                """ and " & _
                "Forenames = """ & AuthorFname & """"
      LocFlag = LocateAuthor("[pkAuthor]", "Author", critStr)

(I have to use double quotes, otherwise Edna O'Brien screws the system!)

Public Function LocateAuthor(strField As String, _
                             strTable As String, _
                             strWhere As String)
Dim rs          As DAO.Recordset         'Related records
Dim strSql      As String
'*Initialise result
  LocateAuthor = 0
'*build SQL string
  strSql = "SELECT " & strField & " FROM " & strTable
  strSql = strSql & " WHERE " & strWhere
  Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
  If rs.RecordCount > 0 Then
    kVal = rs.Fields("pkAuthor").Value
    LocateAuthor = 1
  End If
  Set rs = Nothing
End Function

If the Author DOESN'T already exist, I add an entry as below.

Sub NoMatchX()
  AuthorPtr = AuthorPtr + 1
  With rstAuthor
    .AddNew
    !pkAuthor = AuthorPtr
    !Forenames = AuthorFname
    !Surname = AuthorSname
    ![Hit Count] = 1
    .Update
    .Bookmark = .LastModified
  End With
End Sub 'NoMatchX

Now comes the somewhat arthritic bit.
What I want to do (assuming I've found a matching record) is to simply update it's Hit Count.
Unfortunately, the only way I seem to be able to get addressability to the record to update it is to pick out the foreign key from the SQL based returned record (kVal in the code below), do a MoveFirst, then a Move!

Sub MatchX()
Dim tmp As Long
'*
'* Update Author Record.
'*
  With rstAuthor
    .MoveFirst
    .Move kVal - 1
  End With
  tmp = rstAuthor.Fields("Hit Count").Value + 1
  With rstAuthor
    .Edit
    ![Hit Count] = tmp
    .Update
    .Bookmark = .LastModified
   End With
End Sub 'MatchX

There's GOT to be a better way to get addressability to the record I wish to update. Unfortunately, in my researches all I've found is
MoveFirst,
MoveLast,
MoveNext, and
Move n records.
Can anyone point me in the right direction?

 

[pbaldy]

Probably the cleanest way is to open the recordset on the desired record, as you did with rs. It is much more efficient than opening a recordset on the entire table and then searching through it. In your scenario, you'd probably use FindFirst to locate the record. I'd probably open the recordset on the desired person, and test for EOF (end of file, ie no records). If EOF, you can add a record using the same recordset.

That said, I wouldn't try to store how many books an author has written, since it's essentially a calculated value that's potentially always changing. I'd simply calculate it on the fly, via query or DCount depending on how/where it's needed.

 

[boblarson]

... I wouldn't try to store how many books an author has written, since it's essentially a calculated value that's potentially always changing. I'd simply calculate it on the fly, via query or DCount depending on how/where it's needed.

AMEN to that!

 

[sts023]

Many thanks to pbaldy and Bob Larsen.

I know that putting a count in the Authors Table is a silly idea for when the project goes live - I'm an old programmer, but new to Access VBA, so it was more of a challenge than a requirement! Especially when I found out I couldn't do it!!!

I'll look at the suggested methods - I'm not quite sure what

I'd probably open the recordset on the desired person, and test for EOF

means, but any suggestion made by a reader of "Atlas Shrugged" is probably worth investigating!

Thanks again guys....

 

[pbaldy]

I'll look at the suggested methods - I'm not quite sure what

I'd probably open the recordset on the desired person, and test for EOF

means

It means opening the recordset as you did in the LocateAuthor procedure, with a WHERE clause that will only return the desired record. You tested the record count there, I'd test for EOF, but either will probably work. Along the lines of:

If rs.EOF Then
  'add a record
Else
  'edit the existing record
End If

 

[sts023]

Thanks again Paul!

I've also been investigating other avenues (i.e. learning by my mistakes in a trial and error sort of way).
If I've read it correctly, if I open a recordset as a dbOpenDynaset, (instead of dbOpenTable) then issue a FindFirst with an operand holding some sort of criteria, and subsequently don't find the record, I can issue a recordset.addnew sequence which should add the new record to the Table from which the recordset was derived.
(Yes, I know, I had to read that three times and I wrote it!).

Doubtless there will be more of my deranged ramblings in the future, but for now, let coding commence!

 

[pbaldy]

That sounds about right, but I can't remember the last time I opened a recordset on an entire table. On big linked tables that would be much less efficient than opening the recordset on SQL with a WHERE clause, since the entire table would have to be brought "over the wire".