D
doodle
Greetings all. Windows xp, Access 97.
I have a customer switchboard that contains a search option. The search
works for all options except one. (Search Teardown Data By Spindle
Serial #)
tblTD_SSN
Fields: ReportID,SpindleSN
frmTD_DataEntry
This is a main form that contains subforms. Spindle Serial # is a
subform, sbfmTD_SSN and the field in the subform for Spindle Serial #
is SpindleSN as well.
The reason I am so stumped on this one is that it works if you search
Quality Data By Spindle Serial # and quality is set up the same way,
with the same field names and also has a subform for SpindleSN in the
amin form.
HELP!!!
-doodle
Here is my code: (On click for search button on switchboard)
Private Sub cmdSearch_Click()
Dim i As String
Dim cntCust As Integer
Dim myFrm As String
Dim myTbl As String
Dim x As String
Dim y As String
' Concatenates the two user options into one variable
'For example, search Quality Data by Spindle Serial #
' would be "Quality Data.Spindle Serial #"
i = cmdEditData.Value & "." & cmdSearchBy.Value
' Sets the appropriate forms and tables according to user selection
Select Case cmdEditData.Value
Case Is = "Orders Data"
myFrm = "frmOrders_DataEntry"
myTbl = "tblOrders"
Case Is = "Teardown Data"
Select Case cmdSearchBy.Value
Case Is = "Spindle Serial #"
myFrm = "frmTD_DataEntry"
myTbl = "tblTD_SSN"
Case Is = "Machine Serial #"
myFrm = "frmTD_DataEntry"
myTbl = "tblTD_Main"
End Select
Case Is = "Quality Data"
Select Case cmdSearchBy.Value
Case Is = "Spindle Serial #"
myFrm = "frmQuality_DataEntry"
myTbl = "tblQuality_SpindleSN"
Case Is = "Machine Serial #"
myFrm = "frmQuality_DataEntry"
myTbl = "tblQuality"
End Select
End Select
' Checks to see if the user wants to enter a value or select one
from the list
' Sets the search value to either the text box or list box result
If optSearch.Value = 1 Then x = txtTypeValue.Value Else x =
lstSQL.Value
'Sets the Field name variable according to the user options
Select Case i
Case Is = "Orders Data.Customer", "Teardown Data.Customer": y =
"[Customer]"
Case Is = "Orders Data.Machine Serial #", "Quality Data.Machine
Serial #", _
"Teardown Data.Machine Serial #": y = "[MachineSN]"
Case Is = "Quality Data.Spindle Serial #", _
"Teardown Data.Spindle Serial #": y = "[SpindleSN]"
End Select
' Counts the records in the table that match and prompts the user
' if there is more than one match
cntCust = DCount(y, myTbl, y & "Like ""*" & x & "*""")
If cntCust > 1 Then
MsgBox Prompt:="There are " & cntCust & _
" records that match your search. Use the 'Next Record'
button to see additinal records.", _
Buttons:=vbOKOnly, Title:="Message From Adria"
End If
' Opens the form, filtered to the records where the field name
= the search value
DoCmd.OpenForm myFrm, WhereCondition:=(y & "Like ""*" & x &
"*""")
End Sub
I have a customer switchboard that contains a search option. The search
works for all options except one. (Search Teardown Data By Spindle
Serial #)
tblTD_SSN
Fields: ReportID,SpindleSN
frmTD_DataEntry
This is a main form that contains subforms. Spindle Serial # is a
subform, sbfmTD_SSN and the field in the subform for Spindle Serial #
is SpindleSN as well.
The reason I am so stumped on this one is that it works if you search
Quality Data By Spindle Serial # and quality is set up the same way,
with the same field names and also has a subform for SpindleSN in the
amin form.
HELP!!!
-doodle
Here is my code: (On click for search button on switchboard)
Private Sub cmdSearch_Click()
Dim i As String
Dim cntCust As Integer
Dim myFrm As String
Dim myTbl As String
Dim x As String
Dim y As String
' Concatenates the two user options into one variable
'For example, search Quality Data by Spindle Serial #
' would be "Quality Data.Spindle Serial #"
i = cmdEditData.Value & "." & cmdSearchBy.Value
' Sets the appropriate forms and tables according to user selection
Select Case cmdEditData.Value
Case Is = "Orders Data"
myFrm = "frmOrders_DataEntry"
myTbl = "tblOrders"
Case Is = "Teardown Data"
Select Case cmdSearchBy.Value
Case Is = "Spindle Serial #"
myFrm = "frmTD_DataEntry"
myTbl = "tblTD_SSN"
Case Is = "Machine Serial #"
myFrm = "frmTD_DataEntry"
myTbl = "tblTD_Main"
End Select
Case Is = "Quality Data"
Select Case cmdSearchBy.Value
Case Is = "Spindle Serial #"
myFrm = "frmQuality_DataEntry"
myTbl = "tblQuality_SpindleSN"
Case Is = "Machine Serial #"
myFrm = "frmQuality_DataEntry"
myTbl = "tblQuality"
End Select
End Select
' Checks to see if the user wants to enter a value or select one
from the list
' Sets the search value to either the text box or list box result
If optSearch.Value = 1 Then x = txtTypeValue.Value Else x =
lstSQL.Value
'Sets the Field name variable according to the user options
Select Case i
Case Is = "Orders Data.Customer", "Teardown Data.Customer": y =
"[Customer]"
Case Is = "Orders Data.Machine Serial #", "Quality Data.Machine
Serial #", _
"Teardown Data.Machine Serial #": y = "[MachineSN]"
Case Is = "Quality Data.Spindle Serial #", _
"Teardown Data.Spindle Serial #": y = "[SpindleSN]"
End Select
' Counts the records in the table that match and prompts the user
' if there is more than one match
cntCust = DCount(y, myTbl, y & "Like ""*" & x & "*""")
If cntCust > 1 Then
MsgBox Prompt:="There are " & cntCust & _
" records that match your search. Use the 'Next Record'
button to see additinal records.", _
Buttons:=vbOKOnly, Title:="Message From Adria"
End If
' Opens the form, filtered to the records where the field name
= the search value
DoCmd.OpenForm myFrm, WhereCondition:=(y & "Like ""*" & x &
"*""")
End Sub