Query Won't Accept Multiselect LB Criteria

Mar 23, 2005

Hello,

I've scoured the dozen archived threads that relate to my challenge, but I still can't quite overcome it.

I have an aggregate query that runs but returns no values.

This is how I've tried to set things up:
A user makes a multiple selection in a list box on a form.
The multiple selection gets strung into an SQL that I feed to a hidden control on the form.
Whatever is in the hidden control then becomes the Where clause that I reference in a Criteria cell in my query.
The query is run when the user presses a command button.

Can anybody tell what's wrong with my code? Thanks very much in advance!

Private Sub cmdTractSelect_Click()
On Error GoTo Err_cmdTractSelect_Click

'Declare variables
Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Dim qry As QueryDef
Dim frm As Form
Dim ctl As Control
Dim varItem As Variant
Dim strSQL As String

'Assign values to the variables
Set db = CurrentDb()
Set tbl = db.TableDefs("City")
Set fld = tbl.Fields("Tract")
Set qry = db.QueryDefs("Param")
Set frm = Forms!frmTractSelect
Set ctl = frm!lstTract
strSQL = "[Tract]="

'Grab the list-box selection and string it into SQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " Or [Tract]="
Next varItem

'Make sure the user selected at least one tract
If Len(strSQL) = 0 Then
MsgBox "You didn't select anything" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If

'Trim the SQL
strSQL = Left$(strSQL, Len(strSQL) - 12)

'Insert the SQL into a hidden control that will feed_
'the criteria of the Tract field
txtWhere.Value = strSQL

'Run the query
DoCmd.OpenQuery ("Param")

Exit_cmdTractSelect_Click:
Exit Sub

Err_cmdTractSelect_Click:
MsgBox Err.Description
Resume Exit_cmdTractSelect_Click

End Sub

View Replies


ADVERTISEMENT

Can A Query Accept Value From A String.

Aug 8, 2006

Can i create a query that will have a string in the Criteria box, where that string is assigned a value in VB code?

for instance:

Dim xyz As String

xyz = "TV"



and I put in the Criteria Box WHERE xyz = "Pizza"

View 4 Replies View Related

Query Accept Input From Form

Apr 27, 2006

Hey Folks,

I have a form that has two things on it:

A button to open a query
A list box with names in it

How do I get the query so that it will select all records where the name field matches the chosen name from the list box?

View 2 Replies View Related

Access Query To Accept Multiple Check Box Parameters

Nov 9, 2007

Hi,

I really hope someone will be able to help me with this one as I am sure im just missing something simple.

I have an unbound form which has 20 yes/no unbound check boxes. The purpose of the form is to allow users to tick the various fields and a subform return the results. The subform, which does requery when a check box is ticked is based off a query. Initially, I wanted all the records to display before any check boxes are ticked so I have used the following criteria:

Like IIf([Forms]![Search]![Field1]=False,"*",[Forms]![Search]![Field1])

Which basically reads if field1 is no then display all records, else display all yes. Now that works fine but what I would like to have working is that if a client ticks field1, field2, and field3 it displays all records that have ‘yes’ in either field. Currently, if more than field is ticked the query treats it like:

Field1 And Field2 And Field3 And etc = true

I want to be able to select several check boxes and have the query return results for each check box that was checked. I would like to avoid doing this by having an append and delete query per checkbox.

Thank you

View 13 Replies View Related

Queries :: Append Query For Multiselect Combo Box (has 2 Table)

Jul 11, 2013

"I have 1 "main" access file and "Portable".

In the form of main I creat buttom to open and apped the table of other access file-portable.accdb- to the main table!"

I had a problem before about attachment field appending anj JHB solved that problem in this link.

"I have 1 "main" access file and "Portable".

In the form of main I creat buttom to open and apped the table of other access file-portable.accdb- to the main table!"

See that problem and download attachment of that topic.

But I want to append a table with multi select combo box. That combo box field has query from table "list" and i want to append this 2 table (asli & list) to a main database!!!

Attachment instruction:
1-solved pervious problem(OK)
2-problem with combobox query(has ERRROR)

View 14 Replies View Related

Modules & VBA :: Multiple Selections In List Box - Query To Accept Parameters?

Mar 27, 2014

How to modify my query to accept parameters from a multiple choice list box. This is the SQL code behind my search query:

SELECT AircraftOperators.RegistrationNumber, AircraftOperators.PassengersNumber,
AircraftOperators.ManufactureYear, AircraftOperators.EmailToOperator, AircraftOperators.ExteriorPhoto,
AircraftOperators.InteriorPhoto, tblListOfAircraftOperators.OpratorName, tblAircrafts.AircraftType, tblAirports.AirportName, InfoSource.SourceType, tblCountry.CountryName, tblAircraftCategory.AircraftCategory

[Code] ....

I have got an unbound multiple list box called List44 (Row Source: query based on table tblAircraftCategory, Multi Select - Extended) that needs to be passing parameters to my main query called AircraftSearch2. The multiple choice list box have the following fields:

1. Piston
2. Turbo Prop
3. Entry Level Jet
4. Light Jet
5. Super Light Jet
6. Midsize Jet
7. Super Midsize Jet
8. Heavy Jet
9. Ultra Long Range
10. Helicopter
11. Air Ambulance
12. Cargo
13. Vip Airliner
14. Airliner

The user will use the form for selecting search criteria (the form is called SearchForm2 and has 5 combo boxes, 3 text boxes and one multiple choice list box).

I'm very new to access and need to modify (or coding a separate module) my query to include my multiple choice list box in my query?

View 4 Replies View Related

Exporting Query To Text File - Too Small To Accept The Amount Of Data ... HELP!! WHY?

Mar 30, 2006

Hi folks,
I have a query that returns about 3500 records (and runs very well I have to say). the issue comes when I attempt to export that queries results to a comma seperated text file. It gives me the message that the field is too small to accept the amount of data..bla bla bla... I've looked this up and it mentions stuff about memo fields and issues with that, but I don't have any memo fields in any of the tables that this is pulling information from. Does anyone have a clue why this would be happening...please help..this is urgent.


Thanks - J

View 1 Replies View Related

Multiselect

Mar 31, 2005

Hi all

I have an unbound form. To the left I have a list box with a list of employee names. To the right I have a bunch of text fields. Once I've entered the data, I click on a button and it adds a new record. It works perfectly provided I add one employee at a time. It gets painful when I have multiple employees and I'm entering the same data.

Is there a way I can multiselect employees from the listbox, fill in the data fields and add multiple records which just one click??

I'd appreciate any advice.

View 4 Replies View Related

Multi-MultiSelect With 'All'

Aug 27, 2005

Now i need some help here.
I adapted this code from somewhere on this forum. But iam having a problem with using the 'ALL' criteria.
It Opens a Report Dynamically from multiple selections in the two multiselect ListBoxes, but when i select 'All' and select any other item in another listbox
For Example(All in Names and Home1 in Homes) it gives me all the Results.
I have tried several criterias to solve it out but iam still failing.
Hope there will be help. Attached is a sample db in A2K and A97.
Thanks in Advance.

View 3 Replies View Related

Multiselect Search

Dec 25, 2004

Merry X-mas everyone!!!

I'm new to this message board and I'm glad i came across it. I've been looking for a way to multiselct search using Combo boxes.Reading through the threads here i found one (Posted by SBaxter). I've use the SQL Statements and VB code for my search. It works but not like his for some reason. The problem i'm having is when you open the form it asks for criteria like a parameter Query does. You have to type it in before it loads, unlike his where you click what you want out of combo boxes. At first i thought it was something i did wrong with the query but i've noticed that in his DB if you have the form open and then open the query it doesn't act like parameter. It does if the form is close. With mine that doesn't matter i still have to type it in! So i'm guessing it's with how i set up the list box in my form. I've attached a copy for someone to look at.




Thank.
RichM

View 5 Replies View Related

Multiselect Update

Dec 19, 2004

Hi all

I have a list box with a bunch of names in it that links to a subform on a master form. At the moment I click on a name and update details in the subform. It would be nice if I could multiselect names then update details all at once without having to individually change each one. I tried the multiselect property but I get index or relationship errors. I don't have any relationships (because I'm not adding new records... just updating) and I changed the indexes to NO. Didn't work. I tried!! Any idea how I might get this working??

I appreciate any advice.

Damon

View 13 Replies View Related

MultiSelect Listbox

Apr 19, 2005

I have a table, "people" which has all the main data stored. I have another table, "organizations" which I'd like to be able to use on a form to select which organizations people belong to. I created a button on my main form which opens another form on which a listbox with all the organizations shows. What I'd like to do is from the main form, click a button that brings up a new form. On the new form, I can select the affiliations. Then, when I close that form, a box on the main form shows what i've just selected. I got this to work with the listbox, but when I changed it to multi-select it stopped working. Help!! Thanks.

View 5 Replies View Related

MultiSelect ListBox

Jun 25, 2005

I want to be able to select multiple items from a listbox, click a button, and then print the data related to that item selected using a report.

In the report to print the data, I have added the criteria
[Forms]![Print Labels]![List2]
This is the name of the listbox. However, this doesn't print the items selected in the list box.

Do I have to do this through VBA, or can I accomplish this through a query in the report?

Thanks!

View 1 Replies View Related

Setting Query Criteria To Be 'blank' Depending On The Criteria Of A Combo Box

Oct 21, 2006

I have set up a database that stores actions (i.e jobs). In the table; two of the fields are...'required completion date' and 'actual completion date'. I wish to lookup, by using a query, all of the open actions (those which havent yet been complete (i.e the 'actual completion date' is null)) and then later on all those which are overdue (i.e the 'actual completion date' is null And the 'required completion date' <today....this being the criteria for an overdue action).

However, I have used a form which has a combo box which contains the values open and overdue. When a selection has been made I want a form to display with the results depending on the selection that has been made. I am capable of creating a form based on a query, but am unsure of how to construct the query with the correct criteria based on the option that is selected from the form.

Any help would gratefully be appreciated. Thanks

View 5 Replies View Related

Toggle MultiSelect Property

Mar 10, 2006

Does any one know of a way to toggle a multiselect listbox's property programmatically?
The One In The VBS Help doesn't work!

View 5 Replies View Related

Multiple Multiselect Listboxes

Feb 18, 2006

Hi, my problems are numerous!
I have a form with a number of multiselect listboxes. Their sources are individual tables such as YEARS, PARISHES, CLIENTS. The underlying data is stored in a separate table. I want users to be able to select none, one or some values from each listbox and run a report.
As simple listboxes with one selection permitted this worked fine. I know that my problem is in concantenating the values.

I have tried attaching some code to the OnClick function of a cmd button on the form. I found this code referred to a number of times here and have tried to adapt it but no luck. I have now reduced my form to one listbox to test stuff out- this is the code-

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Form!testform
Set ctl = frm!lboparish
strSQL = "Select * from StJamesAgric where [Parish]="
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [Parish]="
Next varItem

'Trim the end of strSQL
strSQL=left$(strSQL,len(strSQL)-300)

The first error I get is "can't find field 'testform'"

My form is called testform
The listbox is called lboparish
The table with all the data is called StJamesAgric

What am I doing wrong???? And also, if this code EVER works, do I need to repeat it for each listbox or do I insert refernces to each list box within just one subcommand?

Any help greatly appreciated.

Stella

View 5 Replies View Related

Database Will Not Accept Data!

Dec 27, 2004

Dear Access Gurus:

Help! I have creadted a basic foirm to collect data. The problem is when I switch to form view, the "add data, delete data" butons are disabled. I tab thorough the fields and I cannot add any data??????????????

I have checked and rechecked the data properties of the form tab and allow additions and allow deletions are set to yes.

I am at the end of the rope and I am completely lost. Do I have to reinstall Access?

Any help is most welcome..........please

Regards,

Dion

View 2 Replies View Related

Multiselect Values In A Listbox

Jan 28, 2005

I was wondering whether it is possible to select multiple values in a list box on a form that can be stored in a table.

View 4 Replies View Related

Form Won't Accept New Data

Mar 25, 2005

I have created a form based on a query that joins two tables. When I run the form, I am able to input new data into all fields that are from one of the two tables, but it won't accept new data into the second table. I don't get an error message that might help me identify the problem. The join between the tables is of the "include all records from Table A and only those records from Table B where the joined fields are equal."

Can anyone suggest where my problem might lie? Thanks!

kgm

View 2 Replies View Related

Clear MultiSelect Listbox

May 21, 2007

I have a multiSelect Listbox that has email recipients. I have a command button with Me.emaillist = Null, and after I click the command button the Listbox is still highlighted in black. What VBA command do I create to completely clear the multiSelect Listbox, including the highlighted selection?

losstww

View 1 Replies View Related

Forms :: Multiselect Using Checkboxes

Nov 8, 2013

I have a single-user application about to become multi-user.

There is a table, displayed in a form as datasheet, where we do lots of things with the selected items.

Code:
MyID MyItem IsSelected ....
1______ItemA__ Yes
2______ItemB__ Yes
3______ItemC__ No
....

The user checks the IsSelected (bound to a checkbox) for the items required for further processing, does the processing and starts over. The function of the column IsSelected is to hold the boolean signifying whether or not to process the record, and to bind to the checkbox in the form.

How to retain the user interface (i.,e. selecting using one or more checkboxes) if the database is to be used by more than one user?

View 4 Replies View Related

Can Access Accept An SQL Request From Another Program?

Feb 8, 2006

I have noticed diagrams showing Microsoft SQL Server accepting SQL statements sent by “SQL Requesters” over connections. I would like to know if Microsoft Access can be used in that fashion?

Put another way, can Access be configured to accept an SQL statement that another program (e.g. a VBA program in a VBA enabled 3rd party app) creates?

Currently, my VBA program instantiates an Access database object and then manipulates it (I just add a record to one of the tables), then closes and destroys it. This solution seems fragile (it stops working—I can explain more if needed). I would like to know if the technique inferred by my question would be more reliable.

View 2 Replies View Related

Is It Possible To Select The Same Item Twice Using A MultiSelect List Box

Jun 29, 2007

I would like to be able to create a recipe (saved in its own table) by pulling items from a multiselect list box (from the raw materials table) but which allows me to add the same ingredient more than once.

I work for a chemical manufacturing company and we need to track the cost of goods by recipe (final product), by linking the ingredients (where the cost of the raws are associated) to the final prodcut. Several of our products need to have chemicals added in the beginning, but then again at the end of the procedure unlike normal kitchen recipes.

(I sure hope I don't have to learn VBA . . . I'm totally new at Access!)

Thanks in advance for your help.

View 3 Replies View Related

DatePart Will Only Accept 6th Jan 2005 As Week 2

Jun 2, 2005

When using 'ww' as the criteria in the DatePart calculation it will not accept 1 for 6th Jan 2005 which is week 1, you have to enter 2 and it will select it, in fact all the weeks so far in 2005 are out by 1. To cure it you can add on -1 to the query and it works fine until you go back to the previous year and it screws up those dates.

Any ideas?

View 11 Replies View Related

Multiselect Listbox Passing To A Field

Jan 26, 2005

I'm trying to create a database where a single "Classification" field is populated by selections made in a multiselect listbox and I can't figure out how to do this. Any help people can provide?

Table 1:
Name_ID <pk>
Name
Classification

Table 2:
Classification_ID <pk>
Classification

What I want to happen is click on a button next to the Classification field (text) and a popup form with a multi-select list loads (this part is easy, of course). The user can select as many classifications as they want, click the ok button and each item selected then goes back to the first form and populate in the Classification field (seperated by commas or semi-colons).
This possible?

View 3 Replies View Related

Problem With Code In Multiselect Listbox

Jan 27, 2005

Hi,

I have adapted code from ghudson's example on
'http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=52736

I have a subform called frmsJobPartsUsed, which contains a multiselect list box where the user can select multiply parts used for one job and click a save button, which saves the parts to rows on the same forms (see picture). The user then enters the number used and that number is taken away from the UnitsInStock.
This form is made up of the following two tables;
TblStore
PartNo
PartName
UnitsInStock
ReOrderLevel
Discountinued
Remark

tblPartsUsed
PartUsedID
JobDetailsID
PartNo
PartUsedNum
NumberUsed

The multiselect listbox is made form tblStore, PartNo, PartName and Discontinued = 0

This all works fine so far.

What is need to do is before the parts selected are saved to the table I want to run some code to check
If a part’s UnitsInStock is equal to 0 then Message box saying no stock left need to reorder. It won’t save it to the table.
Or else
If UnitsInStock is greater than 0 but less than or equal to ReOrderLevel
Message box saying Stock running low need to reorder asap.

I have this kind of working but it doesn’t seem to be finding the correct UnitsInStock for the part selected.
Here is the code;


Private Sub cmdAnswer_Click() 'SAVE BUTTON
On Error GoTo ErrMsg:

'Code adapted from ghudson's example on
'http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=52736

Dim myFrm As Form, myCtl As Control
Dim mySelection As Variant
Dim iSelected, iCount As Long

Dim myDB As DAO.Database
Dim myRst As DAO.Recordset
Dim myRstCount As DAO.Recordset

Set myDB = CurrentDb()
Set myRst = myDB.OpenRecordset("tblPartsUsed")

Set myFrm = Me
Set myCtl = Me.lstAnswers

iCount = 0
'Count number of selected records/items
For Each mySelection In myCtl.ItemsSelected
iCount = iCount + 1
Next mySelection

'Check if anything is slected
If iCount = 0 Then
MsgBox "There are no Parts selected..", _
vbInformation, "Nothing selected!"

Exit Sub
End If

StrSQLCount = "SELECT tblPartsUsed.JobDetailsID, Count(tblPartsUsed.PartNo) AS CountOfPartNo " & _
"FROM tblPartsUsed " & _
"GROUP BY tblPartsUsed.JobDetailsID " & _
"HAVING (((tblPartsUsed.JobDetailsID)=" & [Forms]![frmJobs]![JobDetailsID] & "));"
Set myRstCount = myDB.OpenRecordset(StrSQLCount, dbOpenSnapshot)




'SART OF MY CODE TO CHECK FOR UNITSINSTOCK

For Each mySelection In myCtl.ItemsSelected
If Me.UnitsInStock.Value = 0 Then
MsgBox "Out of Stock!" & Chr(13) & "Please returen to Orders or Store to Re-Order Stock. " & Chr(13) & " ", vbOKOnly + vbCritical, "Re-Order Stock"
myCtl.Selected(mySelection) = False

Else
If Me.UnitsInStock.Value > 0 And Me.UnitsInStock <= Me.ReOrderLevel.Value Then
MsgBox "The Store is running low on stock!!" & Chr(13) & " Please return to Orders or Store to re-order as soon as possible.", vbInformation, "Need to Re-Order Stock"
End If
End If
Next mySelection
'END





iCount = 0

'Go throught each selected 'record' (ItemsSelected) in listbox
For Each mySelection In myCtl.ItemsSelected
'Current count of selected items
iCount = iCount + 1
'Print value to Immediate Window
Debug.Print myCtl.ItemData(mySelection)
'Add answers
With myRst
.AddNew
.Fields("JobDetailsID") = Forms![frmJobs]![JobDetailsID]
.Fields("PartUsedNum") = iCount
.Fields("PartNo") = myCtl.ItemData(mySelection)
.Update
End With
Next mySelection

'Requery form
Me.Requery

ResumeHere:
Exit Sub

ErrMsg:
MsgBox "Error Number: " & Err.Number & _
"Error Description: " & Err.Description & _
"Error Source: " & Err.Source, vbCritical, "Error!"
Resume ResumeHere:

End Sub



Private Sub cmdUnselect_Click() 'UNSELECT BUTTON
On Error GoTo ErrMsg:

'Code adapted from ghudson's example on
'http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=52736

Dim myFrm As Form, myCtl As Control
Dim mySelection As Variant
Dim iSelected, iCount As Long

Set myFrm = Me
Set myCtl = Me.lstAnswers

'Count number of selected records/items
For Each mySelection In myCtl.ItemsSelected
iCount = iCount + 1
Next mySelection

If iCount = 0 Then
MsgBox "There are no selections to Un-Select..", _
vbInformation, "Nothing selected!"
End If

'Go throught each selected 'record' (ItemsSelected) in listbox
For Each mySelection In myCtl.ItemsSelected
Debug.Print myCtl.ItemData(mySelection)
myCtl.Selected(mySelection) = False
Next mySelection

ResumeHere:
Exit Sub

ErrMsg:
MsgBox "Error Number: " & Err.Number & _
"Error Description: " & Err.Description & _
"Error Source: " & Err.Source, vbCritical, "Error!"
Resume ResumeHere:
End Sub







Any help would be greatly appreciated.
Thanks in advance
Rita

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved