Populating Datasheet with SQL Query

[dccjr3927]

Good Afternoon:

I am trying to populate a datasheet form with a SQL query. When I execute, the fields say #Name?, not the actual data.

Private Sub cmdEditRec_Click()
Dim gstrEditSQL As String


gstrEditSQL = ""
    Select Case Me.cbxSearchBy.Value
        Case "Customer Name"
            gstrEditSQL = "'SELECT(UPC, Category, Employee, Marketplace, Defect, CustomerName, OrderNumber, Status, DefectNotes, CallNotes) FROM DefectEvents WHERE CustomerName =' & Me.lstSearchResults.Selected;"
        Case "UPC"
            gstrEditSQL = "'SELECT(UPC, Category, Employee, Marketplace, Defect, CustomerName, OrderNumber, Status, DefectNotes, CallNotes) FROM DefectEvents WHERE UPC =' & Me.lstSearchResults.Select;"
        Case "Order Number"
            gstrEditSQL = "'SELECT(UPC, Category, Employee, Marketplace, Defect, CustomerName, OrderNumber, Status, DefectNotes, CallNotes) FROM DefectEvents WHERE OrderNumber =' & Me.lstSearchResults.Selected;"
        Case "Status"
            gstrEditSQL = "'SELECT(UPC, Category, Employee, Marketplace, Defect, CustomerName, OrderNumber, Status, DefectNotes, CallNotes) FROM DefectEvents WHERE Status =' & Me.lstSearchResults.Selected;"
        Case "Defect Category"
            gstrEditSQL = "'SELECT(UPC, Category, Employee, Marketplace, Defect, CustomerName, OrderNumber, Status, DefectNotes, CallNotes) FROM DefectEvents WHERE Category =' & Me.lstSearchResults.Selected;"
    End Select
    DoCmd.Close acForm, Me.Name
    DoCmd.OpenForm "frmDefViewEdit", acNormal, gstrEditSQL
 
End Sub

Do I need to set the value for each in code?

[ranman256]

you dont need any code.
make the 5 queries,
put them in a table for a combo to read: SearchBy, Qry
(user sees col1, but col 2 is the value)

docmd.openquery me.cboBox

[pbaldy]

I'd go a different way. Base the form on a query with your SQL without criteria. Then cut your code to just the last bit and open the form this way:

http://www.baldyweb.com/wherecondition.htm

In other words:

Case "Customer Name"
  gstrEditSQL = "CustomerName ='" & Me.lstSearchResults.Selected & "'"

then

DoCmd.OpenForm "frmDefViewEdit", , , gstrEditSQL

[pbaldy]

By the way your SQL is invalid, you're never adding the single quote after the listbox reference.

[dccjr3927]

Here is my changed code.

Case "Work Order"
              Select Case Me.cbxSearchBy.Value
                Case "Order Number"
                    gstrViewSQL = "OrderNo ='" & Me.lstSearchResults.Selected(0) & "''"
                    DoCmd.OpenForm "frmWOView", , , gstrViewSQL
                Case "UPC"
                    gstrViewSQL = "UPC ='" & Me.lstSearchResults.Selected(0) & "''"
                    DoCmd.OpenForm "frmWOView", , , gstrViewSQL
                Case "Employee"
                    gstrViewSQL = "Employee ='" & Me.lstSearchResults.Selected(0) & "''"
                    DoCmd.OpenForm "frmWOView", , , gstrViewSQL
            End Select

It is opening the form correctly, but all fields are blank. I suspect that it is a problem with the Control Source property with the textboxes on frmWOView, but am unsure what it should be. There are 4 to populate (tbxOrderNo, tbxUPC, tbxEmployee, and tbxDate_Time).

[pbaldy]

Use this:

http://www.baldyweb.com/ImmediateWindow.htm

to see what gstrViewSQL is resolving to. Looks like you have an extra apostrophe at the end.

[dccjr3927]

gstrViewSQL is resolving to OrderNo ='0' for this code

Case "Order Number"
                    gstrViewSQL = "OrderNo ='" & Me.lstSearchResults.Selected(0) & "'"
                    DoCmd.OpenForm "frmWOView", acFormDS , , gstrViewSQL
                    Debug.Print gstrViewSQL
                Case "UPC"
                    gstrViewSQL = "UPC ='" & Me.lstSearchResults.Selected(0) & "'"
                    DoCmd.OpenForm "frmWOView", acFormDS, , gstrViewSQL
                Case "Employee"
                    gstrViewSQL = "Employee ='" & Me.lstSearchResults.Selected(0) & "'"
                    DoCmd.OpenForm "frmWOView", acFormDS, , gstrViewSQL

In the case of OrderNo, there will only be 1 record result. However, with UPC and Employee, there will be multiple records. I read the page on Multi-Select Listbox http://www.baldyweb.com/multiselect.htm but this seems to be for reports. Would it be the same for a form? Since the user will not be able to edit these, should I use a report instead?

[pbaldy]

The same method will work for a form, but it appears you aren't getting a valid value from the listbox. Is it multiselect or not? If it is you need to use the looping code to get all the selections, if not

Me.lstSearchResults

should return the selected value.