?
Solved

automatic field update

Posted on 1998-09-06
5
Medium Priority
?
154 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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 Month15 days, 2 hours left to enroll

771 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