Access 2007-2016
Access 2000-2003
Access Seminars
 		TechHelp Support
Tips & Tricks
Access Forum
 		Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 

Forums     

Microsoft Access Forum
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

 
 This forum is for the discussion of Microsoft Access.

Access is my personal specialty. Have questions? Comments? Want to discuss how to do something? Post it here. Also, I get a LOT of questions sent to me in Email from people around the world. I'll post the interesting ones in here. Feel free to comment on them.

Click Here To Subscribe to this forum and receive an email update whenever new posts are added, just scroll down to the bottom of this page and enter your email address in the comment form.

Permanent Link
Keywords: microsoft access forum
Post New Topic

Read Microsoft Access Forum by david P @ 2/12/2019
In Exper7 we learned about ManytoMany relationships. I am wondering if that relationship will work using the same table. Such as a CompanyXCompany. The reason I ask is how to perform a partnership with a company in CompanyT and a Partner from the same CompanyT. If I select a partner from the sub form, I would like to see the reverse show up on the subform when I go to that company record. Sort of a reverse type, if I select a partner, then that partner shows up without having to create that partnership in the left side of the table. If this makes sense.

Reply from Alex Hedley:

Access Relationship Seminar Blog Post
6. Complete family history with relationships (self-join, many-to-many)

Access Relationship Seminar.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Gregory C. S @ 11/20/2018
Alex,  I wrote "unsplit" in the last line.  I think I am too ignorant to tell my Excel macros and Word (single record merge) templates how to find the tables.  I am willing to try the split again if it will help me get rid of some of the error messages and MSysCompactError tables that have shown up in this database in the last 3 months,  The dB has been stable for years. Richard's lessons, other than the splitting lessons, have been incorporated into my dB. I am starting to think our recent server, with its older switches and wiring, are the issue that has been erasing VBA in my forms.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Gregory C. S @ 10/4/2018
I like the VBA lessons.  Unfortunately, I have had a couple instances when the server has dropped a connection and the VBA is removed; not just damaged.  I have to have VBA but I freaked out a bit when the only table that was in my database today was MSysCompactError.  All of the other tables and queries were gone.  Forms, Reports, macros, and two basic modules were the only objects I could see.  If I could add a screen capture of a previous error message it would show "Your network access was interrupted. To continue, close the database, and then open it again."  In the background there is a query showing headings, but every field of every record shows "#Name?".  That "network access" error message has become more frequent. I have started replacing objects in my unsplit database with objects from a 4-month-old version to try to find forms and reports that don't have corruption of the VBA. (I did split the database per the lessons, but that ruined the portability of the small database and there were a couple other reasons that caused me to recombine the database parts.)
Can server connectivity corrupt the VBA in forms and reports that are in the single, unsplit file or am I mistakenly looking for someone to blame in I.T.?

Reply from Alex Hedley:

Are the modules etc on the FE or the BE?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Selby H @ 10/3/2018
Dear Richard, Can you help with some ideas and possibly some Access VBA to change the resolution of the screen or make the form adjust to a different screen resolution when the form loads. Example, I build a database with forms on one computer and move the DB to another computer and the Screen Resolution of that Database on the other computer screen is either smaller or larger. Forms do not fit the screen.

Can I use code when the form loads to make a form fit the screen on any type  of screen resolution. Your help is much appreciated.
All the best - Selby Halfpenny
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Michael R @ 9/13/2018
Can I create an action button on a form that will allow the specific form to be emailed to a customer?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Joseph M @ 8/3/2018
I'm working on a routine which is similar to what Rich described in Developer 7 to prevent users from entering payments after balance reaches zero. My routine is written in Macro's not VBA. What I want to add is the ability to set allowedits to FALSE so I can prevent the user from entering anything when the warning message is displayed.

For the life of me I can't seem to figure out how to set the allowedits field. I need some help with the code and will welcome any suggestions. I have tried using a RUN Code macro and creating a VBA function but that doesn't seem to work. Looking for some guidance and yes, some code.


Reply from Alex Hedley:

Can you share your VBA function and what is in the Run Code macro?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Mei-Yuk M @ 7/22/2018
I am creating a database for the projects our program supported.In a database, one project can have more than one PI and co-PI.(PI can work more than one project) I learnt from the Relationship course and created a relation table and a subform with combo box to input all PIs and Co-PI's name for each project. It works on the form. However, it does not work on Report. I first created a query which is able to  showed 2 records if a project has one PI and one co-PI. However, in the report, it shows only one PI base on the first RelationID. How can the report to show more than one PIs and Co-PI for each project?

Reply from Alex Hedley:

Have you created a set of Reports that mimic the Forms you have, if it works in Forms it should work for Reports.
You can create subreports in a main report and link the Master/Child Fields in the same way.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Dennis O @ 6/22/2018
What lesson covers a report that the form has a drop down menu title "Show" Paid Only, Not Paid Only, Invoice Only, etc. "Sort" By Order Date, Order Total, Amount Due, etc. with a text box saying "SELECT * from ? by ? etc.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by randy r @ 5/24/2018
What video covers using the same table multiple times in a query? i.e. something performed in one department now performed in another-Department table. One employee starting a task and another finishing the task- Employee Table. And to doe this multiple times.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Richard W @ 4/2/2018
I am making a simple database to use to collect customer survey info. My plan is to have a main form with a sub form of questions with yes no check boxes for answeres. I am having trouble getting the sub form to display all 20 questions.
Thanks in advance for the help

Reply from Alex Hedley:

The Web Database has a Test Taking example.
Or Access 323
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Margaret C @ 3/15/2018
I have a form that has 3 combo boxes:
Department, Director, and Team Members.  I am thinking about changing these to cascading combos to filter the extensive employee list.
Here's the thing, how do I edit the query to exclude an employee who is no longer with the company, without changing all of the previous forms that they may be listed on?

Reply from Alex Hedley:

Did you make the Combobox have a Source of a Query or are they all custom SQL?
If it's a Query in your Object Window and you can amend that as wherever it's used will be fine to change all then just add a WHERE clause that has something like Exists=NO or however you determine they have left.
If not you will need to change them manually.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Ken N @ 3/2/2018
If you still need to solve this problem, make sure the field length has enough characters, 9 for the SSN and 2 for the dashes.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Carolyn L @ 2/18/2018
I am on Beginner Level 6.  I am trying to use the input mask for SS#.  I chose that from the drop down menu and clicked next, next and finally finish.  On the last screen, I chose to show the dashes but when I go to input the SS# in the SSN field it gives me a message that the field is too small to accept the amount of data you attempted to add.  Try inserting or pasting less data.  The rest of the SS# show up with dashes but this one will not and it is a standard SS#.  Suggestions??

Reply from Alex Hedley:

Maybe delete it and try again if others are working ok.
Is this input mask on the Form or the Table?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Susan W @ 2/18/2018
Thank you.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Susan W @ 2/4/2018
I just realized my second question was similar to my first.The reason I was getting that message (?NAME)was because the field EmployeeID had an extra space in the Employee Form which was not in the Employee Table.
d

Reply from Alex Hedley:

Have you added a space in the Field name i.e. "Employee ID" not "EmployeeID". Try not to have spaces in Field names, you have to surround them in [] -> [Employee ID] in Queries etc.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Susan W @ 2/3/2018
I did not have the all the necessary fields listed in my table.  I do have another question, though.  I have an employee form and a customer form, both with EmployeeID fields.  However, one is working correctly with the ID # showing, but the other has #Name? in the EmployeeID field instead of the number.  Both seem to be set-up the same way.

Reply from Alex Hedley:

Does EmployeeID exist in the Bound RecordSource?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Susan W @ 1/24/2018
I think I know what I did wrong, thanks.

Reply from Alex Hedley:

What was it?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Susan W @ 1/8/2018
I created an employee form with a picture field (Access Level 1 Lesson 6)  However, when I created the Employee Name Badge Labels, nothing appears in the picture field.  The Company Logo image does appear, though. I'm not sure what I am doing wrong - both have .bitmap file extensions.

Reply from Alex Hedley:

Do you mean ".bmp" not ".bitmap"
What is the full image path? Does it exist?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Robert S @ 10/10/2017
In the video Expert 1 Lesson 4 (Relational Combo Boxes) I Noted that the Filter box on the record line displays "NOT FILTERED" when the combo box is activated, however on my form (ContactF) the filter box displays "FILTERED" can someone explain why this is different?
THanks
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Patrick V @ 8/27/2017
Hi Alex,
Thank you for your reply.  However, I don't think my question was understood correctly, so let me give this example to explain better what I meant.Suppose I have a table with 5 fields: ID, Field1-Field4.  When I set up a combobox for this table in a form, I am setting it up with following column widths:0cm;2.54cm;2.54cm;2.54cm;2.54cm.  I don't see the ID field, because that has width 0, so far so good.  However on the form I only see Field1, not fields Field2-Field4, even though fields Field1-Field4 are selected in the combobox data source (SELECT [Table1].[ID], [Table1].[Field1], [Table1].[Field2], [Table1].[Field3], [Table1].[Field4] FROM Table1).
My question was and is: what decides that I see Field1 only, and not Field2 or Field3.  Is it simply always the 1st field (with width > 0) that is displayed?


Reply from Alex Hedley:

The first column is shown when the dropdown isn't showing, i.e. a value has been selected.
It's the first column with a width greater than 0.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Leola E @ 8/18/2017
I recently purchase 2 courses - Expert Level 5 & 6.  However, trying to figure out how you setup the Notes - where it automatically creates a new ID each time you respond or something. What lesson is this taught?  thank you

Reply from Alex Hedley:

You can use the Microsoft Access Searchable Topic Index to find when something was covered in a lesson.
It's likely got a Default Value in the CustomerId on the Notes Form, it if it's a subform the Master/Child Fields will be linked.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Kimberly @ 3/21/2017
Hi Richard, I hope you can help me figure out how to go about designing this. Here is what I'm trying to do.
I have CD's number 1-7
I have Libraries and each library has its own name (short Text)
And I have Maps each map has its own name (short Text) and a group or maps makes a Library. For example Cd1 contains Library 1221 which contains maps 2,3,4,5,6
One library could have many charts
One CD could have many Libraries
One chart could have many Libraries
So I need to do is build a database that tracks the updates done to a library. I  need an Employee table, A project assignment table and a project table-If a map needs to be updated that library would become a project that needs to be assigned to an employee. So the assign form Would have the library name as the project, The employee who is assigning the project, The employee who the project is assigned to, and the date it was assigned. I want this form to also have a way that the employee assigning the project could select maps from a list and have the populate in a subform. So If you select a library- you get a list of all the maps in that library and you can choose the maps from the list that need to be updated.
Once that form is complete in another form called the Project form populates the projects that were assigned to each employee. That form will then tack the status of the project.
I know this was a lot but writing it is much harder than it sounds.

Reply from Alex Hedley:

Which bits do you need help with?
It's good to draw out a map of your Tables and how they connect.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Toine v @ 2/24/2017
where are all the other posts? I get no reply to any of them.

Reply from Alex Hedley:

There was a problem with spam and I've been waiting for it to be cleared before I answered any, also I do this in my spare time so answer if and when I can.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Wm @ 11/6/2016
What corse would provide how to separate from database table to a link file?

Reply from Alex Hedley:

Expert 20
Expert 21
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Carolyn Lowry @ 9/22/2016
I am new to Access and have listened and watched many of the tutorials.  I still cannot figure out how to keep an individual data base that will have several entries for the same column heading.  I am creating one for personnel mgmt. and need to have a comprehensive list of starting pay and then pay and date of increases for each employee.  Can you give me any guidance?

Reply from Alex Hedley:

Why not have 2 Fields, PayRate, PayDate.
You could then get the earliest, that's your Starting Pay, and the latest - current pay.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Charles Fowler @ 8/24/2016
This database was imported from Alpha Five, so the rosteridnum is the linking field for 3 databases.  That's why I'm not using the autonumber id field.  I did refer to the DMax Counter tip, but it didn't help much.

Reply from Alex Hedley:

Have you tried following the tip in  a stand along db to get it working that way first?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Charles F @ 8/17/2016
I'm trying to set up a counter as described in Expert 29.  The name of the field I want to increment is 'rosteridnum'.  It is a number field in my table 'ctramrsm-t'.  The field is on form 'ctramrsm-f'.  The set value code I entered in the Before Insert property of the form seems to save okay, but when I try to add a new member, I get an error message -- The object doesn't contain the Automation Object 'ctramrsm-t'.  How do I fix this?

Reply from Alex Hedley:

Is there a reason you aren't using an AutoNumber Field?

You could use this tip: Microsoft Access DMax Counter

I'd need to look further into this error, it's not one I've come across before.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Robert R @ 7/26/2016
I have a customer form that opens when I click the MY Customers Button:Here is the code:DoCmd.OpenForm "CustomerF", , , "UserID=" & "Forms!MainMenuF!UserID".So the current user sees the records assign to them.
I also have a listbox and a combobox on the same form. When I choose from the combobox,  "active statuses", my listbox is populated with customers that have an "active status". When I double click an item in the listbox it populates that customers information on the form. The listbox is on the same form, side by side.Listbox on the left Customer Info On the right. When I clear the listbox, how do I see all of the current users assigned record again. The listbox is there so they can use the combobox to choose the customers they want to follow up with. When I clear the listbox I want to refresh the records to go back to the records that belong to the Current User. What I see for customer information is the info from the last customer I clicked on before I cleared the listbox.The record counter shows 1 of 1. The only way it seems to work is if I close the Customer form and then reopen the form again. Now the record counter sows 50 of 50. I want to clear the listbox and load all records again for the current user. 50 of 50. Hope this makes sense.

Reply from Alex Hedley:


Me.Filter = ""
Me.FilterOn = False
Show Just This Thread        Post Reply
Read Microsoft Access Forum by William Wright @ 6/15/2016
Just learning Access

Reply from Alex Hedley:

Good luck, be sure to post any questions here if you need some help.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Shaun @ 5/20/2016
I m trying to calculate how much one of our members get for leave when their on orders. If they have orders that start from 1st-6th they get 2.5 days, 7th-12th 2 days, 13th-18th 1.5 days, 19th-24th 1 day, and the rest of the month .5. Then 2.5 for each month on the orders after the first month. And on the back end its reversed going down.
I m trying to have the access database give the leave for the date the order started on. Then give me the months and 2.5 for each one then add them up and give me the leave amount for that day i.e 2.5, 1.5 etc
Like if the orders start on 6OCT15 and end on 16May16. I want it to give me 2.5 for the 6th then give me the Months Nov, Dec, Jan, Feb, Mar, and April at 2.5 for those months. If there return date is May 10 and I add a day for travel.
Basic formula is
11MAY16(return date) + 2.5+2.5+2.5+2.5+2.5+2.5=17.50 days (MAY 29, 2016) and because it s the 29th the get another 2.5 days.
Any help on this would be great if this doesn t make any since please let me know.

TSgt Olivera
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Mike Jones @ 5/8/2016
thank you for your reply. I have looked at the loan Amort Db and the Loans section with changes would be ideal. Is there any way I can get the structure for the Tables and other related Sections.
regards mike

Reply from Alex Hedley:

Are you wanting the schema?
Are the columns listed in the pics not enough?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Derek P @ 5/5/2016
I am using Access 2016 and I am working thru your Access Security Seminar less 5 and I am getting a Compile Error and Syntax error and I am not sure what to do.

This is the code:

Private Sub Command6_Click()

    ' check if the user is an ADMIN
    
    Dim X As Long
    X = Nz(DLookup("ID", "GroupXUserT", "UserID=" & txtUserID & " And GroupID=1))
    
    If X > 0 Then
    DoCmd.OpenForm "UserF"
    
    Else
        MsgBox "You don't have permission."
    End If
    
End Sub

This is highlighted in red.   X = Nz(DLookup("ID", "GroupXUserT", "UserID=" & txtUserID & " And GroupID=1))


Reply from Alex Hedley:

X = Nz(DLookup("ID", "GroupXUserT", "UserID=" & txtUserID & " And GroupID=1))
You don't have a closing quote (")
X = Nz(DLookup("ID", "GroupXUserT", "UserID=" & txtUserID & " And GroupID=1"))
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Robert @ 5/4/2016
Hi, Im looking for a way to open multiple customer forms. Here is my example: Im working my sales list and then I get 1 more calls that come in right away. I want to be able to put the current call on hold, search for my incoming call in the DB, but when I do this the current customer record changes to the record that called in. I then have to search for the 1st call I was on again when Im done with the 2nd call that came in. I want to be able to have them both on my screen so that when I finish with one I can go right back to the other customer form and finish with that customer.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Selby H @ 5/3/2016
Dear Richard,
Is there a way to display an image with a 50% transparent background on a form?
All the Best Richard. Thanks to Alex for his help. Kindest Regards Selby

Reply from Alex Hedley:

You could have another image on top that you've created a blur effect on, that already has a transparency set. It would then be layered
I don't think access OLE objects have it oob.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Michael W @ 4/19/2016
Which lesson discusses creating or settings for databases for runtime? I which to see how the project looks at run time to get a feel for it.

Reply from Alex Hedley:

You can rename a db to .accdr or you could look at the Save As options for accde
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Karen Davis @ 3/21/2016
Not sure which classes I need. I watched the Beginners Level 1. But this is for my use at my job. There only 2 employees me and my dad. But he has decided to do in-house financing on the appliances he sale. Is Microsoft Access the right software to use

Reply from Alex Hedley:

You can make Access do just about anything, do you have any more detailed requirements so we can better direct you to which courses you might need to take?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Lisa @ 3/3/2016
I have 2 questions:
1) Why are my records automatically sorting ascending? I am not keying them in that way and did not set up to sort.
2) I have records 1-15 keyed in so far yet my subforms are still showing 1 of 1.  Why are they not linking up?

Reply from Alex Hedley:

Is this in the Table, Query or Form, it can be sorted by any column you like
Did you set up a master/child link in your sub form?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by J. Allmond @ 2/5/2016
I was afraid of Access because of not understanding its capabilities. Viewed your Basic lessons on YouTube and was thoroughly surprised at how easy it actually is. Thank you so very much.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Peter D @ 1/18/2016
Thanks for replying but i finally figured it out.

Reply from Alex Hedley:

What did you do, could help others to share
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Peter D @ 1/15/2016
which access learning zone tutorial can help me with Date, Page No., and footer

Reply from Alex Hedley:

Are you meaning the reserved words that will put the Date and Page No in the footer?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Selby H @ 11/10/2015
Dear Richard,
Is it possible to play a sound (piano chord) using access vba, using keyboard?
All the best
Selby

Reply from Alex Hedley:

Allen Browne has a tip.


  Declare Function apisndPlaySound Lib "winmm" Alias "sndPlaySoundA" _
    (ByVal filename As String, ByVal snd_async As Long) As Long

  Function PlaySound(sWavFile As String)
    ' Purpose:  Plays a sound.
    ' Argument: the full path and file name.

    If apisndPlaySound(sWavFile, 1) = 0 Then
        MsgBox "The Sound Did Not Play!"
    End If
  End Function


Then add this to an event

=PlaySound("C:\WINDOWS\CHIMES.WAV")

You could add & to the button name and then Ctrl+{letter} to press it.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Jim Gray @ 11/2/2015
Richard,
As one who is addicted to your teachings and an admirer of your knowledge, I am curious as to your recent lack of visibility.  I, for one, look forward to your classes and videos.  I am sure I'm not the only one.  Hope that this absence is not due to health issues but if it is then please get better soon.  Alex is doing admirably on the forum questions but, with not deference to him, there is nothing like your presence.  
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Marc Bryan @ 9/19/2015
I'm a plumber, responsible for plumbing maintenance for 12 building on a small college campus; learned how to create tables and fields; need to learn how to track continuous data input re:  daily repairs and maintenance issues.  Thanks.

Reply from Alex Hedley:

You can build a Form and use that as data entry.
You can show Table information in a Continuous Form which would show all the Entries.
You could also make a Report to show this data.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by scott c @ 8/10/2015
I am trying to create the calendar form in Access 2010. I get an error with my Row Source when trying to increase the date by +1

How can I correct this?

Here is the Row Source:
SELECT [CalendarQ].[CalendarID], [CalendarQ].[ApptTime], [CalendarQ].[Description] FROM CalendarQ WHERE DateTime=>Forms![DatePickerF]![StartDate] AND DateTime

Reply from Alex Hedley:

Are you using the methods shown in the Calendar Seminar?

What are you adding 1 to?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Fernando R @ 7/17/2015
Actually! I figured it out,

For those interested I posted the solution below:

Compact and Repair.
One of my fields had a '#deleted'error.

Also, it wasn't obvious to me, so I'll mention what I was doing wrong: I was using the compact&repair tool on my front-end and not the back-end of the database where the error was.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Fernando R @ 7/16/2015
So I have this query and within a notes field.

Today I made a continuous notes sub form so that I can see all notes, but I didn't want the records with no notes so I went back to the query and added a criteria <>""

Everything works from within the form with this new subform.

However, when I try to open the subform individually or the query from which it receives it's records i get a message that reads "Record Deleted".

Why does that happen?

Note: When I remove the criteria <>"" the problem goes away.
This also happen if I add another field to the query. (However, it doesn't seem to affect the subform's functionality at all)

Please help and Thank-you in advance.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Tiffany @ 7/15/2015
I have lots of data in Excel from various locations from a number of years all for the same project.  My ideal vision would for a user to be able to select and be able to print out or export the data that I have available. example: someone can say "I want Tree age Data from 2010 and 2012 only, from location A."  Click the Year button(s), Select Tree Age (among other data points we have collected), select location, and have that table/report of tree age available.

What lessons beyond Level 1 would I need to look into for something like this?

Many of my excel files have multiple sheets with similar (not identical)information, so I need to be able to keep,say, the many 2010 tables together in a group but also have them grouped by location.

Thank you for your assistance.

Reply from Alex Hedley:

Various Courses cover various topics, you can use the Search to find the terms you need and look through the outlines.

I'd do some data cleaning (E14) then import it into Access (E20)
From there you can create the Queries you need.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Ingrid L @ 6/17/2015
How can I incorporate a .CSV report to import directly to access.

Reply from Alex Hedley:

Expert 23 shows you how to import a spreadsheet into Access.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Jay collins @ 3/20/2015
I have written a db to track scheduling for a multi dentist group.  I am at a loss on how to query for white space or unscheduled time(AS in the first available time for dentist A)

Reply from Alex Hedley:

Are you wanting something similar to the Next Available Appointment?

Work Order Seminar
From the Outline.
"We will learn how to schedule appointments for all of our workers, whether they're contractors or employees. You can select a worker, then the form will show you all of his upcoming appointments - so you don't schedule a conflict. You can click on the "Next Available Appointment" button to automatically select his next free time slot. Of course, if you do double-book a worker, the database will yell at you. And of course, we'll make printable schedules you can hand out to your workers."

This would require some DLOOKUPS.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Ramona W @ 3/6/2015
I want to create a recurring monthly checklist?  For example: Automatic Withdrawals from Bank Account.  

Due 1st Mortgage 1500.00
Due 31st Insurance 450.00

I would want to have a list that shows the due date, payee, and the monthly amount.  With a little check box in front of each payee name.  Then when the payment clears the bank I would check it off, and it would disappear from the list.

For example lets say that insurance is due on the Jan 31st, which happens to be a Saturday, and therefore will not actually clear bank until the following Monday which is Feb 2nd. I would still need the insurance to appear on the checklist until I have checked it off as clearing the bank.

So technically I guess I would need some kind of continuous list.  So when it clears the bank, the list will automatically update and get rid of the line for the insurance that was due on Jan 31st, as well as updates to add the insurance payment due on Feb 28th to the bottom of the list, with a new due date of Feb 28th.

Is this possible? If so how?

Reply from Alex Hedley:

The Advanced DateTime Seminar covers something similar but with appointments, which you could use.

There's also the Access Payables Seminar which has recurring bills.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by charalampos demiris @ 12/13/2014
HOW CAN I FORCE A USER TO ENTER A VALUE IN A SUBFORM'S FIELD BEFORE GOING TO THE NEXT FIELD?

Reply from Alex Hedley:

Where is the Next Field, it this on the Main Form.
You could have an After Update Event on a Field in the Main Form that has a Go To SubForm Field.
Then you could check a Record is created.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Piet @ 12/13/2014
(NB user will type time as system does not do so when value entered)

Reply from Alex Hedley:

Piet I'm not sure what your question is, could you elaborate?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Pierre van Hal @ 10/18/2014
Dear Richard,
The blank form grid [in design view] in my version of Acces 2013
doesn't have those small points like the grid in your courses. Can you explain how this is possible?

Yours sincerely,

Pierre van Hal.

Reply from Alexander Hedley:

Hi Pierre,
Have you seen this Tip by Rich?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by lee dillon @ 8/7/2014
Hi Richard, I am a very basic user but thanks to your tips I have been able to develop a very simple event tracking DB. I have run into a problem with a report I am trying to generate. I created a query that shows the event date and other stuff.  what I would like do is generate that report covering a 10 day span beginning at the current date. I have already set the date criteria to >=date()but I cant the rest. Thanks Lee Dillon

Reply from Richard Rost:

BETWEEN Date() and Date()+10

See: BETWEEN
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Hassan A @ 6/28/2014
Hi Richard:
I need to get the least expensive product price in a form through conditional formatting. I have tried couple of expression and none of them have worked. The form has this calculated text box =[UnitPrice]/[OzPerUnit] labeled Oz Price. Can you help please?
Hassan
Show Just This Thread        Post Reply
Read Microsoft Access Forum by David @ 6/14/2014
Another help question would be dealing with the global variable issue in VBA Modules.  Currently, whenever an untrapped  exception occurs, such variables are cleared to null, generating runtime error 91.  You mentioned in your Security seminar that you have another way of implementing globals.  I'd like to know what this is.

Also I'd like to get a copy of the security database you work with in that seminar.  I would very much like to implement that in my Ledger database.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by william c @ 6/9/2014
how does one create a link from an address field to an OLE object so the OLE object displays what is in the address field?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Ahmed A @ 5/25/2014
According to the class where you design the Product Form (ProductF), you said
" REMEMBER: Changing price will effect all current order! "

My Question:
Who can I change the price without effect all current orders?

Reply from Richard Rost:

The order details table is currently linked to the products table with a relationship, so if you alter the price of a product, it will change all of your orders. We're going to cover how to fix this when we get into the advanced series (hint: it requires a macro or some VBA).
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Mike A @ 5/23/2014
Hello,
I'm building a search form that will run a query based on an electric utility name and return all records if the name is left blank. I have this criteria plugged into the query for that part and it's working great.

Like "*" & [Forms]![searchtest2]![txtUtility] & "*"

The problem is that I also want to include start and stop dates and have the query return all records if one or the other date fields is blank. I'm not sure what to plug into the criteria for that function. Any help is appreciated.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by William B @ 5/9/2014
Patrick Brookes: I've not done it with a combo box, but I have used a technique for other queries. As the user selects options, you need to use code to build the SQL statement that is saved in a string variable and then run the comboboxname.requery method to update it. Play with some simple options and then work your way up to the full list that you need. Of course, as you add options, the logical complexity increases exponentially.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Patrick Brookes @ 5/5/2014
I currently have a database where I'm using a combo box on a form to return criteria from queried data. The current structure I have listed in my query critiera is Forms![Name of Form!][Name of Combobox!]. This works perfectly fine...

The problem I have is I want to allow the user to select more than one parameter on the form and have the query return the results on each of the options the user selected. I know I have to first change my combobox to a listbox (cause combo boxes can't be used to select multiple lines), and change the list box to allow the user to select multiple lines (Multiselect set = Simple). I first tried setting this listbox up the same way I set up my combobox i.e[(forms!)(name of form!)(name of listbox!)] but that does't return any results for me.

Can someone please help me try and understand what it takes to have a database return results off multiple options selected in a listbox???

Thanks!
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Richard L @ 5/2/2014
I have a Parent Form with a date field and a child subform with a date field whose default value is =Forms!ParentF!Date.  When I enter a date field into the Parent Form the same date is automatically entered in the child subform's date field.  The problem I have is, if I change the date on the Parent Form, the date field in the child subform doesn't correspond with the new data.
HOW DO I REQUERRY OR REFRESH THE DEFAULT VALUE TO REFLECT THE CHANGE VALUE?  IT IS IMPORTANT THAT THEY ALWAY MATCH!
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Richard L @ 4/30/2014
I know this will generate a preview to a report...

DoCmd.OpenReport "", acViewPreview

BUT! I want to know what is the vbCode to just send it to the printer directly?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Richard L @ 4/29/2014
Quarter 1 and quarter 2...etc...
I'd like to set the default dates to be Jan 1 to March 31 of the year you are accessing the database so if its 2014 it's begin date is Jan 1 2014 and end date it's March 31 2014.  next year it's 2015, so 01/01/15 to 03/31/15... and so on a default date.  what's the best way to go about this?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Janine H @ 4/28/2014
upgraded to 2007 trying to do put a button on a form to open another form but get error "The expression on click you entered as the event property setting produced the following error Ambiguous name detected "Opencontactbutton_click.   Help
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Deon Riley @ 3/7/2014
Hi Jyotsana,

If I understand your question - you want the ability to open several external access databases individually from a single switchboard form using individual command buttons.

I've got something similar running and am using this code, which works just fine.

Private Sub CarParkBtn_Click()

    Dim appAccess As Access.Application
    Set appAccess = CreateObject("Access.Application")
    appAccess.OpenCurrentDatabase "C:\Users\Desktop\Car Parking Database FE.accdb"

End Sub

So, make and name a button for each application you require and all you have to do is copy and paste the above code into your button; change the file path name and database name to suit each application for each button and it'll solve your problem.

There should be no limitation on the number of databases you could open.

Best regards,

Deon
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Joe S @ 3/5/2014
I have to count overlapping days from several timelines. Any ideas?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Jyotsana J @ 3/5/2014
Hi Richard

My company wants me to create a ms Access main screen on which 15 databases can be placed and that could be the one stop shop to access all databases. I know we can do open form operation after throughing a button their. But i was thinking is their any way out if we can have open database operation by through button on one main ms access datasheet.
jj
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Ronald R @ 2/28/2014
Thanks for help I did as you suggested but I did not find syntax errors. (Additional Information) This db contains a class list for students. A combo box is use to designate "M" and "F". I typed in a female student and it worked fine. I type a male and the columns still shift to the right. Can you or anyone help me?

Show Just This Thread        Post Reply
Read Microsoft Access Forum by teresa a @ 2/27/2014
Is there a macro, formula, or VBA way to remove a embedded file from one cell (lets say C2) and put it into another cell(C 13) in its respective row. I am not sure how this can be done.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Derreck Sunderland @ 2/24/2014
Hello Elvira M,

You're right, I meant iif(NOT isnull()....Thank You for bringing that to my attention. Proofreading is the most valuable skill in code writing.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Elvira M @ 2/24/2014
I think the mistake is a formal one.
it should be "not isnull ... " and not
"not(isNull).... "
Here an example of what works:

=IIf(Not IsNull(DSum("[Belopp]";"[SupplierFakturaDetailT]";"RedoBelegnr =" & [RedoBelegNr] & " and Konto = 5555"));DSum("[Belopp]";"[SupplierFakturaDetailT]";"RedoBelegnr =" & [RedoBelegNr] & " and Konto = 5555");0)
Show Just This Thread        Post Reply
Read Microsoft Access Forum by MUBEEZI M @ 2/23/2014
Dear Hassan,
please share your DLOOKUP code so we can help you effectively
MICAH
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Derreck Sunderland @ 2/23/2014
Hello Hassan,

What is the DLookup formula you are having #error message with? There may be a typo, or you may be looking up a value that has null records. If that is the case, you will get an #error message where the field is null.
I use to get a lot of error messages when I tried to look up fields where dome of the records were null. I tried this formula:

=IIf(Not(isnull([Thefield]),DLookup("[Thefield]","[RecordSource]"," ")

This is saying:
if the field is not null, then give me the field, else give me a blank space "".

This worked for me on many occasions.

There are other reasons you might get error messages, and you may have another problem that needs another solution.

Try sending the formula that is giving you the #error message and I'm sure a lot of us would be able to help.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Hassan Abadi @ 2/22/2014
Why would i get #error message doing a DLookup?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Gillian C @ 2/17/2014
Can you have multiple sub-forms?  
Such as:
Main form gathers interests, which has a combo box breaking down the interests, but those interests have sub-categories as well.  And be able to select as many as apply.
Thanks for any help.
Gillian
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Dennis P @ 2/14/2014
I have a question about a totals query that I am working on. I have several yes/no boxes in the table that I am using. If I total one that is yes I know how its done.
PayingTotal: Format([PayingMBR]*-100,"Currency")

I have one of the yes/no that has a charge if it is no. How do I do it when its a null value?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by siddik @ 2/14/2014
Hi Richard
I have made an accounting database with invoice. When a customer pays against multiple invoices, how should I link the single payment against many invoices amount & update the outstandings balance?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Charlotte @ 2/10/2014
Any and all, what would be a good make of external hard drive to use as a backup for my database?  i.e., Toshiba, Buffalo, etc.?  I've heard external drives are not very reliable...?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Martin D @ 2/4/2014
Colours and Colour Themes unavailable for changing in Access 2013. I'm using Design view in Forms but The Themes are greyed out. Can anyone help?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Elaine @ 1/24/2014
Discount rate tab, I put in the discount number but keep getting a 0 and not the number.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Richard L @ 1/20/2014
This odd I want to sort a continuous sub form by year... For some reason access wont allow it...I don't get error messages .. it just doesn't allow it???
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Carlos @ 1/16/2014
I've got the following ERROR MESSAGE, but no information as to how to handle it....THE DATABASE ENGINE COULD NOT LOCK TABLE "tblContactProduts" BECAUSE IT IS ALREADY IN USE BY ANOTHER PERSON OR PROCESS. To my knowledge, no other table was open that I could see. I was practicing creating Relationships with Enforced Referential Integrity. Any Gurus out there that can clarify this situation. Thanks!!
Show Just This Thread        Post Reply
Read Microsoft Access Forum by William B @ 1/15/2014
Is there a way to make a textbox change colors (say to yellow) if it is missing information?  Say you are looking at finding parts of an address (such as a ZIP Code) that are missing for a group of individuals.  If it is not null then it is the normal color (say background 1), if it is null, then it changes to yellow.

Thanks.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Greg Davis @ 1/7/2014
Richard I've been developing an accounting program where income is entered, bank deposits, payouts, etc. The problem I'm having is with the balance carried forward each month. I initially need a means of imputing it manually the first time and then doing the calculations automatically from then on. I'm having trouble finding the best place to put it. I tried one of the tables and tried creating a separate table but nothing working properly. I would greatly appreciate some suggestion in this regard.
Tks, Greg
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Derreck Sunderland @ 1/7/2014
Hello Larry,

To create a button to open a form to a new record you can use either the macro builder or a VBA event procedure.
The Macro Builder
1. Create the button. If the wizard pops up, close it.
2. Open the Property Sheet and click the Events tab.
3. On the OnClick row, click the   button to the far right of the row.A dialog box will open with three choices,
a. Macro Builder
b. Expression Builder
c. Code Builder
4. Choose the Macro Builder and the Macro Pane will open.
5. Type openform in the box and press tab.
6. In the form name type the name of the form. You can also choose the form in the dropdown list.
7. The form view will default to form. This is what you want.
8. In the data mode type add or choose it from the dropdown list.
9. Save it and return.
VBA Event Procedure
1. Repeat steps 1-3 above.
2. Choose the Code Builder
3. In the Code Builder pane you will see:
Private Sub Command0_Click()
DoCmd.OpenForm "ClientF", , , , acFormAdd
End Sub
Note: The Italic  DoCmd.OpenForm "ClientF", , , , acFormAdd will not be there. You will have to type this in yourself, with the name of the form you want to open. For Example, if you want to open a form named EmployeeF, type DoCmd.OpenForm " EmployeeF ", , , , acFormAdd.
I hope this helps!
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Terry H @ 1/6/2014
I am in the process of building an inventory database. Using the 300 series classes, I have the database put together. Now, my problem is, I need to have assemblies which uses the individual components in the inventory and I need a way to have the ability to  pull up the assemblies BOM and tell the database to build me 5 of each complete assembly and have the quantities of the individual components removed from the main inventory. I also need the ability to reverse this. Such as, disassemble and the components get credited back to the main inventory.
Can someone shed some light on how to relate the tables and have this happen?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Robert M @ 1/5/2014
SubForm To SubForm
Can you have a SubForm to Form?
I have a project time entry form where the 1st level is to select the persons name 2nd level or tier is the date with the 3rd level or teir is each project with the time, phase, dept, task for that day.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by David @ 1/1/2014
Hi Alex
I am using MS Access 2010.  I followed the instruction you provided and somehow it didn't work out.  
thanks
David
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Alex Hedley @ 12/29/2013
Have you seen this tip?

http://www.599cd.com/tips/access/incrementing-your-own-counter/

Example
ECO2013-001
ECO2013-002

Sample:
="ECO" & Year(Date()) & "-" & CounterCodeAbove
Show Just This Thread        Post Reply
Read Microsoft Access Forum by David @ 12/26/2013
Hi,
I need help to make a new record automatically generate the new number very the Add New button is clicked.

I created a table, ECO Table.  In the table this is a field called ECO Number.

On the form, I would like the new ECO Number automatically generate by itself, please see below:

ECO2013-001, ECO2013-002 and so on..
2013 is the current year. and I need this year will be automatically generate whenever a new comes...

ECO2014-001, ECO2014-002 and so on...
Could you please help me out?
Thank you.
Show Just This Thread        Post Reply
Read Microsoft Access Forum DLookup by Derreck Sunderland @ 12/17/2013



I don't know if this will help with Craig J's DLookup with e-mail issue, but sometimes I need to generate a report with the DLookup function and I get #type errors when the field I want to lookup is null. This doesn't look good on the reports. To get rid of the #type error I use this function.
=IIF(Not isnull([theFieldToLookup]),DLookup("[theFieldToLookup]","[theRecordSource]]),"").
Show Just This Thread        Post Reply
Read Microsoft Access Forum by joey b @ 12/15/2013
As you might remember, I am building a Airport database.

I got information about airports over 28 years.

For this example I got passenger numbers and Aircraft movements.

When I select an airport and after that an year in the form Air movements, and after that I choose an other airport it keeps jumping back to the first Airport. And when I put two subforms in an unbounded mainform and connect them by parentlink and childlink subform2 only responds to one combobox(year) and not to airport.

It is a mess. Should I use VBA, or another way?

Thank you
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Alex Hedley @ 11/23/2013
Hi John,

You could split the db so that each user has a front end - this can sometimes take up a lot of space for all the Forms/Queries/Reports so then they only need to contact the server to get the Table information.

Possible useful Seminar:
Access Split Database Seminar
Check out the outline.

Alex
Show Just This Thread        Post Reply
Read Microsoft Access Forum by John B @ 11/19/2013
I have a database that is for multiple store locations. I would like to have network accessability, not web. I put the entire database on network public server but it bogs down. I understand that there is a way to have some of it on the network and some stored locally. Do you address this in any of your classes/seminars.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Sherri @ 11/12/2013
Thank you for your help.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by David @ 11/7/2013
"Lawyer Name----- Lawyer Amt ------Lawyer 2----- Lawyer 2 amt----- Employee----- Employee amt -----Employee 2----- Emp2 amt" is crying out for related tables (LawyerT and EmployeeT) on a one to many relationship. Further a Date field in the main table, say "entrydate" would avoid having 12 tables from which to pull to get yearly totals. You seem to have created a flat database without taking advantage of the merits of a relational database. You can always sort and filter on the entrydate field to get monthly totals.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Willie @ 11/4/2013
Dear Richard

I have made a form with drop down list on a form and i wan to create products depending on the different options i choose in the lists.

After i have chosen all the cretria the name of the product must include them all
Example: Folded Leaflets / 300gsm Silk Creased / A4 folded to A5 / 1000 qty.

In the Query I use the following

ProductName: =[Item] & " / " & [Paperstock] & " / " & [FinalSize] & " / " & [PP] & " / " & [Qty]

The answer I get is the following:
1 / 3 / 30 / 4PP / 1000

This is the ID`s of the items in the list.

How do i display the Item Name and not the ID

PS my drop down lists display the Names not the ID`s
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Jim @ 10/27/2013
Rick,

I know you are sitting around just hoping that people will come up with more stuff for you to do but I have a suggestion for another seminar for you.  I have now built a pretty complex (for me) database and have intermittent issues with correcting some code that malfunctions.  Finding the errors has been a hit or miss process and I was wondering if you could put together a seminar on debugging code?  Use of the watch window, F8, F9 keys, breakpoints, immediate window, etc., etc.  It would be a big help to me and others I'm sure.  Maybe you could put it together on one of your trips to the grocery store...Just kidding of course.  Have a happy upcoming holiday season and I look forward to maybe this new seminar as well as my continued interest in the barcode inventory seminar.  Thanks as always.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Charlotte @ 10/25/2013
I have a query with three tables that works, however, it also produces the entire set of fields from the first table even though I have criteria that ask for only first name and last name. the remaining first table fields follows the third field.  The second and third tables do not add unasked for fields.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Carl K @ 2/14/2013
Lesson 323, using windows 7 and Access 2007.  The ADO challenging.  I am getting atleast the following compile error "Invalid use of New keyword"  I do have the reference and code as you directed.  Any ideas as to wha is wrong?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by MUBEEZI M @ 2/14/2013
Dear Mike,

I am afraid you may have to provide more details in order to be helped. For instance, when you mark products as received, is this at form level? Or are you doing it in a query or table directly? The answer might be different in different situations.

If you have the running total as a calculated field, then all you may need is to refresh your form with Me.Refresh
You would add that code in the After_Update event of the control that marks products as received.

MICAH
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Mubeezi Micah @ 2/13/2013
Dear Lilly,

Below is a step by step description of what Richard is talking about. Please adjust the code to suit your situation.
In the below example;
1. I have a Table named ProductT and four fields: ProductID (AutoNumber), CatalogNumber, ItemDescr and Result as Text fields. NB: WITH NO SPACES in the field names.
2. I have the sample data that you gave us as example in the table [WITH THE Result field left blank]
3. i create a form with a button and i have the code below in the On_Click Event
Dim db As Database
    Dim rs As Recordset
        
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM ProductT", dbOpenDynaset)
        
    While Not rs.EOF
        rs.Edit
        
        If InStr(rs!ItemDescr, rs!CatalogNumber) > 0 Then
            rs!Result = Replace(rs!ItemDescr, rs!CatalogNumber, "")
        End If
          
        rs.Update
            rs.MoveNext
    Wend
                    
    rs.Close
    db.Close
        
    Set rs = Nothing
    Set db = Nothing


Good luck in your project!

MICAH
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Mike @ 2/13/2013
when you u mark products as received it should add to qty on hand for a running inventory, I cant figure out how to make it add without physically adding...please help
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Lilly Nguyen @ 2/12/2013
Richard,
I have managed to replace if the Catalog numbers are at the front of Item Descr field. but i am not sure how to do it if the catalog number are in the middle or at the end of Item descr field. Here is my sql
UPDATE [PO HISTORY] SET [PO HISTORY].[Hospital Item Description] = IIf(Mid([PO HISTORY]![Hospital Item Description],1,Len([PO HISTORY]![Vendor Catalog Number]))=[PO HISTORY]![Vendor Catalog Number],Mid([PO HISTORY]![Hospital Item Description],Len([PO HISTORY]![Vendor Catalog Number])+2,Len([PO HISTORY]![Hospital Item Description])-Len([PO HISTORY]![Vendor Catalog Number])),[PO HISTORY]![Hospital Item Description]);
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Carl K @ 2/11/2013
In Lesson 313, using windows 7 and access2007.  I split the database and then moved it to c:\Database.  When I go through the Linked Table Manager process as described in the lesson but it never updates the link but continues in a cycle asking me to select the new location which I continually do but every time I click the Open button it comes right back to the same request.  Any ideas on what I may be doing wrong?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Richard L @ 2/9/2013
Currently, based on your videos I have a search button and field to look up records.  When the button is clicked a form pops up and gives me the results.
What i want is to have the results in a sub-form on the main form where the search fiels and button are.  Thereby, entering for example last name in the search field and click the button on the form itself has a list of records to select from.  How do I get a button to requery a subform.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Jess @ 2/8/2013
Hi everyone

I'm having problems adding more information of my customers on their Databank
so here it goes

I have a company ABC but in this company I have 4 or 5 contact people, so what I would like to do is to add those people and their position in the already created company database.

can anybody please help me with that, step by step?? btw I'm using Access 2010

Thanks

Jess
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Alex Hedley @ 2/1/2013
Lets use your example:

ProductName
Flyers/135Gsm/10 000/Gloss/Rounded Corners

Item: Flyer
PaperStock: 135gsm
Finish: Gloss
Corners: Rounded Corners
Quantity: 10 000

Answer 1/1/7/3/2

I'd assume your combo is made from a table with Fields:
ID
NAME
...
So Item table has
1 flyer
2 item2
... etc
And you base your combo on that, repeat for the others.

Then to back the product name you are saying
ProductName: Item & "/" & Paperstock & "/" ...

Now if this is the case then Access sees you want the value from the combo and takes the first field which is ID and therefore returns a number.
If you want the word Flyer to be shown use the ComboBox .Column() property which says return the column number I pass it.
It is has an array index starting at 0 so Item == Item.Column(0) == 1
Item.Column(1) == Flyer

Do this for the other combos and you will get the full product name as required.

Alex
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Willem @ 2/1/2013
Richard, I am trying to make a form with 10 different dropdown options. After choosing the correct option in the drop downs the ProductName is created by adding all the choice together.

ProductName
Flyers/135Gsm/10 000/Gloss/Rounded Corners

Item: Flyer
PaperStock: 135gsm
Finish: Gloss
Corners: Rounded Corners
Quantity: 10 000

Currently my answer is 1/1/7/3/2 which is the ID of each item in the dropdown box
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Bill Allan @ 1/31/2013
Richard is a swell guy!
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Bonita @ 1/30/2013
I'm wanting to create a database for a speed dating service. We want to keep track that John, Larry, Mike, Joe, Sally, Kate, Rachel and Sue went to the Nov. 12/12 event and the Dec. 21/12 along with 5 new men and 5 new women and the Jan. 6/13 event. Do I do this by create different tables for different events or tracking it in the one table with people. How best to track that John wants to connect with Kate and Sue (Sue being a mutual match) and Sue also wants to connect with John (mutual match) and Larry.
Hopes this makes some sense!
Thanks for any input.
Cheers,
Bonita
Show Just This Thread        Post Reply
Read Microsoft Access Forum by THop @ 1/29/2013
I was just wondering; why don't you use the page header on your forms to hide the fields you don't want seen instead of making them invisible in the detail section? Just wondering if I'm missing something.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Rick @ 1/21/2013
I have an Idea... On your lesson re: Search Forms that lookup specific Records in which I click on the results and it opens that record.
What I want to do is have those results in a sub-form so when I enter a search, it list the results underneath and I can click on them to open a form.
I don't know how to do it..  It doesn't update when I do the search and if the filed is empty, it lists all the records.
Can you help?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Rick L @ 1/21/2013
In one of your lessons you mentioned how you could have a class on how to make your forms look pretty...  Just so you know, I'd like to see that class...  The reason why I mention this is A) the template you have looks really nice and B) when I installed Access 2000 on my computer "NorthWind" was a template database demonstating the program, and I thought there were a lot of cool ideas I'd like to learn.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Kevin Robertson @ 1/18/2013
Love your Access tips - keep 'em coming :)
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Vannak H @ 12/22/2012
Richard,

Thanks for having done most of homework for us. I have found it's hard to follow the books for myself however books are good for references or just like you said, when all else failed go for the books. I wished that I had known your website 5 years ago. I really want to get into the database especially with MS Access. Thanks again.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Deon R @ 10/22/2012
Hi Richard,

I believe Birthday wishes to you are in order - so may you have a very happy Birthday today and best regards,

Deon
Show Just This Thread        Post Reply
Read Microsoft Access Forum by David Zeglen @ 10/22/2012
Hi Kevin,

Thanks for the suggestion. So does this mean that I should combine all my contact tables (interns, media, business) into one table, and just create a category row within the table so that I can run a query that will filter the results?

I just want to make sure that I should consolidate all my contacts into one table before I actually put in the time to do so.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Kevin Robertson @ 10/19/2012
Try listing your events in a List Box and filter what you're looking for based on what is selected in a Combo Box.  You can do this by using a Select Case statement in the After Update event for the Combo Box and changing the RowSource(using SQL) for the List Box. Hope this is what you're looking for!
Show Just This Thread        Post Reply
Read Microsoft Access Forum by David @ 10/18/2012
Hello, I am trying to use relationships between tables, and I have a question. I have several tables with contact information divided by category: church contacts, media contacts, intern contacts, etc. and I have another table called an events table. I'd like to make a junction table between all my contact tables and the events table so that I can see who from my contacts lists have been to which events. Is there a way to do this, or do I have to consolidate all my contacts tables into one table?

Thanks in advance for any help.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Brian Ffisher @ 10/18/2012
How do i sent a form or report from my access 2003 database by e-mail ?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by SELBY H @ 7/2/2012
Thanks for all your help over the years from Selby to Richard Rost of 599cd
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Nik @ 6/4/2012
Say for example, I Glycated hemoglobin at the time of diagnosis and then one in one year and 2 year and so on. I also have to input cholesterol result in the similiar fashion, Please help me to create such data.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Nik @ 5/30/2012
First of all, thank you so much for this brilliant help you did to thousands of people like me by posting this incredible lessons. I created a data base for my research project. It is a prospective study. I needed to create a 10 year follow up for each heading, say example lipids profile and drug treatment like year1, year2 and so on for each heading. When I tried to save it does not save and a message appears like, you already have year1, year2 etc. Can you please help me how to create, save?

Reply from Richard Rost:

Nik, I would need to know a lot more about how your database is set up in order to offer any advice. You could easily make a query. I know I've covered reminders before in a couple of lessons. Also try these posts to see if they help: REMINDERS.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Try Hong @ 5/22/2012
I am so interested with this forum. I want to post some questions or comments as well

Reply from Richard Rost:

Well, post away. That's what the forum is here for. :)
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Alex Hedley @ 5/19/2012
Change the records from cycle so you can't go to the next one
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Err @ 5/17/2012
Hello Guys, I am working in a access database that I have prepared for the boss but he asked me to remove the scroll bar because he hates when he unintentionally jumps from one record to another.I tried to put scroll bar to neither or None but no result.I tried to do everything but it is not working. In the same databse I used a subform and it is ok. but the problem is with the main form.
Please, can someone help how to disable or remove the funcion of the scroll bar?
Thank you very mucht to all of you!
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Randy Peters @ 3/29/2012
Thank you. I have to figure out how to set up the tables to support the logic tree.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by lisa bustamante @ 3/17/2012
In the tutorial it was mentioned to put the telephone as text in a table. I can't find when I create a form how to change the telephone field to fit with the -, thanks for everything. Lisa

Reply from Richard Rost:

Well, I strongly recommend just saving the DIGITS in your table. In Access Level 3, I'm going to teach you how to use a FORMAT and/or INPUT MASK to display the phone number WITH the dashes. (I covered this in Access 102 as well).
Show Just This Thread        Post Reply
Read Microsoft Access Forum by BarryMc @ 3/16/2012
I need to create an export to excel that contains fields in the format unique feature id, defect 1, works 1, priority 1, defect 2, works 2, priority 2 and defect 3, works 3, priority 3. My problem is that this data is captured in access on a row basis i.e. unique feature id is repeated for each row followed by a different defectID, defect type, worksid, priority etc. What is required in terms of sql/vba to state the first line against a specific feature id is defect 1 and the second line is defect 2 etc.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Tant @ 3/2/2012
I have a form that has unbound fields I need to ensure that when I click my submit button on my form that this information is sent to my table so much VBA code involved really pulling my hair out

Reply from Richard Rost:

Why not bind the form to a table? Then you don't have to worry about it. Otherwise, you'll need some code in your button to save the data manually - either with a recordset or SQL.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Mariea @ 2/24/2012
Sorry, Eddy. I see that my suggestion was totally useless since what you are really wanting, apparently, is the full path up to the document itself. Since I haven't taken this course from Richard, I am not qualified to offer any real solutions.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Mariea @ 2/24/2012
Hi Eddy. I'm not an expert so this is just a possible suggestion. Have you replaced the word "InitialFolder" with your actual Initial Folder? Like this, perhaps:
LaunCD(strform As Form, [Whatever As String = "C:\"]) As String
Show Just This Thread        Post Reply
Read Microsoft Access Forum by eddy geijselaers @ 2/24/2012
Hi everyone,

What I came up with, was just trying the code and follow the LaunchCD function as it appears to be on my PC.

This would be LaunchCD(Me)

Guess what? It works so far that it now opens the browser on "C:\", and I have to click through all the folders to get to my data. But this works.

Now I have to search for an update of my "comdlg32.dll" to the one in the course.

Maybe Rick can sent me one by mail?

eddy peanut
Show Just This Thread        Post Reply
Read Microsoft Access Forum by eddy geijselaers @ 2/21/2012
Hi Rick, girls and guys;

I am working on the AC Imaging Seminar and run into a problem I could use some help with.

First, you have to know I installed AC 2010 on my PC using MS Office XP.

Problem: lesson 12 on the seminar uses the LaunchCD (function?), and it didn't work on my PC. Surched some books and websites but cannot find a solution there.

So I am back on lesson 12 and comparing Rick's code and mine I see there is a difference in the LaunchCD statement which I cannot explain, so maybe one of you can?

as Rick typed in it apears as;
LaunCD(strform As Form, [InitialFolder As String = "C:\"]) As String
on my PC;
LaunchCD(Strform As Form) As String

My string on running give a Compile Error Wrong number of arguments or invalid property assigment.
Then of course it selects the LaunchCD in the VB code.

Can anyone help me out?
eddy peanut
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Rachelle Ward @ 2/7/2012
I have two issues that I want to resolve.  I need to learn how to populate an outlook calendar from an access query.  And I want to learn how to import data from a MS Word Form into Access programatically.  Do you have a module that would help me learn to do this.  
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Delbert J @ 2/6/2012
I would like to learn how not to overlap time for two worker. Ex. Two people working working from 9 am to 5 PM, they can not work within the same hour. Work week is from Sunday to Saturday.
Thanks
Delbert
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Alex H @ 1/11/2012
Check out this tip
http://www.599cd.com/tips/access/two-combo-boxes/
Al
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Ray Lammbert @ 12/30/2011
After, mmmmm too many years with Borland Paradox circumstance has a forced to change to Access. Not too sure how much use my knowledge of that program is likely to be?  After spending some searching the web time on both sites it seemed an appropriate place to start. Congrats on the freebee video’s those I’ve seen have been excellent in presentation. One of my interests is archiving photographs so the Access Imaging Seminar is first on my shopping list
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Satish Chand @ 12/29/2011
Hi I want to creat a track inventory with access 2007. how can i creat one.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Bob Mandrell @ 12/22/2011
So now I have completed Access 204 and I know a little bit more about macros.  I put the same code into the macro builder as a “run code” and saved it.  I then entered the name of the macro into the Before Insert line in my JobEntryF form.  I tried to enter a new job in the form and got the following error message:

A macro can call itself a maximum of 20 times.

Your macro contains a RunMacro action that calls the same macro more than 20 times.
Use a condition to stop the macro after it has been run 20 times, or call another macro with the RunMacro action.

OK  (button)


So at this point I don’t know why the macro is running more than 20 times.  Is each check of the value in my table considered a run?  Should I be entering the code as an SQL statement for the job number value in my form?  I will continue looking for the answer to this in the classes and some other “expert” I know, but any help would be nice….
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Jim Gray @ 12/21/2011
I think I have solved my own problem.  I saved the .mdb database as an access database .accdb in A2010.  Seem like that corrected the error at least as far as the command button issue is concerned.  I will keep you posted as to whether or not I have any further issues.

  
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Jim Gray @ 12/18/2011
I am trying to use a command button on a form to open a report that is connected to a simple select query selecting the current open form per Access 204.  I am using Access 2010.  After writing the DoCmd.openreport code everything works fine until I try to save it. I get an error "The form or report contains changes that are incompatible with the current database format. The form or report was not saved."  I know this code works in Access 2003 so there must be something unique about 2010?  Do I have to do this simple task via Macros perhaps???  The only way to save the form is to change the Has Module option to No which erases all the code I just wrote.  Any help would be much appreciated.  
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Ben Chua @ 11/11/2011
I have about 6000 records full of names of employees and it was entered like these: NY - Joanne Smith.  I noticed that all of them starts with a State then a space then a "-" and another space before their names.

I would like to remove the state and the dash

I used the Left function, StateRemove: LEFT(Employee,5)

I got the first 5 characters from the left of the Employee name.

Question:  What function should I use to remove the first 5 characters?

I believe this is a very easy question but I'm stumped LOL!

Reply from Richard Rost:

Try:

JustName: RIGHT(Employee,LEN(Employee)-5)
Show Just This Thread        Post Reply
Read Microsoft Access Forum by mike @ 11/8/2011
Richard,

Here is my code:

Private Sub ChangeMonth(X As Integer)
DoCmd.Echo False
C = Forms!DatePickerF!Calendar
Forms!DatePickerF!Calendar = DateSerial(Year(C), Month(C) + X, Day(C))
DoCmd.Close , , acSaveYes
Forms!DatePickerF.OpenCalender
DoCmd.Echo True
End Sub

Reply from Richard Rost:

Your code looks fine. Email me your database file. ZIP it up and send it to amicron@gmail.com.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by mike @ 11/8/2011
It is spelled correctly.  If I add a control and add DoCmd.OpenForm "DatePickerF" it opens fine.  When I add C = Forms!DatePickerF!Calendar it can't find DatePickerF.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by mike @ 11/8/2011
After adding a control to the calender form I receive the following message when clicking the button:

Run-Time error '2450':
Microsoft Access cannot find the referenced form 'DatePickerF'

which points to this line of code:
C = Forms!DatePickerF!Calendar

I tried adding this line in another form and get the same error.  

Any help to solve will be appreciated.

Thanks


Reply from Richard Rost:

Check the name of your form. Make sure it's spelled correctly.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Raju Pulidindi @ 11/5/2011
Hi All,

As part of my latest project, doing some 'Importing' in access using "transferspreadsheet" method into a table with pre-defined types for the columns. Some columns in the excel file contain spaces where there should be integers. So, obviously I am getting these "import errors". I believe these are warnings.
My first question is, any way to stop these errors.
My second question is, suppose if I do not use a pre-defined types, is there any way to generate the same type of columns as they are in excel formats.
As far as the data is concerned, I believe all the fileds are imported successfully.
Any one has any suggestions or thoughts about this situation.

Thanks in advance,

Raju
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Chiplion @ 11/1/2011
Dear Richard,
I am using Access 2003.
A table created with fields " LastName & FirstName". I can create FullName (join lastName & FirstName together) with QUERY Table.
How to create FullName inside the Table?
For your help please! Thank you

Reply from Richard Rost:

You can't. In Access 2003 you cannot have calculated fields inside of TABLES - only queries. That feature wasn't added until Access 2007 (or 2010... one of those two).
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Will @ 10/24/2011
I have many forms, so i thought it best to start them with numbers to keep tract or them.  will VBA code allow this or should i just stick with letters?

- Will

p.s. Happy Birthday
Show Just This Thread        Post Reply
Read Microsoft Access Forum by David @ 10/23/2011
May I join in with the wishes as you certainly deserve it from Access students! Happy birthday.
David

Reply from Richard Rost:

Thanks!
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Mubeezi Micah @ 10/22/2011
Happy birthday Richard!...yes..i remember seeing this date in one of 300 level classes [?tracking student attendance]...Selby, you have a good memory

Reply from Richard Rost:

Thanks!
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Selby @ 10/22/2011
Dear Richard,

Just want to wish you a Happy Birthday for the 23/10/2011. I am sure it is your bithday on that date. I am sorry if I have that wrong. Thanks for your help over the years with the Access and the Excel Classes. excellent
Regards Selby

Reply from Richard Rost:

Thanks, Selby!
Show Just This Thread        Post Reply
Read Microsoft Access Forum by scott adkins @ 10/20/2011
I was just reading through some posts, and have a question...

Can I: assign a variable from the incomplete form,

use it in a dlookup on the parts table and have that result for the combobox?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by scott adkins @ 10/20/2011
Alex, sorry I opened this can of worms. I am sure that I am boring some people to tears.

The part is the combo box on the completed side, but text on the incomplete side.
It has just 2 columns ID(primary key) and text field.

I tried the Dlookup in a msg box and get error 2471. Am I just trying to make this too difficult?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Scott Adkins @ 10/17/2011
I was wrong with the error code 3494 again a run-time error.

No Delete query.
I dont know anything about recordsets, yet

Alex:as far as i know everything is working as it should.
Program flow is incomplete form, vba to open the complete form fired by a command button.
And, sll the other actions work fine

Richard: I should have permissions, i built this small database, just for purposes of figuring out the problem.

The hard drive has 100 gig of room or so.

No one else has access to this database.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by DK @ 9/22/2011
My question is about Timer Events.  I need to set up a timer evenet on everyone's copy of my database so that when changes are made to the database from various locations the data base is refresehed and all users will be able to view the changes. Are there any tutorials on how to do this? Also  is using Timer Events the best way to keep the data refreshed?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Lindray @ 9/19/2011
Richard ..

I tried throwing  Me.Refresh at the end of sub but the result is the same, displaying the same image for every record at the same time, only when I click the record shows the image that belongs to that record. for example .....

I have 2 records with these pictures dsc-2250.jpg, dsc-1410.jpg. Image control displays the first image for both records and just by clicking on the second record is where the image shows. but records provided for all ........ the code is in a continuous subform, if I change to a simple subform that I have no problem, but I need to be a continuous subform ....
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Wayne Loomis @ 9/15/2011
Need to know if I choose to use tab control, can I change the color of it, and how?
Show Just This Thread        Post Reply
Read Microsoft Access Forum by eddy geijselaers @ 9/3/2011
Hi everybody

Working on AC 324 I kind of came on a problem, which must be a MS XP/OfficeXP problem in the settings I cannot find.

The LoanAmortizer works fine for one item.
The "Show All - Show Current" label is where the problem gets visible.
I cannot get any "current" date past april fools day 2011. Of course I tried different entrances in years.

Tried Rick's Database "Mortgage" and it spits the same problem.

Where to look in the XP Professional settings or the Office XP Prof settings? (I suppose).
The yearsetting is up to 2050, by the way.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by ellesha @ 8/10/2011
Hi Richard,

For double casting combo box, I manage to create two combo box working, after update combo1, combo2 will filter the value I need from query.

However, when i close my form (single form) and come back again combo2 value is hide. I need to reclick combo1 inorder to see combo2 value. I try to use setfocus function after update event, but still not working.

Hope you could give me some advise what should I do.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Selby @ 8/1/2011
Dear Richard,
Thanks for your Help with SQL and really all your Access subjects/lessons.
My main interest is Access - however in the past used Excel (Very Basic Level). Getting interested in Excel again. Transfering data from polar running softare to excel and then into Access. Can see all my runs in Access, Calories, Heart Rate distance and time and a lot of other data. I could not have done any of that without your Help
Regards Selby
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Ulises @ 7/31/2011
I am glad I have followed the Access lessons and SQL seminars 1 & 2.
However I need a more specific help:
I am working in a Medical History (MH) which has Patients Table, ICD9 Codes tables (diseases), CPT codes tables (procedures) among others.
How in the world can I enter in a specific MH and select diseases that particular patient has had from the table Icd9Codes, for example. If I succeed doing that the rest may be easier, because the method is the same for almost all the parts of the MH..
I congratulate you for your excellent presentations and give many thanks for all the learning I have made from them.
Please give me this extra help  
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Alex Hedley @ 7/29/2011
I'd split the RelationshipT up. As it is stated that is just a relationship yet you have their name etc included.

Just create a ParentT with ParentID, LastName, FirstName ... now have your RelationshipT - ParentID, PatientID, RelationshipID and now you can add as many parents to a patient and not need to re-add all the same info.

A quick read about Normalisation might also be useful.

Al
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Will @ 7/19/2011
Hi Alex,

I tried running the code but a Run-time error '2465; came up "Application-defined or object-defined error"  

what does that mean, some of the forms changed color but not all.  i put it in a button, does it need to be a public sub or is a private sub ok?

- Will
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Will @ 7/19/2011
Alex,

Thank you for the quick response. ive been looking all over the web for this.  

great job

- Will

p.s. ill see if i can come put with new lesson ideas for Richared ;-)
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Alex Hedley @ 7/18/2011
Hi Will

After a bit of research I came up with the following.
You need to loop through all the forms, open them so you can amend properties then save and close.


Sub AllForms()
    
    Dim obj As AccessObject

    For Each obj In CurrentProject.AllForms
        DoCmd.OpenForm obj.Name, acDesign, , , , acHidden
        
        Forms(obj.Name).FormHeader.BackColor = vbGreen
        
        DoCmd.Close acForm, obj.Name, acSaveYes
    Next obj

End Sub


Just need to change the vbGreen to a colour of your choice.

You could create a separate properties form and have a list of things you wish to customise, add that in a button click and maybe add the colour picker dialog to make the choosing easier.

Alex

--
Could be a cool mini tutorial rich?

Alex

Reply from Richard Rost:

Might be interesting... I've only briefly covered looping through object collections.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by O F Low @ 7/9/2011
Dear MICAH
Thank you for your help. I follow your way and I had found it.
O f Low
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Mubeezi Micah @ 7/9/2011
Dear O F Low,

In design view, try and look out for a button named "Use Control Wizards". Make sure you click on this button. After that, try creating a new Combobox or Listbox again.
If you have done this already, then i may have no additional advice

MICAH
Show Just This Thread        Post Reply
Read Microsoft Access Forum by O F Low @ 7/7/2011
I am using Win 7 64 bit OS. Under create Form, when I click on "combo Box" in my Form design, the "Combo box wizard" does't appear on screen. Asking for help.
Thank you
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Cj @ 7/6/2011
I am developing an Access DB (2007) for use in a retail store. I good good advice here in April on implementing a bar code scanner to control inventory. Now I am considering using an Access Report as the P.O.S. receipt. I need help on the right choice of POS receipt printer and whether this would work well and print quickly. Will the Report print well-aligned without loss of paper? (If this has been dealt with before now, I must have missed it.
Thanks a lot!
Show Just This Thread        Post Reply
Read Microsoft Access Forum by David @ 6/3/2011
You do not need the last IIF, or if you must have it you need to add ,"" or something to show it does not fall into any of those categories before the first of the last closing brackets.
i.e.:
=iif(paymentForm="credit card",Price*6%,iif(paymentForm="credit manual",Price*8%,iif(paymentForm="voucher",Price*10%,iif(paymentForm="cash",Price,"")))) or
=iif(paymentForm="credit card",Price*6%,iif(paymentForm="credit manual",Price*8%,iif(paymentForm="voucher",Price*10%,Price)))

You had not finished the final IIf statement.
David
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Carlos @ 6/3/2011
I need some help. I am trying to do a query where I have this situation:
Price= $200.00, four payment options:
Credit Card, manually Credit, voucher and Cash.
If the payment is by Credit Card, I have to charge 6%,  manually charge 8%, by Voucher charge 10%, by cash no charge.
I did the following calculation:
=iif(paymentForm="credit card",Price*6%,iif(paymentForm="credit manual",Price*8%,iif(paymentForm="voucher",Price*10%,iif(paymentForm="cash",Price))))
but all I got was an error message. How can this be done?
Thank you for your help.
Carlos
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Judy Standifer @ 5/15/2011
Yes, it was spelling.  Works just fine now.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Judy Standifer @ 5/15/2011
In the WorkOrder seminar Lession 20, I tried to do the BuildListBox, but when I click on the "Show Closed", all the data in the list box disappears.  Do you have any idea why this is?

Reply from Richard Rost:

Not sure. I'd have to see what you've got to tell you for sure what's happening. Did you make sure your code is exactly the same as mine? Check your spelling (the #1 cause of errors).
Show Just This Thread        Post Reply
Read Microsoft Access Forum by eddy geijiselaers @ 4/11/2011
Dear reader,

Creating a winecellar database I want to be able to select or add data using combo boxes.

Because every wine has a year the name of the wine will come back from year to year. So listing the names in a combo box makes sense to me. This is the easy one.

A wine is from a certain country, region and even a specific “terroir”
Is there a way to come up with only the regions for the country in the list or just entered  in the “country” box?

I build tables for “country”, “region” to start with.
CountryT has the fields “CountryID”, “Country” and “Flag”
RegionT has the fields “RegionID”, “CountryID” and “Region”

Thanks again for your help
eddy
Show Just This Thread        Post Reply
Read Microsoft Access Forum by eddy geijiselaers @ 4/11/2011
Dear reader,

on lessons Access 324
using Access 2002

I followed the steps as given but failed to get it working properly.

at the line "dim db as database" there pops up a list to pick from but "database" is not in it
On saving the instructions a message pops up:
tranlated from Dutch it says something like: "a user defined datatype is not defined"

trying to sort it out I come to the conclusion that "database" is not the right ìnstruction anymore,
can you help me out?


this is the code:
Private Sub StateCombo_NotInList(NewData As String, Response As Integer)

'(drie keuzemogelijkheden als comentaar ingegeven)
'acDataErrDisplay  (=default error message van access)
'acDataErrContinue (verteld access om de message niet te tonen maar gewoon door te gaan)
'acDataErrAdded (geeft aan dat we de stappen ondernomen hebben die nodig zijn om die waarde toe te voegen in alle velden die nodig zijn)

'eerst gaan we de gebruiker vragen of hij de waarde willen toevoegen

'intypen:
If MsgBox("Add" & NewData & "to list of states?", vbYesNo) <> vbYes Then
    Response = acDataErrContinue
    Exit Sub
End If


'nu moeten we access laten weten dat en waar we de dat willen opslaan

Dim db As database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.openrecordset("StateT")
rs.AddNew
rs!State = NewData
rs.Update
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing


'Dim db As database
'Dim rs As Recordset
'Set db = CurrentDb
'Set rs = db.openrecordset("StateT")    '("StateT" is de tabel die hier gebruikt wordt)
'rs.AddNew
'rs!State = NewData    '(State is het veld dat gebruikt wordt)
'rs.Update
'rs.Close
'db.Close
'Set rs = Nothing
'Set db = Nothing

'Response = acDataErrAdded

'dit ververst de query en de tabellen



End Sub
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Theresa @ 4/8/2011
In Access 2007 I have a form that has text boxes in the footer for calculation. When it is in form view the footer displays at the very bottom of the form and that leaves alot of blank space in the detail section between the columns and the text boxes that shows the totals.  In design view I have decrease the size of the header, footer and detail section for just enough room for the controls.  How can I shorten the space in the detail section to display correctly in form view without all the space?

Reply from Richard Rost:

You really can't. The footer fields will always show up at the very bottom of the form.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by eddyrg @ 4/5/2011
Thankx for your quick reply Richard. I am using XP and Office 2000. So I ave to update at least Access then.
greetings from Holland
Show Just This Thread        Post Reply
Read Microsoft Access Forum by eddyrg @ 4/4/2011
From class 201
insurting a picture to a OLE-object seems to need *.bmp extension
question; is there a way to use other formats like jpg?

Reply from Richard Rost:

Yes, but different versions of Access are picky depending on what other software you have installed. You shouldn't have a problem in Access 2007 or 2010, but 2003 and older can be picky.
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Paula @ 3/22/2011
Thanks Richard....i'll go see if i can crack it tomorrow

Paula
Show Just This Thread        Post Reply
Read Microsoft Access Forum by Alex Hedley @ 3/7/2011
Hi Mary,
When you say cancel out what do you mean, is the record still dirty/unsaved? [little pencil at the top right of the grey record selector bar].
Have you tried moving to another record then moving back, this will save the changes.
Regards
Alex
Show Just This Thread        Post Reply

Collapse All Topics

 

Post Your Comments or Subscribe
    If you would like to be notified of new posts on this forum,
    just enter your email address below. It will be kept private.
 
If you just want to subscribe to get email updates when this forum is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
 
  Your Name:  Required
  Your Email:  NOT Public
  Subject:
  Comments:

 

Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 2+2:
  
  Notify me when the this forum is updated
  Remember Me for my next comments
  
 
 
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be. As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard

 

 

You may want to read these articles from the 599CD News:

6/20/2019TIP: Validation Rules in Access
11/20/2018Microsoft Access Developer 12
8/25/2018NEW: Access Dev 9, 10, 11
8/25/2018Microsoft Access Developer 11
8/25/2018Microsoft Access Developer 10
8/25/2018Microsoft Access Developer 9
8/23/2018Access Dev 11 is ONLINE
8/17/2018Access Dev 10 is ONLINE
8/15/2018Access Tip: Search Form
8/15/2018Access Tip: Locked v. Enabled
 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
 Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
 Info
 
 Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
 Help
 
 Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
 About Us
 
 Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
 Order
 
 Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
 Contact
 
 Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
 Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel 		Richard Rost Microsoft MVP