Change Field name in Table with VBA
Referencing Table Field Value to Change Back Color (with if/iif VBA/expression)
I have a report where some field values will be blank at times. I would like to write an if statement (in VBA or as an expression) to state that if the field is blank, to change the backcolor of the field value on the report.
I can't seem to get it to work in either VBA or as an expression. In VBA I get a debugging error as to how I reference the table field value. In an expression (I am not sure how to refer to the back color of a field) I first tested with true returning "Good" and false returning "Bad".
Even though the table field value is blank, it returns "Bad". I even tried to include IsNull(""), but this still returns "Bad".
Even if I put a value in the table field and refer to that in the expression, it still returns "Bad" instead of "Good".
At the moment I don't have a date selection form before the report, but I have a record in the table that shows when switching the report to View mode.
Change a field to Primary key in linked table
I need to change the properties of a field to be a primary auto number field. I know that we can't just change it so I am needing some code to copy the existing Contacts table structure. Change the ID field to Primary autonumber. Then we need to copy the contents of the original contacts table to the existing table, rename the old contacts to contactsOld and change the name of the temp table to contacts.
The reason for these gymnastics is that I am selling some software that will integrate with ShipRite and I need this to happen programitically so I don't have to physically go to each client to do the install.
Change to a new table
I am trying to get my Access Report to use a new table. In design view, I change the Record Source to the new table name. I also change the table name in the Order By field. But it won't let me save the change.
Change Record value to another field
I have a table with 2000 records.
I need to put the last hour of the day and employee for the field EndTime.
It is possible for vba cod?
Look at the picture in attached, please.
validation rule or vba
I have a table purchases that contains a lookup field. The lookup field displays the field upc from the table red. id is the primary field in the table red.
The lookfield upc should only be able to except the values greater then 10 but less than 100. Do I come up with a validation rule for this field in this table? If so what is it? Or is a form with vba the way to goand if so what is the vba
Change Wizard Default to VBA
I am having lapse in memory. I know that you can change the default when you use the Access Wizard in 2007 to have it create the action in VBA versus the Macro default. I just cannot recall where this is located to make the change. I also thought the I had made this change on a global basis but recently opened a new data base and found that it was creating simple wizard commands embedding macros. I know how to convert them, but much prefer that they be created in VBA
Set the field size
You can adjust the amount of space that each record in a table uses by changing the field size property of number fields in the table. You can also change the field size of a field that stores text data, although this action has a smaller effect on the amount of space that is used.
In this article
* What happens when I change the field size?
* Change the field size of a number field
* Change the field size of a text field
2010: Frontend with VBA and backend change breaks VBA form calls (onLoad etc)?
When I have a frontend with VBA (for table additions etc) and a backend (all tables except Switchboard Items), when I change from my test-backend (USB) to the realtime-backend (Network drive/directory) the tables work, but all VBA calls (events like onLoad etc.) break (I get an error message).
The only way I can fix it atm is by using the Compress and repair database,which restores the links between VBA and the forms etc.
What causes this to happen? Is it a known bug in Access 2010?
Change group and sort options via VBA?
I have a report currently grouped by one field name and sorted by a few others. What's the best way to change the group and sort options via VBA so I can make group/sort buttons for each field on the form? - OR - What are other methods of accomplishing this
Can't change Table Field Name to All Caps
I have a table with a field name "distcode." I need to change it simply to all caps; DISTCODE. In Design Mode I type DISTCODE, save, and when I view the table the field name is all caps. But when I close the database and reopen the table the field name is back to all small letters. What is forcing this default change?
The Data Type is Text, and this table is not linked to another table
Log record change in back-end?
Is there a method, in the back-end file, to log that a value (any value) in a record was changed? - - adding a new field to the table just for this purpose is ok.
I can easily see a method if one can put vba in the front-end form that the user uses. I can use vba to insert a change code into that record's log field.
But I can not see any method that would exist only in the back-end file. Is there any technique out there?
Access 2010: Frontend with VBA and backend change breaks VBA form calls (onLoad etc)?
When I have a frontend with VBA (for table additions etc) and a backend (all tables except Switchboard Items), when I change from my test-backend (USB) to the realtime-backend (Network drive/directory) the tables work, but all VBA calls (events like onLoad etc.) break (i get an error message). The only way I can fix it atm is by using the Compress and repair database, which restores the links between VBA and the forms etc.
What causes this to happen? Is it a known bug in Access 2010?
VBA Code for buttons, Access 2010
In using this new version of Access, I have found that when creating buttons, Access no longer uses vba codebehind to handle all the dirty work - rather, it seems Microsoft is forcing me to use the upgraded (and very nice I must say) Macro builder.
This is fine for all the simple tasks, but what I have myself into is a little more complex than the Macro builder is ready tohandle I think.
Before, when I created a button, not a Macro. I am trying to build the following button types:
Button 1 - Update a Field in a table, Save form, Close form
Button 2 - Update a field in a table, save form, new record
Button 3 - Update a field in a table, save form, print report, close form . and so on and so forth.
I used to put 3 or 4 buttons on the form, then copy and paste the generated vba in the correct order in a single button, and delete the dummy buttons. But now it seems as the basicfunctions are built using an embedded macro, and when that is combined with VBA, Access must choose between one or the other, rendering my button useless.
Is there a setting in 2010 that will change the way buttons are created in that VBA is used by default instead of macros?
Change field name using SQL
I have a Access 2007 database that contains five tables. I need to make a permanent change on some of the field names. In the past we were working with dBase IV, and it was always best touse similar feels with the same name.
Now, I would like to change fields that share the same name. Builder.bld to Builder.bbld.
Is there a way to change field names using SQL. Within the five tables. I probably have 20 feels or more were I would like to change the field name, just by adding the first letter of each table to the affected field.
So is there an SQL command where I can:
Change Builder.bld to Builder.bbld
Change Builder.addr to Builder.baddr
Change Builder.phone to Builder.bphone ?
need to use vba to change a property to table data
I am currently working a a Junior IT and am new to Access and databases in general. I have been tasked at finding a way to integrate an activex object into our database.
What I am trying to do is have it so that when we load a report the object is autmatically populated with the relevant data from a table. Unforunetly this particular control does not have a "Control Source" property, otherwise I would just modify that. I have successfully made it so that I can change the property of the object in question using vba. What I would like to know is if there is any way I can have vba insert table data, much like I would do using "control source" (if it were available
New Table Errors
I am creating a table for Change orders. In my new table I have Survey No (Job No) and the respective Change orders. Usually all change orders range from 001 to 005. Instead of retyping my Survey No I wanted to have it look it up. When I do so, the lookup field is blank. I tried to create a relationship and when I do I get "No unique index found for referenced field of the primary table." What the heck is going on? I don't understand why I cant lookup the data in another field. Very strange to me. Please help. The database is attached.
Check Box Changes - Confirm Change Yes/No
I have several check boxes.
i.e., Check Box Field Name = Principals
If the check box = true and you want to change it to false, confirm the change to true (yes) else keep it true (No).
If the check box = false and you want to change it to true confirm the change to true (Yes) else keep it false (No).
How can this be coded in VBA or perhaps using a macro function?
I have tried several iterations of coding in VBA and macros and have had no success at all.
Convert text to numbers
I have a .CSV file that I imported into a database and includes 900,000 records. When I import the file I have to import all fields as text otherwise I receive errors importing.
One of the fields is Days and contains negative and positive numbers. I am needing to convert this field from text to number datatype. I have tried to change the datatype in the table and get a error message stating "Access can not change the datatype there is not enough disk space or memory". I believe this is due to the amount of records I have in the table.
I need to find another way to change this datatype from text to number. I have tried to do a update query from the field set as a text datatype to a field set as a number datatype and this does not work.
Can this be done through VBA or SQL? Or maybe some other way
how field name output same as input
I have a query from a couple tables, and one of the field names in the table is 'IGP' and I have a field in the query that was IGP and now I changed to: IGP: iif(blah IGP blah) which gives me a circular error.
The table is used by a bunch of other things, so I don't want to change the field name there, and the field name for the output is used by a bunch of things, so nor do I want to change the name there. I am using Access 2007. I thought about changing the output field to IGP_, and I could change the embedded queries in Excel that use it, but is there a way to still use the same name, and have a formula in it, and that uses a field in another table
access 2010 vba question
I am importing a excel 2007 spreadsheet into my DB.
The spreadsheet has 4 columns that are Date type.
After the import is completed, I checked the data type in Access 2010 for this particular table and these 4 fiels have Text datatype.
As a result, when I tried to use the table along with another table for a query (linking dates), I get type mismatch from the query.
I tried to do Docmd.runSQL "alter table ." to change the field type using SQL DDL, but that ended up deleting all the imported date.
I am really stumped here as I can't figure out how to set the field type to Date/Time with Short Date Format at run run time using VBA