	AllExperts > Experts
	Search

About Geoff
(Top Expert on this page)

Expertise
I specialise in database analysis and design, SQL and database queries using QBE and VBA. In my work, I use MS Access together with MS SQL Server as ETL (Extraction - Transformation - Loading) tools for migrating data between business ERP systems and data stores. My forte is building bespoke functions and applications.

See my website for example apps and downloads

Experience
I am a chartered engineer with 30 years of engineering and business experience, member of the BCS and have been working specifically in database applications, including SQL Server (v7/8/2000) for the last 9 or so years. I previously taught a course in Database Analysis and Design, but am now a freelance consultant and systems analyst.

Commercial database design and development work undertaken.

You are here: Experts > Computing/Technology > Business Software > Using MS Access

Questions Answered By Expert Geoff
In Category Using MS Access

Subject	Date Asked

query	11/13/2008
Q: There are two tables and a form, when the user types in the number into the text box and choose the ... A: Zsolt hi, This is a "design" feature of Access ! No records = blank form. The only solution is to ...
How can i fetch data relative to data filled in the current form	11/13/2008
Q: I have two tables in my DB, one is main table and another one is lookup table. I have two fields in ... A: In the [Name] text box Control Source property enter an expression based on the following - you will ...
Multiple Entries in an Access table field	11/12/2008
Q: How can I allow several entries in an Access table field? I have a field called 'associated ... A: I would not advise this in a database application - of course if you want to manually type in ...
Obtaining next available Member ID	11/12/2008
Q: I am trying to determine how to best set my form up to obtain the next available member number. How ... A: Why don't you use the Autonumber data type for the MemberID - it was made for exactly this ...
Multi Field Search Function in Access	11/11/2008
Q: I'm new at using access, and really hope you can give me some detailed instructions. I have set up ... A: Set the subform based on a query, say qrySF - anything just to get going. Next under the search ...
Multi Field Search Function in Access	11/11/2008
Q: I'm new at using access, and really hope you can give me some detailed instructions. I have set up ... A: Proramming a search form is one of the most complicated tasks for any developer, so if you are new ...
Transfertext -can't export date w/o time component	11/10/2008
Q: I have a table with a date fld to export to CSV. Fld is formatted ShortDate. I have tried format & ... A: Could you manually perform the export of the query, and whilst the wizard is open, click on the ...
Updating parts of strings	11/10/2008
Q: I have to update an old database, it contains an "ID" field which is repeated throughout the db. At ... A: Is the ID (in the string) always preceded by " : " ? If so, you could use this to locate the 5 ...
Columns to Rows	11/9/2008
Q: In an earlier response on how to transpose columns to rows you wrote : "Using VBA - create a target ... A: The INSERT SQL command is for inserting new records to a predefined structure: it will not allow you ...
IIf and DateAdd problem	11/7/2008
Q: I have a form that I have created that has the following fields: Date Last Revised: date field ... A: I would create a Frame object with 2 radio buttons (with the wizard). Lets say the Frame object is ...
Transfertext -can't export date w/o time component	11/6/2008
Q: I have a table with a date fld to export to CSV. Fld is formatted ShortDate. I have tried format & ... A: Behind a date/time field is a real number: the LH part provides the date part, the RH part (from the ...
arrays and recordsets	11/6/2008
Q: I have an array ' validcodes' with a few values. i have a recordset with two columns id number and ... A: I have built a table tblArrayValidate, and in the following VBA procedure, I populate an Array ...
Import Object	11/5/2008
Q: I was wondering if it is posible to import a object like a form or module from another database ... A: Sam, Hi, I've found a way - its a bit messy, but it does work: I have to give credit to G Hudson at ...
Input forms and foreign key	11/5/2008
Q: I have two tables where Table1's primary key (RegNum) is a foreign key on Table2. I am now creating ... A: The easiest way is to make Form2 a sub-form inside Form1, with the sub-form container object linked ...
MS ACCESS QUERY GROUP SAME NAME INTO GROUPS >7	11/4/2008
Q: low to mid level with access use it for my hobbie I use a table to check past performances of ... A: if your table is called tblHorses and the column name with the HorseName is called that: the ...
Command Buttons and Subforms	11/4/2008
Q: I have a form, called FormA, with subforms: SubFormB1 and SubFormB2. Here is what I would like to ... A: Just toggle the 'Visible' property of the subform with the command button click event as per... Sub ...
MS Access - OrderDetails table problem	11/2/2008
Q: I am in the middle of creating a stock control system. A problem as strike but i just cant seem to ... A: Elizabeth, hi, You don't provide the code for the function GetListPrice(), however you could use ...
editing listbox	11/1/2008
Q: I have 1 table , 3 query, 1 form(1 textbox, 3 list box, button). The form has been designed to ... A: Instead of using List boxes, use 3 sub-forms - each uses the same view of the data, but with ...
editing listbox	10/31/2008
Q: I have 1 table , 3 query, 1 form(1 textbox, 3 list box, button). The form has been designed to ... A: I don't fully understand the set up of the form, the way the user works with the form, and what/how ...
Mass Email Shot VBA	10/29/2008
Q: A little while ago you sent me some code to help with me sending out mass mailshots by email. The ... A: I have moved the End With associeted with the objMail, inside the While/Wend construct... ...
Simple Class Choice Query	10/29/2008
Q: Basically I am trying to design a database that when data is submitted from a form which asks ... A: Are you on the right track ? Definitely, just a bit more trekking to do !!! If only more developers ...
Automating start date and end date	10/28/2008
Q: I have a form of which I added two combo boxes, (cboYear and cboMonth) and I also added the two ... A: The active code to deliver that functionality would be:- Public Sub cboMonth_AfterUpdate() Dim ...
Automating start date and end date	10/27/2008
Q: I have a form of which I added two combo boxes, (cboYear and cboMonth) and I also added the two ... A: Use the AfterUpdate event on the two combos, but you will need to test that the value in the other ...
Refresh linked table without opening the source	10/24/2008
Q: 1. I have an access table linked to an excel spreadsheet. How can I refresh the excel table without ... A: 1. Ref updating Excel - you can do this using VBA as follows: Excel XLS Handling Dim xlObj As ...
query for comparision	10/24/2008
Q: THis is with regard to the solution u had given me Public Function RunReports() ' OBJECTIVE: Using ... A: Thiru, Hi This will be a bespoke solution for your application, which will require a full tech spec ...
query criteria from table	10/23/2008
Q: I have two databases. one of them has the records. the other table from the 2nd databse has the ... A: The following lines is the build-up of a single SQL based on the constraints held in tblCrit and to ...
Seperate Date from Program	10/23/2008
Q: I made a program on ms access for followup 6 peoples are using it through network, WHEN I need to ... A: He Sher, Your network strategy is fine. A separate MBD file containing the data tables, then a ...
VBA	10/22/2008
Q: I want to change the row source property of combo box through VBA. However when I use the command ... A: I have a form with a combo "cboFruit", and a button "cmdChangeCode" to change the RowSource ...
query criteria from table	10/22/2008
Q: I have two databases. one of them has the records. the other table from the 2nd databse has the ... A: Strategy: 1. Write a function to cycle through the criteria table, and for each criteria, run the ...
query criteria from table	10/21/2008
Q: I have two databases. one of them has the records. the other table from the 2nd databse has the ... A: You certainly can, though you will need to decide very clearly how it should work The tables from ...
Updating Tables Using A Command Button	10/21/2008
Q: I've set up a User Form that contains two fields from my primary table - 'ID' and 'End Date'. The ... A: Mark hi, If the End Date field on the form is called [End Date], then the code to place a date in ...
MS Access calculate GPAs in report	10/7/2008
Q: Greetings, We have a student/course registration database normalized to about 3rd level. I need to ... A: My inclination is to have two sub-reports - one for completed terms, in which the average is ...
Database changes	10/7/2008
Q: I completed the advanced ecdl a few months ago and now work has asked me to make changes to our ... A: Bob Hi, It sounds like you should do some design before you start bashing the keyboard: I normally ...
access table comparision	10/6/2008
Q: I am using access i have two databases. The first database conatains values which are permitted for ... A: Thiru, hi You most definitely can link databases: in the Table tab of the DB dialog, right click, ...
Executing from Access	10/6/2008
Q: I am familiar with VBE from Excel, but new to Access2007. I have converted a macro and it has ... A: Sorry for the delay, I have been away on vacation. Here is some sample code for calling a macro in ...
Email addresses from query to copy / past to mail merge.	10/4/2008
Q: I have a database with a form the user uses to filter records to find only those with email ... A: Ref the recipient... 'Add TO Recipient With .Recipients.Add("abc@xyz.com") .Type = olTo ...
Email addresses from query to copy / past to mail merge.	10/3/2008
Q: I have a database with a form the user uses to filter records to find only those with email ... A: Simon, hi, 1. use With .Recipients.Add(rs![Recipient]) .Type = olBCC ' Blind ...
utonum no deletion	10/3/2008
Q: I created an order database on Access 2003. The order number ist an autonum field in a table. It all ... A: Are you sure about this European law? In which directive is it contained ? It would be impossible to ...
Email addresses from query to copy / past to mail merge.	10/2/2008
Q: I have a database with a form the user uses to filter records to find only those with email ... A: The 'Email All' button should launch a stand alone procedure that generates and sends out the email. ...
Import text file in Access	10/2/2008
Q: I want to import a text file into access. The problem I have is that this file has a new name ... A: The import specification does not store the input file name, but the mapping of input columns to ...
Import text file in Access	10/1/2008
Q: I want to import a text file into access. The problem I have is that this file has a new name ... A: Please see my answer to a similar question... I hope this helps Kind regards Geoff :-) Subject: ...
Update values in one table form another table	9/30/2008
Q: hope you can help. I have a small Access 03 database which records data about student targets and ... A: Adam, Using the Query builder, bring in the two tables, link the FirstName, Surname and DOB fields ...
Update Query	9/30/2008
Q: This is a SQL View of an update query that i created. Could you please help me convert this query to ... A: Here is a subroutine to run the SQL - you could make this the action of a button, by placing the ...
Date add	9/29/2008
Q: I have a form created in a ms access 2000 database. There are 3 fields, Probation Period Start ... A: Use the 'after update' event of the Start Date textbox, as follows... With the form in design mode, ...
Issue with Ending Inventory using Access	9/24/2008
Q: The question is, How can I make the ending inventory value entered in the form to become the ... A: I am not sure how you have structured your transactional, audit and valuation data, and my answer ...
Multiple Selection Combo - need to select all.	9/23/2008
Q: I have a multiple selection combo on my form and I need a macro or VB code which when a cmd button ... A: Try this code under the command button's click event ... Public Sub MyCommandButton_Click() Dim ...
Deactivate	9/18/2008
Q: When a group of data is no used anymore, but i do not want to delete it, i want to deactivate it, by ... A: The best way to achieve this is to carry an additional Record Status column on the table of say ...
Multiple Count function	9/18/2008
Q: I have a table which includes names, accounts and the transaction date. I want to create a query ... A: Fez, hi, First, build a query that returns the Number of days. I'll call this Qry1 SELECT Name, ...
Filter and preview	9/16/2008
Q: It helped me a lot. My new question is... I have a database. It has 3 fields. ID NAME ... A: I will assume you have a command button to open the report. The code behind the button needs to be ...
Time limit use for access application	9/16/2008
Q: I have created a ms-access2003 application. Now I want this application to run for 15 days only. ... A: Here is a possible solution... You need a table to hold the date/time the application was first ...
Access	9/15/2008
Q: I said to a friend that I would try to figure out his problem for him, and I am not having any luck ... A: You will need 3 tables: 1. T_Ingredient 2. T_Product 3. T_ProductXIngredient Table 3 is the ...
Text Box control	9/13/2008
Q: I am trying to make a form. It has 3 text boxes. Textbox1, Textbox2, Textbox3. I want to make the ... A: As the user tabs or clicks into say Textbox2, after Textbox1, the following sequence of events ...
adding multiple queries to a form	9/12/2008
Q: My question is a little convoluted, at least I think so anyway. I was having an issue with a query ... A: Use multiple forms: one as the main form, perhaps controlling the primary table/query, then ...
=Date() function	9/11/2008
Q: As part of my database I have a form in which several new entries need to be inserted each day. I ... A: Beth, hi Please check that the name of the textbox on the form is called [Date] If it is something ...
=Date() function	9/10/2008
Q: As part of my database I have a form in which several new entries need to be inserted each day. I ... A: In the expression Me.Date, there must be NO spaces and you should put [] square brackets around Date ...
Textboxes In Report Empty When Printing	9/10/2008
Q: I have tried to get this to work for a while now. I have an Access report that holds several ... A: Angel, hi Sorry, I can't really help here: its Microsoft wizardry at work... My inclination is to ...
Validation in Ms Access	9/9/2008
Q: Sorry Geoff, I have to ask my question again because I didn’t give you enough details because the ... A: Method 1 - assume a new column 'Validated' in Table 1 UPDATE Table1 LEFT JOIN Table2 ON Table1.IDNo ...
=Date() function	9/9/2008
Q: As part of my database I have a form in which several new entries need to be inserted each day. I ... A: Beth, hi Use the form's Current event to test for a blank/null date and only insert the default if ...
Sorting a form	9/8/2008
Q: We have a form based on a view. We want it to sort by our date field. We tried the OrderBy ... A: Look up 'OrderBy' property in Access onboard help: - it does not always apply itself (weird..) - you ...
Data validation using SQL	9/8/2008
Q: Geoff Can you please assist me with the following question in Access. I have two tables with ... A: You could use two approaches: 1. Append a 'Validated' column with a default of 0, then run update ...
Month Countdown	9/4/2008
Q: I need an expression that counts down months starting at 15. And i need it to work off of a date ... A: Mikel, Hi, Use the DateAdd() function, as follows: = DateAdd("m", -1, txtYourDate) to ...
Access Auto Email Addresssing	9/3/2008
Q: I have what appears to be a pretty simple request - but its got the better of me.... I’m trying to ... A: Nick hi, Try using the following code... ...
Exporting Info to Excel	9/3/2008
Q: I've put together a collection of forms that will essentially walk someone through a particular ... A: Brett, hi I don't fully understand your application requirement, but try using the following code ...
Access Database Connection via ODBC	8/25/2008
Q: I have developed numerous Access database projects where the database is either linked to the tables ... A: I am not sure I know the answer, but I am keen to find the reason for this behaviour as one of my ...
Forms and Parameters	8/16/2008
Q: I'm attempting to control the criteria of a query by using a form. The form will have multiple ... A: Brett hi, The only way to do this is using VBA. The strategy is as follows: 1. Build a configured ...
Anthony Warren's impossible report	8/15/2008
Q: Did your solution provide Anthony with a way to produce the report as he wished? It only took him ... A: The query(ies) behind the report are primarily important as these provide the data being reported ...
Access Reports	8/13/2008
Q: I have a label in a report in access that pulls a year from a specific table within my database. ... A: Katie, hi Sounds like a formating issue: you have 2 options. Option 1: With the report in design ...
Copying records in a form	8/13/2008
Q: While viewing my database in form view I am attempting to code a button to enable the user to add a ... A: 1. You need to be sure that the query is an updateable query, or the data is entered directly into ...
the impossible report?	8/12/2008
Q: I need to build a report to show student choices for work experience at our school. employers ... A: Behind the reports you want to build are queries that deliver the data to be published. Here are my ...
Inventory	8/12/2008
Q: It was very helpful. My original question I have an Inventory DB. SupplierTbl – SupplierID, ... A: Irit, hi, With only the tables you have indicated, you will not be able to provide the ...
Picture IDs	8/10/2008
Q: I work at a Prison with 2500 Inmates, I'm trying to create a roster using one table, this table is ... A: As I see it you really need 3 areas in your database:- 1) Interface table to handle updates from the ...
Rounding in MS Access	8/9/2008
Q: Geoff, Thanks for answering my earlier question. May I also ask your help in the following ... A: Nelson, hi, Try the following technique: ?Round((0.0135 - 0.0005),3) where, I assume, the value ...
access information on subform (datasheet view)	8/8/2008
Q: I am using Access 2003 (v.11) with windowsXP Professional, version 2002 with 1.99 GB of RAM. I have ... A: Janet, hi Might I suggest a clean start, very simple approach, then take a look at this and see ...
Parameter	8/8/2008
Q: "Sir, I want to accept parameter in form, for that I have written following code but I am facing a ... A: Try this:- Set RS = New ADODB.Recordset RS.CursorLocation = adUseClient RS.Open "SELECT ...
Updating records without WHERE clause for the remaining records in a table	8/7/2008
Q: I have four columns in a table. In one column say assessmentyear (date type), I want to change the ... A: Nelson, hi, You will need to use a WHERE predicate if you want to 'UPDATE' only a section of the ...
Showing only 1 of each record	8/7/2008
Q: i have a report on in my database taht shows all of the people interested in going to a certain ... A: Depending on the number of columns in your table, you could use:- 'DISTINCT' or 'GROUP BY' ...
Selecting the date	8/1/2008
Q: hey I am making a database for a company that needs to store data about a group of graduates and ... A: Change the SQL from SELECT ... TrainingRecordDate, DatePart("yyyy",TrainingRecordDate) * 100 + ...
Calculating a total	7/31/2008
Q: hey i have a database on access 97 and i need to calculate the total number of hours that someone ... A: Jaffa, hi Did you try setting up a "Group" on the report ? Assuming each individual has a number ...
databbase locking in MS-Access	7/31/2008
Q: How do I prevent Access from locking the database when I am testing a program that involves ... A: Bob, Go into Access Options (Windows Button), and set Row Lock - then try. Next, try opening the ...
Menu on Form	7/30/2008
Q: Sir, Can I make Menu on my form in Access. If possible how? I was using my software till now on ... A: With the form in design view, Menu / View / Toolbars / Customise New / Enter menu name -> an empty ...
disabling design view	7/30/2008
Q: I've read that i can disable the design view by converting the database to ACCDE format. however ... A: Does your MDB comprise both the data and the forms/queries etc ? If so, split the database: data ...
Inventory	7/29/2008
Q: I have an Inventory DB. SupplierTbl – SupplierID, SupplierName CustomerTBL – CustomerID, ... A: You need three additional tables: PartSNTbl --------- PartID - Numeric - long PartSN - Text ...
Keep a record of entires from multiple tables	7/29/2008
Q: I am writing a program which calls an Access database (.mdb) file to get product information from. ... A: Andy, hi, Could I suggest, a more productive way would be to interrogate the tables concerned, when ...
Conversion of Figure Amount in word	7/28/2008
Q: I am using Ms-Access 2003. Question 1:-I have a report in which I want to display my total amount ... A: Ref #2 You could not have entered the Input mask property correctly, OR your datatype for the ...
SQL and VBA	7/28/2008
Q: I'm trying to connect SQL server via VBA in MS Access. I'm trying to get the list of all databases ... A: I find that you will always need some tables in the application to manage the running of the ...
databbase locking in MS-Access	7/27/2008
Q: How do I prevent Access from locking the database when I am testing a program that involves ... A: This is surprisingly difficult to give you a succinct answer as this is a very broad & deep subject ...
Conversion of Figure Amount in word	7/26/2008
Q: I am using Ms-Access 2003. Question 1:-I have a report in which I want to display my total amount ... A: #1 Paste the following functions into a module and then in your query, call the ...
how to manage inventory inward & outward	7/26/2008
Q: i have these table tblDesign :- Design ID Design name , Shade Number , Pcs , meter i have INWARD ... A: You should enhance the database design a little first: Add a table tblTransaction, with the ...
Inserting JPGs into table using VBA	7/25/2008
Q: Is there a way, using VBA to insert a JPG photo file into a table OLE object field? A: Harold, hi, Access will only permit BMP images The recommended way to manage images, is to store ...
selecting dates from 1 year ago	7/25/2008
Q: Hey Geoff - its me again (hopefully for the last time :P) Still on Access 97 >.< in my database i ... A: Easy.. Use the DateDiff() function on the applicable date column. Lets call this column [StartDate] ...
SQL and VBA	7/25/2008
Q: I'm trying to connect SQL server via VBA in MS Access. I'm trying to get the list of all databases ... A: Shital, hi You need to build a 'Pass-through' query in Access that uses ODBC technology. Using ...
Querying on multiple fields to obtain the result on the third field	7/24/2008
Q: Goeff Advance thanks to u. I have a table by name ‘price_detail_master’ with the following fields ... A: Jai, Hello, The RecordSource property for combo #1 is: SELECT DISTINCT product_name FROM ...
Enter Parameter Value	7/23/2008
Q: I have created a couple of queries for calculations. One calculation is adding then multiplying the ... A: The best approach is to test for a Null or zero divisor before attempting the division, then if all ...
Insert Into Statement	7/23/2008
Q: I am working on my database and I have added code to make a button in my form update my table, but ... A: Chris, hi, You need to check the [ActivitieType] data type: if it is numeric then you need a ...
Insert Into Statement	7/23/2008
Q: I am working on my database and I have added code to make a button in my form update my table, but ... A: Chris, hi, try... dbs.Execute "UPDATE [Contact Infromation] SET [Letter Sent]=-1;" This will up ...
Images	7/22/2008
Q: In this database there is a form for all the graduates and I want to be able to display a picture of ... A: This is a complex area: please refer to the following:- MS Access Help - "Store images in a ...
Querying on multiple fields to obtain the result on the third field	7/22/2008
Q: Goeff Advance thanks to u. I have a table by name ‘price_detail_master’ with the following fields ... A: Jai, hi Sorry I missed that bit.... Use the AfterUpdate event of the 2nd combo to set the textbox ...
Images	7/21/2008
Q: In this database there is a form for all the graduates and I want to be able to display a picture of ... A: Sam, hi, Please describe exactly the problem, including the object types you are using to display ...
Querying on multiple fields to obtain the result on the third field	7/21/2008
Q: Goeff Advance thanks to u. I have a table by name ‘price_detail_master’ with the following fields ... A: Jai, hi Ideally in your data base you should normalise the data, so the table above should comprise ...
database	7/20/2008
Q: I want to develope database for my metal trading business, can you pls help me about the tables, and ... A: Hanif, hi, Your question is very broad ranging and outside the scope of AllExperts.com which is ...
MS Access --> SQL Server	7/19/2008
Q: I currently support a front end MS Access application connecting to SQL Server. I compile a .MDE ... A: Karen hi, Have you tried setting up an ADP (Access Data Project). ADPs are different to the config ...
Access2000 data types	7/18/2008
Q: SIMPLE QUESTION! ( a bit embarassing) How do I format my field to display th ecorrect ... A: You have two options: #1 - Enter the fraction as a real number, ie: 0.095, or, #2 - View the data ...
Maintaining incremental row count	7/18/2008
Q: I have a multi-table, multi-form database that has a "control referencing a control on another form" ... A: Cindy, Hi It sounds like your table structure is not correctly set up for database operations. ...
Selecting the date	7/17/2008
Q: hey I am making a database for a company that needs to store data about a group of graduates and ... A: In your query, create a new column, with a field name of: ReportMonth: ...
Limiting a table to a set number of individual records	7/10/2008
Q: I am an IT Teacher primarily but this is my hobby, that I hope to make pay - moved from the UK to ... A: I think the way I would approach this would be via a time limit rather than number of records, and ...
asp update	7/10/2008
Q: I can't get the update statement to work, can u please help thanks. <% sSQL = "SELECT * FROM Detail ... A: Missing spaces.... sSQL = "Update Detail set" sSQL = sSQL & " Equipment= '" & ...
Update Form	7/9/2008
Q: I have a form to add data into a table. I also have a form to update this table. I need to make ... A: John, Why not add a 'Search' button to your form. Use the Form wizard to configure this for you. ...
comboBox form control	7/7/2008
Q: Geoff, I currently have a form that has a tab in it and (obviously) the tabs have subforms in them. ... A: Jasson, Absolutely - you need to edit the following to suit your application For all of these, ...
Update Queries	7/7/2008
Q: I am using a form that shows the user what the current values in a table are i then have a duplicate ... A: In the code behind the 'Save' button you need to build up the SQL step by step based on the values ...
comboBox form control	7/3/2008
Q: Geoff, I currently have a form that has a tab in it and (obviously) the tabs have subforms in them. ... A: Jasson, hi, I haven't worked a sample as such, but this would be my approach... Main form called ...
computing total	7/2/2008
Q: I want to add a 5% fuel charge on invoices. What formula do i use to compute this and add to ... A: Create a query based on the normal table or query that you'd use for your invoice, except for the ...
Logic vba	7/2/2008
Q: Ive been using the following code on button click to open an letter and run a query if ok is pressed ... A: Try this... Kind regards Geoff Private Sub cmd_Mailmerge_Missing_Info_Click() If MsgBox("Do you ...
Save button	6/25/2008
Q: 1st thanks for previous helps and guides i have form contain data linked directly to GRVtbl, and ... A: Datto, hi, You could have one modal form serving both the Voucher and the transaction, or one for ...
Save button	6/24/2008
Q: 1st thanks for previous helps and guides i have form contain data linked directly to GRVtbl, and ... A: Datto, hi, I think my strategy would be to make the main form/subform as read only. If the user ...
Save button	6/24/2008
Q: 1st thanks for previous helps and guides i have form contain data linked directly to GRVtbl, and ... A: Datto, hi, 1. You are opening Pandora's box as soon as you want to control human behaviour. 2. You ...
printing labels from form view	6/23/2008
Q: i have created a DBMS for stock levels at work and would like some of the information on one of the ... A: Chris, hi 1. Create a query to return the fields needed in the report. 2. Turn this into an ...
Access Query	6/6/2008
Q: I have made a simple crosstab query using columns Description, Expr1: Format([Date],"mmm"), ... A: Paul, hi Sorry to be late, but been v.busy... You can control the vertical sequence but to my ...
Microsoft Access 2000/2003	6/6/2008
Q: I hope you could help me in my problem. I am creating a program in Microsoft Access 2000. Part of ... A: The Combo (assumed to have a single column of [ITEMS]) needs an AfterUpdate event to populate the ...
Update a text field with the result of a query	6/5/2008
Q: I'm trying to get field to update depending on what is selected from a drop down list - namely the ... A: Cate, If you want to look up a value from a table, given a value in a Combo, use teh DLookUp() ...
Query question	6/4/2008
Q: I am trying to create a report to total the number of hours someone is working, AND total number of ... A: Jeff, hi, When you capture the employee times, you should do this using the Now() function, ...
Update a text field with the result of a query	6/4/2008
Q: I'm trying to get field to update depending on what is selected from a drop down list - namely the ... A: I don't understand what the top query does and how it is used, however, I think your after update ...
Imported information doubling up	6/2/2008
Q: I am saving information from my hotels property management system to excel. I am then importing it ... A: You can do a number of things: 1. Save your info in Access - ditch Excel, or export data out to ...
and statement in sql query	5/31/2008
Q: I am selecting a table field with 2 variables. code below. I am recieving a type mismatch error. ... A: The problem would appear to be in the datatypes between: MASTER1.TECH_NUMBER and Me.Technumber and ...
Query question	5/30/2008
Q: I am trying to create a report to total the number of hours someone is working, AND total number of ... A: Try using the Date functions DateDiff() to return the respective times involved, then the report ...
Setting report Page setup via VB in Access2003	5/30/2008
Q: I would like to be able to update the the page setting for a report via VB in Access2003. Update ... A: Michael, hi, 1) Could I refer you to a paper on MSDN ...
Using Combo Boxes to view a list of reports	5/29/2008
Q: Geoff, I'm trying to have a dropdown list (maybe using combo boxes) that displays a list of already ... A: Adam, Hi, Set up a table of report names, and use this as the basis of the combo. I assume the ...
Access Query	5/26/2008
Q: I've created an access.mdb with 5 tables, one of the table contain various usernames, I want to be ... A: If you are using the Access Query tool, by entering an expression as below, under the 'Recipient' ...
Using NotInList with multi-column tables	5/23/2008
Q: I am using MS Access 2003 on WindowsXP, and have been trying to adapt your NotInList code to use ... A: Try using the function: MyValue = InputBox(Message, Title, Default) to get a value on the fly (see ...
access	5/23/2008
Q: 1.how do i get an access database to overcome the limitation of 2 gb? 2.can u create forms in vb ... A: 1. It is usually data that results in hitting the 2GB limit. The way around this is to split out the ...
MS ACCESS 2007- sum two or more fields in a query	5/22/2008
Q: I need help in two things: 1. How can I sum the results of two or more fields in a query. I tried ... A: Elaine, If [field1] etc are calculated 'fields' then it will not work, if they are columns ...
24Hr sum function	5/22/2008
Q: I work in a call centre and deal with MI reporting. Im currently building a form that displays the ... A: Dan, The way to handle this in Access is as follows: Use the DateAdd() or DateDiff() functions to ...
Add data to combo box with table	5/21/2008
Q: Good day Geoff, I have a drop down combo box (Combo11) on a form (FrmLocate). It was created using ... A: Janet Hi, I have built a prototype and got it to work: My combo, called cboFruit, is based on a ...
Add data to combo box with table	5/15/2008
Q: Good day Geoff, I have a drop down combo box (Combo11) on a form (FrmLocate). It was created using ... A: try this... Private Sub Combo11_NotInList(NewData As String, Response As Integer) Dim ctl As ...
Moving from MS Access to ASP.NET & SQL Server Express	5/14/2008
Q: I have a fairly good grasp of MS Access database design with an intermediate level of VBA ... A: Try this URL for learning about SQL Server: http://www.microsoft.com/sql/techinfo/default.mspx ...
Open form by sub-form information	5/14/2008
Q: I'm working on a Tool information database which shouldn't be too hard to carry out. I have two ... A: Exactly as you have done for Tools-Product, but the other way round. Set your main form based on ...
SQL Query	5/3/2008
Q: Following is the table Div district amount 1 mandla 4000 1 Katni 2000 1 Jabalpur1000 ... A: Musharraf hi, Your result set does not tie up with the group by / order by you have stated This ...
Making a checkbox change state	5/2/2008
Q: I have a form with checkboxes associated with fields in a report. I want to have the user check ... A: Try this.. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) If chkIndividual = ...
Access 2003	5/1/2008
Q: I am a newbie & have invested in Access 4 Dummies & numerous other books from the library (so doing ... A: Amanda, hi You will need to get into programming with VBA Check out the following tutorials ...
field updates	5/1/2008
Q: if additional new fields are added inbetween existing fields. how do you get access to tab through a ... A: Anthony hi, Open the form in design mode, select a textbox (field), right mouse button, select ...
CheckBox ComboBox Extending the ComboBox Class and Its Items	4/29/2008
Q: I want to create a combo box with multiple pull down check boxes and when the user clicks on one or ... A: Try using a subform approach. Build a form as 'Continuous Forms' with the check boxes etc linked to ...
name a query and loop	4/29/2008
Q: I have a query that lists several "plans". I am trying to run another query off of that one and ... A: I will try the high level approach, as I think you are fairly close to the solution. If you can't ...
Data Access Page editable query	4/28/2008
Q: I have a main webpage to enter data. Now I have created a query which has a parameter to find a ... A: Janell, hi, There is a simple rule... If you want to edit/add/delete data you need to access the ...
Running a function	4/28/2008
Q: Im quite new to VB so im still learning the basics Through extensive browsing of this forum and ... A: Try this: Private Sub cmd_Mailmerge_error_Click() Dim retVar as variant retVar = ...
MS access program accessibility	4/24/2008
Q: I want to make a MS access program for projects management. Plz I want to ask three questions : ... A: Hussein, hi, My recommendation is that you consider using MS Project Enterprise Edition supported ...
To get the username or ip address on load or click event	4/24/2008
Q: I created MS access program using on my office network with 5 computers and I don't want to create a ... A: Use the code below in your client program to pull the UserName and ComputerName. With that you can ...
Not Allowed to Edit - No reason why.	4/23/2008
Q: I am making quite a complex database (well it seems that way anyway) and i have come accross a ... A: The best way is to break out individual tables into sub-forms, nested on the main form with a linked ...
Not Allowed to Edit - No reason why.	4/23/2008
Q: I am making quite a complex database (well it seems that way anyway) and i have come accross a ... A: Chris, hi, Its probably because the query itself is unupdateable. Try opening the query directly, ...
Can't exit loop	4/21/2008
Q: I really appreciated it. I have another problem I am hoping you can help with. I have two ... A: Alan, hi Why are you using recordsets when you can do this easily with a query ???? (I doo need ...
Move Text Data To Next Empty Row In Column	4/20/2008
Q: I am importing a 23 character text string in to my Form text box (from a RFID scanner). I need to ... A: On your form you need a button to save the record: the easiest way is to open the form in design ...
File Transfer Message	4/18/2008
Q: After Access has completed a file transfer can I build in a message to advise that the action has ... A: Just wrap the FT into a macro or VBA procedure, and follow the FT command with a MsgBox() function ...
login form with access rights check	4/17/2008
Q: i have two forms, Form A to login and enter Form B (provided that the logging in credentials are ... A: I'll start 1/2 way down... 'Check value of password in tblEmployees to see if this matches value ...
changing delimiter	4/17/2008
Q: I hope all is well. I've tried several things but have hit a wall figuring this out. Your assistance ... A: I have lifted this off the Net - so if you can get it to work, brill: if not well, I tried.... 'Use ...
MS Access Query Help	4/16/2008
Q: I've designed a database and I need help creating a query for it. In the database, there are dates ... A: Lets say your starting query is as follows: SELECT aa, bb, MyDate FROM MyTable Notes: The table is ...
Question on pages linking to tables...	4/15/2008
Q: This one is simple i believe... I've read over many help topics but can't seem to figure it out. I ... A: You'll need to develop an interactive website using ASP (for instance) on a IIS Web Server that can ...
How to create a form with an insert record function button.	4/14/2008
Q: I need a method that will allow me to insert a blank record in the middle of a table through a ... A: Rami, Hi To inser a new record for data entry, either use the '*' button in the form navigational ...
Field box automatically adding other fields together...	4/14/2008
Q: I am building a db for a quality tracker. There will be say a set of fields, or questions if you ... A: Aaron Hi, Lets assume your 4 text boxes with values are called:- txtValue1 txtValue2 txtValue3 ...
VBA Code	4/6/2008
Q: I cannot quite find the right syntax for this code to append a new record in one table with data ... A: Why are you using recordsets for this basic append rather than SQL ??? Use the Access Query Builder ...
automatic changing date	4/5/2008
Q: I have a database with records in which I change things on a regular base. There are two dates in ... A: For each updateable object, you need an after update procedure along the lines of: Private Sub ...
change filename	4/5/2008
Q: I need to import a delimited file into access. The file name is 3-30.txt.err. I need to get rid of ... A: Alan, hi Use the FileSystemObject: Look this up in onboard help, but here is a snippet base on a ...
Filter Form Button on Switchboard	4/4/2008
Q: I'm still an Access Newbie but I'm managed to create a simple "contacts" database for my job. I'm ... A: Yes. Use the Wizard to generate the code for the button to open up your required form: open this up ...
Access, subform dependent combo box	4/4/2008
Q: I have read your advice regarding a combo box in a subform that is dependent on a combo box in the ... A: 1. Ensure that the sub-form holder object is named as "sfrmPOdetails", as it is this that is used in ...
Exporting to Txt and losing leading zeros	4/3/2008
Q: I have an MS Access database into which I've imported 2 data sources - a file from my system and a ... A: Try the following: = Right("000000000" & CStr(CLng([MyNum])),9) & Left(CStr(CDble([MyNum]) Mod 1) ...
recordset	4/3/2008
Q: I have a table with the foll fields; purchasedate,name(combo box),coupon(tea/lunch),amount. When a ... A: You could try a Dynamic recordset, which allows the MovePrevious action, or continue with Snapshot, ...
Exporting to Txt and losing leading zeros	4/2/2008
Q: I have an MS Access database into which I've imported 2 data sources - a file from my system and a ... A: Tina, hi, #1 - you need to drag it out from the header row. Use the mouse, click one side of a ...
Basic Delete Query	4/1/2008
Q: Sorry to bother you again, your response worked last time, so i figured you'd be the one to ask ... A: Peter, hi, You'll need to run the following queries: the last one needs to be run last (if you want ...
Exporting to Txt and losing leading zeros	4/1/2008
Q: I have an MS Access database into which I've imported 2 data sources - a file from my system and a ... A: You need to explicitly declare the "000000123450000" as a Text/String I am not sure how you are ...
Very Basic Query Question	3/31/2008
Q: I am new to access; I currently have a table within my database that contains a field called ... A: Two ways:- Method #1 There is a function called "Year(YourDate)" which returns the Year based on a ...
Copying a textbox value, adding it to a table	3/30/2008
Q: This is what i am trying to do: I am trying to copy the value in a textbox, possibly the first 10 ... A: Open the Query Builder in Access, in a new query. Right mouse button, select SQL view - a text area ...
Copying a textbox value, adding it to a table	3/29/2008
Q: This is what i am trying to do: I am trying to copy the value in a textbox, possibly the first 10 ... A: Chris, hi All you need to do is to run a query to do this UPDATE. I will assume the following table ...
separate Fname and LName in a field	3/28/2008
Q: I have a field in which the data has first and last name separated by a comma. How do I separate ... A: Let sTmp = "Smith, John" LName = Left(sTmp, InStr(sTmp, ",") - 1) FName = Right(sTmp, Len(sTmp) - ...
Forms	3/27/2008
Q: I have created an inventory program for the not-for-profit company that i do volunteer work for. I ... A: I assume you have two tables anyway (one for the order header and one for the Order Line) Build ...
ADP	3/27/2008
Q: I wonder how I can print data from a field of table in a row like value1,value2,value3..... not in a ... A: First I built a function to return a string comprising all the columns of a particular table. In my ...
Filters	3/26/2008
Q: I have got a products table that holds products for different customers, for example, ProductID 3000 ... A: Peter Hi, You need a look-up to define the relationship between ProductID and ClientID - this data ...
Forms	3/26/2008
Q: I have created an inventory program for the not-for-profit company that i do volunteer work for. I ... A: You could just have a textbox for each line item, into which you type in the account number for ...
Copy multiple records with vba/sql	3/25/2008
Q: Is it possible to copy more records by with a sql string and DoCmd.RunSql in one action? kind ... A: As long as you can define which records you want to 'SELECT', and your tables contain data in a ...
sql count	3/25/2008
Q: I have a form that simulates the generation of random numbers, so that evaluators can evaluate ... A: Simone, hi Your question is only about 60% clear... I can see there is something wrong with your ...
Linking pictures to access 2007	3/24/2008
Q: I am linking pictures to forms using the code: Private Sub Form_Current() Me![Photos].SetFocus If ... A: Peter hi, Try this: Private Sub Form_Current() If Len(Me![Photos]) > 0 Then ...
Beginner access question	3/21/2008
Q: I am new to access; and have been having trouble with an append query. I wish to run the append ... A: Peter, Hi If I didn't say it before, I'll say it now - good database design is the foundation of ...
Beginner access question	3/21/2008
Q: I am new to access; and have been having trouble with an append query. I wish to run the append ... A: Peter, hi, In the macro you will need two queries - the first one copies the records from the main ...
Beginner access question	3/20/2008
Q: I am new to access; and have been having trouble with an append query. I wish to run the append ... A: Peter, hi, The functionality you mention should be located in a form (or other code) and NOT on the ...
Incremental count	3/20/2008
Q: I have an query with different people sort by name and by date. So it looks like this : John ... A: I built a table speced as follows tblNames Name - Text DoB - DateTime using your data, this query ...
Macros and SQL	3/19/2008
Q: This project using MS Access is based around recording attendance of members of a club, who meet on ... A: Andrew, Hi In relational database solutions, we design and build the tables first, then use forms ...
Access Query	3/19/2008
Q: I am currently working on setting up a simple Access database to hold data for a Bridge (the card ... A: Success will depend totally on your database design. SQL - the language that relational databases ...
Current date update	2/16/2008
Q: Geoff- First, this is my first time trying to work with Access. So, I'm an extreme access novice. ... A: For info here's the code: QUOTE Private Sub cbxSurvey_AfterUpdate() If Me.cbxSurvey= -1 Then ...
memeo field ms access byte variable	2/16/2008
Q: good day, sir i just want to ask you something, this has been my question to myself from the past ... A: Overhead space is storage space taken up by the application to manage itself. Access has its own ...
memeo field ms access byte variable	2/15/2008
Q: good day, sir i just want to ask you something, this has been my question to myself from the past ... A: Memo fields expand to the space required. So if you store 'ABC', only space for 'ABC' is consumed ...
query	2/15/2008
Q: I want to select a query which may return quite a few records. If I want to see only the last 5 ... A: What you are asking is for a report, not a query, and to deliver this, I would build a form ...
Text File > MS Access > Macro - All in Macro	2/14/2008
Q: I need to develop a excel macro, in which I would be posting the contents of a few text files to MS ... A: Gaurav Hi I enclose a reference I have that may help... Best regards Geoff :-) QUOTE Set ...
Expressions Builder	2/13/2008
Q: Q1:- I created a custom (global) function that isn't showing up in my expression builder. Any Ideas ... A: Q1 - Sorry no ideas... Q2 - Often it is a question of database design. From what you have ...
Text File > MS Access > Macro - All in Macro	2/13/2008
Q: I need to develop a excel macro, in which I would be posting the contents of a few text files to MS ... A: Gaurav, Hello, Strategy: I would build the procedure to upload from the text files into Access ...
Lookup on MS access	2/12/2008
Q: Im doing a data base on UCAS applications and i want to be able to create a student code. This has ... A: Probably most flexible to have a function where you send the FName and LName and it returns the ...
starting a database	2/12/2008
Q: I need to create a database for a valeting company and although i am okay with Excel, Access causes ... A: Paul, hi, Its all in the design of the database..... Relational databases, require that each type ...
Storing a 2D array into MS Access	2/10/2008
Q: I've this 2D array in Visual Studio 2005, I've created the connection to MS Access and now want to ... A: Why not set up a new table to take the data:- tblMatrix RowID Numeric - LONG ColID Numeric - LONG ...
Create Simple Report in Access2007	2/9/2008
Q: Create Simple Report in Access2007 Purpose: List ORDERS and ITEMS on the same line. ... A: I think the best way is via a function that returns the list as a string, then use this function in ...
percentage query	2/8/2008
Q: I am relatively new to MS Access and what seems to be a simple task is really annoying me because I ... A: Chris, You could use a feature of the Checkbox to your advantage here... Lets say your table PK is ...
MS ACCESS query	2/8/2008
Q: Q1 == have a table with 2 columns. I need a query to copy individual elements of both column into a ... A: Muhammad, hi Q1 When you say Column A has 1,2,3,6,8 - do you mean each record has a single number ...
QUERY	2/7/2008
Q: I have a table Reseller, Owner, and Manufacturer they are identical to the table Business Entity. ... A: Tana, hi I have 2 questions for you:- 1. Why are you copying data from one table to another ? The ...
Merge fields from Access to Word	2/7/2008
Q: We have recently updated our Access database to include new fields, but when I try to merge them ... A: Dave, This is off my area of expertise, but, I imagine you will need to reconfigure the Access ...
Import/Export solution needed	2/6/2008
Q: I revised an MS Access 2002 mdb that contains 541 records in a people table. A user inadvertently ... A: Kae, hi, You will not be able to control the Autonumber column of the master table (541 records). ...
Query	2/6/2008
Q: I am trying to Create a query to total all the entries on a table according to each inividual id. ... A: In query builder, you need to expose the 'aggregate' view - from the menu, select 'View', then ...
combine a table info with form input into to a new table	2/5/2008
Q: i have different(6)tables set up as test questions. i have a form 'template' that i can pull in the ... A: Justin, hi, It is not clear in your description how your data is structured - ie. the database ...
MS Access 2003 Field List	2/4/2008
Q: I created a form with an imbedded subform using the forms wizard. I have since added fields to the ... A: Al, hi Each form has a query behind it which has the 'fields' selected. So the fix is to extend ...
Embedded macros	1/31/2008
Q: I've created a db for a client using Access 2007, but saved it as .mdb format because they have ... A: My only suggetsion is to take the original Access 2007 version, and save it as Access 2003 (menu ...
Multiple search criteria	1/31/2008
Q: I have created a search form where i have placed four fields, namely: "Make", "Ref No", "Begin ... A: The professional way would be to build a filter string, then apply that to the form based on all the ...
Query within Access	1/30/2008
Q: I have a date range, [start_date] and [end_date], which sets up a resources allocation to a project. ... A: Your question is potentially not an insignificant challenge. Some questions: 1. Table RESOURCE does ...
Query button on switchboard	1/30/2008
Q: I need to place a new button on a switchboard to use it to open a form named "SEARCH." Unfortunately ... A: You need to enter some code on the button's "Click" event so that it opens the form. This is the ...
Forms	1/29/2008
Q: I have a table on my db called nationality which is built up of two fields; 1) nation_code 2) ... A: You can rebuild the combo to work with both the nation code as the "bound column" and the Nation ...
Unbound OLE object based on combo box	1/29/2008
Q: I have an Access 2003 db with tables for region, staff, and correspondence. tblRegion has a key and ... A: Try the following: 1. Use your query to power a header(main) form, then use a sub-form based purely ...
Variant multiple rows per record	1/28/2008
Q: I need to find a way to import a file that has multiple rows per record. The number of rows may ... A: Elaine, You will need to write a VBA script to read the text file, parse the contents (applying ...
Access 03 query	1/26/2008
Q: Upon entering payments received in a form, I am trying to make a query that will adjust the current ... A: Bill, To calculate totals on the fly, you can use the DSum() function - look this up in onboard ...
relationship between form and subform	1/25/2008
Q: I have a main form (stud_details) also I have a subform called (pay_det_sub). I want to create a ... A: Jamil Hi, I always recommend building the database using the in built referential integrity system ...
Access 03 query	1/25/2008
Q: Upon entering payments received in a form, I am trying to make a query that will adjust the current ... A: Bill hi, Depends how your system works... Most accounting systems use transactions, and the ...
Forms	1/24/2008
Q: I have two forms, main form is called stu_details and second form (look-up form) called ... A: Use the afterUpdate event of the combo on form #2 the active command is as follows:- ...
store form calculations into table	1/24/2008
Q: Good day, I have a simple but large database to calculate the trade and selling price as well as ... A: Joh hi, You dont need macros or VBA You build an update query and run that. Below is a query in ...
Forms	1/22/2008
Q: Hallo, I have a form (Qual_hist) with a field named Institution1. Also, I have a table with all ... A: Build a Combo Box control, based on the 'school' table. To hide the school_code from the drop-down, ...
Forms	1/21/2008
Q: hallo, I have two forms, from one form I can navigate to the other form. What I want to do is ... A: Jamil, Hi Insert some VBA on the 'Click' event of the form1 button to do the following: 1) Goto a ...
Date update	1/19/2008
Q: Forgive the novice question. I need to have the date inserted into a field on the check of a check ... A: I will assume that the respective controls are called cbxSurvey (Checkbox) and txtSurveyDate ...
counting characters in a query	1/4/2008
Q: Happy or should I say Hopeful New Year!! I have a looped qry that has "~" characters. I need to ... A: Alan, hi, I don't fully understand your code and how it relates to the question... You can use the ...
Generate e-mail from Access	1/3/2008
Q: I have the task of monitoring e-mail traffic. I use Microsoft's LogParser to extract the data ... A: Mike Hi, Below is a procedure for sending email - if you are working with Outlook2000 or later, ...
Form Data	1/3/2008
Q: I have a database which we are trying to access using forms. Basically allowing us to enter the ... A: Probably the easiest solution is to use the 'Binoculars' button from the form viewing toolbar, ...
Sorting records by date entered	12/19/2007
Q: when I enter the records into my Access database, will they sort out by the dates I enter or will ... A: If you have not entered a sequence number/string that defines the order of entry, then there is no ...
photo's linked in report	12/18/2007
Q: I have probably a very simple question, but I cant find the answer. I have a pretty extensive ... A: Emiel, hi, Save the images in a single folder, eg: C:\Photos\.. In your DB table have a column to ...
MS Access Button Function	12/17/2007
Q: I placed one text box and two buttons on a form, When i Click the first button it add the text of ... A: Sher, Hi, In the VBA code, just insert the following statement before the UPDATE.... SQL ...
locations in inventory control	12/16/2007
Q: i want to thank in advance for any help i have these tables tblproduct :- productID,ProductName , ... A: Exactly the same principle, except that you use a recordset to control the stepping through of each ...
locations in inventory control	12/15/2007
Q: i want to thank in advance for any help i have these tables tblproduct :- productID,ProductName , ... A: 1. PO Form ---------- You could have a button on your PO form with the following 'Click' event ...
Performing calculations	12/15/2007
Q: Hey, I have made a table to record my container details. Here it is: ... A: Jaap, Hi, I would tend to leave validation rules aside until some other day - I hardly ever use ...
Access Report	12/14/2007
Q: I have an inventory report that I have summed dollar amount by part number. If the sum is <1000 I ... A: Buid a query that computes the inventory sum (if required) then constrains only those parts where ...
Form Filtering	12/13/2007
Q: It all works well except for when a users selects a combination of filters that have no records. ... A: Try using the DCount() function and pass the filter string to it for determination. As an example, ...
Code modification	12/13/2007
Q: Hallo, I have a main form called StudentDetails1, and a subform called FeeDetails and a field ... A: Jamil, Hi Your method seems a bit 'different': why don't you have a combo box on ...
Re: Date/Time	12/5/2007
Q: Prior to the previous question, the date and time is now populated successfully when a field is ... A: Here is some code to validate a textbox change and restore the previous value if not accepted ...
TAB Order	12/3/2007
Q: A previous reader has the same problem which I tried your subjections and still having problems. I ... A: I have built many applications with nested form configurations, and can say I have never encountered ...
counting loops	12/2/2007
Q: Your expertise is greatly appreciated. I am about to lose my mind. I use the following code to loop ... A: Alan, hi, I am not sure what you are trying to achieve from your code, could you please supply some ...
Sorted by date	12/1/2007
Q: I am building a database in which I entered customer info in order of the dates I served them. Now, ... A: Sy, If its only 50, just retype them... If its 50,000 then use a query like.. UPDATE tblMyTable ...
Tab Order within a subform	11/30/2007
Q: I have a form with a subform embedded. No problem with the between master and child fields etc. ... A: Jill, hi, 2 things to check: 1// Open the form/subform in design mode. Select the form object (top ...
Access Import Specification - Macro	11/27/2007
Q: 1) Delete the old version of a table 2) Import a .txt file, tab delimited. I have created the ... A: Paul, hi, I appreciate your frustration - I have gone through this many times and sometimes you ...
changing control's forecolor using Timer	11/26/2007
Q: Good Morning Geoff, This time my question is: How to change the forecolor of text in a Label using ... A: Sub Form_Timer If Me.Label.Forecolor=0 Then 'Black Me.Label.ForeColor = ...
changing fields into records	11/24/2007
Q: Hi How would I change fields in table1 into records in a new table2? field1 field 2 field3 ... A: Just to confirm the conversion Current Table F1 F2 F3 F4 Abc Fgd Ytwg TWB ...
Problem with MS Access	11/23/2007
Q: I am using Access 2002. I have two tables. In the first table, among other fields there is a field ... A: Open the form in design mode select the listbox right mouse button select properties select the Data ...
help to write VBA code	11/23/2007
Q: i have a form(frm_progress) on which there is a label which says "Opening File. Please wait....". i ... A: Rajiv Hi In the click event of the button on the main form, just wrap the Excel commands with the ...
declaring variable in access having ref to excel pivot item	11/22/2007
Q: pls help me with a code by which i can declare a variable in Access having reference to a pivot ... A: Rajiv, hi, Herewith, code to open Excel and get a value Public sVar As String 'Declared in the ...
Help with Access Query	11/20/2007
Q: I am trying to set up a query that looks for the lowest price from 3 tables these are; Price File ... A: Christopher, Hi I will assume that each table has a column called ProductID a column called Price, ...
Transfer text specifications	11/19/2007
Q: How do i create the transfer text specifications.Also how to import the file manually so that i can ... A: Anshul, hi, Create import specifications by manually linking a template text file. In the Table ...
Last Update Date	11/18/2007
Q: I know there are past posts on this but the code I am using is not working and I have tried various ... A: Steve, I assume there is a textbox control on the form named 'update_date'. The form's ...
MS Access queries	11/18/2007
Q: Sometime ago I asked about deleting duplicate records. I havn't been able to get any success and ... A: Alan, hi, 1) the table needs to have a column or column combination that is unique - these ...
forms subforms and referential integrity	11/17/2007
Q: I made a Main form with subform link to 2 tables that have a relationship 1 to many with referential ... A: You need to create a formal relationship between the two tables. To do this - menu - Tools / ...
Date criteria	11/17/2007
Q: In a query, i want to insert a date criteria for after the 1st January 2000. I've been trying this ... A: Rahmat hi, Assuming the field is a 'Date' datatype, then >#01/01/2000# If you are using the ...
QBE and ROW&COLUMN data table	11/9/2007
Q: First of all i'd like to say thank you for this kind of ASK-ME-ACCESS webpage :) I'm creating a ... A: Wojciech hi, The problem rests in the fact that the data is not in the Third Normal Form. Until the ...
Access query	11/4/2007
Q: I am a basic Access designer and need a basic answer please: I have a table with tasks in it. Each ... A: Try a little smoke & mirrors !!! The function Date() - abbreviated by vba to Date returns the ...
calculated fields	11/3/2007
Q: Ok, i cant save the result from a calculated field into a table, then i have any other option ... A: I will choose the Form method Assuming your form is called frmMain and the seven textboxes called ...
calculated fields again	10/27/2007
Q: Ok, i cant save the result from a calculated field into a table, then i have any other option ... A: If you need to save calculated results, eg. a sale was made on a day at a specific price.. you can ...
msgbox	10/20/2007
Q: i want to add some code to the code below to display a msg box "Please Complete checklist" if ... A: Try the following snippet for a checkbox... If Not Me.Check325 Then MsgBox "Complete the ...
opening pdf with code	10/14/2007
Q: Wondering if you can help me. In Access, Excel and VB6 if I want to open a file I can use the ... A: Eric, Hi, You could make the Acrobat exe path/file a configured variable that is held in a table, ...
MS Access Combo Boxes	10/13/2007
Q: I'm stuck. I have a Main form which has at the top a combo box that reads a table. There are 6 ... A: Nearly there... cboTemplate_AfterUpdate() Select Case Me.CboTemplate.Value Case "Flash ...
Report filtering and referencing images	9/25/2007
Q: a very good friend of mine constructed a small database for my letting agency. It logs all the ... A: Lynda, hi, From your submission, I believe you need to base the report from a Query and not the ...
Generate Report Based On Input Criteria	9/24/2007
Q: The HR peeps here at work are having some trouble getting certain reports out of our Access ... A: Jeremy, hi, The basic command for opening a report from a button (say) is as follows: ...
Report filtering and referencing images	9/24/2007
Q: a very good friend of mine constructed a small database for my letting agency. It logs all the ... A: Lynda Hi, In your report, you need to place an 'Image' object in the detail section - in the ...
vba search and replace	9/23/2007
Q: Geoff: Would you mind giving me the vba for the following: I have an Access 2002 database which ... A: Rodney, Hi, You don't specify what is the source data for replacement: are the Btrieve tables the ...
Multiple updates in one Access SQL statement	9/23/2007
Q: How can I update a single field in an Access table based on testing two conditions; ie. Update ... A: Jerry, hi, In SQL Server you could use a CASE / WHEN construct inside an UPDATE SQL statement based ...
Year Format in Access	9/22/2007
Q: We in India use April to March as Financial year but the Access Database Sorts from January to ... A: Subhash, hi, It's the same problem world over - you need to set up fiscal period columns in a ...
Report filtering and referencing images	9/22/2007
Q: a very good friend of mine constructed a small database for my letting agency. It logs all the ... A: 1. open your form in design view - select the button - right click & select Properties. Select the ...
MS Access Security	9/21/2007
Q: how do I secure the MS Access database so that certain users can only read the information and not ... A: , Security is not an easy 1 line answer - so its all or nothing. If you want the 'All' then ...
Extracting the right price for stocking	9/21/2007
Q: I have a problem on extracting the right prices of a product. Hope you can help. The price of a ... A: Tiago, hi You need to maintain the "AveragePrice" per product, and when you sell product you should ...
Automatically Run MS ACCESS from desktop	9/20/2007
Q: I have developed a small application in MS acccess by which we we have to analyse the data(Tickets ... A: Saurabh, hi Two ways: One, use the auto run macro (on Access load) - Inside Access set up the ...
access query	9/18/2007
Q: "Create ONE query which will:- Ask for and accept a maximum price from the user, with the cue ... A: Ugur, hi Insert the question in square brackets [] in the Query tool on the Con