union query - populating a listbox

[tuxalot]

I have a listbox with a row source using a union query:

SELECT [quniReports].ReportID, [quniReports].ReportName, [quniReports].ReportCaption, [quniReports].ReportCriteriaFlags, [quniReports].ReportRemote FROM [quniReports] ORDER BY [quniReports].ReportCaption;

The union query sql:

TABLE [tblReports] UNION SELECT * FROM [tblReportsState];
 

tblReportsState is a linked table.

Question:

If the db is opened and the linked table is not linked:

    Const conLINKED_SR As String = "tblReportsState"
 
    If Len(CurrentDb.TableDefs(conLINKED_SR).Connect) = 0 Then
 

how can I then change the listbox row source to only show data in tblReports? I assume I would put the code in the listbox before update event.

Thanks for the look.

[ChipR]

Try something like:

Dim strRowSource as String
 
strRowSource ="SELECT ReportID, ReportName, ReportCaption, ReportCriteriaFlags, ReportRemote FROM ([tblReports]"
 
If Len(CurrentDb.TableDefs(conLINKED_SR).Connect) > 0 Then
   strRowSource = strRowSource & "UNION SELECT * FROM [tblReportsState]) "
else
   strRowSource = strRowSource & ") "
end if
 
strRowSource = strRowSource & "ORDER BY ReportCaption"

[tuxalot]

Thanks Chip but it didn't work. I've tried removing my linked db, moving it's path manually and all I get is a blank listbox.

[ChipR]

Try a
MsgBox strRowSource
right before you set the listbox. Then you can take a look at it to make sure it looks valid in either case. That's the easiest way to reveal a mistake I may have made in concatenating the strings.

[ChipR]

Forgot to mention that the code would be in the form's open event probably.

[Stewart Ross]

Minor error in line 5 of post 2, Chip - the literal value referring to the name of a table in the tabledefs collection has to be within quotes:

If Len(CurrentDb.TableDefs("conLINKED_SR").Connect) > 0 Then

I would suggest placing the code in the on-load event of the form, not on open (as the underlying recordset for the form is not available until the form is loaded), then performing a requery of the control after setting its rowsource; the name of the control has not been mentioned, but the line needed is simply

Me![Name of listbox control].Requery

which should be placed after the lines setting the rowsource.

Ah, but what is also missing is the setting of the control's rowsource to the string.

Me![Name of listbox control].RowSource = strRowSource

This must be placed before the requery line in the final code...

Chip's code then becomes

Dim strRowSource as String 
strRowSource ="SELECT ReportID, ReportName, ReportCaption, ReportCriteriaFlags, ReportRemote FROM [tblReports]" 
If Len(CurrentDb.TableDefs("conLINKED_SR").Connect) > 0 Then 
   strRowSource = strRowSource & " UNION SELECT * FROM [tblReportsState] " 
end if 
strRowSource = strRowSource & " ORDER BY ReportCaption;" 
With Me![Name of listbox control]
  .RowSource = strRowSource
  .Requery
End With

Finally, keep in mind that Union queries will only work if the number of fields and their types are exactly the same in both sets - so your table and the SELECT query must have the same number of fields, of the same type, in the same selection order if the union is to work at all.

-Stewart

[tuxalot]

here's the generated sql string:

SELECT ReportID, ReportName, ReportCaption, ReportCriteriaFlags, ReportRemote FROM ([tblReports]UNION SELECT * FROM [tblReportsState])

Which errors with a 'syntax error in join operation' at the word UNION.

[tuxalot]

Thanks Stewart, I have the code in the tab control like this:

                If TabCtl2.Value = 7 Then
 
                    On Error GoTo Err_lstReportName_Click
 
                    Const conLINKED_SR As String = "tblReportsState"        ' linked State Reports table
                    Dim strRowSource As String
 
                    strRowSource = "SELECT ReportID, ReportName, ReportCaption, ReportCriteriaFlags, ReportRemote FROM ([tblReports]"
 
                    If Len(CurrentDb.TableDefs(conLINKED_SR).Connect) > 0 Then
 
                        'OK, we know that conLINKED_SR is a Linked Table, but is the Link valid?
                        'The next line of code will generate Errors 3011 or 3024 if it isn't
                        CurrentDb.TableDefs(conLINKED_SR).RefreshLink
 
                        strRowSource = strRowSource & "UNION SELECT * FROM [tblReportsState]) "
 
                        Me.lstReportName.RowSource = strRowSource
                        Me.lstReportName.Requery
 
                        MsgBox strRowSource
 
                    Else
 
                        strRowSource = strRowSource & ") "
                        strRowSource = strRowSource & "ORDER BY ReportCaption"
 
                    End If
 
 

[ChipR]

Yep. In post #2, line 3 should have ended with:

FROM ([tblReports] "

I forgot the space at the end, so the resulting string got mashed.

[tuxalot]

Post #8 is my current code placed in the tab control. I added the space at the end so now it reads:

strRowSource = "SELECT ReportID, ReportName, ReportCaption, ReportCriteriaFlags, ReportRemote FROM ([tblReports] "

Still no go.