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.

[ChipR]

You never set the rowsource if the table isn't linked.

[tuxalot]

Thanks Chip. So now we have this IF statement:

                    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"
                        Me.lstReportName.RowSource = strRowSource
                        
                    End If

Table is linked, listbox still empty...

[Stewart Ross]

Tux, you should use the VBE editor to return the rowsource string value (using the messagebox currently commented out in line 12 of the current code version), then copy this into the query editor - switch to SQL view to do so. Check that the SQL actually works when you run the query.

If it does run from the query editor, the query rowsource string itself is OK and something is not right with setting the rowsource for the listbox. If it does not work in the query editor it is not going to fill the listbox either...

-Stewart

[tuxalot]

works now! I had to remove the parenthesis. Final code:

                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 * 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 & "ORDER BY ReportCaption"
                        Me.lstReportName.RowSource = strRowSource
                        Me.lstReportName.Requery
                        
                    End If

Exit_lstReportName_Click:
                    Exit Sub

Err_lstReportName_Click:
                    Select Case Err.Number

                        Case 3265        'Linked Table does not exist
                            MsgBox "You are missing a link to your State Reports database. I will open a form to allow you to locate this file", vbCritical, "Missing Database Link"
                            DoCmd.OpenForm "frmChangePathToStateReports"
                            DoCmd.Close acForm, Me.Name

                        Case 3011, 3024, 3044        'Table is linked, but Linked Table Path not valid
                            strRowSource = strRowSource & "ORDER BY ReportCaption"
                            Me.lstReportName.RowSource = strRowSource
                            Me.lstReportName.Requery

                            'MsgBox "It appears that your State Reports database could not be located.  I will open a form to allow you to update the path to this file", vbCritical, "Link Not Valid"
                            'DoCmd.OpenForm "frmChangePathToStateReports"
                            'DoCmd.Close acForm, Me.Name

                        Case Else
                        
                            MsgBox Err.Description & Err.Number, vbExclamation, "Error in lstReportName_Click()"

                    End Select

                    Resume Exit_lstReportName_Click

Thanks for the help.

Tux