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.
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
DoCmd.GoToRecord acDataForm, "frmMyForm", acGoTo, sCriteria
Now this works perfectly. If the user selects a file whose 5 character code [I]does[I] 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.