Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| 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 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 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
 
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
 
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

 
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
 
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





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





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