Need to find open values in a field through VBA 1

[aaronino]

Is there a function in Access that will allow you to retrieve field information if given a column name and a row number?

I've got a table that stores a "series of values" (along with other info about them) like these:

LO1341
LO1342
LO1344
LO1347
etc.

I need some way of searching through the values of the field, and retrieving the value of numbers that aren't used, like LO1343 and LO1345 in the example above. If I can retrieve the values of two consecutive fields, then I can do the VBA coding to determine if they are sequential.

 

[braindead2]

It could be done using VBA but an easier method mignt be to create a spread sheet in excel that contains all the numbers possible in the range. then import that data into new table in Access then create a find unmatched query.

If that don't work for you. You can parse the data by using either the left(), Right(),mid() or perhaps depending on what you want INT() or Instr()
good luck

 

[wemeier]

I'm confused by your references to "rows" and "columns". Am I to assume that your "series of values" are actually keys to consecutive records on an Access table. There is one value (LO1341, LO1342, etc.) in the same field name within multiple records?

If so, you can write VBA coding to walk though the table and identify the "missing" values:

Dim db as Database
Dim rst as Recordset
dim strLastKey as String
dim strThisKey as String

Set db = Currentdb()
Set rst = db.OpenRecordset(&quot;<put your table name here&quot
rst.MoveFirst
strLastKey = rst!Key
rst.MoveNext
While not rst.EOF
strThisKey = rst!Key
If Right(strThisKey,4) - Right(strLastKey,4) <> 0 Then
<put in coding to handle a &quot;missing&quot; key
End If
strLastKey = strThisKey
rst.MoveNext
Wend

rst.Close
Set db = Nothing

 

[aaronino]

Thanks to both of you. I'm actually going to end up implementing both solutions -- I'll temporarily use braindead's idea to display a list of acceptable values until I can customize wemeier's solution enough to insert a value into a text box for the user.

FYI, wemeier, I was talking about records when I said rows and fields when I said columns. The &quot;series of values&quot; is not unique within the table, it's part of a composite key.

Thank you both again for your help!