Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-15-2019, 04:34 AM   #16
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,769
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Passing the name of a FIELD to a query from a combobox

iterate the selections, and concatenate the fields to a SQL statement. It's probably pretty well the same thing the the Query Designer does behind the scenes.

select [field1], [field2], [field3] from table1

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 05-15-2019, 05:06 AM   #17
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,151
Thanks: 0
Thanked 503 Times in 499 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Passing the name of a FIELD to a query from a combobox

Right, VBA code to build SQL statement. Then do what with it? Create a new query object? Basically the option mentioned in post 3.

Could set a form RecordSource and then also have to change textbox(es) ControlSource.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 05-15-2019, 05:22 AM   #18
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,769
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Passing the name of a FIELD to a query from a combobox

this is aircode, so the syntax may be slightly off on these.

Code:
on error resume next
'delete any existing query first.
currentdb.querydefs.delete("xxTemp")
on error goto 0  ' cancel error handler

currentdb.CreateQueryDef("XXTEMP")
currentdb.QueryDefs("xxtemp").sql = "the sql string"
docmd.openquery "xxtemp"

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 05-15-2019, 07:05 AM   #19
sxschech
Newly Registered User
 
Join Date: Mar 2010
Posts: 593
Thanks: 24
Thanked 111 Times in 100 Posts
sxschech is on a distinguished road
Re: Passing the name of a FIELD to a query from a combobox

Expanding on the air code from previous post. Put this into a module. This function will Edit an existing query or Create a new query if it doesn't exist.
Code:
Function EditQryDef(ByVal stQueryName As String) As DAO.QueryDef
'http://www.tek-tips.com/viewthread.cfm?qid=1758828
'20151119
    On Error GoTo NewQueryDef
    
    Set EditQryDef = CurrentDb.QueryDefs(stQueryName)
    
QryDef_Exit:
    Exit Function
    
NewQueryDef:
    Set EditQryDef = CurrentDb.CreateQueryDef(stQueryName)
    Resume QryDef_Exit
End Function
Here is an example of how to call it.

Code:
Sub qrytesting()
    Dim stsql As String
    Dim qd As DAO.QueryDef

    stsql = "SELECT FieldName1, FieldName2 " & _
                        "FROM YourTable_Query "
                Set qd = EditQryDef("qryYourQueryToModify")
                qd.SQL = stsql
End Sub
sxschech is offline   Reply With Quote
Old 05-15-2019, 07:26 AM   #20
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,002
Thanks: 20
Thanked 376 Times in 369 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Passing the name of a FIELD to a query from a combobox

Quote:
Originally Posted by melodyh40 View Post
I was pretty much an idiot when I sent it up originally and, for that database, there's no turning back now.
I wouldn't say Idiot, not by a long shot. Not versed in data normalization, but if it accomplishes what you need, definitely not an idiot.

It also isn't to late to go back and update. I'd check to see if there is a requirement for each of the classes to actually have a "Completion date" or a "Class Number" or other such unique identifier. If this is a requirement, HR will normally want enough to identify and verify compliance.

You CAN create a query your existing table to add the new child records. It would insert in to the child table each employee number and a "ClassID" that you hard code in, but you would use a where clause for the matching field in your existing table to drive this.

For future design needs, do any of the classes employees have to take expire? I deal with Nurses, so we have to track medical training and health records that often need to be renewed yearly or every other year.

Mark_ is offline   Reply With Quote
Reply

Tags
combo box , field , query

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Passing report field value to query rjrichar40 Reports 0 05-20-2009 09:20 AM
Passing a date field to a Query scampbell Queries 3 04-09-2007 01:58 AM
Passing a Field Name into a SELECT Query? ac163601 Queries 4 08-09-2006 03:29 AM
Passing a Field Name as a Parameter to a Query John Baker Reports 1 12-06-2005 08:28 AM




All times are GMT -8. The time now is 04:45 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World