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  
 

News      User Comments     History     Notify Me

1/1/2008 3:21:00 AM
Microsoft Access 321
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

 
Working with unbound ListBox controls, editing recordset data, multi-select listbox, adding & deleting recordset data.

Permanent Link
Course Link: Microsoft Access 321
Keywords: access
Post Reply

Generation Lookup Comment from Richard L @ 5/28/2015
Can you point to me where in the class lesson I can review it again?


Reply from Alex Hedley:

I think the OKTOSHIP function is created later in 326

You could do something like: you might need to amend the number before passing it, or pass in a date and do some manipulation on that.

Function myGeneration(number As Integer) As Double
  
  Select Case number
    Case 1 To 5
        myFunction = 1
    Case 6, 7, 8
        myFunction = 2
    Case 9 To 10
        myFunction = 3
    Case Else
        myFunction = 4
  End Select

End Function
Show Just This Thread        Post Reply
Generation Lookup Comment from Richard Lanoue @ 5/25/2015
Hers is a project I've been working on. When I enter a Date Of Birth, [DOB], I want to have it select, from a dropdown menu, A Generation.  Example  client is born in 1972, then the selection on the form will select "Gen X"... A client is DOB is 1982...after update it selects, "Millenial"... etc...

I tried to use an update query but it doesn't seem to work.  with record sets...do I send a date and get back an ID number based on Year(DOB)...
Am I Going in the right direction?  If the DOB field is null I want it to select an ID 7 which is on the dropdown form "Unknown"...

Is there a better way?


Reply from Alex Hedley:

You could create a Function in VBA that takes an Year and returns a value.
Show Just This Thread        Post Reply
Microsoft Access 321 Comment from Alan L @ 7/20/2014
OK, so I know 5 years have gone by since the last post on this forum. I have been trying, for hours, to figure out why I am getting a Run Time Error 6 Overflow when I try to delete items. I am fairly certain I followed your code verbatim except that I am trying to adapt it to a different set of records.

Private Sub DeleteSelected_Click()
    
    If MsgBox("Are you Sure?", vbYesNo) <> vbYes Then
        Exit Sub
    End If
    
    Dim I
    For Each I In StudentList.ItemsSelected
    DeleteSelect StudentList.ItemData(I)
    Next
End Sub

Private Sub DeleteSelect(I As Integer)
    On Error Resume Next
  
    Dim db As Database
    Dim rs As Recordset
  
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM StudentT WHERE StudentID=" & I, dbOpenDynaset)
      
      
    rs.Delete
      
  
    db.Close
    rs.Close
    Set db = Nothing
    Set rs = Nothing
    LoadStudentList
End Sub
Show Just This Thread        Post Reply
Comment from michele finizio @ 3/10/2013
hi richard
for the problem that occur after 6:54 minutes lesson 5 i have tried with:
CustomerList.Column(0) and it works
Show Just This Thread        Post Reply
Comment from Debra Edelman @ 1/21/2013
Loved the class. I didn't think that it was either too fast or too slow. And I'm using each class as a continued resource for myself; I wear many hats and it isn't always easy to remember everything. The only thing that could be better is if you revive the tests. I would like to take them so I can be sure of my progress.
Show Just This Thread        Post Reply
Manipulating Items in List Box Comment from Aunali B @ 1/11/2013
Hello Richard:

After trying for a while and referring to our valuable tutorial, i tried the following code and it worked perfectly.

Private Sub Command2_Click()
Dim I
For I = 0 To List0.ListCount
Me.List0.Selected(I) = True
Next I


End Sub

I hope the above is the right way to do or if there is another way you would recommend.





Reply from Richard Rost:

You got it perfect. Good job.
Show Just This Thread        Post Reply
Manipulating Items in List Box Comment from Aunali B @ 1/11/2013
Thank you Richard for your prompt reply, I appreciate your service.

I have types the follwing into the vab code:

Private Sub Command2_Click()

Me.List0.Selected(4) = True


End Sub

and only item no 4 is selected. I would appreciate if you could kindly show me what what arguments to use in order to select all and i am sure the opposite code will deselect.

Thanks and regards

Aunali Bhalloo
Show Just This Thread        Post Reply
Manipulating Items in List Box Comment from Aunali Bhalloo @ 1/10/2013
Access 321   Video: 4

Hello Richard

Kindly let me know if there is a way to swap or select all the records in a list box with just one click to a button and also to clear or de-select all the records with a click of a button
(time index 0.41)


Reply from Richard Rost:

Well, you can manipulate what's selected or not selected by changing the .Selected property. As far as swapping them, that would depend on how you're maintaining your sort order. Are these things possible? Absolutely.
Show Just This Thread        Post Reply
OpenForm with CustomerList Comment from Ben Chua @ 11/20/2011
What did I do wrong?
I noticed that when I docmd.open form, the form opens but it is giving me to add a record.  I turned on record selectors and it shows me 1 of 1 the endrecord is Greyed out.  

Example is from previous command
Docmd.openform "CustomerF",,,"CustomerID=" & CustomerList

Even I change the customerlist to = 4

I did some test, maybe I am doing something wrong in the code.

I created a new button on a new blank form, I called it test.  The event for the open click button is

Docmd.openform "CustomerF",,,"CustomerID=4"

This is just to satisfy me and you.

The result is the same, the form open but it give me a blank for as if I will enter a new record.

Waaaah!




Reply from Richard Rost:

Check the underlying table. Do you have a record with and ID of 4?
Show Just This Thread        Post Reply
OpenForm with CustomerList Comment from Ben Chua @ 11/18/2011
Yup I did it at first but the " and ( sometimes mess mess me up.
Show Just This Thread        Post Reply
OpenForm with CustomerList Comment from Benjamin Chua @ 11/18/2011
Access 321 Time 5:24

when you double click a list box, For example Mike Schmitd, it returns a value of 4.

In CustomerT this 4 is the CustomerID of Mike.

Question: How can you open a form for CustomerF where CustomerID =" & CustomerID?

Because if I do a msgbox customerID it returns a null.


Reply from Richard Rost:

The value is stored in the list box (CustomerList). So you could create a button that says:

Docmd.Openform "CustomerF",,,"CustomerID=" & CustomerList

This will get translated by Access to:

Docmd.Openform "CustomerF",,,"CustomerID=4"

Got it?

Remember "CustomerID" is the field on the FORM YOU'RE OPENING - in this case, CustomerF.


Show Just This Thread        Post Reply
Help with SQL strings Comment from Ben Chua @ 11/17/2011
Compile Error

MySQL = "SELECT * FROM CustomerListbyStateQ WHERE State='" & State & "'" And WorkDate > #" & DateFrom & "# And WorkDate < #" & DateTo & "#"


Reply from Richard Rost:

That was my fault... one too many double-quotes in there. Try this:

MySQL = "SELECT * FROM CustomerListbyStateQ WHERE State='" & State & "' And WorkDate > #" & DateFrom & "# And WorkDate < #" & DateTo & "#"


Show Just This Thread        Post Reply
Help with SQL strings Comment from Ben Chua @ 11/17/2011
I am getting error 13 Type Mismatch.

Here are my observations
1. the dim MyDateField does this have to set as a String or Date?
2. it is currently set as Dim MyDateField as Date
3. when i get the error and click debug,
when i mouse over the yellow line where it says MyDatefield it is showing as MyDateField = 12:00AM
4. is this the reason why it is type mismatch because I am comparing time to a date? because the DateFrom and the DateTo is coming correctly, it shows DateFrom = 7/1/2011 and the DateTo = 7/15/2011
5. When you say "you might need to take your DateFrom and DateTo values OUTSIDE of the string" what exactly do you mean by this? put it outside the quote?


Reply from Richard Rost:

MyDateField is the name of the date field you're using in your table. Don't DIM it. Replace it with the name of your table's field.
Show Just This Thread        Post Reply
Help with SQL strings Comment from Ben Chua @ 11/17/2011
Should I create a Dim MyDatefield as String?

then call it?

I only have i field for date in WorkLogT, which is Date.

Also I created a two text boxes DateFrom and DateTo

In my statement I say this...
MySQL = "SELECT * FROM WorkLogT WHERE State='" & State & "' And MyDateField > DateFrom And MyDateField < DateTo

I also created a query to try it out
MySQL = "SELECT * FROM CustomerListbyStateQ WHERE State='" & State & "' And MyDateField > DateFrom And MyDateField < DateTo

I make no sense do I? :o LOL!

man this is addicting.


Reply from Richard Rost:

Looking good so far, but a couple of notes...

First, never make a field called "Date". Like "Name", "Date" is a reserved keyword in Access. Best to avoid it. Call your field "WorkDate" or something. It will WORK, but then you have to remember to put [brackets] around it all the time or else Access might think you mean the Date() function.

Next, if your SQL works the way you have it written, that's fine - go with it. However, you might need to take your DateFrom and DateTo values OUTSIDE of the string (like you did with State). And remember, date values need to be enclosed in # signs. So your SQL would look like this:

MySQL = "SELECT * FROM CustomerListbyStateQ WHERE State='" & State & "'" And MyDateField > #" & DateFrom & "# And MyDateField < #" & DateTo & "#"

Which Access will translate into:

MySQL = "SELECT * FROM CustomerListbyStateQ WHERE State='NY'" And MyDateField > #1/1/1990# And MyDateField < #2/1/1990#"

And yes, this stuff is VERY addicting! :)


Show Just This Thread        Post Reply
Help with SQL strings Comment from Benjamin Chua @ 11/16/2011
Access 321
How do I select to get between dates?

example: SELECT * FROM WorkLogT WHERE is between from a date and a date???


Reply from Richard Rost:

SELECT * FROM WorkLogT WHERE MyDateField > #1/1/2001# AND MyDateField < #2/1/2001#

If those values are fields, which one of my astute students can show Benjamin the correct way to display it as a STRING?
Show Just This Thread        Post Reply
Comment from Kenneth Lange @ 11/26/2010
Being an Access 'Newbie', I Really Enjoy All the Additional Knowledge & Comments.  I am Enjoying your Recordset Series!
Show Just This Thread        Post Reply
Comment from  Chris @ 3/28/2010
Richard, You may want to have a cavet on these course (Access 321-329) regarding the issue with ADO vs DAO libraries on Access 2000, I definitely had the problem that you referred to in Access 320. In fact, I had to also deactivate the ADO or the material would not work.


Reply from Richard Rost:

I think I did mention that in there somewhere. Some of the versions of Access (I can't recall exactly which ones) do require you to not only move DAO higher in the priority list, but also de-reference ADO. It's a mess. I'm glad they finally decided to standardize on DAO though. It's much more flexible.
Show Just This Thread        Post Reply
Comment from Naomi Roll @ 2/9/2010
Richard, how can I sort or order my listbox. Eg. Surname ascending, then Firstname ascending and not have the box sorted by ID?
Your help is always appreciated. Thankyou


Reply from Richard Rost:

Naomi, the easiest way is to feed your listbox with data from a query that's sorted the way you want it. Create the sorted query FIRST, then use that to fill the listbox with data.
Show Just This Thread        Post Reply
Comment from Richard Rost @ 1/15/2010
I know I talked about them in one of my classes (I think AC311). Basically you want your search string to say:

State="NY"

But you're sending the NY dynamically with a field, so you need to say:

State='" & StateField & "'"

You can either use single quotes inside of double quotes or two double quotes:

State=""" & StateField & """"

Make sense now?
Show Just This Thread        Post Reply
Comment from  Twila @ 1/13/2010
I do not recall you ever discussing this bit of code before:  WHERE State= '" & State & "'"  

Could you take a minute and explain what the extra "" and ' ' are for so that we can understand when and where to use them?  Thanks!
Show Just This Thread        Post Reply
Comment from Richard Rost @ 10/7/2009
If the subforms are simple (no complex objects like combo boxes on them that require MORE querying) then I would say both are probably about equally efficient. That's just my instinct though - not scientific fact. :)
Show Just This Thread        Post Reply
Comment from BRYAN binkerd @ 10/7/2009
Rick,
I have a company that does
service work
Whole Good Tractor Sales
Parts Sales

I have 3 different forms
my question is this
I have a customer form
The customer form has 3 subforms with source written in sql.
Which is more efficient,
3 subforms on one form or 3 listboxes sourced by recordsets.
Thanks,
Bryan
Show Just This Thread        Post Reply
listbox Comment from Richard Rost @ 10/2/2009
Bryan, I looked around a little while researching my upcoming Access 2007 class, and all I could find was a definitive "what's new" guide on Microsoft's web site that covered newly added features. It showed a list of new features in the "normal" user part of Access, but didn't really go into VBA much. If anyone else knows of such a guide, I would LOVE to get my hands on it too, because every time I record a lesson, I always have to manually test it in Access 2007 then 2003 then 2000 (sorry, don't have XP anymore) just to make sure it works for EVERYONE.
Show Just This Thread        Post Reply
listbox Comment from Bryan Binkerd @ 10/1/2009
as always, you rock. To counter clarify, is there a list of features that are available in 2007 vs 2003 vs 2000.  The reason I said conflict is because I checked the 2003 file format to 2000.  I thought this was the "Majic Fix".  As always thanks for your replies
Show Just This Thread        Post Reply
Comment from Richard Rost @ 10/1/2009
Bryan, I addressed this problem in a TIP here: Listbox AddItem for Access 2000. I'm pretty sure I posted it in the Student Forum for that lesson. No?

What do you mean by a "list of objects that conflict with VBA?" This really isn't a conflict. It's a feature that simply didn't exist in 2000.
Show Just This Thread        Post Reply
Comment from BRYAN binkerd @ 9/30/2009
customerlistbox 1 10:14
I've been writing vba on access 2003 and loading it on to computers running access 2000.  Found out the hard way that the box.additem didn't work.  Is there a majic Fix?
I did reprogram it to 2000 but it might help for others. I got the rowsource to work with multiple columns by typing
Box.RowSource = Box.RowSource & ";" & SomeValue & ";" & SomeValue & ";"
By the Way do you have a list of objects that conflict with vba such as the listbox?
Show Just This Thread        Post Reply
Comment from Ray McCain @ 8/13/2009
My limited use of delimeters causes the visability of long code statements to be a problem when the end of the statement does not stay on the screen long enough to hit the pause button.  In this instance it seems that you are going to fast.
Show Just This Thread        Post Reply
Comment from Richard Rost @ 3/6/2009
Harry, yes, IDs should always be LONG INTEGERS. I specify this all the way back in Access 101. Where did I make one an Integer?
Show Just This Thread        Post Reply
Comment from Richard Rost @ 3/6/2009
Harry, functionally both SetFocus and GoToControl work about the same. 99% of the time there is no difference. However, I have run into situations where ONE of the two works and the other doesn't. I don't really know why. I've had problems, especially, when trying to go to controls on a SUBFORM or tab control. Sometimes GoToControl works and SetFocus doesn't... so keep both in your toolbox. One (SetFocus) is a method of a control, the other is a command.
Show Just This Thread        Post Reply
Comment from Richard Rost @ 3/6/2009
Harry, both are fine. If you like using Me! you can. Personally, I don't use it because I started writing code way back in Access 2.0 which didn't support all this newfangled stuff. It's certainly an acceptable practice though. As far as debugging your code before running it, again, there's nothing wrong with it. I usually don't bother debugging until I have a problem.
Show Just This Thread        Post Reply
Comment from  Harry Mullin @ 3/5/2009
You specify ID's as Integers. Shouldn't they be Long to avoid breaking at 32k names?
Show Just This Thread        Post Reply
Comment from  Harry @ 3/5/2009
is there any difference in using:

me.firstname.setfocus

vs

DoCmd.GoToControl "FirstName"?
Show Just This Thread        Post Reply
Comment from  Harry @ 3/5/2009
What is your view on using the prefix (if that is the right word) me. in front of field names?

I like to do it because it triggers intellisense (sp?)

Also, I try to run the Debug/compile routine after writing code but before trying it out. Do you think that is a good practice/
Show Just This Thread        Post Reply
Comment from Richard @ 2/26/2009
ATTENTION STUDENTS: if you are using Access 2000 or earlier, you will NOT have a Listbox AddItem or RemoveItem command available. See this page for details.
Show Just This Thread        Post Reply

Add Your Comments or Subscribe
 
If you just want to subscribe to get email updates when the News 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 8+8:
  
  Notify me when the News 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:

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
2/10/2018Microsoft Access Developer 4
2/1/2018Access Tip: Salary History
7/19/2017Access Developer 2 and 3 Released
7/19/2017Microsoft Access Developer 3
7/19/2017Microsoft Access Developer 2
 

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
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