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
| 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 Search this Thread Display Modes
  #1 (permalink)  
Old May 12th, 2008, 10:26 AM
Authorized User
Points: 336, Level: 6
Points: 336, Level: 6 Points: 336, Level: 6 Points: 336, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2006
Location: , , .
Posts: 73
Thanks: 6
Thanked 0 Times in 0 Posts
Default SOLVED - VBA Help For Row Source and Record Source

Hello All:

I am trying to write some code to do try append a value in Control Source or Row Source depending on what it finds. So basically what I have is:

Private Sub cboPhysician_Dirty(Cancel As Integer)
    Dim strRowSource As String
    strRowSource = "SELECT Physicians.Specialty " _
                    & "FROM Physicians " _
                    & "WHERE Physicians.Physician= " + "'" +
                       Forms!Order_Form.cboPhysician + "'"""

     Forms!Order_Form!cboPhysicianSpecialty.RowSource = strRowSource

When the focus is in the Physican combo box and a selection is made, I want it to do the query. If it finds a physician, put this in the
Control Source for the cboPhysicianSpecialty:

=DLookUp("Specialty","Physicians","Physician = '" & Form!cboPhysician & "'")

This will autofill the Specialty combox with there specialty. Unfortunately if it doesn't find the physician, then that field is not editable if the Control Source property is filled in.

So I would like to figure out how to write it so if it doesn't find a physician, add the above 'Select' statement to the Row Source, that way I can edit the Specialty combo box.

I hope this makes sense.

Thanks,
Tony



End Sub
Reply With Quote
  #2 (permalink)  
Old May 12th, 2008, 04:06 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

The rowsource would be

strRowSource = "SELECT [Specialty] FROM Physicians WHERE [Physician] = '" & Me.cboPhysician & "'"

Me.cboPhysicianSpecialty.RowSource = strRowSource

If this code is in the form Order_Form, you can use the Me shortcut. The plus sign + is for number addition. The ampersand & is for string concatenation.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Reply With Quote
  #3 (permalink)  
Old May 12th, 2008, 04:22 PM
Authorized User
Points: 336, Level: 6
Points: 336, Level: 6 Points: 336, Level: 6 Points: 336, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2006
Location: , , .
Posts: 73
Thanks: 6
Thanked 0 Times in 0 Posts
Default

Thank you SerranoG for the reply. I guess I didn't clarify this right.My apologies.

What i am trying to do is if the a physician is selected from the dropdown then add the DLookup string to the Control Source property for the Specialty Combo box.

If the physician is not listed in the combo box, then they will have to input the physician. Once the physician is entered, I want the Control Source proprty cleared and the SQL string added to the Specialty Row Source property instead.

I have everything working without VBA but the issue is that leaving the DLookup string in the control source makes the Specialty field uneditable.

Thanks,
Tony

Reply With Quote
  #4 (permalink)  
Old May 13th, 2008, 07:01 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

I would put the code in the cboPhysician's AfterUpdate event, not in the Dirty event. You cannot place a control source on a textbox at design view because it will prevent you from assigning it a value on the fly. If the textbox is bound to the data table, you assign a value to it in VBA at runtime.

Code:
Dim strRowSource as String

If Len(Nz(Me.cboPhysician, "")) = 0 Then
    'No Physician Chosen.  Make the rowsource ALL specialties.
    strRowSource = "SELECT DISTINCT [Specialty] FROM Physicians ORDER BY [Specialty]"
Else
    'Physician chosen.  Find his/her specialty.
    strRowSource = "SELECT DISTINCT [Specialty] FROM Physicians WHERE [Physician] = '" & Me.cboPhysician & "' ORDER BY [Specialty]"
End If

Me.cboPhysicianSpecialty.RowSource = strRowSource

If Me.cboPhysicianSpecialty.ListCount = 1 Then
    'The physician has only one specialty.  Enter it automatically.
    Me.cboPhysicianSpecialty = Me.cboPhysicianSpecialty.Column(0,0)
End If
Note the use of DISTINCT and ORDER BY in the rowsource assignment.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
Reply With Quote
  #5 (permalink)  
Old May 13th, 2008, 11:58 AM
Authorized User
Points: 336, Level: 6
Points: 336, Level: 6 Points: 336, Level: 6 Points: 336, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2006
Location: , , .
Posts: 73
Thanks: 6
Thanked 0 Times in 0 Posts
Default

SerranoG:

Thank you so much for the advice and the code. It worked perfectly.


Tony

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
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
record.source WHERE date comparison Richard Lally Access 3 January 5th, 2006 08:03 PM
Update Source Record After Insert Duncan Allen SQL Server DTS 1 October 25th, 2004 10:44 AM
Record source ... does not exist tunsted Access 2 April 1st, 2004 10:03 AM
Report's record source too long Mitch Access 7 January 26th, 2004 04:17 PM
record source damnnono_86 Access 6 November 10th, 2003 10:32 PM



All times are GMT -4. The time now is 09:52 PM.


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