How to lookup the last field name in a MS Access table on a form?

[SunnyC]

I have a table as below in test.mdb Access database. on a form, I want to use lookup to capture the the last field name in a text on the form, in this case, it is 200906.
by using some code I searched on the web site, I only can see all the field names, How can I just capture the last field name in the crosstab query.

Thanks, SunnyC

LocationNo  Customer No  Status  20090101  20090201  20090301  20090401  20090501  20090601
34             1018      Active         1         1         1                             1
28             1038      Active        84        41        39        31        15
21             1035      Active        14        11         6         3         1

[Frinavale]

Hi SunnyC!
Welcome to Bytes :)
You can find answers and ask Access questions in the Access Answers Forum.

Are you using SQL to access the lookup table?
If so could you please post it so that we can see what you're doing?

-Frinny

[SunnyC]

I searched the web and got the code for my report, but since I am a beginner. need help on how to capture the last field name and show it on a text box on a form.

here is my code:

Sub Test()
  Dim Cn As ADODB.Connection
  Dim CrossTbl As ADODB.Recordset
  Dim fld As Field
  Set Cn = New ADODB.Connection
  Cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\SunnyC\Desktop\Test.mdb;"
  Cn.Open
  Set CorssTbl = New ADODB.Recordset
 
  CrossTbl.Open "TheReport", Cn
 
  For Each fld In db.Recordsets("TheReport").Fields
    Debug.Print fld.Name
  Next fld
  MyTable.Close
  MyConn1.Close
End Sub

Thank you for the help,

SunnyC

[Frinavale]

That looks like VB (6) code.
Are you programming a VB6 Application or an Access application?

Do you know how to use SQL to retrieve information from a database?

I don't see where you've declared your variable "db".

[ChipR]

Here's some very crude code (that uses late binding instead of reference dependencies):

Dim strLastField As String
Dim db As Object
Dim fld As Object
Dim intFieldCount As Integer
 
Set db = CurrentDb
 
'loop through all the fields to count how many there are
intFieldCount = 0
For Each fld In db.TableDefs("NameOfYourTable").Fields
  intFieldCount = intFieldCount + 1
Next fld
 
If intFieldCount > 0
  strLastField = db.TableDefs("NameOfYourTable").Fields(intFieldCount - 1).Name
End If

[SunnyC]

I am using Access Application. I know little of SQL, do not know how to use it retrieve information form a database. can you show me how?

thanks.

[Frinavale]

I'm sorry but I don't know much about Access (I'm mainly a .NET developer). I never knew how close the Access syntax was to VB6.

Anyways, it's always useful to know SQL (it stands for: Structured Query Language and is pronounced See-quill). SQL lets you specify what you're looking for....the database management system (in your case Access) reads the SQL you provide to it, compiles it into a command and returns any records that match what you've asked for.

For example, if you were looking for a record with the Field (named ID) with the value of 200906 you would have something like:

SELECT * FROM test WHERE id = 200906;

This statement will retrieve all of the fields (*) for any records that have a value of 200906 in the "id" field from the table named "test".

w3cschools is a great place to learn SQL.

Just be aware that SQL is always a little different for every database management system. It's supposed to be a standard so learning from w3cschools is a great idea but Access will have it's own twist on its SQL.

I should probably let the Access experts take over now :)

-Frinny

PS you may want to let the experts here know what version of Access you're using.

[SunnyC]

Thank you very much Frinny for the information and suggestions. I will take a look of it!
SunnyC

[NeoPa]

You refer to the data source in one sentence as a table, but in another as a CrossTab query.

Any solution will need to follow a better understanding of what you require to be appropriate. If you can share that with us I'm sure we can help you.

Welcome to Bytes!

[yarbrough40]

hey! I'm super new too... let me see if I can help... Assuming
1)the name of the query/table you are referring too is "MyTable"
2)you have an AutoNumber field in the query/table called "PrimKey" (you could also refer to any date field in the table or anything that has the create date of the record)
3) create a reference to "Microsoft Access 10.0 Object Library"
4) your textbox is called "TextBox1"

paste this code to the OnClick event of a button on your form:

Dim Con As ADODB.Connection
Set Con = CurrentProject.Connection
Dim rs As New ADODB.Recordset
Dim sql As String
sql = "select * from MyTable order by PrimKey desc"
With rs
    .ActiveConnection = Con
    .Open (sql)
    End With
 
    Textbox1.text = rs.fields("locationNo").Value
End Sub