Lookup value in Access table without a unique identifier using VBA

If the number of records and buttons are fixed, say 10, and buttons have names like btn1, btn2, etc., consider a procedure like:

Public Sub TableNameRS(TableID As Integer)
    Dim RS As DAO.Recordset
    Set RS = CurrentDb.OpenRecordset("SELECT TableName FROM MyTable WHERE Flag=1 ORDER BY TableName")
    For x = 1 to 10
        Me("btn" & x).Caption = RS("TableName")
        RS.MoveNext
    Next
    RS.Close
End Function

Procedure can be called in form Open event. Then code in button Click event can pass its caption as an argument to whatever other procedure needs to take action with TableName value.

Calculating a sequence identifier in Access query is a common topic MS Access Restart Number Sequence.

Assuming TableName values are unique:

SELECT TableName, DCount("*", "table", "TableName<'" & [TableName] & "' AND Flag=1")+1 AS Seq, 
FROM table
WHERE Flag=1
ORDER BY TableName;

Can use that query as RecordSource for form or report or execute DLookup() on it.

Be aware any change in filter will impact sequence calculation.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top