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

Expand|Select|Wrap|Line Numbers

 
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:

Expand|Select|Wrap|Line Numbers

 
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):

Expand|Select|Wrap|Line Numbers

 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:

Expand|Select|Wrap|Line Numbers

 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 "MyCrosstabQueryName" below......here is your code then: You can call the name this way... ENJOY!

Expand|Select|Wrap|Line Numbers

 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

Expand|Select|Wrap|Line Numbers

  
here's try number 2
 
hopefully this formats the code
 
Thanks for the link - the Earl was quite the man I guess!
 
End Sub

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

/bytes/development

/bytes/it

/bytes/about