Textbox entry to retrieve associate table values

I have a form which has five different "areas" on it where  the user enters information.  The first 'area' is a combo box (created via the wizard) where the user selects a customer name and that populates the proper address info.  

The next two 'areas' have a text field where the user enters a string.   I need each one to perform a specific query (or something) associated with the manually entered value. The fields are labeled ACM# and melt #.  In other words, the user types in a string, presses the Tab key,  then the query  associated with that entered string will execute, and it will display the relevant results in the associated textboxes on the form.  Each of the 'search' text boxes is unbound.  Each 'area' is assiciated with it's own table.  I'm not really sure what I should be using as the main forms  Record Source.  Currently, I have used the query builder to include all necessary tables (there are  and fields.  

The first text field ACM # is associated with the Customer Requirements table.  The second text field (Melt #) is contained within a subform called Chemical Analysis which will be associated with the next subform called Chemical Requirements.  Chemical Analysis is associate the the ChemResults table and Chemical Requirements is associated the the ChemRequirements table.  Upon the user entering the Melt # I need a query to execute where it retrieves the appropriate values from both tables and populates the correct subform.  

The company I work for does not want to use any type of command button, so this is why I believe I need to use the After Update event.  I can not link this CustomerName to the rest of the form, because there will be many products (first textbox) etc associated with this specific customer.  There are similar reasons for the other 'areas'  And my employer does not want drop downs for these other values that the user will manually enter the string.

I am attaching a 2 bmp's of my existing form.  I originally put in three subforms, but I do not know if I can accomplish this without using subforms.  

Here is a code snippet of something I have tried, but it does not return the correct values, and, if the form is blank, it returns nothing.

Private Sub txtACM_AfterUpdate()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    Set db = CurrentDb
    Set qdf = db.QueryDefs("qryACM")
    strSQL = "SELECT CustomerRequirements.* " & _
             "FROM CustomerRequirements " & _
             "WHERE CustomerRequirements.ACM = '"" & Me.ACM & ""' "
             
    qdf.SQL = strSQL
    DoCmd.OpenQuery "qryACM"
   
    Debug.Print strSQL
   
End Sub

Any help on this would be greatly appreciated.  I am obviously not a great programmer, so this is driving me (and my employer) nuts!!   Thank you ahead of time!!!


Start Free Trial