Query Results Differ Between Directly Inputting Parameters And Using [Insert]
Sep 12, 2007
I am having a problem getting any results from a query written with a msgbox parameter in the criteria. My query is supposed to look up a project number, and then report various information regarding that number. Here is my problem.
If I type the project number directly in to the "Criteria" cell in Design View it pulls the appropriate results. However, when I put [Enter Project Number] in the "Criteria" cell and insert the same number into the msgbox when I run the query I get no results at all. Any ideas?
View Replies
ADVERTISEMENT
Jul 22, 2005
Hi all,
I've searched for a solution, and the proposed solution didn't work for me.
I am executing an SQL statement to insert values into a History table when deleting a value in a subform. Two of the 5 values are asking me for parameters when the SQL executes and I cant figure out why! The datatypes they are inserting into are correct and I'm at a loss. The 2 values giving me grief are Manufacturer and Model.
Here's the code:
Dim sSQLMachHistory As String
MsgBox "me.acctid: " & Me.AccountID
MsgBox "me.manufac: " & Me.Manufacturer & " me.serial: " & Me.SerialNumber
MsgBox "model: " & Me.Model
'Insert into History Table:
sSQLMachHistory = "INSERT INTO tblMachineHistory (SerialNumber, Manufacturer, Model, AccountID, MachineID) VALUES (" & Me.SerialNumber & ", " & Me.Manufacturer & ", " & Me.Model & ", " & Me.AccountID & ", " & Me.MachineID & ")"
MsgBox sSQLMachHistory
DoCmd.SetWarnings False
DoCmd.RunSQL sSQLMachHistory
DoCmd.SetWarnings True
My message boxes show me all the values are displaying and should be inserting into the table -- any ideas??? :confused:
cheers
Mike
View 2 Replies
View Related
Jul 29, 2014
how to get xform value because it on 5 differ forms.
so what i was looking for is a way to get the button name when button find on 5 different forms clicked:
if plausible specific only to below button
1) frmmchfpdata, cmdfind615
2) frmmchobdata, cmdfind617
3) frmmchu7data, cmdfind619
4) frmopddata, cmdfind215
5) frmopdflupdata, cmdfind217
below code on ok button at popup form named as frmsrhstudent.
Code:
Private Sub Command0_Click()
Dim Srch As String
Dim Srchx As String
Dim xbtnname As String
[Code].....
View 5 Replies
View Related
Jul 2, 2010
Table TBL_NEWDATA is used to append new data to table TBL_PERSON_ALLOCATIONS.
TBL_NEWDATA { Person_ID, Department_ID }
TBL_PERSON_ALLOCATIONS { Person_ID, Department_ID, ... }
I need to devise a query to append data for a particular Department_ID from TBL_NEWDATA to TBL_PERSON_ALLOCATIONS where that data does not already exist there. i.e. for Department_ID 'Research', I would want to append 'Person_ID', 'Department_ID' (in this case: 'Research') to TBL_PERSON_ALLOCATIONS for any tuples not already held.
INSERT INTO TBL_PERSON_ALLOCATIONS (Person_ID, Department_ID)
SELECT Person_ID, Department_ID
FROM TBL_NEWDATA
WHERE TBL_NEWDATA.Department_ID='Form...'
[code]...
This Query takes a single argument from a control (Forms!Main!IN_Department), and this is the Department_ID to be updated.Is there any way to do this using a single query or will I have to use sub queries? I'd hoped not to as to keep the database as concise as possible.
View 2 Replies
View Related
Jul 17, 2005
i gt this prob here
i gt one main table...taking in a text...
I gt like 4 sub table...which i will have to take in the text from the main one...then sort them into the different table...currently trying to do this via forms...can someone tell me if it is possible...?
Example...
There is this index which user input A123456
I am supposed to put this text into the table which is supposed to contain AXXXXXX text...hw am i supposed to do that?
View 1 Replies
View Related
Nov 26, 2007
Hello,
I am creating a database for a user that has a health & safety report form on paper that he would like replicated on screen on a form that he could fill in. Is there a way to do this? If not any ideas of which way to go from here would be much appeciated.
Thanks in advance for any help
Craig
View 3 Replies
View Related
Feb 2, 2005
Hi there
I have a database that has two tables.
Table 1 is called applications and table 2 is called contacts.
Table 1 has two fields that relate (not through the relationships) to table 2.
These fields are named thus: Officer and ContactTelephone
Through a form I wish to be able to do the following.
When I choose the Officer from the drop down box (combo box tied into Table2) it will then automatically put in to ContactTelephone the necesary number.
It MUST drag the info from Table 2 and place in to Table 1.
Any ideas :confused:
Thanks :D
Jools
View 7 Replies
View Related
Oct 27, 2013
I have a form where it is necessary for the user to input number of tanker loads, however I would ideally like the table to store the information in m3 which means multiplying whatever the user inputs by 11.3.
My plan was to use an unbound textbox for the user to input the "number of loads" into, then use a query to calculate the value and then a Dlookup to update the value of a control which will be hidden somewhere on the form. I got as far as making the query however then realised that it won't let me add an unbound textbox to a query in order to form an expression. I'd like to stay away from the inbuilt "calculated field" option just in case i need to downgrade to an earlier version than 2013 at some point.
View 1 Replies
View Related
Jan 10, 2006
I am trying to link the textboxes on a form so that their values are stored in 2 seperate tables.
Setting the Record Source of the Form to TableA and the various Text boxes etc are set to the Fields in that table works fine, however I want some other text boxes to be linked to another set of table fields, so that when I enter values into those text boxes the data ends up in a different table.
Has anyone any ideas on how text boxes on one form can be linked to different tables.
dave
View 3 Replies
View Related
Aug 24, 2004
I'm making a large database. There are over 100 questions per "chapter" of the data I need to enter. The forms aren't large enough to accept everything at once so I broke up the chapters into a few smaller forms. The problem I'm having now is it saying "you can't go to the specified record" whenever I enter the same company name in two of the forms for the same chapter. The company name is the primary key. How can I make it so the techs can go from page to page without breaking up my tables?
Thanks in advance.
Sean Sturts (USMC)
View 3 Replies
View Related
Oct 29, 2014
I am working in Access 2007. I have a form with 3 text boxes of which I need the Textbox3 to calculate a SUM of the input values from Textbox1 and Textbox2. These text fields will compute ONLY when the record is saved OR if there is a prepopulated value in the text field. EX. When textbox1 is populated with a value when I Openform, then as I input a value into textbox2 I immediately see the SUM in textbox3. Works great in this scenario. However, if both textboxes 1&2 are null, and I input a value into each, then I won't see the SUM in textbox3 UNTIL I save record.
How can I get the SUM to display after inputting the value of my textbox1&2?
View 3 Replies
View Related
Aug 30, 2004
I have a parameter query with 5 possible user entries.
The criteria for all my fields is set up as follows:
Criteria: IIf(IsNull([Enter County]), "", [Enter County])
Or: Like "*" & [Enter County] & "*"
If the user enters something for 2 or more parameters it seems to work, but if they just enter a county, for example,, it gives an error message about the expression being too complex.
Any suggestions.
Thanks.
View 9 Replies
View Related
Apr 1, 2014
I have a database where on the form we are just put in total deductions. We want to make sure that the total deductions is correct so while we are entering this number we want to do an excel thing by hitting = 1 + 1. Anytime we do that we are getting an error message. I really didn't want to have to set up a table to store these different amounts because we really only need the total. Is there a way to do that? I changed the format from currency to general but I'm still getting the error.
View 2 Replies
View Related
Nov 4, 2014
I have a question about for combobox. I have a field from my tables that a text only then I make in the look up property field to become combobox then row "source type is a value list" then,"Allow value list edits, I make it Yes". then in the form its possible for me now to add value list in the combobox. but I want that what ever I input to become a value list, that's all they can select for the value of that field. if they can type or add not listed in the value list I input, it will not possible & there's a msgbox will appear mentioning the "the value you input is not in the list..
View 2 Replies
View Related
Mar 10, 2014
If memory serves me right, I've seen code or maybe a shortcut somewhere for inputting a date value in a form along the following lines.
To input today's date, input "0"
Yesterday = "-1"
A week ago = "-7"
and so on.
View 6 Replies
View Related
Nov 5, 2013
I my form i have a textbox into which the user can enter a value that will serve as criteria in a query.
In the beforeupdate of this textbox there is a check about the validity of the input. If this input is wrong, a message tells the user what's wrong .
In such a case i like to cancel the event but in the same time clearing the textbox.
Canceling isn't a problem, but automatically clearing the wrong input seems not to be so evident.
How can i do that ?
View 5 Replies
View Related
Jan 4, 2006
I have this code of a command button, which would allow me to generate the result of the SQL. I think the code is wrong... Can someone help? I guess something wrong with the bracket...
strSQL = " SELECT NewsClips.RecordNumber, NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment FROM NewsClips " & _
"WHERE (NewsClips.[NewsSource] " & strNewsSource & _
strNewsSourceCondition & "NewsClips.[1CategoryMain] " & str1MainCate & ")" _
str2MainCateCondition & "(" "NewsClips.[NewsSource]" & strNewsSource & _
strNewsSourceCOndition & "NewsClips.[2CategoryMain] " & str2MainCate & ")" ";"
Indeed, I try to modify the SQL that works in a test query (as I want to know what went wrong with my code): the changes would be replace OR to a toggle option.
SELECT NewsClips.IssueDate, NewsClips.Title_Eng, NewsClips.Titile_Chi, NewsClips.NewsSource, NewsClips.[1CategoryMain], NewsClips.[1Sub-Category], NewsClips.[2CategoryMain], NewsClips.[2Sub-Category], NewsClips.hyperlink, NewsClips.FirstTwoPara, NewsClips.Notes, NewsClips.attachment
FROM NewsClips
WHERE (((NewsClips.NewsSource)=[Which News Source]) OR ((NewsClips.[1CategoryMain])=[Which Category?])) OR (((NewsClips.NewsSource)=[Which News Source]) OR ((NewsClips.[2CategoryMain])=[Which Category?]))
ORDER BY NewsClips.IssueDate DESC;
Your help will be greatly appericated.
View 3 Replies
View Related
Feb 1, 2006
Guys I need your help/Advice...
In my Access Database I have a query (lets say qry1) and in this query i have 2 fields for start and end date, which is provided by 2 Get functions.
also i have qry2 based on qry1
then qry3 based on qry2
and finally qry4(using sql in code) based on qry3, and non of these 3 queries have the start and end date fields.
now here is the problem: I am trying to set the criteria in qry4 and then open a record set on this query(qry4) to use the data that it pulls out...
strsql = ""
strsql = "SELECT Sum AS AREA_TOTAL " _
& "FROM qry3 " _
& "WHERE ENERGY_AREA like '" & Area & "';"
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset(strsql)
but when the last line is executed I get this error message:
Runtime Error '3061':
Too few parameters, expected 2.
i also tried doing this:
strsql = ""
strsql = "SELECT Sum AS AREA_TOTAL " _
& "FROM qry3 " _
& "WHERE ENERGY_AREA like '" & Area & "';"
Set MyDB = CurrentDb
MyDB.QueryDefs("qry4").sql = strsql
Set rst = MyDB.OpenRecordset("qry4")
but when the last line is executed it gives me an error message saying that the query does not exist or the name is not spelled correctly. (Ps. I have created the query and the criteria does update once the Select statement is run in the code!)
again the reason for this is that the query has not been populated as the main query (qry1) needs 'strat date' and 'end date'!
Is there anyway I can pass these 2 parameters to qry4 directly using code? If there is a way then this will definitly work as i tried opening the query manually in the Query window and after I input the 2 dates in the input box the query ran successfully!
I would appreciate any help/suggestion guys, I need to sort this out quickly as i have a deadline... Cheers
View 1 Replies
View Related
Sep 13, 2006
I’ve the following query definition “selOrders”
PARAMETERS [DateFrom] DateTime, [DateTo] DateTime;
SELECT * FROM Orders WHERE OrderDate BETWEEN [DateFrom] AND [DateTo]
I want to open this query as a DAO.Recordset but have problems to assign values to the parameters. I tried different possibilities but invain. My latest try was as follows:
Dim QryDef As QueryDef
Dim Date1, Date2 As Date
Dim Orders As DAO.Recordset
Set QryDef = CurrentDb.QueryDefs("selOrders")
QryDef.Parameters("DateFrom") = Date1
QryDef.Parameters("DateTo") = Date2
Set Orders = CurrentDb.OpenRecordset("selOrders")
During execution error 3061 (Too few parameters, expected: 2) occurs.
View 1 Replies
View Related
Apr 10, 2007
Hey,
I am trying to enter a user-defined parameter in a CrossTab query with little luck. It works fine if I enter the code already defined as in: >= #12/06/2006# but when I enter the code for a user-defined / input such as: >= [Enter date:], or if I try to redirect to a textbox on the active Form such as: >=[Me]![dfrom.Value] I get the error "Invalid field or Expression), although this syntax / code works fine if I do this in a normal query.
Any suggestions?
Kind regards, Adam.
View 2 Replies
View Related
Sep 11, 2006
I have created a form with multi-select list boxes, behind which is the following code to transform the users' selections into query parameters.
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("Test")
If Me!lstAB.ItemsSelected.Count > 0 Then
For Each varItem In Me!lstAB.ItemsSelected
strCriteria = strCriteria & "Centres.[Area Board] = " & Chr(34) _
& Me!lstAB.ItemData(varItem) & Chr(34) & "OR "
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
Else
strCriteria = "Centres.[Area Board] Like '*'"
End If
strSQL = "SELECT * FROM Centres " & _
"Where " & strCriteria & ";"
qdf.SQL = strSQL
DoCmd.OpenQuery "Test"
Set db = Nothing
Set qdf = Nothing
End Sub
I would like to allow the user to set, on the same form, which field they want the resultant data to be sorted by, preferably by having a tick box alongside each list box, which sets that field as the sort field. The form will obviously need to allow only one of these tick boxes to be selected.
Can anyone advise me on how to modify the above code to make this possible?
Thanks,
Gary
View 1 Replies
View Related
Oct 1, 2006
Hey guys, I have a simple design question I hope someone can help me with. I have little to no experience with Access, but I know enough to get around. Anyways, I'm working as an extern for a rock band. One of my assignments is to create a publicity contact database. I have all my forms and tables set up but I'm having some trouble creating my query. There are about 10 fields in the contact table. I want the user to be able to search any one of those 10 fields OR any combination of them. Is there a way to do this with only one query, where it willl prompt the user for only the fields they have selected to search?
View 2 Replies
View Related
Dec 1, 2013
I have a query that pulls information from two tables. Some of the fields that are being queried share the same name in the tables, [Reimbursed_Amount] and [Cancel_Fee] specifically. In Design View I have specified that I only want the query to pull these fields from the Event Information table. An error occurs when I try to run it, saying that I need to define which table the field is from in the SQL code.
But then after I added clarification in the SQL, when I run the query it now prompts for a parameter for each of these fields. Why is this happening? I leave it blank, so a parameter has no impact on the query. How can I stop this?
Here's the SQL, after I added the table clarification:
Code:
SELECT (Sum(nz([Program_Cost])+nz([Millage_Fee])+nz([Auditorium_Cost])+nz([Cancel_Fee].[Event Information])-nz([Reimbursed_Amount].[Event Information]))) AS Total_Cost, [Shared Billing Information].Paid, [Shared Billing Information].Shared_Billing_ID, [Event Information].Shared_Billing_ID
FROM [Shared Billing Information] RIGHT JOIN [Event Information] ON [Shared Billing Information].Shared_Billing_ID = [Event Information].Shared_Billing_ID
[Code] ....
View 7 Replies
View Related
Jul 26, 2012
I have a report and export function which is based on date parameters. The field name called "date". However, I added a new field called "followup-date" because we would like to track customers that are returning back. Now, my problem is when I go to Export by date parameters or generate the report by certain date eg. July1st to July 28th, it only pulls up the record based on DATE field.
Is it possible for the date parameters to look up july1st to july 28th under date field as well as followup-date and pull up those records that are meet the date parameters? The reason I have to do this is because some customers do not come back for followup so I have to look under 'date' field too. While some customers can have more than one followup.
View 2 Replies
View Related
Feb 4, 2005
Hi..
I created a database using access database wizard. When I click on the mdb file the form directly opens, while when i click on the mdb file the I have made without using wizard, a list of tables appears and when i need to open a form i have to double click on the form.
Is there any way I can directly get the form to open when I open the mdb file.
I hope I am making my point clear here.
Regards
Rahul
View 2 Replies
View Related
Jul 3, 2014
I'm fairly new to access and have been tasked with fixing a database that to my knowledge is built incorrectly but due to budget constraints and the time it would take to build a new one, we have to stick with this one. Here's my problem, in the database is a form that we use to create new entries in a table but when I try to create a new entry through the form, it creates multiple entries in the table with the rest of the data split between all the new table entries. Is this something that is caused by us moving over to the most current version of Access? Is there a way to fix this problem given the software we are using? At one point I had recreated this table because we had been having issues with other aspects of the database (security deposits were not being updated when entered through this form).
View 14 Replies
View Related
