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  
NEW: Microsoft Access Developer Level 8    dismiss


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 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 Triple State Checkbox by Richard L @ 6/21/2018
I have a triple state check box that defaults to unchecked.  Once clicked it goes to null first then checked.  Is it possible to go to checked first then null?

Reply from Richard Rost:

The only way to accomplish this would be with VBA. Perhaps in the AfterUpdate event. You would have to manually control the cycle.
Show Just This Thread        Post Reply
Read Print using yes no DataType by Ingrid @ 6/11/2018
I have a form with a yes/no checkbox, I would like to be able to print the reports for all the records that have been check. The print button can open the record and print 5 copies but I also would like to only print the records that have been selected.

DoCmd.OpenReport "CertificatR", acViewPreview, ,"CertificatT.TransID=" & TransID
DoCmd.PrintOut , , , , 5
Show Just This Thread        Post Reply
Read string concatenation by John M @ 6/8/2018
I am having a small problem. I am trying to use a list box to show a list of forms, when you select a form from the list it will get the Record Source from that form. Here is the code:
Private Sub List0_AfterUpdate()

    Dim stFrmName As String
    Dim stRECSorce As String
    Dim myName As String
    stFrmName = Me!List0.Column(1)
    DoCmd.OpenForm stFrmName
    myName = Forms!clients.RecordSource
    'myName = (Forms! & stFrmName & .RecordSource)
    'DoCmd.Close acForm, stFrmName
    MsgBox "The information is " & stFrmName & " " & myName
End Sub
in the first myName line I entered it manually works fine. But in the second myName I get on the msgbox "Clients" and then the string Forms!Clients.RecordSource.  What am I doing wrong? I have tried several forms of string concatenation the way I understand it. Need a point in the right direction.
Show Just This Thread        Post Reply
Read Macro Open Form Where Condition by Richard S @ 6/7/2018
Hi folks,

Is it possible to have a compound "Where" when creating a command button to open a form ?  

The example in Advanced 1, Lesson 3 is a good starting point.  The condition is a simple "CustomerID=" & [CustomerID].

Is it possible (and how would you code) to have two conditions, such as:
"LastName=" & [LastName]  AND  "FirstName=" & [FirstName]   ?

Please advise when you get a moment.

Hope all is well & till later,
Rick S.
San Antonio, TX

Reply from Richard Rost:

You're close:

"WHERE LastName=""" & [LastName] & """ AND FirstName=""" & [FirstName] & """"

Show Just This Thread        Post Reply
Read Question regarding Headers by Elaine K @ 6/7/2018
I have a report that was created with headers. I added extra columns to the report. The underline of the header did not expand to include the new columns. How can I do this.
Show Just This Thread        Post Reply
Read Mouse right click stopped working by Gioia H @ 6/3/2018
I had this problem. My fix was under File>Options>Current Database>
Then under the section Ribbon and toolbar properties I had to make sure both the allow menus option were clicked. Then my users could right click and cut & paste, etc.
Show Just This Thread        Post Reply
Read Page Level Locking by Anne @ 6/2/2018
Record Locking Question:  Access Expert 21 for Lesson #5 was VERY helpful.  We are in a mulit-user environment with most users using Access Runtime.  The FrontEnd is on each users' C drive.  The Backend tables are on a shared network server.  All of our database forms (that have a RecordSource) have the Record Locks -> set to Edited Records.  The complied program is set to "Open databases by using record-level locking."   This works great some of the time.  However, each day the locks appear on more than the particular record that is being edited. This is problematic.  We think this happens when we

a . We import data and run routines (vba adds/updates tables) which (according to google) always use Page Level locking.   and/or
b.  We run production reports (the background vba handles delete/add to temporary tables for processing complex crosstabs . . . ) or any routine that has Updates/Appends "action queries"  and again (according to google) Page Level locking kicks in.

It appears that the .laccdb file switches from row-level locking to page level locking . . . From that point all users working are getting records to work that are "locked"  internally (but not programmatically ).  When the program tries to save the User and programmatically lock the record, it can not save the update because the record is locked internally.  So here are my questions:

1.  Is there a way to programatically identify and  "skip" records that are locked internally . . . until we get to one that is not locked internally?  and/or
2.  Is there a way to programatically check and change the page-level locking back to record-level locking after the routines that have "action" queries are completed? Right now, the only solution I can think of is to have all zillion users exit the program (runtime).  Be sure the .laccdb file is gone.  Then log in again.  Not very practially because everytime management runs the production reports locking switches to page locking.
3.  Do you have a suggestion outside this box that might work?

P.S.  If this is covered in one of your courses, I'll be happy to purchase it!
Show Just This Thread        Post Reply
Read Microsoft Access by jannette j @ 6/1/2018
I am doing Rick's search seminar  in lesson17 i get an error when i enter two criterias in the form i get an error  run Time Error 3075

Synttax error ( MISSING OPPERATOR) in query expression

here the code

Private Sub DoSearch()

  Dim Wh As String
  Dim SQL As String

  Wh = ""
    If FirstName <> "" Then
        Wh = Wh & "FirstName Like '*" & FirstName & "*'"
    End If

    If LastName <> "" Then
        Wh = Wh & "LastName Like '*" & LastName & "*'"
    End If

    If City <> "" Then
        Wh = Wh & "City Like '*" & City & "*'"
    End If

    If State <> "" Then
        Wh = Wh & "State Like '*" & State & "*'"
    End If

    If Phone <> "" Then
        Wh = Wh & "Phone Like '*" & Phone & "*'"
    End If
    If Wh = "" Then
        MsgBox " Enter at least one parameter value!"
    Exit Sub
  End If
  SQL = "Select * FROM CustomerT WHERE " & Wh
  DoCmd.OpenForm "CustomerF"
  Forms!CustomerF.RecordSource = SQL
If IsNull(Forms!CustomerF!CustomerID) Then
    MsgBox " No Customer found with given search parameters."
Exit Sub
End If

End Sub

I dont know what i am missing can some one help me please.

Show Just This Thread        Post Reply
Read Post by jannette j @ 5/30/2018
how do I would like to create a form in which a listbox displaying records from Project is filtered based on the selection of a comboboxs Dept and Tech , Date From and DateTO combo boxes containing the Project Data

in other words, i want to select "Deot " (value 1) in the combobox, "Tech" (value 2) in the 2nd combo box , "DateFrom" ( value 3) 3rd combobox, and "DateTo" (value 4) and have only the records from Tech that have value 1 in their ProjectNo field displayed in the listbox?

   MySearch "LastName", "Enter the Customer's Last Name:"
Show Just This Thread        Post Reply
Read Post by Raju H @ 5/30/2018
On a subform how to make invisible certain fields that are based on values of another field on that particular record (Not for the whole recordset of the subform)
Show Just This Thread        Post Reply
Read Mouse right click stopped working by Gioia H @ 5/29/2018
I had this problem a few times Where my users were unable to cut and paste using the right click.

I fixed by making sure that in Options>Current Database>Ribbon & Toolbar > Allow menus should both be checked.
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 Own Icons On the Ribbon by Jan H @ 5/18/2018
Is there any EASY WAY to use own icons on the ribbon?

Reply from Alex Hedley:

I've hopefully got a course released soon on creating your own ribbons.
There's a bit of XML involved in some hidden tables but it's easier once you know how.
Show Just This Thread        Post Reply
Read Duplicate Values on Multiple fields by Brent R @ 5/15/2018
I found your tips on "Microsoft Access Duplicate Values on Multiple Fields" it is very close to what I am looking for but I would like to allow it to check for combinations of First Names and Last Names and make sure there are not duplicates in the table how can I do this? I have a field in the ContactT called FName and LName and the combination of the two can not be duplicated in the table so when the form is closed I would like it to check before upodating and prompt with error when duplicate is found.

Reply from Alex Hedley:

Have you seen the Composite Keys Tip?
Show Just This Thread        Post Reply
Read I want to use access to track payments by Robert R @ 5/10/2018
I don t think you have covered how to handle payment when they bounce or when they decline if your customers pay by check or credit card.
Specifically how to keep balances correct in the event of a declined or bounced check and how to notify a rep if the accounting is a divergent department and you are the rep for the customer who gave you the bad check or credit card.

Reply from Alex Hedley:

You could add an extra column of cleared and set that automatically on Cash payments and then set them for others that need checking.
Show Just This Thread        Post Reply
Read Display a Single Record on a Report by Kirk R @ 5/10/2018
I have been trying to display a single record on a report and have run into a roadblock.  I have created a query based on the report and have set the criteria to Forms!Formname!Fieldname.  This seems like it should work, but everytime I run the query, the qyery asks me for the parameter forms!formname!fieldname.  The form is open and is set to the record I want to see, but i can't seem to get it to work.

Reply from Alex Hedley:

Check the name of the Field on the Form, it may have changed spelling.
Show Just This Thread        Post Reply
Read Forum Search by Gregory C. Smith @ 5/7/2018
How do I search the forum?  I didn't see a search box anywhere in the forum pane. I am trying to make a hot link in a subreport to open a form on that record. I have tried several WHERE statements in a macro.
I can manually enter a record in the parameter box that shows up in the error.  I have no trouble links in subforms to open a different form using a similar WHERE such as [RequisitionNum]=[Forms]![frmContractObligationsTracking]![sfrmContractObligationsTracking]![RequisitionNum]
I cannot figure out why I cannot create the WHERE in a subreport in a report to use the link in Report View.
The link in the subreport opens the form but does not pass the value of the AutoNo to the form's ARR_ID.

Reply from Alex Hedley:

There is a SEARCH box in the top right corner of every page.
Is the link in the main report or the sub report? Might just need the sub report value.
Show Just This Thread        Post Reply
Read Homework by William E @ 5/4/2018
I am on Access Expert level 10.  As a student, I thought I was doing great....until we got the homework. Then I failed miserably.  I haven't been able to complete one task correctly.  I find them to be much to difficult for this level, and it's very discouraging.  If anyone else is willing to admit this, here's a suggestion.  Instead of 3,4, or 5 assignments, how about just one.  And make it something that is in exact relationship to what we just learned.  Like an Iif statement after we learned Iif statements.  If there is no one else, and it's just me, maybe I should start the course over.  Thanks

Reply from Alex Hedley:

Try not to get too disheartened, it's annoying when you can't do something straight away but don't give up.
Can you share the question and what you tried and we can work together to see about a solution.
Show Just This Thread        Post Reply

Show Older Posts         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


Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 8+4:
  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:

8/17/2018Access Dev 10 is ONLINE
8/15/2018Access Tip: Search Form
8/15/2018Access Tip: Locked v. Enabled
8/15/2018Access Dev 9 is ONLINE
7/31/2018Microsoft Access Developer 8
3/23/2018Microsoft Access Developer 7
2/22/2018Microsoft Access Developer 6
2/22/2018Access Assistance Available
2/21/2018Putting Access Databases Online
2/18/2018Microsoft Access Developer 5

Visual Basicindex
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
About Us
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Gift CDs
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