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

[yarbrough40]

Ah Ha! I just re-read your question.... I think you want the last Column Name in your query am I right? again - I don't know the name of your crosstab query but substitute that name in for "MyCrosstabQuer yName" below......here is your code then: You can call the name this way... ENJOY!

Dim Con As ADODB.Connection
Set Con = CurrentProject.Connection
Dim rs As New ADODB.Recordset
Dim sql As String
sql = "select * from MyCrosstabQueryName""
With rs
.ActiveConnection = Con
.Open (sql)
End With

TextBox1.Value = rs.Fields(rs.Fields.Count-1).Name

End Sub

[NeoPa]

Hi Yarbrough,

Welcome to Bytes!

4 posts and already contributing. Nice.

I would just draw your attention to the rules about using the [ CODE ] tags. I look forward to seeing more of you.

NB. Tags are done as matching pairs where the opening one is surrounded by [...] and the closing one by [/...]. A set of buttons is available for ease of use in the Standard Editor (Not the Basic Editor). The one for the [ CODE ] tags has a hash (#) on it. You can choose which editor to use in your profile options (Look near the bottom of the page).

PS. Is your name in any way related to the card game Bridge?

[yarbrough40]

Thanks! this is a great forum... I've been searching it for months and getting great info from past questions...
I must be an idiot because I can't seem to see the Standard Editor options in my browser. anyway.. are you saying to simply type
[...]
then write some code
[/...]
then done?

btw.. my parents were bridge players and so I know about the bridge hand : ) I wish it were a name for one of the good hands...

[NeoPa]

I'm very pleased you like it here. I'm looking forward to more interaction. You sound like you have some experience to offer :)

Did you click on the link I provided? It should take you directly to the page where those profile options are updated.

Lord Yarborough (Earl of Yarborough) was the sorry chap who never seemed to get any points (or maybe he was just pointless - who knows). If you feel you'd like your account name changed to reflect the spelling, please let me know.

[yarbrough40]

here's try number 2

hopefully this formats the code

Thanks for the link - the Earl was quite the man I guess!

End Sub