How to return the value in one field based on lookup value in another field

Question

This is basic stuff, but I'm somewhat unfamiliar with VBA and the Word/Access object models.

I have a two column database of about 117000 records. The columns are 'surname' and 'count'. I want a user to be able to type SMITH in a textbox and hit submit. I then want to run something like

SELECT table.count FROM table WHERE surname = string

and return the value of table.count in a string.

It feels like this should be five or six lines of code (which I have but won't post) but I'm obviously missing something!

Cheers

Joel Spolsky · Accepted Answer · 2009-02-03 03:32:59Z

6

First of all, be careful naming the column 'count' -- this is a keyword in SQL and might cause problems. Similarly, don't call the table 'table'.

Here is some sample code which shows one way of doing it:

' This example uses Microsoft ActiveX Data Objects 2.8,
' which you have to check in Tools | References

' Create the connection. This connection may be reused for other queries.
' Use connectionstrings.com to get the syntax to connect to your database:
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\tmp\Database1.accdb"

Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = conn

' Replace anything which might change in the following SQL string with ?

cmd.CommandText = "select ct from tbl where surname = ?"

' Create one parameter for every ?

Dim param As ADODB.Parameter
Set param = cmd.CreateParameter("surname", adBSTR, adParamInput, , TextBox1.Text)
cmd.Parameters.Append param

Dim rs As ADODB.Recordset
Set rs = cmd.Execute

MsgBox rs("ct")

rs.Close
conn.Close

answered Feb 3, 2009 at 3:32

Joel Spolsky

33.8k18 gold badges91 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user51498 Over a year ago

Table and columns actually have real names, and there are more columns, but I wanted to work that bit out for myself so I actually learn something. Thanks!

Joel Spolsky · Accepted Answer · 2009-02-04 06:03:56Z

4

It is possible to use InsertDatabase:

Sub GetData()
    ActiveDocument.Bookmarks("InsertHere").Select

    Selection.Range.InsertDatabase Format:=0, Style:=0, LinkToSource:=False, _
        Connection:="TABLE Members", SQLStatement:= _
        "SELECT [Count] FROM [Members]" _
        & " WHERE Surname='" _
        & ActiveDocument.FormFields("Text1").Result & "'", _
        DataSource:="C:\docs\ltd.mdb", From:=-1, To:= _
        -1, IncludeFields:=True
End Sub

This is an edited macro recorded using the database toolbar.

EDITED Warning: this code, as shown, is subject to a SQL Injection attack.

edited Feb 4, 2009 at 6:03

Joel Spolsky

33.8k18 gold badges91 silver badges105 bronze badges

answered Feb 3, 2009 at 13:12

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Collectives™ on Stack Overflow

How to return the value in one field based on lookup value in another field

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related