Child list for field .. .cannot be created in joined tables

[tombihn]

My original query for data from the database was:

Dim SQL_RecentWorkOrders As String =
        "Select * from workorder WHERE date>#" 
        + dtToday.AddYears(-YearsToGoBack).ToString 
        + "# ORDER BY worknum DESC"

The updated query is as follows:

Dim SQL_RecentWorkOrders As String = 
         "SELECT * FROM workorder 
          INNER JOIN customer ON workorder.custid = customer.custid 
          WHERE workorder.date>#" 
          + dtToday.AddYears(-YearsToGoBack).ToString 
          + "# ORDER BY worknum DESC"

The rest of the code is as as follows:

daAccess = New OleDbDataAdapter(SQL_RecentWorkOrders, cnAccess)
    daAccess.Fill(dsAccess, "WorkOrders")
    Debug.Print(dsAccess.Tables(0).Rows.Count.ToString)

    'Bind Work Orders
    'WorkOrderNumberCbo.DataBindings.Add("Text", dsAccess.Tables(0), "worknum")

    WorkOrderNumberCbo.DataSource = dsAccess.Tables("WorkOrders").DefaultView
    WorkOrderNumberCbo.DisplayMember = "workorder.worknum"
    WorkOrderNumberCbo.ValueMember = "workorder.worknum"

    CustIDTxt.DataBindings.Add("Text", dsAccess.Tables(0), "workorder.custid")
    LastNameTxt.DataBindings.Add("Text", dsAccess.Tables(0), "workorder.operlast")
    FirstNameTxt.DataBindings.Add("Text", dsAccess.Tables(0), "workorder.operfirst")
    DescriptionTxt.DataBindings.Add("Text", dsAccess.Tables(0), "workorder.description")
    AcresTxt.DataBindings.Add("Text", dsAccess.Tables(0), "workorder.acres")
    GridSizeSelection.DataBindings.Add("Text", dsAccess.Tables(0), "workorder.grid")
    cmWorkOrder = CType(Me.BindingContext(dsAccess.Tables(0)), CurrencyManager)

...I added the table identifier, "workorder." in the data member string after receiving the following error: "Cannot bind to the property or column custid on the DataSource.
Parameter name: dataMember".

If I do the following in the immediate window, I get the results shown

?dsAccess.Tables(0).Columns(0).ColumnName.ToString
    "worknum"
    ?dsAccess.Tables(0).Columns(1).ColumnName.ToString
    "workorder.custid"

I'm not interested in updating the data. Other than setting up aliases for each field, how can i reference the tablename.fieldname in the DataMember string parameter for DataBindings.Add? Is there an escape character?

[adam_k]

If these are all your bindings, then you have no use for customers other than to cut down results. If you want to avoid setting aliases, then why don't you select only workorder.* ?

[tombihn]

I'm in the process of adding CustomerName and other information related to the customer to the form and the additional data I'll be pulling in, such as if this is an in-state or out-of-state customer.

Thanks,Tom

[adam_k]

Then I think that you might have a chance if you add the tablename. to the fields in common and leave the rest of the bindings only with the fieldname.

The

?dsAccess.Tables(0).Columns(0).ColumnName.ToString

approach you've used will show you what you should use to bind each field.
ie:

WorkOrderNumberCbo.DisplayMember = "worknum"
WorkOrderNumberCbo.ValueMember = "worknum"

CustIDTxt.DataBindings.Add("Text", dsAccess.Tables(0), "workorder.custid")

[tombihn]

Using worknum worked fine with WorkorderNumberCbo, but as soon as I stepped into the line with workorder.custid (which matches the column name) I get the error

"Childlist for field workorder cannot be created."