I have an Access report that includes a sub-report in whihc I would like to print the records on one row, rather than one column. So far, I have been unable to find a solution.
Job Number: 123456
7 more replies
I have an Access database used on daily basis to enter and print the maintenance work order in a report format. I have assigned a command button to print the required record. On clicking the button in the Form, a parameter query will run asking the user to put the record number to proceed. Once the correct record number is entered in the parameter text box, the report is previewed and the user click on the print button to print the same.
My question is as follows:
Is there any method to find out the record i am going to print now was already printed before? A kind of record keeping for the printed records.
Also, a message box should warn the user saying incase if the record was already printed, ďThe record was previously printed, do you want to continue printing again?Ē
Please tell me a way to perform the above in my Form.
could't you just add a field in the record for printed?
Then if printeed true, show warning, else show printable form.
I have a problem with records disappearing on a report in Access 2007. This is with a fairly simple database to track overtime. It only happens when there are so many items in the report that it is forced to continue on a new page. The record that should be the first on the new page just is not there.
If I reduce the font size to squeeze everything into 1 page, there is no problem. When I set the size back to normal and it flows onto 2 pages, I have a missing record.
The SQL used to select the data is as follows:
SELECT Staff.FirstName, Staff.Surname, Staff.SemesterSubBank, Staff.StaffID, [Staff cover].StaffCoverID, [Staff cover].StaffCovering, [Staff cover].CoverDate, [Staff cover].Category, [Staff cover].Reason, [Staff cover].Lektionen, [Staff cover].Kontaktzeit, [Staff cover].Printed, [Staff cover].SubBankEligible, [Staff cover].Time, [Staff cover].Class, [Staff cover].Subject, [Staff cover].Notes
FROM Staff INNER JOIN [Staff cover] ON Staff.StaffID = [Staff cover].StaffCovering;
There is also some VBA to decide which of these records to display, and to do some adding up,
Public LektionenTotal As Single
Public KontaktzeitTotal As Single
Public MonthlyLektionenTotal As Single
Public MonthlyKontaktzeitTotal As Single
Public BankTotal As Single
Public StartDate As Date
Public EndDate As Date
Public RecordCounter As Long
'#################Change dates at bottom##################
'#####... Read more
9 more replies
I'm trying to make a monthly report showing inventory that has moved and the cost allocation between operational units within the company.
I only need the records with the latest date in the date field to populate the report - there are multiple records with the same item (TagID) that have moved from Group A to Group B over the course of the month. We want to make sure Group B is charged the cost from Group A.
All items in records have prices, locations, users, etc related from other tables, so it's just this one table with the moves that's my primary concern.
Any ideas on how to write the query for this?
I would suggest 2 queries, the first uses Totals and Group by on TagID and Max on the Date field.
Then the second query uses the first query and the Table (joined by TagID) to add the other data.
This report works ok for all records but my boss wants to be able to select just the records that he wants and print out the report. I know in the query I can do a "[Enter Record Number]" on the "WO" field" but that only works with one record number.
Can I make the query pull up a list/table of all the jobs in this table (With the first 3 columns displayed) and check the jobs I want and then the report would just pull up the selected jobs?
This is over my head. (OBP hope your out there)
I got it figured out kinda. If you are following this you can comment on my other post: “Access form, clear check boxes when closing”
I'm suffering from a bit of Friday morning madness and can't think or find help for a simple problem.
I have a report that details vehicles, and these are sometimes given a 'Decision'. I want a count at the foot of the report that counts only the number of instances of one of the specific decisions.
This fails because it counts all records with any decision (for which the decision field is not null).
So I tried:
This fails because it gives me a negative number - the number is correct but that negative is wrong. So, it's giving me -30 instead of 30.
I'm sure this is sooooo simple, and I'm sure I've done it before, but my head's rattling like a charity worker's collection tin.
Here ya go:
Create a textbox in the detail section of the report. Set the property as not visible, and Running total=yes.
Put an iif statement, something like:
=IIf([clearance code]=2,[cash value],0)
Then, in your report footer, you put a regular sum box, but use the iif as control source.
(You can drag the iif off to the right, make it as small as you want, so it doesn't interfere with your design view.)
Expired Topic: http://forums.techguy.org/business-applications/640860-access-2003-repeat-record-report.html
Microsoft Help Topic: http://support.microsoft.com/kb/207664
I've been gradually trudging and fumbling through creating my first database for my company, but I've finally ran into a considerable problem. The current process for our shipping department when they create shipping labels is open up the MS Word template (an old shipping label, lol) and enter the information (to, from, sidemarks, page # of #, etc.). For each item we ship out, it needs its own label even if it is 500 of the same item. After they've entered in the information they will then copy and paste it as many times as necessary (usually in the range of 1-500). There is usually 5 labels per page. In the end, they have to spend a great deal of time getting the formatting correct to ensure their are 5 labels per page so when they cut the labels, they don't cut the information on the label.
I would love to create a form/report combo that would incorporate this so all they have to do is fill in the shipping information and after keying in the number of labels they need, Access will create all the labels necessary! And apparently I'm not the only one who wants this feature because as I've linked above there are topics that exist already.
However, I'm stuck. I've done the Microsoft Topic solution multiple times now and still no luck. I am able to compl... Read more
16 more replies
I have a form that will be used to preview reports with the given combo box criteria. Everything was working perfectly with the first three combo boxes I made. The fourth one (which is blank in 264 of the 311 records) made the report only return 47 of the 311 records when I searched for all records (keeping all combo boxes blank). Is there a way to ensure that the total number of records are used in the filter? I hope my question is clear. (p.s. my OS is Windows XP) Thank you!
7 more replies
I have created a report from a query. Within this report I would like to have a text box to count the total records in a table. How would I create the control source to accomplish this?
is the control source to use.
Im using access 2010 and there is a print of a report like a certificate whenever needed. My office is requesting me to provide a report of all certificate printed including the date and to whom .
my only way to do this is to force users to enter a field print (yes/no) and from there the query "yes" and the report be printed.
Is there any way to do this without forcing the user to enter the yes/no. field.
6 more replies
I have an Access database used on daily basis to print maintenance work order. Each work order is assigned with an auto field No. I have a commend button assigned to print each record in a report format. On clicking command button, i enter the record number to be printed and it previews the report and check for accuracy of data entered. Once checked, i click on the print icon to print the same.
My question is, is there any method to find out the record i am going to print now is already printed before? A kind of record keeping of the printed records. Also, the same method shall help me to find out how many times each record is printed?
Hope someone can help me
8 more replies
how can i add a new record between two existing recorfs in access 2003 ?
thank you in advance
Sorry I do not understand your question, in what way will the record be "Between" 2 other records?
How can I remove duplicate records?
I have are report as such:
Emp No Name Hire Date Status Company/Subsidiary
7791 Anna Jackson 07/01/2002 Active Access
7997 Irene Hagedorn 08/01/2002 Active Access
7798 Alice Smith 08/01/2002 Active Access
7798 Alice Smith 01/01/2003 Active iSmart
Record Number 3: Displays a hire date of employee to 'Access' Company. Record 4: shows employee was employed in iSmart, a subsidiary of the main company.
The requirement of the report needs to display only record 4 and discard record 3 in the list.
What procedures should I do?
Thank you in advance,
Your query isn't filtering unique records because you also have the field Company/Subsidiary in it and it will always bring up both. Try filtering further using the most current date and then you will only get one record for each employee. From the looks of your table structure more than one employee with a unique ID number can work for more than one Company/Subsidiary...is that correct?
Dears,I have designed a database with many reports of certain importance. basically a report in my DB is one of two types; a Daily Report (requires a single criteria which is the date) and the second is a History report. To view or print a report, I have designed a Filter form to fill in with the report criteria and clicking a button to open the report.The first type (Daily R) work fine without problems. The second type (History R)has a problem. In this report, the criteria are two or three because a history report will retrieve data within a period, so I have to input (FromDate) and (ToDate)and may be adding another criteria which is called a company.In all history reports, the report header will accomodate the two input values of date and extract the rest of report items into the reprot detail area.The problem is, the desigened system doesnot respond well as expected. When I load the filter form to fill in criteria for a history report, I have input the FromDate and ToDate and selected the third criteria (if any), then, the reprot opens without data in it.on the other hand, I tried to load the report directly form opening it, inputing criteria, and it has worked fine without probelms.Would someone expect a key reason for this problem or dirtecly can solve this problem?Appreciate youtr support.Thanks in Advance.mhegazy
16 more replies
I am trying to put a combo box on a form that will allow the user to both scroll through the list and start typing the first name of the employee to find the desired name. Clicking on the desired name would open the appropriate record.
So far, I have successfully created:
A 'multi column' combo box, that shows the information that I want, called "cboSelectBEP"
The combo box lives on a form called "Basic Employee Profile"
The combo box is fed from a query called "Basic Employee Profile Query"
All the main data is kept on a table called "Employee Information"
What I seem to be missing is the following functionality:
The combo box shows the test record (ie the list) when you click on the arrow but does not allow you to type the employee's name in to search
When you do click on the test record, it doesn't actually go to any record - other than the default record - btw - how can I change that so the form comes up blank?
I think part of the issue lies in the fact that my query takes the First & Last names from the "Employee Information" table and puts them together in a 'full name' format. I know the topic of comboboxes with 'search while type' functionality has been covered many times before but I've been searching the internet for 3 days and can't seem to find where I'm going wrong! Any help is appreciated!
To make it easier envision the situation with the... Read more
It is a property setting on the form that is causing the problem.
I have added a find combo to my form in your original database that works fine.
I have a report, with 3 sub-reports in. It collects all the data for a particular SiteID. in Each of the sub-reports, there is a cost value. One for Hardware, one for Mobiles, and one for Phone lines. I have fields at the top of report which reflects the values and totals them up. Works great, except:
When i have a SiteID with one of the subforms being empty; i.e no mobiles on the site it displays fine in the report view, but when i go to print or publish as a pdf it strips out the subform with no value, and throws an error in my calculation. I need the calculation there, which means i need it to stop striping out the subform with no value. I however cannot see how to do this...its just bugging me, its fine inthe report view, just when you send it to print.
Would grateful for some help,
16 more replies
I just tried to learn access to create a database for healthcare testimonials for a new amazing anti-cancer drug my company came out with as well as for some of the other products we have . I wanted to create only 3 fields: Products, Condition and Data. I would cut and paste testimonials into the Data Field. I was able to do that using the wizard. I then expanded the Data field to accommodate room for testimonials in Design View. However, when
I tried to copy and paste a testimonial into the Data column in Datasheet view, I got a "the text is too long message." The text was only a paragraph.
It appears the program is seeing the record space as a field space which only allows for 255 characters, yet I appeared to follow the wizard in creating the form. I'm completely confused.
10 more replies
I know its me again!!! 1 more question, I created a a form with the fields I want. SO now I have records in it but I noticed that everytimeI open this form to enter new records, it defaults to the first record, and when I want to go add another record, I can just click on arrow to go to the last record. I would have to hit tab through all the fields on the first record before I can go to another record. What am I doing wrong? Is there a way to where if I opne the form to add another record, it will default to a blank record?
This will work as long as you are using a button on another form to open the form in question.
In design View pull up the properties for the button that opens the desired form. In the properties box go to event procedures and OnClick Where it says event Procedure Click.
You should then be presented with the VB code behind the button.
Change the Code so it looks similar to this(You may have to play around with it slightly i dont actually have access on this machine so im kindof remembering.
Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
Hope this helps
i just want to ask if there's a query where i can search for just one specific data from all of my database records, without opening each and every record?
cutiemae, yes of course, you can enter your search requirement in a Form and a Query will display the results on a Seperate Form or a Subform.
You can also do the same thing using Visual Basic to set the Form's Filter as well.
If you post a zipped copy of your database (it doesn't need any data in it) and tell us which Field(s) you want to search we can show you how.
Or alternatively if you want to try it yourself there are 2 really good Threads on here that show other Posters building there databases, including "Searching".
I have a database with "Customer purchase Orders" records made up of 3 tables: The order has multiple fields.
Order Header Table
Order Details Table
When entering a new order, if it is a repeat order, to save on data entry, I want to copy the contacts of the previous order and make a new order (exactly the same records in each field, except the Order Number ID obvioulsy) Is there anyway i can do this?
It all depends on how you design your form, I would think. This is sort of guessing at your DB's setup because your terminology is a tad confusing, but if your form has a header with fields for, say, OrderID and CustomerID (among other things), and if your customer data ("contacts of the previous order") is stored independently of the orders--looks like this is the case--then you could use the CustomerID field in the form's header to populate the customer contact info fields in the form's detail section.
Is this what you're talking about, approximately?
I don't know if this is possible but this is what I need to do.
I have a table that has the following info
Item #---------- Finished Good
What I need, is for another table or form, combine the finished goods into one row, seperated by either a comma (or something like that) or each Finished good put into seperate columns for the Item # it matches.
Item # -----------Finished Good
101 --------------10554, 10585, 10875
Item ---# Finished Good1 -----Finished Good2 ---------Finished Good3
101 ---------10554 ------------10585 ------------------10875
6 more replies
I have a need to count the number of employees that are assigned to an associate director. I have them identified in the Employee Info Table as Associate Director ID = 24 and 9. On a form called frmDirectors Report Date, I have a choice to pick which Director, then the printer function calls the report and subsequent query to limit the data. One calculation on the report need to know how many employees in total are assigned to the selected director in a calc that figures average employee hours. I cannot figure out how to add the count to the existing query or any other way of approaching this issue. HELP. I have included the MDB for reference.
I have already answered this question on your previous thread.
I have the following code in a text box on a form:
=IIf(DCount("*","[qryContactsExtended]","[ID]<>" & Nz([ID],0) & " And [ContactName] = '" & Replace(Nz([ContactName]),"'","''") & "'")>0,"Possible Duplicate","")
It just comes up saying #Name?
Thanks in advance.
Quite a complex string to troubleshoot visually, #Name often means Access can't resolve a field or control name. Fully qualifying the [ContactName].[Table1] if its in tables and queries could help. Also if the text box itself doesn't have a unique name that can cause the error.
Also the DCount function is very touchy (technical term ) about its syntax. You might pull it out individually and make sure its working correctly.
I want to sort records by day of the week. Monday should come first and Saturday should come last in a Query. How do I do that?
I have a table called "DAY" which simply has the days of the week from Monday to Saturday. Another table is called "Schedule" which contains all other data and gets the names of the days from the "DAY" table.
I have a query that searches for strings in the "Schedule" table and displays the results including the day of the week. Since the "Day" column in the Query is set for "Ascending", Friday always comes first in the queries. However, I want the records for Monday to come first, followed by Tuesday, Wednesday and so on.
I also have a report that displays the data based on the Query made. Again, the report sorts the names of the days alphabetically (All Fridays first, then all Mondays, then all Saturdays, then all Thursdays, then all Tuesdays, and then all Wednesdays). However, I would like the records to be sorted having all the Mondays first, then all the Tuesdays and so on. How do I do that? Thanks...
By the way, I am using Access 2002 (XP).
OK.. I have a database in Access 97. and it does everyting thing I want. the one thing I am having problems with is copying one record onto another. is this possible. the format of the database is as follows
you choose a customer. which will allow access the the subform on the same page call ordermain. with the key being order number. in this subform there will be details regarding an order , ie. date, contact etc. this will also have a subform linked via the ordernumber 1 to many ratio. called orderdetails on this subform you will have various items which are the main quote. what I would like to do if possible is copy the orderdetails subform onto a new record. how can I do this?
Also when I am using the orderdetails subform I have 4 boxes, item no. price description and qty. in the item no. I currently pu inthe number 1, 2,3 etc manually is there a automagic way of the database auto numbering the items in relation to the number of itmes I have on the subform.
I don't know what you mean by "copying the record"--i.e. where you want it to go. Should be as simple as running an append query with the appropriate criteria set, then maybe requerying the subform if you're trying to do it with the subform open.
As for setting the number automatically, you can use the BeforeInsert event of the form--this will not show on the form until the record is saved, but it will work correctly, together with the DMax() function, the appropriate criteria (ordernumber=x), and a + 1.
I'd suggest doing a little reading in Access help on append queries and DMax as a place to start. And you will have to be able to write (simple) macros to do it, so if you aren't familiar with them you should spend a little time learning about them in help.
Hi, I among my group of coworkers, am an access user. We have been using an older access database for a few months now, I have created a new database because the older one is becoming out of use. There was one problem in particular about the older database, it was located on a network drive, which 5 other users accessed to input records, it was a common problem where the database would lock up because more than one user was on the same record, is there any way I can work around this issue? I would like to put in this troubleshoot for the new db I have created.
Downwitchyobadself you can take a breather on this one bro, you helped me out in the past, if any other techie knows how to take care of this one I would appreciate your help.
Please always give version.
Anyway, Tools-Options, Advanced tab. Check the different locking levels.
(That's all until Down gets here, LOL)
I am using Access 2010. I want the user to be able to search by name or ID to to find out what systems they can access and to what level they can use the system.
This is determined by the department and employee level. I have a separate table for the users with their personal information and another table with the system profiles.
Any help would be appreciated.
You can do this with a query that combines data from the 2 tables.
Create a query based on the Employee table and then in design view add a criteria to identify the user then add the systems profiles.
You can then create a Form or Report based on this new query to display the data.
I understand VBA but I have only limited experience using it with Microsoft Access so maybe some of you can help! I have written a macro that takes information from an Excel workbook and uses it to populate a new record in an Access database table. The first field in the database table is an auto-number field. After inserting the new record I need to set a variable equal to the auto-number that the new record was just assigned. How is this done?
You can always create a recordset and extract the value of the last record in that particular table and add one to it and this will give you your number you are looking for.
something like strNewnumber = Max of (field_PKID) +1
I am trying to archive records that are displayed on a form from [video table] to a [deleted video table] from a button on the form using an append query. But just cant get it to work. It keeps coming back with 'about to append 0 rows' even though there is data in the video table.
Please can you help.
6 more replies
i have an access file that has two tables. one table lists all the foremen and the other has the inspections. i want to print a report that shows how many inspections each foreman has. some foremen will not have any inspections. the two tables are linked together already.
If the inspection table has the field references the foreman, then use the following select statement.
select foreman,count(foreman) from inspection table group by foreman
I am operating in Windows XP and switched from Office 2000 to Office 2003 about a year ago. I created a database for storing environmental data for my company near the end of 2005. I linked the tables in the database to worksheets in an excel file so that we could manipulate the numbers a little better, but have the convenience of the searchability of access. Everything worked fine until I switched over to 2003. When I went into my database it would not let me edit the data or enter new records. Since then, I've been having to logon to other computers in the office who still have Office 2000 to enter the info.
Well, two weeks ago all of the other computers in the office were switched over to Office 2003 and now, I can't get enter anything. I even recreated both the spreadsheet and the database and it still will not let me edit or add. Any ideas?
14 more replies
I am using Access 2003 and want to put the file (database) name and the report name in the footer of my report. I'm using =CurrentProject.Name as the control source in a text box of the footer to get the file name. Is there a command I can use to add the report name?
You could try this:
In the VBA code module (Class Module) for your report write a simple function (I've called it Get_Report_Name in my example):
Private Function Get_Report_Name() As String
Get_Report_Name = Me.NAME
In your report footer: add a text box at the position where you want to display the report name. In the Control Source property for the new text box enter: =Get_Report_Name()
This should display the report name when the report is run.
I wanted to verify that MS Access limitations is correct that you can not have more than 255 data elements in a table or in an active query.
I have an application with 1000 data elements so I divided it into 4 tables, for each table I created a FORM to add new records, I used the MS Access TAB and COMMAND facilities to create paths from FORM 1 to the other FORMS, but I can not pass the KEY from FORM 1 to the other FORMS?
11 more replies
I am trying to calculate the elapsed time between records as shown below and want to apply that elapsed time calculation to the first of the two records. In the example below, I would want a field ElapTim which would be 0:00:42 seconds on record one, 0:18:01 min / sec on record two, and 2:06:45 on record three. Time could be in seconds and I could convert to hours or minutes as desired. Data would be sorted by User and ascending date / Time.
Any ideas how this is done?
Thanks! Al Farrar
User Date Time Location
16494 9/10/2013 3:00:47 PM BSTRT
16494 9/10/2013 3:01:29 PM B035A
16494 9/10/2013 3:19:30 PM B063A
16494 9/10/2013 5:26:15 PM B104-W4
Hello! I was able to figure this out with some SQL code I found on Mr.Excel
SELECT [qry-Tech-Scans-2].WorkID, [qry-Tech-Scans-2].User, [qry-Tech-Scans-2].Time, (SELECT MIN([Time]) FROM [qry-Tech-Scans-2] AS T WHERE T.[Time]>[qry-Tech-Scans-2].[Time]) AS [Next Time], ([Next Time]-[Time])*1440 AS Minutes
ORDER BY [qry-Tech-Scans-2].WorkID;
So I'm not very familiar with Access or any database stuff to be honest, so bare with me on this one.
Access 2013, user has a shipping database with customer orders, etc.
Anyways, the database is updated every morning with a list of records in which the user will scroll through each record and do whatever she does (I believe check quantity requested vs quantity on hand). She doesn't actually modify anything, she just opens it up, scrolls through them, does something (not in Access), and that's that.
Well for the past few weeks now, when she scrolls through the records, she noticed it was skipping some numbers.
Say the database has 20 records. She'll be scrolling though them using the arrow buttons on the bottom left side near the record #. She does this and will get record 1, 2, 3, 4, 6.... for some reason completely skipping 5. So she presses the back arrow, and it will go too far back to 2. Then, if she uses the arrows again, 2, 3, 4, 5, 6, 7, etc.. until she realizes another record was skipped.
Has anyone else experienced this? I verified this myself when I remotely connected to her machine (she's in a different office), so I know she's not just accidentally clicking twice or something. Doesn't seem to be user error.
A little more background info if needed:
Win 7 Ent x64
Office 2013 Suite
No other issues with the PC that we know of.
Are all the records there and are they in order?
You could give them a button(s) to press instead
I have got a database which i use to log customer details and repairs done for them.
Is there a way i can have a form which i use to search certain feilds in the database? so i can search for the customer details by entering there name or postcode and then have a list of results which ten allow me to open those details in my customer form so that i can see all of their details and the repairs done for them?
i dont know if all of this can be done in access but thought i would ask now before i get to many records to change things.
I have attached a database which is exactly the same as the one i use but with out the customer details, i have put a few made up records in there. There are also a few tables and forms that are not finished so dont worry about them.
Thank you for all help.
6 more replies
Lengthly, I know. My apologies.....
I know that there has got to be an easier way to do this than what I'm doing.... Here's the situation. We want to be able to get our questionnaire information into our Access DB.
The way the questionnaire program dumps the data, it puts about 5 fields of identifier info first and then "question number", "answer", "question number", "answer" all onto one row of a flat .csv file. We can import that to a table in Access fine. But, as different questionnaires have different numbers of questions, if we put everything into one big table, we have wasted space (and energy) since we would have to have the table big enough to support the biggest questionnaire.
We want to get it to where our Access table has each question in it's own record instead of all combined together. The way I've done it (which does work) is to import the csv file into a table, and generate more queries than necessary, all hooked up through a macro, to append each question's info into the "broke out" table. But, this gets cumbersome as I have to have 50 queries to cover a questionnaire that has all of the questions in it and then each one will have to be changed if anything is ever added to the identifier info (each one basically says to pull x and y columns, which would change if an extra field is added to the identification fields).
I know that there has got to be a way to either handle this befor... Read more
Open a Module window and then bring up Help. Search for the READ and WRITE methods. This will allow you to read and write to a streaming text file. If you can control your format in such a way that you have a header record that has your identifier fields and then some way of distinguishing between questions and answers, this may be the way to go.
Hope that helps.
I'm not that proficient in Access, so this might be a stupid question, but I'd appreciate some advice anyway.
Is it possible to limit the number of records in an Access table? I'm trying to create a standalone tool which only requires one record and am not sure how to go about it.
12 more replies
I am preparing an end of year report and some of the records have more than one episode...how do I hide the records with duplicate names and dob
SELECT DISTINCT MyTable.LNAME, MyTable.FNAME, MyTable.DOB
hi again i am getting myself totally confused here with queries?, can anyone help please!
i have 2 tables - " messages" and " Textmessages" both tables have a field called message1
if i type a message into the textbox message1 on a form and click a button it then adds it to the table "Textmessages", how can i then copy that message into the table "messages" updating all the records in that table, using vba, i know i can do it manually copy and paste) but i would like it done in vba if poss.
thanks in advance
6 more replies
Hi, I have been searching online for answers to my question(s) and ran across your site. Very informative and a lot of helpful people here...
My problem...and i have already searched the forums on this but could not find it...I am new to Access and database creation so bare with me.
I have a database in Access 2007. It has 4 tables, Customer, Lease, Logo and Structure. I have forms created for each table. I am trying to create a search to search for customers.
1) I open the form and add a combo box.
2) I rename the combo box to SearchCustomer
3) I go to Row Source and create a Query Builder using the Customer table and add the CustomerName to the field. The CustomerName is the Key and always contains a unique identifier (lpus I dont want to have two of the same customers in my database). Save the Query builder.
4) My bound column is 1, Row Source Type is Table/Query, colum count is 1, column width is 2", and no column heads.
5) I go to the Form property and select the Record Source which promts me to create a Query builder for the for. Using the Customer table I add all of the fields. In the CustomerName field, I add the following criteria... Like [forms]![customer].[searchcustomer] & "*" I then save the Query builder.
6) then go back to the SearchCustomer combo box and under the event tab - After Update, I add the code...
Private Sub SearchCustomer_AfterUpdate()
I save everything and open the form and select the names in my cus... Read more
16 more replies
First of all, Im a total beginner and have only been using access for about a month - therefore, ive never run a query (so shouldn't have lots of tables, only the one im working on!)
My problem is that, after adding about 600 people's data to access, it has very randomly messed up about 5 records. Peoples names have turned into funny symbols etc. The database will not let me change their details; every time I re-open it they have gone back to funny symbols. Also, they cannot be deleted. Ive tried using 'delete' on the keyborad, the delete record function in access, deleting them from the spreadsheet view and so on, but it keeps coming up with the same message... "the search key was not found in any record", whenever I press delete. The database will also not let me run a query until these data are gone!
Help, Im totally stuck!!
claire, have you tried Compacting & Repairing the Database?
To do this open Access, (not the database) and go to Main Menu>Tools>Compact & Repair database
Browse to your database and double click it (or single click and click OK) and then when it asks for a name to Save it as, I would click on your current database and add something on the end to denote a newer version. I increment a version i.e.
databasename v 0.0.mdb increments to databasename v 0.1.mdb.
Is there only the one table or do you have other Tables with Relationships set up to the "Problem" table. If you do you might have to delete those relationships before you can delete the records.
I have an Access 2003 database of only moderate size and complexity. I would like to add a field (column) to each table showing when that record was updated. I would like this to be automatic; that is, when a record is saved, a current timestamp should be inserted into that field ONLY IF the record has been modified. Any ideas??
How long do your processes take? You will probably want to "freeze" the timestamp so that everything that runs for a particular process has the same timestamp. For Example, I used to have to run this complicated payroll process that took about 3 hours. If the timestamp wasn't frozen, some tables would be updated at 2:00, others at 2:10, etc... With the timestamp frozen, all tables appeared to be updated at 2:00. - And in fact, there were other indicators within the tiemstamp (PYRL0200RTC). This gives every record that was updated the exact same timestamp. This way if we found out the client sent us a bad payroll file and we needed to delete the records, we could delete by our timestamp field. Just something to think about there.
Do you have the timestamp field created in the tables that you wish to update? If not you will probably want to create a loop that creates that field in all of your tables.
If you are starting from "scratch" (that is no tables have the timestamped updated) and you want to pro-actively update them, you can create a loop for that as well. Otherwise if you are looking to update the new timestamp fields as they occur within the regular flow of your applicatoin, then you may want to create a sub that updates the timestamp and then everytime records get updated, call the sub.
I have a table of data that is an export from a phone that I want to import into Outlook.
The problem is that for every different phone number I have a new record in the table.
EX. Ron Jones cellphone is line 1 Ron Jones Home phone is line 2, Ron Jones Pagor is line 3. If I import this into Outlook it will create 3 separate entries or records for Ron Jones.
I need to somehow bring together all the like records with all the phone numbers. Therefore Ron Jones will have his name and cell, home and pagor # on one record.
Any ideas would be appreciated.
7 more replies
I'm working in Access 2003. I'm running XP Pro, SP-2. My problem is this:
I have a form that is set up to drop data into 4 tables. As I was entering the data, the number of records increased. I also made sure to save frequently. When I finished for the time being, I could scroll through and see all 16 records that I had entered through the form. Then I closed the program. When I opened it up again and went back to the form to continue inputting data, I noticed that it said only 4 records were there. When I checked the tables I saw the data for all 16 records. How come I'm not seeing that in the form? I've tried looking everywhere and I feel like the answer is right under my nose. Any ideas?
8 more replies
I have a booking databasewhich includes room, date, session in a table called booking
I want to find a way of ensuring that before a new booking is added it checks the existing table to see if their is no booking with the same three field values and returns a warning explaining that the record cant be added otherwise it is!
Do you want to do this on a form? Or where?
I can't seem to figure out how to get the data together into one table, one report for each. I hope I can explain this right.
I have 3 tables from a seperate database that I am using.
Table 1 - is the Item Master. I only need the Item # and Item Description from this table.
Table 2-3 all have Item #, Year, Warehouse fields in common. But then they each have different fields
Table 2 has Qty Received Period 1, Qyt Received, Period 2.... and so on.
Table 3 has Qty Sold Period 1, Qty Sold Period 2..... and so on.
Table 4 has Qty Issued Period 1, Qty Issued Period 2... and so on.
I need to have one record be combined for a Item #, warehouse and year - and have tables 2-4 data in the same record together. (This is where I am having issues) Do I use the aggregate group function? Is Access the best way to do this?
Then I need to have the final product be something to have the user pick the year, pick a single or multiple warehouses, and pick single or multiple Item #.
You need to break this down in to single steps at a time.
Create a query of the Table 1 data that you need, then add just table 2 data and get what you need. Then add table 3.
What version of Access are you using?
I am new with using Access. I am trying to maintain a contact database which consists of Company name, last name, first name, phone number, fax number, email address, address, city, st, and zip.
I am trying to find a way to merge records. For example:
Line 1 has Company XYZ, Smith, Bob, 555-555-5555 but not email
Line 2 has Company XYZ, Smith, Bob, no phone but [email protected]
Is there a way to merge the records to 1 row including all the information?
I am happy with using the address as a key.
Any suggestions would be helpful.
16 more replies
I have created several parameter queries in my database. They show the initial records, but if i add new records, the records show up on the form and in the tables but do not appear when i run the queries again. This happens on single table and multiple table queries.
As an example the criteria i use is: Like "*" & [Enter VRN] & "*"
Grateful for any help.
It is most unusual for Single tables queries not to show the records, but multi table queries rely on getting the Joins correct to show them if there are no "matching" records in one or more of the other tables.
I have created a list box in my form and I would like the choice of when clicking on a list item of that box be able to jump to that particular recordset instead of having to scroll thru the records. Could someone help me with the appropiate code in VBA to do this? Would this be an ON CLICK EVENT?
7 more replies
the method access reccommends doesn't work for me. I need to delete duplicate records in a Foxpro database that is linked to Access.
Are there any programs I could write that would eliminate every other record in the duplicates query?
8 more replies
Hi there all
My task is to create an Access database to hold a company phone book. In essence its simple, but the area I am in a fix with is that there is a requirement to be able to add batches of sequential phone numbers (say 1234 to 2345), with identical "properties" in other associated fields, such as phone type, floor, company team name, manager, cost centre and so on, these other fields will be fine tuned later on.
I have a form which holds the starting and ending phone numbers and all associated fields.
Looking at my MDB, I can't figure an elegant way to have a commande button that will go off and add (2345-1234) records to the table in question.
I started with a simple loop that relies on the form being based on the source table:
For Telnum = HiNum To LoNum
DoCmd.GoToRecord , , acNewRec
mstrPRN.Value = Telnum
But this is a but crunchy and inelegant.
Thanks in advance
10 more replies
hi, I am complete beginner with all things programming but I have built a temporary database using Access 2007. It has lots of tables, forms and queries. I havenít created any macros or used SQL, just kept it really simple.
The DB is on one of our shared network drives and the db is used by 4 people.
I have made it possible for us all to open the database at the same time and I have changed the record locking type to edited record.
Up until recently we havenít encountered any issues, however now when one of the users changes a record or many records the details are lost when the person disconnects from the network.
Also when I look at the folder on the shared drive the information is wrong or out of date. an example; user a opens access db the locking file is created when she looks at the shared drive, however when I look at the shared drive I cannot always see the locking file.
Another example is the DB locking file shows me in the file when I do not have access open.
Weíve tried testing with creating notepad files in the same location to see if the same thing happens it doesnít.
Iíve moved the db to another shared drive and the issues are still there.
Any advise would be appreciated as our internal support are stumped.
Open a new database on your own computer and import all of the tables and check them in that pay particular attention to the sequence of any Autonumber fields to see if any and How many of them are missing.
i am creating a small program which will use the database of my company access control program to calculate time and attendance details for employees. ie it will calculate overtimes and undertimes etc.
Employees scan their access cards at the door when antering and leaving the building.
a picture of The original database is attached (64kb).
Now during the day a single employee might scan in and out more than 10 times but i am only interested in the 1st and last scans, so i can calculate the time difference between these scans and thereby calculate overtime undertime etc.
So far i have been able to extract details for 1 specified date and copy them to a new database hwere i would now like to sort and delete all transactions which are not the first and last for a specific operator.
Here's part of my code :
Private Sub Form_Load()
Set db = OpenDatabase("C:\Documents and Settings\sebastian\My Documents\xxx\IT Project 2\ssAccLog.mdb")
Set dbTemp = OpenDatabase("C:\Documents and Settings\sebastian\My Documents\xxx\IT Project 2\DbTemp.mdb")
Set rs = db.OpenRecordset("ssAccessLog")
Set rsTemp = dbTemp.OpenRecordset("tbltemp")
Private Sub Command1_Click()
Dim v As String
If Option1.Value = True Then
z = 9
z = 10
On Error GoTo errorhandler
p = 1
v = Trim(Left$((Str(rs!DateTime)), z))
Reccount = rs.RecordCount
For i = 0 To Reccount
If v = Trim(DTPicker1.Value) Then
rsTemp.Ad... Read more
Look into the MIN and MAX SQL functions.
I have a table in access which comprises of sales order details. I want to copy a whole record in this table. Every field will be identical except the primary key (order number). Is there anyway this can be done easily?
Ideally, I would like to have a form with a cmdButton labeled "COPY ORDER". When I click, it would ask me for the old order number. It will then copy the order and provide me with the new order number. I can then go into the new order and amend any fields I wish.
9 more replies
I am trying to use "Count" to count each individual record in a Query. I only have 25 records in the database so far, and each time I run the query record #2 counts as "5". Everything else shows a "1". I have looked at my table setup, and all the records are set up just alike. I can't figure out what's wrong.
Anyone have any ideas?
What kind of query? A Totals Query I hope?
What field are you "counting"?
Are you SURE it's record #2 and not 5 records for that field?
For my AS Level coursework - we are managing a Village Hall Booking database.
Within the database there will be occasional and regular bookings which are set on certain days, and times.
What i want to know - is how you can insert automated records for the regular bookings within Access.
For example i need Play Group to be a regular booking on Monday and Wednesday Mornings.
How can i do this in Access?
Anybody - any ideas to where i can get it the info from?
I have a 12 year old access db that I have upgraded to 2010. Everything works fine on my development IIS servers. I have a new ISP this year. Sometimes, not always, still have not identified pattern, the execution of a stored procedure will return zero records.
If I build the exact same SQL statement and execute is as a text command, it works every time.
ie: use AdCmdText vs adCmdStoredProc
It is not just a problem with 1 query, it happens with many.
Searched high and low for anyone with the same problem, but no success.
Could it be some registry setting on the ISP's machine?
Access 2000, Windows XP OS
I need to sort records in groups using the fields:
DATE-OUT , EQUIPMENT , LOCATION , INSTRUCTOR,
1/5/05, LCD-Pro, LAB 101, JONES,
and get a count for the equipment used in the location field and a count of the equipment used by each instructor. In other words what I need to know is
1. How many LCDs were used in LAB101 between 1/1/04 and now.
2. How many LCDa did Instructor JONES use between 1/1/04 and now
The DATE-OUT field has records from 1/1/02 thru 6/9/05, I need to use all the records from 1/1/04 to now. I tried using >12/31/03 in the criteria box, but when I use the group by for the LOCATION and INSTRUCTOR I get separate dates. I just need the date range not each date. They need to be sorted by EQUIPMENT, which only matches any reference to LCD in the field and they need to be grouped by LOCATION, and INSTRUCTOR. When I use group by for the two fields LOCATION and INSTRUCTOR I get nothing. Please help. Thanks.
6 more replies
I have a 3 PCs peer-to-peer network sharing to use a Access DB file which resides in say PC-A. Most of the time PC-B and PC-C would open file to do data entry.
PC-B never has any problem.
PC-C the person has been complaining records missing... say she puts in new records 2 days ago, now when she opens the file she cannot find those records. This case does not happen all the time, just hit & miss!
All PCs are WIN98 running Office 2000.
1. Do I need to apply any fixes (SR1?)
2. Is there any sharing issue that I missed?
Currently all I am doing is making PC-A with a shared folder to house the Access data file.
6 more replies
I have a table like this:
|Type |Name |Weight | Date of weighing |
|Children | Gablu | 22 | 4/4/2007
|Children | Gablu | 26 | 5/8/2007
|Adult | Gablu | 20 | 1/3/2007
|Children | Sumo | 18 | 7/8/2007
|Adult | Sumo | 30 | 9/10/2007
|Children | Sumo | 27 | 9/10/2007
NOW, I wan to write a query that will be something like this.
SELECT Name, Weight
WHERE TYPE=Children AND DateOfWeighing=Max(DateofWeighing)
This should return two distinct records (i.e Second and Last one) with latest date for a Child->Name
Can anyone help.
Iím creating a website that will handle tons of data, using MS SQL Server for the active content, basically dropping records into a variety of webpage templates for the end-user. But, my back-office first has to sort through tons of raw data and whip it into shape, drafting and editing text, cropping/resizing photos, assigning links, etc. Iím using Access 2003 for that process, to handle everything that is not yet ready to be imported into the SQL Server for publication to the live website.
I understand that I can display data from an Access db on a webpage, somehow (just a facsimile of the real webpages in which it will be displayed, later, one record at a time). It would be very helpful for the back-office staff to have an in-house, interactive way to see how the data theyíre editing will eventually appear on the website, giving them insights into how they need to adjust it further.
My head is reeling from all of the different stuff Iíve read about how I might do this. Can you all give some general advice on how I should get started, which direction I should take?
Access 2000-2003 uses Data access Pages to display data on the Web. They are an html version of a Form and can be created by saving a Form as an access Page.
originally had 1 big table with 190 records. i had to split it to 2 tables with a relationship set on a specific field. i modified my form to combine the contents of both tables via my relationship. as i went back and added 10 more records, these records can be found/seen directly on my first (original) table but they were not inserted in the second table.
on my form, i can only see up to the original 190 records, and NOT the last ten records added after the table split.
is there something that i need to do to my form to go beyond 190 records?
EVM welcome to the forum. You will get a quicker response to Access questions on the Business Programs Forum.
You sound a little confused over the use of the second table, it's data will not be automatically updated when the first table is updated on your Form. You need to have form, usually a Subform on your mainform to update the second table.
Hi there. This one has me baffled. Below is only a sample of my data in a table in Access. I want to delete only the records that have repeated codes. But I want to keep only the first code on the list.
CODE VEND_PO RECV_DATE UNITPRICE
B115 090187 20040610 2.72
B115 90203 20040610 2.72
B115 90172 20040121 2.64
B115 090126 20040108 2.64
B115H 90308 20060504 5.36
B115H 090173 20040610 5.2
B115HH 90280 20050805 9.16
B115HH 90242 20050311 9.16
B116 90522 20050214 1.25
Here is what the finished table would look like
CODE VEND_PO RECV_DATE UNITPRICE
B115 090187 20040610 2.72
B115H 90308 20060504 5.36
B115HH 90280 20050805 9.16
B116 90522 20050214 1.25
Notice that only the first duplicate is left and the others have been deleted. If the code has no duplicates then that code is not deleted.
6 more replies
I'm using Microsoft Access 2003.
I have a table with data containing information related to Customer Number, Customer Name & Product. Some customers have more than one product (so there are multiple records in the table)...example Customer: 1243 has a row for Product: A and another row for Product: C.
I want to be able to say if Customer 1243 has multiple products than only show me the record for Product C.
Note: Not all customers have multiple records
Does anyone have any ideas on how to do this?
Welcome to the forums.
Firstly, you should Normalise your data. http://en.wikipedia.org/wiki/Database_normalization.
Your current data, has a relationship of Many-to-Many, a big no no, in properly normalised data - an important paradigm of database design. So you should have a table with your customers, one with your products and then a table linking them together, with the unique ID from your customer table and the unique ID from your products table.
I'm not sure how to select product C over A without doing it programmatically using VBA, as I do everything that way, however it may be possible, and someone may be able to give you the answer to that one, but you should normalise your data first.
Failing that, I could write some VBA for you, however, I need to know all the potential options, as well as the data fields, once normalised, the best thing would be to remove any sensitive data, replace it with good dummy data, and then upload the database. If you'd prefer, you can email me the database, just send me a private message, and I'll let you know where to send it.
I'm sorry, I know this is more like a Microsoft Office post but I cant get any answers when i post topics over there, and I was hoping someone here could help
Here is the link to a zip file containing my website, the database is in the database folder
My Web Site
To download it, type in the verification text, wait the 30 second delay and press the grey download button.
The problem I am having is that I cannot write any new records to the database, or edit it or delete them. I am hoping that someone could run the web site on their machine and tell me what is wrong with it. I think it may be a problem with permissions or something like that, but I have no idea how to fix it.
Thanks in advance
idiot4 more replies
I have a table called "Employees" which has the type of data you might expect. For one field called Home Phone, I originally had a input mask with parenthesis around the area code. I later decided to go with straight dashes. However, 4 of the employee records still have the parenthesis. I changed the input mask in the design view of the table, I tried deleting the phone numbers in question and re-entered them, I even deleted the entire employee record and re-entered it - with no success. I believe that the only difference between these records and the rest is that I imported the bulk of the records from an existing Excel Spreadsheet, the records in question are all newly-entered. Which must mean that even though I have changed the input mask for this field, it still is applying the parenthesis for some reason.
I really don't want to delete this table and start over. I've added a lot of fields and tweaking that I really don't want to do over. Any insight would be welcome. Thanks.
Try a find and replace on the phone number field. Replace ( with nothing and then go through again and replace ) with nothing.
First timer - so please tread gently! I'm sure this is a similar situation, but wasn't sure whether it warranted another separate conversation.
I have a situation on an unbound list box in Access 2000 and haven't yet got to the bottom of it. I have a box which displays incorrect records for the user to correct. When they first go into the box, they see all 500 records (for example) and use a dble click event to edit the one they want, when they return to the box it will only display the first visible amount of records. They can use the scroll bar but the records do not scroll!
The Code behind ( and here's where I get lost - I don't do VB!) opens another form, allows you to edit the record, saves the changes, closes the form and returns you to the original form. We have tried keeping the original form open, as it was closed and reopened, but no change.
The DB has been inherited, so I have no idea why a List box has been used an not a combo.
Forms data is taken from a query.
I'll work out how to post a copy of the empty DB to you!
6 more replies
How do I filter information from records that contain year -2003 from date format 01/17/03 (Do not want to filter by day or month)?? What is the correct criteria information??
Create a query with desired fields.
Put this in the criteria for the date column:
Between #1/1/2003# And #12/31/2003#
There's another way to do it by using just the year, but I'm not sure of it.
I am relatively new to access 2007, so forgive me if this is simple.
I created a database to store Overtime hours for our employees(50 or so)
There are only two tables:
Employees(Employee #, Last Name, First Name, Active(yes/no))
Overtime Hours(Employee #, DateWorked, Hours Accepted, Hours Rejected)
The tables are linked with a one to many relationship on Employee#
I was able to import the data from the excel sheet we use, and generate the reports we need from that without a problem. However, I am stuck on the hour entry form.
What I would like is to have the form to have a date entry text box on the top, then display the list of active employees(gotten from query), and next to each employee have a place to enter the hours accepted and hours rejected. Everything I have tried so far has failed.
I tried making a multiple item form bound to the active employee query, with 3 text boxes(HoursAcc, HoursRej, DateWorked) that has sql that executes from a command button using the following code
INSERT INTO OvertimeHours (EmployeeNumber, DateWorked, HoursAccepted, HoursRejected) VALUES ('" & EmployeeNumber.Value & "', #" & Format(DateWorked.Value,"mm/dd/yyyy" ) & "# ," & HoursAcc.Value & ", " & HoursRej.Value & " )
This has three issues,
1. Filling the Hours Accepted, or Hours Rejected text box next to any employee causes the text boxes for all the employees to be filled.
2. It only ... Read more
WolfRoolz, welcome to the Forum.
I think that you have 3 problems with your current setup.
1. You are you using Unbound Text boxes for some reason?
2. The form for Hours should be based on the Overtime Hours table.
You are using an Insert into SQL Query when you don't need to.
Ideally you would have an Employee mainform which also has the date selection/entry on it, the Employee selctable from a Combobox.
The Subform would have the Employee # as it's Master/Child link to the Mainform and the date would be transferred by some simple VBA (or you could have it entered on the subform)
I have a data entry form, which if any fields are left blank will disable the save button. What i need is for it to also allow me to close the form without saving, or even attempting to save (which results in errors) the record.
I found this online which works fine apart from getting a prompt that access cannot save the record and the option to close the form, but i want it to not save, just close.
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
If IsNull(Me.ItemID) Or IsNull(Me.Title) Or IsNull(Me.Category) _
Or IsNull(Me.Type) Or IsNull(Me.HireFee) Then
Cancel = True
strMsg = "Please complete all fields," & cbCrLf & _
"or press Esc twice to undo."
MsgBox strMsg, vbExclamation, "Blank fields"
Any help would be great, cheers,
7 more replies
I currently have a macro that selects all records in a table and deletes them. The problem is when the macro deletes the records, it prompts the end-user for confirmation... Which I don't want it to do. I just want it to delete the records and continue with the other macro procedures.
Is there a way that I can setup the macro so that it doesn't prompt the end-user for confirmation? I know I can turn off deletion confirmations all together but I don't want to do that either.
FYI - The part of the macro that I am referring to is setup like so:
Open Table (Opens the respective table)
Run Command (Selects all records)
Run Command (Delete Record)
Close (Closes the respective table)
8 more replies
Is it possible to view and edit existing records in a Subform? And by existing records I mean those created in a previous session of the database (saved and closed, then reopened). I've already set my subform properties Continuous form, Data Entry = No, Allow Edits = Yes. I can view existing records on my main form but not on the subform.
15 more replies
I'm running a query off of a linked table and it is duplicating 112 records. I've run the query to find them already now I want to delete them. The "Delete" key under the Records tab is inactive and therefore doesn't allow deletion. I'm assumming it's because it is running off of linked table. Is there a way around this?
sylnz, welcome to the Forum.
Have you tried creating a Delete query using the Duplicates query as it's data source?
Nice to meet you guys, was wondering whether you guys can help me out...kinda in a mess right now.
I got a few access tables each in different access db, each contain millions of records and I am sure each contain duplicates.
When I try to use my PC to jus find dup, either the status bar goes half way(for 24hrs) or said out of memory.
Can someone tell is the a way to delete these dup's in a fast and less resource consuming way?
Thank you so much!
15 more replies
I am in the process of developing an Access 2002-based system while simultaneously learning Access. I have been able to sort things out pretty well with one notable exception. I would like to be able to invoke an action that will automatically create a new record in a Transaction Table for each record that exists in a Customer Table. My logic is that at the beginning of each billing cycle, a transaction record is created which can subsequently be manually processed when payments are received. Is this doable from within Access, or will it require VB or ???
I am in the process of developing an Access 2002-based system while simultaneously learning Access. I have been able to sort things out pretty well with one notable exception. I would like to be able to invoke an action that will automatically create a new record in a Transaction Table for each record that exists in a Customer Table. My logic is that at the beginning of each billing cycle, a transaction record is created which can subsequently be manually processed when payments are received. Is this doable from within Access, or will it require VB or ???Click to expand...
From what I understand, you want to periodically take a "peek" at the Customer Table, and then create a new record for each customer, in a Transaction Table.
Yes, it can be done using Access - but the simplest way to do it uses VBA (within Access).
I am not sure what kind of interface you have (forms, menues, switchboard or what) - but basically you can create a snippet of code that would say something like (this is just a sample... so it won't 100% work if you cut and paste it):
dim db as dao.database
dim rst as dao.recordset
dim sSQL as string
set rst = db.openrecordset ("Select * from CustomerTable;")
docmd.setwarngings false 'so you don't see warnings for each insert
while not rst.eof
tempCustomer = rst!customer_id
'.... etc... get the rest of the customer data you want to grab for the transation table
sSQL = "INSERT IN... Read more
Hello again guys
A while back I had some excellent help with a new database I've been working on. Thanks especially to OBP I've solved a few issues and the old topic can be found here:
Basically it's a compliance testing database for my company's internal processes. Once again I've uploaded a working version of the DB if anybody wants to take a look.
In short, in the initial Tests mainform, the user fills in the Test details (Tab #1), chooses which available Items he wants to include in his test (Tab #2), details the Samples which will be tested (Tab #3) and finally assigns a Grade to each Item/Sample combination (Tab #4).
Here are some new things I've been struggling with so far.
1) When I change the Sample Descriptions in tab #3 ("Samples") I get the following error:
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.Click to expand...
It still lets me change the description, but always with the above error. I'd like to know if there's any way to suppress it, or better still, alter the underlying code so that new records are only added if the Item_ID and Sample_ID combinations do n... Read more
16 more replies
I have a bound form that is not registering new records that I added in the corresponding table, any ideas on what the issue could be? I am still learning the in's and out's of access 2010 but have never had this issue.
tookiemouse, welcome to the forum.
I do not have Access 2007/2010 but I can ask some general questions.
Is the Form's Data Entry Property set to "Yes", as it is does not allow displaying of olod records, only the entry of new ones?
Can you enter Records in the Form?
Do they appear in the Table?
I am trying to delete & insert records from my Access database using excel macros. I have been using Select query with recordset which I tried to extend to Delete & insert commands but I realize that I will have to use Execute Method(DAO) to do so. All the example seems to be with QueryDef.. can some one to direct me to a resource for the same or help me find a way around ..thanks
I have Access 2000. I have gotten one of my databases imported into it and created a query and report.
Now when I go try to change a record, I am getting "updating data in a linked table is not supported by this isam"
Help tells me that i need "installable isam". What do I need to do? I've never run into this before.
It's a simple subscriber database I use to print mailing labels. I had it in MS Works, but the new works is too complicated and I can't figure out how to print my labels in it. :S
I don't know about the "installable isam", but I did just import an Excel spreadsheet into my new DB and noted the option to "link tables" rather than actually import the data. It sounds like you may have accidentally done this. Unless you've drastically changed the data in your table, I would change the name slightly and import the data again to test this. Save the new table as the name referenced by your query and report and see if you get the same error.
Juts like the subject say's..... How do I copy, migrate, import teh records from one table to another table in the same DB.. Access 97 by the way.... Dreamboat I am gonna time you on this one... Your my hero..
9 more replies
I hope this is an "easy" answer.
We use an Access database to track class registrations for recreation programs.
Each program has a limit on class size.
How can I set a limit in Access so that when that number is reached, it displays a message....
I'd like to be able to continue to add records if it's decided that the class size can be adjusted, I really just want the message to appear.
I have a course table which has the course name, course code, start date, end date, and cost
I have a registration table in which all the registrants info is entered and the course is selected from the course table.
Thanks for any help with this.
I would do a record count and see if it is equal to or less than the class limit. It is was, then I would do a messagebox or pop up window to give a message. you could to the message box with a yes or no. yes would add it, no would do nothing. the easiest is the message box. just look up msgbox in access help or give some more information on what you are doing.
I am working on someone else's form that has several
combo boxes and text boxes. In this example, there are
four tables, "table_A" , "table_B" , "table_C" and "table_D" .
We will call the form "form_A"
There is no pertinent VB Code.
The record source listed is "table_A"
The control sources for the objects in the form come from fields in "table_A"
When I run the form, I click the >* at the bottom in
order create a new record. I then get focus on Combo Box "combo_A"
For object "combo_A" ,
the Row Source Type is Table/Query
the Row Source is a Query, we will call is "query_A"
"query_A" is a join between the other three tables, and returns only one
field, the customer name.
At this point I select "Records/Refresh"
So what the h@#!ell happens at this point? All my text boxes are getting
filled with data
which I think is coming from "query_A", even though only one field is
returned in the
query. How can I determine what is happening behind the "Records/Refresh"
Any help would be greatly appreciated!
The query is probably populating the Customer name field on the form, when you records/refresh Access picks up the form's fields from the record associated with the Customer Name.
Is the Combo "bound" to the Customer name field on the form?
To actually find out what is happening I would need to see the database, can you post a "zipped" copy on here. Do not forget data protection, so if there are names and addresses etc involved you need to remove them and create some dummy data for the version you post.
I have a very simple data base 3 tables
Product table with 2 fields productid and productdescription
Sale1 is the basic information of a sale invoice number and who items are sold to
Sale2 is the details and shows qty , item, price, and GST charged if any (some people or organizations are tax exempt so it becomes zero.
I've got my form and subform to run correctly
I've got my report to print all items correctly but I can't total the value
the line value for each item is a textbox
=Sum([QTY] * [Saleprice])*([GST])+([QTY] * [Saleprice])
This does return the correct value for that line item.
HOW can I correctly total the sum of all items into the reporrt footer???
HELP please the sale is tomorrow through Sunday.
Add a duplicate of your current calculating field and set it's "Running Sum" (under data tab) to yes overall.
Then in the footer use the new field as the totals Control Source.
hi, i am new to this site and hopefully someone can help me.
i have a specific problem with my database(access 2007) that i do not know how to work around with.
i have a main form which holds a person's information - then i created a subform that shows up on this main person form >> in this subforms i hold information about a specific program this person is attending so 1 person can have many programs ( and the user have a the opportunity to add more programs to this person on the subforms)
i have created a button that prints a report on the main person form together with the details of the program using a macro to open the report (showing the person details and program details)- but my problem is >> if i have more than one program detail in the subform i manage only to print the first program's details in my report- i would like to print the form with the person details and i should be able to select a specific course - as i need to send 1 letter to that specific person and each letter should hold information about each course he is attending.
hope this explanation is helpfull.
16 more replies
Q: Using a front-end / back-end database, is there an automated way to move records that have a field marking the record as "closed" to a second database (along with their linked table records) to store "old" information. Here is the background information on the database.
Database was originally suppossed to hold information on client contact information & file notes. However, over the last year, the boss kept requesting expanded information be available, and I would create new tables and relationships to hold billing information, doctor, scheduled fees, and insurance information.
The main client information input form is set up with the main record information and clientID field. 4 subforms (added using a Tab contol with the subforms linked via the clientID field) are also on this main form. I moved the billing (money owed / money paid and the running totals) to a seperate form completely, which is opened by a command button which opens the billing form with specific data from that clientID. I thought that this would speed things up some, but it hasn't
Things were rocking along fine, until temporary help was hired to input last years clients information in the database as well. So the database has gotten extremely large and rather slow when the main input form is opened (Once it's open, it cycles through the data fine with minimum delay). I added a field in the main table that the clerks can check to mark the client as "closed"... Read more
9 more replies
So, I barely know how to state this question, which is probably not a good sign.
I am working on a self-referencing constraint and want to prevent reciprocal values. Say, for example, my relations were thus:
fruit_name | fruit_color | fruit_taste
orange | orange | sour
lemon | yellow | sour
lime | green | sour
apple | red | sweet
fruit_name | fruit_like
orange | lemon
orange | lime
lime | lemon
[COLOR="Red"]lemon | lime[/COLOR][/SIZE]
I want the constraint to prevent the last record in `tbl_fruit_associations` (the red one) as spurious. The logic in question is the commutative property of associated pairs.
I'd prefer to use a DB solution rather than an application layer solution, if that can be done. Any ideas?
Thanks for input,
I meant "self-referencing" not "self-reflexive."
Sorry to bump; looks like I should have done more research about the new features in Office 2007/2010. Apparently Access almost supports triggers these days, only they're called data macros. I was able to accomplish what I needed by creating a data macro like so:
[SIZE="3"][b]Look Up A Record In[/b] tbl_fruit_associations
[b]Where Condition =[/b]([t_f_a].[fruit_like]=[tbl_fruit_associations].[fruit_name]) And
Error Number 1
Error Description The fruit are already associated.[/SIZE]
Having only just discovered the feature, I can only guess at this point, but I believe the crux of this is the Alias property: since the table is self-referencing, the alias is necessary to distinguish the macro query (i.e. "lookup") from the real table.
Anyway, in layman's terms, here is what happens:
User attempts to create a new record by providing values for the fruit_name and fruit_like fields
Access does not immediately create the new record
Access searches the table for any record where fruit_name has the proposed fruit_like value and fruit_like has the proposed fruit_name value
If such a record is found, Access raises an error with a meaningful message and prevents the new record from being created
Of course, I don't have to worry about the possibility of strictly duplicate r... Read more