Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss 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 .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old September 27th, 2004, 03:34 PM
Registered User
 
Join Date: Sep 2004
Location: Highlands Ranch, CO, USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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 !!!

Reply With Quote
  #2 (permalink)  
Old September 27th, 2004, 03:41 PM
Friend of Wrox
Points: 3,947, Level: 26
Points: 3,947, Level: 26 Points: 3,947, Level: 26 Points: 3,947, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,150
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

When using variables, you cannot use them inside the quotation marks. Therefore, your DoCmd call would look something like this

DoCmd.OpenForm "FrmCustomerDetail", , , "[AssignedEmployee] = '" & varW & "'"

If varW is text, note the two single quotes on either side of the varW. If it were a date, it would look like this

DoCmd.OpenForm "FrmCustomerDetail", , , "[Your Date Field] = #" & varW & "#"

And if it were a number, it would look like this

DoCmd.OpenForm "FrmCustomerDetail", , , "[Your Number Field ] = " & varW


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Reply With Quote
  #3 (permalink)  
Old September 27th, 2004, 04:04 PM
Registered User
 
Join Date: Sep 2004
Location: Highlands Ranch, CO, USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Greg ~
   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: homeshow@realtor.com PH: 303-770-0007

Reply With Quote
  #4 (permalink)  
Old March 20th, 2008, 09:37 AM
Registered User
 
Join Date: Mar 2008
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Greg,
Thanks for the info. You're much better than access help, VBA for Dummies, or Microsoft.com. You gave me the answer I've been looking for. Mike


Quote:
quote:Originally posted by SerranoG
 When using variables, you cannot use them inside the quotation marks.  Therefore, your DoCmd call would look something like this

DoCmd.OpenForm "FrmCustomerDetail", , , "[AssignedEmployee] = '" & varW & "'"

If varW is text, note the two single quotes on either side of the varW.  If it were a date, it would look like this

DoCmd.OpenForm "FrmCustomerDetail", , , "[Your Date Field] = #" & varW & "#"

And if it were a number, it would look like this

DoCmd.OpenForm "FrmCustomerDetail", , , "[Your Number Field ] = " & varW


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Reply With Quote
  #5 (permalink)  
Old March 24th, 2008, 09:05 AM
Friend of Wrox
Points: 3,947, Level: 26
Points: 3,947, Level: 26 Points: 3,947, Level: 26 Points: 3,947, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,150
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Jerry & Mike,

I have just returned from a ten-day vacation in Puerto Rico. Thanks for your notes. It's a nice addition to my 70+ e-mails.

You're both quite welcome. Glad I can help.




Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Reply With Quote
  #6 (permalink)  
Old April 15th, 2010, 03:14 PM
Registered User
 
Join Date: Apr 2010
Location: CT
Posts: 10
Thanks: 1
Thanked 1 Time in 1 Post
Question How To Create dynamic event procedure for btn from table using Access VBA

Hi Everyone!
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
Code:
DoCmd.OpenForm "frmAddCustomer"
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:
MenuID: 3
ItemNumber: 1 (this is the menu option number - first one)
ItemText: Customer Entry (displayed on the button in the caption)
EventCode:
Code:
DoCmd.OpenForm "frmAddCustomer"
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

Code:
 
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
    rs.MoveFirst  
'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]
    rs.MoveNext
    btn2.Caption = rs![ItemText]
    rs.MoveNext
    btn3.Caption = rs![ItemText]
    rs.MoveNext
    btn4.Caption = rs![ItemText]
    rs.MoveNext
    btn5.Caption = rs![ItemText]
End Sub
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?
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
DoCmd.OpenForm BBWEST Access 1 August 21st, 2008 10:02 AM
Docmd.Openform problem mkobulni Access VBA 7 August 29th, 2007 09:05 AM
DoCmd.OpenForm error 2501 requeth Access 6 February 7th, 2007 03:25 PM
DoCmd.OpenForm turp Access VBA 2 December 15th, 2006 07:33 AM
AccessVB: DoCmd.Openform awillis40 Access VBA 2 December 10th, 2006 05:08 PM



All times are GMT -4. The time now is 03:00 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.