Pass-Through Query Question
Jun 6, 2006
I have an Access front-end connected to Oracle tables. When the user opens up the main form to add a new entry, I need to be able to pull the next sequential record number. However, I can't get this to happen, without filling out the entire record, saving, and then it will committ the number.
Can this be done with a pass-through query to the Oracle table, find the next sequential number, and then add it to my record id (ie, 06-1050, with 06 being the year and 1050 the next sequential number). I do have two fields in the Oracle table TXTYear and LNGNumber. This concatenated field is related to three additional tables that have more information. Each of these tables need to cascade a new record.
Confused? me too. any help would be appreciated.
View Replies
ADVERTISEMENT
Jul 15, 2005
Good afternoon, I have a form with a subform and in the first txtbox of the subform in the GotFocus event I have a little procedure which checks the txtboxs on the parent to make sure that there is data in all four of the txtboxes. This works great the first time and it pops up a msgbox and it even setsfocus on the txtbox with no data in it, but if I tab into the subform a second time and there still is no data in one of the txtboxs on the parent form, nothing happens, no message and no setting focus on the txtbox with no data in it. Does anyone know of a way to get this procedure to re-set everytime a user tries to enter the subform? Thank you in advance to anyone offering and ideas and suggestions.
View 8 Replies
View Related
Jul 13, 2007
I'm trying to create a PTQ and just cant seem to find the source table.
The name of the source table is PRM1_ORG_MTRX3_N
If I write my query like this, I get an error code that states username.PRM1_ORG_MTRX3_N is an undefined name. It adds my userid to the beginning of the table name.
SELECT
T128.AS_OF_DATE,
T128.LEVEL8,
T128.LEVEL8_ORG_NAME,
T128.LEVEL9,
T128.LEVEL9_ORG_NAME
FROM
PRM1_ORG_MTRX3_N as T128
WHERE
T128.AS_OF_DATE like '1/1/2007'
Then I rewrote the query like this, and I got and error code that said PRM1.ORG_MTRX3_N is an undefined name. Does anyone know what else I can try here to get this query going? Thanks
SELECT
T128.AS_OF_DATE,
T128.LEVEL8,
T128.LEVEL8_ORG_NAME,
T128.LEVEL9,
T128.LEVEL9_ORG_NAME
FROM
PRM1.ORG_MTRX3_N as T128
WHERE
T128.AS_OF_DATE like '1/1/2007'
View 2 Replies
View Related
Jun 30, 2006
Can you do a pass through Query to a pivot table when your query has parameters? I'm reading like you can't, but nothing has been said concretely yet. When I do it, it gives me an error "Trouble Obtaining Data" when I try and set the layout.
View 1 Replies
View Related
Dec 6, 2007
Hi
I have created a simple query that is used as the record source of one of my forms. I want to pass the query a different criterion each time the form is opened.
Any suggestion/example on who to do that will be very much appreciated.
Thanks,
B
View 4 Replies
View Related
Jan 4, 2006
I have a VERY simple select statement that I am using in a pass-through query: "SELECT * FROM dbo_vReturn;", but I cannot get this to work. Each time I run the query it generates an error: "ODBC call failed - Invalid object name "dbo_vReturn" (#208)".
The dbo_vRteturn is a view in the backend. I can open this table/view from Access using an ODBC connection but when I try to run the pass-through query it falls over!
Does anyone have any idea what is causing this?:confused:
View 2 Replies
View Related
Jul 19, 2006
Hello
Any hellp would be appericated.
I have a pass thorugh query that connets to a Oracle database with a where clause with a between start date and end date.
I need the start date and End Date to come from a form within MS Access.
Is this possible? if so how do I code the where clause to get the start and end date parameters from the form?
View 3 Replies
View Related
Oct 23, 2007
I have a query that I want use as a pass-through query (SQL Server databases).
The query has a sub-query that references another database. The query runs fine in Enterprise Manager because EM allows me to access both databases.
If I wanted to I could link both tables through ODBC and then run an Access query that would do the same, however, I would rather use a pass-through-query for this.
The problem is that a pass-through-query in MS Access only allows me ONE ODBC connection (as far as i can tell).
My question is:
Is there a way I can run a sub-query (referencing a different database. i.e. second ODBC connection) which references a second database?
Is this possible?
Thanks
-Gary
View 1 Replies
View Related
Mar 3, 2008
Is there any way to pass a variable from one criteria on a query to five other criterias in the same query.
I am using this code to select which person the query is running on
Like "*" & [Enter Part of The Landman Name or Enter For All Records] & "*"
I have five areas in this query where I would need to add the name, I just want to do it once?
Thanks.
Fen how
View 14 Replies
View Related
Sep 11, 2005
Hi
I am struggling with what seems like should be a straightforward task. Unexpectedly however it has become an infuriatingly difficult one (no doubt due to my complete novice status).
I have a very basic d/base (3 tables) that I have been searching using basic SQL queries. I want to create a simple 'search' form that produces the results of my various queries without the need to work in SQL. Enter one or more search criteria, hit ENTER, results presented in datasheet perhaps?
Thought this would have been easy but I've had no luck. Can you please explain how I can pass a parameter from a form to a query?
Many thanks
Stuck21
View 1 Replies
View Related
Nov 25, 2005
I'm a couple of years removed from Access and shaking off the rust. I hope someone can help with something that may be obvious but I'm missing.
I'm simply looking to pass the begin and end dates to a query driving reports. The calendar form includes unbound text boxes, txtBeginDate and txtEndDate. My code populates the text boxes correctly; the user clicks on the calendar date, clicks the calendar day, then the Begin (or End) Date control, and each populates the respective textbox.
But when I run the query or report, I'm prompted for the parameters. This is what I have in the query's Date field criteria:
Between [Forms]![frm_Calendar]![txtBeginDate] And [Forms]![frm_Calendar]![txtEndDate]
I'm just drawing blank on what I did several years ago to make this work. If anyone can help, I'm most appreciative.
View 6 Replies
View Related
Jun 3, 2005
I'm getting an error (SQL command not properly ended) but main thing is, I can't get it to recognize the ms access db.
In a nutshell...I want to query the oracle db for a number and count, then scan a number into msaccess and have it 'find' the matching number in oracle and return the count only to ms access. More comments below between the code and here are table names for both oracle and access:
ORACLE: Table name is: batch_status bs
Field names are: batch_num and Batch size
MS ACCESS: Table name is tblbatchstatus
Field names are: batchnum and idcount
CODE in PASSTHROUGH QUERY:
select batch_num,BATCH_SIZE
from batch_status bs
(this works and selects all batch numbers and sizes from oracle)
(If I say 'where batch_num = 60024 -----this works also....)
However...when I add the below part...I get errors...it can't seem to match to access database....
SELECT tblbatchstatus.Batchnum, tblbatchstatus.Idcount
FROM tblbatchstatus;
where batch_num = tblbatchstatus.batchnum
And I want to just say: where batch_num = <<<scanned batch number>>> and return the id count.
View 1 Replies
View Related
Aug 15, 2007
i have this query that i have several buttons that trigger it. how do i pass over a unique variable with each button. each button needs to append a different event. right now i have it set static to "3rd Party Denial" but i need that to be dynamic based on which button is pressed. PLEASE HELP!!!!!!
Code:INSERT INTO [Status Log] ( Status, Edit_Date, Event, Claim_ID )SELECT [Status Lookup].Status, Now() AS Expr1, "3rd Party Denial" AS Expr3, [Claim Report Info].[Claims Header].Claim_IDFROM [Status Lookup] RIGHT JOIN ([Claim Report Info] LEFT JOIN [Status Log] ON [Claim Report Info].[Claims Header].Claim_ID = [Status Log].Claim_ID) ON [Status Lookup].status = [Status Log].StatusGROUP BY [Status Lookup].Status, Now(), "3rd Party Denial", [Claim Report Info].[Claims Header].Claim_IDHAVING ((([Status Lookup].Status)=[Forms]![claiminformation]![ReportForm]![reportstatus1]) AND (([Claim Report Info].[Claims Header].Claim_ID)=[Forms]![claiminformation]![ReportForm]![Report_ClaimID]));
currently i have a macro running when clicked.
that macro runs 2 quieres and a report.
i'm doing this because thats the only way i know how.
View 7 Replies
View Related
Jan 14, 2015
I just got stuck with the following. I have a listbox where I have some values. I want to use the value of the first listbox to pass this to a query. I have the below code:
Code:
Private Sub List28_GotFocus()
' Limit the subject emails in the list box
Me.List28.RowSource = "SELECT [Subject] " & "FROM tbl_eMail_Archive " & " WHERE [FolderName] " & "Me.List23.Value"
' Refresh the list box
Me.List28.Requery
End Sub
why it is not working?
View 2 Replies
View Related
Jan 22, 2015
This is my first time writing a pass through query pinging sql tables using an input parameter from a form. I have gotten as far as executing the query but I can't seem to display the result to ensure it's pulling the right records. I also want to be able to append the records to a table. Below is the code I have written so far:
Sub GETRT()
Code:
Dim db As DAO.Database
Dim QDF As QueryDef
Dim STRSQL As String
Dim RS As Recordset
STRSQL = "SELECT * FROM LAB_MESR.ODM_RT_DAYS" & _
"WHERE LOCATION_ID=" & [Forms]![PARMS]![STR_NBR]
Set db = CurrentDb
Set QDF = db.QueryDefs("001:GET_LT")
QDF.SQL = STRSQL
End Sub
View 5 Replies
View Related
Feb 17, 2014
I have a subform (this subform's source object can change to one of three diffrent forms) that allows the users to filter. I have a requirement to output the filtered data to excel. I am creating a query based on the subforms record source called qryExport. I now want to pass the subforms filter to qryExport. I have the string setup now I just need to know how to pass the data into the filter property of the query.
View 3 Replies
View Related
Dec 23, 2005
Can anyone help? I have a query that queries against 11 linked views against an SQL Server backend.
The query is running dog slow, so I want to convert it to a pass-through query so that the processing is done server side rather than Access having to drag thousands of records across the network, but don't know where to start.
The problem is that I need to convert the Access SQL to a version of SQL that SQL Server understands.
Is there a tool I can use that does this automatically (keeping my fingers crossed here)? If not then does anyone know where I can find out how to do this?
View 2 Replies
View Related
Jan 29, 2008
I am trying to enter dates in a form that calls a report that invokes a query that uses the dates. It has been a less then satisfying experience. I am getting a Run-Time error 3122. Is it possible and I need to work on syntax or do I need to think of another way? BTW how do I lookup the Run-Time errors?
Thanks for helping an old guy learn new tricks.
Jim
View 5 Replies
View Related
Feb 15, 2007
I have a query set up. I need to pass a set of current records ( as I select them in a drop-down menu) in a form to the query as parameters. How would I do it with or without VB? Thanks !
EDIT: Forgot to mention that all combo boxes are bound so they are not customized dialog-boxes which are unbound
View 3 Replies
View Related
Jul 20, 2007
Hello,
Is it possible to pass a AutoNumber Format variable and data to a new table using an Append Query? If not does anyone know what I can try?
Thanks!
View 1 Replies
View Related
Jun 28, 2005
Hi there. What I'm attempting to do is pass multiple values from a multiple selection list box as criteria for an Append Query. Is there any way to do this? The DB keeps track of Real Estate boards and the forms that they use, I would like to be able to select all the boards in a given state/province, but have the ability to deselect some within that province if I don't need them in the query (this is the criteria i'm trying to pass). I don't need to use a list box, any control that would allow me to pass multiple values would be great. Anyone able to help? Thanks very much.
View 1 Replies
View Related
Aug 15, 2006
Is it possible to pass a variable to a form when a checkbox is clicked?
I have 8 tabs, each of which has a checkbox.
When a checkbox is clicked, I would like it to display 1 form but that form should display different data each time by either running a SQL query with specific variables passed to it or by running a different SQL query.
?
View 1 Replies
View Related
Feb 13, 2014
I am after getting an access 2003 database to look after and it contains SQL pass through query's. The database is a front end to a MS SQL server database with a connection string that is contained in a module.
I believe the SQL pass through queries are connecting to the same database as the rest of the application and somehow is using the connection string in the module. However I cannot find how that is configured on the SQL pass through queries.
Most documentation on the net seems to point at using ODBC to connect slq pass through queries to outside databases but I don't think this is the case. There are not Odbc sources set up for the database I am look at.
View 8 Replies
View Related
Feb 12, 2014
Table: DailyExport
Field: FailureGrouping (actually is offices)
I want to export all the fields from DailyExport each of our 9 offices to Excel, whetjer or not they have date in the DailyExport table. So if not, the exported workbook would only have columns headings.
I need to loop through the nine offices and export each office report to Excel.
Dim StrQry As String
Dim strfullpath As String
Dim SOffice as String
strSQL = "SELECT * FROM DailyExport WHERE FailureGrouping = " & SOffice
strfullpath = "Y:" & SOffice &" "& Format(Date,"mm-dd-yy") & "_Failures.xlsx"
DoCmd.TransferSpreadsheet acExport, , FailureGrouping, strfullpath, False
View 1 Replies
View Related
Feb 10, 2014
In a table of a SQL-Server 2008 I have a fieldname "Alter", the German word for age.
When sending a PT-qry to the server I get an error "Incorrect syntax near the keyword 'Alter'"
Even when I try to set "Select tblmytable.Alter as Age" I get the same error.
To change the fieldname is almost impossible, as there is to much code to change.
View 3 Replies
View Related
Feb 24, 2014
I made multiple pass through queries that receive their data via an ODBC link which is established. Currently the username and password is entered seperately in every query but I was wondering if it is possible (and how) to make both look for the values in a table (OR somewhere else if possible) for these values. The following code (with edited information) is used in all queries.
Code:
ODBC;DSN=DSNNAME;Driver=Firebird/InterBase(r) driver;Dbname=Database.FDB;CHARSET=NONE;PWD=Password;UID=User;
The reason that I would like to do this is to ensure that the user can change it easily.
View 3 Replies
View Related
