Combo Box Query To Post Result To A Seperate Query

Sep 27, 2006

Hello, I have a combo box on a form which lists some names generated from a table.

I would like the selected name to be inputted into the 'critera' of another query called 'qryPBCustLevel' and for that query to be run.

I have tried to code this, but it is crashing at the point it trys to add the name into the query.

Can anyone help? Code listed below.

Sub cmbPB_AfterUpdate()

'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the Query definition

strSQL = "SELECT DISTINCT tblTempPB.PB_NAME" & _
"FROM tblTempPB"

strOrder = "tblTempPB.PB_NAME;"

' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[PB_NAME] = '" & Me![cmbPB] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark

'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs("qryPBCustLevel")
qryDef.SQL = strSQL & " " & strOrder

'Open the Query
DoCmd.OpenQuery "qryPBCustLevel", acViewNormal

End Sub

Thanks, Steve. :confused:

View Replies


ADVERTISEMENT

Queries :: Conditional Query To Post Result In Field And Filter Result Records?

Mar 5, 2014

I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg

In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.

View 1 Replies View Related

Combo Box Performs Query, Update Combo Box With Only Result?

Aug 4, 2006

Here's the deal:

I'm an extreme newbie, I do not know access very well, nor do I know VBA, I do know PHP.

I have a for in access that has 2 user input fields, one for prodid one for shipid. I have a combo box that upon entering data into the previous 2 fields, it does a query against an MsSQL database looking for a record that has both. In any case there will only be 2 outcomes, either 1 record, or null. I would like to have that same combo box automatically use the result as it's value so that users don't have to check the box, because they won't, and so that the rest of my VBA will be able to function properly.

Can anyone assist?

Thanks.

View 2 Replies View Related

Table And Its Query Reflect Combo Box ID Equivalent Not Text Result

Mar 10, 2014

Whats the best way to show the text result of a combo box selection when displaying the information through a query? I notice the table is also reflecting only the numerical ID result of the selection so I'm not sure how to take this result and display it as a text result to the user.

View 7 Replies View Related

Query Problem With Two Seperate Fields

Mar 24, 2005

I am working on a DB and would like to include the following information on a report. The information is as follows;

tblPersonnel.Name
Tony
Jennifer
James
Tom

tblPersonnel.LOA
tblPersonnel.Vacation

Tony is on a Leave of Absence (LOA) Jennifer is on Vacation which leaves James and Tom at work. Lucky them! The fields "LOA" and "Vacation" are two separate check boxes.

I would like to limit the query to display employees that are either on a "LOA" or on "Vacation" Since they are in two separate fields how do I write the expression? I know how to limit it to only employees on LOA. Or employees who are on vacation, but how do I qry those who are on vacation or LOA?

View 3 Replies View Related

Merging 2 Seperate Types Of Query Recordset

Feb 2, 2006

Hi there,

I have to queries as below

one which shows a few stats figures etc all on a single row but has 6 columns altogether

the other shows some figures by function area and as there are like 7 function areas there are 7 rows but only 2 columns

i want all this informaiton onto one report so i guess that i need to merge this data together onto the one query some how

whats the best way to do this?

thanks

View 2 Replies View Related

General :: Adding Count To Result Of Query Depending On Month And Result

Aug 18, 2013

I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?

I'm using access 2003.

View 4 Replies View Related

Query Help-1st Time Post

Jun 5, 2007

hi this is my first post. This is probably so basic you will think i am stupid but your help would be appritiated.
I have a very simple database which displays the questions stored in a table in a form . The recipent answers the questions generaly yes or No. On answering yes or no i wish to attach values (hidden) to thier answers and then based on the answers produce a simple set of bar charts. I have tried but do not know how to assign the values to the questions . i would then like to produce 5 bar charts 1 for each of the catogories and one as a total (as an overall and also in sub groups of which there are 4 each of which has a field with a specific prefix either b,c,r,s dependant on the type of question. )

hope someone can help


Page Jump

View 1 Replies View Related

Newbie Post - Query Question

Mar 14, 2006

Hi, i'm a newbie so please be gentle :)

ok, i have 2 tables, one is for part number and product ID and the other has product ID and Vehicle details.

i know how to set up a parameter query but just can't quite get the result i want, which is to set up a query based on the result of the first query.

in short i want to enter a part number and it comes up with the vehicle details.

Many thanks in advance

Ty

View 5 Replies View Related

Queries :: Post To Query From Form

Aug 31, 2013

I have a from where users select from one of four Options. The Print putton on the form enters text into an unbound textbox based on the selection. Here is the code:

If Me.optWhichList = 1 Then
Me.txtWhichList = WHAT TO PUT HERE
ElseIf Me.optWhichList = 2 Then
Me.txtWhichList = "LS"
ElseIf Me.optWhichList = 3 Then
Me.txtWhichList = "AS"
Else: Me.txtWhichList = "SM"

In the Query I have teh Criteria as
[Forms]![popfrmChooseMailingLabels]![txtWhichList]

Works fine for options 2, 3 and 4 which choose the relevant criteria in the corresponding Query, but I want Option 1 to select all records. What do I put there so that all records are selected (i.e., no criteria)

View 8 Replies View Related

Using Query Formula Information To Create Another Formula In A Seperate Query

Nov 20, 2006

I have a query that calculates input information into a value that then needs to be compared to another query values and will be used to output a % change in a third query. Is there any way to make this happen? Thank you in advance!

View 2 Replies View Related

Queries :: If There Is No Result In Query Need To Have Default Result Zero

Oct 12, 2013

I there is no result in query, I need the default result zero in my form field. I only use query wizard to create queries.

View 5 Replies View Related

Append Data From Unbound Combo Boxes To Seperate Table

Aug 4, 2007

I have a form setup that has 5 combo boxes where a user will select one item from each combo box. Each combo box is from a differant table. Once this is done I want to be able to append or update the results into a new table I created. My problem is I don't know how to submit the data from the combo boxes to the new table. Can anyone help me with this?

Thanks

View 11 Replies View Related

Queries :: Use Result Of One Query To Generate Another Query

Apr 15, 2013

I have 2 tables, Event and Person Particulars.

In an event, groups of 2-5 persons may be tagged to this event by a randomly generated number (using autonumber).

Let's say Tom (social security number: 12345X) is tagged to events 2, 5 & 6. There are of course other persons together with Tom in the above 3 events.

If I would like to find out who are the persons who are in events which Tom had participated in, how do I find them using a query?

Currently, I'm thinking of using a searchform where it would return his "associates" if I just query using his social security number, i.e. 12345X.

View 10 Replies View Related

Query ~ Seperate 1 Field Into 8 Fields Using Pipes "|"

Aug 21, 2006

Information
8|REPORT BY THE COMMITTEE APPOINTED TO STUDY CLASSIFICATION, TRANSPORTATION, DISTRIBUTION STORAGE AND SALES OF PESTICIDES IN *******|***** *. *****|******** ** *** **********|****|||PA2|

Okay, so my office had this emergency data dump, we lost the library, the records were saved but to do so they had to be backed up as .t files :-s which means less formating then a txt file :eek: :eek: :eek: , anyway, my office is hopeful I can fix their problems, sorry bout the ***'s, I was told that I can not put that information up on the net for security/privacy purposes. Anyway, what I have to figure out to do is to take this 1 field and split it into 8 fields, these correspond with the | character. If you see 2 || or more together it means that field was empty. Anyway, I just need a way to break these into 8 seperate fields, any feed back would be great.

View 2 Replies View Related

Query From An Existing Query Result

Jun 27, 2005

Hi,

I'm writing a database for a record label. Here is the issue. I need to calculate total number of CD's sold for a specific state, and CD Name. I have a query that asks for the name of the state, and CD Name, it then produces the total number of records from this result. This works fine.

My problem is that I need to use something like a DSUM on the total number of CD Sold for the this given result. I have tried to use the previous query as a Make-Query, and then using the generated table in my next query. However, this has not worked, it just reproduces the old results with out tallying them up into one general record.

Is there anyway to use one query's results in another query.

Your help is greatly appreciated.

View 4 Replies View Related

I Want The Name To Appear Only Once In Query Result

Mar 1, 2006

Hi,

I'm designing this system in which each employee has different area of strength (i.e. Math, Languages,..)

if an employee has 2 or 3 area of strength his name appears in the query more than once. I want his name to appear once.

I tried "group by" but it gave me an error. I think I'm doing it wrong.

Please Help!

CS.

View 7 Replies View Related

ADO Query Result Set

Sep 5, 2011

I am trying to create a VB script to automate a mailing based on several query result sets from access. I have gotten to the stage that the output is correct but have a problem with the 5th and 6th record set query as they only return one record (When in fact there should be at least two for each).

I don't really understand why this is happeneing as the SQL is exactly the same as in the 2nd record set - which works perfectly. Also I've tested the SQL directly in an access query & there are no errors in the formatting that I can see... correct number of records returned.

Code:
Public emailaddress, ccaddress, Subject, body1 As String
Public baserow, toprow, countnumberofrows, emails As Integer
Public tempdir, projectlistdir, WBPATH As String
Option Compare Database
Option Explicit

[code]....

View 5 Replies View Related

Odd Averages Result From Query

Jun 15, 2007

I am trying to calculate the average patients age from 2671 records using this SQL:

SELECT tbl_Customer_Details.DOB, CalcAge([DOB]) AS Age, DAvg("[Age]","qryAvgAge") AS Average
FROM tbl_Customer_Details
GROUP BY tbl_Customer_Details.DOB, CalcAge([DOB]);

why am I getting the result:

68.1131066106

I would have thought that it would have been 68 a whole number, has anyone got any suggestions why this should be.

thanks

View 3 Replies View Related

Query Result To Text Box

Jan 5, 2006

Hi
I have a query with 2 fields, when it is run it returns a result based upon the result of the set criteria.

Can you get this value to be displayed in a text box after a button is pressed.

dave

View 3 Replies View Related

Single Result Query

Mar 1, 2006

Hi

I am new to VBA with access. Im wanting to get the result of a query called "qLastRotaDate" into a variable called "datLastRota". The query returns a single date, it is not possible to have more than one result for this query. I have tried different variations of : datLastRota = qLastRotaDate but cannot get any to work. I'm assuming this is very simple yet I cannot figure it out as am very new to VBA.

Any help would be greatly appreciated. Sorry if this has been dealt with in previous posts but I could not find any info by searching the post.

Thanks
Mikee

View 3 Replies View Related

IIF Query Not Returning Result.

May 27, 2006

Hi,

Can anyone see anything wrong with this query?

I have 10 combo boxes where years are selected. If the last year nothing is selected (meaning is empty) then my query should return the last selected value. It seems the IIF query does not seem to work (Still showing empty). Can you please advise and help me on this?

The query I am using is shown below.

Thank you

dfuas


IIF([Trade].[Vintage_ to] = ' ',([Trade].[Vintage_from9]) OR ([Trade].[Vintage_from8]) OR ([Trade].[Vintage_from7]) OR ([Trade].[Vintage_from6]) OR ([Trade].[Vintage_from6]),[Trade].[Vintage_ to]) AS [Vintage to]

View 4 Replies View Related

Identical Query By With Different Result! Why?

Mar 19, 2007

Hi,

I have a query which I run in the query design view. Then I have the same exact query which I run on the 'background' of a form.

The issue is, both queries turn out to have different record counts(eventhough I am using the same query and same tables). What is going on? Please help.

Thank you.

Joe

View 6 Replies View Related

Query Result Into Variable

Feb 5, 2008

hello every one,
first, im apologize for my English.
I have form that runs until the user answer the all the questions.
I place the last question Id in listbox. do I can place the result of the query (max of question id) in a variant in VB or I must place it in listbox/combobox/textbox?

Tsur triger.

View 14 Replies View Related

Query Giving No Result

Mar 2, 2008

Hi all,

Would appreciate help on this. I have a query which combines the results of 3 queries. Once the underlying queries each has a result I get a result in my query, but if 1 of the underlying queries has no result I get nothing. (I hope this makes sense).

How can I set my query to show results even if the underlying query doesn't.

thanks in advance.

rgs
Ginny

View 5 Replies View Related

Same Query Different Result Win2k / Xp

Apr 13, 2008

Hi,

I have the following query in a access 2000 db

SELECT [5k1].[Race Number], [5k1].[Name], [5k1].[Club], Date()-[Date of birth] AS Age, [5k1].[Handicap Time], [5k1].[Male / Female], [5k1].[Date of birth], [5k1].[handmin], [5k1].[handsecs], [tblRaceEntries].[intEntryRace1], [tblRaceEntries].[intEntryRace2], [tblRaceEntries].[intEntryRace3], [tblRaceEntries].[intEntryRace4], [tblRaceEntries].[intEntryRace5]
FROM 5k1 INNER JOIN tblRaceEntries ON [5k1].[Race Number]=[tblRaceEntries].[RaceNumber];


it is used as a means of input from a form.
When it is used in windows xp then all is good.
When it is used in windows 2k it doesn't work as I intended. The problem being that [5k1].[Race Number] field is reset with a default '0' value of [tblRaceEntries].[RaceNumber]

Whats the difference between the 2k/xp system that causes this.. what the solution?

many thanks

View 3 Replies View Related







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