Passing Values On Form To Union Query

Oct 20, 2005

I'm currently working on a database which provides copier and postage activity in my department, using Access. I'm trying to debug a query which is called by a form that accepts input for a particular month and year. Whenever the OK button is clicked, it gives a message "The Microsoft Jet database engine does not recognize 'Combination Query' as a valid field name or expression."

"Combination Query" is a union query which calls two intermediate queries, each of which in turn calls a base query. Only one, the query responsible for gathering copier activity, is a crosstab query.

The problem I believe lies in either or both of the base queries only when the form is used. When I use a regular parameter query, I receive no error messages.

Here is the SQL code I used for each:

Query 1: Copier Activity
Code:PARAMETERS [Forms]![frmObtainMonthlyInfo]![cmbMonth] Long, [Forms]![frmObtainMonthlyInfo]![cmbYear] Long;TRANSFORM Sum([big table].Copies) AS SumOfCopiesSELECT [big table].[Copy Code], [User codes].Alias, Sum([big table].Copies) AS [Total Of Copies]FROM [User codes] INNER JOIN [big table] ON [User codes].[Copy Code] = [big table].[Copy Code]WHERE (((DatePart("m",[big table].[Time]))=[Forms]![frmObtainMonthlyInfo]![cmbMonth]) AND ((DatePart("yyyy",[big table].[Time]))=[Forms]![frmObtainMonthlyInfo]![cmbYear]))GROUP BY [big table].[Copy Code], [User codes].AliasPIVOT [big table].[Copier Location];

Query 2: Postage Activity
Code:PARAMETERS [Forms]![frmObtainMonthlyInfo]![cmbMonth] Long, [Forms]![frmObtainMonthlyInfo]![cmbYear] Long;SELECT [big Postage Activity].sAcctNum AS Account, Sum(Nz(cBaseRate*lNumPieces,0)+Nz((cRegisteredFee+ cCertifiedFee+cReturnRecFee+cReturnRecMerchFee+cSp ecDeliveryFee+cSpecHandlingFee+cRestrictedDelvFee+ cCallTagFee+cPODFee+cHazMatFee+cSatDeliveryFee+cAO DFee+cCourierPickupFee+cOversizeFee+cShipNotificat ionFee+cDelvConfirmFee+cSignatureConfirmFee+cPALFe e+cResidualShapeSurcharge)*lNumPieces,0)) AS TotalFROM [big Postage Activity]WHERE (((DatePart("m",[big Postage Activity].[sSysDate]))=[Forms]![frmObtainMonthlyInfo]![cmbMonth]) AND ((DatePart("yyyy",[big Postage Activity].[sSysDate]))=[Forms]![frmObtainMonthlyInfo]![cmbYear]))GROUP BY [big Postage Activity].sAcctNum;

I edited this to make it look similar to a recommendation posted on another forum, but sometimes I would get the message "The Microsoft Jet database engine does not recognize '[Forms]![frmObtainMonthlyInfo]![cmbMonth]' as a valid field name or expression."

I also included PARAMETERS [Forms]![frmObtainMonthlyInfo]![cmbMonth] Long, [Forms]![frmObtainMonthlyInfo]![cmbYear] Long; as the first line in the intermediate tables, but I still receive the same error message.

I'm wondering if there was something I overlooked or if there was a way around this. I can post the other queries if you need those. Many thanks in advance for your help.

View Replies


ADVERTISEMENT

Passing Values From Form To Query?

May 2, 2013

I have a form - RunQuery And I have a query - Q2.

The form has text boxes for dates txtStart2, txtEnd2.

On button click I have a macro.

Query Name Q2

view Datasheet
datamode edit

The query will open

The query has a field Assigned which is Date/Time

In my query design view I have this as the criteria:

>[Forms]![RunQuery]![txtStart2] Or <[Forms]![RunQuery]![txtEnd2]

I also have filter on load set to yes.

However the filter does not seem to work.what is missing or wrong?

View 6 Replies View Related

Passing Values From One Form To Another

Aug 22, 2007

I am trying and have been unsuccessful at passing a value in a textbox from one form to another.

I have a form (Form1) and I click a button and Form2 opens.

What I want is a couple values from textboxes in the Form1 to populate a couple textboxes on Form2 when Form2 opens.

I CANT FIGURE THIS OUT....

PLease help

View 5 Replies View Related

Passing Values To A Form (calendar)

Jan 9, 2008

Hi All,

Thanks to "Beginning Access97 VBA Programming" by R. Smith & D. Sussman, I was able to implement the use of a calendar form to allow a client to select a date, rather than keying one in. And here I thought I was done with the problem....Wrong !!! The calendar form only works if existing records already have a date(shortdate) associated with the record.

But when I try to create a new record, the date field is "null" and I can't figure out how to pass a date value to the calendar control even though it's receiving a null. I get this "type mismatch" error I've bumped into the part of the code that was supplied via the book where the "property set datecontrol (Byval ctldate as Control) is set, but I can't figure out
how to deal with this incoming "null" value from the newly created record.

Can anyone help? I'll check in later on tonight if anyone is needing part of the code as reference to what I'm trying to accomplish.

Thanks so much.......CementCarver

View 14 Replies View Related

Passing Values From Subform To New Form

Mar 21, 2006

Hi

I got a little problem wich i'm trying to solve for several days now.
My situation is like this.
I got a form called "Frm_File_List" and in there a subform "Frm_List_Sub" as in a datasheet. In here there are several records.
Im trying that when u double click on the record that a new form "Frm_View_file" will be opened, but according the record I selected in the subform. So when I doubleclick on ID 7 it opens the form with record matching ID 7.

I tried in vba the following

Private Sub Form_Click()
DoCmd.OpenForm "Frm_View_File", , , "Files_ID = Forms!Frm_List_File!Frm_List_Sub.Form!Txt_Files_ID "
End Sub

But when I double click on the record it gives me this popup screen asking for the ID. When I fill in this manually then it does give me the wanted record in the form. But how do I get his ID passed automaticly?

Thanks in advance

View 3 Replies View Related

Passing Multiple Values To A Query

Sep 16, 2005

Ok, i have a form in wich i want to set the parameters to a query.
On the search button it will open another form to display the data.
i used this code stLinkCriteria = "[ID]=" & Me![id] And "[status]=" & "'" & Me![sta] & "'
the 1st value for status is "active"
it returns a msgbox asking the value of the parameter and a field to input the data.

I can't understant whats wrong....

View 4 Replies View Related

Forms :: Passing All Field Values From One Form To Another

May 28, 2015

I have "LossForm" to record loss of inventory items due to damage, theft, etc. It has "Loss Subform" for input of multiple items. The row has a calculated field "TotalLoss" (from qty * itemcost). The footer of subform has unbound text field =Sum(nz([TotalLoss])). This all works fine. The problem I have is that I need to pass the total to another form. I want to have a pop-up form to use some of the field values from the Loss form. I have been able to pass all of the field values except for the TotalLoss.

LossForm Close Event: "DoCmd.OpenForm "Journal", , , , acFormAdd, , Me.LossID & ";" & Me.LossDate

pop up form:
Set frmPrevious = Screen.ActiveForm
Me.TransactionID = frmPrevious.LossID
Me.EntryType = "Loss"
Me.Date = frmPrevious.LossDate
{ Me.Amount = frmPrevious.TotalLoss doesn't work }
DoCmd.Save
End Sub

I also tried to setup a global, class, and module variable but keep getting error message of undefined variable.

View 2 Replies View Related

Passing Multi Parameter Values To A Query

Aug 14, 2005

I have a query set up which needs to have different criteria at run time depending on values selected by the user. If no option is specified, I need to pick up all records with 'Nulls' in them else, if date is specified by user, I need to pick up all records with date > than specified date. The query is able to pick up the date value from an unbound text box in the form. I used the same field in the form and populated "Is Null" in the field and it does not work. How do I pass this as a paramater to the query from the form?
Is there a way around without setting up 2 sets of queries and reports?

Thanks,Priya :mad: :confused:

View 5 Replies View Related

Modules & VBA :: Passing Values Of Selected Items From First Form Until Third Form

Aug 12, 2013

I would like passing values from first form until third form.

In the first form I have a list box after selecting items (For each selected item in first form I have 4 values) and pressing button (or right click of mouse) the second form will be open, then in the second form I have 2 option (inserting, deleting), when I select inserting or deleting in the second form, third form will be open, in the third form there is a "OK" button, when I press that, passed values from first form will be used for inserting or deleting records to the table.

View 4 Replies View Related

Forms :: Creating A New Record From Another Form And Passing Values?

May 20, 2014

Have a form that contains fields AssociatedProject, AssociatedRelease and then the user hits a button to create a new record and a new form opens to display this newly created record which sets the fields and also sets a field called Type. There is no issue with setting the field values and the new record is created. However, when the form opens to display the record it displays another record instead (usually the one before it). I think it could be related to the fact that the db resides on a shared drive which periodically has slow connectivity. However, I'm looking for a solution, perhaps modifying the code?

Here's the code:

Private Sub Command17_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
On Error GoTo Err_Command17_Click
Set db = CurrentDb()
Set rs = db.OpenRecordset("Tbl_Main", dbOpenDynaset, dbAppendOnly)

[code]....

View 6 Replies View Related

Forms :: Input Information - Passing Values To Form

Jan 30, 2014

I have a database of experimental products. The database allows test technicians to input test information as well as product information (obviously in separate tables). Therefore before a tech logs test information, they have to first make sure the test product is in the database. They can do this by looking up the products that are in the database, and if the current one isn't they can input its information. In the lookup section you can also click on an individual product and lookup its test history.

What I am looking to do is add a button in the product information screen that allows the tech to open the test information input form with the test product information already loaded into the form. The product information portion of the test input form, is two combo boxes, one indicates the test products group and the other is the product number.

Looking up information on Google looks like i might need to use the OpenArgs function, however I have never used that before.

View 3 Replies View Related

General :: Passing A Field Value To Insert Query With Values

Jun 2, 2014

I have a button on a subform that becomes visible if there is no records in the source of the subform. When clicked I want to run a query that will insert a record on to the source of the subform. There is one field in the query that I need to get from the parent form.The first part works OK - the button is visible when the source file to the subform for this main form record, is empty.

If I run the query against the source file it inserts the new record after it has asked for the value of the variable field.My problem is that when I try to run the query when the button is clicked It can't find form![ClientFileFrm]![ClientId]..This is my code on the subform

Private Sub Form_Load()
If Me.RecordSource <> "" Then
If Me.Recordset.RecordCount = 0 Then
Me.AddSettingsButton.Visible = True

[code]...

The ClientFileFrm is the main form.I can't seem to reference the clientId variable back to the main form.

View 1 Replies View Related

Forms :: Passing Values From One Form To A New Record In Another Form

Oct 25, 2013

Have one form that contains values AssociatedProject and AssociatedRelease that need to be passed onto another form that opens with a new record. Have tried different variations based on what I read here and couldn't get them to work.

Initial form - frm_ViewList contains the values that I need to pass on and has a "Add" button to bring up the new form that also creates a new record. The add button contains the following:

Dim stDocName As String
MyAssociatedProject = Me.AssociatedProject
MyAssociatedRelease = Me.AssociatedRelease
stDocName = "Frm:ManageQuestionsAnswersProc"
DoCmd.OpenForm stDocName, acNormal
DoCmd.GoToRecord , , acNewRec

Then in the second form Frm:ManageQuestionsAnswersProc the following code is contained in the Before Insert:

Me.AssociatedProject = MyAssociatedProject
Me.AssociatedRelease = MyAssociatedRelease

View 3 Replies View Related

Forms :: Passing Operator Values To Criteria Field In Query

Mar 11, 2014

I have a query which contains figures. i have a search form based on this query and need to search using comparison operators such as <10000 or >500.

Is there a way to pass these value to criteria field in query ?

View 14 Replies View Related

Modules & VBA :: Passing Values (From Two Column Listbox) To Saved Query

Oct 3, 2013

How can I pass two (2) values to a saved query ? These values are in a form that has a listbox with two (2) columns. The name of the form is 'Previous Evaluation Form'. I'm able to retrieve the values from both columns of the listbox in the form and I've already created the query. Both are working fine, but can figure out how to pass the query's criteria to select records for 'Name' and 'Date' columns of the query. Below is what I had in the 'Criteria:' of the query. What wrong with the code that is placed in the query's 'Criteria:' ?

Forms![Previous Evaluation Form]![Individuals].Column(0)
Forms![Previous Evaluation Form]![Individuals].Column(1)

View 3 Replies View Related

Queries :: UNION / UNION ALL Query Crashed Access

Oct 24, 2013

I'm having a problem with a UNION / UNION ALL query.It seems there is a application crash fault when running the query that MS are aware of and have issued a hot fix. Unfortunately it will take my IT dept some time to check and install the hot fix If they agree to do it at all.

Problem signature:
Problem Event Name: APPCRASH
Application Name: MSACCESS.EXE
Application Version: 12.0.6606.1000

[code]...

View 1 Replies View Related

Input Criteria Into Union Query From A Form

May 31, 2006

Based on information from a earlier thread.... I created a Union query that pulls information from multiple tables and fields.


SELECT AG_B_R1 as Num FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_B_R2 FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_B_R3 FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_B_R4 FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_m_R1 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_m_R2 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_m_R3 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_m_R4 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_e_R1 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_e_R2 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_e_R3 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll]
UNION ALL SELECT AG_e_R4 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll];


And then I created another query to get the STDEV of the above query


SELECT StDev([Num]) AS StDev
FROM Q_cals_ag_bme_STDEV_Union;

The result will be on a subform on my main page. How do I get my form to input the [roll] automatically and requery the subform, showing my result.
Thanks

View 1 Replies View Related

General :: Union Query And Form Coding

May 14, 2013

I have 2 tables, (will just call them tbl1 and tbl2 for this example) each with a column AppNumber. The AppNumber may appear in both tables or individually in either one.

I want to be able to show, or flag, on a continuous form which Appnumber's appear on tbl1, tbl2 and on both. This is so the user can click a button and bring up another form with more data relevant to the AppNumber.

I have written a union query to show all results from both tbl1 and tbl2 which contains 4 fields AppNumber, DateApp, Surname, Forename but I can't tell from the query results what AppNumber's appear on each table. Here's what I would like to show on the continuous form:

AppNumber DateApp Surname Forename tbl1 tbl2 Both
12345 11/12/2012 Simpson Homer Yes No No
56565 01/04/2013 Stallone Sly No Yes No
89788 03/03/2012 Rambo John Yes Yes Yes
89745

View 2 Replies View Related

Passing Values

Apr 25, 2005

I need to find a way of passing a value from an Access form to an .asp page...

Page1.asp is viewable on the web and asks a user for a code and password - these are checked against a database and when a match is found page2.asp is opened showing values based on SQL views which match the criteria from page1.asp.

What I want to do is be able to open page2.asp from a button in Access - the button should therefore force the opening of page2.asp in a web browser and pass to it the values from that record on the database (code and password).

What code would sit on the button to get it to do this?

View 1 Replies View Related

Passing Parameter From Form Into Query?

Sep 3, 2007

I know this is probably a basic question- but Im not finding a clear answer here.

Basically- I have a value that I want to select from a drop down box on a form (not created yet). That value will get inserted into my query for a calculation I am doing. The form will pop up the results of the query in a table/dataset.

How do I designate the variable in the query that is being inserted from the form? I am using Access 2002- is there a way to visually perform this task (ie- drag/drop type thing)?
Thanks guys!

View 6 Replies View Related

Passing Form Value To Append Query

Nov 13, 2007

Dear All, I have a problem which I need to solve and am in need of a clue!

I have a table which contains lots of line items relating to quotes. Each quote usually contains three line items and is prefixed in all cases with a number which relates to number of users. For example

5 5 User Software Details Price Cost Note

in the table there are up to 100 users and each has line 3 items. I automate quote generation by using a form. In the form a quote reference is generated and a text box for the number of users. I have a command button which then, based on the value of users text box, selects from the line items table and sends the records to my TblQuotes.

My Tbl quotes is exactly the same structure as my line items table except it also has a field for the quote reference which is in the form.

Where I am stuck is I cant seem to get Access to send the quote reference into the TblQuotes as part of the append query.

Please help! :confused:

View 9 Replies View Related

Passing Form Data To An Sql Query

Nov 15, 2005

I have a hidden field on my form called "key". I can reffer to it as [forms]![MyForm]!key Is that right?

I also have a subform on this form that uses an sql query. I want this query to match on the key from above.

Shouldn't this work? select field1, field2 from table1 where keyvalue = [forms]![MyForm]!key

It doesn't. It returns a blank record.

Any help would be greatly appreciated.

View 1 Replies View Related

Passing Values Between Forms Using Vba

Aug 7, 2005

I have one dialog form which can receive values from multiple forms.
For each form i can build a popup dialog version but that's to much time in administration later on.

The best thing is if i Can declare a variable from the source form and pass it to the target control box in the dialog form.
I tried many ways but currently it's not working.

Maybe you've got example code for me.
Thanks

View 13 Replies View Related

Passing Values Between Forms

Nov 25, 2006

Ok this is probably easy for you guys but my knowledge of access is limited.

I have two forms - Find Job and Notes
On the Find Job form I have a text box called 'Job No' and a combo box called 'Status' that I have coded to open Notes if certain conditions are met. The code is

Private Sub Status_AfterUpdate()
If Status.Value = "WIP - Snagged" Or Status.Value = "WIP - Suspended" Then
DoCmd.OpenForm "Notes", , , acFormAdd
End If

What I need is to be able to pass the value of the 'Job no' text box on the Find Job form to the 'Job No' text box on the Notes form.

Many thanks in advance

Michael

View 1 Replies View Related

Passing Values From Combo Box

Sep 23, 2004

I am having trouble getting information from a combo box to pass the data through some code.

The code is:

Private Sub ListBoxFind_AfterUpdate()

If ListBoxFind = 141 Then
Me.FilterOn = False
Else: Me.Filter = "[Assessment ID]= ListBoxFind"
Me.FilterOn = True

End If

End Sub

I've tried Me.ListBoxFind, Me.ListBoxFind.Value, and ListBoxFind.Value. In all cases, a message box pops up asking for ListBoxFind (in this iteration) and then the filter works with the value I type in. It just seems that the value from the ListBoxFind never gets into the code. ListBoxFind is a combo box with four columns and the value bound to column 1.

Thanks

View 1 Replies View Related

Passing Values Between Tables

Aug 18, 2004

Hello,
I would like to know how to pass a value from one table to another within the same db based on a matching username. I need to update a column called 'store' in one table with the values of another column called 'value' from a different table.
Thanks for any help in advance.

View 5 Replies View Related







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