Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    Atlanta, Georgia, USA
    Posts
    64
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Simple queries in VBA/Access (Access 2000 SR1)

    Here's another newbie question. I know I can do this with DAO, but I suspect that I'm making things too complicated.
    I have a table (Contracts) that has a primary numeric key, called (of all things) Primary Key. Is there a simple way in VBA that I can check to see if a record exists in that table, using the Primary Key?

    While I'm here - I want to thank everyone for being so patient. I've learned a lot from just reading posts, and when I post, the people that answer are always extremely helpful. Thanks!

    Brian

  2. #2
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple queries in VBA/Access (Access 2000 SR1)

    Place the following in an SQL view of a query:

    SELECT Contacts.*
    FROM Contacts
    WHERE (((Contacts.PrimaryKey)=111));

    Then switch back to the query grid and change the 111 to the primary key you are looking for.

    hth

  3. #3
    Star Lounger
    Join Date
    Mar 2001
    Location
    Atlanta, Georgia, USA
    Posts
    64
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple queries in VBA/Access (Access 2000 SR1)

    Thanks, however I think I found a different way. Access is still new to me, but I can do VB. I finally located the DLookup method, and it gives me what I wanted.

    I do appreciate the fast reply.

    Brian

  4. #4
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Simple queries in VBA/Access (Access 2000 SR1)

    Just an FYI, DLookup does work well in this situation, but it can really slow down a process if you using it repeatedly, like in a loop.

    An SQL statement was already posted on your first post, it would be a good idea to get into the habit of using SQL statements for record interactions. They are the fastest solution.

    SQL, or Structured Query Language is actually not too difficult to pick up...at least the basics.

    Essentially each 'line' in an SQL statement serves a purpose. The most basic query requires a Select and From statement. Select tells the database engine what 'fields' you are looking for, and From tells the db engine what table to look in.

    Select MyField1, MyField2
    From tblMyTable

    If you add a Where statement, obviously it puts criteria into the mix:

    Select MyField1, MyField2
    From tblMyTable
    Where MyField3=2

    Note that a field used in the Where statement does not have to be in the Select statement.

    Access 97 has some wonderful help files on how to use the various SQL statements, however, personally, I just use the query builder to build the query I want, then go into SQL view and copy the SQL code. (Access formalizes a lot of things, so the query above would look more complex then what I wrote, but they both work).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •