?
Solved

automatic field update

Posted on 1998-09-06
5
Medium Priority
?
157 Views
Last Modified: 2011-09-20
I have an access 8.0 backend database. one of the tables is my zipcode table which is composed of zipid, zipcode and county, as a county can have several zipcodes, some may be repeated. I have a vb 5.o front end. I would like it that when the user enters a zipcode, that the corresponding county appears, or if that county is not in my list to prompt for addition.  I have a seperate box (cbo, etc)for the  state, county and zipcode. I know that in access i would use dlookup. any help would be appreciated. do i have to create a seperate recordset?



david walker
0
Comment
Question by:dbwalker090698
  • 3
5 Comments
 

Expert Comment

by:ks_rao
ID: 1433356
I have also faced the same problem. But I didn't attempt it to solve.
But I can suggest you some thing.

1) You have to write your own function by passing
the tablename as well as the value required to search.
0
 
LVL 3

Expert Comment

by:a111a111a111
ID: 1433357
Hi,
Send me your email address and I will send you the code you need.

If you care about my answer than I will post it here after you review my code.

The code contained: form, project, Database (MSaccess ver 8.0) and VB EXE.

I will wait for your email.

shayplace@hotmail.com
 
0
 
LVL 3

Expert Comment

by:a111a111a111
ID: 1433358
Hi,
if you want the complete project, email to shayplace@hotmail.com

I made a program as your question and I try it with the database by your question
fields. Here is the answer.

Private Sub cmdLookupZip_Click()
' Program to get a county name from a database when user input a zip code.
' if county not found than a message will notify the user about it.
' if the zip code in not in the table than a message will notify the user about it.
' * ================================
Dim db As Database
Dim SQLstr1 As String
Dim allmylines$
Dim lineQ$
Dim t1 As Dynaset
Dim ZipIn
Dim process_err
Dim X, Y

On Error GoTo process_err
List1.Clear
ZipIn = InputBox("Please enter a zip code", "Zip Code", "")
SQLstr1 = "SELECT County FROM ziptbl WHERE zipCode ='" & Trim$(ZipIn) & "';"
Set db = OpenDatabase("c:\zip-code\zip-code.mdb", False, True)  'open database (you should add error checking)
Set t1 = db.CreateDynaset(SQLstr1, dbReadOnly)  'create dynaset from SQL query
t1.MoveLast
X = t1.RecordCount
t1.MoveFirst
Do
    lineQ = t1("County") & ""
    If lineQ <> "" Then ' see if county column is not empty
        List1.AddItem lineQ
    Else
        MsgBox "County not found for " & ZipIn
    End If
   
 t1.MoveNext
 Loop Until X  ' X = last record remember.
t1.Close
Set t1 = Nothing
process_err:
Select Case (Err)
Case 3021 ' No current record
    MsgBox " Zip Code " & ZipIn & " Not Found"
    Exit Sub
End Select
End Sub

Private Sub Form_Load()
cmdLookupZip_Click ' it just to make one less click in this test project.
End Sub

if you want the complete project, email to shayplace@hotmail.com

0
 

Author Comment

by:dbwalker090698
ID: 1433359
rejected this answer as it was not direct enough for me. However the comment from A11A11A11 was what i wanted. He provided code which would solve my problem, but also let me learn at the same time. Sometimes a starting direction is of great help
0
 
LVL 3

Accepted Solution

by:
a111a111a111 earned 400 total points
ID: 1433360


       Private Sub cmdLookupZip_Click()
       ' Program to get a county name from a database when user input a zip code.
       ' if county not found than a message will notify the user about it.
       ' if the zip code in not in the table than a message will notify the user about it.
       ' * ================================
       Dim db As Database
       Dim SQLstr1 As String
       Dim allmylines$
       Dim lineQ$
       Dim t1 As Dynaset
       Dim ZipIn
       Dim process_err
       Dim X, Y

       On Error GoTo process_err
       List1.Clear
       ZipIn = InputBox("Please enter a zip code", "Zip Code", "")
       SQLstr1 = "SELECT County FROM ziptbl WHERE zipCode ='" & Trim$(ZipIn) & "';"
       Set db = OpenDatabase("c:\zip-code\zip-code.mdb", False, True)  'open database (you should add error checking)
       Set t1 = db.CreateDynaset(SQLstr1, dbReadOnly)  'create dynaset from SQL query
       t1.MoveLast
       X = t1.RecordCount
       t1.MoveFirst
       Do
           lineQ = t1("County") & ""
           If lineQ <> "" Then ' see if county column is not empty
               List1.AddItem lineQ
           Else
               MsgBox "County not found for " & ZipIn
           End If
           
        t1.MoveNext
        Loop Until X  ' X = last record remember.
       t1.Close
       Set t1 = Nothing
       process_err:
       Select Case (Err)
       Case 3021 ' No current record
           MsgBox " Zip Code " & ZipIn & " Not Found"
           Exit Sub
       End Select
       End Sub

       Private Sub Form_Load()
       cmdLookupZip_Click ' it just to make one less click in this test project.
       End Sub

If more help is needed email to shayplace@hotmail.com
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month7 days, 2 hours left to enroll

592 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