Code to Lookup Table Value and Return Record Number

[Tezcatlipoca]

Hi All,

Ok, I'm hoping this is fairly straightforward, but I've been hunting around online and going throguh the inbuilt help files in Access 2003 for about an hour now but simply can't find the answer.

Ok, I have a folder on my PC that contains a set of files whose names all begin with a 5 character code, then a hyphen, then the filename then the extension.

These files are replicated in an Access 2003 table called TblFileData that contains various text fields giving the file's code, name, extension type, and so on.

Now I have a form that contains, amongst a bunch of textboxes which are all bound to the above table for the purpoises of displaying information, a combobox, cbSelectFile.
This combobox is programmed to look at the folder of files in realtime, allowing the user to select any of the files inside. It's contents are generated dynamically via modules to gather data direct from the PC folder; it's values are not populated by the table.

With me?

Ok, not the idea is that the user uses the dropdown box to select a file from the folder. The form then scans the code of the selected file then matches it against it's own table and moves the form to the relevant record for that file. This is achieved by the following code:

Dim sCriteria As Variant
Me.SelCode.Value = Left(Me.cbSelectFile.Value, 5)
Me.RecNo.Value = DLookup("[ID]", "TblFileData", "[fldCode] ='" & Me.SelCode.Value & "'")
sCriteria = Me.RecNo.Value
If IsNull(Me.RecNo.Value) Then
DoCmd.GoToRecord acDataForm, "frmMyForm", acGoTo, 1
Else
DoCmd.GoToRecord acDataForm, "frmMyForm", acGoTo, sCriteria

Now this works perfectly. If the user selects a file whose 5 character code does exist in the Access table, the form changes to that record. If the user selects a file and its code does not exist in the table, then the form changes to record 1 which is purposefully kept blank.

This is all great, but I've recently noticed a problem. The column [ID] in my table is the primary key. I had cause recently to delete some rows directly from the table, and now my code throws debug errors when run.

I've investigated and understand why. Basically, I have row/record 1 (which has the [ID] of 1, in my table as being blank, but row/record 2 (which has the [ID] of 4 since I deleted some previous rows) has the data of a file.
When my code runs, it strips the code out of the file, reads the table, finds the code, but is returning the value of the [ID] field, and not the physical record number; so in my case it finds a match and tries to jump to record 4 (which doesn't exist) instead of record 2.

Any ideas how I can fix this? What should really happen to improve stability is the code should run the match, and, if found, jump to the physical record number, and not try to jump to a record that matches the [ID] numebr since these will sometimes fall out alignment.

 

[MarkK]

Rather than this ...

DoCmd.GoToRecord acDataForm, "frmMyForm", acGoTo, sCriteria

... try this ...

With Me.RecordsetClone
  .FindFirst "ID = " & sCriteria
  If Not .NoMatch Then Me.Bookmark = .Bookmark
End With

IMO jumping to the physical record number is not preferred. In a database it's so easy to return a subset of records or sort on some other field or add or delete records--as you've experienced--that using an offset to go to a record is not reliable. I'd always try to find the actual data.
The above code searches the data source of the form for the ID and if found goes to that record.

 

[Tezcatlipoca]

Rather than this ...

DoCmd.GoToRecord acDataForm, "frmMyForm", acGoTo, sCriteria

... try this ...

With Me.RecordsetClone
  .FindFirst "ID = " & sCriteria
  If Not .NoMatch Then Me.Bookmark = .Bookmark
End With

IMO jumping to the physical record number is not preferred. In a database it's so easy to return a subset of records or sort on some other field or add or delete records--as you've experienced--that using an offset to go to a record is not reliable. I'd always try to find the actual data.
The above code searches the data source of the form for the ID and if found goes to that record.

Ah, fantastic, thank you! Your code works brilliantly and is exactly what I was after.

I fully agree that, for stability reasons, it is far better to have the VB scan for the file code then jump to whichever record has that code, rather than trying to load a physical record row number.

Thanks again.

 

[dfenton]

One thing, if the .NoMatch is true, check if the record is dirty and save it before navigating via bookmark. This forces any errors that might occur in the saving of the record to happen before record departure. Skipping that step can result in data loss.