VBA code to access an external database

Question

I have a user form I'm using in MS Word that has a city, state and zip code field on the form. What I would like to add to this is that if a zip code is entered then the city and state values would be pulled from an MS Access database file I have called zipcodes.mdb which is located on the users' local drive.

To make this as specific as possible so that if someone can help they can post most specific lines of code here are my field names.

MS Word Userform fields:

NP_City

NP_State

NP_Zip

MS Access DB:

Zip Code

City

State Abbreviation

No permanant location yet for the database but could be something like:

C:\ZipCodes\Zipcodes.mdb

Answer 1

While the code in the article Doug cited is useful, it needs some further development to do the job as desired.

First, I'm going to assume that when you say you have a userform, you really mean a UserForm as created in the VBA editor, and not a document that you're calling a "form" with legacy form fields. The syntax is considerably different.

The article says to set a reference to the Microsoft DAO 3.51 Object Library and Microsoft Datasource Interfaces. With Office 2007 or 2010, you should instead choose the Microsoft DAO 3.6 Object Library, and I found that I didn't need a reference to the Interfaces.

The subroutine should be placed in the userform's code module and not in a regular module, so it has easy access to the form's control objects.

Unlike the code in the article, which is intended to loop through all the records in the database, this job can be improved by jumping out of the loop as soon as the desired record is found. Further, if there is no record with the desired zip code, it's a good idea to blank out the City and State fields on the userform rather than possibly leave them containing old data.

With those comments, try this code:

Private Sub NP_Zip_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    GetDataFromDataBase

End Sub

Sub GetDataFromDataBase()

'allocate memory for the database object as a whole and for the active record

Dim myDataBase As Database

Dim myActiveRecord As Recordset

Dim done As Boolean

'Open a database

Set myDataBase = OpenDatabase("C:\ZipCodes\Zipcodes.mdb")

'Access the first record from a particular table <== replace "Table_1" with your table's name

Set myActiveRecord = myDataBase.OpenRecordset("Table1", dbOpenForwardOnly)

'Loop through all the records in the table until the end-of-file marker is reached

'or the zip code is found

done = False

While (Not myActiveRecord.EOF) And (Not done)

    ' Find the record with the entered zip (if it exists)

    If myActiveRecord.Fields("Field1") = Trim(NP_Zip.Text) Then

        NP_City.Text = myActiveRecord.Fields("Field2")

        NP_State.Text = myActiveRecord.Fields("Field3")

        done = True  ' exit loop without looking any further

    End If

     'access the next record

    myActiveRecord.MoveNext

Wend

' If you get here and done = False, the entered zip code isn't in the database

If Not done Then

    NP_City.Text = ""

    NP_State.Text = ""

End If

'Then close the database

myActiveRecord.Close

myDataBase.Close

End Sub

Answer 2

So I got it all resolved!  I cheated a little. Since the code you gave me set the state to the code I just added some additional code that looks in the other table using the code we've already established. Works perfect!

Thanks again for your help!

Answer 3

I'm actually guessing that it should be part of the on change event for the zip code field shouldn't it?

Answer 4

While the code in the article Doug cited is useful, it needs some further development to do the job as desired.

First, I'm going to assume that when you say you have a userform, you really mean a UserForm as created in the VBA editor, and not a document that you're calling a "form" with legacy form fields. The syntax is considerably different.

The article says to set a reference to the Microsoft DAO 3.51 Object Library and Microsoft Datasource Interfaces. With Office 2007 or 2010, you should instead choose the Microsoft DAO 3.6 Object Library, and I found that I didn't need a reference to the Interfaces.

The subroutine should be placed in the userform's code module and not in a regular module, so it has easy access to the form's control objects.

Unlike the code in the article, which is intended to loop through all the records in the database, this job can be improved by jumping out of the loop as soon as the desired record is found. Further, if there is no record with the desired zip code, it's a good idea to blank out the City and State fields on the userform rather than possibly leave them containing old data.

With those comments, try this code:

Private Sub NP_Zip_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    GetDataFromDataBase

End Sub

Sub GetDataFromDataBase()

'allocate memory for the database object as a whole and for the active record

Dim myDataBase As Database

Dim myActiveRecord As Recordset

Dim done As Boolean

'Open a database

Set myDataBase = OpenDatabase("C:\ZipCodes\Zipcodes.mdb")

'Access the first record from a particular table <== replace "Table_1" with your table's name

Set myActiveRecord = myDataBase.OpenRecordset("Table1", dbOpenForwardOnly)

'Loop through all the records in the table until the end-of-file marker is reached

'or the zip code is found

done = False

While (Not myActiveRecord.EOF) And (Not done)

    ' Find the record with the entered zip (if it exists)

    If myActiveRecord.Fields("Field1") = Trim(NP_Zip.Text) Then

        NP_City.Text = myActiveRecord.Fields("Field2")

        NP_State.Text = myActiveRecord.Fields("Field3")

        done = True  ' exit loop without looking any further

    End If

     'access the next record

    myActiveRecord.MoveNext

Wend

' If you get here and done = False, the entered zip code isn't in the database

If Not done Then

    NP_City.Text = ""

    NP_State.Text = ""

End If

'Then close the database

myActiveRecord.Close

myDataBase.Close

End Sub

What part of the UserForm code module. Initialize, activiate, etc...?

Answer 5

I have a user form I'm using in MS Word that has a city, state and zip code field on the form. What I would like to add to this is that if a zip code is entered then the city and state values would be pulled from an MS Access database file I have called zipcodes.mdb which is located on the users' local drive.

 

To make this as specific as possible so that if someone can help they can post most specific lines of code here are my field names.

 

MS Word Userform fields:

NP_City

NP_State

NP_Zip

 

MS Access DB:

Zip Code

City

State Abbreviation

 

No permanant location yet for the database but could be something like:

C:\ZipCodes\Zipcodes.mdb

See the article "Access a database and insert into a Word document the data that you find there” at:

http://www.word.mvps.org/FAQs/InterDev/GetDataFromDB.htm

and

http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm