Access VBADiscuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DoCmd.OpenForm WhereCondition, Syntax Error or ???
I'm a beginner. Please excuse my ignorance.
I can’t seem to get Do.Cmd OpenForm, WhereCondition to recognize and use the contents of a string variable: varW. Works OK when using a literal.
Q1-I’m wondering if WhereCondition can evaluate and handle the contents of a string variable?
I’ve tried the following examples with various syntax changes without success. Contents of the current varW will display correctly using a MsgBox before and after the Do.Cmd. Here is what I know:
'DoCmd.OpenForm "FrmCustomerDetail", , , "AssignedEmployee = 'Jan Jones'" 'This returns the correct records and opens the form correctly as long as contents of varW (Jan Jones in this literal example) are hard coded as a literal.
'But hard coding for all possible literals would be dynamic (ever changing) and therefore impractical. If varW is substituted for the literal "Jan Jones", I can NOT get WhereCondition to evaluate contents of varW and return the Jan Jones records! Please see examples below:
‘DoCmd.OpenForm "FrmCustomerDetail", , , "AssignedEmployee = 'varW'"
'Returns NO records!
This makes sense because there is no “varW” in the field AssignedEmployee in TblCustomers (only real employee names)
'DoCmd.OpenForm "FrmCustomerDetail", whereCondition:="'varW'"
'This ignores value within varW. Returns ALL records!
‘DoCmd.OpenForm "FrmCustomerDetail", WhereCondition:="AssignedEmployee = 'varW'"
'Returns NO records!
This again makes sense because there is no “varW” in the field AssignedEmployee in TblCustomers. Only real employee names.
Q1-Is this simply a syntax problem where I haven’t stated my request properly . . . or
Q2-is this a limitation with the WhereCondition augument?
Thank you very much for any assistance, guidance and coding example you can provide !!!
Just a short note to express my appreciation for your assistance and expertise. Your syntax examples were perfect and worked. After spending hours looking for the answer in no less than 5,500 pages of text-book code, I'm very appreciative for your assistance.
If you're ever in the Denver, Colorado area, lunch is on me! Thanks again and best regards,
Jerry Beauchamp, CEO, HomeShow Realty, INc. Email: firstname.lastname@example.org PH: 303-770-0007
How To Create dynamic event procedure for btn from table using Access VBA
I have a database table containing menu option information similar to a switchboard table, but it also has the actual expression in it as well - such as a single record that has Customer Maintenance as the Desc field, and
that would be the expression that the button would run contained in a field called EventCode in the same table.
The record has the following fields and values as shown below:
ItemNumber: 1 (this is the menu option number - first one)
ItemText: Customer Entry (displayed on the button in the caption)
I am trying to create a dynamic menu in VBA Access 2007 that will read this information from a table and populate the caption for the button, and also add the event procedure to the OnClick event property for that button - essentially building a menu from a table. When my form loads, I want to get the records from the table that correspond to menu item 3, populate all of the captions for the buttons on the screen (which I have working already - see below) and also update the OnClick Event Procedure for it. I have no idea if the event procedure can even be done. The code below shows reading from the table, and code that will load the caption (all those buttons need to be done in a loop so please ignore the icky code that I have at the moment - I want to do this more slowly to make sure that the code is easy to read before I streamline it.)
Can anyone tell me if it is possible to dynamically build an event procedure and how it would be coded? I want to take the text from the field in the table called "EventCode" and format it correctly so that it will update the OnClick for each button, as if I had gone to the OnClick property, clicked the elipsis button, and typed in the code to run the code required for the OnClick event.
The program as it is now is shown below. I would dearly appreciate any help someone might be able to offer.
Thanks a ton!
Ann Z in CT
Private Sub Form_Load()
Dim conn1 As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String
' Format the strSQL string select menu items equal 3 for customer menu
strSQL = "SELECT * FROM MenuItems WHERE MenuID=3 Order By ItemNumber"
' Start the connection and process the SQL query on the recordset.
Set conn1 = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open strSQL, conn1
'this changes the caption on the buttons
'would like to have a line of code that updates the event procedure
'something like btn.OnClick.Event = rs![EventCode] which does not work.
btn1.Caption = rs![ItemText]
btn2.Caption = rs![ItemText]
btn3.Caption = rs![ItemText]
btn4.Caption = rs![ItemText]
btn5.Caption = rs![ItemText]
I have searched for many hours to see if there was a way to build the code for buttons from code loaded in a table, but just cannot find it. Thought the experts might be able to say no way, cant do this or show me how.
P.S. I am a student again, having FAR too much fun back in school after many years - now knowing what I do want to study which is web development. The assignment is to read the menu captions from a table and load them which is working, but I want to take it further and also load the OnClick events to see if it is possible. If we can get at all of the other properties of an object, why not this one?