D
dboollu
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 SQL 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 5) 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 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
Now when the form opens it leaves the ACM# blank (due to it being unbound
I'm sure), but returns the 1st row in the table for all the other fields.
Then regardless of what ACM # I enter it returns the next record. I am also
unable to see the string being returned in the immediate window. Then if I
clear all of the fields manually it displays nothing. In the line:
Set qdf = db.QueryDefs("qryACM")
the qryACM is a stored query I made as a basis to use as a multipurpose
query to be altered by the strSQL variable I wrote. The stored query simply
has a table and one field in it. Is this my problem?? Do I need to alter
this process somehow to get it to run properly???
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!!!
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 SQL 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 5) 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 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
Now when the form opens it leaves the ACM# blank (due to it being unbound
I'm sure), but returns the 1st row in the table for all the other fields.
Then regardless of what ACM # I enter it returns the next record. I am also
unable to see the string being returned in the immediate window. Then if I
clear all of the fields manually it displays nothing. In the line:
Set qdf = db.QueryDefs("qryACM")
the qryACM is a stored query I made as a basis to use as a multipurpose
query to be altered by the strSQL variable I wrote. The stored query simply
has a table and one field in it. Is this my problem?? Do I need to alter
this process somehow to get it to run properly???
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!!!