Multi-Table Access = Head Spinning Fun

[DangerMouse9]

I'm having a couple of problems here with my databases:

1) For some reason I can't load all the zip codes from the zip code database into my combo box anymore. It worked before, but now it just hangs.

2) The big problem I'm having is I have three tables. Table one is the employee table which contains the zip code. Table Two is the ZipCode table which contains each zip code, and it's corresponding city and state abbreviation. Table Three is the State table which has an autonumber, State Abbreviation, and State Name.
Now, when the user selects an employee from the employee combo box, I want to fill in the rest of the information automatically. How can I get it so it links the zip codes together to fill in the city (which is in a text box) and the state name (which is in a combo box)?

here's the bit of code that I have so far for this, but it isn't working.

    Private Sub FormShow()
        Dim objItemEmp As ListItemNumeric
        Dim objItemZip As ListItemNumeric
        Dim strID As String
        Dim objDREmp As DataRow
        Dim objDRZip As DataRow
        Dim picpath, picloc As String

        picpath = "images\employee\"

        objItemEmp = CType(cmbEmpID.SelectedItem, ListItemNumeric)
        With mobjDS.Tables("Employee").Rows
            objDREmp = .Find(objItemEmp.ItemValue)
        End With

        'objItemZip = CType(cmbZipCode.SelectedItem, ListItemNumeric)
        'With mobjDS.Tables("Zip_Code").Rows
        'objDRZip = .Find(objItemZip.city)
        'End With


        txtFName.Text = objDREmp("emp_first").ToString()
        txtLName.Text = objDREmp("emp_last").ToString()
        txtAddress.Text = objDREmp("emp_address").ToString
        strID = objDREmp("emp_zip").ToString
        FindItem(cmbZipCode, strID)
        txtEmail.Text = objDREmp("emp_email").ToString
        txtPicName.Text = objDREmp("emp_pic_name").ToString

        If txtPicName.Text <> "" Then
            picloc = picpath & txtPicName.Text
        Else
            picloc = "images\na.bmp"
        End If
        Try
            pcbEmployee.Image = Image.FromFile(picloc)
        Catch filenotfound As IO.FileNotFoundException
            MessageBox.Show(filenotfound.Message, "Picture Error")
        End Try

        strID = objDREmp("emp_zip").ToString
        FindItem(cmbZipCode, strID)
        'txtCity.Text = objDRZip("City").ToString
        'strID = objDRZip("Zip").ToString()
        'MessageBox.Show("Practice")
        'FindItem(cmbZipCode, strID)


    End Sub

    Private Sub FindItem(ByVal cmbCombo As ComboBox, ByVal strID As String)
        Dim intLoop As Integer
        Dim blnFound As Boolean
        Dim objItem As New ListItemNumeric()
        For intLoop = 0 To cmbCombo.Items.Count - 1
            objItem = CType(cmbCombo.Items(intLoop), ListItemNumeric)
            If objItem.ItemID = CInt(strID) Then
                cmbCombo.SelectedIndex = intLoop
                blnFound = True
                Exit For
            End If
        Next

        If Not blnFound Then
            cmbCombo.SelectedIndex = -1
            MessageBox.Show("Not found")
        End If
    End Sub

Your help is appreciated. Thanks.

[Edneeis]

What about it doesn't work? Are you getting an error? If so what error? What is happening and what do you want to happen?

Also if you have 1 table with just the zipcode and another table with the zipcode and city then isn't that redundent? If there is no other information with the zipcode then why have it in its own table?

[DangerMouse9]

there is no city or state in the employee table. It's to put it in third normal form, which is kind of required for the project.

I want all the text/combo boxes to be populated with the information from the database when the user changes the Employee ID. However, I'm not sure how to get it to access the correct city as that is in a different table. And it from there it has to get what the state is based on the state abbreviation.

[Edneeis]

I think I misread your table layout, my bad. Anyway you can query the datatable by creating a dataview and filtering. Also setting the text of a combo should select the item. Here is an example:

[DangerMouse9]

that didn't help me too much. Let me post what I have. There are several forms that need to access multiple tables and I do not want to use any data-bindings. I also want to refresh the cities and states when the user changes the zip code.

For some reason it will not allow the entire zip code table to load, it hangs for some weird reason, and tells me that my primary key in the inventory table doesn't exist so that part of it doesn't work.

If anyone can help, please either msg me, email me, or what not and if you add code to it, please comment it with what you've done and maybe how it works. your help is much appreciated.

EDIT: the attachment didn't seem to work you can PM me and I'll see if I can send you some of the code to see if you can help. Thanks.

[Edneeis]

What does 'the attachment didn't seem to work' mean? Did you get an error? If so which one? If you are using VS 2002 then you'll need to convert it (see my signature for help with that).

[DangerMouse9]

I didn't see the attachment on my message so I'm assuming it didn't work. I tried to attach my code on there so people could take a look at it to see what I was doing wrong.