Topics Search

Change Field name in Table with VBA

Change Field name in Table with VBA
Views: 126
Is it possible to read the names of the fields in an Access table and rename the fields? *I have three tables, one with several fields, and I need to change the name of the fields.
Sponsored Links:

Similar posts...

 

Referencing Table Field Value to Change Back Color (with if/iif VBA/expression)

Referencing Table Field Value to Change Back Color (with if/iif VBA/expression) Icon
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.
 

Set the field size

Set the field size Icon
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
 

Change to a new table

Change to a new table Icon
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.
 

VBA Code for buttons, Access 2010

VBA Code for buttons, Access 2010 Icon
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?
 

Log record change in back-end?

Log record change in back-end? Icon
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?
 

2010: Frontend with VBA and backend change breaks VBA form calls (onLoad etc)?

2010: Frontend with VBA and backend change breaks VBA form calls (onLoad etc)? Icon
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 field property to hyperlink?

Change field property to hyperlink? Icon
I have a "make-table" query, which includes a field of hyperlinks to documents, in the original source table. But, the query will only set the field to a text type field. In order to my code to open the hyperlink on click event, the table field must be of the "hyperlink" type, which I can manually change in the table properties, but would like to addto the code to first convert the field to a hyperlink field.
 

how field name output same as input

how field name output same as input Icon
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
 

program find button to search whole table

program find button to search whole table Icon
Is there away that I can use VBA code to tell the Find Button on my form to search the whole table rather than having to select the individual field I would like to search?

Example

If I wanted to search for an employee but their name does not exist (typo, etc.), yet I have their ID number. I can just enter their ID number and not have to change the search to now look in the ID field of the table. I can just type the Id number and the whole table will be searched for that number
 

Change field name using SQL

Change field name using SQL Icon
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.

Example:
Permit.bld
Builder.bld

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 ?