Query Yields ID Numbers and Not Desired Fields

[dccjr]

I have a query to search a table and populate a combo box with the result. It seems to be working correctly, but it is populating my combo box with the ID numbers, not the selected field data. Here is what i have so far.


I borrowed and modified the Sub and Function from another user.

Public Sub cboFieldID_Change()
Dim strSQL As String
Dim strDBPath As String
Dim RstCount As Integer
Dim strResultType As String
Dim qdf As DAO.QueryDef
'Queries to populate IDNo combobox
            Select Case Me.cboFieldID.Value
               Case "Work Order #"
                   strSQL = "SELECT RCAData.[WorkOrderNo] FROM RCAData ORDER BY RCAData.[WorkOrderNo] ASC;"
                   Set qdf = CurrentDb.CreateQueryDef("", strSQL)
                   GetrstTemp
               Case "Quality #"
                   strSQL = "SELECT RCAData.[QualityNo] FROM RCAData ORDER BY RCAData.[QualityNo] ASC;"
                   Set qdf = CurrentDb.CreateQueryDef("", strSQL)
                   GetrstTemp
               Case "Part #"
                   strSQL = "SELECT RCAData.[PartNo], RCAData.[WorkOrderNo] FROM RCAData ORDER BY RCAData.[PartNo] ASC;"
                   Set qdf = CurrentDb.CreateQueryDef("", strSQL)
                   GetrstTemp
               Case "Status"
                   strSQL = "SELECT RCAData.[Status], RCAData.[WorkOrderNo] From RCAData ORDER BY RCAData.[DefectDate] ASC;"
                   Set qdf = CurrentDb.CreateQueryDef("", strSQL)
                   GetrstTemp
               Case "Area/Cell"
                   strSQL = "SELECT RCAData.[AreaCell], RCAData.[WorkOrderNo] From RCAData ORDER BY RCAData.[DefectDate] ASC;"
                   Set qdf = CurrentDb.CreateQueryDef("", strSQL)
                   GetrstTemp
                 Case "Status"
                   strSQL = "SELECT DISTINCT RCAData.[Status], RCAData.[WorkOrderNo] FROM RCAData ORDER BY RCAData.[DefectDate] ASC;"
                   Set qdf = CurrentDb.CreateQueryDef("", strSQL)
                   GetrstTemp
                Case "Area/Cell"
                    strSQL = "SELECT DISTINCT RCAData.[AreaCell], RCAData.[WorkOrderNo] FROM RCAData ORDER BY RCAData.[DefectDate] ASC;"
                    Set qdf = CurrentDb.CreateQueryDef("", strSQL)
                    GetrstTemp
            End Select
    
End Sub
Function GetrstTemp(qdfTemp As QueryDef, cboFieldID As String, cboIDNo As String)
Dim rstTemp As Recordset
With qdfTemp
  Set rstTemp = .OpenRecordset
  
  With rstTemp
    RstCount = rstTemp.RecordCount
    If RstCount = 0 Then
        msgbox ("There are no records that match you search criteria. Please try again.")
        cboFieldID.Value = Null
    Else
        If RstCount > 0 Then
            Do While Not rst.EOF
                cboIDNo.AddItem (rstTemp.Fields("WorkOrderNo"))
                rst.MoveNext
            Loop
        Else
        End If
    End If
End Function

Can anyone see what may be causing my problem? Thanks in advance for any assistance that you may provide.

[June7]

RCAData is the query? Have you set Lookups for those fields in their home tables?

[ssanfu]

I was looking at your code and saw some errors.

You are using special characters in your object names - "/" and "#". The hash (#) is a delimiter for dates in Access - can and has caused errors. Should only use letters, numbers and the underscore.

Public Sub cboFieldID_Change()
   Dim strSQL As String
   Dim strDBPath As String
   Dim RstCount As Integer
   Dim strResultType As String
   Dim qdf As DAO.QueryDef
   
   'Queries to populate IDNo combobox
   Select Case Me.cboFieldID
      Case "Work Order #"
         strSQL = "SELECT RCAData.[WorkOrderNo] FROM RCAData ORDER BY RCAData.[WorkOrderNo] ASC;"
      Case "Quality #"
         strSQL = "SELECT RCAData.[QualityNo] FROM RCAData ORDER BY RCAData.[QualityNo] ASC;"
      Case "Part #"
         strSQL = "SELECT RCAData.[PartNo], RCAData.[WorkOrderNo] FROM RCAData ORDER BY RCAData.[PartNo] ASC;"
      Case "Status"
         strSQL = "SELECT RCAData.[Status], RCAData.[WorkOrderNo] From RCAData ORDER BY RCAData.[DefectDate] ASC;"
      Case "Area/Cell"
         strSQL = "SELECT RCAData.[AreaCell], RCAData.[WorkOrderNo] From RCAData ORDER BY RCAData.[DefectDate] ASC;"
      Case "Status"
         strSQL = "SELECT DISTINCT RCAData.[Status], RCAData.[WorkOrderNo] FROM RCAData ORDER BY RCAData.[DefectDate] ASC;"
      Case "Area/Cell"
         strSQL = "SELECT DISTINCT RCAData.[AreaCell], RCAData.[WorkOrderNo] FROM RCAData ORDER BY RCAData.[DefectDate] ASC;"
      Case Else
         MsgBox "Error - Not found"
         Exit Sub
   End Select

   Set qdf = CurrentDb.CreateQueryDef("", strSQL)
   GetrstTemp

End Sub

Since the last two statements were identical in each case, I moved them out of the Select Case.

Why are there two Case statements for "Status" and two for "Area/Cell"? I see the "DISTINCT" keyword, but the first "Status" (without DISTINCT" will always be selected. Same for "Area/Cell".

Function GetrstTemp(qdfTemp As QueryDef, cboFieldID As String, cboIDNo As String)
   Dim rstTemp As DAO.Recordset
   Dim RstCount As Integer
   With qdfTemp
      Set rstTemp = .OpenRecordset

      With rstTemp
         RstCount = rstTemp.RecordCount
         If RstCount = 0 Then
            MsgBox ("There are no records that match you search criteria. Please try again.")
            cboFieldID = Null
         Else
            If RstCount > 0 Then
               Do While Not rst.EOF
                  cboIDNo.AddItem (rstTemp.Fields("WorkOrderNo"))
                  rst.MoveNext
               Loop
               '   Else

            End If
         End If

      End With  ' rstTemp
   End With   ' qdfTemp

End Function

In the function, both "END WITH" statements are missing in your code. Don't know if that will help with your problem, but I think it needs to be fixed.

[dccjr]

Thanks ssnafu for pointing out what should have been obvious errors in my code. June7, I have not set Lookups in my tables in the BE (RCAData). I can't do it from home as it is on the server. I will work on that tomorrow. Thanks for all of your help.

[June7]

I was asking if you have set lookups in tables. I did not say you should, in fact, don't. I NEVER set lookups in tables.

Would have to analyze database.

[ssanfu]

but it is populating my combo box with the ID numbers, not the selected field data

So tell us about the combo box.

What is the SQL of the combo box?

On the properties "DATA" tab:
Bound Column = ???

On the "FORMAT" tab:
Column Count = ???
Column Widths = ???

[dccjr]

The SQL for the combo box is determined by the previous combo box. I set them up in a Select Case arrangement shown in the code in the original posting. Here is 1 of the Case sections:

Select Case Me.cboFieldID.Value
               Case "Work Order #"
                   strSQL = "SELECT RCAData.[WorkOrderNo] FROM RCAData ORDER BY RCAData.[WorkOrderNo] ASC;"
                   Set qdf = CurrentDb.CreateQueryDef("", strSQL)
                   GetrstTemp

GetrstTemp is a function to get the recordset. It too is posted above. As for the properties of the combo box, they are:

Bound Column = 1
Column Count = 1
Column Width = 1

Hope this helps.

[dccjr]

Anyone else have any ideas? My problem persists.

[June7]

I am totally lost on what you are trying to accomplish. Want to provide db for analysis?

[dccjr]

I have tried everything I can think of. This problem, as well as the one from the Programming section of this forum. Attached is the FE and BE for the database. HELP.

[ssanfu]

I got your search form to work (mostly). I made a form (frmResults) to show the results of the search.
I fixed a few other things - where I could. There are forms and reports missing. Attached is the FE only.
Remember the BE table is named "RCAData1", not "RCAData".

[ssanfu]

Here are the changes you asked about in the PM.
The results form had the form properties set to not allow additions, edits or deletions. Per your request, I changed the settings to allow all.

[dccjr]

ISSUE CONTINUATION:

I was dry testing the queries on each user form and I noticed that if I change the cboConstraint box, it is not requerying for the cboConValue box. This is despite a command to do so. This section of code is below.

Private Sub cboConstraint_AfterUpdate()
Dim strConstraintSQL As String
Dim RstCount As Integer
Dim dtmStart As Date
Dim qdf As DAO.QueryDef
strConstraintSQL = ""
strConValue.RowSource = ""
RstCount = 0

If Me.cboRptType.Value <> "RCA Event Summary List" Then
Select Case Me.cboConstraint.Value
Case "Event Type"
strConstraintSQL = "SELECT DISTINCT RCAData1.Type FROM RCAData1 ORDER BY RCAData1.Type DESC;"
Case "Event Category"
strConstraintSQL = "SELECT DISTINCT RCAData1.Category FROM RCAData1 ORDER BY RCAData1.Category DESC;"
Case "Work Area/Cell"
strConstraintSQL = "SELECT DISTINCT RCAData1.AreaCell FROM RCAData1 ORDER BY RCAData1.AreaCell DESC;"
Case "Work Order #"
strConstraintSQL = "SELECT RCAData1.WorkOrderNo FROM RCAData1 ORDER BY RCAData1.DefectDate DESC;"
Case "Quality #"
strConstraintSQL = "SELECT RCAData1.QualityNo FROM RCAData1 ORDER BY RCAData1.DefectDate DESC;"
End Select
Me.cboConValue.Requery
Me.cboConValue.RowSource = strContraintSQL
Else
Me.cboConValue.BackColor = RGB(255, 255, 0)
Me.cboConValue.Value = "Multiple Records"
Me.cboConValue.Locked = True
End If
'*** SQL for Summary Report
End Sub

Suggestions?

[June7]

Have you step debugged? Follow the code as it executes. Where does it deviate from expected behavior?

[dccjr]

I have tried step debugging it. I place the cursor at the first line and press F8 (or Step Into in the Debug menu). Nothing happpens. It does not highlight the next line or anything. Just sits there, mocking me. Is my Requery in the right place ? After the Select Case section? It does not seem to be reexecuting the sub after an update to the boxes. I am at a loss. Any ideas why my debug isn't working?