Retrieving a key field using a variable

Posted on 2004-05-01
Medium Priority
Last Modified: 2010-04-24
I am now able to insert a variable into my Table. Now I want to take this same variable and extract the key field value for this row.

This is an Access Table. The table name is tblAGMain. The fields in the table are: KeyAG (Autonumber field and keyed), dtDate (Date field type), dtTime (Text field type), Locator (Text field type), Initials (Text field type).

Now this is the bit of code that I am trying to use to retrieve the key field value, bu I always get a value of "0".

Dim cmdDate As OleDbCommand = cnAGPOUpDate.CreateCommand()
cmdDate.CommandText = "INSERT INTO tblAGMain (dtDate, tmTime, Initials, Locator) VALUES ('" & strToday & "', " & _
"Null, '" & strLocator & "', Null)"

'create a new command to retrieve the key field from the tblAGMain where the date was just inserted
Dim cmdAGKey As OleDbCommand = cnAGPOUpDate.CreateCommand()
cmdAGKey.CommandText = "SELECT KeyAg FROM tblAGMain WHERE Locator = '" & strLocator & "'"
intAGKey = CInt(cmdAGKey.ExecuteScalar())

strAGKey = intAGKey.ToString


What is wrong with my code, why am I not getting my key field valu back?

Question by:cmem
  • 3
  • 2

Author Comment

ID: 10968524
Does anyone have any ideas?
LVL 27

Accepted Solution

Dabas earned 100 total points
ID: 10969248
Hi cmem:
> KeyAG (Autonumber field and keyed)
1) What do you mean by Autonumber and keyed? Its either Autonumber or not.
2) Does the insert command actually create the new record? (Have you checked the table in Access?)


Author Comment

ID: 10969609
It is an autonumber field and it is the key field for the table. But I see what happened, the fields on the table got rearranged and my app was inserting the variable into the wrong field and when it went to find the value it of course was not in that field.
LVL 27

Expert Comment

ID: 10969626
I assume then that you do not have a problem any more?


Author Comment

ID: 10969888
No that resolved that issue thanks....

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Through the video, you can check the migration process of Outlook PST file to PDF. Kernel for Outlook to PDF tool can convert Outlook emails with all attributes like Subject, To, From, Cc, Bcc and other folders such as Inbox, Outbox, Sent Items, Jun…
Did you know PowerShell can save you time with SaaS platforms? Simply leverage RESTfulAPIs to build your own PowerShell modules. These will kill repetitive tickets and tabs, using the command Invoke-RestMethod. Tune into this webinar to learn how…

586 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question