Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL FROM Statement and combo box 1

Status
Not open for further replies.
LHWC

LHWC

Technical User
Joined
Apr 29, 2004
Messages
52
Location
US
I have several queries (Select and Union) that I would like to respond to a combo box selection. Specifically, I need to change the table reference in the FROM statement.
I realize that the ususal way to do this is to have all records in one table, then use criteria to select the appropriate records. The issue is that the individual tables are linked to Excel spreadsheets that also drive parametric solid models. If I append each to one master table I lose the dynamic link.
How can I have the FROM statement respond to the user selection?
 
Sort by date Sort by votes
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]


 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
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]


 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Are you using A2k?

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


 
Upvote 0 Downvote
2002 DAO
 
Upvote 0 Downvote
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:

Code: 
Dim db as DAO.Database
db.QueryDefs("[i]yourqueryname[/i]").SQL = Replace(db.QueryDefs("[i]yourqueryname[/i]").SQL,"[i]ORIGINALtablename[/]", "[i]NEWtablename[/i]")
DoCmd.OpenQuery "[i]yourqueryname[/i]"
db.QueryDefs("[i]yourqueryname[/i]").SQL = Replace(db.QueryDefs("[i]yourqueryname[/i]").SQL,"[i]NEWtablename[/]", "[i]OLDtablename[/i]")
db.close

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]


 
Upvote 0 Downvote
Thanks, this looks good. What is the function of the and [/] in the Original tablename clause?
 
Upvote 0 Downvote
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]


 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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:

Code: 
Dim db as DAO.Database
db.QueryDefs("yourqueryname").SQL = Replace(db.QueryDefs("yourQUERYname").SQL,"[i]ORIGINALtablename[/]", Me![ComboBoxName])
DoCmd.OpenReport "yourREPORTname"
db.QueryDefs("yourqueryname").SQL = Replace(db.QueryDefs("yourQUERYname").SQL,Me![ComboBoxName], "OLDtablename")
db.close

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]


 
Upvote 0 Downvote
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!
 
Upvote 0 Downvote
Did you use quotes around the field name?

Code: 
Me.RecordSource = "cbxJobs work"

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


 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
When I remove the quotes from cbxJobs, the reports opens, but it seems that there is no record source. All fields contain #NAME?
 
Upvote 0 Downvote
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.

Code: 
Me!RecordSource = FORMS![[i]formname[/i]]![[i]dbxJobs[/i]]

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]


 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

datajockey123
  • Locked
  • Question Question
Note / Information box with date and time stamp for each entery
Replies
2
Views
989
dhookom
dhookom
combo
  • Locked
  • Question Question
SQL data types in CREATE/ALTER TABLE queries
Replies
2
Views
3K
combo
combo
Swi
  • Locked
  • Question Question
Jet Sql Statement - Select Distinct Multiple Columns
Replies
4
Views
1K
Swi
Swi
C
  • Locked
  • Question Question
Run-time error '3075': Syntax error (missing operator) in query expression 'Birthday = and Birthmonth ='.
Replies
3
Views
592
OzFoxy
OzFoxy
serino
  • Locked
  • Question Question
Delete From Table
Replies
1
Views
928
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top