Using MS Access - Business Software - Computing/Technology

	AllExperts > Using MS Access
	Search

About Scottgem
(Top Expert on this page)

Expertise
I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience
I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

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

Questions Answered By Expert Scottgem
In Category Using MS Access

Subject	Date Asked

MS Access If Function	11/21/2009
Q: I run a report on Monday, but display Tuesday in unbound text frame in the report header using a VBA ... A: =Format(Date() + Choose(Weekday(Date(),vbMonday),1,1,1,1,3,2,1),"dddd") To test this Substiture a ...
Time Calculation Using Access Query	11/21/2009
Q: again i need your help regarding calculating difference of time in ms access query. i have a table ... A: First, you need to understand how Access stores DateTime values. Access stores date/time values as a ...
MS Access AutoNumber feature	11/20/2009
Q: I have a database that contains two tables (Activate %26 Wirelist) with a "auto-number" column (WID) ... A: If I follow you, you have two tables in one to many relationship. What's not clear is which is the ...
How to connect a text field to combo box	11/20/2009
Q: "Yes", "No". There's a text box underneath it "Incident number". If user selects "Yes" I want the ... A: I would not use a combo in this case. I would use either an Option Group or a Check box. But the ...
To calculate gross,Net salary for an employee in Access	11/20/2009
Q: I need to calculate the net and gross salary for an employe in Ms Access..that to by writing the ... A: Depends on the structure of your database. The proper structure would be using three tables like ...
ACCESS - modify date	11/19/2009
Q: I've implemented the suggestion to add the date modified to a table that is changed via a form. But ... A: Users should NEVER interact directly with tables. All interaction with the data should be done ...
Search mode??	11/19/2009
Q: I'm new to Access 2007 and have a problem. I've created some tables, queries and reports. I was ... A: Well first you really shouldn't be entering data directly into tables. You should be using forms. ...
Donations Database	11/19/2009
Q: So what i did was create a couple of databases and made sure that nothing overlaps. Here are my ... A: Good start, but room for improvement. First, I recommend naming your PKs using the convention ...
Problems getting forms to work.	11/19/2009
Q: I am not totaly good with access. The problem is: I have many tables setup and working like I need ... A: First, you don't need another table. Vistors should be entered in your People table. You should have ...
Searching for a string in my access form that is made up of text and numbers.	11/19/2009
Q: e. "COMM-125" and other simple text strings? Here is my coding for it thus far: Option Compare ... A: The problem here is the value returned by cboSearchfield. As a rule its is not a good idea to put ...
coding vba	11/19/2009
Q: Please advice on this code What i am trying to do is i have a todo list, and promt me when there is ... A: So what you are trying to do is check periodically if a new task has been added and pop up a ...
Problems getting forms to work.	11/19/2009
Q: I am not totaly good with access. The problem is: I have many tables setup and working like I need ... A: I have one word for you: Subforms! You should find this blog of mine useful: ...
MS Access 2003, Filtering Data Using Forms	11/19/2009
Q: MS Access 2003, I have one table like this with 4 fields, AdmissionDate: AdmissionTime: Discharge ... A: Use the Filter By Form feature. Create a form in Tabular format using the Form Wizard. Then use the ...
Query for different records for identical data	11/19/2009
Q: I have a multi-record table of plant species canopy cover data for 1/10 acre plots/sample areas. ... A: I'm not clear what you want as a result. You can use the Query wizard to create a duplicates query. ...
how do i perform a Count(Distinct) query for 2 different column values using GROUP BY	11/18/2009
Q: I have this "source" table like this.... \|------------------------------------\| \|ROLLUP_ID \| ... A: Actually I don't see two columns using DISTINCT. You are Grouping By Rollup_ID. You are counting ...
Dollar per mile problem	11/18/2009
Q: I have two tables: Table: Petty Cash Register Fields: PCVDate, Vehicle (This is a lookup with ... A: Actually your problem is more on the back end. Since you are recording mileage BEFORE pumping gas, ...
Searching for a string in my access form that is made up of text and numbers.	11/18/2009
Q: e. "COMM-125" and other simple text strings? Here is my coding for it thus far: Option Compare ... A: You have the right idea, just the wrong execution. I don't understand why you are using the Replace ...
Form Parameter for Multiple Values	11/18/2009
Q: I have a form that has 1 list box to select multiple facilities name. That form is based on 1 query ... A: Yes it does work if done correctly. So the question is what about it doesn't work? Did you use the ...
Medical Research Study Database	11/18/2009
Q: I have searched google on trying to create a questionnaire type database. I have knowledge of Access ... A: First, no, you have one Answer field. What the QuestionType does is allow you to modify the form ...
Code for a botton	11/18/2009
Q: I have 2 tables; a table for student information and another one for their reports. 2 tables are ... A: Of course you can. A subform is just a form embedded onto a main form. You can do just about ...
No selectable user. Auto pull-up.	11/18/2009
Q: I am new to access. This might be the simplest question you might answer but for me it hasn't worked ... A: First, while I'm perfectly capable of writing VBA code (I wrote a book on it after all) I don't use ...
Entering Dates in a Form	11/18/2009
Q: MS Access 97 (company won't spring for upgrade). I have a date field that I would like users to be ... A: I'm not sure if this works in Access 97u (I believe it does), but if you have no input mask at all ...
Conversion of date stored as text to usable date format	11/18/2009
Q: This is my response to your answer of my first question. Both my first question and your answer are ... A: I gave you the answer, but you didn't answer an important part of my followup, namely how does the ...
Dollar per mile problem	11/18/2009
Q: I have two tables: Table: Petty Cash Register Fields: PCVDate, Vehicle (This is a lookup with ... A: Try doing this: Create a query that returns all records for the period but groups by vehicle and ...
Looking up a value in a table	11/18/2009
Q: I have a table, which I would like to add a column to and populate with data. This data would be ... A: You may have to do this in several passes. What you need to do is first create a SELECT query that ...
locking records in table/datasheet view	11/18/2009
Q: Multi users are entering data in a single table(datasheet view) which has many fields ... A: Users should NEVER interact directly with tables. By letting your users do so, you have created a ...
Access output to word table	11/18/2009
Q: I am using MS access database to store some employee information. Few employee's information are ... A: You can't. What you CAN do is copy and paste the table to an Excel spreadsheet. If the Word doc is ...
Databases	11/18/2009
Q: Mr. Gem, I am attempting to create a model for a student database... The details I'm concerned ... A: For the first issue you need a Curriculum table So BSC Software Engineering would be a curriculum. ...
Databases	11/18/2009
Q: Mr. Gem, I am attempting to create a model for a student database... The details I'm concerned ... A: You have a many to many relationship. That means you need a Join or Junction table to handle it. ...
Autopoulate with Value	11/17/2009
Q: I have a database whereas I track new building permits issued. In this database I have several ... A: I'm assuming that the Owner info is in the Permits table where there is also a Foreign Key for the ...
Form Parameter for Multiple Values	11/17/2009
Q: I have a form that has 1 list box to select multiple facilities name. That form is based on 1 query ... A: If you are selecting multiple items, you need to build a WHERE clause in code by looping through the ...
Adding a record to a subform when it's based on a query	11/17/2009
Q: Help... I wrote an Access 2003 app 2 years ago and all of a sudden, on October 29th, 2009, it ... A: Lets dissect this. "I have to calculate a percentage using the ACTUAL ATTENDED hours vs. EXPECTED ...
Code for a botton	11/17/2009
Q: I have 2 tables; a table for student information and another one for their reports. 2 tables are ... A: I wouldn't do it that way. Instead create a main form with a subform. The subform is bound to the ...
Sum Field in Reports	11/17/2009
Q: I have a report from a query, in which I have fields which are scores from 0-5. I need to be able ... A: Well, this first indicates that your database may not be designed properly. If you have multiple ...
display a MS word DOC on my form	11/17/2009
Q: I am using office 2007 My application have an employee table %26 each employee has a word document ... A: You can use an Unbound Object frame control. You would then add code in the On Current event of your ...
Access Autonumber reset every month or every year	11/17/2009
Q: I work in a admin office. And I need records from all the paper work that come and go. But to find ... A: An easy way to find this is to count up all the left brackets and all the right ones to make sure ...
query for access 2007	11/16/2009
Q: I am supposed to write a query which involves figuring out a rental rate with a 15% discount and I ... A: Yes this does make sense, but in another way it doesn't. First, let me explain that I generally do ...
Adding a record to a subform when it's based on a query	11/16/2009
Q: Help... I wrote an Access 2003 app 2 years ago and all of a sudden, on October 29th, 2009, it ... A: First I wouldn't laugh at you code. There are a lot of people who get thrown into a position and ...
Changing multiple queries' SQLs	11/16/2009
Q: I am running Microsoft Access 2003 on an Windows XP operating system. The script I'm trying to use ... A: Method 3 is essentially what you do now, without opening the query. However, what do you mean by ...
Changing multiple queries' SQLs	11/16/2009
Q: I am running Microsoft Access 2003 on an Windows XP operating system. The script I'm trying to use ... A: I think you may be misunderstanding something here. If you are using a SELECT query there is no need ...
MS Access- Validation Rules	11/16/2009
Q: I´m using Access 2003 and i have a probem in expressing a conditional validation rule. I want the ... A: You can't do this with Validation rules. You need to use VBA. You don't have fields on a form, you ...
IF AND conditions	11/16/2009
Q: I am trying to write a condition so that If a feild name [Order Date]<=Date()-14 And a field name ... A: If you are having trouble with a piece of code, please show me the code, the line that is causing ...
MS Report Design	11/15/2009
Q: Is there a way to design an Access report such that if there is no data for a particular record, the ... A: I suspect the problem here is your database structure. Structured properly this should not be a ...
Check if server active and primary	11/14/2009
Q: This one seems to be causing a major headache for the project i'm currently a part of. We are ... A: Are you connecting using ODBC DSNs? If so, just run the fReconnectODBC() when the app is opened. ...
Access Autonumber reset every month or every year	11/14/2009
Q: I work in a admin office. And I need records from all the paper work that come and go. But to find ... A: I question the need for this, since you can filter records by year. But its easy to do and I have ...
auto word for invoce id	11/13/2009
Q: i would like to create a auto word and number that occrues for my invoice. how would i do it so ... A: OMG! Who ever told you that you know how to design a databasem especially to charge money for it! ...
Time Duration	11/13/2009
Q: I created a table for the Dispatch Office here. I have a field for Time of Call and a field for ... A: Yes that is what I needed. In that case its a simple subtraction: =[Time of Return]-[Time of ...
Conversion of date stored as text to usable date format	11/13/2009
Q: I have a “Date_Time” field in an Access 2007 table that contains date/time info (example 10/31/2009 ... A: First, if a field is listed as a Date/Time datatype, then, while it can be displayed in a text ...
Time Duration	11/13/2009
Q: I created a table for the Dispatch Office here. I have a field for Time of Call and a field for ... A: Sorry, but you are approaching this from the wrong angle. Its a common mistake made by people ...
Medical Research Study Database	11/13/2009
Q: I have searched google on trying to create a questionnaire type database. I have knowledge of Access ... A: This is a fairly frequently asked question which I've answered several times. I say this because had ...
Access calculated field	11/12/2009
Q: Scott, Wouldn't let me ask another follow-up. Thanks much, but one more issue on this: Answer: ... A: If Me.PreOpValue < 1 Then Me.PreOpValue.Format = "Percent" Else Me.PreOpValue.Format = "General" ...
auto word for invoce id	11/12/2009
Q: i would like to create a auto word and number that occrues for my invoice. how would i do it so ... A: So, let me get this straight. You were employed by someone to create a database where you clearly ...
subform links	11/12/2009
Q: I have 2 subforms on my form, the 1st is a list of components required to build an assembly which ... A: Yes you are using subforms incorrectly. Also your form is designed incorrectly. You say the main ...
Calculated field Miscalculating	11/12/2009
Q: I am working a query that is developed between a Table (Budget) with fixed terms and another query ... A: Build the query using Query Design mode. Don't just enter the SQL. I typed the SQL off the top of my ...
subform links	11/12/2009
Q: I have 2 subforms on my form, the 1st is a list of components required to build an assembly which ... A: Sounds to me like you may be using subforms incorrectly. What is the main form bound to? Is the ...
Calculated field Miscalculating	11/12/2009
Q: I am working a query that is developed between a Table (Budget) with fixed terms and another query ... A: Ok, first thing is you have joins that don't make sense. All your fields are being pulled from ...
auto word for invoce id	11/12/2009
Q: i would like to create a auto word and number that occrues for my invoice. how would i do it so ... A: First, this is not and should not be your primary key. You should use an Autonumber as your PK, and ...
Access 2007	11/11/2009
Q: On an Access form, I need a calculated field that returns the result to the table. I am trying to ... A: OK, first To be able to do this right, you have to make sure that both the numerator and denominator ...
Access 2007	11/11/2009
Q: On an Access form, I need a calculated field that returns the result to the table. I am trying to ... A: Sorry, I thought you could set format options that way as well. In the After Update event, after ...
Access 2007	11/11/2009
Q: On an Access form, I need a calculated field that returns the result to the table. I am trying to ... A: First, as a general rule we do not store calculated values. However, if I understand you correctly, ...
A2007 Report no data.	11/11/2009
Q: I have 2 tables in a report, one for contact names, the other table containing when they were last ... A: It might have helped if you showed me the SQL of the report's Recordsource. But I suspect the ...
Calculating amount of time used in hours and minutes, based on time completed.	11/11/2009
Q: Calculating amount of time used in hours and minutes, based on time completed. I need to calculated ... A: You need to do this with a custom function. Access stores time as a decimal number where the integer ...
conditinal format based on variable	11/10/2009
Q: Good morning. I have one problem. I need to apply conditional formal on field "result" with ... A: First, you don't apply conditional formatting to a field. You apply it to a control either on a form ...
Question about Combo Box Values	11/9/2009
Q: You answered 1 part of the question with a post from 2006 This is the Answer you provided. - I ... A: Why are you using Append and Delete queries? Just use an Update query and your problem goes away. ...
Creating 3 levels of Table of Contents	11/9/2009
Q: I have been assigned to make a ToC at my workplace. We have MS Access Database and working on a ... A: I can't see any easy way to generate a TOC in Access. What I would suggest is that you use a Master ...
Retrieval of Records based on the Name in login Form	11/9/2009
Q: I am new to MS Access. I am creating a database for my personal use. I have a main logon form where ... A: First, if you are creating a database for "personal use", then why would you need to have a logon. ...
Access 2007 Contact Management DB	11/9/2009
Q: I have downloaded the Contact Management DB and want to add a few things. I am a bit of a novice. ... A: Use subforms. When you create a subform using the subform wizard, it automatically establishes a ...
Access 2007 Contact Management DB	11/8/2009
Q: I have downloaded the Contact Management DB and want to add a few things. I am a bit of a novice. ... A: First, you don't define data by using field names. Adding checkboxes for interests is entirely the ...
Farm Database http://en.allexperts.com/q/Using-MS-Access-1440/2009/9/Laying-Database.htm	11/8/2009
Q: How did the farm database turnout from the posting on this webpage: ... A: There was no template from that thread. There was only the original question and my response. If ...
MS Access creating a multi-select list box	11/8/2009
Q: On an Access form I have created a list box. In the properties I have selected Multi-Select to ... A: The problem here is you shouldn't be putting all these selections in a single field. If you have ...
select a record from combo box	11/8/2009
Q: I have only one record in my combo box , I want on enter to select the said record and display it on ... A: No, you don't. Since this is a calculated value, use a DLookup. Set The Controlsource of of a TEXT ...
Find Text File	11/8/2009
Q: My issue is that I have a list of files (I say they are batches) in text format in a particular ... A: Its possible. But would take some significant VBA coding. You would have to loop through the files, ...
Textbox in an Update Form	11/6/2009
Q: I have 2 text boxes in my updatable form that I want ask about. 1st one is "Register Date" and the ... A: You still are making this clear. what you are saying doesn't sound logical. If you are saying that ...
make table & checkbox controls	11/6/2009
Q: I have a contract database that lists contract numbers, sections, and divisions (checkboxes). A ... A: First, your database is not normalized properly. By having fields for each division number you have ...
Microsoft Access 2007 Form Editing	11/6/2009
Q: I am trying to edit the borders on my forms. Normally when it pops up, the whole page is showing. I ... A: Access 2007 has two modes for displaying documents (forms, reports, queries, etc.); Tabbed documents ...
Find Record Text Box Entry	11/6/2009
Q: I have access 2007 and I would like to creat text box entry on the switchboard that would allow ... A: Very good. You referred to a document number, so I assumed it was a number datatype. I still think ...
New Project added to old	11/6/2009
Q: Good morning Scott. I have a database that is complete in Access 2003. My boss wants to add a new ... A: I suggest you read up on Normalization. Customer name should exist in ONE table and ONE table only, ...
Find Record Text Box Entry	11/6/2009
Q: I have access 2007 and I would like to creat text box entry on the switchboard that would allow ... A: First, you shouldn't use the Click event of the Text box, I said to use the After Update event. ...
New Project added to old	11/6/2009
Q: Good morning Scott. I have a database that is complete in Access 2003. My boss wants to add a new ... A: You should never use tablenames (or even fieldnames) to define data. If your database had been ...
Find Record Text Box Entry	11/6/2009
Q: I have access 2007 and I would like to creat text box entry on the switchboard that would allow ... A: Frankly I wouldn't do it that way, but you can by using the After Update event of the textbox. Use ...
Columns in MS Access Form	11/6/2009
Q: I have an assignment for my computer science class - we created a database in Access, and we have to ... A: I suspected you are using 2007. You have two choices here. 1) Redo the form using the Form wizard ...
Textbox in an Update Form	11/6/2009
Q: I have 2 text boxes in my updatable form that I want ask about. 1st one is "Register Date" and the ... A: First, the condition will never be true. Now() changes every tenth of a second. Second, I'm not ...
Filtering Report by Form	11/6/2009
Q: I am using a form to filter a report. On the form there are two unbound combo boxes to filter the ... A: If Not IsNull(ctl.Value) Then If sqlString <> "" Then sql String = sqlSttring & " AND " End If ...
Link - Table & Form	11/6/2009
Q: I created a update form for my table. In the form, I created a text box (client's name) which ... A: A bound control is one where the ControlSource is set to a filed in the form's Recordsource. If, in ...
Access 2007 runtime and older versions of excel	11/6/2009
Q: I have an Access 2007 database with VBA code to transfer specific data to an excel spreadsheet ... A: Yes that is a different issue. If you are using Office automation then you need to include ...
Filtering Report by Form	11/6/2009
Q: I am using a form to filter a report. On the form there are two unbound combo boxes to filter the ... A: I'm not sure why you are using the code you showed. What you need is something like this: Private ...
Using an Access 2002/2003 database from a link on an expressions web intranet based system	11/5/2009
Q: I have generated a link on the intranet site that is using a full UDC path. The database that I am ... A: I think you mean UNC path which is: \\servername\sharename\foldername\filename Is that what they ...
SQL Table Structure to Access	11/5/2009
Q: I am trying to assist a medical mon-profit that supports kids africa by building a database to ... A: Frankly, from what you are showing me, I would not try to recreate this schema since its not ...
Columns in MS Access Form	11/5/2009
Q: I have an assignment for my computer science class - we created a database in Access, and we have to ... A: What version of Access are you working in? Also, you don't put "fields" on a form. You place ...
On dbl click open form where combobox value is equal clicked value	11/5/2009
Q: when a project name in my continous form "Open Opportunities list" is double clicked another form ... A: You seem to have misunderstood my solution. The problem is that you are not using the WHERE clause ...
Link - Table & Form	11/5/2009
Q: I created a update form for my table. In the form, I created a text box (client's name) which ... A: Sounds like you created a "search" combo using the wizard. This creates an UNBOUND control so any ...
Populate field in subform using combo box	11/5/2009
Q: I am trying to redesign a current database using MS Access 2007. I have data from 3 tables ... A: I can understand showing units, but wouldn't the user be entering the quantity? I'm assuming here ...
input data	11/4/2009
Q: I have create text box on my access form. This text box allows users to input numeric data such as ... A: Ok, so I imagine the Rowsource of that Combobox looks something like this: SELECT CountryID, ...
SIngle field entry for " what re you looking for" that will search 6 fields in database to find values that comntain parts of the info and return the records	11/4/2009
Q: I have a very simple database that has a table that contains 10 fields. I would like to set up a ... A: The easier way would require a redesign of your database. If you have data fields in your table that ...
setting out	11/4/2009
Q: can you please guide me on the necessary steps in setting out? A: Setting out what? The more you put into a question the better I can help you with it. Also please be ...
Generate Alphanumeric ID in Access Form	11/4/2009
Q: I found where Scottgem had helped another Access 2007 user with generating an alphanumeric ID ( ... A: Do you have a control on your form named txtLngAutoNo? Is it bound to the ContractNumber field or a ...
Generate Alphanumeric ID in Access Form	11/3/2009
Q: I found where Scottgem had helped another Access 2007 user with generating an alphanumeric ID ( ... A: It would help to see exactly what you have done. If I follow you are using a button to generate the ...
Multitable databases.	11/3/2009
Q: My question is regarding MS Access multitable databases. Could you please tell me when it is ... A: What you need to do is read up on Normalization. This is a key concept in the design of relational ...
ms Access	11/3/2009
Q: I am having a record in a table and would like to edit that record and save it as a new record and ... A: Copy and paste the record as a new record then edit it. Hope this helps, Scott<> Microsoft Access ...
report layout	11/3/2009
Q: I have set up a database containing information on the projects we conduct (title of project, start ... A: First, Do you have only one table? If you do your database is not normalized properly and that is a ...
Queries in a table	11/2/2009
Q: Scott, I have an "Events" table uses a separate "Companies" table for its company source (using ... A: You need to create a Top N of Group query. This article tells you how. You want to create the TOP 1 ...
IF/THEN statement	11/2/2009
Q: good day ok i know i made you crazy with me constantly asking questions but this time i got it ... A: Do you have a check box control named chkfamily on your form? If not, either rename the checkbox ...
access and expiry date	11/2/2009
Q: I am a beginner Access 2003 user, I create a subform showing employees in company but each employee ... A: Use Conditional formatting, look that up in Access Help for specifics on how to use it. Hope this ...
MS Access Cross Tab Query Reporting	11/2/2009
Q: i want to make report for the tracked attendance which i stored in a table through append query. i ... A: First create a query that filters for the date range. In that query add a column with the ...
A current answer you put up	11/1/2009
Q: "Pop up form not in sync with current record: - Hello! I want to do EXACTLY what this guy wanted ... A: First I wouldn't use the Macro Builder, I would use the Code Builder. Second, no I can't tell you ...
Populate field in subform using combo box	11/1/2009
Q: I am trying to redesign a current database using MS Access 2007. I have data from 3 tables ... A: This is a standard technique called synchronized or cascading combos. This article: ...
if / then function in ms access	10/31/2009
Q: Good day, I want to add an if then statement to my table in ms access in the following: one of the ... A: You DO NOT enter data directly into a table. All interaction with a table should be done through ...
if / then function in ms access	10/31/2009
Q: Good day, I want to add an if then statement to my table in ms access in the following: one of the ... A: This is done on a form, NOT in table. Use a Yes/No datatype for Family, then in the After Update ...
employee form	10/31/2009
Q: I am develping a form for adding employees I set the controls for firstname, middlename, ... A: I'm not saying to use your code, I'm saying to check to make sure the controls are not Null. You can ...
Updating Multiple Records in Related Table	10/30/2009
Q: First and foremost, THANK YOU! I've used many of your tips in the past. I'm working in MS Access ... A: Ok, I see this as several tables: tblPeople PersonID (PK autonumber) FirstName LastName ...
Updating Multiple Records in Related Table	10/29/2009
Q: First and foremost, THANK YOU! I've used many of your tips in the past. I'm working in MS Access ... A: Update do not ADD records they alter existing ones. What you want is to APPEND (INSERT INTO) records ...
Percentage Query	10/29/2009
Q: I am trying to build a query that will report the percentage of a total population. My current ... A: Your total pop doesn't make sense to me. Why would the max of a primary key equal total population. ...
Access Periodically Needs to Shut Down	10/29/2009
Q: I have a database that has about 20 users or so. Each user has their own front end on their ... A: Have you done a compact and repair of the front ends and back end? Have you tried giving the problem ...
NUMBERS IN ASCENDING ORDER IN ACCESS	10/29/2009
Q: I want to sort by sizes in ascending order--the trouble seems to be that the sizes run ... A: In other words the sizes are not numeric values so they are sorting as text values. The answer is to ...
Input disappears in form	10/29/2009
Q: I have several forms where I would like to select the Project Name from a combo box based on a ... A: The answer is the same. The cboName control should be unbound. There should be a control on the form ...
Input disappears in form	10/28/2009
Q: I have several forms where I would like to select the Project Name from a combo box based on a ... A: If you are using an unbound combo to select a record, the value in that control won't change when ...
ms access and ms sql	10/28/2009
Q: can an access project access a database which located outside from its local area network? eg. ... A: No, if you convert an Access app to a newer version nothing should be lost, especially not any data. ...
Changing database name	10/27/2009
Q: I'm using Access 2003. Our company name changed. We have a group of 5 databases that interact with ... A: The only place the name of the MDB files would be used is in connection strings or VBA. If you have ...
Design qustion	10/27/2009
Q: I've been looking around but finding no real answers. I don't know Access really well and was ... A: You cannot use a pivot table for input. In Access you need to normalize your data so, you may need ...
Security Audit Report	10/27/2009
Q: I think i have got my security sorted on Access fingers crossed. I now have it showing the user ... A: The way I do this is with a logon form. When the user first logs in I create a record in a log table ...
Access 2003 VBA recordset loop	10/27/2009
Q: I'm wondering if someone would please let me know if there is a better way to run the loop in the ... A: The first thing I would do is normalize your data. The fact that you have a table with field names ...
Access	10/27/2009
Q: I have created a database for my photography club and we have to allocate 3 numbers for competitions ... A: Comment out the CurrentDB line and put DEBUG.Print strSQL before it. Set a break point so you ...
Not sure the best way to run a report	10/26/2009
Q: I am using MS Access 2007. I am setting up a database for issuing badges. I have two types of ... A: OK got it! So you need three tables: tblEmployee EmployeeID (PK Autonumber) FName LName other info ...
Multiselect depends on another multiselect.	10/26/2009
Q: I am using MS Access 2003 on a Windows XP system. I am creating a database with one single select ... A: That's because you don't use (([tblTableName].[Ref#])=[forms]![frmFormName]![List*]). When the ...
Message box	10/26/2009
Q: I have a field in my table [PartNum] that is indexed No Duplicates. How do i remove the Access ... A: A datatype mismatch means you are comparing two different datatypes. Since you didn't specify, I ...
Message box	10/26/2009
Q: I have a field in my table [PartNum] that is indexed No Duplicates. How do i remove the Access ... A: First, you do not have "fields" on a form. You have controls that may or may not be bound to a field ...
global module	10/26/2009
Q: Sir, i have the following code to find the difference between two dates Public Function ... A: Look at the first line of the code. It says PUBLIC Function. If it Said Private or just function, ...
Enter Different Data on A Same Table	10/26/2009
Q: Well, Its really hard to explain but here it is... I have 3 tables: Employees, EmployeesFather, and ... A: Use the Form wizard to create a form bound to the Names table. On that form place an unbound Option ...
Access	10/26/2009
Q: I have created a database for my photography club and we have to allocate 3 numbers for competitions ... A: If I follow you, you have three fields in the members table where you store a number that reflects ...
record selector	10/25/2009
Q: I have developed a form for adding the categories and I had a problem about duplicating the category ... A: I believe I answered this in an earlier question. You need to do error trapping. Look up On Error in ...
Add new product	10/25/2009
Q: I have a form for adding new products, and when I insert a new product which already exist in the ... A: Try it this way: If Not IsNull(DLookup("[ProductName]", "tbl_Product", "[ProductName] = '" & ...
global module	10/25/2009
Q: Sir, i have the following code to find the difference between two dates Public Function ... A: It already is. By using the Public keyword you make it available. Just put it into a module rather ...
Not sure the best way to run a report	10/24/2009
Q: I am using MS Access 2007. I am setting up a database for issuing badges. I have two types of ... A: Let me get this straight. You have badges that are pre-printed with numbers on them, correct? As ...
Access	10/24/2009
Q: After your great help and reading more about the subject I have started to trial access. I have ... A: You really need to read up on normalization more. What you are describing would create duplicate ...
Add new product	10/23/2009
Q: I have a form for adding new products, and when I insert a new product which already exist in the ... A: Then you want to either trap the error message using an OnError command. Or you can use a DLookup in ...
Not sure the best way to run a report	10/23/2009
Q: I am using MS Access 2007. I am setting up a database for issuing badges. I have two types of ... A: Yes, you should have one table for the badges. Like this: tblBadge BadgeID (PK Autonumber) ...
Searching Subform from a form	10/23/2009
Q: I have two tables linked in a one to many relationship. The tables are contacts (one) and fund ... A: Yes it does, but your approach to this is quite right. A subform is just a display of data in a ...
Multiselect depends on another multiselect.	10/23/2009
Q: I am using MS Access 2003 on a Windows XP system. I am creating a database with one single select ... A: Using tables is the correct method. The key piece you are missing it to Generate the SQL stmt using ...
Using Like Operator	10/23/2009
Q: I have a series of vendor names where I want to eliminate anything that contains "freight", ... A: There alternative to a SQL answer is a VBA answer that will be much more complex. Use a WHERE ...
Add New Record on Form and Carry Forward Data	10/23/2009
Q: I am working with access database with the following set up Tables Projects (where a list of ... A: Yep, one of the PCs I use something highlights blocks of text and windows up deleting them when I ...
Automated email in access 07	10/23/2009
Q: I am currently building a database to manage my company's quotes. I want the system to send an email ... A: Look up the SendObject method in Access Help. This will send an e-mail with a number of parameters. ...
Attaching files in access 2003	10/23/2009
Q: Morning / Afternoon / Goodday All, I was hoping someone can help me in understanding the best way to ... A: Umm, actually I was pretty detailed in my instructions. You start with creating the table. Step 2 ...
Database designing using MS Access 2007	10/23/2009
Q: My previous background was more in MS access 2003 but not totally in using some codes. I was ... A: If you need to search on multiple criteria, the best option is to educate the users on using the ...
Searching Subform from a form	10/23/2009
Q: I have two tables linked in a one to many relationship. The tables are contacts (one) and fund ... A: I'm not clear, do you want to display all contacts for a selected fund in the main form, or just ...
Count with conditional	10/23/2009
Q: I am using 2003 version of access. I have two tables: Table1 (Recipt#, Item#, RcdQty, Cust#) ... A: First, do you customers only order one item per order? That might be true of big ticket items like ...
Attaching files in access 2003	10/22/2009
Q: Morning / Afternoon / Goodday All, I was hoping someone can help me in understanding the best way to ... A: I'm not sure you can directly link to the e-mail but there are some options. 1) Save the e-mail as ...
Combo box as search criteria for date range	10/22/2009
Q: I have a search form to return records based on a combo box to return records within so many days. ... A: Have two hidden unbound controls on your form. In the After update event of combo use code like the ...
Subform Not Updating Tables Correctly	10/22/2009
Q: Steve, I've read through your past answers and have learned a lot just by following examples, but ... A: The form does NOT need to include all three tables. The main form should be bound to ...
Importing Linked Tables	10/21/2009
Q: I'm a fellow Access expert on this site, and I wanted to run something past you that's been driving ... A: Right, but, frankly there may be another issue here. If you are concerned about your users swiping ...
Add New Record on Form and Carry Forward Data	10/21/2009
Q: I am working with access database with the following set up Tables Projects (where a list of ... A: OK, I see the problem now. You can handle this in one of two ways. In either case you would create ...
complex questionnaire database design	10/21/2009
Q: I have built a database in Access to track the responses of hospital patients to several different ... A: Yep, Exactly. Each answer would control would have the Visible property set to No. In the On Current ...
Using Access as Registration Database	10/21/2009
Q: I work in the education department of a hospital. We would like to be able to take registration for ... A: Access was designed as an end user/small work group platform. It does not work well with the ...
Add new product	10/20/2009
Q: I have a form for adding new products, and when I insert a new product which already exist in the ... A: Ok, That's what error trapping is about. Look up ONERROR Goto in Access Help. It shows examples of ...
disabling button based on No Records	10/20/2009
Q: I have an app that I'm developing and I noticed I was receiving an error message for a Null value. ... A: If you are using a continuous form this will be difficult. Instead, put code in the edit button ...
complex questionnaire database design	10/20/2009
Q: I have built a database in Access to track the responses of hospital patients to several different ... A: A survey database takes a very specific structure. It looks like you have it, but I'm not 100% sure. ...
Query Help	10/20/2009
Q: Scottgem, I realize this is probably a dumb question and I know how to do a fix but it wouldn't be ... A: First you need Three tables here: Employees, TimeLog and Shifts. Second, you can't use a field in ...
Using Access as Registration Database	10/20/2009
Q: I work in the education department of a hospital. We would like to be able to take registration for ... A: I would not recommend Access for this. I would recommend creating an e-commerce site with a WEB host ...
Filter from one form to another	10/20/2009
Q: I want to click on a field within a form and on that event open up another form querying the ... A: First, you don't have fields on a form. You have controls that may or may not be bound to a field in ...
attachments save location in access 2007	10/19/2009
Q: We have an Access database with attachments. The issue arises when saving those attachments. We ... A: Frankly, I wouldn't use the Attachments data type. While its easier to use, it copies the file into ...
Can't record history	10/19/2009
Q: I am new to MC Access and Visual Basic and this has roughly given me some trouble. I have created a ... A: First, you would know if you were using VB as a front end. You would be developing the front end in ...
DATEDIFF in ms access	10/19/2009
Q: I am creating a table in MS access for railway data base in which journeydate and reservationdate ... A: I rarely use Validation rules except for simple rules. Instead I would use the After Update event ...
MS Access Save Button Code?	10/19/2009
Q: Please i need help in MS Access MS Access Command Button (Save) codes to save one unbound front end ... A: If you are using an unbound form, then YOU need to code the I/O between the form and the table. How ...
Add New Record on Form and Carry Forward Data	10/19/2009
Q: I am working with access database with the following set up Tables Projects (where a list of ... A: Yes I can with one word: "subforms". You are not using the capabilities of Access to make your life ...
Acces Form allowing edits	10/19/2009
Q: I tried your solution to allowing edits using a toggle button. This was to prevent records being ... A: If you are using a Toggle button, it has a Yes/No value. So I would use this the After Update Event ...
Netbooks	10/18/2009
Q: I am in the market for a netbook. I will be utilizing it for home-use purposes such as browsing the ... A: Your budget is high, unless you plan on including some accessories like an external burner, case, ...
Netbooks	10/18/2009
Q: I am in the market for a netbook. I will be utilizing it for home-use purposes such as browsing the ... A: All the Netbooks are very similar. They all use the same processors; most have the Atom 270 with a ...
Access	10/17/2009
Q: After your great help and reading more about the subject I have started to trial access. I have ... A: First, what is the purpose of this table? If you have another table of people, then you don't need ...
.MDE front end file	10/17/2009
Q: I hope you can help me out. My MS Access db is split (frontend/backend). The backend lies on our ... A: Yes. ANYONE who uses an Access file needs Read/Write/Create rights to the folder where either the ...
Pass selected option (Month or Quarter or Year) in Option Group to the criteria Date field of a Query.	10/17/2009
Q: This probably have a simple solution, but I can't find my way out here. I need my Access 2007 ... A: Ok, I understand better now. The ratings for this site are on a scale of 1-10 in 4 categories: ...
Excel to Access	10/17/2009
Q: In my small office of 22 we currently have a excel rotating line roster we rotate once a week by ... A: Yes I do contract work and I've worked with clients around the world. E-mail me the specifications ...
Adding Form	10/17/2009
Q: "hi, I want to inquire about how I can open any form for adding new data without getting the ... A: My turn to apologize. I just tested this and it does allow you to scroll back through entries made ...
Adding Form	10/17/2009
Q: "hi, I want to inquire about how I can open any form for adding new data without getting the ... A: So? I told you what to do...set the Data Entry property to Yes. That set the form to only allow ...
Adding Form	10/17/2009
Q: "hi, I want to inquire about how I can open any form for adding new data without getting the ... A: Set the Data Entry property of the form to Yes. If you want to use the form for both new records and ...
How to combine letters and numbers.	10/16/2009
Q: I have a question for you. Please would you be so kind to answer it? I have created a table of ... A: You can do this one of two ways. You can use an input mask or you can use an after update ...
Q about the attribute.	10/16/2009
Q: I have a question for you. Please would you be so kind to answer it? I have created a table of ... A: I'm not sure what you mean by "attribute"? The prerequsite field is a foreign key just like any ...
Importing Linked Tables	10/16/2009
Q: I'm a fellow Access expert on this site, and I wanted to run something past you that's been driving ... A: Its not really a bug. By not requiring a password for the FE, you are essentially opening a back ...
Split Field into Multiple Columns	10/16/2009
Q: I am attempting to split a field into multiple columns. The field contains the names of guests who ... A: For your purposes you would use this in a procedure. The procedure would either go behind a button ...
Pass selected option (Month or Quarter or Year) in Option Group to the criteria Date field of a Query.	10/16/2009
Q: This probably have a simple solution, but I can't find my way out here. I need my Access 2007 ... A: Look up the GroupLevel property in VBA help. That should help you do what you want. You will need to ...
How to automatically fill in form information when an item is selected from a Combo box in Access 07	10/16/2009
Q: I've been looking around for the perfect solution for my problem, but unfortunately i didn't found ... A: There are several alternatives, I'm surprised you haven't found one. Its not entirely clear what ...
MS Access DB Check Box Logic	10/15/2009
Q: I have the below 3 columns in a table called Order. OrderId String OrderAddress String ... A: "The Recordsource of the form is a multi-table query" That's your problem. When you have a ...
Link multiple front ends to single table.	10/15/2009
Q: I am using Access 2003 currently, All computers on our network run XP Pro. I have two databases ... A: When you open an Access database file a security file (ldb or laccdb) is create that maintains ...
Error Code 3075	10/15/2009
Q: I am trying to add a search to my search form. I dont have a lot of expertise in this. What I am ... A: Ok, slightly different parameters. I didn't realize you want to search on customer OR agency. So you ...
Link multiple front ends to single table.	10/15/2009
Q: I am using Access 2003 currently, All computers on our network run XP Pro. I have two databases ... A: You should have three back end files. One each for the two databases and one for the shared Sites ...
VBA module loop issue	10/15/2009
Q: I have a module which I loop through and create a comma separated text file. It works for the most ... A: I don't have the time right now to fully analyze the code, but have you stepped through the code ...
Grouping Question	10/15/2009
Q: Is it possible to group the results of three separate fields in a report and then sort them from ... A: First, you should not have fields with numbers like that. When you have fields like comment1, ...
Append Query	10/15/2009
Q: I am Magesh, Using Append query in MSaccess I have a multiple table with unique fields and I want to ... A: I'm not clear here. It sounds like you have a bunch of separate tables with the same structure and ...
Maximum Number of Users	10/15/2009
Q: I have a shared Access application where all users should log in & then they have to use the same ... A: First, is the application split between back end (tables) and front end (everything else)? If not, ...
Can't record history	10/14/2009
Q: I am new to MC Access and Visual Basic and this has roughly given me some trouble. I have created a ... A: First Access uses VBA (Visual Basic for Applications) which is a superset of VB. Its not clear ...
Query	10/14/2009
Q: I have a table that contains 10 date fields. The query needs to show any instance where the date in ... A: Your problem is in database design. Your data is not properly normalized. What you are doing is ...
How to make an MS Access Unbound Combo Box A Multiple Value List on an Unbound Form?	10/14/2009
Q: I need you help on MS Access 2007. I have a big problem with my MS Access Unbound Form. How can I ... A: Access 2007 does provide a multivalue field type. I do NOT recommend using it as it breaks ...
How to make an MS Access Unbound Combo Box A Multiple Value List on an Unbound Form?	10/14/2009
Q: I need you help on MS Access 2007. I have a big problem with my MS Access Unbound Form. How can I ... A: First, you seem to be misunderstanding the concept of bound and unbound forms and controls. An ...
Excel to Access	10/14/2009
Q: In my small office of 22 we currently have a excel rotating line roster we rotate once a week by ... A: 1-Yes 2-Not a problem 3-Yes of most of it. The e-mail submission might be an issue. If using Access ...
Access limitation	10/14/2009
Q: Do you have any idea of Access' limitation in Access 2007 version? I read many limitations ... A: I believe some of what was written on that page is exaggeration and may be the result of poor ...
Query	10/14/2009
Q: I have a table that contains 10 date fields. The query needs to show any instance where the date in ... A: Can you describe what these date fields represent and what you data is supposed to do. Your problem ...
MS Access DB Check Box Logic	10/14/2009
Q: I have the below 3 columns in a table called Order. OrderId String OrderAddress String ... A: I'm assuming you set the Recordsource of the form to your table. Is there any message in the status ...
Error Code 3075	10/13/2009
Q: I am trying to add a search to my search form. I dont have a lot of expertise in this. What I am ... A: Why are you doing it this way? If I follow you, you want to enter criteria on a form, then see a ...
Date Expression	10/13/2009
Q: NextI have a form with fields to display the date on which the form was created, and a second field ... A: First you don't have fields on a form. You have controls that may or may not be bound to a field in ...
Ran out of follow ups on previous question	10/13/2009
Q: (FROM PREVIOUS) Sorry, maybe I worded that poorly. How do I populate a field in a table with the ... A: Here's something from an app I wrote Open Me.txtFilename For Input As #1 ' Open ...
Date Expression	10/13/2009
Q: NextI have a form with fields to display the date on which the form was created, and a second field ... A: Almost. First, you need to click the [...] at the right of the Before Update row and Select Code ...
Delete records using module	10/13/2009
Q: Using an action query can delete the records I want to delete but I am trying to get all my tables ... A: If you have a stored query that does the Delete the way you want it, just use DoCmd.OpenQuery ...
Inventory Count List Generator	10/13/2009
Q: Scott: I am fairly new to the Access world but a quick learner. I have been given an assignment to ... A: Again, you create a query with the not counted items. You then run a loop that generates x number of ...
MS Access 2007 Autonumbers	10/13/2009
Q: I have an autonumber field in a MS Access 2007 table. I appreciate the differences between a normal ... A: Then you can't use an autonumber field. An autonumber has ONE and ONLY ONE purpose. That is to ...
Conditional Formatting	10/13/2009
Q: I am trying to apply some conditional formatting to a datasheet subform. I only need 3 conditions so ... A: If the recordsource has different fields then you should use different forms. Create a tab control ...
Date Expression	10/13/2009
Q: NextI have a form with fields to display the date on which the form was created, and a second field ... A: Create Date is easy. You set the Default Value of the field to =Date (or = Now() to get the date and ...
copying data into new fields	10/12/2009
Q: I have a database with 1000 entries, with the following fields - CatNo, Name1, Name2, Sex1, Sex2, ... A: First your database is not normalized properly and that presents part of your problem. When you have ...
CrossTab Query	10/12/2009
Q: I am trying to run an Access 07 crosstab query that needs to have data based on months in the rows ... A: Create a table with each month as a record in it, then use an OUTER join to join that to your table ...
Calculated fields	10/11/2009
Q: Access 2007 I've set up a report that calculates some fields - fees for various aspects of a ... A: Can you do the calcs in the query the report is based on? Or do them in an interim query and then ...
ms office 2007 suite	10/10/2009
Q: The work I'm applying for requires me knowing MS office 2007 Suite. I assume that means excell, ... A: You can download a trial version of the Office 2007 suite. It will give you 60 days to learn it. ...
Update a couple of a records fields from the previous records fields	10/9/2009
Q: I have a database in Access that I need to have update two fields in a record from the previous ... A: Ok, the answer here is do your import properly. Instead of using the import process, Use the Open ...
Update a couple of a records fields from the previous records fields	10/9/2009
Q: I have a database in Access that I need to have update two fields in a record from the previous ... A: In the After Insert event set the Default value of the controls to the current value: ...
Report with Parameter Query/Form	10/9/2009
Q: I need to set up a report that shows four different charts, each based on a different parameter ... A: I almost never use parameter queries. Instead if enter criteria into a form, then reference the ...
Group Records in Word 2003 Merge	10/9/2009
Q: I have database query as a source of the mail merge. It does not have grouping in it since it does ... A: What that code is basically doing is hiding the keyfield until it changes. Again, I'm not that ...
Group Records in Word 2003 Merge	10/9/2009
Q: I have database query as a source of the mail merge. It does not have grouping in it since it does ... A: If you have read that you can do Grouping in word mail merges, that's news to me. But if its ...
AutoSaving	10/9/2009
Q: A few times while inputting some data my fingers fall on some keyboard combination or the cursor was ... A: First, if you are using a bound form, Access will automatically save the current record when the ...
linking Forms to field values	10/9/2009
Q: I have a form that has associates FName and LName, a subform that keeps track of that individual ... A: I am very sure that your timeclock program uses an employeeID as a primary key. I can't imagine a ...
Conditional Formatting	10/9/2009
Q: I am trying to apply some conditional formatting to a datasheet subform. I only need 3 conditions so ... A: First, a form is unbound ONLY if it does not have a RecordSource. Whether you are using a query or a ...
Help with criteria in calculated field	10/9/2009
Q: I am using Access 2007 and this is what i have for my calculated field: Number Of Months Ago: ... A: That's because the result of your calculation is a decimal number so it will not equal 3 exactly. ...
Joining Time and Date in single column	10/9/2009
Q: I am using Access 2003 and I have a table with Time and Date in separate columns and i want to have ... A: First, Date and Time are reserved words in Access and should not be used for object names. Second, ...
primary keys	10/8/2009
Q: Scott, Yesterday, you suggested that I rename my primary keys. Is there a way to rename them so ... A: My opinion is a resounding NO. Users should never work with tables directly. All interaction should ...
access database query	10/8/2009
Q: I need query help. I am trying to run queries for a search form where the end result will give me ... A: Where are you trying to use this query? If you want a form to display the results of the query you ...
linking Forms to field values	10/8/2009
Q: I have a form that has associates FName and LName, a subform that keeps track of that individual ... A: Use DoCmd.OpenReport "reportname",,,[AssociateID] = " & Forms!formname!controlname You should not ...
Access 2007 INSERT INTO syntax	10/8/2009
Q: I found an answer you provided in November 2007 regarding the syntax for using "dbs.Execute INSERT ... A: The commas and other characters (with the exception of the ampersand) should not be a problem as ...
Background images	10/8/2009
Q: Access 2007 I've set up a report that has a background image (jpeg). I've set the fields so that ... A: Sorry, I meant report. And that's what you should use. The report has a Picture property. Set that ...
auto update	10/8/2009
Q: i have four combo boxes that filters answers from one combo box to another. How do i create a ... A: You can do Me.controlname.Requery for each control. If you want to clear everything ...
Forms	10/8/2009
Q: i would like to be able to open a form immediately when i open my access file. How would i be able ... A: Sorry you didn't specify Access 2007. The Start form can be selected by pressing the Office button ...
Report Header from unbound form creates #Name?	10/7/2009
Q: I am using Access 2003 and I have a report header that is generated from an unbound form. The ... A: Don't set the controlsource in code. Just right click on the control and select properties. In the ...
database roadblock	10/7/2009
Q: Okay I'll admit Im a newbie and probably got a lil over my head but maybe you can help.. I have to ... A: Yes, you mentioned that a plot has a section. So the Section table is a lookup to standardize the ...
Radio Button Groups	10/7/2009
Q: Scott, Having read your prvious answer i believe you could be able to save me. I have recently ... A: First, your RowSource for username should look like this: SELECT [Actor ID], Surname & ", " & ...
primary keys	10/7/2009
Q: Scott, Yesterday, you suggested that I rename my primary keys. Is there a way to rename them so ... A: Yes, any tables you want to join requires a FK to connect it to the the parent record. Think of a ...
Report Header from unbound form creates #Name?	10/7/2009
Q: I am using Access 2003 and I have a report header that is generated from an unbound form. The ... A: You can create variables in the Report class module. Right under the Option Compare (or Option ...
primary keys	10/7/2009
Q: Scott, Yesterday, you suggested that I rename my primary keys. Is there a way to rename them so ... A: Sometimes, especially in queries, changing the name in table design will update, but not every ...
Inserting new row into child table from different form	10/7/2009
Q: I have two tables that have a parent child relationship. The parent data is within it's own form but ... A: If you use an EMBEDDED subform, linked on the keyfield, then no code is necessary. when you create a ...
Radio Button Groups	10/7/2009
Q: Scott, Having read your prvious answer i believe you could be able to save me. I have recently ... A: First, your three radio buttons should be in an Option group, because only one can be selected at a ...
Awards Database for Miltary	10/7/2009
Q: My unit has an awards database which was created in microsoft access some time ago, it was given to ... A: Windows XP has built in support for compressing files. Just right click on the file I forget whether ...
ms access	10/7/2009
Q: i ahve created two fields dteDOB and dteEND in ms access form and have created a report and copied ... A: I would be happy to do it for you, but that would mean contracting for my services and that would ...
if then events	10/7/2009
Q: I have a status drop down in a form (Open, Closed) and a date closed field (text), is there a way ... A: Well this brings up slight sore point with me. Users should not influence table design. That's up to ...
help with relationships	10/6/2009
Q: I'm hopeful that you can help me. The more information I read, the more confused I get. I don't ... A: What I would do is create queries that filter for Post and Pre Op records, then use them as the ...
help with relationships	10/6/2009
Q: I'm hopeful that you can help me. The more information I read, the more confused I get. I don't ... A: First, Access does NOT automatically populate FKs. The only tiume an FK is automatically populated ...
Inserting new row into child table from different form	10/6/2009
Q: I have two tables that have a parent child relationship. The parent data is within it's own form but ... A: Its something you code. I can help you with the code, but I need to know whether there is a one to ...
if then events	10/6/2009
Q: I have a status drop down in a form (Open, Closed) and a date closed field (text), is there a way ... A: Sure, but why would you need to? If the only status you record is Open and Closed, then you already ...
Awards Database for Miltary	10/6/2009
Q: My unit has an awards database which was created in microsoft access some time ago, it was given to ... A: Without knowing the structure of the database its hard to advise. If your goal is to print out a ...
ms access	10/6/2009
Q: i ahve created two fields dteDOB and dteEND in ms access form and have created a report and copied ... A: First, you do NOT create fields on a form. You place controls on a form. Such controls may or may ...
Access query	10/5/2009
Q: Hey Scott, I am having trouble getting this query to work correctly. The problem seems to be ... A: First you said; "I put that in a subform and it was all set." By that, I'm assuming you used that ...
question	10/5/2009
Q: age/term 2 3 4 19 0.54 0.232 0.231 20 0.32 0.231 ... A: If I'm following you the new structure would have a record that indicates the age, the category of ...
database roadblock	10/5/2009
Q: Okay I'll admit Im a newbie and probably got a lil over my head but maybe you can help.. I have to ... A: Being a newbie, you need to learn more about normalization. I suggest googling the term and boning ...
Access query	10/5/2009
Q: Hey Scott, I am having trouble getting this query to work correctly. The problem seems to be ... A: I see some problems here. I don't think your structure is correct, making you work much harder than ...
Forms	10/5/2009
Q: i would like to be able to open a form immediately when i open my access file. How would i be able ... A: Under Tools>Startup Ooptions you can set a form to open with the app. Alternatively you can create ...
Access query	10/5/2009
Q: Hey Scott, I am having trouble getting this query to work correctly. The problem seems to be ... A: Why are you doing this? What is the purpose? What it looks like is you are using a Select statement ...
MS Access 2003 ?	10/4/2009
Q: 1) I have a problem about creating report from crosstab query. The column heading field in the ... A: The RowSource of the Items combo should look like this: SELECT ItemID, ItemDescription FROM ...
adding column to the main database	10/3/2009
Q: sex(male, female), smoker or non smoker, term in force and distribution used to purchase the ... A: There are a few ways to do this. You can have multiple nested IFs or SELECT CASES. Or you can use ...
adding column to the main database	10/3/2009
Q: sex(male, female), smoker or non smoker, term in force and distribution used to purchase the ... A: There are a number of ways you can do that. You could use IF Smoker and Gender = "Male" or a ...
adding column to the main database	10/3/2009
Q: sex(male, female), smoker or non smoker, term in force and distribution used to purchase the ... A: I would use a Select Case statement, with embedded IFs. For example: Select Case Age Case 21 to ...
after update	10/3/2009
Q: supposing i have a category combo box (Category) and a Type combo box (Type) in my form. after being ... A: If Type is dependent on Category in a 1 to 1 relationship so incoming is always A, etc., then you ...
database form and report	10/3/2009
Q: i would like to create a form that allows me to enter values and automatically saves it under the ... A: First, Name is a reserved word in Access so shouldn't be used as an object name. I would also be ...
Apostrophe in the name issue	10/2/2009
Q: Hey Scott, question regarding syntax of vba code for the variable name. here is the code: strSQL = ... A: This can be easily solved by using a foreign key instead of a name as your key field. You should ...
MS Access 2003 ?	10/2/2009
Q: 1) I have a problem about creating report from crosstab query. The column heading field in the ... A: 1) Check Microsoft's Knowledge Base (support.microsoft.com) for Dynamic crosstab report. They have ...
How to pass form control to a report	10/2/2009
Q: I'm a volunteer in our sportsclub and I use MS Access 2003 on Vista to make lists for instructors of ... A: I just told you how. ;) What you are referring to is called a parameter prompt query. This is ...
Setting up and editing an Access Database	10/2/2009
Q: The answer to the request ADD NEW FIELD IN A FORM is very unclear. Would you please assist me to ... A: What answer? The closest I could come was an answer I gave Tom on adding a new column to a form. He ...
after update	10/2/2009
Q: supposing i have a category combo box (Category) and a Type combo box (Type) in my form. after being ... A: What you are referring to is called Cascading or Synchronized comoboxes. This is when a selection in ...
Creating a Form	9/30/2009
Q: I've created 2 tables in Access 2007. One with a list of Authors and another table listing titles of ... A: What about co-Authors? Do you deal with books with multiple authors? It also sounds like you need ...
Access	9/30/2009
Q: As I am completely novice to access, below is all the questions which I need to have in the database ... A: It would have helped if you gave a brief description of what each table is supposed to cover. Based ...
Remove Duplicates from Access Query	9/30/2009
Q: I have a database downloaded from the FAA. It contains all the registered aircraft in the U.S. I ... A: Unless the duplicates are exact, this is hard to do. What you really should do is break this out ...
Access 2007 - tabbed interface	9/30/2009
Q: Newbie here! Created table of data, and generated several different forms and reports showing ... A: Yes, Base the reports on queries. Use a form to call the first report and enter the criteria to ...
Counting Cumulative Records	9/30/2009
Q: I am building an issue tracking database and I need to create a graph that shows the cumulative ... A: Sorry, my fault. I misread the situation. The first step is to create two queries that group by ...
MS Access - File Dialog	9/29/2009
Q: I am trying to add a command button to open a File Dialog box and then input the selected file in ... A: Here's the code I use: On Error GoTo Err_cmdBrowseForFile_Click Dim fDialog As Office.FileDialog ...
Access Booking Database	9/29/2009
Q: I want to design an Access database to serve as a booking tracking system for certain collections. ... A: Well you start with a table that lists the appointments. The easiest would be to enter the date ...
SQL Statement	9/28/2009
Q: Could you please help me with the following query SQL statement. I keep getting a sytax error in the ... A: Because that's not a valid SQL statement. Try looking up SQL SELECT in Access Help. Nor is that even ...
Most recent row for each person record	9/27/2009
Q: You will be happy to know I have most of it done, but I just cannot get all the way there. Hoping ... A: First, forget about doing this in one query. Often it is necessary to use what I call "interim" ...
Access - Dlookup and edit record	9/27/2009
Q: iam doing a simple database and it seems to work fine so far. my only problem is that my Dlookup ... A: Define "huge"? If you are selecting clients, why not list the client names? Is there a way to filter ...
Search in Access	9/27/2009
Q: The one i would like to have the search is in the product tab which has 3500 products on the left ... A: Sounds like you have a setup similar to the Form wizard, where you select the fields you want from ...
SQL Statement	9/26/2009
Q: Could you please help me with the following query SQL statement. I keep getting a sytax error in the ... A: So you want the latest date that has a transaction type of 1 or 4. Try WHERE TransactionID ...
Dcount help	9/26/2009
Q: I am using access 2003 I have a table called AttendanceT It has a yes no field called InClass In a ... A: Try dcount("[student]","AttendanceT","[InClass]=False") Or ...
Access - Dlookup and edit record	9/26/2009
Q: iam doing a simple database and it seems to work fine so far. my only problem is that my Dlookup ... A: First I'm not sure what %26 refers to. That could be messing your code. It should be an &. Second, ...
Re: Databse Design	9/26/2009
Q: I copied an exiting database that included a "Swithboard" application in it. Now when ever I go into ... A: First, hold down the shift key as you load the the database. Thgis will bypass the startup options. ...
hi again	9/26/2009
Q: I have a problem in creating a form that will output the specific record not the whole table. my ... A: If you want to do a search for a specific record, then use the Combobox wizard. In Form Design mode ...
hello can you help me	9/26/2009
Q: I already have a database of certain company. That Database must contain a form that will validate ... A: Use an UNBOUND form with two controls. The first control is combobox where the employee can select ...
Access 07 Form Edit Control	9/25/2009
Q: I hope tha you can help with this question that I have. I have a Form "Orders" set up with multiple ... A: First set the Allow Edits property to NO, manually. This way it will be No when you open the form. ...
Access 07 Form Edit Control	9/25/2009
Q: I hope tha you can help with this question that I have. I have a Form "Orders" set up with multiple ... A: You have two choices. The easiest is to set the AllowEdits property to No as the default for the ...
Combo Box Selectable Search	9/25/2009
Q: My question about MS Access is I have a search form called frmMain. There is Text Box and a Find ... A: First, I would suggest you scrap this altogether. Just use the Filter By Form feature. Open your ...
Access Database Query Question	9/25/2009
Q: I'm trying to run a query and am unsure how to do it. What I have is a matrix, where each row is a ... A: The way you do this is by designing your database properly. Your problem is your database is not ...
SQL Statement	9/25/2009
Q: Could you please help me with the following query SQL statement. I keep getting a sytax error in the ... A: First, you cannot set a WHERE clause to equal a select Statement. You need to use an IN Clause ...
Adding a list of dates to all the records in a table	9/25/2009
Q: I have a list of dates saved as an Excel file, and a database that contains Names/Date Expected/Date ... A: Ok, So you have a set of statement dates for for lets say 2010. And you want to create records for ...
msaccess student marks storing, comparing and printing accordingly.	9/25/2009
Q: Sir, I have a problem regarding student marks database. I have created basically three types of ... A: I would have ONE table for marks with a field that identifies the mark as Main, Scrutiny and ...
ComboBox Error	9/24/2009
Q: I have a Salary and Wages form bound to Timesheet table. In my Salary ang Wages form, I have a ... A: First I don't understand why you are using a query in your RowSource. It should be direct from the ...
Query Criteria	9/24/2009
Q: When I type in a criteria for a field for a select query and then if I try to run it, it says ... A: Yes, what has been done is a Lookup field was created on the table level. I do not recommend these ...
acess upgrade	9/24/2009
Q: I have installed office XP professional. My data base(access) was operating on Windows 97. When I ... A: First there is no such thing as Windows 97. There was 95, then 98. There is an Office XP Pro. And ...
Search form using ID	9/23/2009
Q: I developed a form in my database for searching for any rang of records based on the ID number and ... A: If you arre getitng a parameter prompt, that means that somewhere in the process you are asking for ...
Checking totals for all records in a field of a form after user enters another value	9/23/2009
Q: I have a project tracking database that includes a form that has a field called employee percent. ... A: In the After Update event of your emp_percent control use code like this: If ...
Removing filter but staying on current record	9/23/2009
Q: Scott What I have is a text search box that you type in a word and it will find all matches in a ... A: If that code works, then the problem was definitely your DIMming CrID as Integer rather than Long. ...
Removing filter but staying on current record	9/23/2009
Q: Scott What I have is a text search box that you type in a word and it will find all matches in a ... A: First, if your PKs are Autonumbers then CrID should be a Long datatype, not an Integer. So your ...
Update Query Properties in code	9/23/2009
Q: I'm using an Access database as a front-end to a SQL Server 2005 database. There are some 40 pass ... A: If you had setup your database using best practices this wouldn't be an issue. Generally, if you are ...
Print specific records	9/23/2009
Q: I have a form that a user populates data into. In this form is customer information, test type and ... A: Nope. Are you saying the buttons aren't working? If not, in what way aren't they working. What ...
Print specific records	9/23/2009
Q: I have a form that a user populates data into. In this form is customer information, test type and ... A: This can be done with a WHERE clause in the OpenReport method. You can place unbound controls on ...
Removing filter but staying on current record	9/23/2009
Q: Scott What I have is a text search box that you type in a word and it will find all matches in a ... A: Well first, the MenuItem and GotoRecord members of the DoCmd collection are old hat. You should be ...
Table Design	9/23/2009
Q: I am trying to design a database around a form we currently use. The form requires 44 pieces of ... A: Again, without knowing the data its hard for me to advise. But based on what you have told me, you ...
Adding a column to a form	9/22/2009
Q: I used the contact template to input some information. That is in excel. After inputting some ... A: Ok, If you added a field to a table, you open the from in Design mode. Then you can either add a ...
Adding a column to a form	9/22/2009
Q: I used the contact template to input some information. That is in excel. After inputting some ... A: Are we talking Excel or Access? If so what version of Excel? I am an Access expert, not much on ...
Label Size	9/22/2009
Q: I have another problem now. I have a report called MyLabel and when i click this i get a prompt. ... A: OK, First you need to set the Access report to use a Specific printer, selecting your label printer. ...
Query Question	9/22/2009
Q: in this Database i must register a person to a course. Everytime I add a course to the events table ... A: So you want a form that shows Inactive records so that you can restore them to Active? Why not just ...
Search form using ID	9/22/2009
Q: I developed a form in my database for searching for any rang of records based on the ID number and ... A: If ID is an Autonumber, then entering a text value will cause a data mismatch error since an ID can ...
Multiple Combo Boxes	9/21/2009
Q: I have a form with two sub forms embedded. I would like to add a combo box to each sub form. The ... A: This is not the best design, at least not one I would choose. I would bind the main form to the ...
Multiple Combo Boxes	9/21/2009
Q: I have a form with two sub forms embedded. I would like to add a combo box to each sub form. The ... A: First, what is the main form bound to? Second, how is the customer and the test results related? If ...
Query Question	9/21/2009
Q: in this Database i must register a person to a course. Everytime I add a course to the events table ... A: Generally, records are never deleted, especially when relations exists between other tables. You ...
Calculate the value in the form which should show up in the bounded table.	9/21/2009
Q: I created the form bounded with the table. Now in the form have 3 fields, Value1, Value2, Value3. ... A: No you don't. As a general rule we do NOT store calculated values in a table. This is unecessary ...
Query Question	9/21/2009
Q: in this Database i must register a person to a course. Everytime I add a course to the events table ... A: You need TWO tables here: tblCourse CourseID (PK Autonumber) CourseName tblSession SessionID (PK ...
Control source of a field in a form, and moving data from a form to a table	9/21/2009
Q: I have created a datasheet form in access 2007. A property of this form is that onload it opens a ... A: When you say you put the code in the Unbound control what event did you use? And, was field1 null ...
Duplicate Record Entry	9/20/2009
Q: ! Hope all is well. I need some help with macros this time. I have a form in which i have a ... A: First, I rarely use macros they are very limiting. And I think they are beyond the scope of what you ...
Control source of a field in a form, and moving data from a form to a table	9/20/2009
Q: I have created a datasheet form in access 2007. A property of this form is that onload it opens a ... A: Setting the default value only works when you create a new record. So if you are opening your form ...
Control source of a field in a form, and moving data from a form to a table	9/20/2009
Q: I have created a datasheet form in access 2007. A property of this form is that onload it opens a ... A: As I said, the default value is only picked up when you create a new record. See my other answer. I ...
Control source of a field in a form, and moving data from a form to a table	9/19/2009
Q: I have created a datasheet form in access 2007. A property of this form is that onload it opens a ... A: First, You cannot use a modal form for this. A modal form has to be closed before you can use ...
Need you Help	9/19/2009
Q: Sir I join a company as fresher on MS access 2007 and My company give me a Documents to create a ... A: I'd like to help, if I could understand the question. It sounds like you created a database for your ...
Form slow to close since split	9/19/2009
Q: Your answers have helped me many times in the past! Thanks. My problem: -I created a database ... A: I think this can be done much more simply. If IsNull(Me.TaskID) Or IsNull(Me.InstanceID) Then ...
populating form from 2 tables	9/19/2009
Q: I am building a database for a Real Estate Trust and was wondering: I have a listing table and form ... A: Why do you need two tables? From what you explained, the only difference is whether a listing ...
Form slow to close since split	9/18/2009
Q: Your answers have helped me many times in the past! Thanks. My problem: -I created a database ... A: First, you are better off using smaller forms with popups then having one large main form. That ...
Form slow to close since split	9/18/2009
Q: Your answers have helped me many times in the past! Thanks. My problem: -I created a database ... A: In using the DoCmd.Close, are you selecting to NOT save? I suspect the problem is that Access wants ...
sorting multiple fields in a listbox	9/18/2009
Q: Please let me know how I can sort fields in a list box. I would like to have command buttons where ... A: You can't do it on a list box. What you can do is have a sort combo to select the column you want to ...
MS Access	9/18/2009
Q: It's me again. The silo and begintons and loadedtons, gal! I get that we are creating a select ... A: Hmm, and what event are you using? You might try changing Data Entry to NO and opening the form ...
Label visibility issue	9/18/2009
Q: I am running Access 2003 on a Windows XP machine with 2 gigs of RAM. I am writing some VBA to ... A: First, if you have designed your database properly, there should be no need for the code that ...
Open Report	9/18/2009
Q: I'm really getting confused at the moment!! I want to open a report and specify which information is ... A: Sounds like you are using a parameter prompt query where you type something like: [Enter value] As ...
Getting information from other table is database	9/18/2009
Q: I have a 2 tables and 1 form which are the following: - suppliers and customers table - transactions ... A: Your structure is not properly normalized. One of the principles of normalization is that data ...
Importing data into Access from Lotus	9/18/2009
Q: I have created a stock inventory database in MS Access 2007. I am now looking to import particular ... A: Create a link to the spreadsheet, then run an Append query from the linked sheet to your ...
Access	9/18/2009
Q: I have a slight issue which iam struggling to resolve and am sure it is something i have not done ... A: Sorry to say this but there are several issues. Tbl_EHS should look like this: tbl_EHS DemogID (PK ...
Adding a record	9/17/2009
Q: I created a form based on a table. It's a form with a subform. On the main form the user inputs the ... A: Makes perfect sense and is fairly easy to do. Since you do have a date field it will be easy to find ...
Employee Review Form Design	9/17/2009
Q: I am using MS Access 2007 I need to create an employee review database that: --Has several ... A: You did a good job of translating my recommend survey strucutre into your table structure. The only ...
Access 2000 report question - count	9/17/2009
Q: I am on Windows XP 2002 and Access 2000. I had to change one of my reports where I was counting the ... A: So you have a report that is grouped by tractor type. With a number of detail records for each type. ...
Migrate Access Database to SQL Server	9/17/2009
Q: I have created an access database front-end and back-end that scrapes information from a mainframe ... A: ADPs have their place, but primarily when most of the coding is done on the SQL Server end. If you ...
MS Access - Search Box	9/16/2009
Q: I'm creating a database in MS Access 2003... I haven't used Access in a long time and am rusty. I ... A: Filter by Form works great. I use it all the time. It allows you to enter criteria in any one or ...
Dlookup	9/16/2009
Q: I am new to access. I have single table, id, name, surname, comments. if the id was entered ... A: Yes, you can have a combo to list the people. The RowSource would look like this: SELECT PersonID, ...
Dlookup	9/16/2009
Q: I am new to access. I have single table, id, name, surname, comments. if the id was entered ... A: First, Name is a reserved word in Access and shouldn't be used for Object names. I suggest changing ...
Access 'pop-up' for data entry	9/16/2009
Q: How do I copy data from a field to the field immediately below in a 'pop-up' form? EG I can have ... A: I think you need to look at your structure first. You should have two tables here. The first would ...
Inventory Count List Generator	9/15/2009
Q: Scott: I am fairly new to the Access world but a quick learner. I have been given an assignment to ... A: You can use the RND() function to generate a Random list of part numbers. I would suggest using an ...
Duplicate Records	9/15/2009
Q: I got a table with different columns records and one of the column "GPD" contains repeated numbers ... A: Access is an object oriented platform. Everything in Access is an object that is manipulated by its ...
Duplicate Records	9/15/2009
Q: I got a table with different columns records and one of the column "GPD" contains repeated numbers ... A: In Query Design mode add your table. Select the field containing the GRD text string and the field ...
Randomly Pick Unused Alphanumeric From Table	9/15/2009
Q: I want to have the user able to click a command button and have it automatically generate a ... A: DCount will work also. You could use either: If DCount(...) = 0 or If IsNull(DLookup(...)) ...
Import Excel spreadsheet to MS Access	9/15/2009
Q: Scott, Currently my employees are submitting a spreadsheet I’ve created on MS 2007 Excel with the ... A: 1) You can export the spreadsheet from Excel to Access, but its easier the other way around. Import ...
setting subform properties using VBA	9/15/2009
Q: Windows XP, Access 2007 In the current 2003 MDB, using Access 2003 the code to set subform ... A: No its not, I'm using a . instead of a !. Did you try it that way? The Invalid reference message ...
MS Access - Search Box	9/15/2009
Q: I'm creating a database in MS Access 2003... I haven't used Access in a long time and am rusty. I ... A: Its possible, but it would require some complex coding to do a full text search of every field in ...
Table Design	9/15/2009
Q: I am trying to design a database around a form we currently use. The form requires 44 pieces of ... A: First, its not a good idea to design a database around a form. Forms are designed based on the table ...
MS Access - Security Problem	9/15/2009
Q: 1. I am a novice in MS Access 2. I created a database and a security file using a wizard. ... A: It was a long shot. A couple of points. Once you get it reconstructed, split the database into front ...
forming executable for ms access to instal it any where.	9/15/2009
Q: how can i form an executable for a project in ms access and instal it any where, even on pcs without ... A: You can't create an executable. Because the Access file can contain both data and design, it can't ...
MS Access 2003 Shrink form to a pop up window and create path to go from a report back to switchboard	9/14/2009
Q: I have a page that has several buttons that when you press a button it directs you to a form. When ... A: If you set your forms to appear maximized, then all the forms that are subsequently opened will also ...
storing data from user form into log table	9/14/2009
Q: i am trying to find out how to track users info ,logged to my access database by recording the data ... A: Depends on your work flow. You "bind" a table to a form by setting the table as the form's ...
Printing a report from a Form	9/14/2009
Q: When I click the “Print Report” button (from the Form)and enter the job# and ID# the info does not ... A: If its prompting you for a Job # and ID #, then you have those fields on your report or in your ...
Calendar in access 2007	9/14/2009
Q: I need to develop a calendar form in Access 2007 in which the user can click on a specific date and ... A: Access 2007, by default, shows a date picker for any textbox control formatted as a date. So when ...
Randomly Pick Unused Alphanumeric From Table	9/13/2009
Q: I want to have the user able to click a command button and have it automatically generate a ... A: Use a Dlookup(). Hope this helps, Scott<> Microsoft Access MVP 2007 Author: Microsoft Office Access ...
MS Access - Security Problem	9/13/2009
Q: 1. I am a novice in MS Access 2. I created a database and a security file using a wizard. ... A: I'm assuming you tried to recover the mdw from the Recycle bin? Did you use system.mdw as your ...
VBA to lookup matching table values	9/13/2009
Q: Scott: I have a routine that is supposed to look up multiple values in a lookup table called ... A: OK, then that's your problem. Try it this way: ntPatternMatchID = DLookup("[PatternID]", _ ...
VB run report and download to flat file	9/12/2009
Q: I think I'm doing too much tinkering using macros when I'm sure VB could do this more efficiently ... A: First, with Access, you use VBA (Visual Basic for Applications) which is a superset of the Visual ...
Randomly Pick Unused Alphanumeric From Table	9/12/2009
Q: I want to have the user able to click a command button and have it automatically generate a ... A: Use the RND() function to generate a Random number. You want to generate the number within a range ...
Printing a report from a Form	9/11/2009
Q: When I click the “Print Report” button (from the Form)and enter the job# and ID# the info does not ... A: Sorry, I haven't been more available to you, but I've been spending as much time as I can generating ...
Open form to new record	9/11/2009
Q: I have designed a database with multiple forms and currently, when the forms are opened, they open ... A: Two ways. If you set the Data Entry property of the form to Yes, the form can ONLY be used to add ...
Access 2007 - Command Button	9/11/2009
Q: I have a form that hosts a subform. The subform is a list of students for a particular class. I am ... A: Nope all you need is this code: DoCmd.OpenForm "formname",,,"[StudentID] = " & Me.studentID in the ...
Current Month and Cumulative Month total in MS Access Reports or Query	9/11/2009
Q: Sir, I have a database like this : Employee Number and Net salary for the months Net_01 = January, ... A: Your database is not normalized properly. Normalization is a KEY database design concept for ...
Printing Select Lables	9/11/2009
Q: Hope this mail finds you in good health. I have a contact interface that i have created in Access. ... A: In the DoCmd.OpenReport method, one of the parameters is to preview the report. From the preview ...
Access	9/11/2009
Q: Hey I was wondering if you oculd help me abit, i'm jsut starting my A level ICT coursework and i'm ... A: 1) You don't. Data should not exist in more than one table. There are two exceptions to this. First ...
How to design my database and Application	9/11/2009
Q: I have recently assigned to make a database for my company's project. We are Telecom based company ... A: Visio shapes can be linked to database records so that when you click on one, data linked with the ...
Update date	9/11/2009
Q: I have a question concerning a date update.In access i made a form with several command buttons to ... A: Don't use the brackets around the report name. Access is looking for a string value there. Unless ...
many to many relation	9/11/2009
Q: I've a table of Products (name, unit, price, .. etc) and a table for components (name, quantity, ... A: Can a component be used in multiple Products? If so, then you do have a many to many. You should ...
Report Title	9/10/2009
Q: Users choose an item from a drop down list on a form, and then press a button which runs the query ... A: Add a text box to the form where you want the Title. Set the Controlsource of the textbox to: ...
MS-Access	9/10/2009
Q: In a Macro there is 50 Query, In between Running The Macro, how will I know which Query is actually ... A: Why would you want to? And why would you be running 50 queries in a batch? What do these queries do? ...
Copying Rows	9/10/2009
Q: I have a database that tracks serial numbers. The fields are "Lens", "Serial", "Date", "Assembler". ... A: If you copied the code I gave you exactly it won't work unless you have the exact same field and ...
Printing Select Lables	9/10/2009
Q: Hope this mail finds you in good health. I have a contact interface that i have created in Access. ... A: Actually I answered the question, the problem is your code. Jeff is a friend of mine, but that code ...
Multiple tables in one report	9/9/2009
Q: I have multiple tables, 12 to be exact, that I need to combine into one report. I tried using all ... A: First, your database is not structured properly. There is NO need for separate tables. All you need ...
Need help creating a query that doen't double count.	9/9/2009
Q: I am looking for help with creating a query in Access 2007 that allows me to look up net booking or ... A: Try this: 1) Create a query where status = "booked" 2) Create a query where status = "cancelled" 3) ...
Copying Rows	9/9/2009
Q: I have a database that tracks serial numbers. The fields are "Lens", "Serial", "Date", "Assembler". ... A: On the form have two unbound controls; txtCopies and txtLastSN. Add a button to generate copies. The ...
comparison as a result of IIF function	9/9/2009
Q: In the query,The criteria <=[Forms]![ReportForm]![EndConditionDate]) works as standalone but it ... A: I try not to use an IIF in a WHERE clause, it rarely works. I'm not clear what you are trying to do ...
Linking Forms	9/9/2009
Q: I am using MS Access 2003 and I have a form with a sub form and a tabbed form on it. I want to link ... A: If the subform is linked to the parent form on SSN, then the SSN will automatically be populated ...
Accessing the data	9/9/2009
Q: I have 3 tables A is transaction table, B is having Country Field, C table is having Country & City ... A: You need to reference the control on your form, not a field from a table. Also the reason you are ...
Working access for inventory control.	9/9/2009
Q: I been reading most of this pages, but I don;t think I have been able to get an answer to my ... A: Of course you can. The question of HOW to do it depends on your structure. If your application ...
Incremental counter not working when reopening form	9/9/2009
Q: I'm using Access 2007 and I have been using Access for a few years but with pretty basic databases ... A: Hmm, You are saying, that duplicate Client records were created? You checked the table to confirm ...
Adding a record	9/9/2009
Q: I created a form based on a table. It's a form with a subform. On the main form the user inputs the ... A: Try this. In the On Current Event of the form use code like this: If Me.Record Then Me.BeginTons = ...
Frozen navigation pane	9/9/2009
Q: The navigation pane in one of my databases is frozen so it won't resize. I can still access the ... A: Did you try pressing F11 to toggle the nav pane? If that doesn't work, the only other thing I can ...
Linking Forms	9/8/2009
Q: I am using MS Access 2003 and I have a form with a sub form and a tabbed form on it. I want to link ... A: How are the forms currently linked? Is the tabbed form a subform on the subform? Are you creating ...
Access 2007 - Command Button	9/8/2009
Q: I have a form that hosts a subform. The subform is a list of students for a particular class. I am ... A: Then you should take a relook at your PK field. Its not a good idea to use a text field in joins. ...
Laying out Database	9/8/2009
Q: I am currently working for a produce farm. We have 4 different crops. We need to keep track of ... A: First, has your instructor gone over normalization yet? If not, you need to get a good grasp on the ...
Complex query!!	9/8/2009
Q: how are you? I am ask to do a query but have tried and no success! Please help, i appreciated your ... A: 1. I'm confused. Your output should have one row for each value in Pubfield. So OtherSource should ...
Loop function	9/8/2009
Q: I am a university student, on work experience creating a database for a company. The purpose of the ... A: In Access help when you look up Multi-Select, there is an example of looping through the ...
Time problem in access 2003	9/8/2009
Q: I am trying to make a simple database example to show empoyee time tracking . I have ... A: First, you have an issue with your structure. I know you said you are trying to keep it simple, but ...
about your query?	9/7/2009
Q: I want to ask you about the query that you send it to me yesterday. SELECT StudentID, FName, LName ... A: First, I didn't say a "second" form, I said a SUBFORM. A Subform is a form embedded in a parent ...
Rank changes in a priority list	9/7/2009
Q: I was hoping you could help me with an access problem I have. I have a database containing a list of ... A: Yes its possible. If you want to move them up to the top of the list use code like this: Dim db As ...
about your query?	9/7/2009
Q: I want to ask you about the query that you send it to me yesterday. SELECT StudentID, FName, LName ... A: Q1/ Either write it directly or build it in Query Design mode. In Query Design mode select the two ...
History Tables to Active Tables	9/6/2009
Q: I have 3 history tables and 3 Current tables that contain Student Data, Table 1 All student info PK ... A: Do a WEB search on ACCESS AUDIT TRAIL. There are several sample databases that implement this ...
History Tables to Active Tables	9/6/2009
Q: I have 3 history tables and 3 Current tables that contain Student Data, Table 1 All student info PK ... A: Frankly, unless you have a huge number of students (in 6 figures) I would not have history tables. I ...
ABOUT MAIL MERGE??	9/6/2009
Q: there no space in the old question, so I have to write a new one. you said "You can use this query ... A: First create the query as I suggested so it display the students for one adviser. Then use the Mail ...
About record number??	9/6/2009
Q: I want to ask you about the record serial number in the bottom of the from that give each record his ... A: Again, you seem to have this fixation on Record Number that is inaccurate. What you NEED is the ...
About record number??	9/6/2009
Q: I want to ask you about the record serial number in the bottom of the from that give each record his ... A: Its clear what you want, but its not clear WHY you want it. If you need a unique identifier to enter ...
ms access form	9/5/2009
Q: i'm making a form to record to keep track with mail to my home office. i have 2 tables, one for ... A: You need more tables. What you need is a database structure like the following: tblCompany ...
About record number??	9/5/2009
Q: I want to ask you about the record serial number in the bottom of the from that give each record his ... A: Ahh now I understand. You can't make uses of them since they are not tied to the record they depend ...
The record number ??	9/5/2009
Q: its me again :) about the record serial numbers I mean this numbers: ... A: The screen shot doesn't show what you mean. But it does show you may be using a bootleg copy of ...
about report ??	9/5/2009
Q: I want to ask about the report in access 2007. I have two tables in two forms. one for the student ... A: First, in my previous answer, I mentioned about names. Unless you are only storing a first name you ...
MS Access Replace function to replace a part of a string after a substring	9/5/2009
Q: I need to use the Replace Function (MS Access) in a query to replace the last part of a string in a ... A: Try this: Value = Left(Value,Instr(1,Value,"/")-1) & NewValue Value is the text string you want to ...
New Stock Orders and Stock In Storage	9/5/2009
Q: This is a follow-up question from the the subject New Stock Orders and Stock in Storage. The ... A: How do you know it doesn't decrease? As I have said, Stock on Hand is a CALCULATION. If you have a ...
Question about auto number ??	9/4/2009
Q: I want to ask you about one of the data type in access 2007 (the auto number). I have three columns ... A: Why is it a problem? An autonumber has ONE purpose only, to uniquely identify a record. Your ...
Autonumber Format	9/4/2009
Q: Scott, You had answered a question about Autonumber format back on 7/1/2006. **Past Question I am ... A: Did you use the EXACT same code as my example? If so, did you name your fields and controls the same ...
Complex query!!	9/4/2009
Q: how are you? I am ask to do a query but have tried and no success! Please help, i appreciated your ... A: Did you try using a Crosstab? If you followed my instructions for setting up the crosstab, you might ...
Access 2007 - Command Button	9/4/2009
Q: I have a form that hosts a subform. The subform is a list of students for a particular class. I am ... A: First, I rarely use macros I find then very limiting and harder to debug. What you want is done VERY ...
Select & Record Count	9/4/2009
Q: I have a qry which pulls records from a table for a specific date (time period). I need to create ... A: Ok, this is a survey. Is that your query or the underlying table? What do the numbers mean? A survey ...
Select & Record Count	9/4/2009
Q: I have a qry which pulls records from a table for a specific date (time period). I need to create ... A: What do you mean you have 16 fields? 16 different text fields that you need counts for the results? ...
Select & Record Count	9/4/2009
Q: I have a qry which pulls records from a table for a specific date (time period). I need to create ... A: Do this in your query, not the report. What you do is a Group By query. In Query Design mode, first ...
New Orders and Stock in Storage	9/4/2009
Q: First, here is my database structure, which might help you answer my questions and problems. ... A: In an order processing application, you have two tables, the Order table which contains data ...
Filtered Field	9/4/2009
Q: I have a subform bound to a transactions table. What I want is that the subform's transactiontypeid ... A: Ahh you should have specified that you use the same subform for different main forms. My answer ...
MS Access 2003 selective searches	9/3/2009
Q: you have helped me out in the past and am hoping you can get me out of this hole that I am in. I am ... A: You can show me the SQL for the query and let me see what it looks like. Also what about it doesn't ...
Complex query!!	9/3/2009
Q: how are you? I am ask to do a query but have tried and no success! Please help, i appreciated your ... A: Doesn't look like you followed my instructions. I said to set Pubfiled as the Row heading and ...
Complex query!!	9/3/2009
Q: how are you? I am ask to do a query but have tried and no success! Please help, i appreciated your ... A: First, NOT LIKE "*" excludes everything since the asterisk is a wild card. I think you need to use ...
value increment onclick, Access 2007	9/3/2009
Q: Hey Scott - assuming you're not sick of giving me advice: Is it possible to have a value increase ... A: I doubt if anything you can tell me would make me change my mind. I will ALWAYS go for the more ...
Filtered Field	9/3/2009
Q: I have a subform bound to a transactions table. What I want is that the subform's transactiontypeid ... A: First, create a query that includes all the fields for the subform, then make that the Recordsource ...
Printing Select Lables	9/3/2009
Q: Hope this mail finds you in good health. I have a contact interface that i have created in Access. ... A: If you are creating a filter on a form and you want to apply that filter to a report (a label is a ...
Access form/app help!?	9/3/2009
Q: Ok, I'm trying to create a form that is way over my head, and I need to know if its possible. I'm ... A: 'Me' is a shortcut that refers to the currently active form, it stands for Forms.formname. What ...
New Orders and Stock in Storage	9/3/2009
Q: First, here is my database structure, which might help you answer my questions and problems. ... A: First, it still doesn't make sense to have OrderID in the Products table. If you are adding a new ...
Access form/app help!?	9/3/2009
Q: Ok, I'm trying to create a form that is way over my head, and I need to know if its possible. I'm ... A: So leave out the call number. Otherwise I would not advise you to follow through on your table ...
Access form/app help!?	9/2/2009
Q: Ok, I'm trying to create a form that is way over my head, and I need to know if its possible. I'm ... A: Yes you can do this, but your approach is flawed. You do not need three tables and I would not ...
Duplicate Data Results -- Unbound Text Box	9/2/2009
Q: Although I have created many databases with little training, I do not fully know how to customize ... A: No, I'm afraid your table is not created the way I suggested. You do not define data by using field ...
Reducing UnitsInStock Additional Information	9/2/2009
Q: you have set me well on my way. concerning your questions QuantityInUnit would be 20pks per box or ... A: So, if I'm following you General Product Info contains additional info about each product. However, ...
New Orders and Stock in Storage	9/2/2009
Q: First, here is my database structure, which might help you answer my questions and problems. ... A: First, why is OrderID a FK in TProduct? ProductID is a FK in TTransaction, which also has an FK of ...
Fixed Page Size	9/2/2009
Q: I have developed one project with MsAccess. It has 6-7 reports. I have fixed reports size in design ... A: The problem is probably due to different default printers. I'm assuming you have it set to print to ...
Combo-box filter	9/2/2009
Q: I have a question concerning a combo-box. I created one: it helps to go to the specific record in ... A: Use a filter instead. Private Sub Combo18_AfterUpdate() Me.Filter = "[Categorypub] = " & ...
DayOff and Absent Day	9/2/2009
Q: you helped me to adjust time in and time out for the workers and also calculated time worked and ... A: The way I would do this is determine the number of days in the month, then do a count of days where ...
Reducing UnitsInStock	9/2/2009
Q: I'm building a stock inventory database using Access 2003. I'm having problems completing my reorder ... A: Why do you have these three fields in your stocks table: QuantityInUnit,UnitsUsed,UnitsInStock? As I ...
ActiveForm in a Macro	9/2/2009
Q: I am trying to get a macro to use the active form as the "Item." The current "item" in the macro is ... A: I don't think you can do this in a macro. However, it's easy in VBA. Just use Me.UseAltVendor. ...
Duplicate Data Results -- Unbound Text Box	9/1/2009
Q: Although I have created many databases with little training, I do not fully know how to customize ... A: First, your database is not properly normalized. I'm not completely following what your fieds ...
Forecasting sales 13 weeks out	9/1/2009
Q: Scott, I am looking to forecast sales out thirteen weeks. I have the code that you wrote on a ... A: I added that bit about it being the PK after I had almost finished the response. Access, by default, ...
Forecasting sales 13 weeks out	9/1/2009
Q: Scott, I am looking to forecast sales out thirteen weeks. I have the code that you wrote on a ... A: Ok, First. ContractID is the name of the field in tblTemp, correct? And ID is the name of the ...
Delete Query	9/1/2009
Q: I am trying to use a delete query to delete a record. The only thing is that it did not work for ... A: DELETE * FROM tbl_S7TeacherComments WHERE StudentID IN(SELECT StudentID FROM tbl_S1Info WHERE Status ...
IIF Function - Access 2007	9/1/2009
Q: I am trying to setup an IIF Function in a Query for a Gradebook. If the student receives 80%+, they ... A: Try it this way: HGrade: ...
I AM ILL IN MS ACCESS	9/1/2009
Q: Pls, sir i wish to do this! i have a database table with fields like name, address, telephone ... A: Actually you do NOT want to do this. As a general rule we do NOT store calculated values and age is ...
Query from two tables with no field in common	9/1/2009
Q: I'm new to Access and I need some help. I'm trying to create a query that will pull data from two ... A: First, you cannot join two tables unless there is some common element. Second Date is a reserved ...
Reducing UnitsInStock	9/1/2009
Q: I'm building a stock inventory database using Access 2003. I'm having problems completing my reorder ... A: This doesn't really answer my question. It would help if you were more specific about how you will ...
Forecasting sales 13 weeks out	8/31/2009
Q: Scott, I am looking to forecast sales out thirteen weeks. I have the code that you wrote on a ... A: Your SQL is not what I suggested. You are missing several issues. The code I gave you was this: For ...
Reducing UnitsInStock	8/31/2009
Q: I'm building a stock inventory database using Access 2003. I'm having problems completing my reorder ... A: Before I answer this, I need to know how you record stock movement. Do you record each purchase of ...
Splitting up Cash and Check Amount	8/31/2009
Q: I have a sample attached of a Point of Sale Form that i make. In Check and Cash I enter the amount ... A: I'm assuming you have separate fields for cash and check amounts. So you have three controls in the ...
re	8/31/2009
Q: sir what is components of the ms. access screen A: What do you mean by Access "screen"? When you first open Access, you see a menu bar (ribbon in ...
Employee Worker Timeclock	8/30/2009
Q: So I'm making a sign in sign out time punch for employees so all they have to do is type their ... A: The code behind the two buttons should look like this: Sign In: Dim strSQL As String strSQL = ...
Intro to ACCESS	8/30/2009
Q: Typically our office uses Excel spreadsheets - simply because none of us know ACCESS. However I ... A: I'm really not sure what you mean by the "logic". But let me try to explain these products. Word is ...
filter from	8/30/2009
Q: .. I've a form of products which contain a sub form of product details the sub form contains ... A: Create a Query using the table that is the source of the subform (If it isn't a query already). In ...
Display data from a query in a form for reference	8/29/2009
Q: I'd like to display data from a field of a query in a form (for reference only). Every time our ... A: Use a DCount() expression as the Controslource of a textbox. Something like: ...
Forecasting Sales (Weekly)	8/28/2009
Q: I am newer to Access 2007, so I apologize in advance for any shortcomings. I have a sales ... A: First, as a general rule we do NOT store calculated values so this data should not be stored. All ...
Access Linked Tables	8/28/2009
Q: Is there a way to have access automatically update tables linked through ODBC connections? A: Umm that's the definition of a linked table. Linked tables are automatically updated when the data ...
setting up database for survey responses	8/28/2009
Q: I've got database that I'm using to capture a rather lengthy survey. I 've got my tables structured ... A: You need at least THREE tables here. One table is your list of questions. The second table is your ...
Linking MS Office to MS Access	8/28/2009
Q: Sir, we are trying to set up a system where we can input data in Word and have it populate a table ... A: Its possible, but it would require a highly formatted document in Word with fields for each piece of ...
Forecasting Sales (Weekly)	8/28/2009
Q: I am newer to Access 2007, so I apologize in advance for any shortcomings. I have a sales ... A: Again the formula is the key here. What you want is doable, but I would need to understand how you ...
security	8/27/2009
Q: How would I impement the security with VBA? Answer There are lots of ways. Generally I have a users ... A: You assign the level based on what you want them to have access to. For example, in one app I have a ...
user level security on access 2007.	8/27/2009
Q: I have an access database saved as an .mdb format. We are running Office 2007 and I would like to ... A: There are lots of ways. Generally I have a users table and assign a user level to each user. When ...
user level security on access 2007.	8/27/2009
Q: I have an access database saved as an .mdb format. We are running Office 2007 and I would like to ... A: Set your main menu form as the default to load at startup, then have buttons to return to the main ...
Linking AutoCAD block into access report	8/27/2009
Q: I have seen considerable postings on this subject in general, but most relate to linking a ... A: The bloat comes from linking an OLE datatype to an image. Each record would have the link to the ...
user level security on access 2007.	8/27/2009
Q: I have an access database saved as an .mdb format. We are running Office 2007 and I would like to ... A: Access 2007 does not provide great support for ULS. You definitely can't use it with the ACCDB ...
Linking AutoCAD block into access report	8/27/2009
Q: I have seen considerable postings on this subject in general, but most relate to linking a ... A: If you are using an OLE block, this will cause your database to bloat. But if you need to link to a ...
Front end MDE	8/27/2009
Q: I have split my database into a front-end and back-end. The front-end is an .MDE file that I ... A: Unfortunately you can't fully protect them. But users should know not to fool around with design. If ...
Inventory System	8/26/2009
Q: I am trying to make the inventory system you recommended by studying the answers you gave in the ... A: First, there are some problems with your structure. TTransaction should look like this: ...
Creation of fields during runtime ?	8/26/2009
Q: I'm developing an application in Visual Basic with MS Access database. Is it possible to add fields ... A: The answer is yes. You can use SQL, DAO or ADO to alter tables. However, you would have to do so ...
Combine different fields into single record	8/26/2009
Q: Is it possible to take two (or more) records that differ by one field and combine them into a single ... A: This is called grouping. The problem here is that you need to structure this properly. Your database ...
yes/no fields	8/25/2009
Q: I am hoping you can help. I have been set the task amending an existing database and I have done ... A: You made perfect sense, the problem is that the database was not normalized properly. And that is ...
Page Break within report	8/25/2009
Q: I am trying to create a page break in a report in MS Access 97. I have a number of data entries per ... A: Sorry, had a brain fart there. The Page Header automatically repeats on each page. That's why its ...
Migrating two access databases	8/25/2009
Q: I have two access databases which I need to migrate into one. One of the databases is setup with a ... A: If you need to get data from a table in one database into another, then link the table from the ...
MS Access '97 Macro	8/25/2009
Q: Scott, thanks for your help. I know how to do the critera in the queries, but I really wanted to ... A: I think you have misunderstood what I have advised. Either that or your structure is incorrect. The ...
Giving a report a caption at print time	8/24/2009
Q: I want to change the title of a report as it is printed. The reason for this is that I am printing ... A: The real problem was the event you used. As I said it should be On Open, not On Activate. However, ...
Page Break within report	8/24/2009
Q: I am trying to create a page break in a report in MS Access 97. I have a number of data entries per ... A: This is neither a macro or VBA issue. What I would do is group your report on Date. Include a Group ...
IF statements in Access	8/24/2009
Q: I am creating a report based on a query with a dollar amount, either null, negative or positive (the ... A: I wouldn't use an IF. First, I would use a query for the RecordSource of your report. In that query ...
Giving a report a caption at print time	8/24/2009
Q: I want to change the title of a report as it is printed. The reason for this is that I am printing ... A: OK, Several issues. 1) Name is a reserved word in Access and should not be used for object names. ...
Giving a report a caption at print time	8/24/2009
Q: I want to change the title of a report as it is printed. The reason for this is that I am printing ... A: I need some clarification here. First, the lines of code you show are they from an event on ...
Notes in Access Form	8/24/2009
Q: Scott I’ve build a database that is being used as a means to track new contracts from received to ... A: When the Data Entry property is set to YES, the form can ONLY be used to enter NEW data. That's why ...
MS Access '97 Macro	8/24/2009
Q: I have a form with three option to select many reports. (1) report types, (2) report names & (3) ... A: You're not a problem. Its not uncommon for someone to not know exactly how to word the questions. ...
MS Access '97 Macro	8/22/2009
Q: I have a form with three option to select many reports. (1) report types, (2) report names & (3) ... A: You not need a macro here. but I need to get clarification of two items. When you refer to report ...
Submit HTML form in access database	8/21/2009
Q: .I am new to both HTML and MS Access. I have created a form in HTML (no check box, no radio ... A: How is the selection stored? When a viewer makes a choice on an HTML page there has to be some ...
Generate Alphanumeric ID	8/21/2009
Q: I have seen a similar question/response from you, but mine is different, and I just can't find the ... A: Again, NO you do NOT need to store this number with the V. If I understand you correctly you only ...
Command Button to open Hyperlink to a PDF file	8/21/2009
Q: I have a access database with the following tables tblcustomer tlbWorkorder id in the tblworkorder ... A: Application.FollowHyperlink Me.WorkOrderImage That's all you need. However, if your naming ...
Notes in Access Form	8/21/2009
Q: Scott I’ve build a database that is being used as a means to track new contracts from received to ... A: The data Entry property should be set to NO unless you want to use the form only for data entry. ...
Generate Alphanumeric ID	8/20/2009
Q: I have seen a similar question/response from you, but mine is different, and I just can't find the ... A: No, yours is not any different from the several other questions like this that I have answered. The ...
Mail Merge - Labels	8/20/2009
Q: I have a new one I am currently working on. I have access front end, sql back end database. A user ... A: Exactly. When you use the wizard to create labels, you set the size of the label. So when you name ...
Combo Box Search Criteria to return within date range	8/20/2009
Q: I have a search form to return records based on a keyword (which works fine) and a combo box to ... A: Change it to: -11;1 day;-77;7 days;-30;30 days;-365;365 days Then set the bound column to 1, the ...
Continued:Conditional formatting for numbers that are stored in a text field datatype	8/20/2009
Q: It works great, though there is just a few minor problems, one is a type mismatch error that happens ... A: A data type mismatch means that you are trying to assign a value of one datatype to a variable of a ...
Printing Reports from Filter By Form	8/20/2009
Q: I am using Me.Filter with the Filter by Form mode to open a report. Here is my code which is used to ... A: Try this way: Dim strSQL As Sttring stDocName = "R_Requests_Full_List" If iFilterType = 0 Then ...
Navigation Buttons used in Tab Control	8/19/2009
Q: I’m using a Tab Control to display a record set in two ways. The first tab has a subform in ... A: I would use a popup form rather than putting textboxes on a tab. If you don't want to use a popup, ...
Micrsoft Access 2007 - Data Entry Form	8/19/2009
Q: I have created a database to store and report upon customer survey responses and am having trouble ... A: The code would be behind a button with a label like "Generate Questions". The 3 ID fields in the ...
Validate entry to Forms based on values on different table	8/19/2009
Q: I saw this answer that you gave to somebody asking a similar question in February 2006 I believe: ... A: I would do this differently. You really need two validations here. First you need to validate that ...
Mail Merge - Labels	8/19/2009
Q: I have a new one I am currently working on. I have access front end, sql back end database. A user ... A: Umm, why are you doing this in Word? Why not just create a label report in Access? A label is ...
Conditional formatting for numbers that are stored in a text field datatype	8/19/2009
Q: Good afternoon Scott, I was wondering if you would happen to know of a way to be able to use ... A: No, you can't use the Excel CODE() function. But you do need to add a test to the code. Dim ...
Date Query	8/19/2009
Q: I have setup an HR database, a part of it especially for sickness, we total individuals sick on a ... A: Well, first of all, you shouldn't be storing The amount remaining. This is a calculated value and ...
Forms and queries	8/19/2009
Q: I have a table, say "Table1" which has as one of its fields "Field1". I have done a query on that ... A: It sounds like you are using continuous form mode with an unbound control. Continuous form mode is ...
How do I link a image in MS Access from a folder?	8/19/2009
Q: I would like to ask a follow-up to the question "How do I link a image in MS Access from a folder?". ... A: You are doing a report, correct? The Detail_format event is for a report, not a form. On forms and ...
Query distinct field	8/19/2009
Q: Using Access 2003,XP OS I am trying to run a query from a table (See the data below). I want to run ... A: First, Access certainly does support the DISTINCT keyword. The problem for you is that you don't ...
Update error	8/19/2009
Q: You have been very helpful to me in the past with my Access project. My current problem is a 3061 ... A: Where are you defining str3SQL. Is it before or after you define varModID. Also try concatenating it ...
Micrsoft Access 2007 - Data Entry Form	8/18/2009
Q: I have created a database to store and report upon customer survey responses and am having trouble ... A: A questionnaire database has a very specific structure. It looks like your structure might be OK, ...
Conditional formatting for numbers that are stored in a text field datatype	8/18/2009
Q: Good afternoon Scott, I was wondering if you would happen to know of a way to be able to use ... A: Your MsgBox code is close. I would use: Dim intTarget As Integer Dim intAudit As Integer intTarget ...
Conditional formatting for numbers that are stored in a text field datatype	8/18/2009
Q: Good afternoon Scott, I was wondering if you would happen to know of a way to be able to use ... A: Let me see if I follow you. You have a text control bound to the field AuditTarget, which is a text ...
Button to preview specific records in total Report	8/18/2009
Q: I've a report based on total query, in which the date formatted by month, I want to make a simple ... A: Move the Invoice Date to the second colum. All your GROUP BYs should be in the leading columns. Also ...
Date Query	8/18/2009
Q: I have setup an HR database, a part of it especially for sickness, we total individuals sick on a ... A: One way around it is to generate a record for each date So after you enter a To and From date you ...
Re:MS Access Database	8/18/2009
Q: I took your advice and set up my database just like you said but I don't know what types of ... A: Yep, that's the way you should work it. I would be glad to "get you on the right track", but that ...
Color text and export	8/18/2009
Q: I have 2 parts to my query. 1. I want to change the color of the text or the background color of ... A: You can't export with the colors. The conditional formatting is part of a form or report that will ...
Button to preview specific records in total Report	8/18/2009
Q: I've a report based on total query, in which the date formatted by month, I want to make a simple ... A: There are a couple of ways to do this. One way would be to have two textboxes, named txtStart and ...
Filter By Form - Filter Not Clearing	8/18/2009
Q: I am using the Filter By Form function in my Microsoft Access 2003 database. I have the code set so ... A: I've encountered that bug. First, you need to disable the Close box on the form and add your own ...
Open Records Related to Combo Box List	8/18/2009
Q: On a form I have a combo box based on a union query. There is a command button with the following ... A: I'm a little confused. First you don't need a separate button. You can put your code in the After ...
Removing Starting letters	8/18/2009
Q: This is my first question in this website and I hope I will be benefited from this. I have the ... A: Then why did you rate my knowledge so low? I can almost understand the clarity rating, but not the ...
Database Structuration	8/18/2009
Q: Could you explain me what are the most important rules when creating a database of any type (in easy ... A: Excel is a spreadsheet. Like any spreadsheet it can create a flat data file. But a flat data file is ...
Removing Starting letters	8/17/2009
Q: This is my first question in this website and I hope I will be benefited from this. I have the ... A: So split up the data. I still think that will be easier and faster. But you would use a For...Next ...
Re:MS Access Database	8/17/2009
Q: I took your advice and set up my database just like you said but I don't know what types of ... A: Each record in tblAnswers is a potential answer to the question. Question that are true/false or ...
Removing Starting letters	8/16/2009
Q: This is my first question in this website and I hope I will be benefited from this. I have the ... A: The only way I can see doing this is with a function that loops through the string identifying the ...
Access Fundraising Database	8/16/2009
Q: I teach at a parochial school which relies on an annual fundraiser to offset costs. Donations are ... A: First making combos are easy. Just use the combobox wizard. For example if you want to select a ...
Re:MS Access Database	8/16/2009
Q: I took your advice and set up my database just like you said but I don't know what types of ... A: Not sure what you mean. You should know what the answers to the questions should be. Basically you ...
time elapsed problem in Access 2007	8/16/2009
Q: I'm working in database for workers time in and out, it assumes that I input Start time and end time ... A: Access stores date/time values as a double precision number where the integer portion is the number ...
MS ACCESS: How to get the first 3 letters of product description?	8/16/2009
Q: there! I have a table named Products and there is a field called Product Description e.g. Product ... A: Doing the 1 word is easy. The first IF checks to see if there is more than one word by looking for a ...
Access Fundraising Database	8/15/2009
Q: I teach at a parochial school which relies on an annual fundraiser to offset costs. Donations are ... A: First, its not absolutely necessary to formally setup relationships, but what you did was fine. ...
Calculate number of days excluding weekends and holidays	8/15/2009
Q: We need to provide the number of days it took a task to be completed. Right now we can provide ... A: This is the function I use to calculate net workdays between two dates: Public Function ...
Update results to table	8/15/2009
Q: I have 2 tables: - Table 1 has field QOH - Table 2 has field Ordered I want each time creat a ... A: Read what I said. You DO NOT store this value. So there is no need for the field at all. The ...
MS ACCESS: How to get the first 3 letters of product description?	8/15/2009
Q: there! I have a table named Products and there is a field called Product Description e.g. Product ... A: The problem here is you don't have much of a pattern. A Description could be one word, 2 words or ...
Difficulty with Calculated Field on Form	8/14/2009
Q: I have a form called ProjectInformation based on Qry_ProjectInfo. The form contains a lookup combo ... A: You are close. Try: =NZ(DSum("[Sub CO Amount]","[Sub CO Log]","[Subcontractor Name]='" & ...
Re: Questionnaire	8/14/2009
Q: I am trying to create a MS Access Database for use in providing answers to a Questionnaire that my ... A: First, way too much info. I just need to see the structure, not the contents. And with all that, ...
Update results to table	8/14/2009
Q: I have 2 tables: - Table 1 has field QOH - Table 2 has field Ordered I want each time creat a ... A: The answer is that you don't. The best way to deal with QOH is to keep a record of ALL movement of ...
Access Fundraising Database	8/13/2009
Q: I teach at a parochial school which relies on an annual fundraiser to offset costs. Donations are ... A: I would setup your database this way: tblHousehold HouseholdID (Primary Key Autonumber) ...
Exporting Reports/Querying/I don't know	8/13/2009
Q: Using Windows XP, Access 2007 My company uses management software that organizes customers based on ... A: There are several ways to export data to Excel. On the External Data ribbon you can chose to export ...
IIF statements in Access Reports	8/13/2009
Q: I have two other questions for you: Question 1: I have a field in a query “Add-Repl”, this field is ... A: Q1: I would do the calculation in your query rather than on the report. So I would add a column: ...
VBA for Access printing	8/13/2009
Q: i would like to print a 2 pages per sheet report. Pls help. My code is as follow. DoCmd.OpenReport ... A: Now I understand. Plug this into Access VBA Help: Programmatically Retrieve Printer Capabilities ...
Date update report	8/11/2009
Q: A while ago I asked a question as to how to show the date a report was last modified on the main ... A: yes all you need is to supply the actual Report name. No you don't need to add anything to a table. ...
adobe acrobat	8/11/2009
Q: I am the one who asked you about the code of the vba for MS access you asked me about MS access ... A: The only thing you need from that code is the ImportXML line. What you need to do is create a table ...
Expressions that compare fields	8/10/2009
Q: I am using Access 2007 to import a csv file that will change weekly. The csv holds data on test ... A: Try adding a column with this expression: WithinRange: IIF(F2<F1 AND F3>F1,-1,0) If you format ...
Replication	8/10/2009
Q: I am trying to find a good way to share our Inventory Database over the WAN. I split the db into fe ... A: Is the front end local and just the backed on the network? Have you spoken to the network admins ...
Access report prompt	8/10/2009
Q: I'm working in Access 2007. I have a billing database to which I've just added a 'history' table ... A: The best way to do this is through a form. Create an unbound form with two textboxes on it (txtStart ...
Search box	8/10/2009
Q: I have create a search box on a form that the user can search by last name. The problem is that ... A: Make this easier on yourself. Access has the ability to make a search combo as part of the combobox ...
Updating Field Values Using a Macro	8/10/2009
Q: .. I'm trying to update a field in a table using a macro and multiple conditions. So my question ... A: Yes and no. I don't use macros because they are too limiting. And I believe this is one of the ...
ACCESS productivity database	8/10/2009
Q: I need to create a database with weekly productivity statistics such as total picks, total cases, ... A: This medium is not suited to a general tutorial. Its more for asking specific questions. So your ...
Execute Apply Sort/Filter Command	8/10/2009
Q: I am attempting to cater to very non-technical users by adding a button to my form which invokes the ... A: I've run into the same issue. I've solved it in two ways. I've used a custom menu bar attached to ...
Pivot Chart in Access 07	8/10/2009
Q: They all run off one query. The query however has a date criteria (like parameter) and when I switch ... A: Add an unbound control to the form (in the header) to enter the date criteria. Then reference that ...
Inventory System	8/10/2009
Q: I am trying to make the inventory system you recommended by studying the answers you gave in the ... A: We are getting closer, but there is NO need for multiple data entry. What you do is use the ...
Access 2007 forms	8/10/2009
Q: I need help! I am trying to create a student database with different tests scores in separate ... A: Why are you using separate tables? There should be no need for separate tables. If you need to ...
VB Codes	8/9/2009
Q: "This is what I would like to do...I would like to compare data from certain dates, with other ... A: What you need is a table of your ranges with a sequential number assigned to each range. You then ...
how to link ?	8/9/2009
Q: i need help about the forms in adobe acrobat professional and MS access , if i have a form in ... A: The answer is yes, you can do it. The problem is I'm not sure exactly how. When you use fillable ...
SQL command to get table structure in MS Access	8/8/2009
Q: What SQL command can I use to display the structure (column names, data type, var length, etc) of a ... A: I don't believe there is an accessible system table with that info. What you can do is use the ...
Help setting up a auditing database	8/8/2009
Q: I don't know if you remember me but I asked you a bunch of questions on setting up my tables for a ... A: Yes you have used SQL, but maybe not directly. Whenever you create a query, Access generates an SQL ...
Report by Form Filter	8/7/2009
Q: Still fairly new to access I understand some coding but not very proficient. I created a button to ... A: The problem is that your query is using the same field from multiple tables. So when the filter is ...
Requerying I think :)	8/7/2009
Q: Can you tell me where to start? On my form, I have a combo box for EquipType, Manufacturer, then ... A: This is a standard technique called cascading or synchronized combo boxes. This article explains how ...
MS Access 03 - Calculated field	8/7/2009
Q: I'm using Microsoft access 2003 to produce a "supermarket" database. Sometimes, i need to reorder ... A: Who said anything about manually changing anything? I don't think you are quite grasping this. You ...
Synchronizing Combo Boxes	8/7/2009
Q: I was following what was stated in http://support.microsoft.com/kb/209576/en-us on how to ... A: As long as the comboboxes are bound to the fields in the table, by setting their controlsource to ...
best code for Closing a monthly transaction on a payroll system	8/7/2009
Q: I am a trying to develop a payroll system but stack with a code to close transaction on montly basis ... A: In the On Current event of your form use code like this: If Me.[TransDate] < ...
Inventory System	8/7/2009
Q: I am trying to make the inventory system you recommended by studying the answers you gave in the ... A: A lookup table is just a regular Access table. Its different only in its use and maybe its name. I ...
Charts in Access 07	8/7/2009
Q: I am trying to create a chart in Access 07. I have created a query for the chart that I want. When ... A: First, you open a blank form in design mode. Then under the Design ribbon in the Controls section ...
Assign default value to a subform field.	8/7/2009
Q: I need help assigning a default value to a field on a subform, from a field in the main form i.e. I ... A: First, you do not have fields on a form. You have controls, that may or may not be bound to a field ...
Filtering on a Form	8/6/2009
Q: I am trying to add two combo boxes to a form to filter the results of the form. I have the form ... A: Frankly, I think you are better off training the users to use Filter by Form. You need to step ...
Date Range	8/6/2009
Q: I read your answer on how to search on a date range by entering the following under the criteria in ... A: Set the Default Value of txtStart to" =DMin("[datefield],"tablename") and for txtEnd use ...
Help setting up a auditing database	8/6/2009
Q: I don't know if you remember me but I asked you a bunch of questions on setting up my tables for a ... A: This is actually fairly easy to do. I've done this frequently in my apps. Generally what happens is ...
Forms working on one computer but not another	8/6/2009
Q: I'm perplexed. I designed a DB in Access at work. Up until Friday, the data entry form has been ... A: Put all of them on the same platform. If that's not practical make sure they all have the same ...
Charts in Access 07	8/6/2009
Q: I am trying to create a chart in Access 07. I have created a query for the chart that I want. When ... A: You need to go a step further. When you see the East, West, etc. you are in Layout view and its ...
Exporting Reports/Querying/I don't know	8/5/2009
Q: Using Windows XP, Access 2007 My company uses management software that organizes customers based on ... A: The answer to your question is actually very simple. You can export a query to Excel very easily. ...
If Then else statements	8/5/2009
Q: I am creating a report based on a query with a Y for yes or an N for No (the control source is ... A: In the query that is the RecordSource for your report, set the criteria for Deleted Column to "Y". ...
Autonumbering and Relationships	8/5/2009
Q: I work for a large company and am trying to organize several hundred learning tools known as ... A: You are not grasping what I'm trying to tell you. You do NOT need to store the "Flashcard Number". ...
Images in Access 2003	8/5/2009
Q: I created a database to hold info on an aspirin tin collectin that has over 800 pieces. I embedded ... A: In the Properties dialog make sure you select Form in the pull down at the top. Then you will see ...
Top Values Per Group Report Issues	8/5/2009
Q: I've been working on a top values per group report for a while now, basically following the outline ... A: Can you send me a zipped copy of your db to dafiles at optonline.net (format that correctly). ...
Access Report	8/5/2009
Q: I have created a simple selct query based on the on click event of an 'Invoice' number on an ... A: There are ways around that. For example, provide the drivers with a small laptop or netbook. Or even ...
Access Report	8/5/2009
Q: I have created a simple selct query based on the on click event of an 'Invoice' number on an ... A: Grouping is one of the things that make Access reports very powerful. I rarely create reports that ...
Images in Access 2003	8/5/2009
Q: I created a database to hold info on an aspirin tin collectin that has over 800 pieces. I embedded ... A: Did you place an unbound image control on the form? Are you setting the Picture property of the ...
Access Report	8/5/2009
Q: I have created a simple selct query based on the on click event of an 'Invoice' number on an ... A: I'm not sure why you would insert an image. But the correct way to do this is to use Grouping. You ...
Simple Access query - how to auto populate	8/4/2009
Q: I have an Excel spreadsheet with Employee details - name, startdate, paygrade, paypoint, and annual ... A: Correct that the version of Access will not matter. You need a table like this: tblSalary SalaryID ...
Autonumbering and Relationships	8/4/2009
Q: I work for a large company and am trying to organize several hundred learning tools known as ... A: First it is NOT recommend that you use lookup fields on the table level. Users should never interact ...
Top Values Per Group Report Issues	8/4/2009
Q: I've been working on a top values per group report for a while now, basically following the outline ... A: While you can reference a value on an open form using the syntax: forms!formname!controlname You ...
Passing a date value from one form to another in MS Access 2003	8/4/2009
Q: I am having trouble trying to pass the date value from one form to another. Can you assist? ... A: First, you can't put all that code in Form1. Second, your referencing is way off. I don't know where ...
Filtering on a Form	8/3/2009
Q: I am trying to add two combo boxes to a form to filter the results of the form. I have the form ... A: If you are doing variable criteria like that, then you need to build your filters. If you have 2 ...
Images in Access 2003	8/3/2009
Q: I created a database to hold info on an aspirin tin collectin that has over 800 pieces. I embedded ... A: Easily. Don't embed pics using OLE fields. What you need to do is add an UNBOUND Image control to ...
Lookup Fields	8/3/2009
Q: I have an IT inventory database I'm working on. I created it without Lookup fields. I just created ... A: Sorry, but that is not the way to go. Lookup fields on the table level are NOT recommended. What you ...
Autonumbering and Relationships	8/3/2009
Q: I work for a large company and am trying to organize several hundred learning tools known as ... A: Why not just use an Autonumber datatype? That's what they are designed for. So assign a unique ...
MS Access 03 - Calculated field	8/3/2009
Q: I'm using Microsoft access 2003 to produce a "supermarket" database. Sometimes, i need to reorder ... A: I understood that and the fact that quantity changes regularly is WHY you do what I recommend. Again ...
Security issues on access 2007	8/3/2009
Q: Following the last question on access security (sorry for being a pest), I understand that there is ... A: 1. You may want to go to SQL Server as your backend. 2. There are many ways to make a copy of a ...
MS Access 03 - Calculated field	8/3/2009
Q: I'm using Microsoft access 2003 to produce a "supermarket" database. Sometimes, i need to reorder ... A: The answer is that you don't. As a general rule, we do NOT store calculated values. In an inventory ...
Security issues on access 2007	8/3/2009
Q: Following the last question on access security (sorry for being a pest), I understand that there is ... A: If they no where the backend is, they can just copy that file. And it shouldn't be difficult to find ...
Finding Directories in access 2000	8/3/2009
Q: I want to search all available system drives for a specific directory, and possibly sub directory, ... A: First you can use the FileDialog command to open a file Picker dialog box that the user can use to ...
sequential numbering redux	8/3/2009
Q: I’m trying to implement a sequential numbering system for a volunteer organization that works with ... A: There are several problems I see here. Some of them were probably mentioned in the article you got ...
forms	8/2/2009
Q: "hey there i have 2 questions (1)how to use forms to enter query parameters in access 2007 (2)how ... A: First, if you have a new and separate question you should start a new question rather than using the ...
Finding Directories in access 2000	8/2/2009
Q: I want to search all available system drives for a specific directory, and possibly sub directory, ... A: You would need to use the DIR command to get a listing of directories, then drill down looping ...
slow database performance	8/2/2009
Q: This is a followup on the your lastWhat do general users need to do in the database? General users ... A: If general users need to edit data, there is very little you can do to prevent them stealing it. On ...
Too many records in a table?	8/1/2009
Q: Is there any limit on how many records in a single table? I've got over 1,000,000 record combined ... A: There is no limit, the limit is on file size and you can get around that to an extent by using ...
Database too big?	8/1/2009
Q: I've got two databases (the combined size around 1gb) and what is the downside of merging them into ... A: 1. You can apply encryption to prevent theft of data. But that will slow performance. 2. Six people ...
Is my table normalized or even set up correctly? - Continued	8/1/2009
Q: sorry about having to make a new question apparently AllExperts only allows a set number of follow ... A: Yes, I'm sorry, I thought I included a table for each component. Something like this: tblComponent ...
Import Thru WEB QUERY	8/1/2009
Q: I have developed a database for my Portfolio.I retreive the closing prices of my Shares from ... A: 1) That means the quotes ARE downloaded to a file. The format is in Excel. 2) That's fine 3) Run an ...
Import Thru WEB QUERY	8/1/2009
Q: I have developed a database for my Portfolio.I retreive the closing prices of my Shares from ... A: Are you downloading a file from WEB query? If so, you need to import that file according to the file ...
Is my table normalized or even set up correctly?	8/1/2009
Q: I was wondering if you might take a look at my database. The scope I am attempting to achieve is a ... A: If the calibrations are specific to the product, then this is the way I would set it up. tblProduct ...
Finding Matches Between Two Lists in Excel	7/31/2009
Q: I have two lists of email addresses. I want to create a list which has only those addresses that ... A: Umm why are you asking an Access expert? You can try to use Microsoft Query to do this, its very ...
MS-Access Over the Network Issue	7/31/2009
Q: I cannot solve an issue i have with Access over the network. We have a network of 4 computers. All ... A: Its hard to tell. One thing you can try is create a blank database, import all the tables, then ...
Using Access to open another program	7/31/2009
Q: I have used the following in the past as part of an Event in a Combobox to start another program and ... A: You need a space between the program call and the filename you could have used: GetVal = ...
Dlookup in	7/31/2009
Q: I am a beginner in Microsoft Access 2007. Currently, I am working on a form called FBranch Orders in ... A: If the item code and cost code do not change they should NOT be in your orders table. One of the ...
MS-Access Over the Network Issue	7/31/2009
Q: I cannot solve an issue i have with Access over the network. We have a network of 4 computers. All ... A: First check the permissions on the folder. But Access should have no problem allowing multiple PCs ...
Is my table normalized or even set up correctly?	7/31/2009
Q: I was wondering if you might take a look at my database. The scope I am attempting to achieve is a ... A: We are close, but there is still one thing I'm not getting. I would assume that a line makes ...
Is my table normalized or even set up correctly?	7/31/2009
Q: I was wondering if you might take a look at my database. The scope I am attempting to achieve is a ... A: I think you have some circuler relations here. If I'm following you, Lines and Products can each ...
On click to open another form	7/31/2009
Q: Got another one for ya. Ok...I have a continuous form. What I want is that the user can click on ... A: So you are referring to a search combo? The code you are using, should filter the form the ...
Using Access to open another program	7/30/2009
Q: I have used the following in the past as part of an Event in a Combobox to start another program and ... A: There is an easier way, assuming that PagePlus is set as the default for PPP files: Filepath = ...
Linking Data across multiple tables	7/30/2009
Q: I hope I explain my situtation clearly for you. I have a table where the user types in information ... A: In the On Open event of the second form use code like: If NOT Isnaull(Me.openArgs) Then ...
forms	7/30/2009
Q: "hey there i have 2 questions (1)how to use forms to enter query parameters in access 2007 (2)how ... A: 1) Any control on an open form can be referenced using the syntax: =Forms!formname!controlname ...
Relationship Problems	7/30/2009
Q: I'm trying to set up relationships for product info. I'm not sure how to explain this clearly, but ... A: So you want a query to show ProductID, Price and Competitor? You will have a record in ...
Queries	7/30/2009
Q: I am trying to create a query which will take a date and then tell me of all the records which ones ... A: Set the Criteria for the Start date column to: NOT BETWEEN date AND DateAdd("m",21,date) where ...
Relationship Problems	7/30/2009
Q: I'm trying to set up relationships for product info. I'm not sure how to explain this clearly, but ... A: You need four tables: tblProduct ProductID (PK Autonumber) Description etc. tblProductPrice ...
MS ACCESS : SWITCH FUNC	7/30/2009
Q: I Have developed a Database for our Primary school , now I have a STUDENTPERFORMANCE query. I want ... A: First, you don't have to be a hardcore programmer to code simple user defined functions. Second, You ...
access backend sync	7/30/2009
Q: As a follow up to my previous question where you suggested a timer to alert the user to the fact the ... A: Have you tried using replication? Admittedly, I've never had the need, so I'm not that up on it. But ...
SQL	7/30/2009
Q: wHAT are the SQL commands that cannot be use in Microsoft Access? A: Any SQL key words that follow the ANSI standard can be used in Access SQL. There are very few ...
MS ACCESS : SWITCH FUNC	7/30/2009
Q: I Have developed a Database for our Primary school , now I have a STUDENTPERFORMANCE query. I want ... A: I'm not sure I follow. Are you saying you want to display the grade as a letter when the numerical ...
Search for values + and - 1 in access	7/30/2009
Q: Hey I have a problem I hope you can help me with. I have a search function in my access database ... A: Why are you using asterisks? Peak11 should be a number value. It should be: strWhere = strWhere & " ...
Access 2007 Backend Database	7/30/2009
Q: I have 2 sites connected directly by VPN. The VPN is done via the Internet (not the fastest ... A: UNC uses the syntax: \\servername\sharename\foldername\filename If you use Linked Table Manager, ...
Access 2007 to new SQL Backend	7/30/2009
Q: I am trying to update an old 2002 access database system to 2007 and want to link to a 2008 SQL ... A: Frankly I would not do it that way. I would create your SQL Sever database, populate it with data ...
On click to open another form	7/30/2009
Q: Got another one for ya. Ok...I have a continuous form. What I want is that the user can click on ... A: I'm not sure what combobox you are referring to. Following is code I use in the Form's Double Click ...
Access Populating a Table via a Form	7/30/2009
Q: I am using a field list in a form to put data into my form but the table does not reflect my ... A: I'm not sure what you are referring to as field lists. If you are creating lookup fields on the ...
Combining Queries In One Form Or Report	7/29/2009
Q: I use a database to maintain equipment records for customers. Each month I export the customer list ... A: This is pretty standard stuff. How depends on the strucutre of your database. Basically, you create ...
Linking Data across multiple tables	7/29/2009
Q: I hope I explain my situtation clearly for you. I have a table where the user types in information ... A: What you need to do is have a foreign key in the table behind the popup (and yes you use a separate ...
Using MS access for bill of lading	7/29/2009
Q: Is there s template that I can use to generate a bill of lading with MS Access A: A bill of lading is primarily a listing of items in a shipment. Any template that can create orders ...
Query too complex	7/29/2009
Q: First of all let me express my appreciation on the answers you've provided on MS Access. I've ... A: Ok, so these are not postcode tables but people tables. Again why do you have separate tables for ...
specify a path parameter	7/29/2009
Q: I am trying to importing txt files from 8 different directories during a series of processes. I ... A: Check out the FileDialog Property in Access Help. This opens a standard Windows File Picker dialog ...
Make the form shown automatically	7/29/2009
Q: I want to ask you how I can make the my main form open automatically when I open access software?? ... A: Under Setup Options, you can specify a form to open when you load the Access file. You can also ...
Query too complex	7/29/2009
Q: First of all let me express my appreciation on the answers you've provided on MS Access. I've ... A: Not sure if I follow. Are you saying you have separate tables of postcodes? And you want to combine ...
Crosstab Query Report	7/29/2009
Q: i would be appreciate if you could help me to sort this problem, i want to take a class attendance ... A: Do you want to display attendance by class by Month? So you have the months as column headings and ...
MS Access 2003 finding the record source of a data field	7/28/2009
Q: I have a hand-me down of a database with a front end. It contains a form that generates a report. ... A: Actually, I do that all the time. Forms do not HAVE to have a Recordsource. Frequently I have a ...
Help using a SQL to populate a table in Access	7/28/2009
Q: I wrote a query in SQL Server Management Studio and I want it use this query to create a report in ... A: T-SQL (which is what SQL Server uses is different from Access SQL. The differences are not great, ...
combining information, eliminating redundancies	7/28/2009
Q: I'm relatively new to Access, so bear with me. I work for a company with an existing membership ... A: You are going about this correctly. Add a field to your membership database to indicate whether the ...
converting to pdf file	7/28/2009
Q: I have a 16 page, 27 picture report I tried to convert to pdf with Cutewriter program. I tried to ... A: It sounds like you created the report OK, the problem is e-mailing it. If you have a file that is ...
Canceling Out Of Data Entry Form	7/28/2009
Q: I have a data entry form with a 'Save Record' cmd button. I've found that if a user enters all the ... A: Yes, use the On Close event of the form (or just remove the close button and have your own EXIT ...
Duplicating fields from one record to another	7/28/2009
Q: My database deals mainly with purchase orders (PO). I want to facilitate the process of re-ordering ... A: Add a button to your form with a caption of Clone Order. The code behind the button would look like ...
access 2007	7/28/2009
Q: I created a database with 3 tables all kinked by one primary key. When I go to forms only the first ... A: When I ask a question, please provide answers to those questions. I need those answers to try to ...
Form Interface to Table	7/27/2009
Q: I am an expert on sales and marketing and a member of AE. Well i tried doing what you said but it ... A: OK, two things. You should have two tables since I assume the compnay associated with your products ...
about form??	7/27/2009
Q: I want to know how may I use the form in access 2007 as a form only ??? I mean after I finished my ... A: This article: http://www.mvps.org/access/api/api0019.htm shows you how to mimize the Access window ...
access 2007	7/27/2009
Q: I created a database with 3 tables all kinked by one primary key. When I go to forms only the first ... A: When you say only the first record is displayed, do you mean you can't navigate to other records? Is ...
selection from multiple tables	7/27/2009
Q: how to run a query for multiple tables with selection criterion as todays date, so that todays tasks ... A: You can only run a query from multiple tables is there is a relation between them. In that case, you ...
Entering new data in Master Table	7/27/2009
Q: I have been asked to design labels in Access. I have all the necessary data in the table and i ... A: First, in the future, if you have a new question, you should start a new rather than use the ...
generate number in text field	7/27/2009
Q: I have 2 tables, both have Field "Trans_No". This field is text type(width 5). Trans_No should be ... A: First, you should have identified this as a homework problem. Generally I do not do people's ...
MS access - insert table into form	7/26/2009
Q: I'm a complete newby to MS access so I don't understand a thing about SQL or queries just yet and ... A: Why recreate the wheel? Microsoft has a templates for a recipe databases. Here's the link: ...
generate number in text field	7/25/2009
Q: I have 2 tables, both have Field "Trans_No". This field is text type(width 5). Trans_No should be ... A: I assume you need an sequential number. Why are you using a text datatype? You should be using a ...
Labels In access	7/25/2009
Q: I have been asked to design labels in Access. I have all the necessary data in the table and i ... A: 1. Create a form and set the data controls to Locked. You can try to set Allow Edits to No, but I ...
newbie question for queries	7/24/2009
Q: I am relatively new to the concept of databases, tables, and queries... I have a "master table" of ... A: You don't need a make table. Give her a front end just for herself. Make a query from the table that ...
Error Handling	7/24/2009
Q: I have a form with a subform connected by a primary key of ProviderID. When I select a value from a ... A: First, I do NOT recommend using composite keys. If you need to enforce the unique combination of ...
send email using default mail client	7/24/2009
Q: I have been working on this issue for almost a week. I have an Access 2007 frontend/backend ... A: I've tried to find anyone who has done this and can't, I'm sorry. Scott<> Microsoft Access MVP 2007 ...
Calculations	7/24/2009
Q: Is there a way I can make access calculate items for me. What I have is a table the has the ... A: When you design your report, set up to Group by Product and select to Sum QTY. The Date range goes ...
Update date	7/24/2009
Q: I have a question concerning a date update.In access i made a form with several command buttons to ... A: Yes you can do it, but it depends on what you mean by update. If you mean when the report was last ...
Open Filter By Form with Edit	7/24/2009
Q: I am using the following code to open a form directly to filter by form: DoCmd.DoMenuItem ... A: First you have to open the Form, so you can try setting the ReadOnly property when you open the ...
help with creating a record in a table based on a dropdown	7/24/2009
Q: I am a newbie to Access and I am trying to help someone else out (my disclaimer :) ). I have a form ... A: First, its called a Combobox (sometimes a dropdown LIST). Second, the After Update event is a better ...
Event Calendar	7/23/2009
Q: I think you may have provided this response to one question awhile back "I have an app I use that ... A: If you give me an e-mail address to send it to. If you can't make a followup private, then start a ...
Opening access database from within access	7/23/2009
Q: I have two reports that run from two different Access databases. Instead of having two icons on the ... A: Ahh you didn't mention that the other file has a SQL Server backend. You will probably need to ...
How to create table from a macro	7/23/2009
Q: I am trying to run 5 queries thru a macro and then put their results in a new table. Is it possible ... A: I asked for the nature of the queries and a more detailed explanation. I'm sorry but your response ...
How to create table from a macro	7/23/2009
Q: I am trying to run 5 queries thru a macro and then put their results in a new table. Is it possible ... A: Why would you put the results in a table? As long as you can get the result set you want, why not ...
Distinct count	7/23/2009
Q: I am trying to do a count of distinct records but I am still getting duplicate record count in it. I ... A: Try building interim queries (using SELECT DISTINCT each time). Try first filtering for the dates or ...
User query	7/23/2009
Q: I have previously worked with MySQL and PHP, and now I am working on an Access 2007 database. What I ... A: There are two ways to do this. The way I recommend is to use a form. Create a Form with a textbox ...
6 Month Expiry Report	7/23/2009
Q: this will be my last question I promise, this training database will be the death of me. you have ... A: In the Combo where you select the Event, add a column to the RowSource for the Start date. Then set ...
Multiselect Listbox	7/23/2009
Q: I'm trying to get this code to work but it won't insert anything in my table. If I reference a ... A: First you don't need the ADO stuff. Second, you don't need the With Forms either. Just use the ...
Counting Records	7/23/2009
Q: I have been working a an auditing database to collect information about auditing check transactions. ... A: First, I don't know if it was a typo but you have DTTransaction as a PK in both the Transaction and ...
6 Month Expiry Report	7/23/2009
Q: this will be my last question I promise, this training database will be the death of me. you have ... A: Ahh OK, then its even easier. You should already have a column that display the Expiry date, ...
Saving a report design view	7/23/2009
Q: I am using Access 2003 for a simple project. But I have 3 different reports to set up, all from the ... A: After saving a report you can just copy and paste it giving it a new name (you will be prompted to ...
OpenArgs	7/23/2009
Q: I have a command button on a form with the following code to open another form to display the ... A: OK, if you need to know which form called the second form, than code that as your OpenArgs. Pass the ...
Calculations	7/22/2009
Q: Is there a way I can make access calculate items for me. What I have is a table the has the ... A: OK, you need a table like this: tblStock Stock# Description Unopened Partial UOM That last field ...
Calculations	7/22/2009
Q: Is there a way I can make access calculate items for me. What I have is a table the has the ... A: Of course Access can do this. But I'm not sure is your approach is the correct way. I need to ask a ...
Displaying Query Results Horizontally Separated by Commas	7/22/2009
Q: I have designed a database that prints a report that needs lists each patient and their associatied ... A: The function goes in a global module. But you may have to tweak it to get what you want. The ...
6 Month Expiry Report	7/22/2009
Q: this will be my last question I promise, this training database will be the death of me. you have ... A: First, please ask any questions you need answered. If you complete this database there will always ...
get initials from a textbox	7/22/2009
Q: Well, I am here again to trouble you. I want to extract only the initials of the textfield. For ... A: Yes, extracting only the upper case letters isn't that easy, but it is possible. But that doesn't ...
Query for Inventory Database	7/22/2009
Q: I have set up and inventory database containing "Vendor, Description, Height, Depth and Width" with ... A: So what you have is a products table that lists the Vendor, description and size of each item. You ...
Created date in Page Footer	7/22/2009
Q: First of all, I am using MS Access 2007 and running in Windows XP SP2 I would like to insert the ... A: Well you come up with a very good point. You can capture the report's name. But the only way I could ...
OpenArgs	7/22/2009
Q: I have a command button on a form with the following code to open another form to display the ... A: OK, I think I see the problem. The second form can be called from the first form OR it can be opened ...
get initials from a textbox	7/22/2009
Q: Well, I am here again to trouble you. I want to extract only the initials of the textfield. For ... A: This can be VERY difficult. It depends on the pattern of the name. It would be much better if you ...
Update query	7/22/2009
Q: using access 2007 Hello. I've read a lot of the posts about update queries but still have no idea ... A: You would have to create your running sun in a query and bind the form to that query. If multiple ...
Multi Drop Down Menus / Data Validation	7/22/2009
Q: I am working on a project for a carpentry / cabinetmaker service whereby selecting categories would ... A: Usually more info IS better, but frankly, you gave me too much info here. So that I'm not 100% clear ...
Displaying Query Results Horizontally Separated by Commas	7/22/2009
Q: I have designed a database that prints a report that needs lists each patient and their associatied ... A: This article gives you a function that will take the value in one field of multiple records and ...
Creating Search Function based on Checkboxes	7/21/2009
Q: I have several checkboxes on my form relating to insurances that are accepted at a doctor's office. ... A: Because you have a repeating group. You denormalize a database when you have multiple fields ...
Creating Search Function based on Checkboxes	7/21/2009
Q: I have several checkboxes on my form relating to insurances that are accepted at a doctor's office. ... A: A large part of this is related to the structure of your database. From your description it may not ...
Trouble with dlookup	7/21/2009
Q: I have one main table and a select query to pick out, and run calculations on, the data in the main ... A: You don't, all you need is a join. Add the states table (by the way ( would add a column to that ...
Dlookup error :"You canceled the previous action "	7/21/2009
Q: I have been struggling with this error for 2 days and i can't find a solution. What i man trying to ... A: Try this: ExistingName = Nz(DLookup("[Naam]", "Gebruiker", "[Naam] = " & Me.Naam.Value),"") Your ...
Created date in Page Footer	7/21/2009
Q: First of all, I am using MS Access 2007 and running in Windows XP SP2 I would like to insert the ... A: This function will return the created date of the specified report: Public Function ...
Pop up form not in sync with current record	7/21/2009
Q: I have a button in a form that opens another form popped up and modal. The pop up form has the same ... A: Simple. Use the WHERE of the OpenForm method to set your Primary key value to the current record's ...
Access Report Printing	7/21/2009
Q: How can set tear off position on dot matrix printer after print completed. i will be thankful to ... A: This is a function of the printer driver. Most printer drivers have a control sequence you can send ...
Check Weight Diffrences Between Visits Query	7/21/2009
Q: I'm a bit stuck with this one. I have a table where we record multiple patient visits. I need to see ... A: Ahh, that's a bit different if you want to track the weight over time. That's actually easier. I ...
updating field	7/21/2009
Q: I have a table in my database that lists clients, i have created a form that allows me to add the ... A: Since its returning 0 before you select an Event, that part is working, so you need to work on the ...
Check Weight Diffrences Between Visits Query	7/21/2009
Q: I'm a bit stuck with this one. I have a table where we record multiple patient visits. I need to see ... A: There are two ways I can think of to do this. By looping thru the records which would mean using a ...
updating field	7/21/2009
Q: I have a table in my database that lists clients, i have created a form that allows me to add the ... A: The expression is used as a ControlSource on a form? Try using: ...
Increment number in a field	7/20/2009
Q: I am new to access, I created an input form which contains a datasheet view sub form with the ... A: The DMax function is what you need. What did you try? It would be easier for me to see what you used ...
Opening access database from within access	7/20/2009
Q: I have two reports that run from two different Access databases. Instead of having two icons on the ... A: Yes, you can actually run reports in another Access file. Something like this: Sub DisplayForm() ...
Access Lock a Field after it has been data entered	7/20/2009
Q: I was wondering how to not allow changes to a field (locked for entry)once the field has been data ... A: If Not IsNull(Me.controlname) Then Me.controlname.Locked = True Else Me.controlname.locked = False ...
MS Access lookup table	7/20/2009
Q: I have a database where a query is used to enter data. One of the fields is a lookup to another ... A: I'm a little confused. Using a query to enter data means you are entering data in bulk. Why would ...
autofill	7/19/2009
Q: i ahve created a datebase for trackig medical supplies. i have had to go back and add features for ... A: OK, You do need two tables here. Something like this: tblItems ItemID (PK autonumber) StockNumber ...
Access Form (Invoice)	7/19/2009
Q: I'm trying to set up an invoice form for a library system consisting of 9 libraries. What I need is ... A: What you are talking about is pretty common. You might want to look at the Northwinds sample that ...
Calculate Age in years, months and days	7/18/2009
Q: Good evening, I hope that u can help me. In an Access 2003 Form that I designed, I would like to ... A: I thinbk you are looking at this incorrectly. The way the function works, first it calculates the ...
Last enetered data not to be displayed on control	7/18/2009
Q: On one combo box I select an item from drop down list. On another I use where condition to display a ... A: If you have an unbound combobox, then it will retain its value until you change it or the control is ...
Avg Function	7/17/2009
Q: I have 29 columns of data for each row. I would like to average all of the 29 columns (ensuring that ... A: Who said anything about creating 29 different tables? You have ONE table that would look something ...
Avg Function	7/17/2009
Q: I have 29 columns of data for each row. I would like to average all of the 29 columns (ensuring that ... A: Well first you should redesign your table. Having fields named Temp1, Temp2, etc constitute a ...
Access queries	7/17/2009
Q: I have a table with 2 fields containing 125 records that I group and count instances of which I can ... A: Yep, I have just the solution. See this article: ...
Access queries	7/17/2009
Q: I have a table with 2 fields containing 125 records that I group and count instances of which I can ... A: Why would you need to create a table with that info? Do you need to freeze data at a point in time? ...
autofill	7/17/2009
Q: i ahve created a datebase for trackig medical supplies. i have had to go back and add features for ... A: Why would you have separate tables for different stock items? this is going to cause major headaches ...
Importing Date from Fixed Width .txt file with no date delimiter	7/17/2009
Q: I have a fixed width .txt file where each row is a constant stream of numbers and characters. The ... A: To be honest I'm stumped. If the format is always yyyymmdd so that date string is always 8 ...
Error Handling with Functions	7/17/2009
Q: I'm trying to introduce error handling into my functions. This is what I have: Public Function... ... A: Lookup Option Explicit. You may find that modules where not DIMming a variable work do not have ...
How to display weekday name along with date in a table?	7/17/2009
Q: I dont want it in two different fields. I want all in one field. please advise. Thanks A: Use the following expression: =Format([datefield], "dddd yyyy/mm/dd") If you only use 3 Ds ate the ...
How to use Distinct count in MS access	7/17/2009
Q: I am trying to retrieve only distinct records/values in my query but its shows me count for ... A: SELECT DISTINCT gives you a count of all records where the ENTIRE record is unique. First, you ...
Option groups in Access 2000	7/17/2009
Q: I have set up an option group in a DB and this works OK. Only 1 selection at a time, all other ... A: No, its certainly one way to go. But how are you doing it? One way is using the After Update event ...
Option groups in Access 2000	7/17/2009
Q: I have set up an option group in a DB and this works OK. Only 1 selection at a time, all other ... A: You can't assign a value directly to an option, you have to assign it to the group. You can use ...
How do I link a image in MS Access from a folder?	7/17/2009
Q: I have a database in MS Access 2003. I want to put an image for every record that I have. I set the ... A: You should never store anything directly on the C:\, everything should be in folders. So I would ...
Importing Date from Fixed Width .txt file with no date delimiter	7/16/2009
Q: I have a fixed width .txt file where each row is a constant stream of numbers and characters. The ... A: Unfortunately, the date format is not recognized by Access as a valid Date format. So you will have ...
How to use Distinct count in MS access	7/16/2009
Q: I am trying to retrieve only distinct records/values in my query but its shows me count for ... A: In query design mode, open the Properties dialog and set Unique Values to Yes. This will add the ...
relating Multiple tables to one record in another table	7/16/2009
Q: I have a question that may be difficult to answer. I'm currently designing a Access 2007 Database ... A: Do you really need to turn off the tab pages? The way I'm looking at this You want the user to ...
Count IIf calculated time in Access 2003 Reports	7/16/2009
Q: Appreciate if you answer me for: I have a Attendance table with the details This is my Table EMP# ... A: Then you need to keep the FULL date and time. Use a datepicker so the user enters the date and time. ...
How do I link a image in MS Access from a folder?	7/16/2009
Q: I have a database in MS Access 2003. I want to put an image for every record that I have. I set the ... A: The way I solve the portability issue is to create an Images folder within the folder the database ...
Form refreshes multiple time	7/16/2009
Q: I have a form with a subform within. The query that run the form does a number of calculations. I ... A: If you make a change that affects calculated controls that are dependent on each other, this could ...
Creating a look up field in forms	7/15/2009
Q: I have a database with about 7 fields, I want to be able to look up information using the foreign ... A: Displaying Data from related tables on a form This is a very frequently discussed issue. But before ...
MS Access Program	7/15/2009
Q: I support an office that has an access program that was written for them years ago. All of a sudden ... A: If you copied that module, I see the problem. There is no execution of strsql. See the cn.Execute ...
relating Multiple tables to one record in another table	7/15/2009
Q: I have a question that may be difficult to answer. I'm currently designing a Access 2007 Database ... A: OK, I see two choices here. One would be to use a Tab control and place the subforms on each Tab ...
How do I link a image in MS Access from a folder?	7/15/2009
Q: I have a database in MS Access 2003. I want to put an image for every record that I have. I set the ... A: To solve this problem DO NOT use an OLE Object field. This cause the database to bloat in size. The ...
Update query	7/15/2009
Q: using access 2007 Hello. I've read a lot of the posts about update queries but still have no idea ... A: Well part of the problem is you are going about this the wrong way. Since Ranking is a variable ...
How to calculate (SUM) SubReport TOTAL to get TOTAL COST/DUE in ACCESS REPORT	7/15/2009
Q: How can I do calculation in Access Report.I have 4 Reports,Named APPLICATION,STATIONERY,COMPUTER and ... A: Why are you using subreports? You should have a field in your Order detail record to indicate the ...
relating Multiple tables to one record in another table	7/15/2009
Q: I have a question that may be difficult to answer. I'm currently designing a Access 2007 Database ... A: I'm not seeing the problem. It sounds like you have a One to One relation between your Main table ...
Using startDate and endDate in a form as parameters to run multiple queries	7/15/2009
Q: I have a form with StartDate and EndDate fields and I want to run multiple queries using these date ... A: You can reference any value ion a control on an open form using the syntax: ...
Difference between Access 2007 and 2003	7/14/2009
Q: I'm designing a form using access 2007,i've two cascaded drop down lists by queries the selection is ... A: No bother, that's why I'm here. But which combobox name did you use? You need to requery the SECOND ...
tables and forms...	7/14/2009
Q: Here's the problem... I’ve created a form using the fields from my table. In theory what gets ... A: "In theory what gets entered into the form it should populate the respective tables." This ...
Help with refining VBA	7/14/2009
Q: I am looking for an elegant solution to these IF statements, can I use CASE? Many Thanks in advance ... A: The limitation on SELECT Case is that it can only deal with one value at a time. You could use ...
Dynamic Table in Access Report 2	7/14/2009
Q: Is the data in the table static? Well, my answer is two-fold. The table, at the moment, has about ... A: OK, Well the first thing I would do is change the structure. I would have 2 tables: tblReportHeader ...
Update query and IIF statement	7/14/2009
Q: I'd like to know how to use an IFF statement in Update Query. I've made an Update query with ... A: Updating the prices is one thing. Obviously that has to be done. But if you have a query that does ...
Dynamic Table in Access Report	7/13/2009
Q: I need to create a dynamic table in an access report. I have a table, the table has fields (F1, F2, ... A: The way to do this is pretty complex, but it may be made easier with one piece of info. In the table ...
Dynamic Table in Access Report	7/13/2009
Q: I need to create a dynamic table in an access report. I have a table, the table has fields (F1, F2, ... A: Ahhh, I think I understand now. You want to produce a BLANK Report that has boxes for people to fill ...
Microsoft Access 2003 User Level Securty	7/13/2009
Q: I am working with MS Access 2003 & have used the Inside Out Book instructions to secure the data ... A: Those instruction don't include one important step, which is deploying ULS. Unless you specify, ...
Dynamic Table in Access Report	7/13/2009
Q: I need to create a dynamic table in an access report. I have a table, the table has fields (F1, F2, ... A: I'm not following what you want to do. It sounds like a crosstab query may suit your needs. Anything ...
Expiry date	7/13/2009
Q: some of the courses dont expire but some do. i have entered an expiry field on the course form, ... A: As a general rule we do not store calculated values. There is no reason to store this and every ...
Expiry date	7/13/2009
Q: some of the courses dont expire but some do. i have entered an expiry field on the course form, ... A: I'm not sure what you mean. You shouldn't be storing the Expiration date, only the number of months ...
Query Results in a Subform	7/13/2009
Q: I have main form named as "frmauthorTitleQuery" with unbound caption [to be filled by users for the ... A: First, there is no need for a subform here. A subform is generally bound to a table related to the ...
Not Exists (subquery)	7/13/2009
Q: I've 1 tbl containing following records alias ,Qty ,rate ,NetAmt ,transactiontype ... A: The only way I see of simplifying that is to use separate queries. Have a separate query for SELECT ...
Search for values + and - 1 in access	7/13/2009
Q: Hey I have a problem I hope you can help me with. I have a search function in my access database ... A: First, I think your database is not designed properly. Having fields like Komponent1, Komponent2, ...
Calculate Age in years, months and days	7/13/2009
Q: Good evening, I hope that u can help me. In an Access 2003 Form that I designed, I would like to ... A: You would use a function for this. Something like this: Public Function CalcAge(dteDOB As Date, ...
Update query and IIF statement	7/13/2009
Q: I'd like to know how to use an IFF statement in Update Query. I've made an Update query with ... A: Well first, as a general rule we DO NOT store calculated values. So you really should not have ...
ID column	7/12/2009
Q: I'm working on a database. I want to include a field named "ID" that is NOT the primary key and want ... A: First, you do NOT have fields on your form, you have controls that may or may not be bound to fields ...
Access query	7/11/2009
Q: I have a list that I am setting up a query for but I only want to have the first instant of a ... A: I think this article describes what you want. You want to do the TOP 1 per group. If this isn't what ...
Count IIf calculated time in Access 2003 Reports	7/11/2009
Q: Appreciate if you answer me for: I have a Attendance table with the details This is my Table EMP# ... A: First, how do the users enter the data? Do they enter the start time and the amount of time spent or ...
Not Exists (subquery)	7/11/2009
Q: I've 1 tbl containing following records alias ,Qty ,rate ,NetAmt ,transactiontype ... A: First a Subquery can only return ONE column. Second, you don't need a subquery here. All you need ...
MS Access	7/11/2009
Q: I have created a simple Access Database for tracking daily expenses and selling. My problem is in ... A: You will probably need to use a UNION query for this. First create a query that returns all records ...
Access Command button	7/11/2009
Q: Please I needs help on Access.I have a Form with this field DATE,QUANTITY,BOOKS,ISBN & AVAILABLE ... A: First, you do not have fields on a form, you have controls that may or may not be bound to fields in ...
Relationships & forms	7/10/2009
Q: Please help me connect the tables so i can use the forms to enter data to run queries and reports. ... A: Sorry that is not much clearer, but your additional info helps. The three tables that are at issue ...
Relationships & forms	7/10/2009
Q: Please help me connect the tables so i can use the forms to enter data to run queries and reports. ... A: First, the screen shot shows your tables have relationships set. Unfortunately the screen shot is ...
Relationships & forms	7/10/2009
Q: Please help me connect the tables so i can use the forms to enter data to run queries and reports. ... A: First, you do NOT need to connect the tables (using the Relationships Windows) to use forms, queries ...
report layout shows 2 pages, print preview shows 1	7/10/2009
Q: I have a very simple database, keeps track of addresses by category. Example: Photography - Yuen ... A: hmmm, that does sound wierd. You can send a zipped copy of the database to: dafiles at ...
Form / Combo box issues	7/10/2009
Q: So I think i'm getting closer, but still having some problems with my form. Perhaps it is best to ... A: 1) No, that is the way to do it. 2) If its showing a blank record, then your query is returning a ...
Displaying name of who is in the database	7/10/2009
Q: Scott, Is it possible for me to find out the IP or Computer name or the current person in a ... A: Yopu can use the sample found here: http://www.mvps.org/access/modules/mdl0055.htm Another tool I ...
Checkbox/textbox pairing	7/10/2009
Q: I have a database that I have imported data into from an excel spreadhseet. Part of the spread sheet ... A: I don't think you need to do this. So before I provide a solution, I'd like to know more about your ...
Expiry date	7/10/2009
Q: some of the courses dont expire but some do. i have entered an expiry field on the course form, ... A: You do this in a query. In your query add a column like so: Expiration: ...
POP Up in MS access	7/10/2009
Q: I have created a database where there is a follow up, i want to put up a pop up window such that ... A: Without knowing how one determines if a followup is necessary its hard to advise. But you can use ...
Parameter Query	7/9/2009
Q: I need a Parameter Query to accept more than one entry so i can have it return the resuls for ... A: I rarely use Parameter queries because they are so limiting. The way around this is to use a form to ...
ID column	7/9/2009
Q: I'm working on a database. I want to include a field named "ID" that is NOT the primary key and want ... A: Again, I can't answer a lot of your questions because it deals with YOUR workflow. I can tell you ...
Replicating in MS Access 2007	7/9/2009
Q: I was using the replication feature in earlier versions of MS Access, to be able to work on my ... A: Replication exists in Access 2007. If you look up Replication in Access 2007 you will find ...
Auto populate fields	7/9/2009
Q: I have a multi-column combobox in a form that I want to have autoupdate 3 text fields. i have ... A: What is the bound column, 1? If you set the controlsource to read the appropriate column, you can ...
Auto populate fields	7/9/2009
Q: I have a multi-column combobox in a form that I want to have autoupdate 3 text fields. i have ... A: I think the problem here is that you are using unbound controls. When you set the value of an ...
Covert number of days to date	7/9/2009
Q: i have this issue of converting days to date. eg 18809 which is: 188 are days starting from first ... A: This is assuming, the value is a text field, if not it need to be connverted to one. This would be ...
Running a Query Based on Checkbox Results	7/9/2009
Q: ! I'm sorry I keep coming back to you, I tried to find this answer, and couldn't. Could be there, ... A: No problem in coming back to me when you need help and can't find a similar question. I need to ...
ID column	7/9/2009
Q: I'm working on a database. I want to include a field named "ID" that is NOT the primary key and want ... A: I've answered several questions that were essentially the same as yours. The first issue is that you ...
Form Design	7/8/2009
Q: I asked a question earlier about using unbounded controls and realize that there is probably a much ... A: First, I don't see the need for the Report dates table. I would just have two tables: tblReview ...
Form Design	7/8/2009
Q: I asked a question earlier about using unbounded controls and realize that there is probably a much ... A: First, I would add an autonumber primary key to your table. Second, I would set the table as the ...
setting up a complex query in Access	7/8/2009
Q: I am using Microsoft Access 2002-2003 and am having problems setting up a complicated query using ... A: When faced with complex queries, I try to build the queries using interim queries. I define an ...
Form layout	7/8/2009
Q: I have created a form with the Access 2007 forms wizard. The form has the fields laid out in 2 ... A: First, you don't have fields on a form. You have controls that may or may not be bound to a field in ...
Auto populate fields	7/8/2009
Q: I have a multi-column combobox in a form that I want to have autoupdate 3 text fields. i have ... A: I wrote an article on this issue which can be found here: ...
MS Access Maximum Users	7/8/2009
Q: I have a shared Access application where all users should log in & then they have to use the same ... A: Generally, I've found Access tends to bog down when the number of concurrent users exceeds 20. ...
IIF Statment	7/8/2009
Q: I am working on a query that has mutliple dates. This is the table structure RequiredStart ... A: Try IIF(([Signature1] > [RequiredStart] AND [Signature1] < [RequiredEnd]) AND (Signature2] > ...
connect sql server thorugh ms access	7/8/2009
Q: I want to connect sql server 2000 to access data from ms access project wihtout wizard. Connect ... A: I'm not sure where you got that code from. Since the Provider is static, I don't know why you are ...
update fields	7/8/2009
Q: Scottgem: I have three tables, Customer Info (primary key is the ID number), Invoice Header ... A: Well this does change the answer. As long as you have a primary to match the new data to, then just ...
Input Forms	7/7/2009
Q: Hey Scott: I'm using a form to add records to my database but if I use any kind of formating, even ... A: You can't use any formatting in entering data, with the exception of a new line. If you need the ...
MS Access Crash and Modules Missing	7/7/2009
Q: while entering data into my access 2007 db, the program crashed. Upon relaunching, all of my vba ... A: I use a change management system in my apps, Its not a full versioning system, that keeps track of ...
Help setting up table relationships	7/7/2009
Q: This may seem pretty basic, but I'm having trouble... I have three Access tables: Donors, Gifts, ... A: Set the Default value of the DonorID contol on the Action subform to: =Forms!formname!controlname ...
Overflow Message	7/7/2009
Q: as a select query 'System Resource Exceeded' error message appears. The reason for a make-table is ... A: Ok, so the query doesn't work at all. what you need to do is build the select query, bit by bit. ...
update fields	7/7/2009
Q: Scottgem: I have three tables, Customer Info (primary key is the ID number), Invoice Header ... A: Why are you entering data thru a query? Any data entry should be through forms. You should have a ...
access self join	7/7/2009
Q: i have to do an access project like a folders and files tree in windows that under any folder U can ... A: There are a number of ways, depending on your work flow. One possibility that comes to mind is ...
Searching Data base	7/7/2009
Q: I have a form (based on excel file) with information such as name (last & one for first), city, ... A: I do this routinely with names. I use the Combobox wizard to create a search combo (3rd option). I ...
Entity/Attribute as solution to too-many-fields	7/6/2009
Q: You answered a question on 9/26/2008 where the individual was trying to deal with a table with many ... A: Yes, that would be a one to many and yes that would all multiple entries with the same FK. But I ...
Help setting up table relationships	7/6/2009
Q: This may seem pretty basic, but I'm having trouble... I have three Access tables: Donors, Gifts, ... A: Assuming that an Action can only be associated with one Gift, then you simply have a GiftID foreign ...
Checkbox in form to access (filter) data from table	7/6/2009
Q: I am facing a problem I unfortunately cannot solve. Been searching for the everywhere...but noone ... A: First, if a person can only have one status per project you can set a multi-field index on the ...
Checkbox in form to access (filter) data from table	7/6/2009
Q: I am facing a problem I unfortunately cannot solve. Been searching for the everywhere...but noone ... A: The problem here is, most likely, table structure. I'm going to assume you have a table that has a ...
Expiry date	7/6/2009
Q: some of the courses dont expire but some do. i have entered an expiry field on the course form, ... A: You aren't listening. You need to use terms correctly so we can understand each other. Do you have a ...
Combo Box and Option Group	7/5/2009
Q: I am new to access and was wondering if you could help me with this problem. I creating a form. I ... A: You can reference a value entered into a control on a form using the syntax: ...
Combo Box and Option Group	7/5/2009
Q: I am new to access and was wondering if you could help me with this problem. I creating a form. I ... A: Code is contained in a VBA module, so they are, essentially, the same thing. And you do need to use ...
unbound form	7/4/2009
Q: under what circumstance and when to use unbound form to collect info is the better choice. if i use ... A: First, Access is, by default, a multi-user platform. If you set it up right, you should not be ...
Mass E-mail from Access 2007 query	7/4/2009
Q: I’ve seen in your answers you have helped others with the same type of question, but I can’t seem to ... A: "but I can’t figure out how to automatically advance the form to the next record and send the next ...
access 2007 query if	7/3/2009
Q: I'm designing a form using access 2007, in this form I have a page which has two drop down lists ... A: This is actually a standard technique called cascading or synchronized comboboxes. This article: ...
Expiry date	7/3/2009
Q: some of the courses dont expire but some do. i have entered an expiry field on the course form, ... A: First I think we need to get our terms straight. I believe that's the problem here. A subform is a ...
Expiry date	7/3/2009
Q: some of the courses dont expire but some do. i have entered an expiry field on the course form, ... A: As a general rule we do not store calculated values. So there is no need to store this information. ...
Running INSERT via VBA	7/2/2009
Q: I've created a custom form that I want to use enter data into my table. My code currently looks like ... A: First, why are you using an unbound form? Second, drop the single quotes from the fieldnames. They ...
(a) Formatting Numbers in a field and (b) using queries to update fields	7/2/2009
Q: My database, though rudimentary, seems to have come a long way. I have a few additional questions. ... A: First, on the sequential number. Since they want a number from 1-1000 and will start the count at 1 ...
Ignoring Barcode Scanner Input	7/2/2009
Q: I have an MS Access application where the user is scanning barcodes and a corresponding record in a ... A: Something doesn't make sense. Access doesn't work that way. A scanner is alternative input. For ...
Ignoring Barcode Scanner Input	7/2/2009
Q: I have an MS Access application where the user is scanning barcodes and a corresponding record in a ... A: This may be possible but it depends on how the scanner input is processed. Does the user just hit ...
Access Forms	7/2/2009
Q: I have a Form and when I click a button to add a new record.The first record never gets ... A: Frankly, I haven't a clue. If you have an embedded subform and you add a recor din the subform, it ...
creating new table from data from two other tables	7/1/2009
Q: I'm working in MS Access 2003. I need to create a table that is made up of portions of two other ... A: Why? Why not just use a query that joins the two tables? The query will always show the current ...
Quick question about VB in Access vs Excel	7/1/2009
Q: Just a quick question I thought you could help me with. I have recently been using VB in Excel to do ... A: There are major differences between Access VBA and Excel VBA (in fact there are major differences in ...
Maximizing form	7/1/2009
Q: To begin, I'm using Access 07 and for my database I set the Document Windows option to Overlapping ... A: This is a quirk in Access. When you maximize one form, all subsequent forms open maximized. If you ...
Formatting a Report in Access 2007	7/1/2009
Q: I created a report on Access 2007 and I am having a formatting issue. Is there anyway to get rid of ... A: You can't enter it EXACTLY as I gave you. Because I have no idea what the object names of your ...
E-Mailing From Access in Lotus Notes	7/1/2009
Q: Scott, I've got my e-mail code set up and everything works fine: Here is the small segment I am ... A: I don't think any of that will do it. As I recall, Notes will send out the e-mail from the mailbox ...
Page Setup Print Options Problem	7/1/2009
Q: I hope you can help me with this problem. For most of my reports, my margins are 0.5. However, I've ... A: Probably her printer driver. Try setting the printer to a specific printer and using some generic ...
Query Criteria	7/1/2009
Q: I have a question, I am trying to link a query's criteria to a form, so that when I type a number ... A: You can reference a value in a control on an open form using the syntax: Forms!formname!controlname ...
Formatting a Report in Access 2007	6/30/2009
Q: I created a report on Access 2007 and I am having a formatting issue. Is there anyway to get rid of ... A: Set the Can Shrink property of the control to Yes. However, if you are doing a columnar report that ...
Microsoft Access 2007 Relationships	6/30/2009
Q: I'm a new user and I am really struggling with primary keys and relationships. Can you give me a ... A: A primary key is a unique identifier for a record. A foreign key is a field in a related record that ...
Compare dates in Access VBA	6/30/2009
Q: my question is regarding comparing dates in VBA. I need to calculate eligibility_date based on a ... A: I would do this differently tempDate = DateAdd("yyyy", 1, HireDate) tempYear = DatePart("yyyy", ...
Date	6/30/2009
Q: this is my last attempt, sorry to bother you but i followed your instructions and I am getting a ... A: Again, you need to show me exactly what you are seeing. What is the DateDiff lines that's ...
Same Query, Different Parameter	6/30/2009
Q: I'm sure this is a simple thing but I'm having trouble finding my way around it. I have a stored ... A: I would have the user input the criteria on the FORM. (I rarely use parameter prompt queries). Add a ...
Auto Email	6/30/2009
Q: I have 2 very simple tables - one listing multiple supplier numeric IDs and associated email address ... A: Have you looked at the SendObject method? That allows you to send the results of a query as an XLS ...
(a) Formatting Numbers in a field and (b) using queries to update fields	6/29/2009
Q: My database, though rudimentary, seems to have come a long way. I have a few additional questions. ... A: 1. Wherever you need to display this code you use an expression like: =[SiteCode] & ...
Access 2007 Forms and Table Links	6/29/2009
Q: I am trying to create a db for a consignment business. I have a customer table, an inventory table, ... A: First, I you mention macros, I recommend that you use macros sparingly. They are very limiting and ...
Merging, Updating Database	6/29/2009
Q: I have a database in use at the place in which I work. It's constantly being updated and changed. ... A: First, you need to split the database. All the tables go in the back end and everything else is in ...
Inserting records into temporary table based on selected existing records	6/29/2009
Q: I am trying to insert records into a temporary table based on a selection from a combobox. The ... A: A data mismatch error means you are trying to use criteria that involves two different datatytpes. ...
Date	6/29/2009
Q: this is my last attempt, sorry to bother you but i followed your instructions and I am getting a ... A: What did the error say and what was the exact line highlighted. Please don't give up on this, its ...
Date Calculation	6/29/2009
Q: I need to know how to do a date calculation where it puts the days between two dates in one box the ... A: Yep, the best way would be to do it in your query. Add a column to your query like so: Time Past: ...
How to secure access database	6/29/2009
Q: i want secure my database for example when i distribute my database to other users the user just do ... A: Use Tools>Startup to configure the app so that only your custom emnus are avialable. Then save the ...
Vba run a query syntax required	6/28/2009
Q: I have written a query which when opened returns all the table names in an mdb.file. This works ... A: Glad you got it working, but I'm not sure why you are doing this: Set QDF = ...
Vba run a query syntax required	6/27/2009
Q: I have written a query which when opened returns all the table names in an mdb.file. This works ... A: I told you. Since the reason you want to do this is to populate a combobox with the list of tables, ...
Vba run a query syntax required	6/26/2009
Q: I have written a query which when opened returns all the table names in an mdb.file. This works ... A: First, when using embedded SQL you have to buid the SQL statement using concatenation. I'm not sure ...
Access question	6/26/2009
Q: I have an Excel document that we use for a calendar for each shift. Each shift is 4 hours. I am ... A: You can use Access for almost anything and this is no exception. I'm not sure what you mean by ...
Time Clock Functionality	6/26/2009
Q: I have created a database for our department to collect all of our usage statistics. I have been ... A: Ok, here ya go: Public Function RoundTime(dteStart, dteEnd) As Long Dim intDaily As Integer, ...
table design	6/26/2009
Q: I have a table of customer transactions with columns as follows: "Item Number," "Customer ID," ... A: I just tried this. I created a table with the same fields. Next I populated the table with a few ...
Cascading Combo Boxes	6/26/2009
Q: I am trying to set up a cascading combo box and I can not get it to work. The database stucture is ... A: Ok, The RowSource of the Second combo needs to join the tblCards and tblCardHoldersCards on ...
MS ACCES/VB	6/26/2009
Q: I've got to build a DBMS..My query is that whether i should go for ms access or vb?? A: Actually that's not the proper choices. The proper choice is whether to go ALL Access or use VB as a ...
Date Calculation	6/25/2009
Q: I need to know how to do a date calculation where it puts the days between two dates in one box the ... A: Hmm they worked with your first example. This is easier to do in a function: Public Function ...
Cascading Combo Boxes	6/25/2009
Q: I am trying to set up a cascading combo box and I can not get it to work. The database stucture is ... A: Can you explain the application a little more. What do these cards represent? Are you trying to ...
Date Calculation	6/25/2009
Q: I need to know how to do a date calculation where it puts the days between two dates in one box the ... A: I would do it differently: First calc: =DateDiff("d",[LCODT],[CloseDT]) Second calc: ...
Cascading Combo Boxes	6/25/2009
Q: I am trying to set up a cascading combo box and I can not get it to work. The database stucture is ... A: Need more detail. what is the relationship between Card Holders and Cards. Is it many to many? What ...
DB structure follow-up question	6/25/2009
Q: Responding here to your question to my earlier posting. I've provided a screen shot of the ... A: I still think vouchers should be linked to the appointment. Since its possible that one voucher will ...
DB structure follow-up question	6/25/2009
Q: Responding here to your question to my earlier posting. I've provided a screen shot of the ... A: The issue of gift cards and vouchers helps justify your Detail table. The vouchers should join back ...
MS Access 2007 multiple tables in a report	6/25/2009
Q: I am trying to build a report from 12 related tables (approx. 4 fields per table). The report works ... A: First there was NO reason to split the tables by month. All you need to do is have a date field in ...
Access 07 - button on a continuous subform	6/25/2009
Q: I'm having trouble getting buttons to work on subform records. In Access 2003, I was able to perform ... A: This site lists the referencing you need to use with subforms: ...
Updating a table with a sequential count	6/25/2009
Q: Have a table with Customer Account Number and product ID. Want to update (currently null) the SEQ ... A: I'm not sure if an Update query will work, but I would give it a shot first: UPDATE tablename SET ...
DB structure follow-up question	6/25/2009
Q: Responding here to your question to my earlier posting. I've provided a screen shot of the ... A: If that works for you, it looks OK to me. One issue that may have prevented entering a record inthe ...
MS Access 2007 multiple tables in a report	6/25/2009
Q: I am trying to build a report from 12 related tables (approx. 4 fields per table). The report works ... A: No, but you don't add tables to a report unless you are using subreports. Instead, you should be ...
Entire List in Combo Box Doesn't Show	6/25/2009
Q: I'm working on a membership database with a table (tblMasterList) containing a record of about ... A: With that many members I would try filter the combo. I would add a combo where you can select their ...
Database structure	6/24/2009
Q: This is a general question about structuring a database, rather than a specific technical question. ... A: First, is the subform bound to the Appointments table or to a query? If its a query is there only a ...
Access 2007	6/24/2009
Q: Scott, I'm working with several reports that has formulas in the report design that calculates ... A: I'm not clear on what's happening here. As a general rule we do NOT store calculated values in a ...
pop-up form	6/24/2009
Q: I have a pop-up form which right now successfully pulls up records that are related by an ID number ... A: In the On Open event of the popup set the Default value SdIID control to the value fromt he calling ...
Access 07 - button on a continuous subform	6/24/2009
Q: I'm having trouble getting buttons to work on subform records. In Access 2003, I was able to perform ... A: First, I don't use Macros, I find them very limiting. Second, you have your referencing incorrect. ...
Access 2007	6/24/2009
Q: Scott, I'm working with several reports that has formulas in the report design that calculates ... A: Generally I prefer doing my calculations in the query the report is based on. But if you are using ...
Getting rid of Debug option	6/24/2009
Q: Depending on the error my users may have the choice to Debug. Is there a way to prevent them from ... A: First, you shouldn't be deploying production apps that may have such bugs. Error trapping is an ...
Sending an email from MS Access pulling an attachment from a table	6/24/2009
Q: I've created a form that sends an email to a recipient based on an address in a table and attached ... A: Access 2007 has an Attachments data type. I haven't used it yet (not sure I ever will) so I'm not ...
passing parameters	6/24/2009
Q: i have a query in which i have a parameter between [enter the first date] and [enter the last date], ... A: I almost never use parameter prompt queries. They are very limiting. What I do is use a control on a ...
Access Graph	6/23/2009
Q: I would like to include a graph in the report which follows some specific guidelines. The graph ... A: What you will need to do is create a query that groups the data points according to how you want ...
Send message every three months.	6/23/2009
Q: I have been asked to find a way to create a system in microsoft access that alerts staff members ... A: I would add a column to your query like so: Anniversary: DateDiff("m",[StartDate],Date()) Mod 3 ...
Workgroup access question	6/22/2009
Q: In my database I created one lookup form that is attached to a macro, the macro is linked to a query ... A: Sounds like you set some permissions that are restricting users to running that query. I dont use ...
deleting digits	6/22/2009
Q: I need to remove the last four digits of the zip code and the hyphen that precedes those four ... A: Do you really need to change the data? If you need to present only the 5 digit zip code, work from a ...
MS Access VBA	6/22/2009
Q: I have a form with a Optiongroup of 3 options "Unsatisfactory","Close to Completion" and ... A: I'd have to see your code and the exact line that's producing the error message. But there is ...
opne report based on different combo boxes value	6/21/2009
Q: I have a problem open my Ms access report based on different selections made in combo boxes. the ... A: First, did you try the code I gave you? When you concatenate in a text value it has to be ...
opne report based on different combo boxes value	6/21/2009
Q: I have a problem open my Ms access report based on different selections made in combo boxes. the ... A: Yep, since the last three combos return a text value, you need to account for that. Try this: ...
Parameter Query as RecordSource	6/19/2009
Q: I have a listbox with a RecordSourceType of "Table/Query". Is it possible to populate the list with ... A: First a listbox does not have a Recordsource it has a RowSource. I've found that its important, ...
record source?	6/19/2009
Q: AllExperts says I have to start a new question because the previous thread was too big. I'm sorry ... A: If you are using a table as the Recordsource then it will not show the expression since the ...
Database structure	6/19/2009
Q: This is a general question about structuring a database, rather than a specific technical question. ... A: What you need to understand is that each visit is a separate record. Whether the visit is a ...
Access 2007	6/19/2009
Q: Recently I became responsibile for managing a database. As I looked over a few of the pre-existing ... A: Looking up the IIF function in Access Help might have gotten you the answer more quickly. IIF is an ...
MS Access Query	6/19/2009
Q: I'm still working on the formatting. Now I am trying to do a query. I am good with simple queries, ... A: The answer is a Group By query. On your toolbar in Query Design Mode will be a Sigma icon (like an ...
Password Protect Folder	6/18/2009
Q: Scott, Hello and thanks. You have helped me a great deal prior so I thought I would come back to ... A: You can set rights on a file level. While you need to give Read/Write rights to the folder so users ...
record source?	6/18/2009
Q: AllExperts says I have to start a new question because the previous thread was too big. I'm sorry ... A: A Recordsource is a property like many other properties. You open the Properties dialog Select ...
Forms	6/18/2009
Q: The message you are getting indicates that you are trying to enter a value in a combobox that is not ... A: Every Control on a form and every field in a table has a Default Value property. On forms, its on ...
Excel's Lookup converted to Access Dlookup or Elookup	6/18/2009
Q: Excel's formula =LOOKUP(2,1/(B2:B27>0),B2:B27) will find the last value in a column or row greater ... A: There isn't an anlagous function because relational databases work differently then a flat file ...
Forms	6/18/2009
Q: I have built a database table that holds all of the credit cards held by our employees. The table ... A: First, don't apologize for the bother. If I dodn't want to answer questions I wouldn't put my name ...
MS Access 2003/MS Excel 2003/MS Word 2003	6/18/2009
Q: I am working in Windows XP with MS Access 2003, MS Excel 2003 and MS Word 2003. I initially created ... A: Sure, its because Wrod dors not understand Excel/Access Formats. That's why Word has its OWN ...
Combining Tables	6/18/2009
Q: I am really really new to Access, but for work I work with spreadsheets of various sizes and one ... A: You shouldn't have imported the second table as a separate table. You should have just appended it ...
Send message every three months.	6/18/2009
Q: I have been asked to find a way to create a system in microsoft access that alerts staff members ... A: Depends on what part of Outlook you are referring to. You certainly can link to your Outlook ...
VB Query	6/18/2009
Q: I created table name "Institute" that contains columns with name 'institute code' and 'institute ... A: Why are you doing it that way? If you use the Combobox wizard to create the combo from the table, it ...
Forms	6/17/2009
Q: I have built a database table that holds all of the credit cards held by our employees. The table ... A: Sure it can be done. But it doesn't have to be. Talk to your Oracle DBAs, ask them to create a Read ...
Forms	6/17/2009
Q: I have built a database table that holds all of the credit cards held by our employees. The table ... A: Actually this is not your fault. This is a common misunderstanding about Access and the solution is, ...
Report Filter by Unbound text box	6/17/2009
Q: I have an unbound text box on a report that is performing a numerical calculation based on a couple ... A: Try using the expression that is the Controlsource of the unbound control rather then its name. So ...
MS ACCESS	6/17/2009
Q: I was trying to copy data from one Access DB to Another. Was copy and pasting data is a good idea to ... A: If you have tables that inforce referential integrity and your paste would cause a violation then ...
Storing Y/N for Checkbox	6/16/2009
Q: Instead of storing the values 1 or 0, how do I get a checkbox control to store a Y or N? I'm ... A: You don't. The value of a boolean field is stored as zero for a false value and non zero for a True ...
Query	6/16/2009
Q: I need to know if I can have a query that will filter data with duplicate entry in one field and ... A: Simply sort on field 1. That will group all the like field 1 data. In a query, field 1 will ...
Access 2007 Pie Chart	6/16/2009
Q: "Hi I'm an intern at Perot Systems Corporation in Plano, Texas. I am currently creating a report ... A: I just played with this. If I right click on the chart and chose Open, Microsoft Graph opens where ...
Check box displaying text	6/16/2009
Q: I am not that familiar with Access VBA, and I wanted to have a check box display text when checked. ... A: I told you how. The code I gave you should display the words Checked or Unchecked. You can ...
Update Query - error	6/16/2009
Q: Running Windows XP Pro; Access 2007 I've built a table (Table) with related one-to-many table ... A: I will answer your question, but first I'm going to get up on my soapbox a bit. I strongly feel you ...
MS ACCESS	6/16/2009
Q: I was trying to copy data from one Access DB to Another. Was copy and pasting data is a good idea to ... A: First I did get your files, I haven't had a chance to look at them but expect to today. Copy and ...
Check box displaying text	6/16/2009
Q: I am not that familiar with Access VBA, and I wanted to have a check box display text when checked. ... A: There are a couple of ways. Each Checkbox will have a Label attached to it. So you can set the ...
organizing rows with similar data	6/16/2009
Q: So i have a dataset with the first column being the client's ID number and the proceeding columns ... A: A junction table is the same as any table. The only difference is how its used. I gave you the ...
Database structure	6/15/2009
Q: This is a general question about structuring a database, rather than a specific technical question. ... A: First, I'm in the process of taking a course on Database Design (not because I don't know it, but ...
Combo Box on Form in Access 2003	6/15/2009
Q: I am designing a form based on a single table in a Access 2003 database. The table is a customer ... A: In the initial wizard it may not give you the option for sorting. After the combo is setup, Go to ...
combo box selection	6/15/2009
Q: You were a great help to me before, now I have a new problem. I am trying to develop a form that ... A: I do NOT recommend that you use multi-table queries as the Recordsource for a form. Too often this ...
form Criteria	6/15/2009
Q: If you take a look at my form i dont have fields that store the criteria (Attached an Image). I ... A: Sorry, I had assume the start and end dates were hidden controls on the form that you were ...
organizing rows with similar data	6/15/2009
Q: So i have a dataset with the first column being the client's ID number and the proceeding columns ... A: So you want to see the services and the clients that use them? If so, simply place the services ...
Passing parameter when opening a report	6/15/2009
Q: I currently have a form where a user is able to select a f filter criteria from a listbox before ... A: OK, So the only criteria the form is supplying is the dates. the rest of the form just selectes ...
Passing parameter when opening a report	6/15/2009
Q: I currently have a form where a user is able to select a f filter criteria from a listbox before ... A: You didn't quite answer my question or maybe not clearly enough. You said the user selects a time ...
Passing parameter when opening a report	6/15/2009
Q: I currently have a form where a user is able to select a f filter criteria from a listbox before ... A: First I would NOT do it this way, but your problem is your WHERE clause is incorrect. A WHERE ...
Making Age Bands	6/13/2009
Q: I have a table with a column for ages and some other data (name, sex ect). I want to make a query ... A: PUBLIC Function AgeBand(dteDOB As Date) AS String Dim intAge As Integer ...
Hiding forms	6/13/2009
Q: I am working on a MS Access database 2007 containing tables of customers, vendors, and products. I ... A: In the first snippet you should reverse the order of the commands and hard code in the form name. ...
combo box selection	6/12/2009
Q: You were a great help to me before, now I have a new problem. I am trying to develop a form that ... A: First, you need to requery cboExpLine after a new Budget item is selected. What I would do is ...
MS ACCESS 2007 Autofill Fields VB Code	6/12/2009
Q: "When I make a new record on the data entry form I need to be able to type in the sponsor ID number ... A: First, I wrote a detailed article that answers your first question. You can find it here: ...
IIF Statement - Access	6/12/2009
Q: Good Morning, I am in HR and looking to build a report that generates anniversary reminders and ... A: You don't use an IIF. You set the Criteria for the date column in your query like so: BETWEEN ...
MSACCESS	6/12/2009
Q: i have a question regarding MS-Access: iam just learning MS-ACCESS dont know much about this ... A: This is not a simple question. The design of an Inventory application needs to take a specific form ...
Access Query REturning The Right Number of Rows But empty!	6/12/2009
Q: I wrote an Access application in Access 2007. I do have a lot of experience with previous Access ... A: I'm confused. If you run a query on a table with no critera (WHERE clause), then you should get the ...
Making Age Bands	6/12/2009
Q: I have a table with a column for ages and some other data (name, sex ect). I want to make a query ... A: I said to place it in a Global module. Under the Modules category select new, the VBE will open. On ...
Memo field cutting off	6/11/2009
Q: My problem is I have a memo field in a table cutting off, but the codes in the combo box all show ... A: OK, I'm even more adamant about the design problems here. There is a limit on the size of a Value ...
Making Age Bands	6/11/2009
Q: I have a table with a column for ages and some other data (name, sex ect). I want to make a query ... A: First, storing age is not a good thing. You should get DOBs and calculate age since storing age ...
Problem with calculated control	6/11/2009
Q: I am using Windows XP Pro (32 bit) and Microsoft Access 2007 (all critical updates from Microsoft ... A: I didn't say the problem was the result of denormalization. It has to do with the Refreshing of the ...
Memo field cutting off	6/10/2009
Q: My problem is I have a memo field in a table cutting off, but the codes in the combo box all show ... A: Ctrl+Enter is stored as two characters. so I'm betting when you counted 201 you didn't account for ...
Back end migration to SQL Server	6/10/2009
Q: First of all I want to thank you for all your help in the past with MS Access development. Now, we ... A: 1) Access objects that you run within Access run in Access. However, they run against the SQL Server ...
editing a microsoft access report	6/10/2009
Q: I "inherited" several access database files that were created by a predecessor in 2001. I want to ... A: Open the Report in Report Design Mode. Then delete the control and label for the column. Then move ...
No values in Lookup columns	6/10/2009
Q: I am using Access 2003, Windows XP. I have a table that I have assigned lookup columns to (4 of ... A: "It is more user friendly to see the foreign values when the table is viewed" Except that users ...
How to check if the query result it empty	6/10/2009
Q: I am trying to create a save script for a button in form. Following should happen: 1.INSERT INTO ... A: Frankly, I almost never use macros. VBA is so much more flexible and not that much harder than ...
How to check if the query result it empty	6/10/2009
Q: I am trying to create a save script for a button in form. Following should happen: 1.INSERT INTO ... A: Actually you are taking the wrong tack. Just use a DCOUNT() function If Dcount("*","queryname") =0 ...
No values in Lookup columns	6/10/2009
Q: I am using Access 2003, Windows XP. I have a table that I have assigned lookup columns to (4 of ... A: First, I do NOT recommend using lookup fields on the table level. These cause more problems then ...
Dlookup issues in Access 2007	6/9/2009
Q: I have created sales territories for the company I work with based on ZIP codes. From the program I ... A: Yes the PostalCode field is your foeign key. The only thing not having matching codes in your ...
MS Access FE & BE	6/9/2009
Q: Hope u remember me. I did told u that when they were submitting, we were receiving the blank ... A: Can you show me the SQL of any one of those queries? Also are you making changes to the query when ...
Autofill based on previous record	6/9/2009
Q: I am using MS access 2003 to organize some census data, and have a table which looks something like ... A: I'm not 100% following. From what I see, a line can be attributed to the same or a different ...
Dlookup issues in Access 2007	6/9/2009
Q: I have created sales territories for the company I work with based on ZIP codes. From the program I ... A: The problem here appears to be that you are used to speadsheets and are trying to apply that ...
Design Travel	6/9/2009
Q: Just to confirm using your example below. tblLeg LegID (PK Autonumber) ItineraryID (FK) DateType ... A: Not quite, You have a repeating group (TypeData). I'm not sure why you think you need multiple ...
Multiple Record Entry	6/9/2009
Q: I have two tables Table1(TripEventTypes) TripEventyTypeID EventTypeID ... A: Actually I think your structure is normalized, but to a degree where it loses efficiency. What you ...
Capturing who enters data into a form	6/9/2009
Q: How can I get a users name to be on every entry they make in a form? I need the entries to show ... A: Well the issue here is HOW you determine a user. My preference is to capture the user's network ...
Double entry need more help	6/9/2009
Q: i have an Ms Access database that i already entered data in it . But my supervisors need to do a ... A: Ok, I'm going to assume here that you want to verify that ALL data is the same between the two ...
MS Access FE & BE	6/9/2009
Q: Hope ui remember me. When our users (30+) were entering data in their standalone application and ... A: Someone updates an address from 123 Elm St to 321 Oak St. How do you know what address record was ...
Multiple Record Entry	6/9/2009
Q: I have two tables Table1(TripEventTypes) TripEventyTypeID EventTypeID ... A: Yes, Input Mask is property that is addressable thru VBA. Something like: If Me.Type = "Departure" ...
MS Access FE & BE	6/8/2009
Q: Hope ui remember me. When our users (30+) were entering data in their standalone application and ... A: Your problem is knowing if a record is an add or an update. If most of the records will be adds and ...
Memo History	6/8/2009
Q: I am using a memo type field and I also have the Memo history in a text box. After a user has ... A: Again I have to ask WHY you are doing it this way? What if you wanted to report on all memos for a ...
Memo History	6/8/2009
Q: I am using a memo type field and I also have the Memo history in a text box. After a user has ... A: I'm not sure what you mean by a memo history. If you are referring to User, datetime stamp and any ...
Linking Forms	6/8/2009
Q: I am using Access 2003. I have 1 table but several forms asking questions and memo for a dozen ... A: Umm, why would you do that? It sounds like you should not have a single table here. A single table ...
Problem with calculated control	6/8/2009
Q: I am using Windows XP Pro (32 bit) and Microsoft Access 2007 (all critical updates from Microsoft ... A: Frankly, your description indicates a denormalized database. What happens if the invoice is for more ...
Double entry need more help	6/8/2009
Q: i have an Ms Access database that i already entered data in it . But my supervisors need to do a ... A: Ok, this helps, but I still need a little more info. How is the ID generated? Is it an autonumber or ...
y/n, list, combo boxes and synchonization	6/8/2009
Q: I would like to have some sort of cascading set of text boxes. I don't know access lingo all that ... A: I would use a question table like the following tblQuestions QuestionID (PK Autonumber) Question ...
Input value from query to form	6/8/2009
Q: I have just stared working with MS Access and I have some trouble trying to enter a value which is ... A: Just lookup DSum and DLookup in VBA Help. It explains how to use them with examples. Basically they ...
MS Access FE & BE	6/8/2009
Q: Hope ui remember me. When our users (30+) were entering data in their standalone application and ... A: Well first, you can provide a query or report for the users to check whether there is data in the ...
ms access 2003 help	6/8/2009
Q: I have designed a database for my inventory control in ms access 2003. I have a form to enter ... A: Ahh, Now I understand. So if you change the type, but don't change code, the previously selected ...
ms access 2003 help	6/8/2009
Q: I have designed a database for my inventory control in ms access 2003. I have a form to enter ... A: I'm not clear what you are asking. Are you trying to do a synchronized combo where the selection of ...
Double entry need more help	6/8/2009
Q: i have an Ms Access database that i already entered data in it . But my supervisors need to do a ... A: It would have helped if you also showed my answer. Usually I'm pretty complete in my response. I ...
MS Aceess FE & BE	6/7/2009
Q: We have our front end and backend split and be on a shared folder on network drive. We have about ... A: This helps a little but I need to know specifically what your keys are. For example, if you are ...
Input value from query to form	6/7/2009
Q: I have just stared working with MS Access and I have some trouble trying to enter a value which is ... A: There are two ways to do this. First you can bypass the query and use a DSUM() function. A DSUM ...
MS Aceess FE & BE	6/6/2009
Q: We have our front end and backend split and be on a shared folder on network drive. We have about ... A: OK, let me see if I understand this. Since not all users have access to master back end, some of ...
MS Aceess FE & BE	6/6/2009
Q: We have our front end and backend split and be on a shared folder on network drive. We have about ... A: The definition of a split database is where the backend has all the tables and the front end ...
Reset Checkboxes	6/6/2009
Q: I have a subform which has 8 records. One field in each record is a checkbox. When I select (check) ... A: First, if you are generating SQL and running it from a VBA module then you need to create a string ...
Setting field format property via VBA	6/5/2009
Q: In a Public Sub I create a table and then define its field's properties like Field.AllowZeroLength = ... A: Yep, that good ole helpfuly Billy Gates ;) When creating tables you should only be concerned with ...
Setting field format property via VBA	6/5/2009
Q: In a Public Sub I create a table and then define its field's properties like Field.AllowZeroLength = ... A: When you start typing code in the VBE and you press the dot, Intellisense offers you a list of ...
How to create a report with date ranges	6/5/2009
Q: So I am very new at Access (as will become obvious). I have inherited an Access database for my work ... A: You have two options here. You can use a BETWEEN Clause in the criteria for the date field. For ...
Time Clock Functionality	6/5/2009
Q: I have created a database for our department to collect all of our usage statistics. I have been ... A: First, I would NOT use Initials as a Foreign key. Two reasons, Initials may not be unique and joins ...
Create E-Mail with Groupwise	6/5/2009
Q: I have 2 modules that I am using to create a Groupwise email, well when I run it it gives me the ... A: Well the Error message indicates that some value you are passing to some function is invalid. What I ...
MS Access query #Error after 20m records	6/5/2009
Q: I have a query that uses pulls 2 queries of data together to create a record set of about 24m ... A: I wonder if its a performance issue. The query may be processing too fast or too slow for the ...
Determining relationships	6/5/2009
Q: I have a general idea about determining relationships but I am in a quandary at the moment. I have ... A: If a person can only hold one rank at any one time, then on the person side its ONE. If only one ...
IFNULL action	6/4/2009
Q: I've got a form that has a drop-down list that reflects data from its corresponding table. Within ... A: Try SELECT [Media_Contacts].[ID], [Media_Contacts].[Company] & ", " & [Media_Contacts].[Last Name] ...
IFNULL action	6/4/2009
Q: I've got a form that has a drop-down list that reflects data from its corresponding table. Within ... A: A combobox can display multiple values during the selection process, but once selected only the ...
expiration date	6/4/2009
Q: I need a way to view an expiration date 3 yrs from a purchase date. I have created a table that has ... A: As a general rule we dont store calculated values. You can use the expression: ...
Creating table via VBA to Backend	6/4/2009
Q: This is a follow-up to my last thread regarding how to update the backend db with new tables or ... A: One of my friends has done all the groundwork on this. See his Back End Auto Update tool here: ...
Access Ribbon Tab Click Detection	6/4/2009
Q: I know that the Tab control does not have the onAction event implemented so how do I capture ... A: You did specify a "Tab Control" not a Ribbon tab. However, I have not worked with customized ribbons ...
Access Ribbon Tab Click Detection	6/4/2009
Q: I know that the Tab control does not have the onAction event implemented so how do I capture ... A: You use the On Click event of the whole tab control. When you click on a tab, the control returns ...
Open/Save Dialog in access 2007	6/4/2009
Q: desperately need your advice, I am developing an access 2007 application which is to be used on ... A: 1) Use the FileDialog that is built into Access. Search VBA Help for more info on how to use it. ...
Users	6/3/2009
Q: I have a table of users. I then have table with privalges(both an ID and a name). I also have a ... A: You are close, but not quite there: If DLookup("[PrivilegeID]", "staff", "[StaffBannerID] = " & ...
Users	6/3/2009
Q: I have a table of users. I then have table with privalges(both an ID and a name). I also have a ... A: Generally I have a structure like this: tblUser UserID (PK) FName LName LoginID PrivilegeID ...
Access 97: Count matching records function	6/3/2009
Q: I have inherited an Access 97 database which I have to keep that way, due to some design challenges. ... A: Well first you can do most of thsi with queries. But you will need three of them. First you will ...
Modifying Backend db	6/3/2009
Q: Thinking ahead here: After I split my db and distribute it to my parish (and possibly others who may ... A: 1) Correct 2) Correct. A class module is mostly used to describe an object. Allowing you to define ...
Modifying Backend db	6/3/2009
Q: Thinking ahead here: After I split my db and distribute it to my parish (and possibly others who may ... A: 1) Module is more generic. Any container of VBA code is a module. There are form modules, event ...
Allow Edit form issue	6/3/2009
Q: Man, talk about having patience with me! Still having problems, I once again think I have zero'ed ... A: I actually referred to this in an earlier response (as I recall). A subform is a form embedded into ...
Creating Reports from Linked Tables/Forms	6/3/2009
Q: Earlier last week I asked some questions regarding relational databases. It was to do with a few ... A: Reports are actually a bit easier. The key here is to create a query that has all the data you want ...
Modifying Backend db	6/2/2009
Q: Thinking ahead here: After I split my db and distribute it to my parish (and possibly others who may ... A: There are a few ways to deal with this. You can either have them send you the backend and not enter ...
Update Status Field from Date Field	6/2/2009
Q: If you recall, in access 2007, I was trying to udpate a STATUS field from an ENDDATE field. You ... A: If the Status is dependent on the EndDate, then you don't want to store it. Create a Public ...
printing reports	6/2/2009
Q: it worked perfectly. I only have one small issue. How do I select a printer when I am setting up a ... A: You do this in Report Design Mode. Select File>Printer Setup then when asked for default or Specific ...
Clearing data fields when a form opens	6/2/2009
Q: you're service is absolutely invaluable to us all! I'm a beginner who's been diving in head-first, ... A: Again, my test was done on a subform where the main form has the Date Entry property set to No and ...
how to open a form from a report	6/2/2009
Q: I have a report based on a group by clause (say group by customer name), which produces the summary ... A: Reports arre NOT interactive, so you can't do what you want. But there is a way to do this. Its ...
Clearing data fields when a form opens	6/2/2009
Q: you're service is absolutely invaluable to us all! I'm a beginner who's been diving in head-first, ... A: Hmm, I just tested this (with 2007). When the Data Entry property is set to Yes, the subform loads ...
MS Access Event Procedure	6/1/2009
Q: It's John again, Travel Itineray Database Design I am trying to input a button on a main form so ... A: The code I gave you will work. The Me. qualifier stands for the current form. Since ...
MS Access Event Procedure	6/1/2009
Q: It's John again, Travel Itineray Database Design I am trying to input a button on a main form so ... A: First, a subform is a form embedded within another form and linked to the parent form via a ...
Travel Itinerary Database Design	6/1/2009
Q: I am creating a database that will hold itinerary details. Each itinerary(trip) will have a ... A: If you need to store that type of info, then I would go a step further with child tables of ...
left-hand side vertical scrollbar	6/1/2009
Q: Is there a way to get a form's vertical scroll bar to appear on the left-hand side of the form ... A: None that I know of, nor would I recommend doing so. Users are used to some specific conventions for ...
USysRibbon problem	6/1/2009
Q: I have a table - USysRibbon - in which I have xml code to define a custom ribbon for my app. I've ... A: I haven't played with custom ribbons enough to answer that. I would, for the time being, make the ...
Travel Itinerary Database Design	6/1/2009
Q: I am creating a database that will hold itinerary details. Each itinerary(trip) will have a ... A: What's the roadblock? I might do things a bit differently though. I assume the ItineraryDetail ...
Clearing data fields when a form opens	6/1/2009
Q: you're service is absolutely invaluable to us all! I'm a beginner who's been diving in head-first, ... A: Set the Data Entry property of the subform to Yes. This will mean the form can only be used to enter ...
Printing reports	5/31/2009
Q: Just a quick question.. I have a form which shows all medical data for employees. I want to create ... A: Behind your button use the command: DoCmd.OpenReport "reportname"",,,"[keyfield] = " & ...
mailing label report	5/29/2009
Q: I have a report, r_FamStmt, which is a letter stating family contributions. My users will want to ... A: You might be able to play with the height and width properties of the Detail band as well as the Top ...
Report problem	5/29/2009
Q: I have a pop-up form (f_ParamFamStmt) that contains two controls: start date and end date. The user ... A: No I don't mean popups. If youy click the Office button in the top Left corner, then select Access ...
Resizing form to fit monitor screen	5/29/2009
Q: My app will likely be distributed to different computers with different sized monitors. How do you ... A: Generally I design forms to fit in 1024x768 which is pretty standard. However there are a number of ...
Report problem	5/29/2009
Q: I have a pop-up form (f_ParamFamStmt) that contains two controls: start date and end date. The user ... A: Hmm, I haven't use the tab docking feature of Access 2007 much, so this may be a quirk of that. ...
active form	5/29/2009
Q: Application defined or object defined error 2465 THis is what I get every time I open the form which ... A: The problem is that Route is not a valid object of the Active Form collection. What are you trying ...
Access 2007 Users	5/29/2009
Q: I have read quite a few responses on users loging into Access 07 but nothing with a complete ... A: Don't close the login form, just hide it or minimize it. You can then capture the user by ...
Report problem	5/29/2009
Q: I have a pop-up form (f_ParamFamStmt) that contains two controls: start date and end date. The user ... A: Try just setting the controlsource of ReportingPeriod to: = "Reporting Period: " & ...
Populating table/checkboxes in After Insert/On Current events	5/29/2009
Q: I've now tried the After Insert code when entering a new record. When I check a couple of symptom ... A: You need to remember to always use your actual object names when someone gives you code samples. I ...
Designing a Database	5/29/2009
Q: I'm designing a database for use on my farm. It is primarily being used to log spray events. I am ... A: No, you should have a Crops table and use a foreign key of the CropID to identify what crop the ...
Designing a Database	5/28/2009
Q: I'm designing a database for use on my farm. It is primarily being used to log spray events. I am ... A: Is CropID an attribute of the event or an attribute of the SprayEvent? That's what you need to ...
Lookup Values cont'd	5/28/2009
Q: Just one final question. You said that lookup values should be maintained separately, not during ... A: I've never seen that feature before, I think its new to 2007, I'll have to check. But its designed ...
Designing a Database	5/28/2009
Q: I'm designing a database for use on my farm. It is primarily being used to log spray events. I am ... A: Yes, Setting up Spray1, Spray2 etc. is a violation of normalization rules. But let me try to ...
Lookup Values cont'd	5/28/2009
Q: Just one final question. You said that lookup values should be maintained separately, not during ... A: To be honest, I'm not familiar with that. How are you getting to that? In my design, whenever I ...
Linking two tables to one.	5/28/2009
Q: I am using Access 2003, and I have a design question. I have four tables: Incidents Evidence ... A: Ahh, yes it makes sense now and this is a limitation of the sub-datasheet feature, which can only ...
Lookup Values	5/28/2009
Q: For my lookup tables I typically have 3 fields: PK (autonum), lookup value (text), and active ... A: I think the problem is more workflow than anything else. Lookup values should be maintained ...
Few basic questions	5/28/2009
Q: Just want to say that I've scoured books, online sources and asked around for answers to a few basic ... A: 1: You will not be able to view the document on the form itself. But, you can store the path to the ...
Lookup Values	5/28/2009
Q: For my lookup tables I typically have 3 fields: PK (autonum), lookup value (text), and active ... A: Yes that is an issue but fairly easily dealt with. First, I just want to correct something, you ...
Displaying values for True checkboxes only, not False	5/28/2009
Q: very helpful! In my report, I now have in the Details section a list of visits for each patient at ... A: Ok, the code will only work when you are adding a new record. Also, since the checkboxes are now ...
MS Access Questions	5/28/2009
Q: I would like to know 1. How to access a remotely located MS Access Database. 2. How to combine two ... A: 1. Depends on what you mean by remotely located. It is on a network server, or on some other network ...
Search with a Form Question	5/28/2009
Q: I am trying to create a form in Access 2003 to bring up some data that I have in a table. I have ... A: You can do this without any code. Use the form wizard and create a form bound to your table. Forget ...
Linking two tables to one.	5/27/2009
Q: I am using Access 2003, and I have a design question. I have four tables: Incidents Evidence ... A: Ok, You shouldn't have a problem linking. What you should be doing is dragging the EvidenceNo onto ...
MSWord to MSAccess	5/27/2009
Q: Good Morning Scottgem, I need a way to take a manually populated MSWord Template and have that ... A: Take a look at InfoPath. I believe you can do free text in an InfoPath for that can be linked or ...
How to update file paths when database is moved	5/27/2009
Q: I am working with ms access to develop a database that I would want to distribute to colleagues on ... A: I've been using that function since Access 2K, maybe earlier. I'm not sure what you mean by ...
Output dynamic array results into table format	5/27/2009
Q: Scott: You been very helpful in the past, so I need to ask another question. I am building a ... A: Generating it as a pivot table is your answer. The key is how you are building the array. While ...
Labels	5/27/2009
Q: I have 2 questions, Question 1 I have created an order entry form that will print out an invoice and ... A: First, in my original response, I suggested that box count is an attribute of the ORDER not the ...
Microsoft Access 2003 Question	5/27/2009
Q: I am having trouble with an append query that I created. What I need to do is say I have a file ... A: First, if you are getting an error message its best to produce the EXACT message to help us diagnose ...
adobe acrobat ribbon tab	5/27/2009
Q: I "installed" my access application on another computer and I noticed that there's an adobe acrobat ... A: it has to do with the way Acrobat was installed on that machine. Whoever installed it probably has ...
MSWord to MSAccess	5/27/2009
Q: Good Morning Scottgem, I need a way to take a manually populated MSWord Template and have that ... A: In other words you are using a Word document to gather information? May I ask why? This is the most ...
How to update file paths when database is moved	5/27/2009
Q: I am working with ms access to develop a database that I would want to distribute to colleagues on ... A: You have to make your links more generic. The following function returns the current path of the ...
Using Email to gather data in Access 2007	5/27/2009
Q: When I use the "Collect data through e-mail messages" function in Access 2007, most of my tables ... A: With Append/Update queries. If you are doing an update, your e-mail should include the primary keys ...
Using Email to gather data in Access 2007	5/27/2009
Q: When I use the "Collect data through e-mail messages" function in Access 2007, most of my tables ... A: Because you may not be able to update a query. If you need to use an e-mail to gather data for ...
Updating specific records based on user selection	5/26/2009
Q: You were assisting me last week before I went on a short holiday vacation. My question was with ... A: Try it this way: strSQL = "UPDATE tblAwards SET ProjID = " & "[Forms]![frmProjects]![ProjID] " & _ ...
Linking two tables to one.	5/26/2009
Q: I am using Access 2003, and I have a design question. I have four tables: Incidents Evidence ... A: Are the structures of the Exhibits and Testimony tables different? If not, you shouldn't have two ...
data from COM1 into Access table or form	5/26/2009
Q: How to put know formatting delimited ASCII data from COM1 into Access table, form? thanks ANSWER: ... A: You need to talk to the people who make the device that ius feeding data to your COM port. They will ...
Height adjustment	5/26/2009
Q: I am using Access 2007. I need to adjust the height of each row individually, each row needs to be ... A: Don't apologize for asking questions. If I didn't want to answer I wouldn't be here. First, you ...
Height adjustment	5/26/2009
Q: I am using Access 2007. I need to adjust the height of each row individually, each row needs to be ... A: Not really. First you have a structural issue here. If you there can be multiple objectives for a ...
Joining Split ACCESS files	5/26/2009
Q: I have an MS-ACCESS aplication which does Inventory. While I can do 'Split database' into data files ... A: You are missing the point. There is no reason to rejoin the front end and back end to edit the ...
access approach....continuation	5/25/2009
Q: Well, let me try to explain this with a simplified picture.... We are beginners so you'll have to be ... A: 1) I'm not sure I understand the question here. But I'll take a stab at an explanation. A form is a ...
Displaying values for True checkboxes only, not False	5/25/2009
Q: very helpful! In my report, I now have in the Details section a list of visits for each patient at ... A: 1. No this is not hard. It should only take a few minutes. It will need to be done while the3 ...
Joining Split ACCESS files	5/25/2009
Q: I have an MS-ACCESS aplication which does Inventory. While I can do 'Split database' into data ... A: I think part of your problem here is that you are using the wrong terms which is giving you an ...
Transferring ListBox items	5/25/2009
Q: How do I write the code on a command button's OnClick event to transfer some items from one listbox ... A: Are you trying to simulate what happens when you are in form design and you select fields on a box ...
Trying to get access to open PDF	5/24/2009
Q: Good morning I am trying to get my Access data base to Search for specific PDF file"s and then open ... A: If the files are in different folders, you are probably best off having the user select the files. ...
Auto Word-Wrap in Access	5/24/2009
Q: I want to show data in Table-View or Multi-Column List- Control. How can Access show them with ... A: The only way to do this is on a form in continuous form mode. You have to use the On Current event ...
Labels	5/24/2009
Q: I have 2 questions, Question 1 I have created an order entry form that will print out an invoice and ... A: Q1 I think your problem here is structure. In an Order Entry database an order should comprise two ...
Need guidance for direction on a project.	5/23/2009
Q: How are you? I am from the US and I work for a small gas station chain in midwest and I need help ... A: I need a bit more information to state whether Access is the correct tool or not. It certainly can ...
Printing MS-Access 2003 reports in DOS mode	5/23/2009
Q: I use MS-ACCESS 2003 and a Dot-matrix printer and an inkjet printer. My operating system is ... A: This actually depends on the printer. You need to set the printer driver parameters. What you can ...
additional information...	5/22/2009
Q: I read the following question and answer on the site:I have a user who needs to insert blanks (can't ... A: I don't think you understood my answer. You cannot insert a "blank" in a numeric field. However, you ...
Displaying values for True checkboxes only, not False	5/22/2009
Q: very helpful! In my report, I now have in the Details section a list of visits for each patient at ... A: Nope, the solution lies in normalizing your data. Use of multiple Yes/No fields is a sign of a ...
access approach....continuation	5/21/2009
Q: Well, let me try to explain this with a simplified picture.... We are beginners so you'll have to be ... A: Ok, Now that I see that I understand why the master works for you. What I would have done is a ...
simple access question pls	5/21/2009
Q: So...I am missing something basic. I have 4 tables with linked field firstnamelastname; two tables ... A: Why get Excel involved? Why would you need to use a filter to get a unique list of Primary Keys?? ...
Access- qry updating col X in specific table's rows with data taken from similar rows of same table.	5/21/2009
Q: I do have a project of relatively small Flight Connections Browser (small network but still ... A: "Regarding composite keys I stick to them whenever I want to prevent records from duplicating when ...
Encrypting Backend db problem	5/21/2009
Q: I found that if I password encrypt my backend db my frontend does not open the app, just the generic ... A: Because you have to provide the password when you connect to the back end. This gets more complex. ...
Access- qry updating col X in specific table's rows with data taken from similar rows of same table.	5/21/2009
Q: I do have a project of relatively small Flight Connections Browser (small network but still ... A: First, it looks like you have a composite primary key (ORIG, DEST and GR8_CIRCLE. I do not recommend ...
Split db problem	5/21/2009
Q: I'm testing splitting my db. From what I've read, I want to deliver a split db to my parish so I can ... A: Hmmm Shift should work not sure why it wouldn't. You do have to hold it down until the app loads. ...
simple access question pls	5/21/2009
Q: So...I am missing something basic. I have 4 tables with linked field firstnamelastname; two tables ... A: "then introduce the third table into the query design box " No, This is not separate queries. What ...
Split db problem	5/21/2009
Q: I'm testing splitting my db. From what I've read, I want to deliver a split db to my parish so I can ... A: Is the path to the backend exactly the same on both PCs? When you split a DB, the backend file is ...
Listing multiple visits to clinic for each patient	5/21/2009
Q: I've got a database of patients for an HIV clinic with one record of each patient visit. One patient ... A: This should be easy, but I suspect you are missing the step of correctly using the Recordsource of ...
applying iif on matching records	5/21/2009
Q: i work in a trading company, and consequently we ship cargo, and we get a booking number for every ... A: You will need to do this in steps. Step 1 - Create a query that counts the booking number. ...
Combobox populated by another combobox	5/20/2009
Q: Hey, I have a switchboard, and embedded in that switchboard I have a subform. When a certain form ... A: I'm not clear what doesn't change? If there is a value in the Schools combo then the Clubs combo ...
simple access question pls	5/20/2009
Q: So...I am missing something basic. I have 4 tables with linked field firstnamelastname; two tables ... A: First I would NEVER use a field like firstnamelastname as a link. If you have such a field you ...
Updating specific records based on user selection	5/20/2009
Q: I am developing an application and need to establish projects with multiple funding sources. I ... A: The thing is it sounds like an award can apply to multiple projects, is that the case? If it is, ...
CurrentDb.Execute vs. DoCmd.RunSQL	5/20/2009
Q: What is the difference between CurrentDb.Execute and DoCmd.RunSQL? What are the ... A: DoCmd.RunSQL is an older command. CurrentDB.Execute is faster and doesn't show warnings. I will use ...
Updating specific records based on user selection	5/20/2009
Q: I am developing an application and need to establish projects with multiple funding sources. I ... A: I'm not following your work flow. Can an Award only have one project? Because that's what it seems ...
Combobox populated by another combobox	5/20/2009
Q: Hey, I have a switchboard, and embedded in that switchboard I have a subform. When a certain form ... A: First, I always name my PKs using the convention tablenameID. This way it makes it easier to ...
Combobox populated by another combobox	5/20/2009
Q: Hey, I have a switchboard, and embedded in that switchboard I have a subform. When a certain form ... A: First you can't do this with macros. Personally, I rarely use macros since they are so limiting. ...
applying iif on matching records	5/20/2009
Q: i work in a trading company, and consequently we ship cargo, and we get a booking number for every ... A: Let me see if I understand this. Each booking number represents one shipment. But you may have ...
subform combo box	5/20/2009
Q: I have a main form called Meeting and a subform called Participants. The participants subform has a ... A: First, Name is a reser4ved word in Access and shouldn't be used for object names. Second people ...
Record Insertion Problem	5/19/2009
Q: I have a form (f_BatchEntry) that allows for a batch entry of gifts from families. It's record ... A: What is probably happening is you aren't committing the current record before you do your Append. So ...
Netbook	5/19/2009
Q: I am thinking of getting a netbook so I can update Access while at a conference. Have you tried ... A: The only problem is the amount of data. Many Netbooks have only SSD so the internal storage is only ...
resequence an identity field	5/19/2009
Q: you avoided answering this question and I believe I understand why, But I have the same question and ... A: If you are referring to an Identity datatype in SQL Server or an Autonumber Datatype in Access, then ...
Update query	5/19/2009
Q: You answered this on 8/19/08 but I can't get it to work. I am using a Totals query to total values ... A: If you are being prompted that means Access can't find the references. Try it this way: UPDATE ...
Opening Word in Access	5/19/2009
Q: Is it possible to open a saved word document by clicking on a cmd button in Access? If so, is it ... A: The answer to your first question is yes. The answer to the second is no. You can use the command: ...
access synchronization	5/18/2009
Q: i am using access 2007 I have a Access db that is used by multiple users, some of the users have ... A: Access has a feature called replication. This feature allows you to keep remote databaes in sync. ...
Input Formulas into Access	5/17/2009
Q: I have a table with several different amounts in them. i need to input a formula into a different ... A: You don't. Access is not like Excel, you need to change your thinking a bit when you move from Excel ...
Text delimited file	5/17/2009
Q: Not sure if this is the right area but maybe you can help. I would prefer not to use access but a I ... A: Access would be perfect for this function. I don't know why you wouldn't want to use Access (or some ...
REODER OF TASK NUMBERS IN ACCESS 2007	5/17/2009
Q: I was wondering if you advise please. I am tyring to create a task table that can be reordered based ... A: OK, I understand. I was assuming that as tasks get completed on one day, you want to move any ...
REODER OF TASK NUMBERS IN ACCESS 2007	5/16/2009
Q: I was wondering if you advise please. I am tyring to create a task table that can be reordered based ... A: What you need is to add a task priority field to your table. This will NOT be a keyfield since it ...
IFF statements	5/16/2009
Q: Im having trouble using the iff functions. this is what im trying to get. If their age is between 10 ... A: What happens if they aren't a teenager? I can answer your question fairly easily, but since there ...
About Query	5/15/2009
Q: .i just want to know that when we make a query..so what is the function to put the find option? in ... A: I'm sorry but I don't quite understand what you arre asking. A query represents a subset of a table ...
syntax error	5/15/2009
Q: I'm getting a run-time error '3075' that says there's a "Syntax error (missing operator) in query ... A: Try: CurrentDb.Execute "UPDATE t_EnvNum SET FamilyId = Null WHERE FamilyId = " & Me.FamilyId & ";" ...
Object/field names	5/15/2009
Q: Is it better that names for access objects and fields in tables be as short as possible? I was told ... A: Short names are good, but mostly for coding purposes. I would not sacrifice intelligibility for ...
Subform with null data interfering with form	5/14/2009
Q: I have a form with a subform displaying data from a query. This form just has 2 buttons ( export and ... A: Why are you using that code? The idea is to close the form when there is a null recordset. Since ...
Access update query	5/14/2009
Q: This question may be a simple one but I have only a few forms under my belt. I have a form that is ... A: There is a lot written about the differences between bound and unbound forms. Too much for me to ...
Access update query	5/14/2009
Q: This question may be a simple one but I have only a few forms under my belt. I have a form that is ... A: You will need to generate and run an update query in a VBA module. Before I give an example of this, ...
help in requery plz	5/13/2009
Q: im building the code of the requery the usual one forms![formename]... . requery but in that way i ... A: If the IsOpen function isn't in Access 2000 you can create a custom function that will do the same. ...
Showing weekending date on grouped dates	5/13/2009
Q: On a Access 2000 report that tracks time off based on project, Weekending, and Category, I have it ... A: How are you calculating the week grouping? Scott<> Microsoft Access MVP 2007 Author: Microsoft ...
Conditional Formatting	5/13/2009
Q: Scott, I have had this question posted on "UtterAccess" for over a day now, but I have not recieved ... A: Sorry, I rarely use CF so I thought it was more robust. In this case you will need to use the On ...
Option Group	5/13/2009
Q: I want an option group to remember what option the user selected the next time he opens the form. ... A: Yes, you set the controlsource of the group to the field in the table. The caveat here is that the ...
Check before saving a record	5/13/2009
Q: I've been reading your answers and they are really helpful but now i have a question regarding ... A: I'm assuming here that you have a table with the TestcaseID as a foreign key and a status field that ...
ms Access	5/13/2009
Q: How do I use combo box to lookup separate table and pull not only Name but a checkbox into second ... A: The answer is; you don't. In a relational database data should only exist in ONE place. Since the ...
help in requery plz	5/13/2009
Q: im building the code of the requery the usual one forms![formename]... . requery but in that way i ... A: There are custom functions that will check if a form is open. Depending on the version of Access ...
Subform with null data interfering with form	5/12/2009
Q: I have a form with a subform displaying data from a query. This form just has 2 buttons ( export and ... A: You don't need a subform in this case. Make the subform the main form and put your buttons in the ...
Using a combo box to update a field in the same table using info from a separate table	5/12/2009
Q: Hey Scott, First off, I've read many answers you've done, and it's been really helpful. Thanks! I ... A: First, I have used this method many times. If you use the correct RowSource and column number it ...
about mde file	5/12/2009
Q: sir, i am learn & work in access i want mde file to convert mdb file but in ms access no any option ... A: Depends on the version of Access. If you look up MDE in Access help it will tell you the steps. If ...
Using a combo box to update a field in the same table using info from a separate table	5/12/2009
Q: Hey Scott, First off, I've read many answers you've done, and it's been really helpful. Thanks! I ... A: First, you should NOT have the e-mail field in the Tasks table. Access is a relational database and, ...
Validating control data MSACCESS	5/12/2009
Q: The problem i have is that i have a form of unbound controls, depending on an option from one of the ... A: You can use the Tag or the controlname properties for this. As you loop through the controls you ...
on database load	5/12/2009
Q: I'm trying create a function that maximizes my switchboard once a user double clicks the ms access ... A: Setup the switchboard as the default form in your Startup options. This is accessed through the ...
Reports Contd	5/11/2009
Q: Could you please explain that in more detail. I've never done this before... For example if i ... A: 75% of programming is in the concepts of IF...THEN...ELSE and looping. If you understand these ...
Reports Contd	5/11/2009
Q: Could you please explain that in more detail. I've never done this before... For example if i ... A: Select the band the control is in (Detail? Header?). Then select Properties and select the event ...
setting report's recordsource	5/11/2009
Q: Due to the complexity of my one report's query (utilizing subqueries that are filtered by ... A: There are two ways to do this. Pass the SQL to the report as an OpenArgs or reference a control on a ...
Expression Field error	5/11/2009
Q: Hey Scott, I;m having a problem with creating an expression field. CustID: ... A: If conditional formatting doesn't work, then use the On Current/After Update events to change the ...
dlookup	5/11/2009
Q: I am trying to do a dlookup and get an error. Here is what i have entered in the control source for ... A: Why are you doing this? If First name is not unique you will get incorrect results. I really need to ...
Problems with INSERT WHERE statement	5/11/2009
Q: WinXP MS Access 2000 I'm trying to append a record with vba code and I cannot seem to get it right. ... A: Well you could eliminate all the code by using a bound form. I really don't see any compelling ...
Expression Field error	5/11/2009
Q: Hey Scott, I;m having a problem with creating an expression field. CustID: ... A: The error message is pretty self-explanaroty. Since CustomerID is in both tables, you have to ...
dlookup	5/11/2009
Q: I am trying to do a dlookup and get an error. Here is what i have entered in the control source for ... A: First Name is a reserved word in Access and shouldn't be used for object names. That could be part ...
Getting form control to filter report	5/11/2009
Q: I have a report with a grouping that totals the contributions made by a family. The control is ... A: Because that doesn't do any filtering. You might be able to hide a group if it doesn't meet the ...
MS Access--Crosstab HELP!!	5/11/2009
Q: I have a table with multiple fields.... 7 of the fields represent different areas; but have the same ... A: Where do you get the idea a crosstab only allows 3 rows. It will suppoort as many rows as there are ...
Getting form control to filter report	5/11/2009
Q: I have a report with a grouping that totals the contributions made by a family. The control is ... A: Your going to have to use another query here. You need to create a query that groups by Family and ...
Auto-search by suggest box in Access2007	5/11/2009
Q: I'm writing an application on Access2007. My application is to support our customer to place order ... A: OK, I think I understand a little better what you want to accomplish, but I think you are going ...
Dynamic Crosstab Query	5/11/2009
Q: In an Access 2007 database, is there a simple way to create a dynamic crosstab query with a varying ... A: "I've added a 'FromDate' and 'ToDate' (Date/Time) to the query parameters" That's why. Its also one ...
ms access	5/10/2009
Q: how to learn codding in ms access as well as the symbol which we use among statements.like quotation ... A: There are several ways to learn coding. Taking classes, reading books etc. I wouild, of course, ...
I need to requery a form	5/10/2009
Q: I have a customers form in which I select the particular customer with a combo box on top. When I do ... A: Is the form bound to a query that references the combo's value as criteria? Probab;y better to use ...
Deleting records from active form.	5/9/2009
Q: Hey Scott, I have created a form of customer table using the wizard. How do I enter new customer ... A: if you are using an unbound form then you have to write your own I/O between the form and the table. ...
Deleting records from active form.	5/9/2009
Q: Hey Scott, I have created a form of customer table using the wizard. How do I enter new customer ... A: With a bound form, the record is committed when focus moves off the current record. Before that you ...
Deleting records from active form.	5/9/2009
Q: Hey Scott, I have created a form of customer table using the wizard. How do I enter new customer ... A: There are several options. When you open a form, by default it opens in edit mode, displaying the ...
Problems with INSERT WHERE statement	5/8/2009
Q: WinXP MS Access 2000 I'm trying to append a record with vba code and I cannot seem to get it right. ... A: Let me get this straight. You have a record in the Child table and you want to populate the ParentID ...
Problems with INSERT WHERE statement	5/8/2009
Q: WinXP MS Access 2000 I'm trying to append a record with vba code and I cannot seem to get it right. ... A: The syntax for an INSERT INTO statement works like this INSERT INTO table (fieldlist) SELECT ...
Subquery or not to subquery??	5/8/2009
Q: I have a couple of complex (in my opinion) problems with a database i am trying to make at work. i ... A: A datatype mismatch means you are comparing two different datattypes like a Text with a number. so ...
Browse to folder	5/7/2009
Q: Scott, You were right! In my last question to you (dated today - regarding browsing to a folder) I ... A: I'm a little confused here. If you are use the Ken Getz code, the File Dialog box that is opened is ...
MS Access 2007 Forms	5/7/2009
Q: i ve a database that have the following fields in a table called Books- Year of Publishing, Subject, ... A: This sounds like a homework question. Generally I don't answer those, but I will give you some ...
Table question	5/7/2009
Q: I have an Access DB that other users have access to. It has three tables of data. I have to delete ... A: I gather no changes are made to the data. What I would do is first see if the DBAs for the source ...
executing VBA code	5/7/2009
Q: Scott, I am using Access 2003 (v.11) with windowsXP Professional, version 2002 with 1.99 GB of RAM. ... A: I don't understand this. According to this article: http://support.microsoft.com/kb/824272 The ...
Select all items in listbox	5/7/2009
Q: I have an options group (CalculateTotalsGroup) and in that options group there's a listbox that ... A: Its defined by the loop. It starts with the value defined as the starting point in the FOR ...
executing VBA code	5/7/2009
Q: Scott, I am using Access 2003 (v.11) with windowsXP Professional, version 2002 with 1.99 GB of RAM. ... A: First, one of the parameters to the DIR function is the path to the folder. So all you need to do is ...
Select all items in listbox	5/7/2009
Q: I have an options group (CalculateTotalsGroup) and in that options group there's a listbox that ... A: Similar to what you have done. But you want to loop through the Items collection and set each item ...
Auto-search by suggest box in Access2007	5/7/2009
Q: I'm writing an application on Access2007. My application is to support our customer to place order ... A: I'm not sure what you are getting at here. I understand what you want to do, but not why. If you ...
Print rich text	5/7/2009
Q: I have a table containing a memo field linked to a rich text field on a form (Access 2003 Lebans ... A: Did you try using the Can Grow property? I know that controls that span multiple pages don't work ...
Dlookup with multiple criteria	5/7/2009
Q: I used Dlookup with multiple queries as below...It gives error as #Name...I have checked the field ... A: Looks good to me. Where are you placing the expression? If its a control on a form you might try ...
executing VBA code	5/6/2009
Q: Scott, I am using Access 2003 (v.11) with windowsXP Professional, version 2002 with 1.99 GB of RAM. ... A: Try looking up FileDialog in Access VBA Help. The code sample may be just what you want. Hope this ...
Serial Number	5/6/2009
Q: is it possible to create serial number (auto number) in query? Let`s say, we have records ... A: This doesn't change my answer. But it still doesn't help explain. Why would seeing the list of ...
Populate a text box with a file path using a file lookup screen that was launched by a button.	5/6/2009
Q: Good Morning - I apologize if this has already been asked, but I was unable to find this answer ... A: Yes it has been answered before, I just recently had a similar question. Lookup FileDialog in ...
Serial Number	5/6/2009
Q: is it possible to create serial number (auto number) in query? Let`s say, we have records ... A: You can use a running sum to assign a sequential number to records in a query. But what value is ...
Vb Code	5/6/2009
Q: Am developing a code in ms access. I have a form called Tizi which has three fields(Name,Rep,Bal). ... A: VBA (not VB) code will only operate on the currently selected record unless you use an Update query ...
MS Access - Date Parameter Query	5/6/2009
Q: I am trying to build a query based on start date / end date for a my Safety Records. In the query I ... A: Use BETWEEN Forms!formname!startcontrolname AND Forms!formname!endcontrolname as your criteria to ...
locking form	5/5/2009
Q: I have an access database that accesses several centralised databases via ODBC drivers. Works fine ... A: A couple of possibilities come to mind. One is checking the DSN to make sure the username has the ...
Multi user access	5/5/2009
Q: I am using an access 2000 database I have it so that more then one user can access the database at a ... A: The attachment in this article: ...
Wireless MS Access 2007	5/5/2009
Q: We have a small business and intend to add an integrated computer system with computers at each work ... A: I don't know any reason why any software should have a problem with a wireless system. The software ...
2007 Query Mailmerge Macro?	5/5/2009
Q: I regularly mailmerge from several query tables created in Access 2007. Rather than have to repeat ... A: If you create a merge document that uses the same datasource, then you should simply need to open ...
"Locking" forms	5/5/2009
Q: I have an access database that accesses several centralised databases via ODBC drivers. Works fine ... A: If you use it as an MDB rsather than an MDE does it work? Does it error out in all three instances ...
Filtering to Current Students	5/4/2009
Q: I work at a small ESL school, and we use Access (the newest one) to enter all our students' ... A: Set the criteria so that the Start date is less than (<) the period date and that the End Date is ...
Set Calendar Control to Curr Date on open	5/4/2009
Q: Scott: I have a Calendqar control on a tabbed form which I want to automatically set to discplay the ... A: Do you have a default date set for the control? If so, you can remove it or set it to Date(). Or you ...
Replace function in query	5/4/2009
Q: I'm using the replace function in a query to replace a carriage return with a comma and a space. ... A: Try playing with the font of the listbox control. Basically what is happening is an unreadable ...
vba advice	5/4/2009
Q: I have an unbound control called FamilyAddressInfo in which code populates it with address info as ... A: Since its all in one field there is no concatenation. What you need to do is a REPLACE function, ...
Text box	5/4/2009
Q: I need that my text box within form (and form as well) will grow simultaneously with the text. I ... A: What you can do is use the On Change event to adjust the height of the control. You will need to ...
Help Me	5/3/2009
Q: I was wanting help with building a query where the queries return value is updated by a value in a ... A: The solution is the TOP predicate. The problem is I'm not sure if you can create a canned query that ...
MS Access 2003	5/3/2009
Q: How can I create a command in an Access Menu? For instance if I have a Sub that I wish to process by ... A: Have you done some benchmarks, I don't quite see how a linked table would change the performance ...
MS Access 2003	5/3/2009
Q: How can I create a command in an Access Menu? For instance if I have a Sub that I wish to process by ... A: What code is behind OpenDatabaseUsingODBC()? have you looked up DSNless connections at ...
Generate Alphanumeric ID in Access Form	5/2/2009
Q: I am new to Access but am trying to design a database (form) that requires more advance knowledge, ... A: As you are new to Access let me explain a couple of things first. One, you do not have fields on a ...
Event which fires after undo?	5/2/2009
Q: Until now, I thought I was a pretty experienced access solution developer but this one has me ... A: What I did was open a form in Design mode. I made sure that Form was selected in the Selection type ...
Update infotmation of table	5/1/2009
Q: I have a table that is setup with old information which has a CustomerID as primary key. I have a ... A: You do not have to "create" relationships period. It is a good idea to formalize relations (use the ...
updating the Search combobox	5/1/2009
Q: I'm pretty sure that this is a simple thing, I just can't seem to put my finger on the code for it. ... A: In the On Current Event of the form, add a line of code like: Me.cboSearch = Me.txtPK where ...
Event which fires after undo?	5/1/2009
Q: Until now, I thought I was a pretty experienced access solution developer but this one has me ... A: You are going to be very red-faced and start kicking yourself when you read the answer. Its the On ...
Update infotmation of table	5/1/2009
Q: I have a table that is setup with old information which has a CustomerID as primary key. I have a ... A: You have to run an update query. First import or link to the CSV file. Then create a query that ...
vba advice	5/1/2009
Q: I have an unbound control called FamilyAddressInfo in which code populates it with address info as ... A: A textbox has an Enter Key behavior property (on the Other tab). This can be set to move to the next ...
vba advice	5/1/2009
Q: I have an unbound control called FamilyAddressInfo in which code populates it with address info as ... A: Well first, one of the things I changed when I started working with Access is having separate fields ...
MS Access 07 - Forms	5/1/2009
Q: I am working on a database for purchasing... i'll like to be able to select a product id/part # from ... A: Not only is it possible, but its pretty much SOP. If you look at any order entry template for ...
Yes/No message box	5/1/2009
Q: Hey Scott, Could you tell me how to create a Yes/No option in a message box in a macro? I have a ... A: You shouldn't need an Else then, though it may depend on what event you use. If No is chosen, then ...
Another schedule issue	5/1/2009
Q: I am trying to build a databse which will help me track the rental of four spaces at a non profit ... A: If you want to send me your e-mail address, I have an Access app I use that allows the user to enter ...
listbox record selection	4/30/2009
Q: I have a multi-select (simple) listbox which displays LastName first. If I start typing in letters ... A: That's what I've never liked about listboxes. Its not always easy to tell which is the selected ...
Selecting the first record in a table	4/30/2009
Q: I need to run a routine in Access 2007 that will perform a number of operations for each record in a ... A: Nope, what you use is a Loop. The following example will loop through a recordset Dim db As ...
Tracking Which Users Access My DB	4/30/2009
Q: Good evening. I am deploying a database to a large group, and I would like to see how often the ... A: Ahhh, The Run Time environment is NOT a front end. Its just a set programs that allow you to run an ...
HELP! parameter query within either a subform or main form	4/30/2009
Q: I have a hard time trying to resolve this issue. I Have an unbound form which has a subform in it. ... A: OK, that does make sense. So you are getting a parameter prompt when you open the form? The only ...
Access button press open hyperlink from lookup table	4/30/2009
Q: I have a table with the fields; id qyerytype file location i have a form with a combobox for the ... A: You don't need a hyperlink datatype. You weren't entirely clear, but I'm assuming that the Combo has ...
HELP! parameter query within either a subform or main form	4/30/2009
Q: I have a hard time trying to resolve this issue. I Have an unbound form which has a subform in it. ... A: If I'm following you, you have a subform in continuous form mode on an unbound mainform and you use ...
Tracking Which Users Access My DB	4/30/2009
Q: Good evening. I am deploying a database to a large group, and I would like to see how often the ... A: I have no idea what RTE is. But if you are not using Access as the front end I can be of little ...
cascading combo boxes	4/30/2009
Q: i have the following problem. the tables are listed below. both combo's are in sync. the problem i ... A: First your RowSource is wrong. It should be: cboPartID.RowSource = "SELECT pkPartID, PartName FROM" ...
Form Subform Question - Right Join Query	4/29/2009
Q: I’m trying to write a survey application with the following tables for storing questions and ... A: Let me see if I follow this. The question relate to a claim, correct? So the InsClaim file in ...
VBA querying	4/29/2009
Q: I have a form that contains a multi-select list control (LastNameSearch). The control's row source ... A: yes they were Single quotes and that should work. You need to check what strSQLK resolves to in ...
VBA querying	4/29/2009
Q: I have a form that contains a multi-select list control (LastNameSearch). The control's row source ... A: Take the SQL from the query and past it into your VBA module like: Dim strSQL As String strSQL = ...
CurrentUser and Search box	4/29/2009
Q: would ask you some following questions: 1./ In the qry, I have a field CurrentUser:CurrentUser() ... A: How do you get CurrentUser now? Its a function that retruns a value equal to the logged in ID. ...
Acc