Access sample database Issue

[vaa571]

Hi,

I am using 2003 ISSUE sample database to store RMAS and now i need to make a
the search by RMA #. I have created another table in this database where the
RMA # is being stored (it is not in the "issue" table). The table is called
RMA Board Log and has a field "RMA #". I would like to be able to search by
the RMA #. The form has a search button but when i click it does not find the
matching number, instead it load all records. I can search by the "Title"
which i am storing the assy p/n (Title is a field in the Issue table). Any
help will be appreciated.

 

[KARL DEWEY]

I am confused. From your description of your data the Issue table does not
have RMA within it. If this is true then how do you expect to search?

 

[John W. Vinson]

Hi,

I am using 2003 ISSUE sample database to store RMAS and now i need to make a
the search by RMA #. I have created another table in this database where the
RMA # is being stored (it is not in the "issue" table). The table is called
RMA Board Log and has a field "RMA #". I would like to be able to search by
the RMA #. The form has a search button but when i click it does not find the
matching number, instead it load all records. I can search by the "Title"
which i am storing the assy p/n (Title is a field in the Issue table). Any
help will be appreciated.

What is in the [RMA #] field in the Board Log table? Have you defined a
relationship between the Issues table and the Log table? What code exists in
the search command's button?

 

[vaa571]

the RMA # store numbers i.e. 6305, that is my tracking reference of the
issue. Yes, there is a relantionship between the issues table and the MRB
Board Log table; copy of the code:

Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Assigned To
If Not IsNull(Me.AssignedTo) Then
'Create Predicate
strWhere = strWhere & " AND " & "Issues.[Assigned To] = " &
Me.AssignedTo & ""
End If

' If Opened By
If Not IsNull(Me.OpenedBy) Then
'Add the predicate
strWhere = strWhere & " AND " & "Issues.[Opened By] = " &
Me.OpenedBy & ""
End If

' If Status
If Nz(Me.Status) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Issues.Status = '" & Me.Status & "'"
End If

' If Category
If Nz(Me.Category) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Issues.Category = '" & Me.Category
& "'"
End If

' If Priority
If Nz(Me.Priority) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Issues.Priority = '" & Me.Priority
& "'"
End If

' If Opened Date From
If IsDate(Me.OpenedDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Issues.[Opened Date] >= " &
GetDateFilter(Me.OpenedDateFrom)
ElseIf Nz(Me.OpenedDateFrom) <> "" Then
strError = cInvalidDateError
End If

' If Opened Date To
If IsDate(Me.OpenedDateTo) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Issues.[Opened Date] <= " &
GetDateFilter(Me.OpenedDateTo)
ElseIf Nz(Me.OpenedDateTo) <> "" Then
strError = cInvalidDateError
End If

' If Due Date From
If IsDate(Me.DueDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Issues.[Due Date] >= " &
GetDateFilter(Me.DueDateFrom)
ElseIf Nz(Me.DueDateFrom) <> "" Then
strError = cInvalidDateError
End If

' If Due Date To
If IsDate(Me.DueDateTo) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Issues.[Due Date] <= " &
GetDateFilter(Me.DueDateTo)
ElseIf Nz(Me.DueDateTo) <> "" Then
strError = cInvalidDateError
End If

' If Title
If Nz(Me.Title) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Issues.Title Like '*" & Me.Title &
"*'"
End If


If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "Browse Issues", acFormDS, , strWhere, acFormEdit,
acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Browse_All_Issues.Form.Filter = strWhere
Me.Browse_All_Issues.Form.FilterOn = True
End If
End Sub

Function GetDateFilter(dtDate As Date) As String
' Date filters must be in MM/DD/YYYY format
GetDateFilter = "#" & Format(dtDate, "MM/DD/YYYY hh:mm:ss AM/PM") & "#"
End Function

Thanks,

Hi,

I am using 2003 ISSUE sample database to store RMAS and now i need to make a
the search by RMA #. I have created another table in this database where the
RMA # is being stored (it is not in the "issue" table). The table is called
RMA Board Log and has a field "RMA #". I would like to be able to search by
the RMA #. The form has a search button but when i click it does not find the
matching number, instead it load all records. I can search by the "Title"
which i am storing the assy p/n (Title is a field in the Issue table). Any
help will be appreciated.

What is in the [RMA #] field in the Board Log table? Have you defined a
relationship between the Issues table and the Log table? What code exists in
the search command's button?

 

[vaa571]

Hi Karl,

The RMA # is stored in another table which has relationship with the issues
table. I think i need to modify my search code to look for that.

Thanks,

 

[KARL DEWEY]

Your RMA Board Log table has a field "RMA #" for storing the RMA but what is
the field in the Issue table that relates to it?

 

[vaa571]

It relates to "Title" field in the issue table

Your RMA Board Log table has a field "RMA #" for storing the RMA but what is
the field in the Issue table that relates to it?

 

[KARL DEWEY]

In your orignal post you said 'I can search by the "Title" which i am storing
the assy p/n (Title is a field in the Issue table).'
So if the 'assy p/n' is stored there how are you also storing the RMA number?

 

[vaa571]

The RMA number is not stored in the issue table, it is in the MRB Log table.
I added a sub-form into the issues form so i can stored all the details
related to the "Title" (which i am using the p/n)

 

[KARL DEWEY]

How are your table related? What data element do they have in common?

Post sample data from your tables and then an example of what you would like
the output to look like.