[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 156
  • Last Modified:

automatic field update

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
dbwalker090698
Asked:
dbwalker090698
  • 3
1 Solution
 
ks_raoCommented:
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
 
a111a111a111Commented:
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
 
a111a111a111Commented:
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
 
dbwalker090698Author Commented:
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
 
a111a111a111Commented:


       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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now