Using count() to locate records?

[BE19]

Is it possible to use the count() function to count down a table and return that value i.e. a user first selects a table based upon a list box. The user then enters a number. That number would be used to count down in the table and return the value in the table that corresponds to that row.

 

[boblarson]

It would be possible, however meaningless. A table doesn't store records in any meaningful order, so while it may LOOK like it is storing records in the order that the records were entered, it really isn't necessarily doing so. In order for a table to have "order" you must apply the order to it in the form of a field that has ordered meaning (even an autonumber doesn't necessarily apply consecutive order, especially if used in a replicated database). So, a date/time stamp would probably have the most meaning and still the table itself isn't necessarily stored in that order, but a query can pull it in order.

So the answer is yes, you can do this, but it is highly dependent upon other factors as to how to pull it with meaning.

 

[GaryPanic]

also why ......
if they can see it double click it to get the result to do something
thinking out loud (and I dow down to Bob's knowledge here)
but I would upon 1 click or select it - have a hidden field on your form ,this selection on double click run qry from this field to get your result ???

sloppy but might work

g

 

[BE19]

Interesting. I didn't know that. So you're saying the data isn't stored in lineal progression. That shoot that idea for solving my two dimensional lookup. Back to the thinking board.

 

[boblarson]

What kind of data are you trying to store and then retreive? There may be a good logical way that we know of that will help, if we know what you're trying to accomplish.

 

[BE19]

I'm trying to find a way to do a two dimensional lookup w/o VBA. I have a table that is 10x9 (CxR). I thought I could use a combo box with the field names in it to represent the columns across the top. I then put in a list box for the row selection. I thought I could use the count() and dlookup functions together to then go the column (selected using the combo box) and the dlookup function would then count (based upon the list box selection) to the value in the table. It would return the value at the cross section.

What I came up with is

 

[boblarson]

So is the table always going to have the same dimensions?

 

[BE19]

Yes, the contents of the table is static.

 

[boblarson]

Which version of Access are you using? I'll write out some code for you that will do what you want, but the version you're using would be good to know so I know which option (DAO or ADO) is it's default - (that way you don't have to set a reference).

 

[BE19]

Access 2003. I can send the table I'm trying to construct this one, if you'd like. All I want to do is return the cross section of the two values so I can use it elsewhere. You'd think I was asking for a piece of the true cross! :O

 

[boblarson]

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim strTableName As String

strTableName = Me.YourListBoxName ' (if name is bound column, but use Me.YourListBoxName.Column(1) if the name is the second column and not the bound column)
Set rst = CurrentDb.OpenRecordset(strTableName, dbOpenDynaset)

  Do Until intCount = Me.YourFormsTextBoxNameForRecordNumberHere
       intCount = intCount + 1
       rst.MoveNext
  Loop
    Me.YourTextBox1 = rst(0) 'this puts the values retreived from the table into a text box on your form - zero being the first column of the table, 1 being the next, etc.)
    Me.YourTextBox2 = rst(1)
'...continue with however many columns you want - rst(0) will get you the first column, so you may want to put in an integer and assign it based on a text box that is on your form, for column as well.
rst.Close
Set rst = Nothing