Looking up a set of names with a button (1 Viewer)

jtice01

New member
Local time
Today, 08:07
Joined
Nov 19, 2015
Messages
7
Sorry for this painful first post.


I have a normalized database with
tables
[tblPersonnel]
With fields in this configuration
{autonumber}{name}{billing_rate}

[tblProjects]
{autonumber}{projectname}

[tblMonth]
{autonumber}{_month}


This is my collection table for the inputs of a form which asks for the allotments of a particular person to a particular project during a particular month. All but the final field are look-ups to the above referenced table:
[tblProjectData]
{autonumber}{PDprojectname}{PDperson}{PDmonth}{PDallotment}

The primary user of this database will be administrative personnel who have no knowledge of access.

Because we have hundreds of personnel, I want to allow the user to select a set of names (one by one) and do a search to check whether that set of people are overalloted during a month.
My vision is this:
Primary form with combo-box-(selectsearch). Button (sendtoquery) with the on click set to run a query.
(selectsearch)used as criteria for the query. The query will have relationships to all four tables. The foreign keys should be obvious.

Fields of query are:
{name}{PDprojectname}{PDmonth}{PDallotment}
The output would be displayed on the primary form inside of a subform that would be editable.

My issue is, that I dont know how to use a combo box to select multiple names and send them to the query simultaneously. Is there a means by which I can send multiple selections (using a button or something) to the criteria section of a query?
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 15:07
Joined
Feb 19, 2013
Messages
16,555
first using 'name','month' in field names is not recommended, they are reserved words and using them can produce unexpected results.

second, combo boxes can only be used to select a single value. It sounds like you need to use a multi select listbox. See this link or google 'access multi select listbox' to find out more

https://support.microsoft.com/en-us/kb/827423

However if you have 100's of names this may not be the best approach since the user will have to scroll up and down to find names to select.
 

jtice01

New member
Local time
Today, 08:07
Joined
Nov 19, 2015
Messages
7
However if you have 100's of names this may not be the best approach since the user will have to scroll up and down to find names to select.

That is very helpful. However, I'm still not sure how I can use the comma delimited string as a filtering criteria for a query, which is my ultimate goal. Any words of wisdom there?

It would seem like I would need take that string and send it to the query with some kind of macro.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 15:07
Joined
Feb 19, 2013
Messages
16,555
you would us in in your query

dim cdstr as string
dim sqlstr as string
...
code to collate selections as comma delimited string ending up with something like

'name1', 'name2', 'name3'

...
sqlstr="SELECT *
FROM myTable
WHERE Sometextfield IN (" & cdstr & ")"
currentdb.execute(sqlstr)

or to filter the form

me.filter=" Sometextfield IN (" & cdstr & ")"
me.filteron=true
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:07
Joined
Jan 23, 2006
Messages
15,364
jtice01,
It seems you may not be familiar with vba.

What CJ has shown you is some vba (Visual Basic for Applications). You would have a form with a listbox from which multiple values could be selected. With the values selected from the listbox you would create a string variable of those values separated by commas.
You would create a query...Select.... where....IN(...)
or filter a form as CJ said.
 
Last edited:

Users who are viewing this thread

Top Bottom