[AccessD] Add new data to existing table

Doris,

I'm assuming that you have a combo box for your zip codes.  If you paste this code into the not in list event of your combo box, you should be pretty close.  Of course, I'm guessing on field names and layout of your ZipCode table; you will have to make changes to those, I'm sure.   

I'm checking mail at 11 p.m. only because of not being in the office tomorrow.  But if you have more questions, don't hesitate to ask.  

Good luck!

Liz


Private Sub cboZip_NotInList(NewData As String, Response As Integer)
On Error GoTo eh
Dim rs As Recordset
Dim db As Database
Dim strState As String
Dim strCity As String


' Prompt user to verify they wish to add new value.
    If MsgBox(NewData & " is not in list. Add it?", vbOKCancel) = vbOK Then
        ' suppress the error about your item not being in the list
        Response = acDataErrAdded
        'collect the rest of the data you need
        strState = InputBox("Enter 2-letter State Abbreviation", "State")
        strCity = InputBox("Enter City Name", "City")
        'write it to your table
        Set db = CurrentDb
        Set rs = db.OpenRecordset("ZipList")
            With rs
                .AddNew
                !zip = NewData
                !city = strCity
                !state = strState
                .Update
            End With
    Else
    ' If user chooses Cancel, undo changes.
        Response = acDataErrContinue
        Me.cboZip.Undo
    End If

ex:  Set rs = Nothing 'close recordset and database
     Set db = Nothing
     Exit Sub
eh:  MsgBox Err.Description, , Err.Number
     Resume ex
End Sub

---------- Original Message ----------------------------------
From: DorisH3 at aol.com
Reply-To: Access Developers discussion and problem solving<accessd at databaseadvisors.com>
Date:  Mon, 6 Dec 2004 19:42:47 EST

I want to apologize ahead of time for my ignorance of Access but I am new to 
this and would appreciate any help that would come my way. 

I have a Customer entry form whereby I am entering customer first name, 
customer last name, address, zip code, City, State and remarks.  I also have a 
ZipCode table where I have  Zip Code, City, State fields. When entering data into 
the Customer Entry Form the Zip Code field goes out and looks up the City and 
State and returns the value from the table on exit from the Zip Code field.  
My question is this:  when a value is not found in the Zip code table that 
matches the entered zip code, how do I from the Customer entry form add the new 
Zip code, City and State to the Zip Code table?

Again, I appreciate any help you can extend me.

Doris
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


--
Thanks,

Liz Doering
Symphony Information Services
www.symphonyinfo.com
763.391.7400
--