Solved

automatic field update

Posted on 1998-09-06
5
153 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 200 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

729 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