You are here:
| Subject | Date Asked |
| Populate Combo Box | 2/11/2012 |
| Q: How do i populate a combo box from another in a form?I want to do it via ms access visual basic for ... A: I'm curious as to why you only rated me 8 on knowledgability? My answer was 100% correct. Your ... | |
| Populate Combo Box | 2/11/2012 |
| Q: How do i populate a combo box from another in a form?I want to do it via ms access visual basic for ... A: To populate a combo, you have to set its Rowsource. A combo's RowSource is either a list of values ... | |
| Import a ROW of related information | 2/9/2012 |
| Q: Scott: I'm working on building a huge Access DB using Excel data (upwards of 20000 lines per ... A: Run a series of APPEND queries: INSERT INTO table (StampID, TopicID) SELECT StampID, Category1 ... | |
| Query An Age used for a Mail Merge | 2/9/2012 |
| Q: I have a maketable query which is used for a mailmerge letter. To get an age, in the query heading I ... A: Use this for your age calc: Age: DateDiff("yyyy", [Date of Birth], Now())+ Int( Format(now(), ... | |
| Access 2007 Query multiple conditions | 2/9/2012 |
| Q: I'm trying to use the query expression to builder for the following: If the "Value" field is ... A: There are at least 3 ways to do this. I would do it creating a custom function. Another way is with ... | |
| Access and Linking Databases | 2/8/2012 |
| Q: So right now I have three databases. One is the master and two other are working databases. The two ... A: The first question is why do you have three separate databases? Do the people using the two working ... | |
| extract the underlying data | 2/8/2012 |
| Q: e.g --- **** **** **** 7315 A: I'm sorry your question is not clear. Are you using Access? Where is the credit card data? What are ... | |
| Forms | 2/7/2012 |
| Q: I am using Access 2010. In my database I have the following tables, among others, to provide me with ... A: I think something went wrong in posting because your table structure is not clear. But if Class and ... | |
| Microsoft Access Queries for Travel Agency | 2/7/2012 |
| Q: I'm creating a microsoft access database for a travel agency. And i'm not very familiar with access ... A: A query is used to pull information from a table or tables and/or filter information to meet ... | |
| Creating an invoice in Access 97 | 2/6/2012 |
| Q: I need to create an invoice form that automatically fills in customer name, address and phone number ... A: Do you already have a database setup? If not, you might try starting from a template. Any Order ... | |
| open 2 forms instead of only 1 | 2/6/2012 |
| Q: I changed my code for the login form from: Private Sub ButtonLogin_Click() 'Check that User is ... A: Sorry, you have to make sure your IFs have matching End Ifs Try it this way: Private Sub ... | |
| open 2 forms instead of only 1 | 2/5/2012 |
| Q: I changed my code for the login form from: Private Sub ButtonLogin_Click() 'Check that User is ... A: If Me.txtWachtwoord0 = Me.cboUser.Column(2) Then DoCmd.OpenForm "frmHoofdmenu" ... | |
| Database runtime error | 2/5/2012 |
| Q: i tried the Dlookup in the immediate window, your right there is no number that comes up but the ... A: tblUser should have a Foreign key that links to the Access Level ID from your Access Level table. ... | |
| Multi User Login Access 2010 | 2/5/2012 |
| Q: I struggle on the user login of our database. (access 2010 Database) We use the database to log the ... A: Actually that doesn't follow. An autonumber is generally used as a PK, but its not cause and effect ... | |
| Multi User Login Access 2010 | 2/5/2012 |
| Q: I struggle on the user login of our database. (access 2010 Database) We use the database to log the ... A: I suspect UserID is text so it should be: intUserLevel = DLookup("ToegangsLevel", "tblUser", ... | |
| Access / Infopath | 2/4/2012 |
| Q: I need to create an invoice form that will pull info that I currently have in an Excel spreadsheet. ... A: Yes I meant the Access Form Wizard. And Yes I'm not recommending InfoPath for this. As I sais, ... | |
| Multi User Login Access 2010 | 2/4/2012 |
| Q: I struggle on the user login of our database. (access 2010 Database) We use the database to log the ... A: You may have seen my blog on Login Security since your description is very similar to what I've ... | |
| Limit number of records for each id in a query | 2/2/2012 |
| Q: I have a complex query that returns large numbers of records (each record is a date) for each id. ... A: What you need is a top N of group query. This article explains how to create one: ... | |
| Access / Infopath | 2/2/2012 |
| Q: I need to create an invoice form that will pull info that I currently have in an Excel spreadsheet. ... A: Infopath is useful for distributing forms for collecting data. So what you want to do is link your ... | |
| Auto-date fill in form | 1/31/2012 |
| Q: I have a field in a form where you enter the "Opening Date", and the next field is "Due Date". I ... A: First, you don't have "fields" on a form. You have controls that may or may not be bound to a field ... | |
| Add Diameter Range Column | 1/31/2012 |
| Q: I have the following problem. I have a table containing articles [bearings] and one column contains ... A: What you do is create a table that has fields for the Article and the Diameter Range. Then create ... | |
| Subject: Synchronizing Combo Boxes Based on a Many-to-Many Relationship | 1/30/2012 |
| Q: Scott - If I interpreted your last suggestion correctly, this is where we're at now for the first ... A: I'm not sure why setting it to Null is not working. For the time being, I would just comment out the ... | |
| Enhancing report print in MS Access Data base | 1/30/2012 |
| Q: Printing reports from the data base rather takes a very long time when more than one user is logged ... A: How are your databases setup? Are they split? If not, they should be. That will help the issue. ... | |
| NESTED SUBFORMS | 1/30/2012 |
| Q: Good Morning and thank you for any help you may be able to offer in the following area. NESTED ... A: First, you can't nest a continuous form or datasheet view within another continuous form or ... | |
| Dynaically setting image property for detail section of a report. | 1/29/2012 |
| Q: Greetings, This is the first time using this site... I have a WinXP sp3 machine running Access ... A: Use both the ON Print and On Format event of the Detail band. Put code in like this: ... | |
| Synchronizing Combo Boxes Based on a Many-to-Many Relationship | 1/27/2012 |
| Q: Scott - Per your suggestion, I bound my second combo box to the CSID field in the Invoices table, ... A: Try going into your table and changing the default value of CSID field (in the data table, not the ... | |
| Access 2007 | 1/27/2012 |
| Q: (farzad wrote at 2011-02-19 08:34:23 hi dear all this is very simple to work in access you must ... A: The screen shots indicate that you need to do this a different way. You need to create a query that ... | |
| Synchronizing Combo Boxes Based on a Many-to-Many Relationship | 1/26/2012 |
| Q: I am following up on previous questions/postings to you. Thank you for your most recent ... A: A bound control has its ControlSource set to a field in the form's RecordSource. This then ... | |
| Formatting dates | 1/26/2012 |
| Q: Formatting dates: I need to display and enter dates in the following formats, as well as base query ... A: Thought I had answered this already. You can't set the form for the whole database. You have to set ... | |
| Formatting dates | 1/26/2012 |
| Q: Formatting dates: I need to display and enter dates in the following formats, as well as base query ... A: Unless set on an database level, Date formats default to Windows Regional settings. You can use ... | |
| MS Access | 1/26/2012 |
| Q: which function cannot be used for calculated controls in a report?¬¬ which control type do you use ... A: I don't know of any function that can't be used as part of an expression. There are functions that ... | |
| Synchronizing Combo Boxes Based on a Many-to-Many Relationship | 1/25/2012 |
| Q: I am attempting to create 2 synchronized combo boxes where the values for each combo box would be ... A: First, yes that should have been a ;, sorry about that. Correct, you don't need the name fields in ... | |
| Access 2007 | 1/25/2012 |
| Q: (farzad wrote at 2011-02-19 08:34:23 hi dear all this is very simple to work in access you must ... A: So you need do a query or Dsum that calculates the closing balance for the previous month and use ... | |
| Migrate Access 2007 to 2010 preserving VBA coding | 1/24/2012 |
| Q: Could you please advise how to migrate DB from Access 2007 to 2010 in such a way that VBA codings ... A: If the DB is in an ACCDB format, there should be no need for a migration. Just open the file in ... | |
| Lock control after data entry follow-up | 1/24/2012 |
| Q: Scott, I figured out the code that I asked you about a few minutes ago. The only problem now is ... A: If the fields are number datatypes, Access assigns a default value of 0 by default. You can remove ... | |
| Copying Information into an Unbound Text Box | 1/24/2012 |
| Q: Scott, Your help in the past has been amazing, so I will ask for help from you yet again. I'm ... A: Is there a reason you are doing this with an unbound form? Seems to me it would be easier with a ... | |
| Access 2007 | 1/24/2012 |
| Q: (farzad wrote at 2011-02-19 08:34:23 hi dear all this is very simple to work in access you must ... A: Your question is not clear. Balances are calcuated by adding incoming amounts and subtracting ... | |
| Date freeze | 1/24/2012 |
| Q: I have a combo box called 'Status' with the following options 'Open' and 'Closed' . When 'Closed' ... A: The thing you left out is WHERE you put this code. It should be in the After Update event of the ... | |
| Lock control after data entry | 1/23/2012 |
| Q: I have a question about an earlier answer of yours: Answer If Not IsNull(Me.controlname) Then ... A: Are you using your own control names? You are inserting the code in the right place, but if you are ... | |
| Access Module problems | 1/23/2012 |
| Q: One of my users tried to access her db this morning and upon opening was greeted with a message that ... A: Well first, this is not good design. Its an invitation to corruption. The database should be split ... | |
| Attachment dialog box controls | 1/23/2012 |
| Q: In Access 2007, is there a way to disable the "remove" button from the attachment dialog box, but ... A: I don't think so, but I don't like the Attachment data type. Instead I create an Attachment table, ... | |
| Data Type mismatch in criteria expression | 1/23/2012 |
| Q: I have an update query that is giving me the error message in the thread title. I know what field ... A: Try creating a select query with that criteria only. See if that's causing a problem. If not, then ... | |
| Access Module problems | 1/23/2012 |
| Q: One of my users tried to access her db this morning and upon opening was greeted with a message that ... A: Did you try a compact and repair? Can you restore from a backup? How is the database setup? Split ... | |
| Access 2010 reports | 1/23/2012 |
| Q: I have two tables, Personnel and Activities, with a many to many relationship; necessitating the ... A: Yep, In your query you need to create an Outer join between Personnel and PersonnelActivity. Right ... | |
| Data Type mismatch in criteria expression | 1/20/2012 |
| Q: I have an update query that is giving me the error message in the thread title. I know what field ... A: The only reason for a datatype mismatch is if you are trying to compare fields of different types. ... | |
| Display table record as Message Box/Multiple Condition | 1/19/2012 |
| Q: I have a form and a table login(user, password, role)where user can login with username and password ... A: If you don't want to use a combo, then you should use a DLookup to retrieve the password and other ... | |
| Access | 1/18/2012 |
| Q: I currently have an excel application as a front end with an access backend. Is it possible to run ... A: You could use Application.FollowHyperlink to open the spreadsheet, but this is not really opening IN ... | |
| Access Zip Code field | 1/18/2012 |
| Q: I am importing data from Excel (5 digit Zip Codes) into Access 2007 but when i import the zip codes ... A: How are you doing the import? Directly into the table? Manually or with code? Hope this helps, ... | |
| Access | 1/18/2012 |
| Q: I currently have an excel application as a front end with an access backend. Is it possible to run ... A: You could certainly load the Excel app from within Access. But why use Excel Userforms when Access ... | |
| Display table record as Message Box/Multiple Condition | 1/18/2012 |
| Q: I have a form and a table login(user, password, role)where user can login with username and password ... A: Please see my blog article on Login Security Using VBA (blog address in sig). It does exactly what ... | |
| decimal point adjustment in calc | 1/17/2012 |
| Q: i have a calculation on a report using data from my database, it's to do with a role i have at work ... A: The number of decimal places that are displayed can be adjusted in one of two ways. You can use the ... | |
| User friendy data analysis on different levels [Sector, Customer, etc] | 1/17/2012 |
| Q: I am currently doing internship in a supply chain department. I ask to provide a tool to measure ... A: "Of course I can use the field "criteria" to do so but then I have to type in the name of each ... | |
| Using DMax to auto-increment | 1/15/2012 |
| Q: Here's what my code looks like: Me.DerivativeID = Nz(DMax("[DerivativeID]", "tbl_FRM107", ... A: I'm at a loss here. I just don't see anything wrong with that line of code. The only thing I can ... | |
| Synchronizing Combo Boxes Based on a Many-to-Many Relationship | 1/14/2012 |
| Q: I am attempting to create 2 synchronized combo boxes where the values for each combo box would be ... A: Ok, first, your TBL_CS_Join is setup wrong. Names are not unique so it should use the IDs: ... | |
| Dates | 1/14/2012 |
| Q: I am working on Access 2007. I have a table that captures daily sickness record. I want to create a ... A: Look up looping in VBA help. You will find sample code to loop through a recordset. Within the loop ... | |
| Using DMax to auto-increment | 1/14/2012 |
| Q: Here's what my code looks like: Me.DerivativeID = Nz(DMax("[DerivativeID]", "tbl_FRM107", ... A: Is ProjectID a text or numeric field? If its text, then your code should be: Me.DerivativeID = ... | |
| Sorting / hiding columns | 1/14/2012 |
| Q: I hope i can pose my question in a way you will understand. I have a form that gathers numerical ... A: Sounds like you have not designed your database properly. A survey application has a specific ... | |
| Synchronizing Combo Boxes Based on a Many-to-Many Relationship | 1/13/2012 |
| Q: I am attempting to create 2 synchronized combo boxes where the values for each combo box would be ... A: Need more details. Can you give the table structure of the tables, how they are related and some ... | |
| Using DMax to auto-increment | 1/13/2012 |
| Q: Here's what my code looks like: Me.DerivativeID = Nz(DMax("[DerivativeID]", "tbl_FRM107", ... A: Is ProjectID a field in tbl_FRM107? Is there a control on your form named ProjectID bound to this ... | |
| Access 97 - entries to drop off via query after selected days | 1/12/2012 |
| Q: I'd be lying if i said i was an expert at creating Access databases (atleast in terms of writing the ... A: To add a calculated column go to the first empty column and enter the expression in the top row of ... | |
| User friendy data analysis on different levels [Sector, Customer, etc] | 1/12/2012 |
| Q: I am currently doing internship in a supply chain department. I ask to provide a tool to measure ... A: If you want to show the result of different criteria at the same time, you will need to generate a ... | |
| sending group email from ms access | 1/12/2012 |
| Q: I am very new to ms access and i have got a table column with all the email address. i want to send ... A: Look at the SendObject command in VBA Help. This will allow you to send an e-mail from Access. ... | |
| query append | 1/11/2012 |
| Q: Happy new year! Thanks for all helping in pass years! I have a database with two tables: > first ... A: I'm still not following why you need separate tables. Is the issue that you need to cross reference ... | |
| query append | 1/11/2012 |
| Q: Happy new year! Thanks for all helping in pass years! I have a database with two tables: > first ... A: If ItemID is the foreign key in tblSubItems, then just a join on that field will show only records ... | |
| still trying to get a percentage of classes completed | 1/11/2012 |
| Q: I asked a question a while back and I still haven't figured out how to solve my issue. I am trying ... A: Try this: "FROM [Course list];" if you have spaces in object names, you have to use brackets to ... | |
| User friendy data analysis on different levels [Sector, Customer, etc] | 1/11/2012 |
| Q: I am currently doing internship in a supply chain department. I ask to provide a tool to measure ... A: First, your database does not appear to be normalized properly. If you have fields like Jan2012, ... | |
| Access 97 - entries to drop off via query after selected days | 1/10/2012 |
| Q: I'd be lying if i said i was an expert at creating Access databases (atleast in terms of writing the ... A: OK, First "Date" and "Time" are reserved words in Access and shouldn't be used for Object names. ... | |
| Multiple option box query creation? | 1/10/2012 |
| Q: I have a database where there are many (100+) records per person, multiple people (10-20) per ... A: I'm not clear on your structure, which is key to being able to help you. You should have at least ... | |
| Automatic Incremental Number | 1/9/2012 |
| Q: I read a post of yours about incremental numbering. The issue was to automatically generate a ID ... A: That must have been an old post. Since then I've written a blog on Sequential Numbering which ... | |
| still trying to get a percentage of classes completed | 1/8/2012 |
| Q: I asked a question a while back and I still haven't figured out how to solve my issue. I am trying ... A: Try placing a Debug.Print strSQL line prior to CurrentDB.Execute line. This will print the value ... | |
| Dates | 1/8/2012 |
| Q: I am working on Access 2007. I have a table that captures daily sickness record. I want to create a ... A: You would need to loop through your recordset. You test each record to see if its the same employee ... | |
| Report for store check | 1/7/2012 |
| Q: I have two tables Store check table with house bill numbers and items/products(T1)and A shipping ... A: Again, I would need to know the structure of the tables to help further. But you need to have a date ... | |
| UserForm 2.0 Interface | 1/6/2012 |
| Q: Once a UserForm has been loaded, shown and becomes the active window; from there a table object is ... A: Not sure what you mean by UserForm 2.0? If you are asking about Microsoft Access, why would you ... | |
| Report for store check | 1/5/2012 |
| Q: I have two tables Store check table with house bill numbers and items/products(T1)and A shipping ... A: What is the structure of the data you get? Does it indicate when an item was received by the store? ... | |
| Changing multiple records in a table | 1/5/2012 |
| Q: I have a table which includes thousands of names with contact info and the company that they are ... A: Sure, its called an UPDATE query. Open Query Design mode and add your table. Then add the column(s) ... | |
| query and report on data from a subform | 1/4/2012 |
| Q: I am very new to Access and am working from a database that was built from a previous employee. I ... A: Yes you have it correctly, except you are using brackets when you should use parentheses: ... | |
| Print preview in a vba loop | 1/3/2012 |
| Q: Using Access 2003,I've created two reports in the form of a headed letter. The first shows client ... A: I would have two different print buttons. One to print all of the clients and one to print a ... | |
| MS Access | 1/2/2012 |
| Q: I am a total MS Access novice. I have a textbox that i remains hidden unless an associated checkbox ... A: Then base your report on a query instead of the table. In the query join the lookup table to your ... | |
| still trying to get a percentage of classes completed | 1/1/2012 |
| Q: I asked a question a while back and I still haven't figured out how to solve my issue. I am trying ... A: You need at least three tables here. Your employee table, a course table which is just a list of ... | |
| Date calculations and formatting | 12/29/2011 |
| Q: Here I am back to the fount of knowledge; hope you can help me again. I have established a query ... A: Yes, use a custom function and use Test the day to determine which increment to use: For example: ... | |
| MS Access / Serial Number | 12/29/2011 |
| Q: I created a main form as demand register and a sub-form as actions on the specific demand linked as ... A: Why not use an Autonumber datatype? But if you need a specific formatting for the serial number. ... | |
| MS Access | 12/29/2011 |
| Q: I am a total MS Access novice. I have a textbox that i remains hidden unless an associated checkbox ... A: For future reference, if you have a new question its better to start a new question rather than use ... | |
| create a service log | 12/29/2011 |
| Q: My existing database holds student records with tabs and subforms to track academic courses, testing ... A: Add the student's ID as a Foreign key in the Service table. The table should look like this: ... | |
| Date calculations and formatting | 12/28/2011 |
| Q: Here I am back to the fount of knowledge; hope you can help me again. I have established a query ... A: You can't do it in a single expression. So you either have to use 3 columns or a custom function: ... | |
| Overflow | 12/27/2011 |
| Q: Merry Christmas.... I work on access 2007 db , I created a query to find the rank of students by ... A: That sounds wierd. Try creating a new query using the working one as the source. Apply your criteria ... | |
| word & Numbers database | 12/27/2011 |
| Q: I hope u can assist me, its rather urgent I would like to build a form of access database for my 4yr ... A: Well first, there are several educational toys from people like Fisher Price and Leapfrog, that do a ... | |
| writing a if statement within a query | 12/26/2011 |
| Q: For a report, we have the following code : =Format([Diagnosis21-5],"#.##") & ... A: First, a value can be Null, but a portion of a value can't be null. If one of your fields might be ... | |
| inserting a checkbox in MS-Access | 12/26/2011 |
| Q: While working with Microsoft Access 2000, on Windows Xp,I am unable to insert a checkbox for a ... A: A checkbox is usually associated with a Yes/No datatype. Such a datatype holds either 0 (No) or a -1 ... | |
| Updating a checkbox from a text box. | 12/25/2011 |
| Q: Mr. Scottgem, I have an unbound form. This form has a sub-form bound to a table. Among the other ... A: If TextScan is on the main form, this will not work. Me.RecordsetClone should produce an error since ... | |
| Transitioning a Database to the Web | 12/22/2011 |
| Q: I have an MS Access 2007 database that instructors currently use to track student ... A: Yes, the advantage is in security. Enterprise level databases have a more robust security model than ... | |
| Trying to use a different database | 12/22/2011 |
| Q: I am using Access 2007 and the database is pulling data from "dbo_ext_007_cnd". Could you please ... A: What isn't clear here is whether dbo_ext_007_11 is a table or database. It appears to be a table but ... | |
| Re: reports (continued) | 12/22/2011 |
| Q: It worked very well. How do you split to show only one grouping per page even though there is enough ... A: In your previous question I suggested using Grouping. My last response to the previous question was: ... | |
| Re: reports (continued) | 12/22/2011 |
| Q: It worked very well. How do you split to show only one grouping per page even though there is enough ... A: You have to follow my instructions. The report footer ONLY appears at the end of each report. To ... | |
| Adjusting text boxes automatically | 12/22/2011 |
| Q: in order to save myself a lot of work, I'd like to simplify the input into my dB. I have a form ... A: Ok, I understand better now. There is no way you can do this with one table. You can't automate ... | |
| Adjusting text boxes automatically | 12/22/2011 |
| Q: in order to save myself a lot of work, I'd like to simplify the input into my dB. I have a form ... A: If I understand you correctly, you have a Projects table which contains the identification info ... | |
| Re: reports (continued) | 12/21/2011 |
| Q: It worked very well. How do you split to show only one grouping per page even though there is enough ... A: There is a Force New Page property for a footer. No there is no way of doing a table of contents. ... | |
| Reports | 12/21/2011 |
| Q: I have to create a report in access with the following parameters. The table has 3 fields, ... A: Then you definitely need to use grouping You add a Group Footer and set it to start a new page after ... | |
| Using Access for purchase requisitions & tracking contracts | 12/21/2011 |
| Q: I googled and this came up with your entry/comments that you had built a db using Access for ... A: Generally, and especially for Access, a back end is considered just the tables or data. The front ... | |
| Using Access for purchase requisitions & tracking contracts | 12/21/2011 |
| Q: I googled and this came up with your entry/comments that you had built a db using Access for ... A: If Clientele uses an Access back end, then you have your table structure. You can either copy the ... | |
| Reports | 12/21/2011 |
| Q: I have to create a report in access with the following parameters. The table has 3 fields, ... A: Its called the Hide Duplicates property on the Format tab. You have to select the control first. ... | |
| Autofill date text box | 12/21/2011 |
| Q: I have a combo box 're-occurence' and a text box labelled 'Due Date'. I would like to have a VBA ... A: The Bound column tells you what value is stored in the combobox. But without knowing the RowSource, ... | |
| Autofill date text box | 12/21/2011 |
| Q: I have a combo box 're-occurence' and a text box labelled 'Due Date'. I would like to have a VBA ... A: This should be a simple IF, however, you need to check what is actually stored in the combo. What is ... | |
| Reset total pages in group numbering | 12/20/2011 |
| Q: I've found code and instructions to reset the total pages in a group when using the "page # of ... A: Its probably not going to be a simple solution. Have you tried the techniques in this article: ... | |
| Exchange rates | 12/20/2011 |
| Q: I would like to know if there is a script/Macro that can be used in Access to: get daily exchange ... A: I'm sure there is but the only thign I found was this: ... | |
| Using Access for purchase requisitions & tracking contracts | 12/20/2011 |
| Q: I googled and this came up with your entry/comments that you had built a db using Access for ... A: I think you are referring to an app I created at a former employer, which I no longer have access ... | |
| Look up Bar Code | 12/20/2011 |
| Q: I want to check if a bar code is already in a look up table and if it is not then to return the ... A: You are close. The Not IsNull() is the correct function. The idea is you want to lookup a record and ... | |
| Property Disabled and Conditional Formatting | 12/20/2011 |
| Q: Sir, I am working on Access 2007. Some option are disabled on my form and these are enable by Edit ... A: Try locking the controls rather than use the enabled property. Conditional formatting is negated by ... | |
| Reset total pages in group numbering | 12/20/2011 |
| Q: I've found code and instructions to reset the total pages in a group when using the "page # of ... A: The code should be the same for Access 2007. VBA didn't change. What code are you using? Hope this ... | |
| access - | 12/19/2011 |
| Q: I am trying to write a field from one table to another. In the first table I am using a lookup to a ... A: First, I do NOT recommend using lookup fields on the table level. This causes more problems than its ... | |
| Reports | 12/19/2011 |
| Q: I have to create a report in access with the following parameters. The table has 3 fields, ... A: There are two ways to do this. The way I would do it is with a Group header. Put the Committee Name ... | |
| Missing data in Query | 12/16/2011 |
| Q: I have two tables I am joining Finding and Finding Follow-up. These tables have with medical centers ... A: It sounds like you need an OUTER join rather than an INNER one. So it looks like there are 38 ... | |
| Assign date/time value to a table via parameter query | 12/16/2011 |
| Q: Could you please advise how the VBA/SQL code should look like to perform the following (by clicking ... A: 1. What type of query? If it is a SELECT query there is no need to explicitly run it. 2. Use the ... | |
| Pass values from a form to a query | 12/15/2011 |
| Q: I have an uneditable form (source is Totals query). However, the form needs to get a date stamp and ... A: If the form is bound to an uneditable query, it can still have unbound controls on it that are ... | |
| query and report on data from a subform | 12/15/2011 |
| Q: I am very new to Access and am working from a database that was built from a previous employee. I ... A: When you add a calculated column to a query you have to give it an Alias. So you enter the ... | |
| Input Mask Wizard - can I make a selection to add to the predefined masks? | 12/15/2011 |
| Q: When I use the Input Mask Wizard, there is a short list of predefined masks that I can select from. ... A: Frankly this was a new one on me. But the reason you can't use all of them is because they are ... | |
| Open report based on 2 form's values | 12/14/2011 |
| Q: I have a command button on a form that opens a report: DoCmd.OpenReport stDocName, acViewPreview, , ... A: DoCmd.OpenReport stDocName, acViewPreview, , "[JN] = " & Forms!frmJobs![JN] & " AND [WO] = #" & ... | |
| Open report based on 2 form's values | 12/14/2011 |
| Q: I have a command button on a form that opens a report: DoCmd.OpenReport stDocName, acViewPreview, , ... A: DoCmd.OpenReport stDocName, acViewPreview, , "[JN] = " & Forms!frmJobs![JN] & " AND [WO] = " & ... | |
| deleting records from subform in Access 2007 | 12/14/2011 |
| Q: I have a main form/subform combination in my database. My subform is in Datasheet view. I want the ... A: You should have no problem deleting the record in the subform unless the subform is bound to a ... | |
| populate textbox on form with email addresses from query | 12/13/2011 |
| Q: I have a query (ParentsEmail) that holds parents of our students who prefer emails of announcements, ... A: You are close. Set the TO value in SendObject to a school e-mail address. Essentially send it to ... | |
| deleting records from subform in Access 2007 | 12/13/2011 |
| Q: I have a main form/subform combination in my database. My subform is in Datasheet view. I want the ... A: Are you sure you want to do that? Why would you delete BOTH the junction record AND the related ... | |
| Field Validation without using a LookUp Column | 12/13/2011 |
| Q: I have a field that I want to have limited valid entries for, 48 different valid entries to be ... A: I was afraid of that. One of the principles of a relational database is to eliminate redundant data. ... | |
| Field Validation without using a LookUp Column | 12/13/2011 |
| Q: I have a field that I want to have limited valid entries for, 48 different valid entries to be ... A: There are several possible ways to handle this. But I do have one question first. If I'm following ... | |
| Access 2007 Sequence Alphanumeric ID Windows & | 12/12/2011 |
| Q: Scottgem, I'm looking for something similar to some of your already answered questions on ... A: The #Name error means that Access can't resolve the names used in the expression. The code sample is ... | |
| How to access and use subform data/calculations | 12/12/2011 |
| Q: I have a Work Request database that includes two subforms (tabbed) to keep track of working progress ... A: Try it this way: Me.DesignTime.FORM.DesignTotalTime The FORM indicates that DesignTime is a form ... | |
| How to access and use subform data/calculations | 12/12/2011 |
| Q: I have a Work Request database that includes two subforms (tabbed) to keep track of working progress ... A: There are two ways. You can add a footer to the subforms and put in a text box with the expression: ... | |
| Access 2007 Sequence Alphanumeric ID Windows & | 12/12/2011 |
| Q: Scottgem, I'm looking for something similar to some of your already answered questions on ... A: The only problem I see is making AlphaSeq a text field. By making it a text field if you are storing ... | |
| Event order problems on deletes | 12/12/2011 |
| Q: Using Access 2003, I have a main form with two subforms. The subforms display in datasheet view and ... A: Try the Lost Focus or On Exit event of the subform. It should trigger whenever you move from the ... | |
| deleting records from subform in Access 2007 | 12/10/2011 |
| Q: I have a main form/subform combination in my database. My subform is in Datasheet view. I want the ... A: A form doesn't hold records it just displays them. First, I wouldn't use Datasheet mode. I would use ... | |
| Access 2010 Import code | 12/9/2011 |
| Q: I have my export codes working great, now, thanks to your help, but I'm experiencing a problem with ... A: A couple of problems that might exist. When you use a fully qualified parameter list (i.e ... | |
| counting zeros | 12/9/2011 |
| Q: I cannot seem to find a way to count up all the 0's in a particular field of a query. I have ... A: You can use a DCount to count all records that meet a specific criteria. You can use a Group By ... | |
| Access 2007 Sequence Alphanumeric ID Windows & | 12/8/2011 |
| Q: Scottgem, I'm looking for something similar to some of your already answered questions on ... A: Have you read my blog on Sequential Numbering? You can build on that. What you are going to need to ... | |
| Access 2003 Autonumber input | 12/8/2011 |
| Q: I've seen you answer this question elsewhere, but when I try the formula that you answered with I ... A: Have you read my blog on Sequential Numbering? If not, please review the blog (see address below) as ... | |
| Eliminating duplicate records in a table | 12/8/2011 |
| Q: Could you please advise how to make a query that would only show unique records from a table ... A: 2 might be possible, if you can make sure its the previous record. You need some way of identifying ... | |
| Eliminating duplicate records in a table | 12/8/2011 |
| Q: Could you please advise how to make a query that would only show unique records from a table ... A: Can't be done. As soon as you use any method (SELECT DISTINCT, GROUP BY, etc.) to eliminate ... | |
| Access 2010 transfer spreadsheet import VBA | 12/8/2011 |
| Q: Hey scottgem, I got some code from a website ZIP file that imports text into an import table in my ... A: Are you importing a spreadsheet file? Is it always the same filename in the same folder? The code ... | |
| Editable equivalent of Union query | 12/7/2011 |
| Q: In my Access DB I have several tables and they all have the same column with the same or not the ... A: Why do you have the same value in different tables. You might want to look at your table design. If ... | |
| Access 2007 | 12/7/2011 |
| Q: How can I get the Header to repeat on every page when printed on a Report? I can see it displayed ... A: Don't print a form. Forms are not meant to be printed. Create a report to print the data. Hope this ... | |
| Input mask for General Date format | 12/7/2011 |
| Q: I have been attempting to formulate an input mask for a date time field in “General Date” format. I ... A: Try this: 99/99/99\ 99:99\ >LL;0; Paste that into the Input Mask box for the field in Table Design ... | |
| Working With Date/Time | 12/6/2011 |
| Q: Back again. Hope you can help with this one. I have a table, tblEvents, with four fields EventID ... A: This is a function I use to convert a time difference into days/hours/minutes/seconds Public ... | |
| Transitioning a Database to the Web | 12/6/2011 |
| Q: I have an MS Access 2007 database that instructors currently use to track student ... A: First, Access has a free runtime module. This means that anyone can download it and run Access ... | |
| Access 2007 Append Query Alias Field Name | 12/5/2011 |
| Q: I have an append query that has multiple fields that need to append to the same destination field. ... A: Then you need to design the new database properly. Fields in a table should be broken out into their ... | |
| Access 2007 Append Query Alias Field Name | 12/4/2011 |
| Q: I have an append query that has multiple fields that need to append to the same destination field. ... A: Why would you want several pieces of data to go into one field? Why would you be copying data from ... | |
| Access 2007 | 12/1/2011 |
| Q: How can I get the Header to repeat on every page when printed on a Report? I can see it displayed ... A: The Report header will print only on the first page. The Page Header will print on each page. Group ... | |
| Query, choose | 12/1/2011 |
| Q: I have a table with a field that is set to Yes/No and is a check box on the form. I have a query ... A: If your field name is Approval, then you can copy and paste the code I gave into the top row of an ... | |
| Query, choose "Yes" or "No" | 12/1/2011 |
| Q: I have a table with a field that is set to Yes/No and is a check box on the form. I have a query ... A: Try 0 (for No) and -1 (for Yes). The problem here is you are using a parameter prompt query. When ... | |
| Concatenate in Access tables | 12/1/2011 |
| Q: I'm currently working on an Access-db for archaeological research. This DB has to store information ... A: I'm going to assume you are an archaeologist and not a database designer. Because you are taking the ... | |
| Sequential Numbering | 11/30/2011 |
| Q: Scottgem, I want the barcode to go off the last generated # and the date at the end. First 4 digits ... A: You're not answering my question. Please review my previous response. Did you read my blog on ... | |
| Sequential Numbering | 11/30/2011 |
| Q: Scottgem, I'm building a new order database with Microsoft Access. I have a form where personnel ... A: Your whole question makes sense. The problem is to provide more help then I have already given I ... | |
| Sequential Numbering | 11/30/2011 |
| Q: Scottgem, I'm building a new order database with Microsoft Access. I have a form where personnel ... A: So your Barcode number will be made up of the Quantity Box (what's that?), ProductID and date, plus ... | |
| Access 2007 | 11/30/2011 |
| Q: How would I make the First Letter of a Table (eg. Last Name) convert to a capital letter if entered ... A: Yes, you can do this with an input mask: >L<?????????????? will convert the first letter to upper ... | |
| Access 2007 with Mail Merge | 11/29/2011 |
| Q: I'm generating a thank you letter to donors that uses an Access query as the data source. Is there ... A: OK, So use the same criteria in an Update query: UPDATE table SET fieldname = True WHERE ... | |
| Sequential Numbering | 11/29/2011 |
| Q: Scottgem, I'm building a new order database with Microsoft Access. I have a form where personnel ... A: First, you can check out my blog article on Sequential numbering. That will help you create the ... | |
| Access 2007 with Mail Merge | 11/28/2011 |
| Q: I'm generating a thank you letter to donors that uses an Access query as the data source. Is there ... A: Basically an update query. But you need to explain how you determine who to send to. Hope this ... | |
| Radio Button in Report | 11/28/2011 |
| Q: I have a report in Access where an option button is visible for each record. As of now, the option ... A: I'm sorry I forgot this was a report not a form. For a report you put the code in the On Format ... | |
| Radio Button in Report | 11/28/2011 |
| Q: I have a report in Access where an option button is visible for each record. As of now, the option ... A: There are no properties to change the actual radio button. What you CAN do is change the fore color ... | |
| Filter a form and update table based on filter | 11/28/2011 |
| Q: I have a form based on a single table. I would like to apply a filter and get a group of records ... A: Hmm, that should work. Try copy ad paste the generated SQL into SQL view of a query. That should ... | |
| Logo in report - change for page 2 | 11/28/2011 |
| Q: I did find a resolution to the report showing 2nd page blue (new property Alternate back color). Now ... A: Then put code in the On Format and On Print events for the Page header band to check to see if it is ... | |
| Logo in report - change for page 2 | 11/27/2011 |
| Q: I did find a resolution to the report showing 2nd page blue (new property Alternate back color). Now ... A: If you put a Logo in a REPORT header, it will only show up on the first page. You can then put the ... | |
| Queries, forms and subforms | 11/27/2011 |
| Q: Hope you can help! I am using Access 2003 and have a database with three tables with fields as ... A: You have done exactly everything correct up until thinking you need a query. You don't. You create a ... | |
| Using Access - Input Masks for particular Products | 11/27/2011 |
| Q: I am getting quite stressed about trying to enter a Field for Products that include items like ... A: This is too variable to use an Input Mask. I'm assuming this is for the Products table. I would also ... | |
| MS Access | 11/26/2011 |
| Q: I am a total MS Access novice. I have a textbox that i remains hidden unless an associated checkbox ... A: I suspect the checkbox is not bound to a field in a table. If you need to checkbox to remember is ... | |
| Rename Database Objects | 11/24/2011 |
| Q: I inherited a database that has the year in the names of objects (tables,queries, etc.) Is there a ... A: Unfortunately not. However, if you change the names, it should update automatically in queries and ... | |
| A form picks up updates done in the other form without been closed and re-opened | 11/24/2011 |
| Q: I have a form with a subform based on a union query. Users needs an option of updating this sub on ... A: It depends on what is being edited. Yes you can have a popup form with an editable recordsource. ... | |
| 2010 MS Access - reports | 11/22/2011 |
| Q: I have a word letter that I am trying to convert to a report and drop to a PDF for them to review ... A: Hmm, not sure why that would happen. I would suggest trying to create a new report. Create a blank ... | |
| counting fields | 11/22/2011 |
| Q: I am having trouble writing an expression in access that will return the percentage that this excel ... A: If I'm guessing right each of those fields represent a training course. If so, then your database is ... | |
| 2010 MS Access - reports | 11/22/2011 |
| Q: I have a word letter that I am trying to convert to a report and drop to a PDF for them to review ... A: Try it this way: DoCmd.OutputTo acOutputReport, sLetterNam, acFormatPDF, sOutFileNam, False, , , ... | |
| counting fields | 11/22/2011 |
| Q: I am having trouble writing an expression in access that will return the percentage that this excel ... A: Just list the fields. You can also use the Database Documenter to create a document and copy and ... | |
| Access tables on SQL Server | 11/21/2011 |
| Q: I created an access database(with several tables) at work and then I copied the mdb file to a CD so ... A: Either use the External Data function to import the tables or use Make Table queries to create the ... | |
| Showing only records that meet criteria | 11/21/2011 |
| Q: I am using a query to select employees based on their security entered by a login screen. If ... A: I need to see more about how your login system works. Also the SQL for this query. I do this ... | |
| search box in access2007 | 11/21/2011 |
| Q: how on earth do i create a search box in access 2007 without using code because i can not seem to ... A: Actually there is a very easy way which doesn't require writing code. If you use the Combobox wizard ... | |
| IIF , AND | 11/21/2011 |
| Q: how to make this condition in field 'discount': you can get the discount if you buy Bed, Desk, ... A: So if I understand this, then get a discount, when they buy a whole set. This needs to be entered ... | |
| Access 2010 query for records that don't have related content | 11/19/2011 |
| Q: I'm sure this is an easy answer, but I'm having considerable difficulty finding what I'm looking for ... A: Use the Query Wizard and create a unmatched query. First, create a query for just Orientations. Then ... | |
| Append Table and Password issue | 11/18/2011 |
| Q: I have a table with numerous fields of which one is an autonumber field called Contact ID (currently ... A: First check to make sure the user didn't download a local copy. Without seeing the code you are ... | |
| Append Table | 11/17/2011 |
| Q: I have a table with numerous fields of which one is an autonumber field called Contact ID (currently ... A: Assuming the current ID is a numeric value, it should append fine with an Append query. Using an ... | |
| DLOOKUP in a subform | 11/17/2011 |
| Q: i am new to access and figuring it out. I created a Form with 2 Text boxes , one is unbound while ... A: What error? I think I did miss something. If you are using this expression as the Controlsource of a ... | |
| Access Records Loss | 11/16/2011 |
| Q: I don't know why I couldn't ask another question on the thread we were posting on, but I had to make ... A: AllExperts has a limit on the number of followups. A feature I've disagreed with. If you want to ... | |
| Access Loss of records | 11/15/2011 |
| Q: I don't know if I am out of luck here, but I am going to ask the question anyway. I created an ... A: So the RecordSource IS a query. And the Excel sheets are imported, not linked. So the next thing to ... | |
| Access Loss of records | 11/15/2011 |
| Q: I don't know if I am out of luck here, but I am going to ask the question anyway. I created an ... A: I need a bit more exact info. Are you linking to the Excel table or importing the data into an ... | |
| Conditional Formatting | 11/15/2011 |
| Q: I have a database with 4 table that correspond to 4 different types of computer systems within my ... A: I would change your structure. I'm sure there is information common to each of the tables. For ... | |
| Access Loss of records | 11/14/2011 |
| Q: I don't know if I am out of luck here, but I am going to ask the question anyway. I created an ... A: What is the Recordsource of the form? What version of Access? Did you open the BE and make sure your ... | |
| DLOOKUP in a subform | 11/14/2011 |
| Q: i am new to access and figuring it out. I created a Form with 2 Text boxes , one is unbound while ... A: By making it a subform you changed the reference to the control workerID. Try changing the code to: ... | |
| Report to show data for each individual employee! | 11/11/2011 |
| Q: I have a query based on a table - this table has many fields, with each field representing a report. ... A: Not that I know of. You can't use a crosstab because of the structure. However, it would not be ... | |
| Followup | 11/11/2011 |
| Q: The previous question would not let me follow up. I think I have gotten this to the final question. ... A: When you click the ellipses [...] to the right of the property box it will open a dialog to choose ... | |
| Followup | 11/11/2011 |
| Q: The previous question would not let me follow up. I think I have gotten this to the final question. ... A: No. Me is a shortcut to indicate the current form. So its Me.controlname.Picture where controlname ... | |
| Followup | 11/11/2011 |
| Q: The previous question would not let me follow up. I think I have gotten this to the final question. ... A: First, you need to be in Form Design Mode. There are two ways do that. If you click in the gray ... | |
| Hyperlink in Microsoft Access 2002 | 11/11/2011 |
| Q: I am using Microsoft Access 2002. I have a directory of JPG Files whose names correspond to the ... A: Actually, it isn't that easy to tell. I've attached a screen shot of what the Image control looks ... | |
| Access 2007 with Mail Merge | 11/11/2011 |
| Q: I'm generating a thank you letter to donors that uses an Access query as the data source. Is there ... A: It depends on how you trigger the merge. If you trigger the merge through Access then yes. But if ... | |
| NO. OF CHARACTERS IN TEXT BOX | 11/10/2011 |
| Q: I know max number is 255. I have upgraded prev. database from 2003 t0 2007. Text box has 14 ... A: First the number of characters in a Text box is constrained by the ControlSource for that text box. ... | |
| Adding Date Created to record, after the fact | 11/10/2011 |
| Q: I'm a noob and probably a doofus, too. I created my Access database and neglected to add date ... A: Sorry, no. Access does not timestamp a record unless you consciously set a field to record the ... | |
| Hyperlink in Microsoft Access 2002 | 11/10/2011 |
| Q: I am using Microsoft Access 2002. I have a directory of JPG Files whose names correspond to the ... A: On Current is a Form event, not a control event. In the screen shot you are looking at properties ... | |
| Hyperlink in Microsoft Access 2002 | 11/10/2011 |
| Q: I am using Microsoft Access 2002. I have a directory of JPG Files whose names correspond to the ... A: Add an UNBOUND image control to your form. In the On Current event use code like this: ... | |
| Access 2007 | 11/9/2011 |
| Q: When a record in Access 2007 has been modified. I need a text box to popup and allow the operator ... A: The question is whether you want the reason stored in the same table of another table. You use ... | |
| rolling 12 month avg | 11/8/2011 |
| Q: I am trying to create a query to throw out a twelve month average. The field i am trying to average ... A: I don't think you can do this in the same query. But if you add a Group By query using that query as ... | |
| rolling 12 month avg | 11/8/2011 |
| Q: I am trying to create a query to throw out a twelve month average. The field i am trying to average ... A: Can you show me the SQL you currently have or the structure of your tables. This is possible but I ... | |
| Access | 11/8/2011 |
| Q: I have question regarding Yes or no format. Instead of Yes or no, when I export my data to excel, I ... A: Set the format for the column in the query you want to export to General Number. That should export ... | |
| query and report on data from a subform | 11/7/2011 |
| Q: I am very new to Access and am working from a database that was built from a previous employee. I ... A: You DON't do a report from a form. A form is based on tables just like a report. What you need to do ... | |
| Creating inventory databass | 11/7/2011 |
| Q: I am new to microsoft access. I'm trying to create a simple inventory system in which i can create ... A: "I have setup the table..." I've quoted that portion because it indicates your database is not ... | |
| Data entry form setup in a survey database | 11/6/2011 |
| Q: You helped me a set up tables for a survey database to do facility inspections a few months ago; I ... A: Dim strSQL AS String strSQL = "INSERT INTO tbl_Results (Question_ID, ResponseID) " & _ ... | |
| Sort drop down in a query | 11/3/2011 |
| Q: I have a Subform (Datasheet view) which is based on a query. Query, in turn, is based on several ... A: What is the RowSource of the combo? Note: A combobox displays a list of choices when the dropdown ... | |
| Access 2010 Subforms not populating | 11/3/2011 |
| Q: I have a data entry form for a donation process, which runs like this: Mainform = frmMoneyEntry, ... A: Subforms need to be linked to the main form based on the key field. Did you check the links? Hope ... | |
| Data entry form setup in a survey database | 11/2/2011 |
| Q: You helped me a set up tables for a survey database to do facility inspections a few months ago; I ... A: Actually I wasn't envisioning a separate form for each question, but just one per form. The way I ... | |
| Data entry form setup in a survey database | 11/2/2011 |
| Q: You helped me a set up tables for a survey database to do facility inspections a few months ago; I ... A: The first thing is to create the form to respond to the questionnaire. But you need to refresh my ... | |
| MS Access for Rosters | 11/1/2011 |
| Q: I am a fireman and I am trying to create an Access program I once saw in which I can create an ... A: I have an Access event calendar that can do partially what you want. You create an event for each ... | |
| Counting the number of times a certain conditions exsists. | 11/1/2011 |
| Q: I have a Querry and need to count the number of times a situation exsists in each column. Example: ... A: You can use Group by queries to group by a column and do a count of the record ID. You can also use ... | |
| Displaying search results of queries with more than 1 word | 10/31/2011 |
| Q: I have a query that my users will be searching through using key words. I want the user to be able ... A: OK, First you have to parse out the words into an array You can do this with InStr() function. The ... | |
| Displaying search results of queries with more than 1 word | 10/31/2011 |
| Q: I have a query that my users will be searching through using key words. I want the user to be able ... A: Are you searching in a specific field? Also, will there also be spaces separating the words or can a ... | |
| BOM database in MS Access not updating price changes | 10/31/2011 |
| Q: we have a Bill of materials database in MS Access 2010 that we have a main table containing all of ... A: It would help to know the structure of your tables. But I suspect what is happening is when you add ... | |
| MS Access 2010 | 10/29/2011 |
| Q: I have created a table for customers which includes a price code. I have an inventory table with ... A: Rate should be stored with the contract then, not the customer. You should not have fields like ... | |
| MS 2007 | 10/29/2011 |
| Q: how i can switch board open in start up and also be minimized and maximized A: Setting a form to open at startup is done in Access options after pressing the Office button (upper ... | |
| MS Access 2010 | 10/29/2011 |
| Q: I have created a table for customers which includes a price code. I have an inventory table with ... A: So each item you sell has different prices for different customers? And when you want to create an ... | |
| Linking Forms | 10/28/2011 |
| Q: I have a question. I have a form with a subform embedded. This is the maincustomer form. On this ... A: The quick answer is to use a Where clause in the OpenForm method to filter for the current customer. ... | |
| MS Access 2007 | 10/24/2011 |
| Q: Here's something that's driving me crazy. I have a memo field in an Access table, whose default ... A: I don't have 2007 installed anymore. But I just tried this with 2010. I had no problem changing font ... | |
| How to run Module | 10/24/2011 |
| Q: Sir, I made a little module in access 2007 and i am running through macro to it but when it is run ... A: The problem is that you are not identifying where what you are checking is from. If this module is ... | |
| Updating several fields into one field | 10/24/2011 |
| Q: Hope i can make my question clear. I have table 1 with two fields: "PRN" and "Remarks" and another ... A: Sure, but Update is not what you want. If I understand you, you have a one to many relationship ... | |
| Conditional Page Break in a Report | 10/20/2011 |
| Q: How do I "look up" a value in the next record from any given record within a report when (ID +1) ... A: This is the part that bothers me: "At first, all seemed OK if I grouped by the study KeyID (not the ... | |
| Creating new db merging tables/queries/forms/etc. from two existing db. | 10/20/2011 |
| Q: We are using the MS Access "Services" templates currently. We like the layout and ease of use, but ... A: What did you do to merge? I just checked, From the Templates screen in Access 2010 I used the ... | |
| Extracting unique data | 10/20/2011 |
| Q: i have 2 tables containing surnames initials 3 address fields and a postcode(zip code) one table ... A: This is not going to be easy. Since names and address may not be unique or entered the same way. For ... | |
| Refreshing 'Time' field on a form | 10/19/2011 |
| Q: Is there a way to set an interval for refreshing/updating the time field on a form that is open in ... A: Yes Using the Form's Timer event. This article describes how: ... | |
| query update | 10/19/2011 |
| Q: I have 2 MS Access database (one and two). the structure are same. I want combine them in one. the ... A: NOW it makes sense. So ID 1 in database one has the first 50 questions answered and ID 1 in Database ... | |
| query update | 10/19/2011 |
| Q: I have 2 MS Access database (one and two). the structure are same. I want combine them in one. the ... A: This really doesn't help much. Do you actually have fields named v1, v2, etc? What are the values? ... | |
| Facility Inspections | 10/17/2011 |
| Q: .. I am trying to create a very similar database as this user; ... A: Ok, So you start off with your tables for Facility and Inspectors. That's good. I'm assuming Items ... | |
| Facility Inspections | 10/17/2011 |
| Q: .. I am trying to create a very similar database as this user; ... A: Unfortunately, there is no way to directly contact a member who asks a question here. I can help ... | |
| Multiple Data Input on the Main Menu/Switchboard of a Database | 10/17/2011 |
| Q: Please, how do i input or upload multiple data(like 100) on the Main Menu/Switchboard of a Database ... A: The error means there were duplicate keys in the imported data. You have to check your key fields. ... | |
| General access db structure / design | 10/17/2011 |
| Q: Hey Scott, You've given me some valuable help in the past for this one and only access project of ... A: What I needed to see what a listing of each field and its datatype. Maybe with a description of what ... | |
| Date Increment | 10/16/2011 |
| Q: I am lacking in experience so my question may not be a dumb one. Where would I enter this into ... A: I have a blog entry on Sequential Numbering that will help you with this. Please review it and if ... | |
| Access to refresh/update open form at other workstation | 10/15/2011 |
| Q: I use to use your qualified answers before, so i am going again to ask you. I have simple DB in ... A: If a form is open on a different PC and data is changed in the back end through another form on ... | |
| General access db structure / design | 10/14/2011 |
| Q: Hey Scott, You've given me some valuable help in the past for this one and only access project of ... A: I had to trim the extraneous info from the document to get the field list: Table: tblCallData ... | |
| Access to refresh/update open form at other workstation | 10/14/2011 |
| Q: I use to use your qualified answers before, so i am going again to ask you. I have simple DB in ... A: Forms don't hold data, they present views of data in tables and queries. If I understand you there ... | |
| General access db structure / design | 10/14/2011 |
| Q: Hey Scott, You've given me some valuable help in the past for this one and only access project of ... A: You can use the Database Documenter and create a listing of your table, that you can then paste into ... | |
| Microsoft Access has stopped working | 10/14/2011 |
| Q: I have an Access 2003 db that runs just fine in 2003. But my company has upgraded to Access 2010. ... A: The missing reference issue is typical. You probably don't need that reference so I would just ... | |
| Access 2010 Report not generating properly | 10/14/2011 |
| Q: You helped me out yesterday with a form/subform calculation, which was solved through a Dsum and ... A: For a report I would do this differently. I would create a query using tblfundRaisingChecks that ... | |
| Microsoft Access has stopped working | 10/13/2011 |
| Q: I have an Access 2003 db that runs just fine in 2003. But my company has upgraded to Access 2010. ... A: Is this happening on multiple machines? Is this a multi-user app? If so, is it split? Has Office ... | |
| Declare a Function to do set a value of a field | 10/13/2011 |
| Q: You wrote: "That's the only way to do it. To create an Excel file or even to create a report you ... A: Public Function TimeStamp() Forms!formname!controlname = Now() End Function use the Actual form ... | |
| Capture time the command button is clicked | 10/13/2011 |
| Q: Could you please let me know how I can capture time in a field on a form when a command button on ... A: That's the only way to do it. To create an Excel file or even to create a report you will still need ... | |
| MS Access 2003 Expression Help | 10/13/2011 |
| Q: I am writing an expression that is comparing 2 dates to identify the following information. If the ... A: IIf(DateDiff("d",[SeniorityDate],[SaleDate])=0,"NEW",IIf(DateDiff("d",[SeniorityDate],[SaleDate])> ... | |
| Access 2010 VBA Dim statement | 10/13/2011 |
| Q: I received your help earlier in the year with the following statement: Private Sub ... A: That should work. But another way to do this is with a DSum If Me.Type = "Check" AND ... | |
| MS Access 2003 Expression Help | 10/13/2011 |
| Q: I am writing an expression that is comparing 2 dates to identify the following information. If the ... A: You are close, try it this way: ... | |
| Capture time the command button is clicked | 10/13/2011 |
| Q: Could you please let me know how I can capture time in a field on a form when a command button on ... A: You set the control's value to Now(). I don't use macros, so not sure how to set a controls's value ... | |
| Multiple parameters and nulls? | 10/13/2011 |
| Q: My 2007 database tracks projects and the contracts on which they are obligated. Each contract has a ... A: Don't use a Parameter prompt. Create a form with text box to enter the fiscal year. In your query, ... | |
| opening a specific report from a form command | 10/12/2011 |
| Q: I am using MS Access 2003. I have a single form into which I input Employee information. I have a ... A: First, I do not recommend using a text field as a primary key. It slows performance in searches and ... | |
| opening a specific report from a form command | 10/12/2011 |
| Q: I am using MS Access 2003. I have a single form into which I input Employee information. I have a ... A: Yep, a few things. 1) What is the primary key field for tblEmployee 2) what is the datatype of THAT ... | |
| Multiple Data Input on the Main Menu/Switchboard of a Database | 10/12/2011 |
| Q: Please, how do i input or upload multiple data(like 100) on the Main Menu/Switchboard of a Database ... A: Without knowing the nature of the data files, how they are generated, how they relate to tables in ... | |
| Conditional formatting | 10/12/2011 |
| Q: I have a email field in a sub query (lists customers software that is expiring in 45 days or less) ... A: Did you name the checkbox txtEmail_Notify? If you have an object name with spaces, put brackets ... | |
| Computation Fields | 10/11/2011 |
| Q: I have a situation where there are four fields, the first field is a static input field. The 2nd ... A: First, Field 4 should not exist in your table. As a general rule we don't store calculated values. ... | |
| automate route work | 10/7/2011 |
| Q: I am trying to automate some of my routine work in MS Access. My work includes 1. Import a txt file ... A: I believe so. It depends on the specific of what you are doing. You can string a series of macros ... | |
| Conditional formatting | 10/7/2011 |
| Q: I have a email field in a sub query (lists customers software that is expiring in 45 days or less) ... A: You can easily turn the color of the control in the code where send the e-mail: ... | |
| Access 2003 print settings | 10/7/2011 |
| Q: I have developed an MS Access 2003 database with a variety of reports (portrait and landscape). None ... A: I suspect the reason is different default printers. Most printers (especially lasers) cannot print ... | |
| Conditional formatting | 10/6/2011 |
| Q: I have a email field in a sub query (lists customers software that is expiring in 45 days or less) ... A: You can't use conditional formatting on a query. You would have to use the query as the source of a ... | |
| Conditional formatting | 10/6/2011 |
| Q: I have a Access database (2007) that lists cases done for customers ("Case List" form). These cases ... A: Set the condition to an expression like: DCount("*","Cases","[Customer] = " & [Customer] & " AND ... | |
| Print Single Access Report Record from Form | 10/3/2011 |
| Q: I have spent 8 hours working on this and the closest I get is an error message saying "invalid ... A: The correct code would be: strCriteria = "[INDVID] = " & Me.[INDVID] This assumes that INDVID is ... | |
| Scanning document | 10/3/2011 |
| Q: I have created a database using Ms Access 2007, I input my records and at the end, would like to ... A: There are some ActiveX Controls that you can use: ... | |
| update query | 10/3/2011 |
| Q: I am trying to convert this line to a update query: SELECT Purchasing.PurchaseNo, ... A: If you are using linked tables, there is no need to use ADODB. The tables are already linked and ... | |
| update query | 10/3/2011 |
| Q: I am trying to convert this line to a update query: SELECT Purchasing.PurchaseNo, ... A: Ok, your problem is that you are mixing methods. You can't SET an Action query to a variable. Just ... | |
| update query | 10/3/2011 |
| Q: I am trying to convert this line to a update query: SELECT Purchasing.PurchaseNo, ... A: It looks like you are trying to generate an Update state in Code. Can you show me the whole code ... | |
| Replacing an blank date with the current date | 10/3/2011 |
| Q: I was using your code from a previous questioner with regards to calculating age: Public Function ... A: Use the Nz function: Nz([PromotionDate3],Date()) However, if you have three fields in a table with ... | |
| Access 2010 | 10/2/2011 |
| Q: I Hope you are well. I need your help if you can. I need to create a database for an investment ... A: Your first step is to create a query that returns records 3 days prior to due date. You use the ... | |
| Access 2007 import | 10/1/2011 |
| Q: I have imported 3 tables from another database and all three tables share an ID field. So there is ... A: It depends on the datatype of the ID field. If it is an autonumber, then you are going to need to ... | |
| Access 2007 Me.Dirty not working | 9/30/2011 |
| Q: I have a form and a subform, which updates when you enter a participant ID number in the main form ... A: Use error trapping. Put an ON Error GOTO at the start of the code snippet. Then test for that error ... | |
| Multiple record input from one from | 9/30/2011 |
| Q: I have created an access 2007 database at work. I am tasked with coming up with a solution for the ... A: Well first, why do you have three separate tables? I would combine these into ONE table, with a ... | |
| Access 2007 Me.Dirty not working | 9/30/2011 |
| Q: I have a form and a subform, which updates when you enter a participant ID number in the main form ... A: You have referential integrity set on the tables. So when you enter a wrong ID it tries to create a ... | |
| Transactions in Inventory Tracking db | 9/30/2011 |
| Q: I have set up my warehouse parts inventory tracking db with a parts, parts in/out and transaction ... A: PK is Primary key. Yes, your structure makes sense as long as you have fields to identify the part ... | |
| Transactions in Inventory Tracking db | 9/29/2011 |
| Q: I have set up my warehouse parts inventory tracking db with a parts, parts in/out and transaction ... A: You should have two fields in your Parts table. A part number and a location field. This use an ... | |
| Copy value from one field to another | 9/29/2011 |
| Q: I have a subform (Datasheet view) in a form. For one of the fields in this subform I need to add an ... A: You misunderstood or maybe it was me who wasn't clear. Hold down the Ctrl key and press the double ... | |
| Design Form | 9/29/2011 |
| Q: I have a question concerning the best approach to a project. I have a form that has about three ... A: The first step in designing a database is to design your table structure. Access is a relational ... | |
| Vba code | 9/28/2011 |
| Q: I have 2 fields - Application 1 and Application 2 (as sometimes a JobNo would have 2 application ... A: So the RowSource should be: SELECT tblTCPO.ID, tblTCPO.TCPORefNoAppl1 FROM tblTCPO ORDER BY ... | |
| Vba code | 9/28/2011 |
| Q: I have 2 fields - Application 1 and Application 2 (as sometimes a JobNo would have 2 application ... A: Change the RowSource of the combo to: SELECT tblTCPO.PKfield, tblTCPO.TCPORefNoAppl1 FROM tblTCPO ... | |
| Multiple record input from one from | 9/27/2011 |
| Q: I have created an access 2007 database at work. I am tasked with coming up with a solution for the ... A: The structure of your database is important here. You CAN do it the way you are suggesting, but that ... | |
| Update Query not working | 9/26/2011 |
| Q: I have two tables, "Dancers" and "DancerYearlyInfo" which house information drawn from one temporary ... A: Ok, now that I understand the structure, the answer is easy. First, you create a Multiple field ... | |
| Update Query not working | 9/26/2011 |
| Q: I have two tables, "Dancers" and "DancerYearlyInfo" which house information drawn from one temporary ... A: Your problem is that you don't have keys defined. If I'm following you, you import the Excel sheet ... | |
| Filtering a query for next month | 9/26/2011 |
| Q: I am trying to use your dynamic date filter so that I can use a query to pull back all records with ... A: Where did you put this? Sounds like you put it as an expression in a column rather than as criteria. ... | |
| Dsum | 9/26/2011 |
| Q: I am constructing a payroll db everything is fine but i want to add a YTD field when printing ... A: First, you aren't adding a "field" for YTD. YTD is a calculation, so you want to add an expression ... | |
| Displaying Memo Fields Selected via Combo Box | 9/23/2011 |
| Q: I have an Access 2007 database that serves as a front-end to a SQL Server database. One of my forms ... A: Sorry to say, but your googling led you astray. This is actually very easy to handle. A Combobox has ... | |
| Combobox?? | 9/22/2011 |
| Q: Using Access 2007 on a Vista PC. Very new to making a database form ground zero. I have several ... A: What you are referring to is a standard technique referred to as Cascading or Synchronized ... | |
| Access 2007 | 9/21/2011 |
| Q: In my database, History = the table that contains the ODU SN and IDU SN columns and the current ... A: Yeah, I wasn't sure the dual linking would work. Use the control on the MAIN form where you select ... | |
| Access 2007 | 9/21/2011 |
| Q: All this is from before, I could not post a reply or my reply didnt go through sooo starting again. ... A: I would try two things. First I would try creating a second link. You probably have a link between ... | |
| Access 2007 | 9/20/2011 |
| Q: All this is from before, I could not post a reply or my reply didnt go through sooo starting again. ... A: I think I need to backtrack a bit. You have records that have two fields, one for the ODU SN one for ... | |
| Access 2007 date sort | 9/19/2011 |
| Q: My challenge involves date use. I have 2 applications, but the answer to the first will help with ... A: What it sounds like you need is a TOP n per Group query. To get the most recent training you would ... | |
| Access 2007 | 9/19/2011 |
| Q: Alright, this is my situation. I am creating a database that deals with microwave radios. This ... A: What is the parameter prompting for. I suspect its for PrimaryKey. If that the name of the PK field ... | |
| Access 2007 | 9/19/2011 |
| Q: Alright, this is my situation. I am creating a database that deals with microwave radios. This ... A: Ok, Change the RowSource of the combo to something like this: SELECT PrimaryKey, ODUSN AS SN FROM ... | |
| Auto insert | 9/15/2011 |
| Q: Sorry still not working, I get: Run-time error 2220 Microsoft Access can,t open the file ... A: Unfortunately no. A continuous form is actually one form that is repeated as many times as needed. ... | |
| question from an Access newbie | 9/14/2011 |
| Q: I have a query in Access 2003 that looks at a database that contains a column with several different ... A: This can be done very easily. In Design mode add the field you want to get counts on. Then set the ... | |
| Record is too large | 9/14/2011 |
| Q: I have this database that I have been using for over a year. I added a new column today. There are ... A: No, a cell is spreadsheet terminology. A spreadsheet is comprised of rows and columns a database is ... | |
| Calculating totals in an Access 2007 Form | 9/14/2011 |
| Q: I am creating a call quality database in MS Access 2007. I have the tables and forms all created ... A: As I said you have design problems. You should not have a field for the answer to each question. ... | |
| Access 2007 | 9/14/2011 |
| Q: Alright, this is my situation. I am creating a database that deals with microwave radios. This ... A: Do you have any code behind the combobox to do the search? If so, I need to see it to advise how to ... | |
| Calculating totals in an Access 2007 Form | 9/14/2011 |
| Q: I am creating a call quality database in MS Access 2007. I have the tables and forms all created ... A: Well first thing you seem to have some design problems with your database. Any time you have fields ... | |
| Record is too large | 9/14/2011 |
| Q: I have this database that I have been using for over a year. I added a new column today. There are ... A: Ok, lets get some terms straight. A table is an Access object that contains data. A table is has ... | |
| Facility Inspection Database | 9/13/2011 |
| Q: I'm trying to create a database in Access 2007 that a group of inspectors will use to perform ... A: So the "answer" is the score? This make it very easy. The questions table will have as many rows as ... | |
| Facility Inspection Database | 9/13/2011 |
| Q: I'm trying to create a database in Access 2007 that a group of inspectors will use to perform ... A: This is, essentially, a survey application. Such an application has a specific structure. something ... | |
| How do I make a table to accept formulas as input | 9/13/2011 |
| Q: I am trying to make a MS Access to make make a database to store, compute and analyze different ... A: Its not possible that I can see. What it sounds like you are trying to do is create an interface ... | |
| VBA codes does not work | 9/10/2011 |
| Q: In answer to you question, So what form do I need to look at? And, is that form the same in the ... A: OK, one more question, Brenda, before I can take a look at this. Do you have any code that ... | |
| VBA codes does not work | 9/9/2011 |
| Q: Did you get the link for the download of zip files you requested? ANSWER: yes, but I was in the ... A: I just looked at the zip file. Sorry to take so long. But it looks like you sent me the front end as ... | |
| Auto insert | 9/9/2011 |
| Q: Sorry still not working, I get: Run-time error 2220 Microsoft Access can,t open the file ... A: I just tried testing it and it doesn't look like its going to work. You will need to have a filed in ... | |
| Auto insert | 9/9/2011 |
| Q: Sorry still not working, I get: Run-time error 2220 Microsoft Access can,t open the file ... A: Try it this way: Dim strfile As String strfile = "C:\Program Files\Kinetic\BaseStation\JPGPhotos\" ... | |
| Auto insert | 9/9/2011 |
| Q: Sorry couldn,t find follow up button. The code below works fine individually in OnCurrent but when I ... A: Whoops, forgot the opening quotes Should be: strfile = "C:\Program ... | |
| Auto insert | 9/9/2011 |
| Q: Sorry couldn,t find follow up button. The code below works fine individually in OnCurrent but when I ... A: Yes and no. You can use a wild card in the DIR function to check if a similar file exists. So you ... | |
| In Memory Access Tables | 9/8/2011 |
| Q: Does putting an Access table in memory actually put it "in memory" in the same way that super fast ... A: If you specifically assign a table to a workspace, I do believe, at least the indexes are placed in ... | |
| Auto insert | 9/8/2011 |
| Q: Sorry couldn,t find follow up button. The code below works fine individually in OnCurrent but when I ... A: Sounds like you copied the Sub name as well. Make sure there is no other sub named Form_Current. ... | |
| Date Filters Access 2010 | 9/8/2011 |
| Q: This may be something very simple but I'm stuck. I set up a form that has a text box with a format ... A: DATE is a reserved word in Access and shouldn't be used as an Object name. The format of a date ... | |
| Sharing an Access DB through Splitting a DB | 9/7/2011 |
| Q: I have created a Access 2007 database that is put on a shared network folder. This folder is ... A: First, splitting is the best solution. However, anyone using the database needs full read/write ... | |
| Sending HTML emails through Outlook 2007, from Access 2007 | 9/7/2011 |
| Q: My organization wants to start sending html formatted emails from our Access 2007 database, using ... A: To send HTML formatted mail you can't use SendObject, you need to use Office Automation. This means ... | |
| Auto insert pictures | 9/6/2011 |
| Q: I Have an Access 2010 database of over 6000 aircraft records(Table 1),also 3000 photos of aircraft ... A: The image Control is on your Control Toolbar. The icon looks like a landscape with a mountain and ... | |
| Auto insert pictures | 9/6/2011 |
| Q: I Have an Access 2010 database of over 6000 aircraft records(Table 1),also 3000 photos of aircraft ... A: You don't need insert the photos at all. If the photos are all named according to the aircraft ... | |
| Expresion on Ms Access | 9/4/2011 |
| Q: can one help me on creating formula for the below figure in the query field of MS Access? ... A: You need to define this further. There is no such thing as a "query field". Are you referring to the ... | |
| Warning message if an employee is scheduled twice for same date | 9/4/2011 |
| Q: I have a form where user can add or edit schedule for a company. User can choose the date and ... A: Hmm, the DLookup should still return a null if there are no records for an employee. What you can do ... | |
| Warning message if an employee is scheduled twice for same date | 9/2/2011 |
| Q: I have a form where user can add or edit schedule for a company. User can choose the date and ... A: First, DATE is a reserved word in Access and shouldn't be used for object (field) names. You should ... | |
| Calculating pay for Stat holiday | 9/2/2011 |
| Q: I have database where I calculate how many hours each employee worked. I have to pay each employee ... A: You need a table that lists the stat holidays. From there you can use an expression like: ... | |
| generate Weekly total & YTD rows automatically | 9/1/2011 |
| Q: I have a extremely large Excel spreadsheet that I have liked to Access 2003 in hopes of finding ways ... A: You could automate the copy and paste of the formulas. But you would have to use Excel VBA for that, ... | |
| generate Weekly total & YTD rows automatically | 9/1/2011 |
| Q: I have a extremely large Excel spreadsheet that I have liked to Access 2003 in hopes of finding ways ... A: I'm sorry, but you really DO NOT need this. You are thinking along spreadsheet lines and not how a ... | |
| generate Weekly total & YTD rows automatically | 9/1/2011 |
| Q: I have a extremely large Excel spreadsheet that I have liked to Access 2003 in hopes of finding ways ... A: Are you doing this in Access or Excel? There should be no reason to do this in Access. You can ... | |
| EXPORTING QUERY TO EXCEL | 9/1/2011 |
| Q: I'm using command :DoCmd.OutputTo acOutputQuery, "Sheet1", acFormatXLS, strFile, False, , ENGLISH ... A: You have to use an alias when using an expression. So you need something like: SELECT ... | |
| MS Access, using an autonumber field to assign a value through a query. | 9/1/2011 |
| Q: I am using MS Access 2003 to create a meter reads DB. Basic logic is a customer can have multiple ... A: First, the error message indicates you are entering a query name in the On Change event. That won't ... | |
| Membership Database | 8/31/2011 |
| Q: Our Church parish currently uses Microsoft Office 2003. I am trying to create a database of ... A: http://office.microsoft.com/en-us/templates/membership-database-TC001018584.aspx Hope this helps, ... | |
| EXPORTING QUERY TO EXCEL | 8/31/2011 |
| Q: I'm using command :DoCmd.OutputTo acOutputQuery, "Sheet1", acFormatXLS, strFile, False, , ENGLISH ... A: What format are the columns in the query? Are there any records where the values may be text or ... | |
| Matching records in Access 2003 | 8/31/2011 |
| Q: I have 2 tables in Access 2003. Table 1 has Account numbers, order numbers and customer names. ... A: You can create a new query and add the first query along with Table 3 and join on the address field. ... | |
| Append data from excel work book to access table | 8/30/2011 |
| Q: I have an access table to which I want to append data from an excel sheet.The fields in the access ... A: Yep, could be very easy. I assume Store number is a unique identifier so there is only one record ... | |
| Help with microsoft access 2007 | 8/28/2011 |
| Q: Please help, I created a query based on two tables. A vegetable table and a Varieties table that ... A: First, you shouldn't be using Text field for key fields. Text fields perform slower in joins. You ... | |
| VBA codes does not work | 8/27/2011 |
| Q: Inserted the MsgBox statements you sent and they worked in the ACCDE file, after clicking ok to ... A: Ok, That's good, so we know the problem is with the code being used. At this point, ... | |
| VBA codes does not work | 8/26/2011 |
| Q: I've converted my database from accdb to accde and when I run the accde file none of the codes, ... A: I'm sorry, I thought you understood. You can't make changes to design elements in the ACCDE. That's ... | |
| VBA codes does not work | 8/26/2011 |
| Q: I've converted my database from accdb to accde and when I run the accde file none of the codes, ... A: Hmmm, that is odd. The only suggestion I can make is to temporarily put in some test messages. For ... | |
| VBA codes does not work | 8/26/2011 |
| Q: I've converted my database from accdb to accde and when I run the accde file none of the codes, ... A: Before converting to an ACCDE, you should make sure you have error trapping in place in most, if not ... | |
| Help with microsoft access 2007 | 8/26/2011 |
| Q: Please help, I created a query based on two tables. A vegetable table and a Varieties table that ... A: Well first, I think your database is not normalized properly. Having 11 Yes/No fields for the ... | |
| Main Menu in backgroung of New Password Login | 8/24/2011 |
| Q: The POP UP and MODAL are set to YES. How can I cause the Main Menu not to pop until after the New ... A: Did you try the change I suggested in my last response. Its a slight change, but should solve the ... | |
| Main Menu in backgroung of New Password Login | 8/24/2011 |
| Q: The POP UP and MODAL are set to YES. How can I cause the Main Menu not to pop until after the New ... A: Try it this way: Private Sub txtPassword_AfterUpdate() 'Check that EE is selected If ... | |
| Main Menu in backgroung of New Password Login | 8/24/2011 |
| Q: The POP UP and MODAL are set to YES. How can I cause the Main Menu not to pop until after the New ... A: Hmm, First you need to check where you are opening the main menu form . Search your code for an ... | |
| Run-time Error 3033 | 8/24/2011 |
| Q: Your questions to me: How did you set permissions and why? I converted the 2007 database from accdb ... A: This is only on the New Password form? Make sure the New Password form set to Modal and Popup. This ... | |
| Run-time Error 3033 | 8/23/2011 |
| Q: Your questions to me: How did you set permissions and why? I converted the 2007 database from accdb ... A: In the On Open event of your login form put: x = DisableRibbon() as the first line. Set the login ... | |
| CanShrink doesn't shrink labels! | 8/23/2011 |
| Q: I have managed to hide those fields = null, followed your steps but I still get the blank space ... A: I'm not Bob, but you are correct, the Can Shrink/Grow properties don't apply to labels. I'm not sure ... | |
| Calculate two dates on an access table 2007 | 8/22/2011 |
| Q: How I can calculate an expiration date in a table access? I want that my column called "3 months ... A: The answer is basically the same. Payment due is NOT stored, you can display it wherever you need to ... | |
| MS Access 2007 | 8/21/2011 |
| Q: I maintain an Access database that is populated by a dozen or so people using a form. This form is ... A: You're not going to like this answer. You have some serious design problems with your database. Your ... | |
| MS Access 2007 | 8/20/2011 |
| Q: I maintain an Access database that is populated by a dozen or so people using a form. This form is ... A: Copy and paste should continue to work. What problems did you encounter? But frankly, that is not ... | |
| Run-time Error 3033 | 8/20/2011 |
| Q: Your questions to me: How did you set permissions and why? I converted the 2007 database from accdb ... A: Frankly, I would convert it back to accdb and not use ULS. Most of the developers I know (and I know ... | |
| Setting Permission in Access 2003 | 8/19/2011 |
| Q: I’m getting the error message “Run-time error ‘3033’: "You do not have the necessary permission to ... A: How did you set permissions and why? When it was converted was it changed to an accdb file or left ... | |
| Future Income Projections | 8/19/2011 |
| Q: I have a set of two tables, tblVisitData and tblVisitType, where tblVisitData contains a listing of ... A: Sorry, thought I had responded. You need something to identify which week number in the sequence ... | |
| Setting Permission in Access 2003 | 8/19/2011 |
| Q: I’m getting the error message “Run-time error ‘3033’: "You do not have the necessary permission to ... A: Well first, Access 2007 does not support ULS unless you are using the older mdb format. Frankly, I ... | |
| Future Income Projections | 8/19/2011 |
| Q: I have a set of two tables, tblVisitData and tblVisitType, where tblVisitData contains a listing of ... A: That really doesn't answer my question. Is VisitType unique? So when you select a visit type can you ... | |
| is not null expression | 8/18/2011 |
| Q: It's not quite working because I get an Error 2950 which indicates that my macro to change the date ... A: I'm sure you are correct that the macro is interfering. I think the On Current code that I suggested ... | |
| Future Income Projections | 8/18/2011 |
| Q: I have a set of two tables, tblVisitData and tblVisitType, where tblVisitData contains a listing of ... A: Do you record the week number someplace? I gather tblVisitType has the cost for each visit in the ... | |
| Syntax error (missing operator) in query expression | 8/18/2011 |
| Q: I get the following error: Syntax error (missing operator) in query expression '[E-mail Address]=' ... A: Try : "[E-mail Address] = '" & Me.Approver & "'" as the criteria for the Dlookup. E-mail addresses ... | |
| stock balance of sales management system | 8/17/2011 |
| Q: i have a tblSales(sales date,item name,qnty, cost), tblStock, i want to run a query to display the ... A: Quantity on Hand (QOH) is a calculated value. It is calculated by adding incoming transactions and ... | |
| Calculate two dates on an access table 2007 | 8/16/2011 |
| Q: How I can calculate an expiration date in a table access? I want that my column called "3 months ... A: The key here is that this is a calculation and shouldn't be stored in a table. On your form you ... | |
| is not null expression | 8/16/2011 |
| Q: On a Single form, I have a textbox where the user can input a URL address after they click a ... A: In the On Current event of the single form, use code like: If NOT IsNull(Me.URL) Then ... | |
| Using Check Boxes Independently | 8/15/2011 |
| Q: Let Me start by saying I am self taught in Access so pleas use the simplest terms for me. I have ... A: Again, your database may not be designed properly. But I'm not sure what you mean "use them several ... | |
| Lookup Values in Access | 8/15/2011 |
| Q: I have now separted my city and zipcodes into separate fields. I need to lookup either the zipcode ... A: First, I suggest reading my blog on Displaying Data from Related tables (scottgem.Wordpress.com). ... | |
| acwzmain.mlbl_Entry (Label Wizard) | 8/15/2011 |
| Q: I see that you have fielded a couple of questions regarding the label wizard. If you time, I have ... A: Sorry I mistyped. The Runtime DOES NOT allow you into the design elements. There is no way Jeff's ... | |
| IF/THEN Conditionals | 8/15/2011 |
| Q: I'm trying to create multiple IF/THEN statements for one field. I have this so far-- NRS Level Pre: ... A: If you need multiple conditionals you need to use an AND or OR. For example: IIF(X>Y OR X<Z,...) ... | |
| Access 2010 OLE | 8/13/2011 |
| Q: I just had to redesign a form when I upgrades from access 2000 to 2010. All went well except when I ... A: You need to find out what code is referencing the OLE object. You probably have a Dim statement that ... | |
| Removing text characters in Access Field | 8/13/2011 |
| Q: How could I separate the following field, I just need the data containing the city name 'Atlanta' ... A: It depends on how static that pattern is. For example, if he city always starts with the 4th ... | |
| Using Check Boxes Independently | 8/10/2011 |
| Q: Let Me start by saying I am self taught in Access so pleas use the simplest terms for me. I have ... A: The image came through, but it appears you have entered a trap common to new database designers. It ... | |
| Ms Access 2007 | 8/10/2011 |
| Q: In fact i have create some buttons on my form and i want to view the vb code for these buttons. But ... A: The same way you did in earlier versions. You need to open the form in Design mode. Then right click ... | |
| Sendkeys with MSACCESS 2007 | 8/9/2011 |
| Q: I have used sendkeys in databases(Only when I felt there was no other way ! I hate sendkeys !) I ... A: Why? The ONLY time I would ever use SendKeys is if I need to send keystrokes outside of Access. ... | |
| Counting multiple values in a field | 8/9/2011 |
| Q: I am developing a program report based on several fields. I need to have the report list the ... A: Since you are in the process of developing the program, lets develop it correctly. The use of a lot ... | |
| Sendkeys with MSACCESS 2007 | 8/9/2011 |
| Q: I have used sendkeys in databases(Only when I felt there was no other way ! I hate sendkeys !) I ... A: I hate SendKeys too. I have rarely used it because its pretty flaky. Unfortunately, I have no ... | |
| Action query to Update History | 8/9/2011 |
| Q: I am trying to execute an action query on an AfterUpdate of a field. Here is what I have; where am I ... A: Using my method will allow you to query the Transactions table. to create your transactions report ... | |
| Action query to Update History | 8/9/2011 |
| Q: I am trying to execute an action query on an AfterUpdate of a field. Here is what I have; where am I ... A: Well first I would need to see the SQL for qryUpdateHistory. But you are not doing Quantity on Hand ... | |
| refining search pt2 | 8/9/2011 |
| Q: .. You just replied back to me and here are my responses to the questions you asked that needed to ... A: Do you know how to step through code using the Debug menu? You need to do that to see whether the ... | |
| MS Access Date Fields | 8/9/2011 |
| Q: I have a query that currently has a Entry date field and then a Update field. Currently when the ... A: Ok, You need to use a subquery for this. Each record should have a primary key. So you set the ... | |
| Text-to Column option in Access | 8/8/2011 |
| Q: I may have pressed something accidentally because in one of my columns, every record has a ... A: Run an Update query: UPDATE table SET fieldname = Left(fieldname,Instr(1,fieldname,"#")); Try this ... | |
| refining search pt2 | 8/8/2011 |
| Q: .. You just replied back to me and here are my responses to the questions you asked that needed to ... A: Lets start with b). In 3) you said the values in the Option button correspond to the stored status. ... | |
| refining search | 8/8/2011 |
| Q: I have a form that allows you to search the database for different chemicals in the database, by ... A: OK, you need to define a couple of things first, but you are very close. 1) Are the filters for ... | |
| Synchronized Combo Box with itemization? | 8/8/2011 |
| Q: I'm fairly new to Microsoft Access, and am having trouble trying to create the following. I have 3 ... A: You need a table where the items are categorized, but you may need several tables for the different ... | |
| computer | 8/8/2011 |
| Q: what are steps to prepare mark table? A: You need to define what you mean by "mark" table. You also need to let me know what version of ... | |
| Alerts, pop ups | 8/6/2011 |
| Q: Im putting a database for my post box business and would like to know how to put up alerts saying ... A: Sorry for the delay, I though I had answered this. First, yes you do need data for the query to ... | |
| Choosing a form type | 8/5/2011 |
| Q: I already have a table for this but need to create a form. I'm very new to access and would greatly ... A: Just use the Form Wizard to create the form. Move the controls around to suit your needs. As for ... | |
| refining search | 8/5/2011 |
| Q: I have a form that allows you to search the database for different chemicals in the database, by ... A: Well that's why it isn't working. Your module should look like this: Private Sub cmdFilter_Click() ... | |
| Sum/ grouping calculate columns | 8/5/2011 |
| Q: Good day, I admit this might be basic, but I am having difficulty getting around it. I have a simple ... A: Create two queries. The first query would look like this in SQL mode: SELECT Company, ... | |
| Alerts, pop ups | 8/5/2011 |
| Q: Im putting a database for my post box business and would like to know how to put up alerts saying ... A: I'm assuming you use this database daily. How much advance notice do you want to give them? ... | |
| refining search | 8/4/2011 |
| Q: I have a form that allows you to search the database for different chemicals in the database, by ... A: First where is strWhere defined? Second, why use the IF ElseIf. You can designate the button in the ... | |
| Search Filters | 8/4/2011 |
| Q: I have a cmdFindTool for find and replace. I would like the button to first clear the previous ... A: Some of the wizards use archaic code. I'm going to send you two responses. This response is to let ... | |
| Search Filters | 8/4/2011 |
| Q: I have a cmdFindTool for find and replace. I would like the button to first clear the previous ... A: What version of Access do you have? DoCmd.MenuItem is very old code and only included for backward ... | |
| Is there a way to override automatic record updating of Access 2007 forms? | 8/2/2011 |
| Q: I have a form that currently searches my table and displays the results as a continuous form. It has ... A: This is one of Access's strengths and weaknesses. Many people get caught on the fact that, as soon ... | |
| Access to Oracle linking | 8/2/2011 |
| Q: In previous versions of Access, I only had to enter my password one time and mark the save password ... A: How are you connecting to the Oracle tables? If you are using ODBC, then you need to look at the ... | |
| hiding months in cross tabs report | 8/2/2011 |
| Q: We are creating an Access 2007 database for our kindergarten teachers to record the information of ... A: You are on the right track. Unfortunately the screen shot of your query design was too fuzzy for me ... | |
| Calculate Days between two dates. | 8/2/2011 |
| Q: I have 2 text boxes with starting date and ending date. I want to get the calculation of the number ... A: Set the controlsource of the third textbox to: =DateDiff("d",[Start],[End]) Substitute the actual ... | |
| How to increment a number with letter included? | 8/1/2011 |
| Q: Good Day Sir, i have a series of serial number that will input in my database, the serial number has ... A: I've actually written a blog on how to do this. See the Sequential Numbering blog. Blog address is ... | |
| null values and queries | 7/29/2011 |
| Q: 2 questions that are currently giving me a headache, if you can help at all would be great. 1) i ... A: 1) Its very hard to SET a null option. You can allow a control to retain its initial null option, ... | |
| Reports to Word | 7/29/2011 |
| Q: Long time no questions :) How would i go about getting a Report to open in word off of a button in ... A: DoCmd.Openreport "reportname", acPreview DoCmd.RunCommand acCmdExportRTF That will open the dialog ... | |
| Lock field on a form | 7/29/2011 |
| Q: Is it possible to automatically lock a field (unable editing) on a form based on a date or criteria? ... A: Well part of your problem may be structure. If you have fields in a table labeled Period1, period2, ... | |
| Date manipulation and query | 7/29/2011 |
| Q: Scott: My database has 2 fields for dates: start, and finish. All entries have a start, not all ... A: Well first adding 75 years is easy, Just use the DateAdd function. The problem is the condition of ... | |
| Auto Populate a field from the same table | 7/28/2011 |
| Q: I have two tables set up, one with personal information (SSN, Last, First, Rank and DOB. The SSN is ... A: Please trust my expertise in this. I know exactly what your issue is. Your problem is that you don't ... | |
| Text box for field that has been modified | 7/28/2011 |
| Q: Access 2003 I am new to creating databases. I need a text box to be display when a field in a form ... A: First you don't have fields on forms, you have controls that may or may not be bound to a field in a ... | |
| Comparing data in query | 7/28/2011 |
| Q: This is the SQL code: SELECT [Copy Of Diagonal].[Profile od diagonal] FROM [Copy Of Upright] INNER ... A: Is there a space before INNER? Can you look at it in Design View and make sure the joins are ... | |
| Code | 7/28/2011 |
| Q: Scott I have a main form and datasheet subform both linked by a date. The subform has several fields ... A: What version of Access? I would probably use DoCmd.RunCommand acCmdRecordNew (? not exaclty sure of ... | |
| A request with great respect. | 7/28/2011 |
| Q: R/Sir, this is right (BETWEEN Forms!formname!txtStart AND Forms!formname!txtEnd), sir but what about ... A: You need two queries as I've said. One query to sum the transactions for the day before the ... | |
| Forms and Questionaire | 7/28/2011 |
| Q: I need some help in setting up an Access Database. Basically, this database will be used to conduct ... A: What I need to see is your table structure. A questionnaire or survey database has a very specific ... | |
| Comparing data in query | 7/28/2011 |
| Q: This is the SQL code: SELECT [Copy Of Diagonal].[Profile od diagonal] FROM [Copy Of Upright] INNER ... A: The SQL statment doesn't match the table descriptions you gave me. Based on the tables, This SQL ... | |
| public function to get listbox string as query criteria | 7/27/2011 |
| Q: I have a listbox on a form containing ‘key areas’ that a user can select. I have a public function ... A: 1) Ok, in this case I would have a hidden control on my form and assign strList to that ... | |
| Comparing data in queries | 7/27/2011 |
| Q: I have a question about the comparing data. I have got two tables witch are: ID_Upright Weight brace ... A: If you are getting a data type mismatch that means that the weight field in each table is a ... | |
| public function to get listbox string as query criteria | 7/26/2011 |
| Q: I have a listbox on a form containing ‘key areas’ that a user can select. I have a public function ... A: Welcome to the club. This has bugged a lot of us. It just doesn't work as variable. The way I get ... | |
| Comparing data in queries | 7/26/2011 |
| Q: I have a question about the comparing data. I have got two tables witch are: ID_Upright Weight brace ... A: The problem here is if Weight brace/B is not unique to Diagonal. But what you can do is use Query ... | |
| unbound check box | 7/25/2011 |
| Q: I am working with Access 2007. I have continuous data in my detail section of a form, each with its ... A: Continuous form mode is really one set of controls displayed multiple times. So if you have an ... | |
| duplicate subreports | 7/25/2011 |
| Q: I have a main report that lists the background info about a selected project; I want subreports on ... A: I don't see anyway of doing this. The best idea I could come up with was to try an create a crosstab ... | |
| Calculating Budget | 7/25/2011 |
| Q: and thank you in advance for your time. I'm using Ms Access 2007 to create a database that contains ... A: I see some holes in your logic and structure. First, how do you calculate cost per trainee? Seems to ... | |
| Access question | 7/21/2011 |
| Q: You had helped me in another question with my Customer satisfaction / survey type db. It's coming ... A: The purpose of a relational database is to limit the amount of redundant data. When you find ... | |
| Msaccess 2003 Entire application mousepointer change | 7/21/2011 |
| Q: Scottgem, 1.is it possible to change the entire msaccess application cursor? I can change for ... A: There are very few application level events. so i don't think this is possible using VBA. There may ... | |
| Moving fields of one table to another table as records | 7/21/2011 |
| Q: Working on a different database, this time trying to clean it up. Problem: Records tracking donors ... A: What you are doing is, essentially, normalizing your data. You would have to do this by first ... | |
| RE: Problem populating subform from parent form | 7/21/2011 |
| Q: The other threat wouldn't let me ask another follow up question (yikes!). I was having issues with ... A: One thing to try. In the After Update event of the AcountHolder combo, add this line of code: ... | |
| Access calculating data | 7/20/2011 |
| Q: I read your answer about how you don't need to ever store calculated data in a table, which is ... A: Well part of your problem here is structure. A survey database has a specific structure and its not ... | |
| make Textbox right/left area clickable | 7/20/2011 |
| Q: Scottgem, nondropdown cbo + its button opening popup frms not answered. current ... A: Controls have a single and double click event, but not a right click event. An icon is an image ... | |
| Access calculating data | 7/20/2011 |
| Q: I read your answer about how you don't need to ever store calculated data in a table, which is ... A: First, let me say no problem in trying to learn to use Access, that's what I and others are here ... | |
| RE: Problem populating subform from parent form | 7/20/2011 |
| Q: The other threat wouldn't let me ask another follow up question (yikes!). I was having issues with ... A: I'm a little confused here. When you change the AccountHolder in the Combobox the subform does not ... | |
| Opening Budget | 7/19/2011 |
| Q: I downloaded the Expense Tracker template. How can I format the Expense Tracker Template to show a ... A: If its the template I think it is (for 2010?) you can create a dummy record for 12/31/2010 with the ... | |
| A request with great respect. | 7/19/2011 |
| Q: R/Sir, this is right (BETWEEN Forms!formname!txtStart AND Forms!formname!txtEnd), sir but what about ... A: Again, opening and closing balances are calculated, not stored. So you have fields for receipt and ... | |
| Dlookup to auto fill | 7/18/2011 |
| Q: My name is Jason and I have a questions with Access I was wondering if you could possibly shed some ... A: My question here is what are the ControlSources of those controls listed (txtPhone, etc.) I would ... | |
| A request with great respect. | 7/18/2011 |
| Q: R/Sir, this is right (BETWEEN Forms!formname!txtStart AND Forms!formname!txtEnd), sir but what about ... A: Opening and Closing Balances are CALCULATED, not stored. You calculate them by adding the incoming ... | |
| Access to a scanner | 7/17/2011 |
| Q: I am attempting to hook up a database i have created to a scanner that we have. What i would like to ... A: Yes, this is possible. But it depends on the scanner. A barcode scanner is just an alternative input ... | |
| MS Access reports | 7/17/2011 |
| Q: I am running Access 2000. I have set up 3 tables with information from decisions made at 3 ... A: The problem is probably with your joins in your queries, but to help I would need to know the ... | |
| Filter report with form Access 2010 | 7/15/2011 |
| Q: I have upgraded my computer to Windows 7, with access 2010. I have a report that was filtered with a ... A: Why are you making this much more complicated then you have to? First, you should NOT be opening ... | |
| Access Query | 7/15/2011 |
| Q: Windows Vista and MS Access 2003 Hi I am new to SQL and MS Access 2003. I am trying to generate a ... A: This might work out best in a report. Group the report on TC Name and add a footer to Sum the time. ... | |
| MS Access - | 7/14/2011 |
| Q: I am using Windows version 6.0 (Build 6002: Service Pack 2) MS Access 2007. I have a report which ... A: Rather than use the AVG() function try doing the calc using = ... | |
| RE: Time Format 24 hours | 7/14/2011 |
| Q: After reading your article http://en.allexperts.com/q/Using-MS-Access-1440/Time-format-24-hours.htm, ... A: The function goes in a Global module. Just copy and paste from Public Function to End Function into ... | |
| Access Query | 7/14/2011 |
| Q: Windows Vista and MS Access 2003 Hi I am new to SQL and MS Access 2003. I am trying to generate a ... A: I would need to know the structure of your tables to be able to suggest the query design. Have you ... | |
| A request with great respect. | 7/14/2011 |
| Q: R/Sir, this is right (BETWEEN Forms!formname!txtStart AND Forms!formname!txtEnd), sir but what about ... A: If I understand the question, you want to know how to calculate the opening and closing balances for ... | |
| MS Access - | 7/14/2011 |
| Q: I am using Windows version 6.0 (Build 6002: Service Pack 2) MS Access 2007. I have a report which ... A: Ok, So does this query calculate correctly? How far off is the AVG calc? Are there records with ... | |
| Generating Emails with Access | 7/13/2011 |
| Q: Currently I have a mail merge that I use to send out letters to students. I take the list generated ... A: You don't have to export anything. You can create an Access report that works like a form letter. ... | |
| Access 2003 Autofill/Autopopulate | 7/13/2011 |
| Q: I'm not sure of the exact terminology regarding my query, but when I attempted searching your blog I ... A: You need to look at the code behind your controls. Probably behind the After Update events. The ... | |
| Access 2007 populate field in parent table from child table | 7/10/2011 |
| Q: I'm designing a database to track school schedules and what not for a small performing arts academy ... A: Your AcctHolder table should have the last name of the guardian, you can have fields for the two ... | |
| Restrict accessing menu for certain users | 7/10/2011 |
| Q: I have created a database with many menu's and in that I am having a menu called "Admin Use" and I ... A: Check out my blog on Login Security (see blog address below). I think it has the info and code ... | |
| Dlookup to auto fill | 7/9/2011 |
| Q: My name is Jason and I have a questions with Access I was wondering if you could possibly shed some ... A: First, I'm not robert. Second, did you look at my blog? It explains exactly how to do what you are ... | |
| Combo boxes | 7/9/2011 |
| Q: Scott I have an 'Attendance' subform where the Combobox[Province] is dependent on another ... A: The way I would do this is to use TWO controls. A combobox with the Rowsource like: SELECT Province ... | |
| Access 2007 Reference on a Form | 7/8/2011 |
| Q: I am using Access 2007 on a laptop with Vista. My question has to do with a form that I have which ... A: You should NOT be using a combobox for the third control. What you should be doing is using an ... | |
| Print Current Record on different reports | 7/7/2011 |
| Q: Sir, I have a form based on single table. But I want to print current record in different reports. ... A: Is the Employee No field named ID? Is the name of the control also ID? Does the prntmemo report use ... | |
| Conditional Formatting on a Continuous Form | 7/6/2011 |
| Q: What is the best way to format one field in a Continuous Form based on current date, not the value ... A: What is the structure of your table? I assumed you were using a crosstab query so that period ... | |
| Convert MSDN Example titled "A Visual Foxpro Security System" to Ms Access 2003 | 7/6/2011 |
| Q: Located in Visual Studio 6.0 MSDN/Technical > Chapter: Visual Tools Published: FoxTalk Written: Les ... A: First, I don't like Access ULS security either and never use it. I'm not going to translate that ... | |
| Print Current Record on different reports | 7/5/2011 |
| Q: Sir, I have a form based on single table. But I want to print current record in different reports. ... A: Then use the Report wizard to create different reports. Include the employee # has a hidden control ... | |
| MULTI TABLES IN ONE | 7/5/2011 |
| Q: im using access 2003 I made tables to which each hold's unique value's for example 3 tables are ... A: Why do you have three tables? All you need is one projects table with a filed to indicate which ... | |
| ACCESS SQL Question | 7/4/2011 |
| Q: I have an Access database which has author name information in it. the relavent fields are: FName, ... A: Unless all three have the same ID #, there is little you can do. If the names are all the same you ... | |
| AutoFill options... | 7/4/2011 |
| Q: My name is Jason and I have a questions with Access I was wondering if you could possibly shed some ... A: Part of the answer goes to structure. You need at least three tables here. A Customers table with ... | |
| Invoice Attendance | 7/4/2011 |
| Q: I am trying to create a database that invoices an individual based on a tick box. for example this ... A: This goes to structure. This is basically a order entry application. You should have at least four ... | |
| Conditional Formatting on a Continuous Form | 7/1/2011 |
| Q: What is the best way to format one field in a Continuous Form based on current date, not the value ... A: Then you need to compare today's date to the period. What is the Recordsource of the form? What does ... | |
| import data | 6/30/2011 |
| Q: When i append a data file (.txt) with 80,000 records (rows) to a MS Access 2007 table, i got a ... A: I suspect there is something that doesn't match up between the data in the text file and target ... | |
| Controlling check boxes using a different form | 6/30/2011 |
| Q: I am working on a project where I have multiple forms. There are 2 forms where the controls are not ... A: The code looks OK, but I would like to understand better the purpose here. Have you tried stepping ... | |
| Conditional Formatting on a Continuous Form | 6/30/2011 |
| Q: What is the best way to format one field in a Continuous Form based on current date, not the value ... A: When using Conditional Formatting you are generally comparing the value in the field or in a related ... | |
| Question about MS Access | 6/30/2011 |
| Q: Windows XP and MS Access 2007 Hi! Is it possible to receive an automatic email notification if I ... A: You can use the SendObject method to generate an e-mail when any "event" is triggered. For example ... | |
| Starting a new project - want to start right | 6/30/2011 |
| Q: that went out today and i got alot of thanks for it, which is in some parts due to you and it ... A: Students<->Events is definitely many to many. But the tjxStudentEvent table is basically your ... | |
| Switchboard password | 6/30/2011 |
| Q: I'm reading your replies to switchboard password. ... A: I've written a blog on using VBA to create login security (see blog address in signature). The blog ... | |
| Starting a new project - want to start right | 6/29/2011 |
| Q: that went out today and i got alot of thanks for it, which is in some parts due to you and it ... A: Well to start off you need a Houses table, a Students table, an events table, a Sports Day table and ... | |
| Query and LIKE | 6/29/2011 |
| Q: Is it possible to link 2 queries using operator LIKE. Specifically, 1st query has column ... A: No you can't join with LIKE. You need to redesign, not only your tables but your work processes. ... | |
| Total of Time | 6/28/2011 |
| Q: (access 2007) I want to a have a field in a report that shows the total of time value fields. i am ... A: Access stores a date/time value as a double precision number where the integer portion is the number ... | |
| Combobox selection height | 6/27/2011 |
| Q: I have a combobox that shows the values of a stacked address query I have the combobox height ... A: Don't think you can do this. I tried and the drop down will only show one line. What you might try ... | |
| Auto Inserting Record upon date change | 6/26/2011 |
| Q: is there a way that everday.. a new record will be created.. eg.. on 04/06/06... wen i open my ... A: Sure. You could run code like this when the app opens: Dim dteMax as Date, dteCurr As Date Dim ... | |
| Enable/Disable shift key | 6/24/2011 |
| Q: Me again. Hopefully the last question i will ever need to ask :) I would like a button on my ... A: Actually you don't want to do that. What you want is a tool that will change it externally. So what ... | |
| MS Access 2007 query | 6/23/2011 |
| Q: I have a database written in access 2003 but since upgraded to 2007 the basic database is sound and ... A: This is what is generally referred to as a Bill of Materials (or BOM). Where you have individual ... | |
| MS Access 2007 query | 6/23/2011 |
| Q: I have a database written in access 2003 but since upgraded to 2007 the basic database is sound and ... A: Yes, but this is a design issue. You need an additional table here. For example: tblCombinedUnits ... | |
| Search Query for Linked Tables | 6/22/2011 |
| Q: I have three access databases using the asset tracking template. I want to link all the databases ... A: The databases have the same structure? You can create a UNION query using an IN clause to reference ... | |
| Creating a searchable database that generates reports | 6/21/2011 |
| Q: I have next to no experience whatsoever with using Access, but I am a quick learner and am trying to ... A: First, to print a report just for a selected record is easy. You use a WHERE clause in the ... | |
| multiple entries | 6/21/2011 |
| Q: I'm new to access. doing a research where i need to monitor patient's lipid levels. trying to create ... A: Continuous form mode is also referred to as a Tabular form. The Form wizard will create one. The ... | |
| Warning msgboxes? | 6/20/2011 |
| Q: Sorry to ask for your help again, As you know i'm creating a database for ink/toner requests for ... A: Ok, First, Your Requests table can use some modification. You do not need the Manufacture and Model ... | |
| Warning msgboxes? | 6/20/2011 |
| Q: Sorry to ask for your help again, As you know i'm creating a database for ink/toner requests for ... A: OK, show me the structure of your Cart table and your Requests table. Also, describe, briefly how ... | |
| Warning msgboxes? | 6/19/2011 |
| Q: Sorry to ask for your help again, As you know i'm creating a database for ink/toner requests for ... A: First, let me say that this service exists for helping people. I enjoy helping people which is why I ... | |
| DLookup problems | 6/19/2011 |
| Q: I am having a slight issue with DLookup function in a control source of a text field on a form. I ... A: First, I really wouldn't use a Dlookup for this. I would just hide the ID column. For example; using ... | |
| DLookup problems | 6/18/2011 |
| Q: I am having a slight issue with DLookup function in a control source of a text field on a form. I ... A: Close, but what you are doing is pulling the VALUE of the control. The quotes are therefore ... | |
| iif function | 6/18/2011 |
| Q: I am using Access 2003, Windows XP Pro. I have a report in my Access database which includes a ... A: Actually, this can be done more easily using Conditional formatting. Open the report in Design mode ... | |
| I really hope you can help | 6/17/2011 |
| Q: I am totally stumped.. In Access 2007, I have a field in a table that stores the last transaction ... A: First, If you are trying to filter records according to some criteria, you don't do a calculation. ... | |
| Refresh Access 2003 table | 6/17/2011 |
| Q: I've created a Inventory order form, which pull data, from various tables, to calculated order ... A: Its easy to do, but I have to question WHY you are doing it. Sounds Like you have a setup that is ... | |
| Yes/No calculating values | 6/17/2011 |
| Q: I am trying to figure out how to do a formula in Access 2007 for accommodation booking.I got 4 ... A: Part of the problem here is the structure of your database. Using Yes/No fields can be a sign of a ... | |
| passwords | 6/16/2011 |
| Q: I have been trying use a method of password protecting a command button that I found on here, but it ... A: Try it this way: DoCmd.OpenForm "Activities_Menu" The formname parameter is a string value so you ... | |
| Update table by form (query) | 6/14/2011 |
| Q: I have a form that is based off a query of two tables. I need the users to enter data based off the ... A: I do not recommend basing a from on a multi-table query. Generally such queries are uneditable. What ... | |
| Multiple Calendars in access 2007 | 6/14/2011 |
| Q: How can i make multiple calendars in access 2007. I am developing a database for a driving school in ... A: Sure you can. Basically you create a table with the schedule and a table of drivers. Each driver is ... | |
| Before update question | 6/14/2011 |
| Q: I've got a simple database that I created for my professor to use to keep track of students who earn ... A: If you use a Bound form, Access will save any changes when the record loses focus. To prevent this, ... | |
| Access Query | 6/13/2011 |
| Q: I am trying to put a search button on a form for nhs. I have written some vb but it stops searching ... A: Ok, What version of Access are you using? This may differ by version. You can try: Set rs = ... | |
| execute function in remote msaccess 2007 db | 6/13/2011 |
| Q: is there a way to execute a function in a remote msaccess 2007 db ? My scenario: i have multiple ... A: Not that I know of. You can access some part of an Access app remotely, but I don't believe you can ... | |
| option box | 6/11/2011 |
| Q: Scott: I have an option box on a form to select a type of biopsy (US guided, Stereo, Excision etc.) ... A: I would use a lookup table that matches the numbers of the group's controls to the text. Then join ... | |
| Access Query | 6/11/2011 |
| Q: I am trying to put a search button on a form for nhs. I have written some vb but it stops searching ... A: I would not use DoCmd.FindRecord if there may be multiple matches. Instead I would use a filter. You ... | |
| execute function in remote msaccess 2007 db | 6/10/2011 |
| Q: is there a way to execute a function in a remote msaccess 2007 db ? My scenario: i have multiple ... A: What you CAN do is create a single front end that links to all the tables you need to update. Put ... | |
| automatic line | 6/10/2011 |
| Q: I was wandering if you could help me. Is there a way of doing an automatic line when you finished ... A: Your question is not very clear. Data Entry should be done through forms. If you use a datasheet or ... | |
| MS Access query on next record | 6/9/2011 |
| Q: I had a question on the use of queries in microsoft access: let's say I have a table : User ... A: So you want something like this: User Contracts 1 1234:1237:1389 If so, you can do that with ... | |
| Need some direction | 6/9/2011 |
| Q: You look like the perfect person to ask. I need a solution for trying to move our office into being ... A: If you want to use a Win 7 based tablet, then you can use Access for this task. I'm not sure how ... | |
| MS Access Switchboard | 6/7/2011 |
| Q: I used Access 2003 and Windows Vista. I designed a menu for my application using Access ... A: Hold down the shift key until the db loads. Alternatively, you may be able to just press F11 to get ... | |
| Using a field created with a recordset in a report. | 6/6/2011 |
| Q: Scott, What I need: I would like to have a form with a combo box that the user will select a ... A: I think you are making this much more complicated then it needs to be. First, create a query that ... | |
| tracking records browsed | 6/6/2011 |
| Q: Before I re-invent the wheel, is there a method in MS Access (2007 is the version I'm using) to keep ... A: Not built in. You would have to create a search function that keeps track of previous searches per ... | |
| Basic user login form | 6/6/2011 |
| Q: I would like to create a login form in access 2007, I have created the form with an unbound username ... A: I've written a blog (see blog address below) on using VBA to create logins. I would suggest you ... | |
| Creating a Form Query | 6/6/2011 |
| Q: I want to know when creating a form if it is possible to make it so it links all lines that has a ... A: I need to see your table structure. A form is just a view of your data. Forms can be in Single ... | |
| Creating a 'dynamic' lookup column based on a field value from the same row | 6/5/2011 |
| Q: I'm not even sure if what I want to do is possible but I figured if it is, you would probably know ... A: Well first I have to wonder why you want to recreate the wheel. Quicken or Microsoft Money do what ... | |
| multiple entries | 6/4/2011 |
| Q: I'm new to access. doing a research where i need to monitor patient's lipid levels. trying to create ... A: You should have at least two tables here. One table for patient info, the other for visit info. With ... | |
| Grouping in a Report | 6/3/2011 |
| Q: Could you please advise how I can make a subform/subreport inserted into the main report (both based ... A: Like I said, you probably need to include Section in your main report and link the subreport by ... | |
| Grouping in a Report | 6/3/2011 |
| Q: Could you please advise how I can make a subform/subreport inserted into the main report (both based ... A: How are the sub-reports linked to the main report? I suspect you will need to add Section as a ... | |
| Updating a table problem | 6/2/2011 |
| Q: My DB has a requirement imposed upon it where it needs to be backed up weekly. To make sure this is ... A: I assume BuDate is control on your form bound to the BuDate field. The possible problem is that you ... | |
| Access 2007 | 6/1/2011 |
| Q: I am somewhat computer literate and I am trying to create a database for my plaintiff's workers' ... A: The key here is in your database design. The first thing you need to do is make a list of all the ... | |
| Printing a report based on Yes or No answer | 6/1/2011 |
| Q: I have a form that users input data into, and one of the fields requires the users to choose if what ... A: Well, first, I need to say that you shouldn't print a form. Forms are not designed for printing. You ... | |
| access - evidence of readers | 6/1/2011 |
| Q: foremost i would like to tell you, that I'm real Access novice. I'm learning how to create a small ... A: 1. VisitorID is the primary Key ONLY in tblVisitor (its OK if its not an autonumber as long as its ... | |
| access - evidence of readers | 5/31/2011 |
| Q: foremost i would like to tell you, that I'm real Access novice. I'm learning how to create a small ... A: Ok, so if its manual input, the librarian will select the visitor name from a list (or input a new ... | |
| access - evidence of readers | 5/31/2011 |
| Q: foremost i would like to tell you, that I'm real Access novice. I'm learning how to create a small ... A: Tables are almost correct. What you need is: tblVistor VistorID (Primary Key Autonumber) Firstname ... | |
| Help on MS Access (password for a form) | 5/30/2011 |
| Q: Whichever form name I put instead of PasswordProtectedForm, Access says the form doesn't exist. I'm ... A: Not sure why the acFormEdit should make a difference, but I do believe the order and definition of ... | |
| Help on MS Access (password for a form) | 5/29/2011 |
| Q: Whichever form name I put instead of PasswordProtectedForm, Access says the form doesn't exist. I'm ... A: No, there is no Input mask on a button, but I didn't say to put it on the button. Put it on the ... | |
| Help on MS Access (password for a form) | 5/29/2011 |
| Q: Whichever form name I put instead of PasswordProtectedForm, Access says the form doesn't exist. I'm ... A: First, don't close the form until After you open the new form. Second, You use the name of the form ... | |
| SetValue in Embedded Macro | 5/28/2011 |
| Q: I'm trying to create a SetValue embedded macro. I go AfterUpdate, click on ..., Macro builder. ... A: Unless you are planning on publishing the app to the WEB, using macros, especially with 2007, is not ... | |
| Limitation | 5/28/2011 |
| Q: I am using Ms Access Database ver(2003) I want to know what is the capacity. How many table can I ... A: Access files are limited to 2GB in size. But this limitation can be gotten around by using linked ... | |
| automatically generate records weekly | 5/27/2011 |
| Q: I am a total novice with Access 2003, but I have managed to create a fairly usable ... A: I'm sorry, I misunderstood. Create an Append query. Again, in Query Design mode select the table ... | |
| automatically generate records weekly | 5/26/2011 |
| Q: I am a total novice with Access 2003, but I have managed to create a fairly usable ... A: You need a query. Open query design mode and select your table. Add all the fields you want in the ... | |
| Search Box on forms in MsAccess 2007 | 5/26/2011 |
| Q: Is there a way to either disable or hide the search box on a form in Access 2007 ? A: If you are referring to the search box on the Navigation Bar (that includes the VCR buttons), then I ... | |
| Airline Booking System | 5/25/2011 |
| Q: I want to make tables on Airline Booking System.I am having difficulty that which tables should I ... A: I can provide you some guidelines, but the scope of your app will dictate the actual structure. ... | |
| Access list boxes | 5/24/2011 |
| Q: I have a form with a combobox listing projects. Once updated a list box is populated with the ... A: what is the purpose of using a listbox if its not to select something. To clear the selection you ... | |
| queries not working | 5/23/2011 |
| Q: I have a fairly simple query that is to look up the first names and surnames of members from the ... A: In any table that references the member all you need int hat table is the member ID as a foreign ... | |
| queries not working | 5/23/2011 |
| Q: I have a fairly simple query that is to look up the first names and surnames of members from the ... A: First, you should not have the IDName and Fullname as fields in your database. Since you already ... | |
| Dlookup and delete if found | 5/22/2011 |
| Q: My table is named "LivraisonsTemp", that contains records and the important field here is [Réf ... A: OK, first it looks like your database is not normalized properly with redundant data. It looks like ... | |
| 2 combo boxes in continuous form | 5/21/2011 |
| Q: I've developed a continuous form (frmSchedule) that contains two combo boxes: cboCustomer and ... A: I was afraid that might not work. One other thing you should try is setting the RowSource back to ... | |
| 2 combo boxes in continuous form | 5/19/2011 |
| Q: I've developed a continuous form (frmSchedule) that contains two combo boxes: cboCustomer and ... A: It is a weakness of the continuous form, since a continuous form is really one set of controls ... | |
| Forms / Sub Tables | 5/19/2011 |
| Q: First I want to thank you so much for the answer I just read from you "Prevent a duplicate ... A: First, tlu is a prefix I use to indicate a lookup table. Lookup tables are ones used to standardize ... | |
| 2nd part of inventory question | 5/19/2011 |
| Q: I was able to get QOH to run and all is well with that part. Thank you! My second dilema is on ... A: You need to use an OUTER join here, not the INNER join. In Query Design mode right click on the join ... | |
| M S Acces Report creation | 5/19/2011 |
| Q: How to create a report which will provide output of only those fields of months in which we have ... A: Create a query that returns only the data you want to report on, then use that query as the ... | |
| AdjQty Question | 5/18/2011 |
| Q: I've been reading you responses regarding setting an adjusted qty on hand in Access. I have a ... A: Now I'm confused. The query you show above doesn't have the adjqty column. Nor does it a field names ... | |
| AdjQty Question | 5/18/2011 |
| Q: I've been reading you responses regarding setting an adjusted qty on hand in Access. I have a ... A: If it is prompting you for a value, that indicates that Access can't find the referenced item within ... | |
| AdjQty Question | 5/18/2011 |
| Q: I've been reading you responses regarding setting an adjusted qty on hand in Access. I have a ... A: Try it this way: adjqty: iif([orderitemstatus]= 6 or [orderitemstatus]= ... | |
| subdatasheets / maybe forms | 5/18/2011 |
| Q: First I want to thank you so much for the answer I just read from you "Prevent a duplicate ... A: Subdatasheets are not the correct terminology. The way I would handle this is with a subtable like ... | |
| password protection | 5/18/2011 |
| Q: I am very slowly getting there with my database that I am creating. Firstly having great ... A: I suggest you check out my blog on Login Security using VBA. Access is not the most secure platform. ... | |
| Scores for different parameters | 5/18/2011 |
| Q: I have a table with a regions performance with regard to 20 parameters.Scores needs to be allocated ... A: I would create a table that holds the scores for each set of field values. Then use a DLookup to ... | |
| Still won't show zero when printing | 5/17/2011 |
| Q: Here is the last question I asked (the system would not let me ask more questions):Here is the sql ... A: I think I need to see the data here. Can you send me a copy of your db with just the tables and ... | |
| Access 2007 custom menus | 5/16/2011 |
| Q: We have an Access 2007 database created in an earlier version with a custom menu which Access 2007 ... A: Is this an MDB running under 2007 or was it converted to an ACCDB? Without knowing how the menu was ... | |
| Access 2010 PDF Export - File very large | 5/15/2011 |
| Q: I am using VBA code to create a PDF file from a Access 2010 report and then going on to use it to ... A: This is partially a PDF issue and partially Access PDF writer. But there is little you can do about ... | |
| Open Forms | 5/13/2011 |
| Q: I would like to open a form to a blank record BUT still be able to open previous records. When I ... A: The Openform method has a DataMode property which can open a form in either Add or Edit mode. So you ... | |
| Open Forms | 5/13/2011 |
| Q: I would like to open a form to a blank record BUT still be able to open previous records. When I ... A: Yes, but it depends on how you open the form now. What version of Access and how do you open the ... | |
| Access 2007 Printing Report | 5/12/2011 |
| Q: I have a database that contains a report with 3 subreports. The subreports make calculations and on ... A: Ok, so the query only returns values for Task Order 1? If that's the case, you need to either create ... | |
| Acess linking fields | 5/12/2011 |
| Q: Ok, another Question. With the same database as previously. I now have my item_no & item_price in ... A: "our work was to do research and figure this out." And that's why I won't be as detailed. it's up ... | |
| Acess linking fields | 5/12/2011 |
| Q: I am battling to create a database. i need my fields include: Employees Employee nr, employee name, ... A: Ah so these are questions related to a course. While I will help, my help will be a bit different. I ... | |
| Update a field in one table with a field from another table | 5/11/2011 |
| Q: I have two tables and am trying to update a field in one with a field in the other using an Update ... A: Ok, then your problem is that no records match your selection criteria. Either there are no matching ... | |
| Sharing Access | 5/11/2011 |
| Q: I maintain a Access database, which accepts data submitting from a web-application. However, time by ... A: Files that are used by users are referred to as Production files. Files are used in development are ... | |
| Update a field in one table with a field from another table | 5/11/2011 |
| Q: I have two tables and am trying to update a field in one with a field in the other using an Update ... A: First thing to try is turn the query back to a SELECT query and see if it returns the correct ... | |
| Acess linking fields | 5/11/2011 |
| Q: I am battling to create a database. i need my fields include: Employees Employee nr, employee name, ... A: The answer is you don't. As a general rule we do not store calculated values. These can be ... | |
| Update a field in one table with a field from another table | 5/11/2011 |
| Q: I have two tables and am trying to update a field in one with a field in the other using an Update ... A: Can you show me the SQL for the query? Open the query in Design mode then select SQL view and copy ... | |
| Solution to Multiple Field Update | 5/10/2011 |
| Q: I wanted this to be a follow-up but I already asked too many. Anyway, I wanted to post that I got ... A: Yeah, one of the issues with this site is they limit followup. Hmm should have worked with a comma ... | |
| MS Access Report View | 5/10/2011 |
| Q: Could you please help out with the report design? The report should display the progression of ... A: You can ONLY columnize the Detail band. You have to create Group headers for the the two grouping ... | |
| Access - Printing Report | 5/10/2011 |
| Q: I am trying to print a report in access and the following message comes up The expression on format ... A: Check the On Format events for each band in Design mode. There is something in that event that is ... | |
| Automating Changes to Multiple Records | 5/10/2011 |
| Q: I have 3 fields in play here: folder_number, acc_number, & box_number. I have a situation where I ... A: Ok, its becoming clearer. But the question now becomes where are you getting the acc_number and ... | |
| MS Access Report View | 5/10/2011 |
| Q: Could you please help out with the report design? The report should display the progression of ... A: Your best bet is to add a grouping level for TradeID. So group on Section, then TradeID. Add ... | |
| Automating Changes to Multiple Records | 5/10/2011 |
| Q: I have 3 fields in play here: folder_number, acc_number, & box_number. I have a situation where I ... A: Still not 100% clear, but if you want to append lets say 10 records with the same folder number, ... | |
| Automating Changes to Multiple Records | 5/9/2011 |
| Q: I have 3 fields in play here: folder_number, acc_number, & box_number. I have a situation where I ... A: What I'm missing here is insert into what? What it sounds like you want is to generate an Append ... | |
| Access Forms | 5/9/2011 |
| Q: Scott I have a form which returns the results of 10 Dlookup queries to give yearly totals. It works ... A: The thing is, if you can get a single query to display the all the values, you can then use a ... | |
| createing a unique customer id | 5/7/2011 |
| Q: I want to generate a new unique customer id through programming in MS Access form. The form could be ... A: Why not just use an Autonumber Datatype? But if you want to generate a number on your own, see my ... | |
| results of expressions not showing elsewhere | 5/6/2011 |
| Q: As I have found I cannot put expressions into my table I have put them into the form fields, but I ... A: The problem is not getting it to calculate past a 24 hour period, but to get it to DISPLAY. Access ... | |
| Searching between dates | 5/6/2011 |
| Q: Scott: I have a database which tracks records by date; however, many of them are in folders which ... A: Ok, Access stores a Date/Time value as a double precision number where the integer represents the ... | |
| Cut off point for Access | 5/5/2011 |
| Q: ! Where is the cut off point for using access and switching to SQL. Can you reach 100m in sakes ... A: The reasons to go to SQL as the back end are two fold. First is concurrent usage. The Access JET ... | |
| results of expressions not showing elsewhere | 5/5/2011 |
| Q: As I have found I cannot put expressions into my table I have put them into the form fields, but I ... A: As a general rule we don't store calculated values. You are correct that you can display the results ... | |
| importing data from Excel | 5/5/2011 |
| Q: I have Office 2007 I am finding that when I am importing data from Excel in Access that Access is ... A: As for automating the process. I believe the DoCmd.RunCommand method will allow you to run s a saved ... | |
| importing data from Excel | 5/5/2011 |
| Q: I have Office 2007 I am finding that when I am importing data from Excel in Access that Access is ... A: One would assume that. The only thing I can think of is that Windows thinks the Excel file is still ... | |
| MS Access Report Sort Order - SQL Server back-end | 5/4/2011 |
| Q: We have an MS Access 2007 database with linked SQL server 2005 tables and views. There are two MS ... A: Access reports have their own independent sort. So any sort done in queries or tables is ignored by ... | |
| validation rule | 5/3/2011 |
| Q: I'm not all that familiar with Validation Rules, but I tried this rule : ([subCategory] Is Null) OR ... A: Nope the code I gave you will work just as well on a continuous and single form view. The only issue ... | |
| validation rule | 5/2/2011 |
| Q: I'm not all that familiar with Validation Rules, but I tried this rule : ([subCategory] Is Null) OR ... A: Yes a validation rule doesn't work well here. You do need code but you need it in multiple places. ... | |
| Lookup a value in Access 2003 | 5/2/2011 |
| Q: I have a sales total for the week. I need this to look up and automatically populate a staff hours ... A: In that case I would change your table tblSalesHour SalesHourID (PK Autonumber) LowValue HiValue ... | |
| Lookup a value in Access 2003 | 5/2/2011 |
| Q: I have a sales total for the week. I need this to look up and automatically populate a staff hours ... A: Are there only 3 values for hours? If so I would do this with a nested IIF: OPHours: IIF([sales] ... | |
| MS Access 2010 Date Format | 4/29/2011 |
| Q: I use MS Office 2010. When I import data from MS Excel to Access all data are OK excepts the column ... A: The format is just how the date is displayed. A Date/Time datatype is stored as a double precision ... | |
| Crosstab Query in Access 2007 | 4/29/2011 |
| Q: Is there any way I can display blank column in a crosstab query? I have to make matrix report based ... A: You can use a table with records for each value and then join that into your source for the crosstab ... | |
| Subreports in Access | 4/28/2011 |
| Q: I have an unbound report: rptBiteReportingAll. This report contains two subreports: rptBiteReporting ... A: Add a Control on the form to enter the Year. Then set the criteria to: =Forms!formname!controlname ... | |
| Show daily bookings | 4/27/2011 |
| Q: ) I have a database that include a table with the fields Customer Name, People, Arrival Date, ... A: First NumDays in my code example is the number of days for a specific trip. So my idea was to enter ... | |
| Replication | 4/27/2011 |
| Q: I have a database that is on our network, this DB is used by multiple people on mulitpile work ... A: The way I would recreate it is to make structure only copies of your tables, link to the tables and ... | |
| MS ACCESS | 4/27/2011 |
| Q: I have 3 fields credit debit and balance credit - debit = balance But if credit is empty ... A: Balance is a calculated value and should not be stored. You can calculate it on the fly in queries. ... | |
| auto calculate using acess 2003 | 4/27/2011 |
| Q: I want the programme to autocalculate the time difference in minutes between 2 entries Many thanks ... A: I did. Set the Control source of the control where you want to show the difference to the expression ... | |
| Sending Emails | 4/27/2011 |
| Q: I have my club's members as an individual field in Access 2007 (about 200). I now need to send a ... A: Ok, that makes it easier. You can create a distribution list in Outlook and reference the name of ... | |
| Duplicate output destination | 4/26/2011 |
| Q: I'm trying to append a table to a master table in Access and would like to append three fields from ... A: Yes Create a column like: new field: field1 & " " & field2 & " " & field3 then select the field in ... | |
| auto calculate using acess 2003 | 4/26/2011 |
| Q: I want the programme to autocalculate the time difference in minutes between 2 entries Many thanks A: Use the DateDiff function. Calculations are done in forms and queries or reports. So, for example ... | |
| Sending Emails | 4/25/2011 |
| Q: I have my club's members as an individual field in Access 2007 (about 200). I now need to send a ... A: Look up the SendOBject command in VBA help. This can easily send an e-mail to a distribution list as ... | |
| Require a field on form | 4/25/2011 |
| Q: I'm still a novice at Access 2007, but I have an "employee" field on various forms that connect from ... A: Don't use a datasheet view. Use a Continuous form instead. Give you more control. You can use the ... | |
| Show daily bookings | 4/25/2011 |
| Q: ) I have a database that include a table with the fields Customer Name, People, Arrival Date, ... A: Well there are two ways. Instead of having Arrival and Departure dates, simply have an occupancy ... | |
| Access Query Criteria | 4/25/2011 |
| Q: Scott I am using office 2007. I have found a problem that my access query criteria based on a form ... A: Is your query criteria simply =[Forms]![Proposal Form]![Surname] I just test this and I had no ... | |
| Show daily bookings | 4/24/2011 |
| Q: ) I have a database that include a table with the fields Customer Name, People, Arrival Date, ... A: You will have to create a record for each day. You will either have to add another table or change ... | |
| Access | 4/23/2011 |
| Q: Scott My database form is using autonumber.I have noticed that when using a 'goto next record' ... A: The SOLE purpose of an Autonumber is to provide a unique identifier for a record. It should not be ... | |
| exporting exel data to Access | 4/20/2011 |
| Q: I have Office 2007 and am fairly proficient with Excel and writing code in Excel. I have a ... A: I'm not that good on Excel VBA. But the key here is Office Automation. You can kick off a session of ... | |
| Query | 4/20/2011 |
| Q: I have a query which allows the user to filter out only the students with the specific status they ... A: First there is no reason for a Group By. You aren't totaling anything. What you want to do is ... | |
| Date parameter query | 4/20/2011 |
| Q: I am using Access 2003. I have set up a report based on a query which contains a field "StudyDate". ... A: Simple answer is enter in the criteria row of the query for the date column: [Enter Date] When the ... | |
| Calculate Networkdays in Access 2007 | 4/19/2011 |
| Q: The Internet has the code below for calculating the NetWorkdays in Access 2007. Unfortunately, it ... A: Yes, by using the Step property. Add a DIM statement: Dim intStep As Integer Then, after ... | |
| forms | 4/19/2011 |
| Q: I have have 3 tables in my database. table 1 = supplier info, table 2 = rejects and table 3 = ... A: Sure, Create a Query that joins the three tables and add the columns you want to appear in your ... | |
| SUB QUERY | 4/18/2011 |
| Q: Sir, The sub query for medication list is working fine. it does return the most recent medication. I ... A: The problem is you are using the wrong subquery. You need to build this bit by bit. First you need ... | |
| SQL Query | 4/17/2011 |
| Q: I am using the following query to populate a report. The query is as follows SELECT ... A: Same answer. The TOP 1 of group query will do this. If you sort by date in descending order it will ... | |
| Parse .kml (xml) into a table | 4/17/2011 |
| Q: As KML is merely a form of XML I purchased MSOffice Pro 2010 with expectation I could load the many ... A: I'm not saying that KML wasn't derived from XML. It is, in fact a superset of XML. That doesn't mean ... | |
| SQL Query | 4/17/2011 |
| Q: I am using the following query to populate a report. The query is as follows SELECT ... A: Ok, that helps. What you need is a subquery. You need to do a Top 1 of group query to get the the ... | |
| Parse .kml (xml) into a table | 4/17/2011 |
| Q: As KML is merely a form of XML I purchased MSOffice Pro 2010 with expectation I could load the many ... A: Ok, first KML is not "merely a form of XML" any more than XML is a form of HTML. The similarity is ... | |
| Based on dropdown values I got records in subform but I want calculate time for subform result | 4/16/2011 |
| Q: I'm using MS-Access 2007.I was doing my college assignment in Ms-Access. Just want give quick ... A: Let me try to explain this a different way. There is no event or anything that is automatically ... | |
| Based on dropdown values I got records in subform but I want calculate time for subform result | 4/15/2011 |
| Q: I'm using MS-Access 2007.I was doing my college assignment in Ms-Access. Just want give quick ... A: The only way to do this is subjective. You can capture the time when you select a value in the ... | |
| Issue with users unable to open database. | 4/14/2011 |
| Q: I have a Access 2000 database that has about 30 users. Recently my company upgraded to Windows Vista ... A: First, go back to your PM and show him this communication. Tell him I said, that if he wants to ... | |
| Microsoft Form Error | 4/14/2011 |
| Q: I have a database set up and I just have on simple record in the Customer table (to at least have ... A: The On Load event is often used to set up a form in a certain way. Without knowing what code was in ... | |
| Form Operation | 4/14/2011 |
| Q: I have created a form that allows me to update a table. I would like to add a button that will allow ... A: Use the Command button wizard to create a button to open a form. The wizard will walk you through ... | |
| Checking multiple open reports | 4/13/2011 |
| Q: This is the code I tried, but won't work: Private Sub Report_Close() 'for report named "Overdue ... A: You can probably set your process flow to open only one report at a time. Assuming you are opening ... | |
| Move a borderless form | 4/12/2011 |
| Q: I'm fairly new at Access and coding, but I'm trying to figure out if there's a way to move ... A: "Bottom left corner" is hard to define. It can be in a different place on each user's computer. Top ... | |
| MS Access Signature | 4/12/2011 |
| Q: I want to use a touch screen for MS access database. I understand that a touch screen works like a ... A: Yes its possible. You will need a control on the form that will allow you to draw something. I'm not ... | |
| Automated check and send email as required | 4/11/2011 |
| Q: G'day, I am trying to build a group of queries and macros to check a table containing valuation ... A: In that case I would definitely be more inclined to use VBA code: IF DCount("*", "queryname") > 0 ... | |
| Issue with users unable to open database. | 4/11/2011 |
| Q: I have a Access 2000 database that has about 30 users. Recently my company upgraded to Windows Vista ... A: To run an Access application the user needs either a full Access license or the Runtime version of ... | |
| preventing duplicate values before saving record | 4/11/2011 |
| Q: I am trying to stop duplicate values from being created. I am in the same boat with another user ... A: The only syntax I wasn't sure of is the Runcommand. The first line (the IF) does 1 & 2 in one line. ... | |
| preventing duplicate values before saving record | 4/11/2011 |
| Q: I am trying to stop duplicate values from being created. I am in the same boat with another user ... A: First Social Security number is not the best choice for primary key. With the SSN privacy issues ... | |
| Creating a Form in Access 2010 | 4/11/2011 |
| Q: Greetings - thanks in advance for reading my question. I am using Access 2010 and have no ... A: You are working a bit backwards here. Look like you have transferred the table structure from the ... | |
| Automated check and send email as required | 4/11/2011 |
| Q: G'day, I am trying to build a group of queries and macros to check a table containing valuation ... A: Not hard to do. There are some questions though. Do you need to send one e-mail for all records or ... | |
| Bind the listbox to every employee | 4/11/2011 |
| Q: i have a listbox on a form but i do not know how to bind it to the particular employee, rather than ... A: Now you have me totally confused. A controlsource for a combobox is either the field in the Forms's ... | |
| Resize only certain row in datasheet (access 2007) | 4/10/2011 |
| Q: I have a main form and a sub form datasheet. The combobox on the main form will trigger and change ... A: Ok, First this has nothing to do with the way the form or datasheet is formatted. But you cannot use ... | |
| Some stuff abt changing quantity | 4/9/2011 |
| Q: i have problems with ms access and i'm wondering if you could help me. i have 3 tables, namely, ... A: You need to change your approach. Quantity on Hand (QOH) is a CALCULATED value and should not be ... | |
| Access query criteria | 4/8/2011 |
| Q: Scott Unfortunately I am unable to ask a futher follow up question so this is a new one on the same ... A: Yeah that is a drawback of this site. The message means the syntax is wrong. I may have had too ... | |
| Automating value in a text box | 4/7/2011 |
| Q: Sir, I need to automate a value in the textbox. The scenario is that a score is being calculated in ... A: This question makes me wonder about your table structure. I'd like to know more about what your ... | |
| Multipal Search using a Text Box and double form | 4/7/2011 |
| Q: I have been researching how to create a form such as the one in this youtube video: ... A: Not the way I would do it, but all you need to do is add Me.Requery in the After Update event of the ... | |
| Automating value in a text box | 4/7/2011 |
| Q: Sir, I need to automate a value in the textbox. The scenario is that a score is being calculated in ... A: You could use an IIF: =IIF([Score]>=10,"High",IIF([Score]>=5,"Medium","Low")) This is called a ... | |
| Help with query report creation | 4/7/2011 |
| Q: I want to run a query that sums the build times of parts in several systems. In a query named ... A: You are on the right track but on the side of the road ;) You cannot set the criteria in query to ... | |
| Access Query Criteria | 4/7/2011 |
| Q: Scott I am having difficulty using an IIf function query on a date field with a Where clause. I want ... A: Ok, so on Sept 2 2011, you will need it show a range from Sept 1, 2011 to Aug 31, 2012, but prior to ... | |
| MS ACCESS 2007 | 4/7/2011 |
| Q: Good afternoon can i ask a question regarding ms access 2007 because our supervisor in our ojt want ... A: You need to explain how you want to present the results of the search. Access has two features that ... | |
| Entity Relationship | 4/6/2011 |
| Q: I have following scenario and I am having difficulty to create relationship The company requires a ... A: This is actually pretty straight forward. tblEmployees: EmployeeID (PK Autonumber), FirstName, ... | |
| Access Query Criteria | 4/6/2011 |
| Q: Scott I am having difficulty using an IIf function query on a date field with a Where clause. I want ... A: Ahh, I see. Are you only dealing with the current and prior fiscal year at any one time? If so, ... | |
| Images | 4/6/2011 |
| Q: I have created a continuous form in access. The continuous form show per record different images. ... A: Why can't you set the Recordsource of the report? What error message is it giving you? How are you ... | |
| Password on a Tick Box? | 4/6/2011 |
| Q: In follow up to our last thread, can I just get it straight. I've created my tick box, I go to ... A: What did you name the Checkbox? That's what you need to put after the Me. The error means Access ... | |
| Access Query Criteria | 4/6/2011 |
| Q: Scott I am having difficulty using an IIf function query on a date field with a Where clause. I want ... A: Why not just: BETWEEN DateSerial(Year(Date())-1,9,1) And DateSerial(Year(Date()),8,31) This will ... | |
| Images | 4/6/2011 |
| Q: I have created a continuous form in access. The continuous form show per record different images. ... A: Because a continuous form is actually one form displayed many times. So you are setting the Picture ... | |
| Password on a Tick Box? | 4/6/2011 |
| Q: In follow up to our last thread, can I just get it straight. I've created my tick box, I go to ... A: Close. Do not erase the start and end lines that Access puts in for you. Just enter the code between ... | |
| database Security | 4/6/2011 |
| Q: Please Help me I want a way to protect the database so the user can not access to the Design view ... A: You didn't attach the screen shot correctly so I can't see the error message. But before you can ... | |
| Switchboard design | 4/6/2011 |
| Q: Access 2003. Intermediate user. I am trying to make my switchboard pages look different. I have the ... A: The On Current event is a property of the Form. So you have to select Form in the drop down list at ... | |
| Multipal Search using a Text Box and double form | 4/6/2011 |
| Q: I have been researching how to create a form such as the one in this youtube video: ... A: Frankly I would suggest learning how to use the filter by form feature. It turns any form into a ... | |
| Calculation / Sum Control Boxes | 4/5/2011 |
| Q: I found my 1st answer (How to add up 3 fields on a form) here. ... A: I'm not Bob, but I can certainly answer this question. The answer is you don't. As a general rule we ... | |
| Password on a Tick Box? | 4/5/2011 |
| Q: I've created a very simple database to raise and control purchase orders within my company. I've set ... A: Look at the code. Isn't the password being pulled from a table based on the Approver? So yes you ... | |
| Switchboard design | 4/5/2011 |
| Q: Access 2003. Intermediate user. I am trying to make my switchboard pages look different. I have the ... A: Well first you need to understand that a Switchboard created by the Switchboard manager is actually ... | |
| Bind the listbox to every employee | 4/5/2011 |
| Q: i have a listbox on a form but i do not know how to bind it to the particular employee, rather than ... A: The terminology you are using is slightly off. What you are talking about is FILTERING the listbox ... | |
| linking of queries | 4/4/2011 |
| Q: I HAVE RETRIEVED SOME DATA BY USING 5 QUERIES FOR DIFF.PERIOD. FOR EXAMPLE Q1 FOR 0 MONTH DATA, Q2 ... A: Like I said, the message is clear. The last SELECT (ABOVE 3 Years Details) has only 8 fields. Its ... | |
| database Security | 4/3/2011 |
| Q: Please Help me I want a way to protect the database so the user can not access to the Design view ... A: Convert your database to an MDE/ACCDE (depending on version) and deploy that to your users. The ... | |
| Multiple computers and 1 database | 4/2/2011 |
| Q: I have access 2003 and want to create a database where 14 computers have to access it. I have a ... A: Yes, by design and default Access is a multi-user platform. From a practical standpoint you NEED to ... | |
| Need help with a Query that won't sort | 4/2/2011 |
| Q: I have some code that depending on what was selected on a form, builds a query and then creates the ... A: Because reports don't carry over the sort from the query. You have to specify the sort in the ... | |
| Access VB | 4/1/2011 |
| Q: I have a combobox which as well as autoexpanded, dropsdown on change, to automatically give me a ... A: Hmmm, I would have used the Got Focus event. But I'm not able to duplicate this behavior. What ... | |
| hiding Sub report | 4/1/2011 |
| Q: Sir, I have a main report with many sub reports, in turn these sub reports have further sub reports. ... A: No, a Query is only a view on a table. So if the values don't exist in a table, or you can't use a ... | |
| hiding Sub report | 4/1/2011 |
| Q: Sir, I have a main report with many sub reports, in turn these sub reports have further sub reports. ... A: If you are using an unbound control, then you have two choices. Either you store the value somewhere ... | |
| Help with assigning form combobox value | 4/1/2011 |
| Q: I have a form that records Build times of a part. When I hit the "submit" button I want the form to ... A: Sorry, but I think you approach here is wrong. A part is a part. If you need to designate that a ... | |
| open form on selected record | 4/1/2011 |
| Q: I have two forms that i am working with CallCenterSearch_frm and EditCallCenter_frm. The ... A: Use the On Double Click event of the SUBFORM. DoCmd.OpenForm "EditCallCenter_frm",,,"[CaseNumber] ... | |
| Password on a Tick Box? | 4/1/2011 |
| Q: I've created a very simple database to raise and control purchase orders within my company. I've set ... A: I think you can. I think you are being intimidated by the code when it is really simple code. If ... | |
| Password on a Tick Box? | 4/1/2011 |
| Q: I've created a very simple database to raise and control purchase orders within my company. I've set ... A: Simple is good, but I'm a little concerned about your table structure. First, When your company ... | |
| linking of queries | 4/1/2011 |
| Q: I HAVE RETRIEVED SOME DATA BY USING 5 QUERIES FOR DIFF.PERIOD. FOR EXAMPLE Q1 FOR 0 MONTH DATA, Q2 ... A: Well I'd need to see the SQL for your UNION query. Sounds like a Union query is the answer, but the ... | |
| Please Reply me Immedietely,It is nice question for me | 3/31/2011 |
| Q: Sir, I have tow queries : 1. I want to create a fixed length report whithout header and footer in MS ... A: 1. What is the purpose of this report? If you need to export data for use in another program, then ... | |
| Blocking a field with gender | 3/31/2011 |
| Q: I got a healthcare database which is being developed in MS Access. My report is ready to be printed ... A: On the surface this is simple. In the On Format and On Print events of the band where the Smear test ... | |
| E-mail Form from MS Access | 3/30/2011 |
| Q: I have a form created in Microsoft access 2003 containing employee information. I would like to be ... A: I know you can do it if you have the full version of Acrobat. But I'm not an Acrobat expert so I ... | |
| two forms as control source | 3/29/2011 |
| Q: I'm just starting out with Access, so is it possible to use two forms as a control source? Such as: ... A: A controlsource tells Access where to pull the data from to display in a control. A controlsource ... | |
| E-mail Form from MS Access | 3/29/2011 |
| Q: I have a form created in Microsoft access 2003 containing employee information. I would like to be ... A: You need to upgrade to Office 2007 or 2010. This feature was first included with Access 2007, but ... | |
| Prevent editing of certain values in combo box lookup | 3/29/2011 |
| Q: Situation: The users of the database have a number of combo boxes for entering data into form ... A: Well frankly, when you have lookup tables entries should NEVER be deleted unless you have ... | |
| Using subdatasheets as a criteria | 3/29/2011 |
| Q: I have an add form with a "PartName" field and a "Aircraft System" field (among a couple others that ... A: What you are referring to is a standard technique called Synchronized or Cascading comboboxes. This ... | |
| Compress picture | 3/29/2011 |
| Q: Is it possible to compress a picture using vba? Kind regards, Erwin Maas A: WINZIP has a command line utility that can be used to ZIP a file. Access can then run a batch file ... | |
| Multiple duplicate entries | 3/29/2011 |
| Q: In my database I have a form that opens in add mode and adds the inputted data to the parent table. ... A: Yes you CAN do that, however, if you HAVE to do that it indicates your database is not designed ... | |
| how to make random number | 3/29/2011 |
| Q: I'm doing past exam paper system. I want to random number for exam paper. I have 3 combo box for ... A: I asked a question whihc you didn't answer. The code you are using will assign a random number to a ... | |
| Sequential File Numbers - Follow up | 3/28/2011 |
| Q: I maxed out the follow up questions! Last week, you instructed me to try this line of code: ? ... A: I think something is wierd with your keyboard that Access is not recognizing quote marks properly. ... | |
| Sequential File Numbers - Follow up | 3/28/2011 |
| Q: I maxed out the follow up questions! Last week, you instructed me to try this line of code: ? ... A: Hmm, the message indicates there is a missing comma or closing parentheses. But I don't see one. The ... | |
| Copy a Textbox Value | 3/28/2011 |
| Q: I have a form with two textbox: txtbox A hold a calculated result, I want use an even that will copy ... A: Again, you probably shouldn't be doing this. I'd like to understand why you are doing this. The ... | |
| Directory Dialog Box | 3/28/2011 |
| Q: I have a code to create a filename based on the date, checks if there's a file with that name in the ... A: I would use the DIR() function to check the existence of a file. Its simpler and works well. I would ... | |
| how to make random number | 3/28/2011 |
| Q: I'm doing past exam paper system. I want to random number for exam paper. I have 3 combo box for ... A: What is the purpose of this random number? If you need to generate the questionnum as a random ... | |
| Macro | 3/27/2011 |
| Q: i want to have a button that run a macro in a condition that if a field has a value of 3% it will ... A: Me is a shortcut that indicates the currently active form. It is a shortcut for Forms!formname. ... | |
| Macro | 3/27/2011 |
| Q: i want to have a button that run a macro in a condition that if a field has a value of 3% it will ... A: I do not recommend using macros. Until Access 2010 they were very limiting. Even with 2010 they are ... | |
| if and else macro | 3/26/2011 |
| Q: i want to run a macro if the value of my field gives >10 and if false run a macro if the value of a ... A: I would not recommend a macro. This is simple in VBA: IF Me.control > 10 Then Do this else Do ... | |
| method 'Picture' of object '_Image' failed | 3/26/2011 |
| Q: Basically i have a record set and i have added cmdbuttons for scrolling between records. Every ... A: If you are going to store the full path, then why bother, use a relative path. The need to parse out ... | |
| Max Indexes and duplication of designations | 3/25/2011 |
| Q: I currently have 2 problems, it seems that i have overused the so called indexes on a table, and the ... A: Yep you have violated 1NF and that's a big part of your problem. Access automatically creates an ... | |
| method 'Picture' of object '_Image' failed | 3/25/2011 |
| Q: Basically i have a record set and i have added cmdbuttons for scrolling between records. Every ... A: I think the problem may be in generating the path. How is strImagePath assigned? What is stored in ... | |
| Copy a Textbox Value | 3/24/2011 |
| Q: I have a form with two textbox: txtbox A hold a calculated result, I want use an even that will copy ... A: Ok, I see what you are doing, but you shouldn't be. An Oustanding Debit amount should be calculated ... | |
| date difference - working days | 3/24/2011 |
| Q: I have a table to record employee absence records. I have an absence start date field [dtmDateFirst] ... A: No simple formula, but one of the functions found here: ... | |
| using barcodein ms access with multiple quantities | 3/23/2011 |
| Q: i am using an access database to mantain inventory with a purchase main form and a sale main form ... A: Ok, you have two issues here. Issue 1 is to print the barcodes, Issue 2 is to print multiples. So ... | |
| using barcodein ms access with multiple quantities | 3/23/2011 |
| Q: i am using an access database to mantain inventory with a purchase main form and a sale main form ... A: You asked me a question. I can answer your question and give you the solution you need, but I need ... | |
| using barcodein ms access with multiple quantities | 3/23/2011 |
| Q: i am using an access database to mantain inventory with a purchase main form and a sale main form ... A: What type of labels are you printing on? For example are you printing on a sheet of like 30 labels ... | |
| Help with Report sorting and null comand | 3/23/2011 |
| Q: I have a report that is sorted based on a selection of a part in a combobox. It displays all of the ... A: What do you mean by your report page? Are we talking about the Database Window? There aren't ... | |
| Convert columns to rows: Query | 3/23/2011 |
| Q: I am here with another question for you. I'm extremely happy about your response to my previous ... A: Sorry, I misunderstood, I thought you were reporting on existing data not trying to create a data ... | |
| To display filtered records in a listbox | 3/23/2011 |
| Q: I have 2nd form(frmEmployeesHIPO) which displays filtered records(from table Employees) from the ... A: How are you calling this function and from where? The Me qualifier is a shortcut for the active ... | |
| access invoice design | 3/23/2011 |
| Q: I am running windows Xp with access 2010. I have 10 tables that are related in various ways to track ... A: I need more info on your structure, but the first thing is to create a query that returns the ... | |
| method 'Picture' of object '_Image' failed | 3/23/2011 |
| Q: Basically i have a record set and i have added cmdbuttons for scrolling between records. Every ... A: How are displaying the image. The way I normally do it is with an unbound image control and I set ... | |
| Importing an Excel Spread Sheet into Access | 3/22/2011 |
| Q: Sir, I am working on a project at work and I am stuck. I have an excel spreadsheet that tracks ... A: The tables you list are mostly supporting info. You are missing one important table, an incident ... | |
| Convert columns to rows: Query | 3/22/2011 |
| Q: I am here with another question for you. I'm extremely happy about your response to my previous ... A: Use a Crosstab query. The Crosstab query wizard will walk you through the process. Appname goes to ... | |
| hiding Sub report | 3/21/2011 |
| Q: Sir, I have a main report with many sub reports, in turn these sub reports have further sub reports. ... A: The first thing to try is the On No Data event of the report. In that event try setting the Visible ... | |
| Syntax Error | 3/21/2011 |
| Q: Could you please take a look at my code and help me figure out where is the error, it has bugged me ... A: The most obvious thing that is wrong is that you are trying to supply a WHERE clause to an OpenForm ... | |
| Access | 3/21/2011 |
| Q: Scottgem, thank you very much in advance for your help, here is what i want to do, i think its ... A: Start with a blank Access database and crate your table. Make sure the fields are the right ... | |
| Query critera for next month. | 3/20/2011 |
| Q: Hoping you could help. I currently have a field set in date format, on the form the format is ... A: I would add one column to your query: Mth: Month(datefield) I would set the criteria for that ... | |
| Dcount | 3/20/2011 |
| Q: I am building a simple employee accountibility database. It has one datasheet with five fields such ... A: The better way would be to set grouping on your report. Use the wizard to create the report then ... | |
| Updating a text box value after editing a related form | 3/19/2011 |
| Q: Here's the set-up: MainTable contains addresses. SmallTable contains details of addresses that have ... A: It's actually not Access. Access uses fairly standard SQL rules when building WHERE clauses. You ... | |
| Sequential File Numbers | 3/19/2011 |
| Q: Scott, I have been trying to use the directions on your blog to have my database generate automatic ... A: Hmmm, Nothing that leaps to mind. Try this: In the Immediate window type ? Year(Date()) See if ... | |
| Printing individually a report with different forms | 3/19/2011 |
| Q: I have a report which got included 3 forms, and would like to print each form in different pages. ... A: First, forms and reports are different objects. We don't print forms we create reports to print ... | |
| Can't get subform continuous output | 3/18/2011 |
| Q: If got a subform thats set to default view continuous and it displays continous when it is run ... A: A subform is embedded within a main form. As such its a control on that form. So check the control's ... | |
| Sequential File Numbers | 3/18/2011 |
| Q: Scott, I have been trying to use the directions on your blog to have my database generate automatic ... A: Ok, what references ARE checked. Sorry I didn't specify that. What's entered into the Sequence ... | |
| Sequential File Numbers | 3/18/2011 |
| Q: Scott, I have been trying to use the directions on your blog to have my database generate automatic ... A: The error you are getting indicates you have a missing reference. In the VBE go to Tools>References ... | |
| Help with Report sorting and null comand | 3/18/2011 |
| Q: I have a report that is sorted based on a selection of a part in a combobox. It displays all of the ... A: I would not do this on Report Load. I would do this where you call the report. If you are triggering ... | |
| Using Access 2007 to open Outlook | 3/18/2011 |
| Q: I need to automatically open an new email in MS after clicking the email link, then have it ... A: Take a look at the SendObject command. This can be used to create an e-mail and may be sufficient ... | |
| Inventory Database | 3/17/2011 |
| Q: you just helped me with my search box dilemma that works almost exactly as I envisioned with the ... A: strTablename = Nz(DLookup("[Source]", "Query1", "[Item] = '" & Me.Text2563 & "'"),"") If ... | |
| Inventory Database | 3/17/2011 |
| Q: you just helped me with my search box dilemma that works almost exactly as I envisioned with the ... A: What record do they need to go to? Is it the item record or something else? If its the item, just ... | |
| Access forms | 3/17/2011 |
| Q: I am using Access 2010 MS OFfice Professional 64 bit version I created a form and I incorporated a ... A: You can't maximize a tab control. A Tab control is not a form that has Minimize of Maximize ... | |
| Inventory DB | 3/17/2011 |
| Q: We currently have a database that tracks the amount of t-shirts, computers, books, and office ... A: Yep and its partially my fault. I left out something. Change this line to: strTablename = ... | |
| Access 2007 Combo Box not saving to control source | 3/17/2011 |
| Q: I have a combo box that I created both manually and later via the wizard, which is populated by the ... A: Ok, If I'm getting this, you can streamline this. The RowSource of the Combobox should be something ... | |
| open forms with afrer update event of a combobox | 3/17/2011 |
| Q: I have a combo box with 8 values on a result sheet form that is linked to a table result sheets ... A: If you are opening a form to the same record that is currently being displayed then it is probable ... | |
| Inventory DB | 3/16/2011 |
| Q: We currently have a database that tracks the amount of t-shirts, computers, books, and office ... A: First to answer the previous question. You do have to enter a UNION query in SQL view. You can't do ... | |
| Access 2007 Combo Box not saving to control source | 3/16/2011 |
| Q: I have a combo box that I created both manually and later via the wizard, which is populated by the ... A: A control cannot be bound to a table, it can only be bound to a field in the Recordsource of the ... | |
| Inventory DB | 3/16/2011 |
| Q: We currently have a database that tracks the amount of t-shirts, computers, books, and office ... A: I would have two tables, one for lendable items and one for consumable items. I'm assuming here ... | |
| Searching Records | 3/15/2011 |
| Q: I am using Access 2010 to keep track of years worth of records (legal, not vinyl). I will not be ... A: Frankly I train my uses to use the Filter by Form feature. Since this feature allows users to search ... | |
| Display data of a field of same records of table in a field in query like crosstab | 3/15/2011 |
| Q: I am just like a beginner in ACCESS. 1. I have database related to MOTOR in MS ACCESS (2003) as ... A: You can use the Function found here: http://www.mvps.org/access/modules/mdl0008.htm to do this. You ... | |
| ACCESS 2003 | 3/15/2011 |
| Q: I have downloaded the Microsoft office 2003 from the net,(since i did not have any original cd) but ... A: As far as I know Office 2003 is no longer available since Office is now up to version 2010. You ... | |
| ACCESS 2003 | 3/15/2011 |
| Q: I have downloaded the Microsoft office 2003 from the net,(since i did not have any original cd) but ... A: Office 2003 has been sunsetted and is no longer available for sale. You may be able to find a ... | |
| Sequential Numbering | 3/15/2011 |
| Q: We both thought we were done - not so much. When I closed the database and then reopened it later, I ... A: Who is making these decisions? Because I doubt if they are IT people or at least not developers. The ... | |
| Display Volunteers | 3/14/2011 |
| Q: Create a report to show office staff who to expect:when they should arrive, which class they will be ... A: I would use a Main/sub form. Create your main form bound to the Availability table. Then a sub form ... | |
| database reworking-Previous question about report template | 3/14/2011 |
| Q: I just got your union query suggestion and It works with a slight problem though. When I create the ... A: Then you are going to have to combine them into one table. You can use the Union query that worked ... | |
| Create a report | 3/13/2011 |
| Q: Create a report to show office staff who to expect:when they should arrive, which class they will be ... A: Ok it looks like you need to to join the Event, EventVolunteer and Volunteer tables. EventID to ... | |
| Sequential Numbering | 3/13/2011 |
| Q: We both thought we were done - not so much. When I closed the database and then reopened it later, I ... A: Does it give you a Debug choice? If so, on what line. Because nothing in the code I gave you should ... | |
| Sequential Numbering continued | 3/12/2011 |
| Q: 3 controls: name: Table Number and the control: Table Number name: Bidder Number and the control: ... A: It is so nice when the light bulb goes off, isn't it? I'm also glad you weren't too put off by my ... | |
| Sequential Numbering continued | 3/12/2011 |
| Q: 3 controls: name: Table Number and the control: Table Number name: Bidder Number and the control: ... A: Actually you ARE there. Add another record for the same table number. Then add a record for a ... | |
| Sequential Numbering continued | 3/12/2011 |
| Q: 3 controls: name: Table Number and the control: Table Number name: Bidder Number and the control: ... A: OK, I think I spotted the problem. First, let me say it is highly recommended that you do NOT use ... | |
| Sequential Numbering | 3/12/2011 |
| Q: Scott: I am having a problem translating the code to my needs: Me.txtSequence = ... A: I DO think you are trying. But I think you are getting intimidated and not trying to think things ... | |
| Sequential Numbering | 3/12/2011 |
| Q: Scott: I am having a problem translating the code to my needs: Me.txtSequence = ... A: Ok, You need to think this through a bit. Does it make sense to assign a value to Bidder_Number ... | |
| Sequential Numbering | 3/12/2011 |
| Q: Scott: I am having a problem translating the code to my needs: Me.txtSequence = ... A: Well I had already told you that you would substitute BidderNumber for Sequence. But you need to ... | |
| database reworking-Previous question about report template | 3/11/2011 |
| Q: I just got your union query suggestion and It works with a slight problem though. When I create the ... A: OK, not a big issue. The question, however, again becomes what the EXACT structure of your table ... | |
| Previous question about report template | 3/11/2011 |
| Q: I think I have come up with a way to create a new report on demand without having to make a query of ... A: It should not be difficult to fix this. I assume all the parts tables have the exact same structure? ... | |
| Access 2007 AR Aging Query | 3/11/2011 |
| Q: my db has customer with charges + tax - payment = total with a date it was posted. (tblcharges) ... A: One thing has nothing to do with the other. Maybe you better explain what it is you are really ... | |
| Access 2007 AR Aging Query | 3/11/2011 |
| Q: my db has customer with charges + tax - payment = total with a date it was posted. (tblcharges) ... A: Generally Aging is calculated from some date to the current date. Whether its the date the charges ... | |
| Access 2007 Form IIF | 3/11/2011 |
| Q: I have a form with a Payment Method combo field "Combo44" and a Checkbox field "Guest". I'm trying ... A: First getting back to the original question. I'm not sure how you can work that in a query and have ... | |
| Previous question about report template | 3/11/2011 |
| Q: I think I have come up with a way to create a new report on demand without having to make a query of ... A: "For each part there is a table that contains the fields ID, QA time, date_completed, and ... | |
| Previous question about report template | 3/11/2011 |
| Q: I think I have come up with a way to create a new report on demand without having to make a query of ... A: I think you are misunderstanding (or maybe misstating) how reports work. A user doesn't "create" a ... | |
| Report Template | 3/11/2011 |
| Q: I was wondering if there is a way to create a Report template. I currently have around 100 different ... A: I just posted another answer to you whihc bears on the problems listed above. You can answer in the ... | |
| Report Template | 3/11/2011 |
| Q: I was wondering if there is a way to create a Report template. I currently have around 100 different ... A: Use the Report wizard to create your report, it does all the heavy lifting for you. You will ... | |
| Access 2007 Form IIF | 3/10/2011 |
| Q: I have a form with a Payment Method combo field "Combo44" and a Checkbox field "Guest". I'm trying ... A: This is pretty standard code so it will work, but the reason its not working is probably because you ... | |
| Access 2007 Form IIF | 3/10/2011 |
| Q: I have a form with a Payment Method combo field "Combo44" and a Checkbox field "Guest". I'm trying ... A: You don't want an IIF. In the After Update event of the combo use the Code Builder to enter code ... | |
| Report Template | 3/10/2011 |
| Q: I was wondering if there is a way to create a Report template. I currently have around 100 different ... A: Sure, create your report, group it by part and show the fields in the detail. When you want to run ... | |
| Dynamically Updating Acess | 3/10/2011 |
| Q: Is there an efficient way to update an access database? I'm using Access 2007. I have a relational ... A: Take a look at MZ Tools (http://www.mztools.com/index.aspx) and Rick Fisher's Find and Replace tool ... | |
| Passwords And Hyperlinks | 3/10/2011 |
| Q: Is it possible to create a on click event for a button, ask for a password if the password is ... A: If InputBox("password") = "thepassword" then Application.FollowHyperlink ... | |
| Find data | 3/10/2011 |
| Q: i need to split string and number.For Example in my table i have like this data ... A: This can be done with an Make table query. You first do a Select query joining the two tables on the ... | |
| Previous question about textbox into combobox | 3/10/2011 |
| Q: I have taken your advice on the method in which to solve my problem. But the code that you helped me ... A: Sorry, my bad, I left out something. The criteria should be: LIKE "*" & Forms!form1!entertxt & "*" ... | |
| Unable to navigate records after search | 3/10/2011 |
| Q: Im having a problem after searching for a certain record. I am unable to browse the other records ... A: The problem is that you are filtering the form for the EmployeeID selected on the search form. A ... | |
| control duplicates in multiple fields | 3/9/2011 |
| Q: I have 2 tables: table 1. tblItem: itemID(AutoNumber)(PK) boxnumber boxrow boxcolumn table 2. ... A: Create a multi-field unique index on the combination of the three fields. For details, look up ... | |
| Previous question about textbox into combobox | 3/9/2011 |
| Q: I have taken your advice on the method in which to solve my problem. But the code that you helped me ... A: Yes, create a new Query in Query Design mode. No, do not copy and paste, you have to enter the ... | |
| Previous question about textbox into combobox | 3/9/2011 |
| Q: I have taken your advice on the method in which to solve my problem. But the code that you helped me ... A: Wow, I encountered the same error and I couldn't figure it out. But I did get a workaround. Create a ... | |
| Combo box modification / help | 3/9/2011 |
| Q: I have made a database that keeps track of assembly times of about 100 parts. I have a combobox on a ... A: This should be working. It will do nothing after you enter in the Textbox, but when you pull down ... | |
| Combo box modification / help | 3/9/2011 |
| Q: I have made a database that keeps track of assembly times of about 100 parts. I have a combobox on a ... A: To add to my other answer. There is no way you can do what you want. You can enter a value to use to ... | |
| Access 2007 AR Aging Query | 3/9/2011 |
| Q: my db has customer with charges + tax - payment = total with a date it was posted. (tblcharges) ... A: Do you also have a Paid date? Generally you would add a column something like this: Aging: ... | |
| Combo box modification / help | 3/8/2011 |
| Q: I have made a database that keeps track of assembly times of about 100 parts. I have a combobox on a ... A: Name is a reserved word in Access and shouldn't be used for object names. That could be causing the ... | |
| filtering record useing quirry | 3/8/2011 |
| Q: i created a table with 10 columns and put some data in 250 rows regarding posting and transfer of ... A: I am assuming then, that you have a field that indicates the location. So you set the criteria for ... | |
| Combo box modification / help | 3/8/2011 |
| Q: I have made a database that keeps track of assembly times of about 100 parts. I have a combobox on a ... A: Yes they would type in the text box, but they would not be required to. If they want to use the Auto ... | |
| When to send e-mail | 3/8/2011 |
| Q: Now I want this notification to be sent when the user closes the form which is no problem. If the ... A: Try using the Form's After Update event. This will fire when the record is updated but before focus ... | |
| Combo box modification / help | 3/8/2011 |
| Q: I have made a database that keeps track of assembly times of about 100 parts. I have a combobox on a ... A: You might be able to do this in the On change event, but it would be very complex and there is an ... | |
| Split String And Number | 3/8/2011 |
| Q: i need to split string and number.For Example in my table i have like this data ... A: Since there is no pattern other than the when the number starts that's where you need to stop, then ... | |
| Email report to multiple recipients | 3/7/2011 |
| Q: I have it working with a minor exception. When the code is fired the e-mail is displayed and I have ... A: The next suggestion I was going to make was to specify DAO for the Database and Recordset DIM ... | |
| Can i make exe file of access | 3/6/2011 |
| Q: ANSWER: No you cannot make an EXE from Access. However, there are ways to hide the Access window ... A: Its still not clear. Access is a database, you put in as many records as you need to. If you create ... | |
| Macro/Module VB Error | 3/4/2011 |
| Q: I am using an autoexe macro to run some processes include a module. Everything works fine, however, ... A: OK, I understand doing the screen shot for the macro, but the code can just be copied and pasted. ... | |
| Access 2007 & time duration over 24 hours | 3/4/2011 |
| Q: I have read the previous postings regarding how to make time durations in excess of 24 hours display ... A: I wouldn't try to to display time as a time value for graphing like that. In a graph you want to ... | |
| Access 2007 Query | 3/4/2011 |
| Q: I have a calculated field in a query: Reservations Balance Due: [Reservations Amount ... A: Try: Reservations Balance Due: Format(([Reservations Number of Tickets]*150)-[Reservations Amount ... | |
| Looking for a reliable ebook | 3/4/2011 |
| Q: I have been looking for an ebook covering the topics of automating Access 2007 using VBA. I ... A: My book is available as an ebook. If you are looking for a free ebook, I'm not sure of any. ... | |
| Access 2007 Query | 3/3/2011 |
| Q: I have a calculated field in a query: Reservations Balance Due: [Reservations Amount ... A: It could. Try changing the expression to: Reservations Balance Due: Format(([Reservations Number of ... | |
| Access 2007 Query | 3/3/2011 |
| Q: I have a calculated field in a query: Reservations Balance Due: [Reservations Amount ... A: Are the two fields currency data types? Putting 0 in the criteria for that column should work. ... | |
| Access 2007 | 3/2/2011 |
| Q: I am struggling with creating a report in Access 2007. My database is a fleet maintenance DB. Each ... A: First, You should have one field for the date You can standardize on either the first (easier) or ... | |
| convert rows to columns in access | 3/1/2011 |
| Q: This is the reverse form of a question you answered through this website. I have a qery like this ... A: Not a crosstab, but a UNION query. SELECT Forms, "Step1" AS Steps, Step1 AS Dates FROM table ORDER ... | |
| Replica vs. Design Master | 2/28/2011 |
| Q: I am starting a new job and would like to bring over an Access DB that I created. (I had set it up ... A: Do you really need to use a Design master? I've rarely worked with Replication so I'm not sure how ... | |
| unbound | 2/28/2011 |
| Q: I am trying to connect unbound check boxes and option boxes to a report. As they are unbound so ... A: The form will need to remain open while the report prints. From there you set the ControlSource of ... | |
| Table Integrity Violations | 2/26/2011 |
| Q: I am working on a database with 2 tables – Contacts and Comments. I want to capture the comments my ... A: I think the problem is in your SQL Statement. The SQL Statement you provided inserts the text ... | |
| Can i make exe file of access | 2/25/2011 |
| Q: Thank you in advance A: No you cannot make an EXE from Access. However, there are ways to hide the Access window depending ... | |
| Table/Query design | 2/25/2011 |
| Q: I have 2 tables. Table 1: RoomNo Module A2611 5 A2612 3 A2610 2 Table 2: RoomNo ... A: You can't do this in a query, but you can in a report. You join the two tables on Room No. You will ... | |
| combo box help | 2/25/2011 |
| Q: I have an Unbound combo box to help users choose items faster and take them to the record. Problem ... A: OK, if you want the category name to appear when you drop down the list, you need to add the ... | |
| Access-Records Deleted | 2/24/2011 |
| Q: Never mind Scott. I found the manager. I tried it and it didn't work. I take it that's pretty ... A: No, that's why I would need to see the database. You might mention to your manager that I'm a ... | |
| Access-Records Deleted | 2/24/2011 |
| Q: Never mind Scott. I found the manager. I tried it and it didn't work. I take it that's pretty ... A: I'm not sure if this is corruption. I think some setting has been changed causing the problem, but I ... | |
| Access report | 2/24/2011 |
| Q: I have an access database where users have been inputting data into forms which loads into the ... A: As I thought, your database is not normalized properly. What you should have is a child table like ... | |
| Access 7 Creating Group Options | 2/24/2011 |
| Q: I have researched establishing of the group option in Access 7 and according to all the information, ... A: "the form's record source is a query." Is the form's recordsource a multi-table query? Can you edit ... | |
| Access report | 2/24/2011 |
| Q: I have an access database where users have been inputting data into forms which loads into the ... A: Why would you have 12 different Comments boxes? This gives me concern that your data base is not ... | |
| Access-Deleted Records | 2/24/2011 |
| Q: In response to your question, what's popping up is a dialog box that specifically says "Record is ... A: Hmm, This is a hard one. We can proceed in a couple of ways. The first suggestion is to make a copy ... | |
| Access-Deleted Records | 2/24/2011 |
| Q: In response to your question, what's popping up is a dialog box that specifically says "Record is ... A: Whoops, sorry, The VBE is the Visual Basic Environment. Its where you do any of your VB coding. You ... | |
| Access-Deleted Records | 2/24/2011 |
| Q: In response to your question, what's popping up is a dialog box that specifically says "Record is ... A: I have feeling that's something in your code because I've never seen a message like that. Open the ... | |
| Login form | 2/24/2011 |
| Q: Am running the code below to create user login form using Access 2007 front end and SQL2005 as the ... A: What line of code is throwing the error? There still seems to be something missing here. As you ... | |
| Update multiple tables | 2/24/2011 |
| Q: I am in the US Army and have created a complex personnel database tracking everything from simple ... A: Yep In the After Insert event of the Main form add code like: CurrentDB.Execute "INSERT INTO ... | |
| Access-Records Deleted | 2/23/2011 |
| Q: I've spoken with you before regarding Access. You helped me create a split database. Things have ... A: Hmmm, Are they getting a message box saying this or does the form open with Deleted in the controls. ... | |
| Report / Query | 2/23/2011 |
| Q: Following up on your response: I think you have made this a little more complicated than it needs ... A: You can just create a query on the Recovery Expense table and have an unbound combo oin the header ... | |
| Access-Records Deleted | 2/23/2011 |
| Q: I've spoken with you before regarding Access. You helped me create a split database. Things have ... A: What might be happening is someone is working on a record and someone else deletes that record while ... | |
| MS Access Query Expresssion | 2/23/2011 |
| Q: I am trying to write a complex expression that will give me the results of Null is equal to "N" 0 is ... A: First, is both System_Contract and qry_Prior_YR_TotalValue in the same query? Are they linked in an ... | |
| Login form | 2/23/2011 |
| Q: Am running the code below to create user login form using Access 2007 front end and SQL2005 as the ... A: First, it would help if you indicated the line throwing the error. A data mismatch error means you ... | |
| Insert Into another form | 2/22/2011 |
| Q: I'm back for more help. Previously you helped me with my purchasing database and setting up a ... A: Ok, So you are not inserting a record, you are populating a control on a form. Totally different ... | |
| Statements | 2/22/2011 |
| Q: I am creating A/R statements using MS Access 2003. I have "days out" and the $ value of the invoice. ... A: I prefer to do my calcs in a query. However, if these amounts are specific to the Invoice and you ... | |
| Report / Query | 2/22/2011 |
| Q: Following up on your response: I think you have made this a little more complicated than it needs ... A: Is your form bound to the query? You shouldn't be using a multi-table query as the Recordsource for ... | |
| Email Report | 2/22/2011 |
| Q: Access 2002 on Windows XP I just sent a follow up question. I have played with the code a little ... A: If its highlighting rs = Nothing, that line is wrong is should be SET rs = Nothing But then the ... | |
| Email Report | 2/22/2011 |
| Q: Access 2002 on Windows XP I just sent a follow up question. I have played with the code a little ... A: Try it this way: Set rs = db.OpenRecordset("SELECT EMail FROM qryEMailList WHERE [Jobs] = '" & ... | |
| Insert Into another form | 2/22/2011 |
| Q: I'm back for more help. Previously you helped me with my purchasing database and setting up a ... A: A form is just a view on a table, you don't insert data into a form, you insert the data into the ... | |
| Email from Access Program | 2/22/2011 |
| Q: Access 2002 on Windows XP. I am trying to get the program you have been helping me with to send an ... A: Try this way: Set rs = db.OpenRecordset("SELECT Email FROM table WHERE [JobNumber] = " & ... | |
| Email from Access Program | 2/22/2011 |
| Q: Access 2002 on Windows XP. I am trying to get the program you have been helping me with to send an ... A: The first 2 issues are probably a reference issues. What version of Access are you using. You may ... | |
| Pictures in form | 2/22/2011 |
| Q: I was wandering if it is possible to show multiple pictures under each record. The pictures are in ... A: You need to add a subtable with the path to the pics tblPictures PictureID (PK Autonumber) ParentID ... | |
| Database solutions | 2/22/2011 |
| Q: We have 3 times entities (companies) that are merging into one and the main database is Single ... A: To be honest, I'm not clear on your structure. Are you saying you have three separate databases or ... | |
| Email from Access Program | 2/21/2011 |
| Q: Access 2002 on Windows XP. I am trying to get the program you have been helping me with to send an ... A: The first part of the code should create a comma separated list of all the emails in the query. You ... | |
| Subquery for selecting TOP n records within grouped data | 2/21/2011 |
| Q: Any response is much appreciated. What I am after is: -the fourth chronological date (and the rest ... A: Try this: SELECT [2010 chicks rung].primary_key, [2010 chicks rung].bird_id, [2010 chicks ... | |
| Access 7 Creating Group Options | 2/21/2011 |
| Q: I have researched establishing of the group option in Access 7 and according to all the information, ... A: What is the datatype of RecMethod? Since an Option group returns an Integer value it should be set ... | |
| Split String And Number | 2/20/2011 |
| Q: i need to split string and number.For Example in my table i have like this data ... A: Is the pattern where the last 2 characters are always the number? If so, ... | |
| follow-up | 2/20/2011 |
| Q: I hit the limit on follow-ups and had to start again. Thanks for sticking with me on this. The way ... A: First, names are not a good primary key. Names are not unique and text fields are slower in joins. I ... | |
| merging records | 2/19/2011 |
| Q: Running windows 7 Home Premium, MS Access 2007. I have a table of student data called 'draft'. I ... A: Joining the two tables on StudentID should have eliminated this problem. The update should only have ... | |
| Using the Tag property as a Rowsource | 2/19/2011 |
| Q: This question is about using the Tag property (not SmartTags) in controls on a data entry form to ... A: 1) I actually assumed there was more than one form. But I did assume that you would want to do the ... | |
| Using the Tag property as a Rowsource | 2/18/2011 |
| Q: This question is about using the Tag property (not SmartTags) in controls on a data entry form to ... A: I can see a way to make this work, BUT not the way you are thinking. You are trying to make it too ... | |
| Subquery for selecting TOP n records within grouped data | 2/18/2011 |
| Q: Any response is much appreciated. What I am after is: -the fourth chronological date (and the rest ... A: Part of your problem could be the fact that you are using reserved words as field names. Date and ... | |
| merging records | 2/18/2011 |
| Q: Running windows 7 Home Premium, MS Access 2007. I have a table of student data called 'draft'. I ... A: My understanding here was that all the fields contained data. Either they contained the same data or ... | |
| Form VBA to check textbox values with formula? | 2/17/2011 |
| Q: I have a form, MoneyCounting, where we track donations during our money counting process. I've ... A: Not only possible but easy. Dim curSumCash as Currency curSumCash = Me.OneDollar + (Me.Quarters * ... | |
| Access 2007/subforms | 2/17/2011 |
| Q: I have an Access db and I have a form that have a subform in it. My question is, is there anyway to ... A: First, you don't have fields on a form (or subform). You have controls that may or may not be bound ... | |
| sum fields on a form | 2/17/2011 |
| Q: Hope all is well..... I have a few fields in my form as follows: Fine 1 Amount,Fine 2 Amount,Fine 3 ... A: Ok, in that case you need two tables. A table for the ticket and another, child table, for the ... | |
| Expression builder training / tutorial | 2/17/2011 |
| Q: Me again.... Do you know where I can sharpen up my skills on expression builder for Access 2007? ... A: The query design mode is actually very simple. 1) add the tables you need to include in the query ... | |
| Expression builder training / tutorial | 2/17/2011 |
| Q: Me again.... Do you know where I can sharpen up my skills on expression builder for Access 2007? ... A: Sorry I don't know of any tutorial that specifically deals with expression builder. Are you having ... | |
| sum fields on a form | 2/17/2011 |
| Q: Hope all is well..... I have a few fields in my form as follows: Fine 1 Amount,Fine 2 Amount,Fine 3 ... A: You have two problems here. First your database does not appear to be normalized properly. Second as ... | |
| Report | 2/16/2011 |
| Q: Good afternoon Scott: I have a Timesheet form with record source qryTimesheetextended. With a ... A: I think you have made this a little more complicated than it needs to be. tblSRMJobList should be ... | |
| Number generator in form | 2/16/2011 |
| Q: Back in 09, you assisted me with a problem I was having. I have a split database, front end has a ... A: Is MaAssignDat a Date/time datatype? It doesn't matter how its formated as long as it is Date/Time. ... | |
| Report | 2/15/2011 |
| Q: Good afternoon Scott: I have a Timesheet form with record source qryTimesheetextended. With a ... A: I always try to name my Foreign Keys the same as the corresponding Primary Key. So its clear what ... | |
| Importing | 2/15/2011 |
| Q: Scott: I am creating a new database and I already have in a linked table with the fields (JobNo & ... A: If the Jobno in the spreadsheet is related to your existing table. Then you do need the JobNo as a ... | |
| How to change column in query at runtime | 2/13/2011 |
| Q: I need to update data in a Table through query and want to select the column to be updated at ... A: Let me get this straight. You have a table named Absent. This table contains a record of when an ... | |
| Number generator in form | 2/11/2011 |
| Q: Back in 09, you assisted me with a problem I was having. I have a split database, front end has a ... A: What it looks like happened here is, when you inserted the additional 2010 numbers you didn't change ... | |
| MS Access 2007 using Dlookup with multiple criteria | 2/11/2011 |
| Q: I am trying to use and unbound form text box to retrieve a single record with multipe criteria. For ... A: First Date is a reserved word in Access and shouldn't be used for Object names. Try it this way: ... | |
| Stock Control System | 2/10/2011 |
| Q: I'm trying to develop the "Inventory Management Database" template from the Microsoft website. It ... A: What help are you looking for? This site is best suited for asking and getting answers to specific ... | |
| Duplicate Name Error | 2/10/2011 |
| Q: Good morning: I have an employee form. I would like a message to pop up saying possible duplicate. ... A: Changing the Field name back didn't fix it? it should have. You might try reentering the the ... | |
| Inserting a SUM function into a table's cell? | 2/9/2011 |
| Q: Hey, I have a master table, "Participants", that tracks each participant's personal information, ... A: Unless you are using Access 2010 you don't. Even if you are using Access 2010, its not a good idea. ... | |
| Summing calculations in reports / queries | 2/9/2011 |
| Q: I am using an IIf expression to do a calculation, which works well; however when I create a report ... A: A calculated column in a query should be summable the same as any field in a table. When you use a ... | |
| Form Not Reflecting Table | 2/9/2011 |
| Q: I have a form that is bound to a main table. I have added additional records to the table however, ... A: What is the RecordSource for the form? Are there any filters set? Do you see the records when you ... | |
| Literal characters | 2/8/2011 |
| Q: I am confused about literal characters in an input mask. For example, of the following characters, ... A: A literal character in an Input mask are characters that are included in the result and not typed by ... | |
| Form Record Saving | 2/8/2011 |
| Q: I copied and pasted your code in "Code Builder" for the button control. I'm still getting the same ... A: Yes a form (frmOverUnder) is supposed to open with the new record showing the stk number. Put this ... | |
| Two IIF conditions in Access with AND | 2/8/2011 |
| Q: I am very new to Access and am trying to modify a query made by someone else who no longer works ... A: I've never liked using IIFs for criteria. Sometimes its necessary, but not this time. The easier way ... | |
| Form Record Saving | 2/8/2011 |
| Q: I copied and pasted your code in "Code Builder" for the button control. I'm still getting the same ... A: Ok, try it this way: "VALUES('" & Me.[Stk Number] & "', #" & Now() & "#);" Also make sure that Stk ... | |
| Stumped, table/query structure question | 2/8/2011 |
| Q: I'm a worship leader at a church, and I'm building a database in Access 2010 to track services from ... A: I would suggest you do some research on the term normalization. This is a key design concept for ... | |
| attachment in report | 2/7/2011 |
| Q: My level of experience is intermediate and I work with access 2007. I created a database with ... A: How did you make the report? If you use the Report wizard, there are several choices for the ... | |
| Exporting to Excel | 2/7/2011 |
| Q: What is the secret to get the 'transfer spreadsheet' macro command to always overwrite old data on ... A: I think the issue is probably what version of Excel was used to create the original spreadsheet. ... | |
| Acces dBase relating tables | 2/7/2011 |
| Q: I am working on an Access Dbase project that will help the Golf Course gather information as to the ... A: This is a standard technique called Cascading or synchronized combos. Where a selection in one ... | |
| merging records | 2/6/2011 |
| Q: Running windows 7 Home Premium, MS Access 2007. I have a table of student data called 'draft'. I ... A: This can be very easy IF both tables have the same key values for student. Assuming there is a ... | |
| Default values in a form | 2/4/2011 |
| Q: .. A novice and predominantly an end-user of Access 2007. Here is my problem. We are using ... A: Not sure why using a default value would inhibit the ability to do calcs. But yes, allowing the N/A ... | |
| Form record saving. | 2/4/2011 |
| Q: I'll go ahead and appologize for being somewhat of a newb when it comes to forms and form controls. ... A: Sorry my fault, I left out a space after the closing parentheses. Use: strSQL = "INSERT INTO ... | |
| Emailing | 2/4/2011 |
| Q: Wonder if you can help i've had a look around google and can't really find an answer. I would like ... A: First, the code snippet does nothing visible by itself. If you add the line MsgBox strTo prior to ... | |
| Emailing | 2/4/2011 |
| Q: Wonder if you can help i've had a look around google and can't really find an answer. I would like ... A: Is this a named query, if so what is the name? Also I don't see the attendees e-mail address in the ... | |
| Automating Access | 2/4/2011 |
| Q: I'm trying to automate running an Access 2007 (accdb file format) macro using a Windows scheduled ... A: I would do this differently. I would create a separate front end with an autoexec macro that runs ... | |
| how do I force a field to be filled on a masterform before data can be entered in a subform? | 2/4/2011 |
| Q: The master form has a field CONTACTID, which, when filled in, generates an INVOICENO. The subform ... A: I'm sorry, I don't have memorized all the events for all the different types of controls. But its ... | |
| Basketball database | 2/4/2011 |
| Q: I need some help! I have created a database meant to track a basketball league with 6 teams. The ... A: This all depends on your table structure. I would have the following tables: tblPlayer: PlayerID (PK ... | |
| email in access | 2/3/2011 |
| Q: HOwever writing the code is a different story. I am having trouble writing it so that it will take ... A: You would use the SendObject method. The syntax for the mothed is: DoCmd.SendObject [objecttype][, ... | |
| Emailing | 2/3/2011 |
| Q: Wonder if you can help i've had a look around google and can't really find an answer. I would like ... A: There are a few options. You will have to loop through the recordset for the subform to either send ... | |
| Using Issues Template in Access | 2/2/2011 |
| Q: I'm using the Issues template in MS Access. I'm trying to use it to log issues, then email those ... A: First, what version of Access are you using? Second, are you using Outlook as your default e-mail ... | |
| how do I force a field to be filled on a masterform before data can be entered in a subform? | 2/1/2011 |
| Q: The master form has a field CONTACTID, which, when filled in, generates an INVOICENO. The subform ... A: First, you can't name objects starting with a number. I'm surprised that was accepted. Second, the ... | |
| how do I force a field to be filled on a masterform before data can be entered in a subform? | 2/1/2011 |
| Q: The master form has a field CONTACTID, which, when filled in, generates an INVOICENO. The subform ... A: In the Got Focus event of the subform control use Code like: If ... | |
| ms access | 2/1/2011 |
| Q: SELECT staff.StaffNo, semester.SemesterID, teach.SubjectID, teach.Sub2, teach.Sub3, teach.Sub4, ... A: Yes, tblSemesterSubjects has ONE record per subject per teacher per semester. So you have to add a ... | |
| Manipulating Dates | 1/31/2011 |
| Q: I am working with Access 2003. i am trying to update operation completion due dates (OpDate) based ... A: It should work both ways. If intDays is a negative number because the new date is earlier than the ... | |
| Manipulating Dates | 1/31/2011 |
| Q: I am working with Access 2003. i am trying to update operation completion due dates (OpDate) based ... A: In the After Update Event of the OrdDate control try code like: Dim intDays As integer intDays = ... | |
| subtracting dates in column headers | 1/31/2011 |
| Q: I need to know how to write a query in access to do what I am currently doing in excel. I am taking ... A: There are many fully functional databases that are not designed properly and work great until one ... | |
| subtracting dates in column headers | 1/31/2011 |
| Q: I need to know how to write a query in access to do what I am currently doing in excel. I am taking ... A: The designer of your database didn't (doesn't) know very much about database design. It appears all ... | |
| Spliting Large files into small ones | 1/31/2011 |
| Q: I have designed a School Management Software. Now I want to do: Whenever a .accdb file become ... A: First, an Access database file (accdb) cannot exceed 2GB in space. Second, an Access database ... | |
| Access 2007 VBA If statement to substitute text? | 1/31/2011 |
| Q: Hey there, I am tracking names and addresses of individuals in one of my tables, but the state ... A: What I would do is create a table of all the state names with their abbreviations. You can probably ... | |
| Access 2007 union query | 1/30/2011 |
| Q: I have 2 select queries used in a union query. I have a lookup field in one of the queries that ... A: I'll assume there is an EventID field in the event and event tracking tables. It would be the ... | |
| Access 2007 union query | 1/29/2011 |
| Q: I have 2 select queries used in a union query. I have a lookup field in one of the queries that ... A: First, it is NOT recommended that you use lookup fields on a table level. Lookup fields should only ... | |
| subtracting dates in column headers | 1/28/2011 |
| Q: I need to know how to write a query in access to do what I am currently doing in excel. I am taking ... A: Access is not a spreadsheet and shouldn't be used as one. If your database is designed properly. ... | |
| MS Access 2007 Question | 1/28/2011 |
| Q: In Access 2007 I have a column of Zip Codes (5 digits). I have managed to be able to show Leading ... A: Zip codes should NOT be numeric datatypes. They should be a text datatype. You only use Number ... | |
| MS Access If...Then...Else | 1/28/2011 |
| Q: Here is what I am trying to do: In my database there is a table called "Letter1". In the table ... A: Is Req1 a field in your table? If so, then it shouldn't be there. As a general rule we don't store ... | |
| Form record saving. | 1/28/2011 |
| Q: I'll go ahead and appologize for being somewhat of a newb when it comes to forms and form controls. ... A: OK, Yes, the problem is you are using an uneditable query so you can't edit in place. First I ... | |
| Form record saving. | 1/28/2011 |
| Q: I'll go ahead and appologize for being somewhat of a newb when it comes to forms and form controls. ... A: You are a little unclear here. Do you want to add a record to the OverUnderTable or do you want to ... | |
| Re: Counting Duplicates as one | 1/28/2011 |
| Q: Using Microsoft Access 2003/2007 I have encountered the same issue as below. I want to count ... A: There are two possibilities here. First, make a copy of the query you created but remove the Group ... | |
| ms access | 1/28/2011 |
| Q: SELECT staff.StaffNo, semester.SemesterID, teach.SubjectID, teach.Sub2, teach.Sub3, teach.Sub4, ... A: There is a design concept for relational databases called Normalization. If you google that term you ... | |
| Access 2007 Report - how to enter a field into an already typed paragraph? | 1/27/2011 |
| Q: I'm using my Access 2007 database to generate a letter that will be sent out to thousands of ... A: You can set the ControlSource to something like this: ="Your child, " & [Childname] & ", is ... | |
| Re: Counting Duplicates as one | 1/27/2011 |
| Q: Using Microsoft Access 2003/2007 I have encountered the same issue as below. I want to count ... A: In Query Design mode add first the Division field then the Account No field. Next press the Sigma ... | |
| Access Split form with a twist | 1/27/2011 |
| Q: I am trying to set up a split form that shows issue records on the top and the data entry section ... A: Split view is kind of restricting. I'm not a fan of it. In my opinion it has limited usage. In your ... | |
| ms access | 1/27/2011 |
| Q: SELECT staff.StaffNo, semester.SemesterID, teach.SubjectID, teach.Sub2, teach.Sub3, teach.Sub4, ... A: First your database is not designed properly. You should not have fields like Sub2, Sub3, etc. This ... | |
| Continuous Form and Visibility | 1/26/2011 |
| Q: I'm fairly new at Access, but I have a Continuous Form (because there are only a few fields I need ... A: There are actually a couple of ways to do this. The easiest way is to have a small text control ... | |
| Bound Vs Unbound Controls | 1/25/2011 |
| Q: I appreciate your to-the-point answers. I have a question regarding to one of your answers (back in ... A: Until the record has lost focus or committed it can be undone. So you can have a Cancel button that ... | |
| Reports and queries | 1/25/2011 |
| Q: I am using Access 2003. I have a database with three tables; tblActivity, tblPersonnel and a ... A: This indicates that you may have more than one outer join. Sometimes that won't fly. In such a case ... | |
| Marking a recordset with a unique ID number | 1/24/2011 |
| Q: Background: I have a form that uses unbound text boxes in the Header to get record selection ... A: The easy way is a table like this: tblPackets PacketID (PK Autonumber) PacketDate ... | |
| "out of range" message | 1/24/2011 |
| Q: It's been a while since I've asked you a question, but once again I'm stuck in a jam and am seeking ... A: This is an easy one. You are looping through an array and, at some point in the loop, the value of i ... | |
| Re: Counting Duplicates as one | 1/24/2011 |
| Q: Using Microsoft Access 2003/2007 I have encountered the same issue as below. I want to count ... A: There are two things here. First is to get a unique count of account numbers. Second is to get an ... | |
| MS Access Command button | 1/24/2011 |
| Q: Sir,I have a database form in MS Access with two buttons. One is to "add/change" picture and another ... A: Use the FileDialog object to search for and select files from your hard drive. This article ... | |
| Reports and queries | 1/24/2011 |
| Q: I am using Access 2003. I have a database with three tables; tblActivity, tblPersonnel and a ... A: Yep, You need to do an OUTER JOIN. In your query, you should be showing all three tables in the ... | |
| Automatically update a date when records updated | 1/22/2011 |
| Q: Scott: I have an Access 2007 form for entering into the database, one object at a time. What I need ... A: In the BEFORE UPDATE event of the FORM place code (using the Code Builder) like: Me.UpdatedDate = ... | |
| Trips Database | 1/21/2011 |
| Q: I need to create an MS Access database for people to book an excursion. Each person can only go ... A: Makes a lot of sense and Access is perfect for the purpose. It will make your job, way easier. But ... | |
| query | 1/20/2011 |
| Q: Scott: I recently asked a question about cleaning up my physician table (non users) in my ... A: What version of Access? There should be an icon on the tool bar or ribbon that allows you the select ... | |
| query | 1/20/2011 |
| Q: Scott: I recently asked a question about cleaning up my physician table (non users) in my ... A: SELECT PHYX FROM tpatVisit WHERE DOEX >#01/01/2007#; Enter (copy/paste) that in SQL View of a blank ... | |
| query | 1/20/2011 |
| Q: Scott: I recently asked a question about cleaning up my physician table (non users) in my ... A: The last I left it was to have you check the subquery as an independent query. This query: SELECT ... | |
| Access for debt interest calculations & cash flow reporting | 1/19/2011 |
| Q: This is just a general feasibility question at this point. I'm trying to take a very cumbersome ... A: Can it be done? Yes. I haven't encountered anything that Access can't do. You can use some Excel ... | |
| access | 1/19/2011 |
| Q: when i use access ,thee error "validation period checking not scucceed" occured why is it so . how ... A: Sounds like you have a validation rule somewhere that isn't working properly. You need to be more ... | |
| forms | 1/19/2011 |
| Q: That has tables named partnumbers, supplier info, manager info, supplier rejects, and parts ... A: Not sure what you are asking, but if you want store values in more than one table, that's a no-no. ... | |
| forms | 1/18/2011 |
| Q: That has tables named partnumbers, supplier info, manager info, supplier rejects, and parts ... A: Yes it makes perfect sense and is a very common thing. Again, the blog I wrote tells you different ... | |
| forms | 1/18/2011 |
| Q: That has tables named partnumbers, supplier info, manager info, supplier rejects, and parts ... A: First, I've written a blog on displaying data from a related table, which is part of what you are ... | |
| MS Access 2003 | 1/18/2011 |
| Q: I have a question for you. I have a form the end user fills out for transactions. One of the ... A: Create a lookup table to feed the combobox. Something like: tluYards YardID (Primary Key ... | |
| Access 2007 Custom Form Printing | 1/18/2011 |
| Q: Not being an Access expert by any stretch, how do I print a typical form BUT WITH A RESTRICTED OR ... A: Yes, Create a report and put a button on the form to print the report. You can filter the report to ... | |
| Access 2007 Custom Form Printing | 1/18/2011 |
| Q: Not being an Access expert by any stretch, how do I print a typical form BUT WITH A RESTRICTED OR ... A: Well the first thing is you don't print forms. Forms are designed for interactivity on a screen. ... | |
| Link label to text box | 1/17/2011 |
| Q: I am a beginer in Access. I have opened one of the templates in Access to do my database. (access ... A: Sounds like you added controls to a form, not field to a table. Controls on a form are not fields. A ... | |
| save big access table to separated xls sheets | 1/17/2011 |
| Q: please, should you help me resolve my problem how to do automatically save one big access 2003 table ... A: Create queries to return to recordsets you want to export to Excel. Then create a module that runs a ... | |
| Confirm/Cancel Check Box Changes | 1/16/2011 |
| Q: I have a form with several check boxes. I want a confirmation for changes to each of the check ... A: Yes there is a better way. Its called normalization and that's a key concept for the design of ... | |
| Close form open form | 1/15/2011 |
| Q: I have an Access database (2003)with a custom switchboard. Main Switchboard->Forms ... A: I'm not clear exactly what you want to stop. But if you want to close one form when you open another ... | |
| Confirm/Cancel Check Box Changes | 1/14/2011 |
| Q: I have a form with several check boxes. I want a confirmation for changes to each of the check ... A: If Me.chkPrincipals Then IF Msgbox("Confirm Subscription Change to Yes".",vbYesNo) = vbNo Then ... | |
| Run-Time Error '3075' | 1/13/2011 |
| Q: Here is my code: Private Sub Command20_Click() Dim Emailto As String Emailto = ... A: The syntax for a DLookup is: DLookup("[field/column name]","table name", "criteria") The DLookup ... | |
| sequential alphanumeric order number | 1/13/2011 |
| Q: Please be kind as I am new to MS-Access. I'm using XP and Access 2003. My problem is that i have ... A: I'm sorry I may have misunderstood. I thought you want to import the existing quotes numbers as is, ... | |
| sequential alphanumeric order number | 1/12/2011 |
| Q: Please be kind as I am new to MS-Access. I'm using XP and Access 2003. My problem is that i have ... A: If the pattern of the order number is COD and a number, then you can extract the numerical portion ... | |
| Report | 1/11/2011 |
| Q: Good afternoon Scott: I have a Timesheet form with record source qryTimesheetextended. With a ... A: Your report should have a Recordsource of a query that joins the Timesheet table with Jobs table. ... | |
| access 2007 | 1/11/2011 |
| Q: Query - how to output checkbox -1 to text in a query. I have a yes/no field titled Ambassador. ... A: My fault, my eyes are going. But the error message tells you exactly what the problem is. You use a ... | |
| access 2007 | 1/11/2011 |
| Q: Query - how to output checkbox -1 to text in a query. I have a yes/no field titled Ambassador. ... A: Alias: IIF([fieldname]."Ambassador","") Put that in a new column in your query. Alias is the title ... | |
| Dynamic Field in a Form | 1/10/2011 |
| Q: I am using Windows 7-64 bit and running Office 10 Pro. I currently have a database constructed that ... A: First you don't have "fields" on a form. You have controls that may or may not be bound to a field ... | |
| Access: Check box - how to have a true response without using mouse | 1/10/2011 |
| Q: I do volunteer work for our local elementary school supporting our Parent Teacher Organization. In ... A: The easy answer to your question is to use the spacebar. However, a database with a lot of Yes/No ... | |
| Cannot type special letters in Access 2003 VBE | 1/8/2011 |
| Q: Good day Mr. Scott. I cannot type any special letters (ex: ñ, é, or í) in the Access 2003 visual ... A: I've never tried to do this, but I have some suggestions that may help. I'm assuming you are trying ... | |
| Restaurant bookings and customer database | 1/8/2011 |
| Q: I'm trying to design a database for a restaurant. I have Customers table and Bookings table with ... A: Very simple. Create a form based on tblBookings. On that form make the customer_id control a ... | |
| MS Access Query issue | 1/8/2011 |
| Q: I have two tables: 1st:- MainFile_Aug and 2nd:- DescFile_Aug DescFile_Aug have two fields:- File_ID ... A: So you need to have the total hits AND the # of hits in the last 30 days? In that case I would add ... | |
| MS Access Query issue | 1/7/2011 |
| Q: I have two tables: 1st:- MainFile_Aug and 2nd:- DescFile_Aug DescFile_Aug have two fields:- File_ID ... A: That's because you are using a Group By query, but each column needs to be either in the Group By or ... | |
| Access QBE | 1/6/2011 |
| Q: I have entered IN ([Enter ages:]) as Criteria. During query execution, entering 1 age works fine. ... A: Using a Parameter Prompt query is VERY limiting. If you want to enter multiple criteria. The best ... | |
| Volunteer Hours Question | 1/6/2011 |
| Q: I read and earlier question (http://en.allexperts.com/q/Using-MS-Access-1440/2010/9/query-37.htm) ... A: It's not the Rowsource that is the issue. Your RowSource is fine (though I would use Lname & ", " & ... | |
| transfering excel spreadsheet into an Access table | 1/5/2011 |
| Q: I am trying to write a VBA code to import excel files into a single table named "CCTV_Daily_Report". ... A: First, you can use the FileDialog command to open the Standard Windows file Open dialog. You can ... | |
| Volunteer Hours Question | 1/5/2011 |
| Q: I read and earlier question (http://en.allexperts.com/q/Using-MS-Access-1440/2010/9/query-37.htm) ... A: Ok, the problem is either in your table or in your controlsource for the combo. I'm assuming here ... | |
| Access, Drop-Down Box, Data not Displaying | 1/5/2011 |
| Q: I have seen your questions and answers to Access Drop-Down boxes, not displaying all of the fields. ... A: Is that the RowSource TYPE? If the Rowsource just says query, that could be the problem. The ... | |
| Disassociation of VBA code module... | 1/5/2011 |
| Q: So, this has happened twice now, with no discernable explanation. My coworker and I have created an ... A: So the events do say [Event Procedure] on them. And when you click the ellipses it opens the VBE to ... | |
| query | 1/5/2011 |
| Q: I have a mammography program 3 key tables Patient demographics, patient visits (w date), and ... A: You would do this using a Subquery. In the Date Column you would add something like: NOT IN(SELECT ... | |
| Disassociation of VBA code module... | 1/5/2011 |
| Q: So, this has happened twice now, with no discernable explanation. My coworker and I have created an ... A: Hmm, the only time I have seen that happen is when you copy forms. The first thing I would tryis to ... | |
| Unsaved Data | 1/3/2011 |
| Q: I had to start a new question because the system said I had too many follow up questions. So I ... A: Yes you can copy and paste. But AutoFEUpdater makes it easier. Check it out at autofeupdater.com If ... | |
| Unsaved Data | 1/3/2011 |
| Q: I had to start a new question because the system said I had too many follow up questions. So I ... A: Umm I forgot what I was thinking. I think the next step is to just deploy all the front ends. So ... | |
| summing up grouped values | 1/2/2011 |
| Q: I have question about MS Access 2003 query. I have a table containing 4 fields; Product_Code ... A: This is very easy to do using Query Design mode. You do NOT need nor want a new table for this as ... | |
| Sequential numbering using several controls | 12/31/2010 |
| Q: This is for a different form in the same program you have been helping me with. I have Date, ... A: First, you shouldn't be storing RecordNumber. Since its a concatenation of the other three no reason ... | |
| attendance system | 12/31/2010 |
| Q: sir, i want to track attendance of students of different classes while in each class there are 6 ... A: This was posted to a general pool, so I'm not sure who's video you were referring to. When I setup ... | |
| Saving an attachment using the attachment control on forms | 12/31/2010 |
| Q: I have an Access database, with a table that has an attachment type field among others. I created a ... A: Application.FollowHyperlink Me.Controlname where Me.Controlname is the name of the control bound to ... | |
| filtering subform using radio buttons | 12/30/2010 |
| Q: (Using Access 2007) Im trying to program my radio buttons to filter data for student pass/fail in a ... A: The first question is what is stored in the Pass or Fail field (btw using spaces in object names is ... | |
| Quantity on Hand | 12/30/2010 |
| Q: I’m new to Access and using Access 2003. Tried so many approachs but failed in the last stage on ... A: Ok, here's your initial problem. You have duplications in your tables. This is what I think your ... | |
| Saving an attachment using the attachment control on forms | 12/30/2010 |
| Q: I have an Access database, with a table that has an attachment type field among others. I created a ... A: You can just use a text field and control to store the location. You can use the FileDialog command ... | |
| Unsaved Data | 12/29/2010 |
| Q: I had to start a new question because the system said I had too many follow up questions. So I ... A: Yeah I knew that was coming. I've complained about that. Yes you did the right thing, but you need ... | |
| MS Access 2007 | 12/29/2010 |
| Q: I am looking at setting up a new database and was wondering if MS Access is an option to use in a ... A: First stop thinking in terms of a spreadsheet. Access is NOT a spreadsheet its a relational ... | |
| Unsaved Data | 12/29/2010 |
| Q: I'm basically brand new to Access XP but have been designated the Access tech person at work. ... A: "Hi Scott, I checked out the actual tables to see if the data is being entered there and it is. So ... | |
| Unsaved Data | 12/29/2010 |
| Q: I'm basically brand new to Access XP but have been designated the Access tech person at work. ... A: It's hard to tell what went wrong. First I don't know if its the form or something else. That's why ... | |
| Unsaved Data | 12/29/2010 |
| Q: I'm basically brand new to Access XP but have been designated the Access tech person at work. ... A: Whoever gave you that advice apparently know less than you. The first issue is you NEED to split ... | |
| Using IF function between 2 tables | 12/29/2010 |
| Q: I have two tables one tbl_PRODUCTS and the other tbl_ORDER In the product I have PRODUCTS_Quantity ... A: I wouldn't do this as a validation rule. In the After Update event of the Quantity control on your ... | |
| Saving an attachment using the attachment control on forms | 12/29/2010 |
| Q: I have an Access database, with a table that has an attachment type field among others. I created a ... A: First, why are you storing the data somewhere else? If you are using an Unbound form why? Since an ... | |
| MS Access 2007 | 12/23/2010 |
| Q: I am building a vehicle usage database where I have multiple vehicles and multiple drivers. I am ... A: You really don't need to do this. Basically you are duplicating data which is a no-no. This is ... | |
| Filter on Signing In | 12/23/2010 |
| Q: I have a database which is locked down to user permissions. the users have asked me if it is ... A: So you are using ULS. I'm not a big fan of ULS. In fact Microsoft has dropped it for 2007 (when ... | |
| Sequential numbering by Job | 12/22/2010 |
| Q: I am using Access 2002. When I enter data in a form, frmNCRReport, I want a sequential numbering of ... A: If JobNumber is a text datatype, then it should be: Me.txtOrderNumber = Nz(DMax("[OrderNumber]", ... | |
| Auto populate a cell | 12/22/2010 |
| Q: I am using Access 2007. I have a table that has a last name column and a first name column. There ... A: You have to use YOUR field and table names in the RowSource. You may need to put brackets around the ... | |
| Verify Item Number not Duplicate | 12/21/2010 |
| Q: I'm using Access 2002. I scan a bar code and the number goes to a text box, txtReleaseID, on form, ... A: My fault, I missed that comma after MsgBox. It shouldn't be there. Lets say you want the number to ... | |
| Verify Item Number not Duplicate | 12/21/2010 |
| Q: I'm using Access 2002. I scan a bar code and the number goes to a text box, txtReleaseID, on form, ... A: You are partially correct. The ReleaseID being text is part of the issue (by the way its not a good ... | |
| creating a comments history field | 12/21/2010 |
| Q: I really hope that you can help me here, I’m trying to build a data base for the company using MS ... A: Not sure where you got that solution, but its NOT what I would recommend. Instead I would recommend ... | |
| Access input masks and auto tab | 12/20/2010 |
| Q: I have programmed several command buttons (0-9) on my form in order to input numbers into a textbox. ... A: Need more details about what you are trying to do. If I'm following you there are buttons arranged ... | |
| Auto populate a cell | 12/20/2010 |
| Q: I am using Access 2007. I have a table that has a last name column and a first name column. There ... A: Frankly you don't. I'm assuming here that you a separate people table and are entering a record in ... | |
| Using a Form to filter a Query | 12/20/2010 |
| Q: I have a Query set up to filter using an UNBOUND Form. I currently want to filter using 4 separate ... A: A couple of possibilities. First on the date range, set the default values of the start and end date ... | |
| Database Design | 12/17/2010 |
| Q: I'm new to Access and trying to build a database of genetic information. So far, I have a ... A: The problem here is I don't know enough about the subject to be sure of certain things. Much of the ... | |
| Access report won't save | 12/16/2010 |
| Q: My report cannot be edited or saved. The name cannot be altered. Details, in case you need them: ... A: First, have you done a compact and repair of the database? When you say it can't be edited or saved ... | |
| MS Access (Calculating Average Start Time) | 12/16/2010 |
| Q: I need to calculate the average start time of a daily event and am having far to much trouble ... A: You are close. Try this: SELECT [QUERYA].wknbr, [QUERYA].NME_JOB, ... | |
| Display dropdown values based on contents of another field | 12/15/2010 |
| Q: "I have an Access 2007 form that, among others, has 2 fields called Department and Category. The ... A: This is a standard procedure called Cascading or Synchronized combos. This article explains how to ... | |
| linking data | 12/15/2010 |
| Q: i have a main mgr, alt mgr, and mgr table. is there a way to take the info from the alt and main and ... A: I also asked for the structure of these tables. Based on what you have told me so far. I would ... | |
| linking data | 12/15/2010 |
| Q: i have a main mgr, alt mgr, and mgr table. is there a way to take the info from the alt and main and ... A: I'm not sure why you need three tables. Can you explain the structure and purpose of these tables? I ... | |
| Lookup to two tables | 12/14/2010 |
| Q: I have Access 2002 I have two tables, Foreman and Inspector (currently five names in each), that I ... A: There are two things you can do here. The first is to use only one lookup table and add a field to ... | |
| using one access form to interface more than one table of the same type | 12/14/2010 |
| Q: I am upgrading my database in which I want to store data of the same type in the same format for ... A: Wrong structure! There is NO reason to create separate tables for the same data. All you need is a ... | |
| vb.net | 12/13/2010 |
| Q: now i am creating data base progarmming in vb.net, how to automaticaly msaccess is update in my ... A: I['m not a VB.net developer, but if you are using an Access database (either MDB or ACCDB) as your ... | |
| Summing In a from | 12/13/2010 |
| Q: I am trying to calculate the sum of a field in a form. I have tried creating a query but I'm not ... A: Then adding a text box control to the footer and setting the Controlsource to: =Sum([Pressure Loss]) ... | |
| Summing In a from | 12/13/2010 |
| Q: I am trying to calculate the sum of a field in a form. I have tried creating a query but I'm not ... A: If its only a single form, why bother with a sum? If you want to sum the total of pressure loss for ... | |
| Summing In a from | 12/12/2010 |
| Q: I am trying to calculate the sum of a field in a form. I have tried creating a query but I'm not ... A: Is the form a continuous form, datasheet or a single form? Did you add the control (not field) in ... | |
| Reports and grouping | 12/12/2010 |
| Q: I have a number of individual reports all doing the 'same but a little' different reporting. They ... A: Yes, If you put an expression with an aggregate function (i.e. Sum, Average, Count, etc.) in a Group ... | |
| Access 2007 | 12/12/2010 |
| Q: Follow-up - You said "since event tracking record has to exist before a payment can exist..." Not ... A: It doesn't matter that an event may not have a payment, in that case, you just don't create a ... | |
| Access 2007 | 12/12/2010 |
| Q: I have a form (Demographics) that includes a subform (Payments). Within the Payments subform is a ... A: First, You should NOT be using Event Name as a key field. Using text fields for joins slows ... | |
| Sorting (Alphabitizing function) | 12/12/2010 |
| Q: I have windows 7 and use M/S works for text documents. If I have a list of names, etc, I cannot ... A: This also has nothing to do with MS Access, and I'm not very familiar with Works, but I may have a ... | |
| Access 2007 | 12/12/2010 |
| Q: I have a form (Demographics) that includes a subform (Payments). Within the Payments subform is a ... A: A Foreign Key is a field in a child table that contains the Primary key value of the related record ... | |
| Access 2007 | 12/12/2010 |
| Q: I have a form (Demographics) that includes a subform (Payments). Within the Payments subform is a ... A: Is either of the subforms in continuous form or datasheet mode? You really need another table, by ... | |
| HELP - need to unhide table in Access 2010 | 12/10/2010 |
| Q: I wanted to find a way to password protect tables as a way to reduce the chance of accidentally ... A: Right click on the title bar of the Navigation pane and choose Navigation Options. You can select to ... | |
| Print Report From Form | 12/10/2010 |
| Q: I have a form, frmReport, that is used to input data. On this form is a control, ReportNumber, that ... A: The code you posted should work if there is a field named ReportNumber and that field is a numeric ... | |
| Number from date | 12/9/2010 |
| Q: I have a date control on my form that feeds the date field on a table. I want to use the date as ... A: If I understand what you need, you want to have an identifier on a report. That identifier will ... | |
| Access 2007 Query Results | 12/9/2010 |
| Q: Scott: Access 2007: How do I use query results as the basis for another query so that I can further ... A: If you are are using Query Design mode to create your queries, then you just add the query to the ... | |
| Parameter Query | 12/8/2010 |
| Q: I have created a form to enter parameters for generating a report. Of these there are two fields ... A: The way I handle this is to set the default values of the two text boxes to: ... | |
| Converting formats | 12/8/2010 |
| Q: In Access 2003. I have two tables; one is SerialNumber table that creates the field SerialNum by ... A: If you are Updating the data then I would run 2 passes to remove the double leading zeros. If you ... | |
| Converting formats | 12/8/2010 |
| Q: In Access 2003. I have two tables; one is SerialNumber table that creates the field SerialNum by ... A: Sounds very similar to the company I work for. I still think, however, that you should be using a ... | |
| Converting formats | 12/7/2010 |
| Q: In Access 2003. I have two tables; one is SerialNumber table that creates the field SerialNum by ... A: You have the wrong approach here. You are confusing an autonunmber PK with a visible identifier. By ... | |
| Filtering to a record from another form | 12/7/2010 |
| Q: What I’m trying to do is open the form ‘proforma’ from the main form and go to a selected record. I ... A: The first Code should work assuming that PosNo is a number datatype. The second code should also ... | |
| refreshlinks | 12/3/2010 |
| Q: Here's where my ignorance really shows. You have given me a link, and a short piece of code. What ... A: Sorry to not answer sooner. Somehow notice of the question got lost. As I recall, I referred you to ... | |
| Access 2010 | 12/3/2010 |
| Q: I am trying to make a database for my husband's website. Specifically I would like to sell a ... A: Is this database to manage the orders made through the website or to allow customers to place ... | |
| replication of database for new year | 12/2/2010 |
| Q: I have a database that I have inherited. When the database icon is clicked the database opens into ... A: And then you have to do this EVERY year? That is NOT good design. You should have a field in your ... | |
| Access Reporting Design | 12/2/2010 |
| Q: I've created a report from three tables and have got it pretty much where I want it, Groups, layout, ... A: First I have to say that almost reports I do are based on queries. Basing a report on a query gives ... | |
| Finding the difference between two dates in microsoft access | 12/2/2010 |
| Q: i am trying to subtract from a particular date eg. 2/28/2009, from a date record in a table...but ... A: You can use the DateDiff() function to determine the number of calendar days between two dates: ... | |
| Tricking the clock | 12/2/2010 |
| Q: .... A newbie to Access 2007 here. Just curious to know if when entering data into a Access 07 ... A: Actually it is. In the After Update event of the control where you enter the date, you can put code ... | |
| Access 2007 | 12/1/2010 |
| Q: I need your help. I tried to import data into an existing access table but kept getting errors, so ... A: First question is what are the datatypes for Imp Business Needs and Geographic Region. In the Dusm ... | |
| Updating table, with form.. | 12/1/2010 |
| Q: don't worry my question isn't this long, just some background: I am a beginner in terms of FORMS, ... A: There are two ways to build forms. The first and easiest is with a bound form. With bound forms, ... | |
| Access 2007 | 11/30/2010 |
| Q: I have an access database which I need to access from support centres around the country. I would ... A: Not only practical, but its what Sharepoint was designed for. This is especially true if the primary ... | |
| shedule updates at night | 11/29/2010 |
| Q: you answered in mid 2009: I would have separate Access file that only you can access that links, ... A: You can set a form to load on default and run code in the On Load or On Open events. But that seems ... | |
| access runtime | 11/29/2010 |
| Q: I renamed it Leafandlawn.accdr, but when I look at Properties, it tells me it is still an accdb ... A: I saw you posted on Microsoft's Answers forum. I started reading that thread because I thought it ... | |
| Access 2007 | 11/24/2010 |
| Q: .. it is along the lines of the answer you gave to another person ... I will be building an access ... A: No, the back end is shared. If you haven't split yet, then make sure you split the back end to the ... | |
| Recommendations | 11/23/2010 |
| Q: I'm jumping from Access 2003 to 2010 and there seems to be a huge difference. When using this ... A: I tech edited Roger Jennings new book from Que Publishing whihc will be released next month. You can ... | |
| access runtime | 11/23/2010 |
| Q: I renamed it Leafandlawn.accdr, but when I look at Properties, it tells me it is still an accdb ... A: I wonder if something to do with the stick is messing it up. Can you try e-mailing the app to ... | |
| shedule updates at night | 11/23/2010 |
| Q: you answered in mid 2009: I would have separate Access file that only you can access that links, ... A: This doesn't require a batch file. I have a separate front end with an autoexec macro that runs the ... | |
| Group Concat | 11/23/2010 |
| Q: I am a real beginner at creating access databases. What I would like to be able to do is create ... A: You need a custom function for this. You can find a function that will concatenate values from a ... | |
| Relationship Diagram | 11/23/2010 |
| Q: Scott - Thanks for your help this far. I have an Access application with a partitioned front end ... A: I've never heard of any performance issues either way. Generally I only formalize relations to ... | |
| access runtime | 11/23/2010 |
| Q: I renamed it Leafandlawn.accdr, but when I look at Properties, it tells me it is still an accdb ... A: That's correct, Windows still considers it an accdb. So running it as an accdr eliminates the ... | |
| MS Access 2007 | 11/22/2010 |
| Q: I have several products, one of the product called 'Biryani', it has ingredients of measured Raw ... A: You need to create a transaction table that lists any movement of raw materials both in and out. You ... | |
| Help trouble-shooting a query | 11/22/2010 |
| Q: I would consider myself a new user of Access. I recently completed a introductory course to Oracle, ... A: My Fault. I forgot the Alias in the SQL Statement I gave you: SELECT Travel_Card_ID, Vendor_ID, ... | |
| Help trouble-shooting a query | 11/22/2010 |
| Q: I would consider myself a new user of Access. I recently completed a introductory course to Oracle, ... A: Sorry to say but you have gone wrong in several places. Well maybe wrong is too strong a word, but ... | |
| Reports and many-to-many relationships | 11/21/2010 |
| Q: Good evening, I am using Access 2007 on Windows XP Professional to track projects and the funding ... A: I don't believe you can do this from the perspective of projects. You can go the opposite router and ... | |
| Access Runtime - read only message | 11/21/2010 |
| Q: In response to your questions:- I have put the FE and BE in a folder, and placed that folder in ... A: Ok, So you have checked that a) He has a folder named C:\Program files\Mydatabase on his PC b) that ... | |
| combobox goes blank | 11/21/2010 |
| Q: Correct, the prof_ID should show. When I open the table all the records are filled in, so they are ... A: I'm drawing a blank. I can't duplicate this and it looks like you have everything setup correctly. ... | |
| HELP ! combobox goes blank | 11/21/2010 |
| Q: or I should say problem. I have three comboboxes on a subform; the subform is a continious form. ... A: So the ProfID should show after a selection is made. Is it possible the ProfID is blank for the ... | |
| Access Runtime - read only message | 11/21/2010 |
| Q: In response to your questions:- I have put the FE and BE in a folder, and placed that folder in ... A: I really don't believe that 2010 is the culprit here. I've gone back and forth between 2010 and 2007 ... | |
| HELP ! combobox goes blank | 11/20/2010 |
| Q: or I should say problem. I have three comboboxes on a subform; the subform is a continious form. ... A: Ok, Check the Column count and Column widths properties. Looks like you should have a Column count ... | |
| runtime - VBA not working | 11/19/2010 |
| Q: I have Access 2007, and have a database that works perfectly when I run it as "Leaf /runtime" on my ... A: What about the rights to the folder where the front and back ends reside. Do you have full ... | |
| Make Table query - variable name | 11/19/2010 |
| Q: I am running an access database that is linked through ODBC into a series of Peoplesoft Tables. To ... A: You will have to generate the SQL code in VBA to do this. What I would suggest is to create one Make ... | |
| Composite Key Reference | 11/19/2010 |
| Q: I have a database that is tracking issues that I solve. An ISSUE has an OWNER and a LOCATION. A ... A: Not the way you are doing it. You don't want a city and department to be unique. Can't you have ... | |
| runtime - VBA not working | 11/18/2010 |
| Q: I have Access 2007, and have a database that works perfectly when I run it as "Leaf /runtime" on my ... A: Not sure why you are using the GotoControl, but don't use GotoRecord. Use: DoCmd.RunCommand ... | |
| macros to modules | 11/18/2010 |
| Q: Just as I can read French passably, but cannot write it, os it is with VBA. When I develop a ... A: Without seeing the code, its hard to know what's going on. But I suspect, from the message, that ... | |
| Creating a list with sequential numbers 0 - 5000 or more... | 11/17/2010 |
| Q: I'm a intermediate Access user (using Access 2003#. I can make tables, create relationships, run ... A: This would be easier to do in Excel. Just start a sequence and drag the sequence until completion. ... | |
| Product ordering database | 11/17/2010 |
| Q: I need to set up a database that allows my chef to place orders for customer visits and allows me to ... A: First, in reference to populating other tables, you shouldn't be doing that. One of the principles ... | |
| Access Multiple Field Index | 11/17/2010 |
| Q: Julie - A few years ago, you answered a question about how to create a multiple field index. My ... A: Correct. Whether the field being included in the index is a FK or not is immaterial. The purpose is ... | |
| macros to modules | 11/16/2010 |
| Q: Just as I can read French passably, but cannot write it, os it is with VBA. When I develop a ... A: To rename a file just select the file in Windows Explorer, right click and choose Rename. I would ... | |
| Database setup | 11/16/2010 |
| Q: I have a small office with 8 staff in PR business, we would like to set up a database for clients ... A: While Filemaker is a great product, since you already have Access, it makes sense to use Access. ... | |
| MS Access Query IF Statement | 11/16/2010 |
| Q: Within a query I want to use an if statement to identify those records with a specific word withing ... A: If you are looking to add a column that identifies whether the field contains SPSP try: SPSP: ... | |
| AfterUpdate() calculation | 11/11/2010 |
| Q: I have an Access 2007 form where the user selects a Month from a combo box. After selecting the ... A: I'm not Manish, but I think I can help. Your DSum should work depending on where you put it. If the ... | |
| Inventory database | 11/9/2010 |
| Q: Version 2007 I'm trying to build a data base that can be used for inventory and maintenance of fall ... A: First, your design has flaws. When designing a database you first design as much of the tables as ... | |
| Getting a field to autosearch for dupe records | 11/4/2010 |
| Q: I have a form setup to keep track of patients and a subform setup for their separate visits. I need ... A: Is the main form bound to the patient table to enter new patients? If so, use a DLookup in the After ... | |
| macros to modules | 11/3/2010 |
| Q: Just as I can read French passably, but cannot write it, os it is with VBA. When I develop a ... A: What version of of Access? If 2007 or later, you can name a file accdr to force it to load in ... | |
| Access DB | 11/3/2010 |
| Q: I have four tables (project data, deliverables, risks, cost). I would users to be able to pick ... A: Its not pre-populate, its filtering. If the user is adding a new record then all they should need is ... | |
| Audit Trail | 11/2/2010 |
| Q: I am trying to create an audit trail for a musdic database. I am not very proficient with VBA and ... A: A comprehensive audit trail that will record any and all changes to data in specified fields ... | |
| "0" <> "0" in Number Data Type | 11/1/2010 |
| Q: I update one table from another based on certain criteria that includes a comparison of numeric ... A: What are the numerical subdatatypes for the two fields? For example: If one is Long integer and the ... | |
| Access Asset Template Question | 11/1/2010 |
| Q: I have few questions regarding the template provided by access 2007 called Assets. In the form ... A: The code for that would probably be an embedded macro in the After Update event. The picture ... | |
| Passing multiple selections from a form to a query | 10/29/2010 |
| Q: I have seen this question many times on the internet but no-one has answered it for my exact ... A: You are correct. Theoretically, you should be able to enter: IN(Forms!formname!controlname) as ... | |
| Eliminating Duplicates | 10/29/2010 |
| Q: I have a database that includes First Name, Last Name, Street Address, City, State and Zip Code and ... A: Your problem is that you don't exact duplications. Your best bet is to provide some additional info ... | |
| Access Multiple Field Index | 10/29/2010 |
| Q: Julie - A few years ago, you answered a question about how to create a multiple field index. My ... A: I'm not Julie, but to answer you; a multi-field index allows you to require that a combination of ... | |
| If statement in Access | 10/29/2010 |
| Q: I would like to do a form in the Microsoft Access, and my question is the following: 1. i have a ... A: This isn't an IF statement. What it sounds like you are talking about called Cascading or ... | |
| LINKING MS Access to Lotus Notes | 10/29/2010 |
| Q: I have a metadata database on a project I am working on. I have tables that contain data specific ... A: You can link to a Notes database from Access. You need to get the ODBC drivers for Notes from IBM's ... | |
| Access 2007 | 10/28/2010 |
| Q: It would be a great help if you kindly solve my problem. I have created 5 forms named A,B,C,D,E. Now ... A: This is very easy, but I would not use an Option Group, That requires more programming. Instead, ... | |
| Query | 10/28/2010 |
| Q: I have a formula which I need to add an OR statement to. The numbers we are looking for are either ... A: First, Is this column in a Group By query? Otherwise there is no need for the Sum() Second, an IIF ... | |
| Massive Database Design | 10/27/2010 |
| Q: First off, I will be the first to admit that databases are anything but my strong point! Here is ... A: You have a good start here, you have identified the data you need to capture about an item being ... | |
| Using DateDiff to calculate how late a student is to class | 10/27/2010 |
| Q: I've created a database to help track student behaviors, one of which is the arrival time of a ... A: First, you need to understand that Access stores date/time values as a double precision number where ... | |
| cannot run VB expression in different computers MS Access 2003 | 10/27/2010 |
| Q: I tried to open my file on the different computers. one works but the another doesnt work. I dont ... A: I think I understand the problem. One computer has different regional settings than the other. The ... | |
| Access Form | 10/27/2010 |
| Q: I have a form1 which I open from Form A to Add a new record for a client(navigation bar turned off). ... A: Easier to use two different forms. One with the Data Entry property set to Yes for new records and ... | |
| Importing Data for a particular month | 10/26/2010 |
| Q: I am creating a database to track perfect attendance for employees on a monthly basis. What the ... A: That doesn't really answer my question. What would be best is if each dept supervisor submits the ... | |
| Compare | 10/26/2010 |
| Q: I have got 2 Queries 1 showing the people who cancelled or did not show up to a training course and ... A: Just do a join between the two queries. You should include AttendeeID in both queries so you can do ... | |
| On click event button | 10/26/2010 |
| Q: Just after a bit of help. I currently have a button that is used to email form details automatically ... A: Why do both? The datetime stamp indicates that the mail was sent. Having a yes/no field as well is ... | |
| Dates and week of the year | 10/25/2010 |
| Q: I have a field that I'm calling "approval dates". I want to incorporate that field into another ... A: The key here is how you define the first week of the year. You say you translated to Week number but ... | |
| Combo boxes | 10/23/2010 |
| Q: Julie, I am trying to link two Combo boxes so that when I select and item from the Category Box the ... A: I'm not Julie, but what you are asking is a standard technique called cascading or synchronized ... | |
| Importing Data for a particular month | 10/22/2010 |
| Q: I am creating a database to track perfect attendance for employees on a monthly basis. What the ... A: Can the user dictate the format the information is provided in? Is the info provided in a consistent ... | |
| User Input "Criteria" | 10/21/2010 |
| Q: Julie, Using Access 2003. Trying to create a Crosstab Query where the user inputs a Date. The query ... A: I'm not Julie, but the solution to your problem is twofold. First, do not use a Parameter prompt ... | |
| Ms Access 2007, Multivalue combo box as a query criteria | 10/21/2010 |
| Q: i m creating a database with many fields as a multiple selections. i want to know how could i use ... A: If you are looking to filter a combobox based on the selection in another control then the ... | |
| Populating a text box/memo box | 10/20/2010 |
| Q: I would like to know if there a way to populate a text box or a memo box by clicking a check box? ... A: Your design does not appear to be properly normalized. Having a lot of Yes/No fields is a sign of a ... | |
| Open form and set specific ID Value | 10/20/2010 |
| Q: I've a database for Contracts (Pk)linked to another one for shipments(FK), I've a button on the ... A: Something like this should work Behind your button use code like: If ... | |
| Forms | 10/20/2010 |
| Q: I have a form that shows multiple records. I want to type in a date once and update all of the dates ... A: Use an Update query. You can add an unbound text box to the form to enter the date. You can then add ... | |
| Importing Data for a particular month | 10/19/2010 |
| Q: I am creating a database to track perfect attendance for employees on a monthly basis. What the ... A: First, the database is not properly set up. The correct way to do this is with at least two tables: ... | |
| create dropdown list that links to different reports | 10/18/2010 |
| Q: I have created few reports and would like a dropdown list on a form (or switchboard) which links to ... A: There are two ways. The easier way is the set the Rowsource of the combo to: SELECT Name FROM ... | |
| Data Entry Form for a Many-to-Many Relationship | 10/16/2010 |
| Q: I am using Access 2007 on a Windows XP Professional machine to track projects and the funding ... A: First, create your main form bound to tbl_Projects. Put any controls you want about projects. Add a ... | |
| searching db | 10/16/2010 |
| Q: I’m using Access 07 as a front end, and SQL Server as the back end. I'm dealing with a lot of data ... A: Yes, a survey database requires a special structure. It looks something like this: tblQuestions ... | |
| Access | 10/15/2010 |
| Q: Scott When setting up my database I changed an 'Address' text box key behaviour from default to a ... A: First, each line DOES show in the table, you just have to expand the height of the row to see it. ... | |
| Access 2003 Invoice Due Date Set | 10/15/2010 |
| Q: In Access there is a DATE field and an AMOUNT. I would like to create an INVOICE DUE DATE calculated ... A: First, as a general rule we do not store calculated values. There is no need to do so as this value ... | |
| Pop Up Reminder in Access | 10/15/2010 |
| Q: I am working with an access 2003 database. On my main form (with a subform), I would like to have a ... A: You have the chkReminder control set to Triple State which allows Nulls. First run an Update query ... | |
| Pop Up Reminder in Access | 10/14/2010 |
| Q: I am working with an access 2003 database. On my main form (with a subform), I would like to have a ... A: Did you create a form named frmReminder and/or did you name the checkbox chkReminder? You need to ... | |
| form and report references | 10/14/2010 |
| Q: I need help understanding how to reference fields contained on other forms and reports within my ... A: You can reference a value in a field on an open form using the syntax: Forms!formname!controlname ... | |
| Pop Up Reminder in Access | 10/13/2010 |
| Q: I am working with an access 2003 database. On my main form (with a subform), I would like to have a ... A: In the On Current event of the form put code like: If Me.chkReminder Then DoCmd.OpenForm ... | |
| Access 2007 | 10/13/2010 |
| Q: I have inherited a database with a table containing many fields eg:name, date,TimeOfIncident, ... A: Well there is a right way and a wrong way. If the users what to see check boxes, then you should be ... | |
| Access 2007 | 10/13/2010 |
| Q: I have inherited a database with a table containing many fields eg:name, date,TimeOfIncident, ... A: Yes, your problem is in design. Having a lot of yes/no fields is a sign of a denormalized database. ... | |
| Merging columns | 10/12/2010 |
| Q: i need to have a single column header above to separate fields which also have their own titles.how ... A: While there are exceptions, since its far easier to combine fields for display using concatenation, ... | |
| Conditional printing of Report | 10/11/2010 |
| Q: I use a form to compile a KEY which is used as criteria for alternating identical queries called RED ... A: Dim strDocname as String If Me.controlname = "B215" then strDocname = "rptWorksheet2" Else ... | |
| Merging columns | 10/11/2010 |
| Q: i need to have a single column header above to separate fields which also have their own titles.how ... A: OK, I think I understand. The first question is whether this table is under your control. You should ... | |
| barcode | 10/10/2010 |
| Q: I have made a form on access which is for orders. It is working great when I select a producr from a ... A: A barcode reader is an alternate input device. Each reader is different. You need to talk to the ... | |
| restrection for some item | 10/9/2010 |
| Q: i have some tables , one table have two column (Model , Part number ) and other table i record item ... A: I'm not sure I follow what you need here. If you want to make sure the user records only items in ... | |
| CDate() in Access | 10/8/2010 |
| Q: I am using Access 2002 and am trying to convert date data imported from Excel. The data is in the ... A: First, you are saying that in Excel the data is stored as a text value that is the count of the ... | |
| Running VBA in Excel from Access | 10/6/2010 |
| Q: I am using Access 2002. I generate a .csv file from a proprietary company program that provides ... A: You can use the TransferText method to link to the csv file. If the csv is always the same name and ... | |
| Running VBA in Excel from Access | 10/6/2010 |
| Q: I am using Access 2002. I generate a .csv file from a proprietary company program that provides ... A: I would eliminate Excel completely. I would link to the CSV file then delete the data from the ... | |
| Report Problems | 10/4/2010 |
| Q: I am having strange problems with printing my report. I have a "print report" button that should ... A: Check your WHERE condition. I suspect that you are using a combobox to display the name of the ... | |
| date in excel to access | 10/2/2010 |
| Q: I have a external excel file PSNND.txt. I made an external link in access ttxtPSNND. In orde for ... A: First a txt file is not an Excel file. It may be able to open in Excel but generally its plain text. ... | |
| Filter by Date Range on Form | 10/1/2010 |
| Q: How would I create a form to pull data based on a date range? I have a query to pull all claims ... A: In the form header add two text controls; txtStart and txtEnd. In the query set the criteria to: ... | |
| Bound column setting doesn't work? | 9/30/2010 |
| Q: I build a tblCurrent Program of which a field called PRODUCT ID needs to be filled in. So in the ... A: Google the phrase 'natural vs surrogate keys'. You should find some discussion on the differences ... | |
| Display combo box selection in subsequent forms | 9/30/2010 |
| Q: I am new to Office Access 2007. I have multiple forms in my Access file. However, I could not figure ... A: First After Update is an event and you don't want to assign a value to it. Second, if use a ... | |
| Database Design | 9/30/2010 |
| Q: I am having trouble with the following design. A Department has a single manager and has many ... A: The only reason to include IsManager in the Employees table is to filter a combo to select just ... | |
| Bound column setting doesn't work? | 9/30/2010 |
| Q: I build a tblCurrent Program of which a field called PRODUCT ID needs to be filled in. So in the ... A: Again, a combobox displays the first NON zero width column. So given your setup, it will display the ... | |
| Bound column setting doesn't work? | 9/29/2010 |
| Q: I build a tblCurrent Program of which a field called PRODUCT ID needs to be filled in. So in the ... A: First, I do NOT recommend doing lookups on the table level. This causes more problems then its ... | |
| Follow up to another question on "Option group values showing as the words in reports" | 9/28/2010 |
| Q: You wrote: "Create a query that joins your data table to this look-up table with the field in the ... A: There are different ways to deal with this depending on the number and nature of the the options in ... | |
| Combo Box Unique values | 9/26/2010 |
| Q: I have the following tables & fields: PersonTypes -PersonTypeID -PersonType Employees -EmployeeId ... A: Simple, have ONE people table: tblPeople PersonID (PK Autonumber) PersonTypeID (FK) FirstName ... | |
| Expression Builder | 9/25/2010 |
| Q: I am trying to build an expression when a grade is equal or greater than 60%, it figures out the ... A: First, you don't need the second IIF since that would be the result if the condition isn't True. ... | |
| proper and uppercase in access | 9/25/2010 |
| Q: Scottgem I am zulfiqar from pakistan and I have a problem in access database Sir in my data ... A: You are going to need to use VBA in the After Update event of the control. Something like this: Dim ... | |
| Access 2007 basic form setup | 9/25/2010 |
| Q: I've set up a many:many relationship between table A and table B by creating two one:many ... A: First, this is the correct way of handling a many to many relationship. Table c is generally called ... | |
| Access | 9/24/2010 |
| Q: and i am facing lots of problem in that 1. i can't able to create subform 2. i have create add ... A: 1. There are two ways to create an embedded subform. I prefer to use the Form wizard and create my ... | |
| Display combo box selection in subsequent forms | 9/23/2010 |
| Q: I am new to Office Access 2007. I have multiple forms in my Access file. However, I could not figure ... A: Ahh now I see. sounds like you want a login form. I've written a blog about this at ... | |
| Updating Through Forms | 9/23/2010 |
| Q: I have a form based on a query. I would like to be able to edit data in this particular form; ... A: I suspect your query joins multiple tables. This often results in an uneditable query. I do not ... | |
| Sending emails from Access multi-list form | 9/23/2010 |
| Q: I can do a form with a click button for a routine that will send an email to the person on that one ... A: The loop is fairly simple: Dim db As Database Dim rs As Recordset Dim strEmail As String Set ... | |
| Advantages and disadvantages of macros | 9/23/2010 |
| Q: What are the advantages and disadvantages of macros in access A: Depends on what version of Access. Also depends on whether you are asking Macros vs VBA or macros vs ... | |
| Sending emails from Access multi-list form | 9/22/2010 |
| Q: I can do a form with a click button for a routine that will send an email to the person on that one ... A: Access 2007 VBA is not very different from previous versions. What you need to do is loop through ... | |
| Two tables, one form | 9/22/2010 |
| Q: I'm a total Access novice and I suspect there might be a lot I'm doing wrong. I've started this ... A: First, you should not use a multi table query as the Recordsource for a form. Usually this creates ... | |
| Setting values to Combo Boxes on a seperate form | 9/22/2010 |
| Q: I recently worked out how to set values to unbound combo boxes using code like If Check0 = True ... A: Actually that helped greatly. The first part of my answer is you need to be careful when adding or ... | |
| Display combo box selection in subsequent forms | 9/22/2010 |
| Q: I am new to Office Access 2007. I have multiple forms in my Access file. However, I could not figure ... A: A combo box is a control. Controls are used to display data by setting the control source or the ... | |
| Setting values to Combo Boxes on a seperate form | 9/22/2010 |
| Q: I recently worked out how to set values to unbound combo boxes using code like If Check0 = True ... A: If you are running out of room on a form, then there may be an issue with your table design. Instead ... | |
| compare password entered to DB then place signature BMP | 9/21/2010 |
| Q: Advanced user with some Visual Basic. Working in 2003, but also have 2007 & 2010 available. I'm ... A: First, I hope you don't have the signature images as an OLE field. This will cause your database to ... | |
| query | 9/21/2010 |
| Q: .. I made a form where I enter volunteers (by name) that have participated in a particular service ... A: You can do all that you want, but you need to make some adjustments to your forms. Let me explain. ... | |
| Counting Fields with Text in Form | 9/21/2010 |
| Q: I am trying to count the number of fields with an "X" or are not null out of 56 fields, then ... A: Your problem seems mostly due to structure. You made a common error among newbie database designers ... | |
| IF/THEN in a query | 9/21/2010 |
| Q: .. I made a form where I enter volunteers (by name) that have participated in a particular service ... A: The problem here is in your structure. Your database is not properly normalized. Normalization is an ... | |
| linking a particular filed with ms word file | 9/20/2010 |
| Q: I HAVE A DATABASE IN MS ACCESS 2007. MY OPERATING SYSTEM IS WINDOWS XP. ON THAT DATABASE I MADE ONE ... A: First please don't type in all caps. What you want to do is create a query that returns all records ... | |
| substracting inventory | 9/20/2010 |
| Q: I have the same problem as Sam on his question about getting the quantity on hand, but I need to get ... A: I answer dozens of questions daily, so without a link or quote, I really don't know exactly what I ... | |
| MS Access 2007 Combo Box | 9/17/2010 |
| Q: I am trying to use a combo box to select a column name in my report sql. something like this... ... A: OK so you want to filter the report when the field selected in combo1 is less than the value ... | |
| MS Access 2007 Combo Box | 9/17/2010 |
| Q: I am trying to use a combo box to select a column name in my report sql. something like this... ... A: So Combo1 contains a field name? But this doesn't answer where you are using the SQL. If I'm ... | |
| Access Emails | 9/17/2010 |
| Q: I am using Access 2007 and would like to be able to collect information using the email data ... A: I haven't used this feature yet, so I just ran through the wizard. As part of the wizard you can ... | |
| MS Access 2007 Combo Box | 9/17/2010 |
| Q: I am trying to use a combo box to select a column name in my report sql. something like this... ... A: You can't compare 2 values on a form like that. The syntax for a WHERE clause is: fieldname ... | |
| Updating a column | 9/15/2010 |
| Q: I'm using Microsoft Access 2003. I have a table with an 'ID' column that is a series of sequential ... A: Looping is a key concept in programming. A loop performs a process for a defined number of times. ... | |
| Updating a column | 9/15/2010 |
| Q: I'm using Microsoft Access 2003. I have a table with an 'ID' column that is a series of sequential ... A: If you want to renumber records to create a sequential numbering without gaps, you can do this from ... | |
| Finding Edited records | 9/15/2010 |
| Q: I not a complete newbie at access but I've been out of the field for a couple of years and have ... A: You don't update the table. ALL interaction with a table should be done through a form. if you use ... | |
| Print multiple copies based on a quantity field | 9/14/2010 |
| Q: first, i had to enter a new question because when I clicked ask a follow up screen said I had too ... A: Nope that won't work. As I recall, you wanted to run these labels for several records in a query. So ... | |
| Print multiple copies based on a quantity field | 9/14/2010 |
| Q: You helped me with a problem before so I'm turning to you again! I want to print multiple copies ... A: Sound like you entered something in the wrong place. The code from the article should go in a global ... | |
| subtraction from same field | 9/13/2010 |
| Q: I'm trying to build a Vacation Leave database but I am stuck. Apart from the usual information I ... A: Your initial problem is your structure. You need at least two tables here: tblEmployees EmployeeID ... | |
| Print multiple copies based on a quantity field | 9/12/2010 |
| Q: You helped me with a problem before so I'm turning to you again! I want to print multiple copies ... A: Nope, Cartesian joins are something to be avoided, not exploited. You want a query to produce the ... | |
| Print multiple copies based on a quantity field | 9/12/2010 |
| Q: You helped me with a problem before so I'm turning to you again! I want to print multiple copies ... A: I didn't say to "input" the quantity. I said to "assign" the value. If you have a query with ... | |
| Print multiple copies based on a quantity field | 9/12/2010 |
| Q: You helped me with a problem before so I'm turning to you again! I want to print multiple copies ... A: This article can help: http://support.microsoft.com/kb/231801 In the LabelSetup function you would ... | |
| Importing excel file into current Access table, to update records | 9/12/2010 |
| Q: I'm currently working on the import features of my database in Access 2007. I want to be able to ... A: There's your problem. You have no Join connecting Test with TestTemp. So it updates each record to ... | |
| Importing excel file into current Access table, to update records | 9/11/2010 |
| Q: I'm currently working on the import features of my database in Access 2007. I want to be able to ... A: First you run an Update query. You link the main table with your import table on the key field. You ... | |
| Access query criteria | 9/9/2010 |
| Q: Scott I have 7 fields in my query from a Table. [Number ](auto), [Surname], [1st Name], ... A: Your problem here is in database structure. You made a common mistake by defining data based on ... | |
| merge data to tables in access | 9/9/2010 |
| Q: We have set up an Access database with tables x,y, & z where we would like to have multiple people ... A: The problem you have is in defining duplicates. There are two options here. One is using Replication ... | |
| Access database | 9/8/2010 |
| Q: I have a customer table and on the "Name" field I can't put any commas "," or quotation marks. I ... A: This is too complex for a validation rule but you can do it in the After Update event of the control ... | |
| Access 2007 VBA | 9/5/2010 |
| Q: given a table having four fields, say product, season, category, and year; the first three fields ... A: Access is an object oriented platform. Every object has properties. The environment is manipulated ... | |
| Access 2007 VBA | 9/4/2010 |
| Q: given a table having four fields, say product, season, category, and year; the first three fields ... A: Linking a form to a table is done by setting the Recordsource of the form to the table. This is ... | |
| Access 2007 - Currency Exchage Rate | 9/1/2010 |
| Q: Scott, this is a continuation of my previous queiries re Access 2010. Apparently, I have to "ask a ... A: In Query Design mode create a query that returns the records you want to update. Then set the query ... | |
| #error message | 8/30/2010 |
| Q: I am trying to run this expression in a query and it gives me the #error message and yet I don't see ... A: I still say you should change the field name. But try: Plan B Service: IIf((([SumofActual ... | |
| If Statements | 8/30/2010 |
| Q: It seems like the beginning of the line ">=11" is being picked up and applied during execution of ... A: Yes, the Correct syntax is If LEVEL3 = "05-BLUEFIN GR 1" OR LEVEL3 ="06-BlUEFIN GR2" OR LEVEL3 ... | |
| VBA code to iterate | 8/30/2010 |
| Q: This is follow up to my earlier question-You mentioned RolePair- Is this a single field? Role ... A: You currently have 2 fields each with a role in it. What I'm saying it to have ONE field to identify ... | |
| VBA code to iterate | 8/29/2010 |
| Q: I have a table that lists role combinations. There are two columns Role A and Role B. The table ... A: I understand the results you want, you don't have to keep repeating them. I mention that because ... | |
| VBA code to iterate | 8/29/2010 |
| Q: I have a table that lists role combinations. There are two columns Role A and Role B. The table ... A: Again, this can be done with only a query. But I need to understand something here. So each role ... | |
| VBA code to iterate | 8/29/2010 |
| Q: I have a table that lists role combinations. There are two columns Role A and Role B. The table ... A: First, you do not need VBA or a second table. This can be done with a query only. However, I don't ... | |
| Help? Need Text organization program that allows multiple keyword searching | 8/29/2010 |
| Q: I have been searching for a text (and possibly graphics) organizational program that will do what I ... A: There used to be a program called Ask Sam that was a text database that sounds like it might work. I ... | |
| Access2007: create subform which will look like additional fields to the main form | 8/27/2010 |
| Q: Scottgem, could you please advise on the following: I need to create an input form which would ... A: If you have to enter data, using a multi-table query could cause problems. There is a difference ... | |
| Access2007: create subform which will look like additional fields to the main form | 8/27/2010 |
| Q: Scottgem, could you please advise on the following: I need to create an input form which would ... A: I thought that might be the case, but wanted to be sure. The way I would handle this is to make your ... | |
| Access2007: create subform which will look like additional fields to the main form | 8/27/2010 |
| Q: Scottgem, could you please advise on the following: I need to create an input form which would ... A: I need to understand your structure to see what I think would be best. Table2 is your users table, ... | |
| Inventory Management | 8/27/2010 |
| Q: I'm creating a summary query that looks for data from three tables. One table is for purchases where ... A: I don't see the requirement for a separate table. If you need to record which purchase the issue is ... | |
| Inventory Management | 8/26/2010 |
| Q: I'm creating a summary query that looks for data from three tables. One table is for purchases where ... A: I think your database is not designed properly. You should have a SINGLE transactions table that ... | |
| If Statements | 8/26/2010 |
| Q: It seems like the beginning of the line ">=11" is being picked up and applied during execution of ... A: Try it this way: If LEVEL3 = "05-BLUEFIN GR 1" Then MF3 = 55 * 9 SELECT CASE [Swimmer3 Age] ... | |
| Access expressions | 8/26/2010 |
| Q: Scott I have two controls on my form which contain the number of the year ie.(1999). A third control ... A: Hmm, I just tested these on a form I have that contains a date range and they both worked perfectly. ... | |
| Access - mulit users also updating at same time | 8/26/2010 |
| Q: I found this site by googling my problem and your answers have helped so far. We have a membership ... A: EVERY multi-user application needs to be split between a back end (the tables) and a front end ... | |
| Access expressions | 8/26/2010 |
| Q: Scott I have two controls on my form which contain the number of the year ie.(1999). A third control ... A: First, I would recommend that you use the DateDiff function instead so this will be the most ... | |
| Saving values in combo boxes | 8/25/2010 |
| Q: I have been looking for an answer to my basic problem (being a beginner) but I still haven't found ... A: Ok, we are back to my original solution. The only way you can save the value for an unbound control ... | |
| Saving values in combo boxes | 8/25/2010 |
| Q: I have been looking for an answer to my basic problem (being a beginner) but I still haven't found ... A: I think I understand. First, are you saying that whenever you open the form, it opens to a blank ... | |
| Access database | 8/25/2010 |
| Q: I have a product table that contains over 1200 products and each has a unique item number that is ... A: Sure, Link to the table or data file your supplier provides and do an UPDATE query. Open Query ... | |
| Access form and combo box | 8/24/2010 |
| Q: Scott I have a 'Members' form which I have copied and pasted as 'PilgrimMembers' as I want its ... A: I'm sorry I misunderstood, If you used the wizard to create the combo, it should work properly. So I ... | |
| Access form and combo box | 8/24/2010 |
| Q: Scott I have a 'Members' form which I have copied and pasted as 'PilgrimMembers' as I want its ... A: If you are referring to a "search" combo using the 3rd option of the Combobox wizard, this is normal ... | |
| Saving values in combo boxes | 8/24/2010 |
| Q: I have been looking for an answer to my basic problem (being a beginner) but I still haven't found ... A: I'm not sure I'm following you. Do you want that the values from the last record you entered be ... | |
| Access 2007 -Currency Exchange Rate | 8/23/2010 |
| Q: I'm learning how to use Access 2010 but know that 2007 is fairly identical so I pose this question ... A: I'm confused. If are selling an item that requires a currency exchange, there are two ways to handle ... | |
| Updating One Child Updates All | 8/23/2010 |
| Q: I have a form with a parent-child relationship based on an autonumber field in the parent. I have ... A: Your problem is that you are using a continuous form so an unbound control won't work. You also do ... | |
| Recieved transactions | 8/20/2010 |
| Q: Currently I have a database with which to make Purchase Orders that then proceed to invoice and ... A: Your transactions table should handle all transactions, whether incoming or outgoing. That's what ... | |
| Passing variables between different forms | 8/19/2010 |
| Q: I'm kind of piggy-backing off a question that was already answered here. Someone asked what a good ... A: So, have an unbound control on the second form to store the user ID (not the name). Not sure why ... | |
| Passing variables between different forms | 8/19/2010 |
| Q: I'm kind of piggy-backing off a question that was already answered here. Someone asked what a good ... A: You can close the form, after capturing the data if you want. Or you can use the OpenArgs argument ... | |
| how to stock control ingredient while making restaurant database at access | 8/19/2010 |
| Q: i am trying to make a restaurant stock control system, but can not find a solution that how do i ... A: This is called a Bill of Materials function. You have to create a table that contains a record for ... | |
| Auto Generate a Number | 8/16/2010 |
| Q: I am trying to do something similar to a question that you answered on 3/17/2009 about auto ... A: Ok, First, you don't use Build Event, you use the Code Builder to enter the code. Second, what you ... | |
| MS Access 2007 stock control database | 8/16/2010 |
| Q: I am creating a database system for a small business in MS Access 2007. The database will control ... A: No, That is done by using queries and/or filters. You can have a control on the form to filter by ... | |
| Access 2007 Form | 8/14/2010 |
| Q: I have a form that includes a subform. The form contains vehicle information (from Vehicle Table) ... A: Use the actual name of the report. Also, is WorkOrderNumber a numeric Data Type? Finally this code ... | |
| Access 2007 Form | 8/14/2010 |
| Q: I have a form that includes a subform. The form contains vehicle information (from Vehicle Table) ... A: Create a button to print current work order on the subform. In the code behind the button use: ... | |
| combining two fields and truncating the first if both don't fit | 8/13/2010 |
| Q: I'm using an expression to combine a [title] field and an [author] field onto one line in a report ... A: The report should be based on a query. Add a column to the query with the expression: NewTitle: ... | |
| combining two fields and truncating the first if both don't fit | 8/13/2010 |
| Q: I'm using an expression to combine a [title] field and an [author] field onto one line in a report ... A: Well the only way to do this is to put a character limit on it. So I would use a custom function ... | |
| MS Access 2007 stock control database | 8/13/2010 |
| Q: I am creating a database system for a small business in MS Access 2007. The database will control ... A: Ok, First, you have a couple of design issues. You don't need tblDispatch, Every movement of stock ... | |
| Auto Generate a Number | 8/12/2010 |
| Q: I am trying to do something similar to a question that you answered on 3/17/2009 about auto ... A: Did you read my blog entry? Assigning the AP# is not done in the properties of that control. You ... | |
| Custom number conversion functions (cont'd) | 8/12/2010 |
| Q: My database includes geographic coordinates, latitude and longitude. To be compatible with other ... A: Doesn't seem counter intuitive to me. Longitude and Latitude have a specific format: Degrees, ... | |
| Custom number conversion functions (cont'd) | 8/12/2010 |
| Q: My database includes geographic coordinates, latitude and longitude. To be compatible with other ... A: Ok, compatibility with Google Earth and MatLab I understand. So why can't you go the other way? Why ... | |
| Access 2007 -Currency Exchange Rate | 8/12/2010 |
| Q: I'm learning how to use Access 2010 but know that 2007 is fairly identical so I pose this question ... A: First, your question really has nothing to do with whether you are using Access 2010, 2007 or ... | |
| Number conversion function | 8/12/2010 |
| Q: I am showing a number of geographic locations on a continuous form. My data in the table is a ... A: Correct, you will not be able to update txtlatitude. What you should have is a control bound to ... | |
| Auto Generate a Number | 8/11/2010 |
| Q: I am trying to do something similar to a question that you answered on 3/17/2009 about auto ... A: I've written a blog on this subject with more detail than may have been in that answer you saw. You ... | |
| Number conversion function | 8/11/2010 |
| Q: I am showing a number of geographic locations on a continuous form. My data in the table is a ... A: Yes a continuous form is a single set of controls displayed multiple times. So unbound controls ... | |
| Queries or Macro | 8/9/2010 |
| Q: The task that I want accomplished is having the deposits and withdrawals of a Raw Materials account ... A: When you open Query Design mode it prompts you for a table or query. Select the new query. Or you ... | |
| Queries or Macro | 8/9/2010 |
| Q: The task that I want accomplished is having the deposits and withdrawals of a Raw Materials account ... A: Create an interim query with a column like so: AdjQty: IIF(TransationType] = ... | |
| Login Code | 8/8/2010 |
| Q: **UPDATED** Couldn't post another follow up question.Windows Vista MS Office Access 2007 VB 6.5 I ... A: First, you have two sets of code, whihc one is actually being used. I assume you have a Login ... | |
| automating fill in accees forms | 8/7/2010 |
| Q: I fiil in a access form to generate a report. Sometimes I have to fill in the same form however with ... A: This helps a bit, but I still have questions. First, is this form bound? Are all the controls on ... | |
| Login code | 8/6/2010 |
| Q: Windows Vista MS Office Access 2007 VB 6.5 I have: Table called Customer(containing multiple ... A: Ahh this makes a little more sense. If I follow what you are doing, you are asking the user to enter ... | |
| Login code | 8/6/2010 |
| Q: Windows Vista MS Office Access 2007 VB 6.5 I have: Table called Customer(containing multiple ... A: Are you using VB or VBA? This looks like VBA code not VB. Try changing DLookup to: If ... | |
| Format time remaining | 8/5/2010 |
| Q: I'm tracking certification expiration dates in an access database. I am trying to add a field in a ... A: You have to do this with a custom function. Something like this: Public Function ... | |
| Lookup wizard - adding item that is not on list | 8/5/2010 |
| Q: I am currently in the process of trying to design a fairly basic database for my company, so that we ... A: Yes, but first I need some info. What is the RowSource of your Combobox. And what is the bound ... | |
| Database Design | 8/5/2010 |
| Q: Subject Database Design QuestionHi Scott, I am designing a database to track Contracts, Purchase ... A: No. Again BillingID is your link between the Invoice and either the contract or req. For example ... | |
| Database Design | 8/5/2010 |
| Q: I am designing a database to track Contracts, Purchase Requisitions. I have the following tables. ... A: The BillingID would be your FK. It would contain the PK of either the contract or req whichever the ... | |
| Database Design | 8/5/2010 |
| Q: I am designing a database to track Contracts, Purchase Requisitions. I have the following tables. ... A: I would add BillingID as an FK in your Invoices table. This would tie the invoice to either a ... | |
| Help with customised sequential numbering | 8/4/2010 |
| Q: I'm setting up a database to record all incoming requests and all request need to be formatted as ... A: As my blog says, that is something YOU need to decide. You need to run the code immediately before ... | |
| Form Fill In | 8/3/2010 |
| Q: I have a table with Zip Code, City, Area Code, State Code, and State Name in it. I want to have a ... A: That doesn't answer my question, but it would appear that there is only one record per Zip Code. In ... | |
| MS Access 07 | 8/3/2010 |
| Q: .I have a database that has 2 rows of data, one for Credits, one for Debits..I need to sum the two ... A: You need use aliases then. You need to add the table into your query as a self join. Then use ... | |
| Concatenate Rows to 1 Column- Access 07 | 8/3/2010 |
| Q: Is there a way to concatenate multiple rows of 1 field into a single column using SQL? I do not know ... A: Not using SQL, but here is a function that will work: ... | |
| Access 2007 data import | 8/3/2010 |
| Q: I need to import data from another Access 2007 database, however, I do not want to import any data I ... A: You can't do it in the Import process. You have a couple of options. You can link to the table ... | |
| Field limit to MS Access tables | 8/2/2010 |
| Q: When I use the Analyze Table wizard and select that I want to select the fields for each table on my ... A: That limit may be a function of the wizard. You can have many more fields if you need them. But a ... | |
| Access 2007 data import | 8/2/2010 |
| Q: I need to import data from another Access 2007 database, however, I do not want to import any data I ... A: Only partially. Your problem is that names and addresses can be duplications. You can create a ... | |
| Changing RecordSource of Report | 8/2/2010 |
| Q: I have three queries that display the same data filtered on one field "Active". One is for "Active ... A: First I would change this to Option box instead of command buttons. Then you need one control, one ... | |
| Subquery to calculate the YTD amount | 8/2/2010 |
| Q: Can you offer any assistance? Code SELECT GetFiscalYear([InvoiceDate]) AS [Fiscal Year], ... A: Did you do what I suggested? I just noticed a mistake I made in editing your queries, the second ... | |
| error 3709 in search form | 8/1/2010 |
| Q: I have form for edit products I have this code behind search cmdbutton, when I select the product ... A: You need to step through the code and find out what the value of strWhere is before you assign it as ... | |
| sequential numbering | 8/1/2010 |
| Q: I am developing a database for my school with ms access but i don't know how to automatically assign ... A: For the first issue, All you should need to do is sort a query by grade. For the other two I've ... | |
| Subquery to calculate the YTD amount | 7/31/2010 |
| Q: Can you offer any assistance? Code SELECT GetFiscalYear([InvoiceDate]) AS [Fiscal Year], ... A: I would do this in two steps: qryTransbyFY SELECT GetFiscalYear([InvoiceDate]) AS [Fiscal Year], ... | |
| ACCESS - making a report from a query | 7/31/2010 |
| Q: Sirs I'm a bit of a newbie here, I'm using Access 2003 with Vista. I have 2 tables: tblPatients ... A: First you really should have three tables here. I say this because a visit can involve multiple ... | |
| Hyperlink code to a shortcut database | 7/30/2010 |
| Q: I am using Access 2007 on Win XP. I have been successful in creating the hyperlink by using ... A: I'm unclear what you want to do. If you are trying to open an Access application from within another ... | |
| Access 2007 Dlookup, Formulas and Combo boxes | 7/30/2010 |
| Q: You have helped me and thousands of others so I figured you are the man to ask - I am using Access ... A: I assume this form is just for doing the calculations and displaying the results. Otherwise I do not ... | |
| question related to auto populate | 7/29/2010 |
| Q: I am creating a database. It is to manage a system to keep track of various documents. I have two ... A: DoCmd.Menuitem was an old method of executing menu commands. The numbers represent the various menu ... | |
| Select most recent data | 7/29/2010 |
| Q: Help! I am working with a vehicle database. The vehicle mileage is entered monthly, only if ... A: What you want to do is a Top N of group query. If you search Microsoft's Knowledge base, you will ... | |
| Custom AutoNumber | 7/29/2010 |
| Q: I have created a custom autonumber in a table. Example: "845 - 0000". I want 845 - to print in ... A: First an Autonumber is an Access datatype that automatically generates a unique number for each ... | |
| Report Building | 7/28/2010 |
| Q: Hey Scott, Again thanks for the help yesterday the database looks great. Now I'm on to building a ... A: Your first step here is to design a query that has all the information you want on the report. You ... | |
| Access 2007 updating fields | 7/28/2010 |
| Q: First I am a user not a programer. We have an application that calculates two fields when entering ... A: Well first, Beginning Balance should not be stored. Its a calculated value and, as such, should be ... | |
| Matching partial records | 7/28/2010 |
| Q: I need a query to match texts which are in Table 1 to that of Table 2. There's no common field in ... A: If the pattern is 5 numbers and 2 letters and you onyl need to match the first 5 characters this is ... | |
| Link Tables and Forms | 7/27/2010 |
| Q: I am working with a vendor that has a database and wants 3 others to have open access to edit/add ... A: By default, Access does not create exclusive links. So its hard to understand why this is happening. ... | |
| Preventing the Design View in Access 2007 | 7/27/2010 |
| Q: Is there a way to prevent users from having the Design View access? A: Save the application as an mde/accde file. Hope this helps, Scott<> Comment - Thanks, I guess I ... | |
| Dlookup / Subforms / Linking Forms | 7/27/2010 |
| Q: I have a few questions, I'm a summer intern and I'm reworking my companies database. I currently ... A: You want to use the DoCmd.OpenForm command with the add option. If the Default value of the control ... | |
| Dlookup / Subforms / Linking Forms | 7/27/2010 |
| Q: I have a few questions, I'm a summer intern and I'm reworking my companies database. I currently ... A: 1) What is the RecordSource of the subform? Is it linked properly to the main form? could OrgID be ... | |
| Footer calculations | 7/27/2010 |
| Q: I have an Access 2003 report listing student names and their report card grades - A, B, C, D, F. ... A: I referred to queries in my response. The function is created in a global module and referenced in ... | |
| Reset line no to 0 each month | 7/27/2010 |
| Q: I have not worked with Access database for about 4 years and I am trying to figure out how to do the ... A: You don't bind a control to VBA code. The Controlsource of a control is either a field int he ... | |
| Queries in MS Access | 7/27/2010 |
| Q: I have a table that has multiple fields. Name Test1 Test2 Bob 40 70 Sam ... A: You have to set the criteria in the column for that field. What does the SQL look like or can you do ... | |
| Footer calculations | 7/26/2010 |
| Q: I have an Access 2003 report listing student names and their report card grades - A, B, C, D, F. ... A: Are the grade entered in the same field? If so, I would create a Function that returns the ... | |
| Link Tables and Forms | 7/26/2010 |
| Q: I am working with a vendor that has a database and wants 3 others to have open access to edit/add ... A: So you have a front end with forms and a back end with tables. The front end is linked to the back ... | |
| Populate table date field based on form field | 7/26/2010 |
| Q: In a form PatientInformation (base on a table PatientInfo), I’ve inserted the following tblMoRpt ... A: I'm not sure prepopulating the table is the best idea. Can't you just check the last date of a ... | |
| Update query using max of field in related table | 7/25/2010 |
| Q: I have a table that has a field CHILD_NO which is supposed to be the number of children in the ... A: You are missing my point. You don't need to make any changes. If you use the expression I gave you ... | |
| Update query using max of field in related table | 7/24/2010 |
| Q: I have a table that has a field CHILD_NO which is supposed to be the number of children in the ... A: This is a prime example of why you should not store calculated values. Also, a sequential number ... | |
| Report based query with Color | 7/23/2010 |
| Q: I have a Report that has 4 sub Reports all based off query. I would like to change the color value ... A: What is MCL? If you are looking to change the color for a specific record, you need to reference the ... | |
| searching | 7/23/2010 |
| Q: I am trying to create a form that contains a search button that searches a table and then displays ... A: It would be easier to just use the Filter by Form feature. Also, if your database is constructed ... | |
| access database form design. | 7/23/2010 |
| Q: Scott, I am setting up a database for scheduling service people to several customers per day. I ... A: You need at least three tables. You need a table of service people, one of customers and a ... | |
| Access 2007 updating fields | 7/23/2010 |
| Q: First I am a user not a programer. We have an application that calculates two fields when entering ... A: And again, I need to know HOW those calcs are being performed. Clearly it would appear to be a ... | |
| Access 2007 updating fields | 7/23/2010 |
| Q: First I am a user not a programer. We have an application that calculates two fields when entering ... A: First we need to deal with terms here so we can understand each other. Fields are logical objects ... | |
| Macros | 7/22/2010 |
| Q: Please help me out, i have a small database with a login screen, what im trying to do is to get the ... A: I'm not a fan of using a time expiration. Too often people get too busy and can't spend enough time ... | |
| Design | 7/22/2010 |
| Q: I am setting up a Database to track invoices and I am having issues with the Design. I have ... A: OK, First, I understood why you separated BC and EC, but if you think about it, you still have to ... | |
| Creating a Validation Rule | 7/22/2010 |
| Q: I am trying to create a validation rule for a number data type field. My rule would only allow ... A: I don't think this can be handled by a simple Validation rule. So what I would do is use the After ... | |
| Desing | 7/22/2010 |
| Q: I am setting up a Database to track invoices and I am having issues with the Design. I have ... A: So you are saying that amounts under Budget code 123 can have Expense code aa and amounts under ... | |
| Desing | 7/22/2010 |
| Q: I am setting up a Database to track invoices and I am having issues with the Design. I have ... A: If I follow your design, the total amount of the invoice is split so that a portion of the invoice ... | |
| Clearing fields in a form | 7/22/2010 |
| Q: We are using access to trial some patient data collection from Nurses. Of necessity, there is one ... A: Somehow, your structure doesn't appear correct. Why would you be focused on the bed rather than the ... | |
| Reset line no to 0 each month | 7/21/2010 |
| Q: I have not worked with Access database for about 4 years and I am trying to figure out how to do the ... A: A control is an object on a form or report that displays data. It may be bound to a field in a table ... | |
| Macros | 7/20/2010 |
| Q: Please help me out, i have a small database with a login screen, what im trying to do is to get the ... A: Yes its possible, but it would be easier with VBA then a macro. But what is it you are trying to ... | |
| If-Then Statement / Substituting Text | 7/20/2010 |
| Q: I have a query field called "Task_Ctr." In it, I have certain records with "#". I want to substitute ... A: Sounds like you are putting your IIF in the criteria row. You need to put it in the Expression row ... | |
| I am a teacher | 7/20/2010 |
| Q: i am a teacher of database.what website or books do you think i need to really know this ... A: First, you need to define your scope here. Are you teaching databases in general or teaching Access ... | |
| Reset line no to 0 each month | 7/20/2010 |
| Q: I have not worked with Access database for about 4 years and I am trying to figure out how to do the ... A: Well first, you really should use a full date. You can format the date to display as only month and ... | |
| Emailing attachment with outlook | 7/20/2010 |
| Q: Hoping to get some help with this one, ive been working on this for a while now and have come to a ... A: OK, The problem is that the SendObject command is sending the report. So its running the report ... | |
| Updating table info | 7/19/2010 |
| Q: I have an MS Access database that houses two tables with data for our customers. Due to the large ... A: Allright, so the delta has complete records for the updated records. So you just run an update query ... | |
| database design | 7/19/2010 |
| Q: I have a project that each record with around 400 question (variables) by category with hard copies. ... A: OK, the way I set up a survey is like this: tblQuestions QuestionID (Primary Key Autonumber) ... | |
| Multiple-Index | 7/19/2010 |
| Q: I asked you some questions over the past couple of days about linking two tables. You recommended ... A: Did you create a MULTI-FIELD index. Sounds like you just created an index on 4 fields, but not on a ... | |
| Updating table info | 7/19/2010 |
| Q: I have an MS Access database that houses two tables with data for our customers. Due to the large ... A: First, when you refer to large size, are you referring in terms of records or fields or what? ... | |
| database design | 7/19/2010 |
| Q: I have a project that each record with around 400 question (variables) by category with hard copies. ... A: I need more specifics to be able to suggest something. When designing a database, my first step is ... | |
| Comparing different columns according to other's column value | 7/19/2010 |
| Q: Hey there! I was wondering if you can help me on this matter. I spent entire day as I'm not an ... A: Again, you should not have a structure like that. What happens when you run out of fields? There is ... | |
| between current date fields | 7/19/2010 |
| Q: not sure if this is possible but hoping for your assistance. I currently have a table in MS Access ... A: Sure, In the On Current event of your form you run code like this: IF Me.[Mntc Expiry From] < ... | |
| Emailing attachment with outlook | 7/19/2010 |
| Q: Hoping to get some help with this one, ive been working on this for a while now and have come to a ... A: If your code is working except that the attachment includes all invoices for the customer, then you ... | |
| Updating table | 7/18/2010 |
| Q: I asked you some questions over the past couple of days about linking two tables. You recommended ... A: You need to go back and reread my original answer. The removal of the duplicates occurs when you do ... | |
| Storing stock on hand everyday | 7/18/2010 |
| Q: I know that stock on hand should be calculated on the fly. I agree but I want to store the value for ... A: You're not listening to me. This is very easy to do. You run a query to get your QOH as of ... | |
| Comparing different columns according to other's column value | 7/17/2010 |
| Q: Hey there! I was wondering if you can help me on this matter. I spent entire day as I'm not an ... A: What is your table structure? If you have fields that arre named 2010June, 2010July, etc., then your ... | |
| Storing stock on hand everyday | 7/17/2010 |
| Q: I know that stock on hand should be calculated on the fly. I agree but I want to store the value for ... A: You do not need to store this. This is an example of why this should be calculated on the fly. If ... | |
| Linking two tables in Access | 7/16/2010 |
| Q: I am using Access 2003. I have two tables that I would like to combine into one table. There are ... A: Make the copy of one of the original tables. Just right c | |