I have a table called providers with the following 3 fields:
Provider ID
Cancellation Reason
Visit ID
Date
It is possible to have multiple instances of the same provider ID, cancellation reason and visit ID on the same date. I'm trying to count just 1 instance based on visit ID if there are multiple instances of the visit ID and reason on the same date. Select distinct doesn't seem to do the trick.
I am fixing a database that was previously built. It is use to show listings of items for sale. There is a form that displays all of the information about a particular listing, each with its own id code.
If I want to email a particular item and info to an email address i can, but i can only send one at a time. I click a button which then asks what format to save the report, i choose html, then it opens a new email message from Outlook and sets the file as an attatchment.
The problem is, i want to be able to send more than one listing at a time in the same email. But once the first listing is created and attatched i have to send that one before i can send another listing.
Any help would be greatly appreciated.
Jared
Hi, I would like to recreate a record locking problem (error # 3218) our users are experiencing on an application we have developed. I currently do not have access to a network, so I would like to run multiple instances of Access on one machine. How do I do that?
Thanks,
Bill
I have a very elaborate Access 2000 application that uses three different SQL databases as the back end. Mostly the data is modified or selected via ADO connections.
The problem I am having is that recently users on Terminal Server are not able to open the application because for some reason the application is not allowing more than one copy of it to be opened at once. I checked everywhere and couldn't find anything that would not allow multiple instances but couldn't find anything.
Any ideas on what could be causing this? Any help is greatly appreciated
Is there a way to allow Access to open a second (or thrid...) copy of a form? We need this so we can visually compare the contents for record one with the contents of record two.
Thanks,
David
I have an Access 2007 mydb.accdb file. If I click on it, it starts up fine. If I click on it again, then focus goes to the already open db.
If I do the same but with the .accde of the same db, for each click a new instance opens. This is not good, in that a single user then can have multiple instances running without noticing it, and that might mess things up. What Windows setting to use, to convince Win XP that only one instance of mydb.accde should run, similar to the .accdb?
After creating multiple instances of a form how should they be closed so as to ensure that all of their assets are disposed of and there are no memory leaks? DoCmd.Close seems not to work because it expects a name string which would be the same for all instances of the form.
Since a form's name is a read only property it cannot be modified when the form is created so it doesn't uniquely identify a particular form instance. I've tried setting the form reference to Nothing which appears to work, but I'm not sure whether this is the correct technique and whether it disposes of everything cleanly.
Not sure if this is the correct forum but its worth a shot.
When I try to open any database, MS Access will hang if there is already another instance msaccess.exe running. I even tried opening two newly created blank dbs with the same result. Has anyone ever come accross this behavior?
I have tried repairinging/reinstalling/checking and unchecking "Windows in Taskbar option"
Thanks for any help!
We have a form that will have one combo box for Associates (like Employee in the OPs DB) which I've got set to show only active Associates for new entry per the tips in the above thread, but we'll also have a combo box for Developer which I need to function the same way. I don't know how to add that part to the On Current event?
Code:
Private Sub Form_Current()
Dim strComboRowSource As String
If Me.NewRecord Then
strComboRowSource = "SELECT T_Associates.AssociateID, T_Associates.Associate, T_Associates.Active " & _
"FROM T_Associates " & _
"WHERE (((T_Associates.Active)=True));"
[Code] .....
I have an existing query, created using the query wizard which works just fine - however, I would like to modify it to return only instances where there are 3 or more records appearing.
Essentially, its an employee history report for a particular action done by those employees, which returns all records between two dates as specified by the user. What I would like to do is only show those employees who have had more than three instances of this action in the given date period.
I am thinking along the lines of DCount? but how it would be phrased in the query?
I have a large table (>1M rows), and I have searched various forums for a way to add sequential numbers by Group. The query I have works, but since it's a large table, I broke it up, and did everything what starts with A-E, the F-Q, etc, and appended to a new table.
This query works, on anything that starts with the letter D or later... A-C will not work.
Basically, the source table is a list of all combinations of Part_ID and UPC_Code. I am trying to number the UPC_Code field, per Part_ID. There is an AutoNumber field (ID) that is in the table as well.
This is the SQL. Query1 is the query that is being executed, so the DCount is within this same query, if that makes sense.
Code:
SELECT tbl_upc.ID, tbl_upc.Part_ID, tbl_upc.upc_code, DCount("[Part_ID]","Query1","[Part_ID] = '" & [Part_ID] & "'")-DCount("[Part_ID]","Query1","[Part_ID] = '" & [Part_ID] & "' AND [ID] > " & [ID]) AS Seq_Num
FROM tbl_upc
GROUP BY tbl_upc.ID, tbl_upc.Part_ID, tbl_upc.upc_code
ORDER BY tbl_upc.ID;
The results of this query are that all Part_IDs that start with A through C produces a Seq_Num of 0, but any that start with a "D" or later number correctly - in other words, the first instance of a particular Part_ID is 1, then 2, and so on up to the total count of that Part_ID.
Running Access 2013.
I've come across an interesting problem a colleague has been trying to solve and even though I can 'see' what needs to be done and could probably program it on our mainframe, I'm really stuck on how to achieve the same results in MS Access
I'll do my best to explain the problem by showing an example of how the data exists in the DB and also how it 'should' be output on a report.
DATE OPERATOR COUNT
29/06/06 - Oper A - 32
29/06/06 - Oper B - 12
29/06/06 - Oper A - 10
29/06/06 - Oper C - 13
29/06/06 - Oper B - 10
The output needed however should read as follows
29/06/06 - Oper A - 42
29/06/06 - Oper B - 22
29/06/06 - Oper C - 13
Setting up a date selection shouldn't be a problem, its just adding the totals on each instance of each operator into one figure without ending up with a total of the number of times an operator appears on a date.
Any help and guidance about how best to approach this problem (macro/query/script) would be appreciated.
Hi,
I have developed a MS Access tool to be used in multiuser environ. In some computers, the tool is working while in some, there is an error message-"Provider not found. May not installed properly". Is it problem with the script or is it that the provider was not installed with MS Office. I have used ADODB connection.
Hi Guys
I'm kinda stuck with this error.
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E37)
[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot find the input table or query 'Results'. Make sure it exists and that its name is spelled correctly.
/Access_Mem/verifypassword.asp, line 18
I just don't know where I'm going wrong. Pls help
Thkx (Attached is my code)
----------------------------------------------------------
<!--#include file="config.inc"-->
<%
email = trim(Request.form("username"))
password = trim(Request.form("password"))
if email = "" or password = "" then
Response.Write "please enter username and password"
else
ConnString = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("member.mdb")
set my_conn= Server.CreateObject("ADODB.Connection")
set rs = server.CreateObject("ADODB.RecordSet")
my_Conn.Open ConnString
set rscheck = my_conn.Execute ("Select * from Results where UserName='" & UserName & "' and Password='" & Password & "'") --- Line 18
if rscheck.eof then
Response.redirect invalid_page
'invalid password and redirect to login page
else
session("login") = "yes"
session("name") = rscheck("name")
'session("...") = rscheck("...")
'add session ..if you want to keep profile of your user.
Response.redirect valid_login
' password accept and go to memberpage.asp
end if
RScheck.close
rs.Close
my_conn.close
set my_conn = nothing
end if
%>
A child in our company can have up to 4 different service providers offering different services at a time. I have to do a report for each individual service provider and there delivered services. The problem is, all the providers and delivered services are listed on one record (the child's record). How do I make a query that will only pull up their delivered services in a record and not the services of the others attached to it? Should I use IIF? If so, what would be a good statement to use? If anyone can help me out, I'd be thankful. Let me know if additional information is needed.
View 1 Replies View RelatedMicrosoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x818 Thread 0xa74 DBC 0x5f5c1024 Jet'.
I get the above error when I run my login form can anyone help? it points to the code below
<% Response.Buffer = true %>
<%
Session("DatabasePath") = "MoneygallGAA.mdb"
If Request.Form("btnLogin") = "Login" AND Request.Form("txtName") <> "" _
AND Request.Form("txtPassword") <> "" Then
'-- Declare your variables
Dim DataConnection, cmdDC, RecordSet
Dim RecordToEdit, Updated, strUserName, strPassword
strUserName = Request.Form("txtName")
strPassword = Request.Form("txtPassword")
'-- Create object and open database
Set DataConnection = Server.CreateObject("ADODB.Connection")
DataConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)};" & _
"DBQ=" & Session("DatabasePath") & ";"
Set cmdDC = Server.CreateObject("ADODB.Command")
cmdDC.ActiveConnection = DataConnection
'-- default SQL
SQL = "SELECT * FROM Login"
If Request.Form("txtName") <> "" Then
SQL = "SELECT Login.* FROM Login " & _
"WHERE Login.userID='" & strUserName& _
"' AND Login.password ='" & strPassword & "'"
End If
cmdDC.CommandText = SQL
Set RecordSet = Server.CreateObject("ADODB.Recordset")
'-- Cursor Type, Lock Type
'-- ForwardOnly 0 - ReadOnly 1
'-- KeySet 1 - Pessimistic 2
'-- Dynamic 2 - Optimistic 3
'-- Static 3 - BatchOptimistic 4
RecordSet.Open cmdDC, , 0, 2
If Not RecordSet.EOF Then
Dim struserLevel
struserLevel = RecordSet.Fields("userLevel")
Session("userLevel") = struserLevel
Else
'The user was not validated...
'Take them to a page which tells them they were not validated...
Response.Redirect "default.htm"
End If
End If
%>
Hey everybody out there,
Recently I came to know about a wonderful website providing cheap magazine subscription. One can find any popular magazines here, that too on a price less than 35% newsstands price. You can subscribe for all popular Sports magazines, lifestyle magazine, health magazines, and magazines related to popular music genres and almost any kind of publications.
The website is http://www.magazineland.com. I hope you’ll get benefit through this latest news.
Hi!
I am a newbie in Access. I am running Access 2003 and would like to be able to create links to Visual Foxpro9 tables using the VFP OleDB provider and not the ODBC drivers.
A few of my clients want to use the VFP9 tables in their reports but so far I have not been able to find a way to do it using OLEDB (except in a module with VBA code which I don't want).
I can do it using the ODBC but the problem is that ODBC driver does not recognise the VarChar fields in the tables.
I tried creating an Access project and then 'File --> Connections' but it does not display the 'Provider' tab to select which provider to use and by default assumes that SQL Server is the source database.
How can I get Access 2003 to allow me to select the OLEDB Provider?
Any help will be appreciated. Thanks.
Hi Guys
Sorry to come to you with this one but my sql is terrible......
I have a recordset named Leave reason. Its numerical and can cover some 20 differing numbers. These are not always sequential.
Ive tried a crosstab - not good enough, Ive tried a form with =Count(IIf([Leave reason]=9,0)) in it, this works, but, it requires a text box for every possible number that could occur!
To labour intensive that.
So the bottom line is is there a small piece of sql that I can add in to the queries sql view to handle this issue, if there is can someone just lay down the code (yes I know Its naughty to ask) so Ill learn and be able to do it in some 30 other
queries of the same nature.
thanks guys - sorry its so drawn out
gareth.
I have a database for storing part numbers of power supplies, very basic database. I also have a multifield search form and search query. All very basic stuff, however I am new to access and cannot seem to find out what the is causing this issue.
There are 174 entries in the database table, they all start with "0" for the part number, I have wild cards in my Query so that for instance if I searched 0 for the part number field, it should return all 174 entries, but for some reason it only returns 49 entries.
I have a text file that cannot be imported and turned into a table because it has no line feed characters and is therefore well over 255 columns. The fields are delimited by commas, however.
How can I search this file for all instances of "{id" and then grab the next 10 characters to the right and store them somewhere?
I have a subform that formats the data in a record in a particular 2D way. I want to use it a variable number of times on a single form. Is there any way to do that? I've tried using CreateControl, but access just says it can't add the controls I've requested -- with no reason given, of course.
View 2 Replies View RelatedI have written a large database for the school I used to work for. In the main table there is a field named "Active" which is text with yes or no choices.Is there a way to find in what objects this field is being used in the database?
For the life of me I can't remember why I put this field in or if its even used now.This database has had many versions over the years.
I have searched many of the queries but can not find where this field is used.If there isn't a way I plan to backup, remove field from table and test all the features of the program to see which crashes. However I'm hoping there might be a quicker way.This was one of the first DB I wrote and didn't pay much attention to mapping or keeping notes back then.
The thing I've been trying to do is make it so that my form filters my records, and I'm trying to make a between function for it. My form is shown below in the attachment.
What I need to do is make it so that my form filters my records Between the two year boxes AND between the two Length boxes. But I need it so that if nothing is in the boxes, it shows all records, and if something is in only the Year boxes, it only filters the years and not the lengths.
Code:
Field: Length
Criteria: Between [Forms]![SearchForm]![Length1] And [Forms]![SearchForm]![Length2]
Then in a separate column I had
Code:
Field: [Forms]![SearchForm]![Length1]
Or: Is Null
This works fine if it's only for Length, but if I try to do the same for the MovieYear boxes, it screws up and just shows me no records...?
I have a database where several entries may have the same address, but someone has been inputting some of the zip codes incorrectly. I want to come up with a way to get a query or the like to spit out a report with just the entries that have identical addresses and conflicting zip codes, and have hit a brick wall.
View 5 Replies View RelatedI have some code that opens and populates an Excel spreadsheets then does a save as to a specific directory and also outputs a PDF. This code is working with one small problem, I am leaving the first instance of Excel open and can't figure out where I should put the code for it to quit. Here is my code:
Code:
' This code creates a recordset based on the current reservation in order to gather data on
' a specific reservation, to output to a payment schedule excel spreadsheet which calculates
' Payment dates and amounts for specific reservations
' Declare variables for recordset
Dim rst As DAO.Recordset
Dim db As DAO.Database
[Code] ....
The instance that I need to close is shown in red.