By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,478 Members | 1,602 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,478 IT Pros & Developers. It's quick & easy.

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

P: 4
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
  1. LocationNo  Customer No  Status  20090101  20090201  20090301  20090401  20090501  20090601
  2. 34             1018      Active         1         1         1                             1
  3. 28             1038      Active        84        41        39        31        15
  4. 21             1035      Active        14        11         6         3         1
Jun 16 '09 #1
Share this Question
Share on Google+
14 Replies


Frinavale
Expert Mod 5K+
P: 9,731
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
Jun 17 '09 #2

P: 4
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
  1. Sub Test()
  2.   Dim Cn As ADODB.Connection
  3.   Dim CrossTbl As ADODB.Recordset
  4.   Dim fld As Field
  5.   Set Cn = New ADODB.Connection
  6.   Cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\SunnyC\Desktop\Test.mdb;"
  7.   Cn.Open
  8.   Set CorssTbl = New ADODB.Recordset
  9.  
  10.   CrossTbl.Open "TheReport", Cn
  11.  
  12.   For Each fld In db.Recordsets("TheReport").Fields
  13.     Debug.Print fld.Name
  14.   Next fld
  15.   MyTable.Close
  16.   MyConn1.Close
  17. End Sub
Thank you for the help,

SunnyC
Jun 17 '09 #3

Frinavale
Expert Mod 5K+
P: 9,731
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".
Jun 17 '09 #4

Expert 100+
P: 1,287
Here's some very crude code (that uses late binding instead of reference dependencies):
Expand|Select|Wrap|Line Numbers
  1. Dim strLastField As String
  2. Dim db As Object
  3. Dim fld As Object
  4. Dim intFieldCount As Integer
  5.  
  6. Set db = CurrentDb
  7.  
  8. 'loop through all the fields to count how many there are
  9. intFieldCount = 0
  10. For Each fld In db.TableDefs("NameOfYourTable").Fields
  11.   intFieldCount = intFieldCount + 1
  12. Next fld
  13.  
  14. If intFieldCount > 0
  15.   strLastField = db.TableDefs("NameOfYourTable").Fields(intFieldCount - 1).Name
  16. End If
Jun 17 '09 #5

P: 4
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.
Jun 17 '09 #6

Frinavale
Expert Mod 5K+
P: 9,731
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.
Jun 17 '09 #7

P: 4
Thank you very much Frinny for the information and suggestions. I will take a look of it!
SunnyC
Jun 17 '09 #8

NeoPa
Expert Mod 15k+
P: 31,709
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!
Jun 18 '09 #9

yarbrough40
100+
P: 320
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
  1. Dim Con As ADODB.Connection
  2. Set Con = CurrentProject.Connection
  3. Dim rs As New ADODB.Recordset
  4. Dim sql As String
  5. sql = "select * from MyTable order by PrimKey desc"
  6. With rs
  7.     .ActiveConnection = Con
  8.     .Open (sql)
  9.     End With
  10.  
  11.     Textbox1.text = rs.fields("locationNo").Value
  12. End Sub
Jun 21 '09 #10

yarbrough40
100+
P: 320
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
  1. Dim Con As ADODB.Connection
  2. Set Con = CurrentProject.Connection
  3. Dim rs As New ADODB.Recordset
  4. Dim sql As String
  5. sql = "select * from MyCrosstabQueryName""
  6. With rs
  7. .ActiveConnection = Con
  8. .Open (sql)
  9. End With
  10.  
  11. TextBox1.Value = rs.Fields(rs.Fields.Count-1).Name
  12.  
  13. End Sub
Jun 21 '09 #11

NeoPa
Expert Mod 15k+
P: 31,709
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?
Jun 22 '09 #12

yarbrough40
100+
P: 320
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...
Jun 22 '09 #13

NeoPa
Expert Mod 15k+
P: 31,709
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.
Jun 22 '09 #14

yarbrough40
100+
P: 320
Expand|Select|Wrap|Line Numbers
  1.  
  2. here's try number 2
  3.  
  4. hopefully this formats the code
  5.  
  6. Thanks for the link - the Earl was quite the man I guess!
  7.  
  8. End Sub
  9.  
  10.  
Jun 22 '09 #15

Post your reply

Sign in to post your reply or Sign up for a free account.