Bytes - answers http://bytes.com Microsoft Office Access database and VBA - Get answers to questions about microsoft access queries, reports, forms, macros, modules, security, automation, functions, records, mining, visual basic for applications and more. en Fri, 20 Nov 2009 22:16:40 GMT vBulletin 60 http://bytes.com/images/misc/rss.jpg Bytes - answers http://bytes.com Reserved Error (-1517) when adding new records to table. http://bytes.com/topic/access/answers/877377-reserved-error-1517-when-adding-new-records-table Fri, 20 Nov 2009 16:38:22 GMT I have a form in which I build a list of employees who have attended a training course, then populate new records in a training table based on the... I have a form in which I build a list of employees who have attended a training course, then populate new records in a training table based on the list. There has never been a problem with this routine, yet this morning I went in to create the list for a training that happenned earlier this week, and when VBA hit the DoCmd.RunSQL statement that inserts the new record into the table, it threw up the message:

Reserved Error (-1517); There is no message for this error.

So, I re-built the code to use a recordset instead of the RunSQL function. I just tried to run it and got the same error as soon as it tried to set the value for the first field after the .AddNew statement.

I have tried and am able to add a record to the table manually, so it's not an issue with permissions on the back-end.

Anyone have any experience with this? I tried Googling the error and got zip. ]]> answers topher23 http://bytes.com/topic/access/answers/877377-reserved-error-1517-when-adding-new-records-table how to format caracters string to make it compatible whit criteria in a query http://bytes.com/topic/access/answers/877372-how-format-caracters-string-make-compatible-whit-criteria-query Fri, 20 Nov 2009 16:07:34 GMT Good day to every one

Weird question i guest,

I'm using a function as a query criteria, that function simply get the value of a global variable all this worked of but is seams that ms access does not
understand

this an example (it refers to what it beside the quotation marks)

property = "Between 1 And 98"

When i type Between 1 And 98 as a criteria in the query design it works OK.

I can't figure what wrong.

Thanks for your help. ]]> answers atoy http://bytes.com/topic/access/answers/877372-how-format-caracters-string-make-compatible-whit-criteria-query Allow user to add a field. http://bytes.com/topic/access/answers/877362-allow-user-add-field Fri, 20 Nov 2009 12:35:17 GMT What is the best method to allow the end user to be able to add a field? Is there anyway to have a button or something on the form that would allow... What is the best method to allow the end user to be able to add a field? Is there anyway to have a button or something on the form that would allow the user to create a new field without the user having to mess with the design? ]]> answers MyWaterloo http://bytes.com/topic/access/answers/877362-allow-user-add-field Delete Query Not Working after Upgrading To SQL Server http://bytes.com/topic/access/answers/877348-delete-query-not-working-after-upgrading-sql-server Fri, 20 Nov 2009 06:40:29 GMT Hi,
I have upgraded to SQL Server Express 2005 and now my Delete Querey which was made in MS Access Query Builder is not working. It doesn't give any error it just doesn't work:

If its of any use the SQL in the query is :
Expand|Select|Wrap|Line Numbers
  1. DELETE tblCollections.MDCollectionID, tblCollections.Delete
  2. FROM tblCollections
  3. WHERE (((tblCollections.Delete)=-1));
  4.  
Any Help Would be Greatly Appreciated, I am truly desperate to get this resolved, Thanks. ]]> answers iheartvba http://bytes.com/topic/access/answers/877348-delete-query-not-working-after-upgrading-sql-server Problem of transferring data from one table to another in the same DB http://bytes.com/topic/access/answers/877338-problem-transferring-data-one-table-another-same-db Fri, 20 Nov 2009 02:45:39 GMT Dear All, I am facing a problem of transferring data between 2 tables within the same database. I set up 2 tables. The first table is the... Dear All,

I am facing a problem of transferring data between 2 tables within the same database.

I set up 2 tables. The first table is the permanent table (oos_table) for saving records. the other table is a temp table (oos_table_temp) for storing records which all data would be deleted after the data transferred to the permanent table.

below is the code I am currently using.
Expand|Select|Wrap|Line Numbers
  1. Dim strNewTable As String
  2. Dim strOldTable As String
  3. strNewTable = "oos_table"  
  4. strOldTable = "oos_table_temp"  
  5.  
  6. DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb.Name, acTable, strOldTable, strNewTable, False
  7.  
the above code works in the first time and can transfer all data in the temp table to the permanent one.

But when doing the second time, the previously data in the permanent table will be replaced by the new temp table data.

The problem is, I want to keep te previous data in the permanent table and add the data in the temp table to it. not replacing the existing data.

Could anyone help with this?

Thanks a million. ]]> answers angusfreefa http://bytes.com/topic/access/answers/877338-problem-transferring-data-one-table-another-same-db Importing multiple .CSV files. How to loop? http://bytes.com/topic/access/answers/877327-importing-multiple-csv-files-how-loop Thu, 19 Nov 2009 20:02:22 GMT Hello all, I am new to Access and VBA and hope some of you can point me in the right direction. I am not asking for someone to do my job, I want to... Hello all, I am new to Access and VBA and hope some of you can point me in the right direction. I am not asking for someone to do my job, I want to learn how to do this.

My task is to automate an access app where a user manualy imports data from printed worksheets. Currently, all this data is also available in .CSV files. I want to do all this though VBA at the click of a button on a user form.

What I need to do is:
1. Create a temporary table
2. Count all the CSV files in a dir
3. Loop through and Import all CSV files into temporary table.
4. Validate the data then move it into the existing database.

Thanks for any advice you can provide. ]]> answers Kyrgan http://bytes.com/topic/access/answers/877327-importing-multiple-csv-files-how-loop CHARTS in Access http://bytes.com/topic/access/answers/877326-charts-access Thu, 19 Nov 2009 19:34:20 GMT I am trying to add charts to forms. For my examples I have chosen fields from tables in Northwind Sample Database. The number of categories in the... I am trying to add charts to forms. For my examples I have chosen fields from tables in Northwind Sample Database. The number of categories in the fields selected could be greater than 20.

In all the examples I have tried to create ACCESS returns a chart for an ‘East, West, North’ table. The table is a very simple 3 by 4. Could the problem be that ACCESS cannot chart complex data sets and the chart I see uses a default? ]]> answers peter1234 http://bytes.com/topic/access/answers/877326-charts-access Adding additional options to combo box based on SQL statement http://bytes.com/topic/access/answers/877322-adding-additional-options-combo-box-based-sql-statement Thu, 19 Nov 2009 18:03:54 GMT Hello,

Suppose I have combo box that is linked to the following SQL statement: "SELECT DISTINCT Column1 FROM Tbl1;"

Suppose the results are 3 fields: Red, Green, Yellow

What if I wanted to add an additional field (say, Blue) to this list, although it doesn't appear in the table? I know I can type in the values myself, but I want the combo box to look the table (which is dynamic/changing), and add a few additional options.

Any advice is very much appreciated. ]]> answers bbatson http://bytes.com/topic/access/answers/877322-adding-additional-options-combo-box-based-sql-statement How do you put an image in reports without making your file size huge? http://bytes.com/topic/access/answers/877321-how-do-you-put-image-reports-without-making-your-file-size-huge Thu, 19 Nov 2009 17:53:50 GMT I have searched around the net breifly over the last few weeks trying to figure out why Access file sizes increase so dramatically when you put your... I have searched around the net breifly over the last few weeks trying to figure out why Access file sizes increase so dramatically when you put your logo on forms/reports. I haven't found much on the topic...but I know I can't be the only person having this problem. When I put a 200KB logo on about 10 forms and 10reports, my file size goes to over 100MB. Without the logos, the file size is only 3MB. At this point I have removed the logos but would like to put them back if someone can give me an idea how to do this more efficiently (from a file size perspective).

Thanks in advance. ]]> answers DanicaDear http://bytes.com/topic/access/answers/877321-how-do-you-put-image-reports-without-making-your-file-size-huge Cascading ComboBoxes http://bytes.com/topic/access/answers/877324-cascading-comboboxes Thu, 19 Nov 2009 17:40:21 GMT ...You're fast! I appreciate that.

Thanks! I look forward to your explanation.

One last problem I have on my form is getting all of my comboboxes to update each other. I know how to update them as a cascade (i.e. Region updates Country which updates State which updates City) but I would like them to be interdependent (i.e. if I select a City, I want the Region, Country, and State comboboxes to populate with the corresponding data. Hoping to get this effect when I select any of these given boxes.

For ease of understanding, let's assume these are all of my comboboxes which correspond to a field of the same name, sans "com":
comRegion
comCountry
comState
comCity

Thanks again. You're a pro. ]]> answers Tarheel http://bytes.com/topic/access/answers/877324-cascading-comboboxes Web Browser Control only shows 5 events in property sheet http://bytes.com/topic/access/answers/877318-web-browser-control-only-shows-5-events-property-sheet Thu, 19 Nov 2009 17:06:17 GMT Hi,

I'm trying to find an event in the web browser control that triggers when the user clicks on a link inside the control and arrives at a new page.

My web browser control only shows 5 events in the property list: On Updated, On Enter, On Exit, On Got Focus, and On Lost Focus.

Is this all the events?

I've tested the On Update event with debug.print, and it does not fire when a user clicks inside the control, or when the user arrives on a new page.

How do I access more events?

Thanks,
Adam ]]> answers AdamOnAccess http://bytes.com/topic/access/answers/877318-web-browser-control-only-shows-5-events-property-sheet Link to Map Site http://bytes.com/topic/access/answers/877313-link-map-site Thu, 19 Nov 2009 15:04:05 GMT I am trying to duplicate an affect I found in a contacts database I downloaded from Access. There is a little map button that will open up Bing.com... I am trying to duplicate an affect I found in a contacts database I downloaded from Access. There is a little map button that will open up Bing.com and show the address location of the address of the contact. I would really like to duplicate this in a database I am working on. Unfortunately I can't get this function to work. Has anyone ever done this? Is there code I can run or a macro? I tried copying the macro from the example database but it won't work in mine. How do I tell access to go to a website and enter the address info into the sites search parameters? Any help would be greatly appreciated. ]]> answers MyWaterloo http://bytes.com/topic/access/answers/877313-link-map-site Validation of Dates http://bytes.com/topic/access/answers/877312-validation-dates Thu, 19 Nov 2009 14:42:27 GMT My table is defined as follows : Reference text Primary Key StartDate date Primary Key EndDate date ... My table is defined as follows :

Reference text Primary Key
StartDate date Primary Key
EndDate date can be null

I currently have a row with :

Reference = ABC,
StartDate = 1st Jan 2009, and
EndDate = 1st November 2009.

I want my Form to allow me to enter a 2nd row with
Reference = ABC, and
StartDate greater than or equal to the EndDate above.

If I enter an EndDate prior to this, then I want to display a meaningful message.

This should be the case for all References, not just ABC.

Should I do so as a Validation Rule in the table design
Or an ‘on enter’ event in the Form design ?

And what would the coding look like ?

Many thanks ]]> answers grinder332518 http://bytes.com/topic/access/answers/877312-validation-dates Charts By Month http://bytes.com/topic/access/answers/877308-charts-month Thu, 19 Nov 2009 13:51:32 GMT I have created several charts of an activity by month using a simple query to aggregate the data. When I do the results only include the months that... I have created several charts of an activity by month using a simple query to aggregate the data. When I do the results only include the months that have data, i.e., if there was no activity for a given month the month is left out of the chart. Is there a way to force the x axis to increment monthly even if the data value for the month is 0? I am using stacked bar charts.

Thanks

dnetzley ]]> answers dnetzley http://bytes.com/topic/access/answers/877308-charts-month Need help ,Using switchboard not able to display chart form http://bytes.com/topic/access/answers/877305-need-help-using-switchboard-not-able-display-chart-form Thu, 19 Nov 2009 13:04:36 GMT Hello all, I am new to MS Access VBA 2007, i need help on how to open a chart form in switchboard. *SwitchBoard having options to... Hello all,

I am new to MS Access VBA 2007, i need help on how to open a chart form in switchboard.

SwitchBoard having options to open a form in Add mode and edit mode, in both of them chart is not displaying. instead its showing text box to enter data on the form.

I had attached a image file, please have a look at that.
image name 1.jpg--> its showing data when i open this chart form using SwitchBoard
Image name 2.jpg--> its showing chart when i open it by double clicking on form on left side pane.

Can anyone of you help me to resolve this issue.

Thanks in advance!
Asif
Attached Thumbnails
Click image for larger version Name: 1.jpg Views: N/A Size: 11.2 KB ID: 2441   Click image for larger version Name: 2.jpg Views: N/A Size: 10.0 KB ID: 2442  
]]> answers asifu9 http://bytes.com/topic/access/answers/877305-need-help-using-switchboard-not-able-display-chart-form