I've a database which have some confusing names for its reports and forms. So as I found myself very confused when applying group policy to all the objects I was wondering if there is a way to rename those objects in a way that ALL the references in the hole database for the renamed objects will change too.
E.g.
Form_old_name -> form_new_name
then all the references will change both in the VBA code and in subforms references
I tried doing that mannualy and then using the search & replace tool to update the rewferences in the vba code. Turns out that all the database become a mess and now I have constant "out of memmory" messages
I have an Access database that I need to import in to a SIR database that only takes fields names of a maximum of 8 characters long.
I want to create a new database with the same field names, only abreviated, and then import the data from my original database into the Access database with the new names.
Is this possible and could someone give me explicit info on how to do it?
The last line is the problem. A field name in an ADO recordset is limited to read-only persmissions for opened (already exisiting) recordsets. link:http://www.w3schools.com/ado/prop_name.asp
Is there another way around this without using an SQL "ALTER" statement. In other words, by accessing the field's name through a number like rs(i).name instead of rs("fieldname").name?
Hi, I have an Access database that I would like to do a mass Table Name change. Reason is, all tables start with Data.<table name> so one table is called Data.Names and I would like it to be called just Names. I can manually do this but there are over 600 tables. Would take some time.
Please let me know if there is a script or how I can approach this. Maybe I could loop through each table name where it finds "Data." and strip that part out.
I recieve Excel worksheets with information and then import this information into a table I have in access. I verify the data and as a last step I append the data to a table called AllReportData2012 which is used to pull information for my reports. Part of the Excel information that is imported to a new table includes the employee's name with work details in each row.
In my access report I do not use the employees name, I use their employee ID number. I have a table in my acess database that contains 2 columns. The employees Name and their associated Employee ID number.After I import the Excel information to Access how do I set up a macro or programming that will look at the name in each row and change it to the correct Employee ID. I could have anywhere from 20 rows of recods to 600 rows of records.
I am using Access 2010. I used a template called calltracker from Microsoft. I have adapted it to my use. Instead of tracking calls it is tracking follow ups. On one form there is a drop down list of reports that came with the template. I have modified the reports structure to fit my needs but in the drop down list they still show the names that came with the template. How can I change the names of the reports that show in the drop down list. How can I add additional reports I created to show in this drop down list
Hi, Is it possible to get the cursor to change on a OLE object like it does on an Hyperlink? How know that you can do that with a text box with the "Is hyperlink" Format property or by adding a space in "the hyperlink address" of a command button but I can't see any property that I can change for an Ole object or a label. Is someone know if it would be possible?
I need to know how to change the Access variable names programmatically.
I have a large Access database, with hundreds of variables that need to be shortened to 8 characters since they are to be imported into a SIR database, which does not allow variable names longer than 8 characters.
Because the variables have to be unique and yet still identifiable, I have to write code that will do abreviations. For instance:
I have a database that I used as a back-end for an Excel spreadsheet. Now that the database has been moved from one place on the network to another, I need to update the information in my spreadsheet to reflect the database's new name and location. Is there any way of doing this short of deleting and recreating the queries in Excel?
In my database, my "switchboard" consists of two listboxes: "Available Forms" and "Available Reports". The Available Forms listbox lists all the forms that can be accessed, and Available Forms listbox lists all the reports that can be accessed. I did this so that I wouldn't have to create buttons for each new form or report. They all are automatically listed in the listbox for the user to double-click on to open.
Since all my form and report names are not user-friendly (ie: fmComplicatedAndUglyName, rpComplicatedAndUglyReport) I want a way for the db admin to easily assign captions for each form that the average database user would find intuitive and easy to understand (ie: "Car Maintenance" instead of fmCarMaintenance). To do this, I built a table called tbDBObjectsCaptions consisting of these fields:
dbObject_ID (PK - Long Integer) Caption (Text)
Next, I have an unbound form (fmDBObjectsCaptions) consisting of two subforms:
1) sbfDBObjectsCaptions_Forms, which lists all Forms with captions 2) sbfDBObjectsCaptions_Reports, which lists all Reports with captions.
Each subform's record source is tbDBObjectsCaptions with an Inner Join between the table and the MSysObjects table so that I can show only forms (Type field in MSysObjects = -32768) or only reports (Type field in MSysObjects = -32764). So, the record source looks like:
Code: SELECT tbDBObjectsCaptions.dbObject_ID, tbDBObjectsCaptions.Caption FROM tbDBObjectsCaptions INNER JOIN MSysObjects ON tbDBObjectsCaptions.dbObject_ID = MSysObjects.Id WHERE (((MSysObjects.Type)=-32768));
(Except the Report's subform record source Where statement would have "-32764" instead of "-32768".)Each subform also consists of a "Caption" textbox and a combobox that lists all the forms or reports in MSysObjects. The Row Source for those comboboxes are:
Code: SELECT MSysObjects.Id, MSysObjects.Name FROM MSysObjects WHERE (((MSysObjects.Name) Not Like "*sbf*") AND ((MSysObjects.Type)=-32768));
(Except the Report's combobox row source Where statement would have "-32764" instead of "-32768".)My first day playing with fmDBObjectsCaptions went fine. Both subforms' comboboxes list either Reports or Forms and would easily let me choose a form or report. The subforms would record the same "Id" from the MSysObjects table into the tbDBObjectsCaptions table and each Caption I typed in was recorded into the tbDBObjectsCaptions table for each "Id" I chose. It worked just fine.
However, the next day, I noticed that the captions I assigned for forms/reports were now assigned to different forms and reports! In fact, the fmDBObjectsCaptions no longer shows any captioned reports as their Types have somehow changed from -32764 to -32768 and are therefore now displayed in the Forms subform.
Either the dbObject_ID is somehow mysteriously changing for each record in the tbDBObjectsCaptions table or the Id is somehow mysteriously changing for each record in the MSysObjects table. I don't know how or why but that's what's happening. So now, in the fmDBObjectsCaptions form, my Forms subform is displaying the wrong captions for the forms, and is also displaying records that were originally Reports (items that have captions but blank comboboxes were originally entered in the Reports subform).
I attached a copy of the database. Any different solution that allows for easy Admin'ing of displayed form/report names?
I need to update the names of my ODBC linked tables in my Access database, how can I do this without causing issues with my queries/reports?The current linked tables are to a SQL View on a database called mcsrm_live, and called e.g. vwDamagesReportNew
The new SQL views that I need to link to are identical in structure and content and on the same SQL server but different database - forkdw and are called e.g vw_R_Damages
Is there a straightforward process to do this without affecting the queries and reports in my Access db?
I have the following script which I use to modify all report settings, with an array so that I can easily list reports that I want to change the settings for, there could be 50+ reports.
I can use a string but I have to put str1 as string, str2 as string etc... whereas an array would be easier (if I knew how to do it).
Code: Public Sub ModifyAllReportsProperties() Dim obj As AccessObject, dbs As Object Set dbs = Application.CurrentProject Dim ReportName As String
I'm trying to change a database that consists of a sequence of subforms held individually within different tabs on a subform. There seems to be a bit too much going on, so I've decided to leave the subforms unbound, and set them when the appropriate tab is selected, and unset them when the tab is changed. For most of the subforms, this works fine...
However! There's always an exception!!!
On one of them, when I set the sourceobject, it automatically populates the linkchildfields and linkmasterfields properties with an inexplicably crazy number of fields, most of which aren't even on the forms in question. I've tried to get around this by setting linkchildfields and linkmasterfields after changing the sourceobject. This does not work!
The first thing that happens is it returns error 2335 (relating to an imbalance between the number of fields) when I set linkchildfields. I try to get around this by trapping and resuming to the next step so it sets linkmasterfields and restores the balance, but, despite the fact that the linkchildfields and linkmasterfields are showing the correct fields, the form acts as if it has been bound with the screwy list of fields, and starts asking for fieldnames which are on neither form as parameters. When I hit cancel the subform control is empty.
I'm at a loss as to what the problem is or how I should sort it out! Any suggestions gratefully received.
My Db has 1 table with 4 columns named ID, First_name, Last_name and Photo.
ID is a counter and primary key. First+last names are text fields. Photo is an Ole object.
I display everything in a form that is controlled by a combo box. The combo box displays the first name and when I chage the first name with this box the last name changes too. I use this code together with the event "after update"
Code:Private Sub Combo_First_name_AfterUpdate()Me.txt_last_name.Value = Me.Combo_first_name.Column(2)
Everything works perfectly fine (txt_last_name changes according to the first name in the combo box), except that the photo of the person doesn't change. Nothing happens to the photo when changing value in the combo box or it just displays a message box with an error messages when I have tried to get it to work.
I want the ole object picture to change too, when changing the first name in the combo box.
I can not beleve it I have just designed a data base with tables ,queries, a switchboard,apend table/querie, an delete table/querie etc (all self taught) but the last thing I have to do is take the data from 1 table and idata from another table and put it together BUT!! table 1(orders) has space(Key),Customer,Order date, use buy date,Quantity, Product, Discription(no data in this field after apend), Date out (no Data in this field after apend)and a yes no box for delete(no data in this field after apend),Table two(Deleted) has the same data except it has data in the discription,date out and deleted fields. When I fill out the form (orders) I put a date out and tick the check box to delete the order. my apend querie takes anything with a tick and puts it in the deleted table and then my deleted querie deletes it off the orders table.
Hope you are still with me!!
So I want to do a querie that gives me all orders both the current and deleted but not duplicate the customer name.
When I attempt to create a new DB - by performing Blank Database I immediately get a message 'Object Library not registered'.
If I 'OK' that box and try to create a table, I can do so - create Table in design view.
When I then try to Import external data - an excel file I get the message 'ActiveX component Can't create object'.
I have looked ob various sites for help and forum information regarding these errors but have found nothing conclusive, with specidfic regard to Access 2003.
The version has been loaded on my machine about 1 year as part pf Office Professional but this is the first time I have attempted to run Access itself.
Does any forum member have any ideas as to how this problem could be resolved.
I have a form with several data fields on it. I also have a button on the form that allows the user to duplicate a record . The reason for this duplication is so that if there will be an additional client record for the same customer, but only one piece of data will need to be changed, it's easier to copy the record and then change the one field.
However, I am getting the following message:
"some of the field names you tried to paste don't match fieldnames on the form"
and then not all data in all fields gets duplicated.
I need to figure this out, but am going nuts with it. If anyone has an idea or two they'd care to toss my way, I would be happy.
I am currently building a database for the company I work for that is fairly similar to the Northwind Database; however it is made from scratch so hopefully some of the common problems with that database won't find their way into mine.My problem is that when I go to my Orders form, I pick a customer from the main form, which creates a record on the Orders table. When I then go to the subform to choose a product/line item, I get the error in my title ("The LinkMasterFields property setting has produced this error: 'The object doesn't contain the Automation object 'OrdersT.") as soon as a product is chosen from the drop down list.
I'm getting an error message (informational only):
The object doesn't contain the automation object 'ClientID.'You tried to run a visual basic procedure to set a property or method for an object. However, the component doesn't make the property or method available for Automation operations.
Check the components documentation for information on the properties and methods it makes available for automation operations. This happens when I start entering data in the field "productname" of a subform.
So I'm assuming something in the form or in the code of the form is referring to ClientID. but I've checked it, and there is nothing referring to it anymore. Well, at least as far as I'm aware of.
This afternoon access crashed without any error, it just closed. Upon reopening the DB again, all my work of the past one and a half week was gone. Normally I always backup the data, but these past days I didn't due to christmas and still working a lot too. So I forgot.
I did some redesigning in the process this evening, deleting and adding some fields and code.
I can't get rid of this error. Can anyone help? Or point me in the right direction?
Also the DB is 3.5MB in size. While it is completely empty. And I can't imagine that some empty tables, queries, forms and a bit of code can be so much. But that's for a later time to worry about I guess, unless one of you says: this and that, maybe that works... Otherwise, I would really be very glad already if the error disappears....
I am creating an form in a database and whenever one of my procedure's run it creates this error message:
The expression ON Load you entered as the event property setting produced the following error: Member already exists in an object module from which this object module derives.
*The expression may not result in the name of a macro, the name of a user-defined function, or [event Procedure]. *There may have been an error evaluating the function, event, or macro.
