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

[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