Course List - Access 321

Student Interaction: Microsoft Access 321

Richard on 1/1/2008: Working with unbound ListBox controls, editing recordset data, multi-select listbox, adding & deleting recordset data.

Richard on 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.

�Harry on 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/

�Harry on 3/5/2009: is there any difference in using: me.firstname.setfocus vs DoCmd.GoToControl "FirstName"?

�Harry Mullin on 3/5/2009: You specify ID's as Integers. Shouldn't they be Long to avoid breaking at 32k names?

Richard Rost on 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.

Richard Rost on 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.

Richard Rost on 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?

Ray�McCain on 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.

BRYAN�binkerd on 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?

Richard Rost on 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.

Bryan Binkerd on 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

Richard Rost on 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.

BRYAN�binkerd on 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

Richard Rost on 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. :)

�Twila on 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!

Richard Rost on 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?

Naomi�Roll on 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.

Add Your Questions or Comments