Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 13th, 2003, 05:46 PM
Authorized User
 
Join Date: Nov 2003
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default Option buttons, queries and combo boxes...

I am having trouble getting this to work:

- There is a group of 5 option buttons.
- When one of the option buttons is "clicked", the results of a query display in a combo box...

I have created the queries and they work fine. I have created the option buttons and the combo box. I am at an utter loss on how to go about displaying the query results in the combo box.
 
Old November 13th, 2003, 06:37 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

When you say "displaying the query results in the combo box" are you refering to a filtered column from the query? A different result depending on which option is selected?

################################
Private Sub frame1_BeforeUpdate(Cancel As Integer)

    If Me.frame1.Value = 1 Then
        Me.cboIssueTypeID.RowSource = "SELECT Empid, EmpName " & _
                                      "FROM employees;"


    ElseIf Me.frame1.Value = 2 Then

        Me.combo1.RowSource = "SELECT Empid, EmpName " & _
                                      "FROM employees " & _
                                      "WHERE (((Empid)=1 Or (Empid)=2)) " & _
                                      "ORDER BY EmpName;"
    End If
End Sub
#################################

Is this what you are looking for? There is no need for queries, just use a string.

Let me know if this helps.



Sal
 
Old November 13th, 2003, 06:56 PM
Authorized User
 
Join Date: Nov 2003
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well, this is my code...It isn't working:

Private Sub Frm_InvUpd_Category_BeforeUpdate(Cancel As Integer)

If Me.Frm_InvUpd_Category.Value = 1 Then
Me.Cmb_InvUpd_Prod.RowSource = "SELECT Product.Product_Name" & _
                               "FROM Product" & _
                               "WHERE (((Product.Product_Category) = 'Long Range'))" & _
                               "ORDER BY Product.Product_Name;"

ElseIf Me.Frm_InvUpd_Category.Value = 2 Then
Me.Cmb_InvUpd_Prod.RowSource = "SELECT Product.Product_Name" & _
                               "FROM Product" & _
                               "WHERE (((Product.Product_Category) = 'Mid Range'))" & _
                               "ORDER BY Product.Product_Name;"

ElseIf Me.Frm_InvUpd_Category.Value = 3 Then
Me.Cmb_InvUpd_Prod.RowSource = "SELECT Product.Product_Name" & _
                               "FROM Product" & _
                               "WHERE (((Product.Product_Category) = 'Putt & Approach'))" & _
                               "ORDER BY Product.Product_Name;"

ElseIf Me.Frm_InvUpd_Category.Value = 4 Then
Me.Cmb_InvUpd_Prod.RowSource = "SELECT Product.Product_Name" & _
                               "FROM Product" & _
                               "WHERE (((Product.Product_Category) = 'Specialty'))" & _
                               "ORDER BY Product.Product_Name;"

ElseIf Me.Frm_InvUpd_Category.Value = 5 Then
Me.Cmb_InvUpd_Prod.RowSource = "SELECT Product.Product_Name" & _
                               "FROM Product" & _
                               "WHERE (((Product.Product_Category) = 'Recreational'))" & _
                               "ORDER BY Product.Product_Name;"
End If

End Sub
 
Old November 13th, 2003, 07:33 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Put the code on the frame Before Update event. Also use a case statement

select case frame1.value
    case 1
        yada yada yada
    case 2
        bla bla bla
    case 3
        gafddsfd
end select



Sal
 
Old November 13th, 2003, 08:19 PM
Authorized User
 
Join Date: Nov 2003
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

 
Quote:
quote:Put the code on the frame Before Update event.


I thought it was in the before update event...

Private Sub Frm_InvUpd_Category_BeforeUpdate(Cancel As Integer)


would the select statement look like this?

Select case Frm_InvUpd_Category.Value
  Case 1
    Me.Frm_InvUpd_Category.Value = 1 Then
Me.Cmb_InvUpd_Prod.RowSource = "SELECT Product.Product_Name" & _
                               "FROM Product" & _
                               "WHERE (((Product.Product_Category) = 'Long Range'))" & _
                               "ORDER BY Product.Product_Name;"

  Case 2
    Me.Frm_InvUpd_Category.Value = 2 Then
Me.Cmb_InvUpd_Prod.RowSource = "SELECT Product.Product_Name" & _
                               "FROM Product" & _
                               "WHERE (((Product.Product_Category) = 'Mid Range'))" & _
                               "ORDER BY Product.Product_Name;"

  Case 3
    bla bla bla

  Case 4
    etc etc etc

End Select
 
Old November 13th, 2003, 09:03 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Put the code on the before update event of the Frame control that holds the option buttons, not on the form's event.

Do not use

Select case Frm_InvUpd_Category.Value

use
Select case frame1.value

Substitute Frame1 woth the name of your Frame control that holds the option buttons.



Sal
 
Old November 13th, 2003, 09:08 PM
Authorized User
 
Join Date: Nov 2003
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

that is the name of my frame... Frm_InvUpd_Category
 
Old November 14th, 2003, 05:41 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 120
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Also, if the code posted below is a faithful copy, you seem to be suffering from a shortage of spaces
Code:
"SELECT Product.Product_Name" & _
"FROM Product" & _
"WHERE (((Product.Product_Category) = 'Long Range'))" & _
"ORDER BY Product.Product_Name;"
resolves to:
SELECT Product.Product_NameFROM ProductWHERE (((Product.Product_Category) = 'Long Range'))ORDER BY Product.Product_Name;

If your company is having difficulty in procuring whitespace, post another message and we'll send some through. Here's a sample so you can check the quality ! :):):);)


Brian Skelton
Braxis Computer Services Ltd.
 
Old November 14th, 2003, 08:13 AM
Authorized User
 
Join Date: Nov 2003
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

 
Quote:
quote:Also, if the code posted below is a faithful copy, you seem to be suffering from a shortage of spaces


erm...I'm afraid I don't know what you mean...
 
Old November 14th, 2003, 08:22 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 120
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This code:
Code:
Me.Cmb_InvUpd_Prod.RowSource = "SELECT Product.Product_Name" & _
                               "FROM Product" & _
                               "WHERE (((Product.Product_Category) = 'Long Range'))" & _
                               "ORDER BY Product.Product_Name;"
                               will result in the rowsource of Cmb_InvUpd_Prod being set to:
Code:
SELECT Product.Product_NameFROM ProductWHERE (((Product.Product_Category) = 'Long Range'))ORDER BY Product.Product_Name;
You need spaces before FROM, WHERE and ORDER BY.


Brian Skelton
Braxis Computer Services Ltd.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Create Check Boxes/Option buttons/Option Group hewstone999 Access VBA 1 March 14th, 2008 07:25 AM
Error is using option buttons return value Subuana Beginning VB 6 2 May 1st, 2006 03:39 PM
updating text fields with option buttons redtechcoms Access VBA 7 February 2nd, 2006 03:14 PM
Using Option buttons NovieProgrammer Access VBA 1 April 2nd, 2005 09:16 PM
Is one (1) a 'length' for option buttons? John K. King Javascript How-To 2 November 7th, 2004 06:04 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.