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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
