VB Code to copy linked table to local
I have an Access 2007 database with a linked ODBC table that changes on a
daily basis. I want to automate a function where the linked table is copied
and pasted as a local table. I tried the following command:
DoCmd.CopyObject , stNewName, acTable, stTblName
It worked, and named the new table correctly (based on an earlier command
that assigns the table name with the current date as "stNewName"), but it
stayed as a linked table - which means tomorrow, today's data will be lost.
Is there a parameter of the CopyObject command that will make the new table
Is there a way to view all flagged items, either
completed or incomplete?
What version of Outlook? Do you mean items across multiple folders or in a
single folder? What kinds of items?
Ben M. Schorr, OneNote-MVP
**I apologize but I am unable to respond to direct requests for assistance.
Please post questions and replies here in the newsgroup. Mahalo!
"Curt" <email@example.com> wrote in message
> Is there a way to view all flagged items, either
> co...Quick Flags and Blackberry
I am an EA and use quick flags (Outlook 2003) to indicate the significance
of emails in my boss' account. However because he travels frequently he uses
his Blackberry to access emails and can not see the flags. Is there a way
to sync the Microsoft quick flags and Blackberry or is there another
suggestion for sorting emails by importance that will transfer to a
Blackberry? Thank you.
you could try turning on Inline editing and type in the Subject field.
Trainer and Consultant
There are various articles about using Outlook he...Textbox fields on continuous forms
How can you have different coloured text in a text box of a continuous form.
For example I would like the text to be red if the value is 25% or greater
all other value should have black text. The data type is percent with 1
Assuming you're working with Access 2000 or later, select the field, goto
Format > Conditional Format and set your condition and text format preference.
>How can you have different coloured text in a text box of a continuous form.
>For example I would like the text to be red if the value is 25% or greater
>all other valu...Can Row and Column Inputs in 2 Var. Data Table be on a separate sh #2
I have created a two variable data table that I would like to reference data
from a separate worksheet (i.e., the Row and Column Inputs would reference
cells on a separate worksheet). How can I accomplish this?
When you are in the dialog box asking for reference cells, simply switch to
the other worksheet and point to the cells.
> I have created a two variable data table that I would like to reference data
> from a separate worksheet (i.e., the Row and Column Inputs would reference
> cells on a separate worksheet). How can I accomplish this?
I trie...Selectively Update Field in Table
I need to selectively update a field in my master table with new data. By
selective I mean only records that meet a particular criteria need to be
updated. My new data is in a different table, and they are related by repair
order. This is the SQL code that Access generates:
UPDATE RepairData LEFT JOIN DallasData ON RepairData.[Repair Order] =
DallasData.[Repair Order] SET RepairData.[Material Out] =
I know there are approx 75k records that need to be updated, but when I go
to run this query Access ...split data in a field into two fields
I importated a db from ms works into access. I wold like to seperate the data
from one field into two different fields what's the easiest way to do this
without resorting to manual entry?
Eg department field should be department and location fields
200 1786 becomes
200 in one department field and
1786 in location field
I would first back up the database and then use an update query. If your
values are all consistent with a single space between the Department and
Location, your syntax might look like:
SET Department = Left(DeptLoc, Instr(...Two-Input table #2
Allowence $au 2500
Exchange rate 75%
Converted rate 1875
How do i do a two input table to calculate rates on a table
A B C D E
$1875 1000 1500 2000 2500
so it calcualtes the rates for me? i have fiddles around with a few
different ways but am getting no where fast.
Please Help and thanks in advanced...I am a new user so be sensitive
In A1: 2500, in B1: 75%, then in C1: =A1*B1
C1 is the base formu...How do I concatenate a text field from a form to a "Where" string ?
In the following, "id_Part" is a text field in a report.
Me.txtPart is a field on my input form.
strWhere is the condition for opening the report.
How do I concatenate the Me.txtPart so it is text (surrounded by
in the strWhere ?
In other words, I want the strWhere string to evaluate to the
id_Part = '95405' (where 95405 is what is entered on the form)
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Does any one know what table in SQL the notes field in the CRM ties back to?
Or does anyone know how i can extract notes along with other fields for an
You can access CRM Notes data from the FilteredAnnotation view. The text of
the Notes are in the column "NoteText". You will need to join on the ObjectId
to the enitity's Id field. For eg:
LEFT JOIN FilteredAnnotation An
ON Ac.AccountId = An.AnnotationId
"Melvin F" wrote:
> Does any one know what table in SQL the notes f...Flagging
I did something stupid and now I am in the soup. My
boss's boss called me while he was traveling on business
to check his e-mail messages because his assistant was out
to lunch. When I opened his mailbox he had literaly 15 of
those pesky flagged message pop-up reminders. I dismissed
them because I find them annoying and wanted to get to his
messages. It turns out those pesky reminders are a
valuable mnemonic device for him. How do I restore the
active pop-up messages without changing the original due
date? He wants the original action date to remain. When
I try to put in b...Utilizing Recordset field in vb Requery Command Verbiage
I have a Record set loop set up in Vb. During the loop I want to utilize a
field returned within a command that uses another field with part of the same
Table : Facilities
Field: Facility (Values 100,200,300,400)
I have a form that has 10 textboxes that are labeled Tot100Cnt, Tot200Cnt,
I want to requery the textboxes utilizing the Recordset loop. Problem is I
am not sure how I need to format the command utilizing the returned recordset
field as part of the requery line. I don't want to code each field
independantly if I can use a looping ...Field
can some explain what is the field 'user to enter' about
Can you be a bit more specific? This field is used to store a User ID value,
but it is used in different ways in different parts of the application.
Exactly what is the context you are looking at?
David Musgrave [MSFT]
Escalation Engineer - Microsoft Dynamics GP
Microsoft Dynamics Support - Asia Pacific
Microsoft Dynamics (formerly Microsoft Business Solutions)
Any views contained within are my personal views and
not necessarily Microsoft Busines...Yes/No Field Size
Is there a way to increase the size of the check box for a Yes/No field?
On Wed, 27 Jan 2010 11:54:44 -0500, JamesJ wrote:
> Is there a way to increase the size of the check box for a Yes/No field?
A Yes/No (check box) field can only have a value of -1 or 0.
In other words, no, you can't.
Please respond only to this newsgroup.
I do not reply to personal e-mail
Check what I had in my March, 2004 "Access Answers" column in Pinnacle
Publication's "Smart Access". You can read the column on-line at
http://w...Pivot table from multiple worksheets . . . possible?
Is it possible for a pivot table to pull from multiple worksheets, provided
the information being pulled has the same number of columns with the same
Thanks for any help!
If you create a Pivot Table from multiple consolidation ranges, you
won't get the same pivot table layout that you'd get from a single
range. There's an example here:
If possible, store your data in a single worksheet, and you'll have more
flexibility in creating the pivot table.
> Is it possible for a pivot table to pul...Looking to automatically re-link tables in Access
Using Office 2007
Looking for a way to automatically re-link access database tables.
I currently have a database (IBD.MDB) that has links to 30+ Excel
Excel files are auto updated on a daily basis
The links to IBD.MDB are C:\Materials\Accss (Master)
My problem is I have 2 othe MDB files with the same name and uses the
same files, 1 is on a network server and the links are O:\Access, the
other is on another site and the links are J:\Access
To maintain this I have to manually copy any querie changes or new
changes from the master to the other 2 IBD.MDB files
...Query to eliminate blank fields
We have a DB that is holding info for different people in several different
fields, some of which are not populated. An example would be:
LastName FirstName Field1 Field2 Field3 Field4
Test1 Test1 1 3
Test2 Test2 2 3
Test3 Test3 1 2 4
How can I run a query that would return only the fields with values per
person? An example of the desired result would be:
Test1 Test1 1 3
Test2 Test2 2 3
Test3 ...button that prompts for a value and then enters it into a field
Anyone no where i might find some info on how to do this?
I want to click a button on my form, be prompted for a value (similar
to a parameter query prompt) and after i enter it and click "ok" it is
copied to a field.
I dont even know where to start.
A place to start: check out the InputBox method.
"kyle775" <firstname.lastname@example.org> wrote in message
> Anyone no where i might find some info on how to do this?
> I want to click a button on my form, be prompted for a value (similar
> ...Ensure a non-key field uniquely matches one field of compound key
New job, new database, new problems.
There is a table with a compound key on PatientNo and AdmissionNo.
There is an additional field, StudyCode, which is dependent on
PatientNo. Each unique PatientNo should match a unique StudyCode.
However, this is not the case.
How do you suggest I ensure that this rule is enforced? Currently I
am finding individual PatientNo's with two StudyCodes. This seems to
be the result of patients transferring between hospital sites, each
with their own copy of the database, and inadvertently being assigned
different StudyNo's at each site.
...How to print Design View of Table ? (Fields & Field Definitions)
Is there any way to print the design view of a table...
meaning the list of field names, data types, and field descriptions
Other than doing a screen capture (PrtSc) and then pasting into a
graphic program and then printing??
thanks for any help.
"MyEmailList@gmail.com" <MyEmailList@gmail.com> wrote in message
> Is there any way to print the design view of a table...
> meaning the list of field names, data types, and field descriptions
> Other than doing a screen capture (PrtSc) and then pasti...How do I hightlight field when criteria met?
Hi and thanks for taking the time to help.
I'm using Access 2003.
I have a field on a form which I want to highlight grey when another field
has a specific data entered.
Both fields are in the same table and on the same form.
The field "PurchaseOrLeaseType" is a combo box taking its list from another
When this field has the data "Purchase" or "Gift" entered I would like the
text box "MnthlyPaymentincStamp" to turn Grey.
Is this possible??
If Purchase or Gift are fixed values you might consider using "conditi...Splitting List of Codes in a Field Alphabetically
I have this issue where I have a spreadsheet that contains 60000 rows and 2
column. The first column is the Account number and the second column contain
1 to 500 three letter codes that are seperated by a ; I would like to have
26 colmuns and group all the codes alphbetically. Is this possible and if so
should I do it in Access or Excel?
Thank you in Advance,
So the first column contains a unique account number, and the 2nd column
contains concatenated codes. In a relational database, you would want these
as many *records* in a related table, instead of concatenated values in...Error message on required field
I have an Access 2003 data entry form that gives me a cryptic message
from the system when I get to the end of the file.
The field that is causing problems is required and cannot contain null
data. I want it this way.
The messsage I get is:
"The field "ID" cannot contain a Null value because the Required
property is set to True. Enter a value in this field."
I do not want to enter a value in this field.
When I get to the end of the file I would rather just get a message
"End of file. Please go back a record or exit program."
And then I return to ...Field formatting
How can I achieve the following field formatting on a form at data entry?
1. Make first letter of each word in a field caps?
2. Make all letters in a field caps?
3. If there is an apostrophe in a word to make the letter following the
apostrophe a caps?
4. If there is a hyphen in a word to make the letter following the hyphen a
Temporary change or permanent change. If you want to permanently change the
data do you need to change existing data (use an update query) or data as it
is entered into a form (use the control's after u...one field value to a page
I have a field CHSTOR that has 5 different stores I want to put one store to
On Tue, 1 Jun 2010 20:21:01 -0700, Norm <Norm@discussions.microsoft.com>
>I have a field CHSTOR that has 5 different stores I want to put one store to
Use the Report's Sorting and Grouping; group by CHSTOR; and put a Page Break
control in the footer for that group.
John W. Vinson [MVP]
Thanks John...I was afraid it was that easy
"John W. Vinson" wrote:
> On Tue, 1 Jun 2010 20:21:01 -0700, Norm ...