Ask MetaFilter questions tagged with access and vbahttp://ask.metafilter.com/tags/access+vba
Questions tagged with 'access' and 'vba' at Ask MetaFilter.Fri, 10 May 2013 10:15:42 -0800Fri, 10 May 2013 10:15:42 -0800en-ushttp://blogs.law.harvard.edu/tech/rss60Assigning a keyboard shortcut to a macro in Access 2007http://ask.metafilter.com/240743/Assigning%2Da%2Dkeyboard%2Dshortcut%2Dto%2Da%2Dmacro%2Din%2DAccess%2D2007
Is there a simple way to assign a keyboard shortcut to a macro in Access 2007? I understand that macros behave differently in Access than they do in Excel (which I'm more familiar with). I have written a sub called "ImportAll" in Module1. I'd like it to run when I push Ctrl+I. How would I go about achieving this?tag:ask.metafilter.com,2013:site.240743Fri, 10 May 2013 10:15:42 -0800ProginoskesLearning to use VBAhttp://ask.metafilter.com/238667/Learning%2Dto%2Duse%2DVBA
I have read that John Walkenbach's books are the de-facto standard for learning to use VBA in Microsoft Excel. I'd like to know what books are recommended to learn to use vba in other Office products - primarily Access, but also Word and Powerpoint. I'm more interested in books than in websites.tag:ask.metafilter.com,2013:site.238667Sat, 06 Apr 2013 21:31:13 -0800ProginoskesBest practice in implementing a two-part unique identifier?http://ask.metafilter.com/185400/Best%2Dpractice%2Din%2Dimplementing%2Da%2Dtwopart%2Dunique%2Didentifier
Best way to implement a two-part unique identifier in Access? I've been tasked with building a solution that involves items with a two-part identifier that must be unique. Imagine the identifiers as letters and numbers. Each part can have multiple entries with the same values, but when put together, they must be unique.<br>
<br>
AA01<br>
AA02<br>
BB01<br>
BB02<br>
etc.<br>
<br>
I could make [LetterPart] and [NumberPart] together the primary key for the table. But I'd love to have a primary key that is just one entry, and still have the database reject any attempt to insert a non-unique [LetterPart] and [NumberPart] pair.<br>
<br>
I'd also love for a way to have the database automatically increase the number part in a way that is aware of the letter part, so I don't have to do it in code. So I could tell it to Insert new AA, and it looks at the AA entries, sees the last one was 02, and creates AA03. Then when I tell it to insert CC, for example, it creats CC01.<br>
<br>
Are these things possible? What are the known, sane approaches to this problem?tag:ask.metafilter.com,2011:site.185400Mon, 09 May 2011 09:52:38 -0800jsturgillVBA Moron Wants Magic Bullethttp://ask.metafilter.com/146477/VBA%2DMoron%2DWants%2DMagic%2DBullet
Access 2007: Need code to grab the value _displayed_ in a combo box (as opposed to its actual value). Big, detailed explanation inside. I have a form which involves a couple of combo boxes, and I would like to base some conditional coding based on the displayed contents of the combo boxes. However, the bound field of the combo box is not the control source of the combo box. How do I get my VBA code to recognize the displayed text, rather than going to the underlying number?<br>
<br>
Specifics, if needed:<br>
The table this form feeds back to has the following fields:<br>
<br>
LMAutoID AutoNumber field<br>
LMDate Date field<br>
LMEmployee Number field (lookup box feeding from an employee table.) <br>
LMLocation Number field (lookup box feeding from a cost allocation table)<br>
LMEmergency Yes/No field<br>
LMCount Number field<br>
<br>
Basically, the LMEmployee has to submit daily LMCounts for any of 4 possible locations, and subdivide them out by whether it was (1) an emergency and (2) a water or a sewer call. The water/sewer differentiation has not been given a field because the cost allocation table lists water/sewer for each location option, making the distinction implicit in the LMLocation selection. Note that both lookup fields are rooted in their respective table's autoID field, for all they display a text value.<br>
<br>
Because of the nature of the data collected (up to 16 LMCount entries for each date), the typing on trial was extremely repetitive. So far I've dealt with that by <br>
<br>
(1) embedding the LM form in an employee form, so that the employee can pull up their own name and have that value forward to the LM form, so they don't have to type their own name for every entry of every day of the month. <br>
<br>
(2) adding an unbound field at the end of the form with an "on exit" event procedure. Depending on whether the unbound field has been changed, the default value for the date field will either be the same date as the record just finished, or will advance by one day.<br>
<br>
(3) setting the default value of LMEmergency to "no," since about 80% of the time, entries will be non-emergency only.<br>
<br>
The final cheat that would make this data entry relatively painless would be an evaluation based on whether the entry was "Water" or "Sewer." Basically, the things almost always come in pairs. (...almost...) So if you did 5 line marking tickets for water at a location on December 7, you'd <em>almost</em> certainly be doing 5 sewer markings at that location on December 7. What I'd like to do is to set up something where every time a water record was created, the new record would basically default-in all the values from the exiting record, except for the MLLocation specification. The user could then specify the location (and again, the water-sewer distinction is implicit in the location selection), press [tab], and the entry, voila, would be done. Or [Esc] if there were no corresponding Sewer markings.<br>
<br>
I'm not good at VBA. I only venture there when I've run out of SQL ideas. I tried making a (non-visible) combo-box which was sourced on MLLocation but displayed the cost allocation's water-sewer designation, but the problem is still the same: <br>
<br>
me.MLLocation.value = <strong><em>the wrong field</em></strong> <br>
<br>
The number that statement collects is the bound column's value, which happens to be the cost allocation's autoID number. This cannot (in my opinion --which may be uninformed) be meaningfully translated into the value I need, which is "Water" or "Sewer". Is there a way to simply copy the displayed string from the combo box and use that value as the basis for my if/then statement?<br>
<br>
There is a whispering voice at the back of my brain that says, "rethink the tables, man!" But I don't think that parsing this table into multiple interdependent tables will result in easier data entry. I'm really hoping for a code fix, since there's so much cool stuff that can be accomplished that way, if you just know what to say! <br>
<br>
Bonus points if you can figure a way to get the MLLocation field to fill in itself: The location names used in the LM table always start with "w" if it's water, and "s" if it's sewer. So a typical allocation record would be:<br>
<br>
AutoID LocationName System LocationLookupName<br>
1 Mercer Island Water wMI<br>
3 Hells Gate Sewer sHG<br>
5 Mercer Island Sewer sMI<br>
6 Hells Gate Water wHG<br>
<br>
This transfers to the LM table as:<br>
<br>
Date Employee Location Emerg? Count<br>
12/5/09 Dan The Man wMI No 4<br>
12/5/09 Dan The Man sMI No 4<br>
<br>
Thanks all!tag:ask.metafilter.com,2010:site.146477Sat, 20 Feb 2010 18:22:51 -0800YsMacgyverFilter: link sharing using MS Officehttp://ask.metafilter.com/138600/MacgyverFilter%2Dlink%2Dsharing%2Dusing%2DMS%2DOffice
Macgyver IT: I want my team to use something like del.icio.us to share links. But since this is ask.metafilter, I can't use the obvious right answer. I need to make something like it myself, using <strike>bubblegum and string</strike> the MS Office 2000 suite and my knowledge of VB. What would be my best approach? This is clearly insane, but I want to try <small>(please don't ask why I can't use something reasonable -- I just can't)</small>. I don't need the full del.icio.us, just links and tags. I'm also okay with having only one person at a time able to add a link to the dB, and everyone sharing a big ball of links.<br>
<br>
I've thought about using an Excel file, but I don't know if there's a reasonable way to do that, or what that way would be. Could that work, or should I bite the bullet and go for Access? Is there a feature hidden somewhere (in Outlook?) that I'm overlooking?tag:ask.metafilter.com,2009:site.138600Thu, 19 Nov 2009 19:23:17 -0800Monday, stony MondayProblems creating a popup keypad in access.http://ask.metafilter.com/49836/Problems%2Dcreating%2Da%2Dpopup%2Dkeypad%2Din%2Daccess
I am developing an access app for a UMPC, and because of the lack of a keyboard, I want to make a keypad number entry popup.
I am now struggling with the getting it to work. My current method is <br>
Private Sub Button1_Click()<br>
DoCmd.GoToControl "PackQuantity"<br>
SendKeys "1", no<br>
End Sub<br>
<br>
and then repeat for each button. Currently I can only get it to put one number in the text box, because each press of the button erases the existing data. I have tried adding a line like:<br>
SendKeys "{End}", no<br>
Or instead of end, Right, but it does about the same thing.<br>
I know this probably isn't the best way to do it, but its all I've come up with so far. Any suggestions would be welcome at this point.tag:ask.metafilter.com,2006:site.49836Tue, 31 Oct 2006 08:29:06 -0800JonnyRotten