I have a linked excel table in my database but sometimes because the network is down I am getting "not responding" message from Access if I accidentlaly try to open the linked table while off-line. That does not seem to timeout at all. I am wondering where I can actually set the timeout so Access will not hang trying to get to the linked source?
View RepliesI have a linked table and want to change it's data source to another table entirely -- ie: different file name.
Can this be done with linked tables, or can you just link to another file with the same name but in another directory? If linked tables have this limitation, how would you accomplish what I'm trying to do. I want to keep the same name for the linked table.
Ultimately, I want to populate a listbox with the different files in a directory and change tblSource to point at the chosen data source.
Thanks,
Randy
I have OLE Object connected to a source Document. Each time I change the source document to a new one by using the sourcedoc property and action property. What I need is how to programatically print this document.
View 1 Replies View RelatedI have been working on quite a few multi user Access projects in the past few months with a backend and a front end which has linked tables.
I would like to know if there is any way to find out the source of a table linked to the front end and where would its location be on the network.
I have a form that is linked to a query by its Record Source.In one of the Text boxes I want to appear the number of rows in the query where Days = 4 so I thought I would try to use DCOUNT as the Control Source for the Text Box. However every expression I try for DCOUNT throws back error? or name?. How to sort my code?
=DCount("[EPISODEUNIQUEKEY]","1-12 Show Records by Date Range","[Days] = '4'")
Suppose I have a front end and backend (tables) database which I want to deploye. Currently Access tries to find the backend in the same absolute location (rather than relative position). This causes a problem as drive letters change and sometimes I would want it in a specific location on my computer and another place on deployed computer. Is there anyway to change source of linked tables after compiling to ACCDE
View 3 Replies View RelatedAm getting an ODBC Timeout message when trying to run a SQL stored procedure from Access. I know that I can set the Timeout property in an Access query, but can I do the same thing using the code below?
Code: Set oconn = New ADODB.Connection oconn.Open "Driver={SQL Server};" & _ "Server=SQLSW;" & _ "Database=PHRpt;" & _ "Uid=user;" & _ "Pwd=none;" SQL = "dbo.spACH_RunAllProcs '" & StartDate & "', '" & EndDate & "'" Set rs = oconn.Execute(SQL)
I have a datasheet subform that is acting like a timesheet. I have a TimeIn field and TimeOut field (both fields formatted as Medium Time); and a field for Hours to total the time. I used the expression =HoursAndMinutes([TimeOut]-[TimeIn]) that I found in another post, and it didn't work. All I got was #Name. Any thoughts as to why that's happening? I would preferably like the hours to total in tenths of hours, ie. 10.5.
Thanks
ScrmingWhisprs
I am using MS Access 2007.
I have created a multi-valued field "Product Category" that lookups data items from a linked table. So, the Data Type for the multi-valued field "Product Category" is Number.
Now I want to change the Data Type of "Product Category" from Number to Text, and make a value list that I can type values in and can provide the same data items as the linked table.
How to change item source for the multi-valued field from a linked table to a list that I can type in values? Is there a feature provided by MS Access 2007 can enable such a conversion?
I am using MS Access 2007.
I have created a multi-valued field "Product Category" that lookups data items from a linked table. So, the Data Type for the multi-valued field "Product Category" is Number.
Now I want to change the Data Type of "Product Category" from Number to Text, and make a value list that I can type values in and can provide the same data items as the linked table.
How to change item source for the multi-valued field from a linked table to a list that I can type in values?
Is there a feature provided by MS Access 2007 can enable such a conversion?
Hello, (not sure if this post belongs in the Reports forum, sorry)
From 2 Tables: In the Query I have these fields:
LogID | UserID | TimeIn | TimeOut |
with an additional column where I have this:
TotalTime: Int([TimeOut]-[TimeIn]) & " day " & Format([Timeout]-[TimeIn],"h"" hr ""n"" min ""s"" sec""")
The above gives me the total time "TimeIn" to "TimeOut". For example:
Record1: 1 day 2 hours 33 minutes 44 seconds.
Record2: 2 days 3 hours 4 minutes 6 second.
No Problems there.
In a Report based on this query, I can show each record and GROUP IT BY >USERID<
I need help with the following :
In the Group Footer for UserID, I need to SUM the column (TotalTime) from the above Query. So that the SUM of the above 2 records shows:
Total All Records: 3 days 5 hours 37 minutes 50 seconds.
Any advice please.
Thank You.
I have an unbound form with three tabs. On each tab there is a sub form. Each tab is a search form and each sub is a results returned. I have made everything unbound and set the sub form recordsource and its controls control souce on afterupdate of the main form search criteria. Works fine except for after some use the db decides the sub forms are not unbound and sets the record source and control sources.
I'm trying to do a
Me.PollingPlacesResults.Form.RecordSource = ""
Me!PollingPlacesResults!PollID.ControlSource = ""
but this does not seem to work in actualy removeing the record source and control source.
I have a database with a number of linked tables that are linked to tables in different databases (not a back-end).for example, I have table1 that is linked to table1 in K:databasedb1.mdb.table2 linked to table2 in S:datadata.mdb.and so on...
However, recently we have moved all our databases to a new location.
K:databasedb1.mdb is now residing in O:masterdatabase
and S:datadata.mdb is now residing in O:masterdata
and so on...
I'm now in charge of relinking all those tables to point to the new location.I would do this in linked table manager one by one but we have 100s of tables linked to multiple different databases in different location.is there a way to create a VBA code that will automatically do this re-linking process?
so,
1. find unlinkable tables
2. search its new location under O:master
3. re-link it to the new location
Database names and tables names have not been changed. Just the location of databases.
I am trying to build a Form that will show an estimate (then eventually will be moved to a project if customer and employee aggree to price and project) in a Form F_Estimates is a M_Customers(Customer_ID) (Based on a Table) and thier info in a Subform. Also is the "projected costs" from parts out of the Parts(Part_ID) (Based on another Table) in a second Subform as a list that I need to calculate $$$ in
(Dang that still sounds evil and definately NOT understandable even after edit... so)
Here's some basic info
Tables
EstimatesandParts - Table
EstimatesandParts_ID : Autonumber
Estimate_ID : Number
Part_ID : Number
Parts - Table
Part_ID : Autonumber
PartNumber : Text (not a number due to some part#s have letters in them)
PartName : Text
Unit Price : Currency
Description : Text
Estimates - Table
Estimate_ID : Autonumber
InvoiceNumber : Text (again can have letters in it)
EstimateDate : Date/Time
EstimateTime : Date/Time
Employee_ID : Number
Customer_ID : Number
ProblemDescription : Memo
Customers - Table
Customer_ID : Autonumber
FirstName : Text
LastName : Text
CompanyName : Text
Address : Text
City : Text
Province_State : Text
Postal_ZIPCode : Text (CDN Postal codes are letter num letter...)
you can see the link table in the EstimatesandParts Table
Now I want to use that link to populate a subform in the F_Estimates form
Forms
SF_Customers - SubForm
(all boxes atm are text boxes on this form till I figure out the Parts section then will use same base for this so I can pick any customer in the database to be the customer for this estimate. Also will have ctrl button for making new customer with customer form and a refresh on Focus Gain bit of code)
FirstName
LastName
CompanyName
Address
City
Province_State
Postal_ZIPCode
SF_Parts - SubForm
Default View -Continuous Forms
(want it to be a list of parts that I can grab prices and descriptions from then in a bit of code to calculate a cost of parts)
Part_ID : Combo Box
Control Source - Part_ID
Row Source Type - Table/Query
Row Source - SELECT Parts.Part_ID, Parts.PartNumber, Parts.PartName, Parts.UnitPrice, Parts.Description FROM Parts ORDER BY Parts.Description;
(Pulls info from the table Parts for input into a list of parts to be used on that project)
PartName : Text Box
UnitPrice : Text Box
(here's where I run into problems due to the fact that the form is not based on the parts table but rather the link table EstimatesandParts so I can't propogate the info to the 2 other text boxes, ps I dont care if they cant be text boxes and have to be linked or some other type I'm not "set" just need to find out how to make it work )
(have tried a couple things to complete this task)
Me.txtPartName = Me.Part_ID.Column(2)
Me.txtUnitPrice = Me.Part_ID.Column(3)
(works AWSOME ... for ONE ROW then propogates the second selection to the first and second and third selection to first second and third and so on ...)
(tried to make control source for the txtPartName to)
=Forms!Parts!Partname
(Doesnt exist .. akkk, cant use ActiveForm either as it doesn't focus on the SubForm but the MainForm ... cry)
F_Estimates - Form
Estimate_ID
InvioceNumber
EstimateDate
EstimateTime
ProblemDescription
(all basic Text Boxes)
Employee_ID
Customer_ID
(Combo Boxes Select Customer and Employee from list of present ones of each)
SF_Customers
SF_Parts
(Both SubForms on the main form)
Now this is an Exerp from my entire Database I like to work on one small problem at a time and I have made this its own little database till I figure out the problem then I will bring the info I learn back into the rest of the database and go from there ...
Hope you can help I have a feeling I will need to make a recordset and go from there but I'm just not able to wrap my head around that for some reason
Thanks in advance for ANY and ALL help that I get from here
Hey
I have two list box.
the first box give the names of states.
The secound box uses the state name from box one. Then displays all colleges within that state.
This is an event. I am trying to get a grasp on the difference bettween DAO and ADO before coding.
At this point I like to know how to set the row source of the secound list box choice.
If you have the time to provide the compleet VBA; I will enjoy it.
Thanks a bunch
tom
Hi all,
I've got a form in my database that enables you to print or view reports, which I inherited. There is a list box that is automatically populated with all of the reports listed in the Reports tab of the database window. The list box row source says the following:
SELECT msysobjects.Name FROM msysobjects WHERE (((msysobjects.Type)= 32764));
However, I have been asked to create an identical report list that only shows some reports from the database window.
Does anybody know how I could modify the above code to only get reports beginning frm_id for example?
Many thanks
Hi, i'm having troubles using the wizard to enable me to insert fields all in one form.
I was wondering if anyone know a source code so when i start a new form from scratch, no wizard to help me. That I can use the event procedure to enable me to look up the source or the Control Source using the text field box
Thanks
I have a form bound to a query.
I have command button to open the form but I want to change the query bound to it when it opens.
I've tried changing the row source etc in an event... No luck.
Any ideas??
Hi,
I have an Access 2000 database with some linked tables. When upsizing the database I selected 'Save password and user ID with attached tables'.
Therefore in the Linked Table manager after each table in brackets I have the name of the database to which the tables are linked.
I am now wondering if I want to link the tables to a different database how do I change the database name, which is in brackets after the table name.
If I select a table and select 'prompt for a new location each time' I am getting prompted to select a different DSN. At the moment I don't have a DSN and I don't want to have to set one up.
I imagine that the database name and the user id and password I entered in the upsizing wizard are stored in some configuration box but I don't know where I can access this.
Can anyone point me in the right direction??
Thanks
Hi all, at the moment all of my field are control sourced. I heard there is a way whereby the field could be unbound and one button it the control source for them all. Im not entirely sure what this means or how to go about it. But i think it would solve the problem of if my users start entering data into a record and then decide to leave it, this record is automatically added to the table, with little or no data. As the data in the table is used for statistics, the table showing records which are not actually there is causing problems. Im not sure if i explained this well, but i would be grateful of any help. Thanx
View 1 Replies View RelatedI have a subform listing items for purchase. Some are services and are not taxable, some are products and are taxable.
So far, I am calculating the sales tax for each record (item paid for), based on whether the thing is taxable or not and putting that result in a text box (txtSales_Tax) on the form. Since these calculated amounts aren't stored in a field, I can't SUM them, since SUM only works on data in table fields. Can someone tell me how to get a total?
Otherwise, I'll get rid of txtSales_Tax entirely, if someone can tell me how to write a SUM function, as a Control Source, that includes an IIf, or a WHERE, so it only adds items where the yes/no field Taxable=True.
I've got an IIf with a SUM function in it but, even in the footer, that only works for the current record. I need a SUM that has a nested IIf or WHERE in order to include all the records.
I have a drop down list of Last name and first name.
I want this to be the only unlocked feature on the page. When a name is selected the other information is shown (the persons cc#, Exp date, phone number, email etc.)
I am almost sure its a control source, but i'm not 100% as i'm relatively new to Access..
Another issue I would like to know is how to show the Last Name, First Name in the selected drop down after its selected, currently its only showing the Last name.
Thanks in advance
I have a form with several drop dpwn lists in it. I want the choice of each on to narrow the choices of the next drop down list.
I have see a few examples on this site on how this is done but at this point I dont understand how to write the information. I have taken what another person had posted and dont really get what to do next. Do I paste the info in the rowsource box and then build the query? The example I saw had expr1 and expr2 in the querys so I was lost.
Thanks, Joe
1st drop down list row source
SELECT DISTINCTROW tblProjects.ProjectID, tblProject.Poject FROM tblProjects ORDER BY tblProjects.Project;
2nd drop down list row source
SELECT DISTINCTROW tblLocations.LocationID, tblLocations.Location FROM tblLocations WHERE (((tblLocations.ProjectID)=[Forms]![frmMain]![cboProjects])) ORDER BY tblLocations.Location;
In my contacts datababse I have a main table where all the is stored for my contacts.
I have a form that I use to enter and display the information from, Because I have more the one contact at a given company I have created a seperate table that contains all of the contact details for each office.
What I would like to be able to do is when I select a office on my form Access will populate the the rest of the fields in this section with the relevant information.
How do you do this?
Hi!
I have those (simplified) tables that are linked with an n:m relation
persons:
#ID#FullName#
trainings:
#ID#startDate#
training_members:
#ID#trainingID#personID
Now I created a form to fill the training_members table with the participants. For the form I'm using a dropdown list filled with the names of the people from the persons table. What I now want is that the dropdown list only shows the values that are not already used before. So I changed the SQL statement of the row source property:
SELECT persons.ID, persons.FullName FROM persons WHERE persons.ID Not In (SELECT personID from training_members inner join trainings on training_members.trainingID = trainingsn.ID) ORDER BY persons.FullName;
I also added "me.recalc" to the after_update event of the dropdown list.
It does work - but there's one problem left: The SQL query doesn't include the just selected value. Because only values that are included in the list are accepted, the name of the person isn't shown in the dropdown.
Do you know how I can update the SQL statement so that the just selected entry is as well mentioned in the list? The abovementioned form is displayed as "Continuous Forms", so I don't know if it is possible to access the recordset the dropdowns are located into...
Thank you in advance!
is it possible to change the control source of an unbound text box ?
For example i have a textbox which simply displays the number of current records displayed i.e. =DCount("*", "queryname")
Now i wanted that with the click of a button (or something), i wanted to change it's controlsource to =DCount("*", "queryname2") ... however, as a result i got #name instead of the correct count.
Thanks