access-vba

Record locking in vba

i have a VBA application which is spitted into two one for front end; and backend while updating how to prevent two users edit it ?? ...

Operation must use an updatable query. (Error 3073) Microsoft Access

I have written this query: UPDATE tbl_stock1 SET tbl_stock1.weight1 = ( select (b.weight1 - c.weight_in_gram) as temp from tbl_stock1 as b, tbl_sales_item as c where b.item_submodel_id = c.item_submodel_id and b.item_submodel_id = tbl_stock1.item_submodel_id and b.status <> 'D' ...

Import from csv to ms access tables using macro

Can some provide me a VBA macro to import data from a CSV file into ms access tables ? ...

Update calling all rows if clause in vba

i am writing a macro to convert the zeros in the access table to "0000" Zero is text data type so i cast it to int in the if condition to update the records which is only zeros and preventing it in updating records which are non zeros but now all the records are getting updated ..if clause is calling all the time even there is a value...

Need a better way then using if statements in MS Access

Right now I am working on a new database that will show changes to one of our manuals that people request. I have two tables and one form. The first table is were all the information will be stored from the reference or subchapter of the manual, the current text in that subchapter, and what the proposed changes are. The second table i...

Using VBA to export all MS Access SQL queries to text files

I have to document an MS Access database with many many macros queries, etc. I wish to use code to extract each SQL query to a file which is named the same as the query, eg if a query is named q_warehouse_issues then i wish to extract the SQL to a file named q_warehouse_issues.sql I DO NOT WISH TO EXPORT THE QUERY RESULT SET, JUST THE S...

Compiling an Access 2007 accdb into accde

I'm trying to use the built in Make ACCDE functionality in access to make a standalone application that interfaces to an SQL-Server 2008 back end. When I run the Make ACCDE I receive an error that states that too many TableID references are being used (the limit is stated to be around 2048). Through trial and error (deleting forms unti...

SQL as Control Source for Access Form field

Is there any way populate an Access Form's text feild's value using SQL? I have read that it is not possible to simply enter SQL as the Control Source. Is this true? thanks for any halp :) --edit-- I need to perform this query; SELECT tblCaseIssues.IssueDesc FROM tblCaseIssues INNER JOIN tblCaseNewHS_Issues ON tblCaseIssues.ID = tbl...

Best way to get data from Ms Access Mdb file to sql server 2005

We use a software which uses Sql server 2005 as back-end. The problem is we have data coming in access file and we need to store it in sql server tables. I have the sql server tables ready. I don't need all the data i get in MDB access file but need specific columns from access and then store it in SQL server. I get fresh access tab...

Error when running Access 2003 app in Access 2007

My organization is in the process of upgrading from Access 2003 to Access 2007. One of the apps I am tasked with upgrading is returning very odd errors while opening and closing forms that have nested forms. Have the events changed between Access 2003 and Access 2007 ? The events seem to be firing in a different order in 2007 then they d...

Catching event when a link is clicked in a Textbox displaying richtext

In Microsoft Access 2007 the Textbox can be set to display a cut-down version of HTML as richtext. However, there does not seem to be an easy way to detect what has been clicked within the box itself. For instance, you can display a classic HTML <a> tag that appears as a link but clicking it doesn't generate any event. I'd like to know...

VBA - Accessing a collection of values

I have two access forms. frm_Main and frm_Sub which has data conditionally displayed depending on the selections of the main form. I need to write a select all function for items displayed in frm_Sub. In VBA is there a way that I can get a list of the id's currently being displayed in frm_Sub? for example, if I do this me.controls(...

Access VBA - relative file references

Hi, In have built a couple of mda library files which I am then referencing from my main Access application (i.e. using Tools -> References from within the IDE). Is there a way that these references can be made relative rather than absolute. The reason I am asking is so that it would make it easy to set-up on the user's computer if all...

Limit number of selections in a MultiSelect ListBox in Access?

Is there a way to limit the number of selections a user can choose on a ListBox with MultiSelect enabled in Access 2003? Right now I have a procedure that fires on the On Click event that checks the number of choices selected and if it is over my threshold it will display a warning label. ...

Access sometimes jumps to existing record on save new record - Access2k FE/SQL2005 BE

Hi, this is my first question on SO, am really posting this out of desperation after searching around a lot for an answer and trying a few different things with no success. I have an Access database where I have recently migrated the tables to SQL 2005, Access continues to function to the users as a front-end providing forms, reports, ...

Rookie SQL inside of VB question - MSAccess 2003

Hey guys, can anyone help me with a simple question. I have this SQL statement below in a sub in VB within access from a button click, and I am new to this. Here is what I typed: Private Sub Command0_Click() Dim rs As Recordset Dim sql As String Dim db As Database Set db = CurrentDb sql = "SELECT * FROM Transactions" Set...

MS Access 2003 VBA String Split on Line Break

I have a textbox on a MS Access form that users are going to copy a column of numbers into from an excel spreadsheet. I need to take this input and use it as parameters to build a query. I have code that looks like this Dim data as variant Dim input as String data = Split(input,vbLf) I want to be able to build a list of the input fr...

MS Access 2003 - Information from a form output to a text file

So I have this form that has tons of text boxes, combo boxes, etc. There is VB in the form's module that check to see if there are duplicates, and then run a few update queries to save the information in to tables. All that works great. My question is what VB method can I use in this forms module to take information from all these objec...

BeforeUpdate problem - Runtime error 2115

I have written the following query : Private Sub Size_Sqft_BeforeUpdate(Cancel As Integer) Me!Size_Sqft = Nz(Me!Size_Sqft, 0) End Sub But while i removing the zero in the filed to make it null , i am getting the following error Runtime error 2115 Macro and function set to before update and validation rule property for this field...

How to access the label of a control in code

Hi! Often a label control is bound to a textbox or similar. I know that there is a way to access the label through code, but I do not remember now how to do it. Anyone that knows? Maybe something like: Me.txtName.Child!lblName.Value Or Me.txtName.Parent!lblName.Value I will use this when I am enumrating through the controls on a...