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.
13 2836 ChipR 1,287
Recognized Expert Top Contributor
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"
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 1,287
Recognized Expert Top Contributor
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 1,287
Recognized Expert Top Contributor
Forgot to mention that the code would be in the form's open event probably.
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
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.
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 1,287
Recognized Expert Top Contributor
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.
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 1,287
Recognized Expert Top Contributor
You never set the rowsource if the table isn't linked.
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...
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
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
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Bill Brinkworth |
last post by:
I want the user to type in part of a word, and I want to return all words
from an Access word table that contains specified letters. If they were to
type "fe??", it should return all words in the...
|
by: Paradigm |
last post by:
I am using Access 2K as a front end to a MYSQL database.
I am trying to run a Union query on the MYSQL database. The query is (much
simplified)
SELECT as ID from faxdata UNION
SELECT as ID ...
|
by: collie |
last post by:
Hi,
I have 2 listboxes. The first gets populated from the db as soon as
the page loads. The second listbox get populated based on the user's
selection from the first listbox. However,...
|
by: Chris Leuty |
last post by:
I am populating a multiselect Listbox from a dataset, with the content of
the listbox filled by one table, and the selections determined from another
table. So far, I have been keeping the dataset...
|
by: P K |
last post by:
I have a listbox which I am populating on the client (it contains a list of
dates selected from calender). The listbox is a server control.
When I get to the server after postback by selecting an...
| |
by: mikes |
last post by:
I have 2 separate queries, which effectively are the same except they
draw data from separate tables. Both tables are (design-wise)
identical, only the data is different. for each query, there are...
|
by: halberd4 |
last post by:
Hello. I have an Access2003 db that I need to create a 'combined' query for. This query will then become the source for a listbox. The query needs to combine Query1 (example)
SELECT Aa, Bb, Cc,...
|
by: wugon.net |
last post by:
question: db2 LUW V8 UNION ALL with table function month() have bad
query performance
Env:
db2 LUW V8 + FP14
Problem :
We have history data from 2005/01/01 ~ 2007/05/xx in single big...
|
by: mcalex |
last post by:
Hi, I'm having trouble trying to populate a list box with data from a parameterized query.
If I set the rowsource property to the query name, when the form opens I get the parameter values dialog,...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |