Solved

automatic field update

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

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Validating VB6 Function 19 59
MsgBox 2 54
to transfer string from C lanaguage to VBA 4 65
Excel Automation VBA 19 70
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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

773 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