SQL FROM Statement and combo box 1

We can usually do this by cancatenating a SQL string together with the user prompted table names. Please post your SQL for the query and let's see how complicated it is.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

This is the SQL for one of the nine needed:

SELECT
PIC1,TYPE,QTY1,FRW1,STW1,TSVM1,BSVM1,TJL1,SW1,GWD1,STPW1,STPTL1,PBTL1,CRPL1
,HPL1,HML1,TSWGHT1,BSWGHT1,LQTY1,WS1,MTAPE1,PTAPE1,SCW1,MPKT,FRH,JD,JTH,LLT
H,LLANG,LLXCAS,STH,SCASW,HCASW,CASTH,MCTH,BBW,BBTH,BBEXT,STPTH,TRW,BRW,CRW,
GLRAB,MTH,MWD,TSHM,BSHM,IPROF,EPROF,OSFF,TSL,BSL,TGH,BGH,JWD,SJL,LLW,LLL,IL
LW,STPSL,XSTPW,XSTPSL,PBSL,BSSL,CASSL,CASTL,MCW,MCL,BBSL,BBTL,CRTH,CRPTH,TV
PL,BVPL,TVML,BVML,NUMSA,QLON,SCH,XBAR,QTY

FROM [Unit Master]
WHERE PIC1="N"
UNION SELECT
PIC2,TYPE2,QTY2,FRW2,STW2,TSVM2,BSVM2,TJL2,SW2,GWD2,STPW2,STPTL2,PBTL2,CRPL
2,HPL2,HML2,TSWGHT2,BSWGHT2,LQTY2,WS2,MTAPE2,PTAPE2,SCW2,MPKT,FRH,JD,JTH,LL
TH,LLANG,LLXCAS,STH,SCASW,HCASW,CASTH,MCTH,BBW,BBTH,BBEXT,STPTH,TRW,BRW,CRW
,GLRAB,MTH,MWD,TSHM,BSHM,IPROF,EPROF,OSFF,TSL,BSL,TGH,BGH,JWD,SJL,LLW,LLL,I
LLW,STPSL,XSTPW,XSTPSL,PBSL,BSSL,CASSL,CASTL,MCW,MCL,BBSL,BBTL,CRTH,CRPTH,T
VPL,BVPL,TVML,BVML,NUMSA,QLON,SCH,XBAR,QTY

FROM [Unit Master]
WHERE TYPE2 is not null AND PIC2="N";
UNION SELECT
PIC1,TYPE,QTY1,FRW1,STW1,0,FSVM1,TJL1,SW1,GWD1,STPW1,STPTL1,PBTL1,0,HPL1,HM
L1,0,BSWGHT1,0,WS1,MTAPE1,PTAPE1,0,MPKT,FRH,JD,JTH,LLTH,LLANG,LLXCAS,STH,SC
ASW,HCASW,CASTH,MCTH,BBW,BBTH,BBEXT,STPTH,TRW,BRW,0,GLRAB,MTH,MWD,0,FSHM1,I
PROF,EPROF,OSFF,0,FSL,0,FGH,JWD,SJL,LLW,LLL,ILLW,STPSL,XSTPW,XSTPSL,0,BSSL,
CASSL,CASTL,MCW,MCL,BBSL,BBTL,0,0,0,FVPL,0,FVML,NUMSA,QLON,0,0,QTY

FROM [Unit Master]
WHERE PIC1="Y"
UNION SELECT
PIC2,TYPE2,QTY2,FRW2,STW2,0,FSVM2,TJL2,SW2,GWD2,STPW2,STPTL2,PBTL2,0,HPL2,H
ML2,0,BSWGHT2,0,WS2,MTAPE2,PTAPE2,0,MPKT,FRH,JD,JTH,LLTH,LLANG,LLXCAS,STH,S
CASW,HCASW,CASTH,MCTH,BBW,BBTH,BBEXT,STPTH,TRW,BRW,0,GLRAB,MTH,MWD,0,FSHM2,
IPROF,EPROF,OSFF,0,FSL,0,FGH,JWD,SJL,LLW,LLL,ILLW,STPSL,XSTPW,XSTPSL,0,BSSL
,CASSL,CASTL,MCW,MCL,BBSL,BBTL,0,0,0,FVPL,0,FVML,NUMSA,QLON,0,0,QTY

FROM [Unit Master]
WHERE TYPE2 is not null AND PIC2="Y";

Thanks for your help!

Now each of these Selects are using the same record source (i.e. FROM [Unit Master]). What you want is to change the [Unit Master] to another table name depending upon the user picking it from a combobox. This would update all 4 of the FROM statements. Is that correct?

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

Yes, that's the idea. The same applies to the other eight queries. The reports that use these queries will then show values from the selected Table, each of which is a distinct job in our plant. The reports are, essentially, Bills of Materials.

Are you using A2k?

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

2002 DAO

This should be easy. But, we must always start the query SQL out with the tablename being a know given because after we change it and run the query we want to change it back. You see we are going use the Replace function. This function requires something to look for and then what to change it to. If we want to be able to do this over and over we have to always have the query setup to know what to look for.

In your query example here the following will work and can be proliferated to other setups:


Make sure that the old tablename is only found in the SQL string in the FROM statement. Otherwise, it will change also. Doesn't look like a problem thought.

You can see how this works and should be able to modify it for all of your queries. Just manage it correctly and always return the tablename to the one that you started with so that subsequent Replace functions can find it.

Post back if you have any questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

Thanks, this looks good. What is the function of the and [/] in the Original tablename clause?

Sorry about that. Ignore them. We use them to post and make the characters italics and I messed it up. Remove the and [/]. Also, the Newtable name can be a reference to the combobox control if your user is selecting the table name in that manner. If so then don't use the quotes and just make a reference to the control on the form.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

I have tested this and it indeed changes the table reference. Thanks!
I've used the following to change the RecordSource of a report on opening to no avail:

Me.RecordSource = cbxJobs

cbxJobs is the same combo box used in the query code. When I open thge form the record source field is empty. Any ideas?

Your RecordSource for the Report or Form should always be this query. We are changing this query's input table so that different data is made available but you should NOT change the RecordSource to the ComboBox.

The ComboBox reference should be what you use to change the table in the code that I provided. You combobox has a list of table names I presume. Put the code I provided in the Afterupdate event procedure of the ComboBox. Change the references to the fields I called NEWtablename to Me![yourcomboboxname], leaving out the two double quotes. What will happen is that the code will replace the table references in the with the value of the combobox which is the new table name. Then it will run the report. Then return the query to it's original state.

Here is code to put in the ComboBox's AfterUpdate event procedure:


Remember that the report should have your query name as the RecordSource and there is no need to change it.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

Some reports reference the queries and the code you provided works great. But some reports reference the table directly. That's why I want to be able to change the record source on those reports just as we did with the queries. The combo box code is in a public sub. Why doesn't Me.RecordSource = cbxJobs work?
Thanks again for staying with me!

Did you use quotes around the field name?


Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

I get a runtime error when I run the code. When I debug, the line in the On_Click of the Cmd button that opens the chosen report is highlighted. The error text is:

Runtime error 2580. The record source 'cbxJobs' specified on this form or report doese not exist.

This is the code for on open event of the chosen report

Private Sub Report_Open(Cancel As Integer)

Dim strSource As String
strSource = "cbxJobs"
Me.RecordSource = strSource

End Sub

When I remove the quotes from cbxJobs, the reports opens, but it seems that there is no record source. All fields contain #NAME?

Okay, the RecordSource of a Report must be a table or a query or SQL string. Or, you can assign the table name through a variable or a Control.

Try this as you are still not referring to the control on the form.


This code in the Report has to make a full reference syntax to the combobox on the Open Form. The table name picked will then be the value assigned to this property.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]