Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Passing a comboBox to function byRef not working - getting it's Value instead

Posted on 2005-05-07
17
Medium Priority
?
624 Views
Last Modified: 2008-02-01
I want to pass a comboBox to a procedure, as a combobox. Here is the calling procedue:

Private Sub cboEntity1_AfterUpdate()
    Dim targetCbo As ComboBox, currentCbo As Object
    Set targetCbo = Me.cboEntity2
    Set currentCbo = Me.cboEntity1
    If Not SetEntityComboBox(targetCbo, currentCbo.Column(0), Me.lblEntity2, Me.lblStatus) Then
        UpdateLists (currentCbo)<------------------------------------it's taking it's value, not it's rference to the combobox!!!
    End If
End Sub

Here is the procedure:
Private Sub UpdateLists(ByRef aComboBox As ComboBox)

On Error GoTo UpdateLists_Error

           ' update unassigned people
            Call SetEmployeeRelatedListBox(lstUnassignedPeople, m_relatedFieldName, Null)
           
            If wasChangeMade Then
                ' update assigned people
                m_relatedFieldID = aComboBox.Column(0)
                Call SetEmployeeRelatedListBox(lstAssignedPeople, m_relatedFieldName, m_relatedFieldID)
               
                ' set related fields
                Me!txtSpecialPerson = aComboBox.Column(1)
                Me!lblStatus.Caption = ""
               
                ' establish the count so we can know if changes were made the next time
                m_assignedCount = lstUnassignedPeople.ListCount
                GuiState
            End If
End Sub


As you can see, I need the comboBox to be acting as a comboBox in here, but I'm getting it's currently selected value. What's interest is that in the procedure call imdiately above, I'm also passing it as a combobox, and in that procedure, it recognizese it as such. I'm confused.

I guess I could pass the name as a string and refer to it as part of the form's controls collection, but I don't want to do that if I don't have to.

Thanks! Eric
0
Comment
Question by:efinger
  • 8
  • 4
  • 4
  • +1
17 Comments
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 13950905
Why not Dim currentCbo As Combobox too?

    Dim targetCbo As ComboBox, currentCbo As ComboBox

/gustav
0
 

Author Comment

by:efinger
ID: 13950913
I had originally. Didn't realized I changed it to object as a desperate attempt. Neither worked.

Thanks, Eric
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 13950957
How can you see "it's taking it's value" and where does the code fail?

/gustav
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:efinger
ID: 13950962
where i marked it:
        UpdateLists (currentCbo)<------------------------------------it's taking it's value, not it's rference

The value in the debugger at that point is the value of it's bound column, so there is a data mismatch on the call requesting a Combobox parm

Thanks, Eric
0
 
LVL 39

Expert Comment

by:thenelson
ID: 13951000
I am having a bit of a problem following your code.  Are you trying to change the dropdown list (rowsource property) of one combobox by the input of another combobox?  
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 13951007
The debugger is correct as the default property of a combobox is its Value.

Dim it as ComboBox as your function requests - or change the function to accept Object.

/gustav
0
 

Author Comment

by:efinger
ID: 13951328
yes theNelson, that is exactly the exercise

I have tried it dimmed as ComboBox cactus...I started it that way

Thanks
0
 

Author Comment

by:efinger
ID: 13951342
in fact, holding the mouse ove the variable on this statement:

    Dim targetCbo As ComboBox, currentCbo As ComboBox

shows its value ("146" as it happens right now)
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 13951357
But it should be so, the error must be somewhere else ...

/gustav
0
 

Author Comment

by:efinger
ID: 13951383
Here is the code of the function that updates the RowSource. Maybe I should pass the ComboBox byVal??


'---------------------------------------------------------------------------------------
' Procedure : SetEntityComboBox
' Purpose   : Standard way to set any combo up that deals with entities, after that entity combo
'             has been updated. Let's us set the heirarchy of entities.
' Inputs    : aTargetCbo - the combobox to setup; this will be the child entity of the calling
'             after_update procedure
'           : aStatusLabel - status
'           : aTargetCboLabel - a label associated with the target cbo
'           : aParentID - the parent whose children we will use to populate aTargetCbo, or
'           : anOrgTypeID - optional parm makes the source data be all entities of this type; this
' Outputs   : True if the target has children, false if not
' Precon(s) : None
' DateTime  : 3/25/2005
' Author    : EBF
'---------------------------------------------------------------------------------------
'
Public Function SetEntityComboBox( _
        aTargetCbo As ComboBox, _
        aParentID As Variant, _
        aTargetCboLabel As Label, _
        aStatusLabel As Label, _
        Optional anOrgTypeID As Variant _
    ) As Boolean
   
On Error GoTo SetEntityComboBox_Error

    If IsMissing(anOrgTypeID) Then
        aTargetCbo.RowSource = sqlAllChildrenOfParent(QualifyIdForCriteria(aParentID))
    Else
        aTargetCbo.RowSource = sqlOrgType(QualifyIdForCriteria(anOrgTypeID))
    End If
   
    aTargetCbo.Requery
   
    Dim cboCount As Long
    cboCount = aTargetCbo.ListCount
    Dim bResult As Boolean
    bResult = cboCount > 0
   
    If bResult Then
        Dim cboDescription As String
        cboDescription = DLookup("[Description]", "OrgType", "[OrgTypeID] = " & aTargetCbo.Column(2, 1))
        aTargetCboLabel.Caption = cboDescription
        aTargetCbo.Visible = True
        aStatusLabel.Caption = cboCount & cboDescription & "(s) available for selection..."
    Else
        aTargetCboLabel.Caption = ""
        aTargetCbo.Visible = False
        aStatusLabel.Caption = ""
    End If
   
    SetEntityComboBox = bResult

On Error GoTo 0
   Exit Function

SetEntityComboBox_Error:

    DisplayError "Error " & Err.Number & " (" & Err.Description & _
        ") in procedure SetEntityComboBox of Module modEntity"
End Function
0
 
LVL 39

Expert Comment

by:thenelson
ID: 13951499
Are you trying to change the dropdown list (rowsource property) of one combobox by the input of another combobox?  
 yes theNelson, that is exactly the exercise

Then I think you are making it way too complicated.

Assuming new Column(0) = Null, new Column(1) = NameToAdd

If the row source type is a value list then updating would be:
aComboBox ,rowsource = aComboBox ,rowsource & "," & Null & "," & [NameToAdd]

If the row source type is a field list to a table then updating would be an append query to the table.

If the row source type is a field list to a query or Table/Query then updating would be changing the query.

If you paste what is in the rowsource proptery for the combobox you want to update and how you want to update it, I can show you how to do it.
0
 

Author Comment

by:efinger
ID: 13951610
I had originally used a Value list as you suggest. My problem was that the list was full of employee names, formatted "lastName, firstName", and the add item method was interpreting the comma in the name as a delimeter. Plus I didn't like the idea of cramming the row source with what is already two thousand values.

So I am currently using "Table/Query", which has the additional complication of clearing the lists, which I currently do on form load as:
   
    'initialize our lists
    Const accessSucks = "Value List"
    Me.lstAssignedPeople.RowSourceType = accessSucks
    Me.lstAssignedPeople.RowSource = ""
    Me.lstUnassignedPeople.RowSourceType = accessSucks
    Me.lstUnassignedPeople.RowSource = ""
    Const accessReallySucks = "Table/Query"
    Me.lstAssignedPeople.RowSourceType = accessReallySucks
    Me.lstUnassignedPeople.RowSourceType = accessReallySucks

I had to do this because it turns out that when it a Table/Query type, and the query returns an empty set, Access leaves a new record in there to add, so my ListCount was always saying 1 when the list was in fact empty.

I don't have any experience with using a field list.

The set-up *is* complicated, partly because I am trying to use it as a generic way to manipulate both self-referential tables, and do so based on any related table id (ie, employees have an associated entityID, a SupervisorID, etc, all of which are of interest yet used similarly for maintence).

I would certainly appreciate any ideas on uncomplicating this.

Thanks, Eric
0
 
LVL 39

Expert Comment

by:thenelson
ID: 13951822
I know you are making a statement here, one all of us has felt at one time and another but you can replace:

    Const accessReallySucks = "Table/Query"
    Me.lstAssignedPeople.RowSourceType = accessReallySucks
    Me.lstUnassignedPeople.RowSourceType = accessReallySucks

with just:

    Me.lstAssignedPeople.RowSourceType = "Table/Query"
    Me.lstUnassignedPeople.RowSourceType = "Table/Query"

That is really a BTW.  The real question is:
   
After you set  Me.lstAssignedPeople.RowSourceType &  Me.lstUnassignedPeople.RowSourceType what is
Me.lstAssignedPeople.RowSource & Me.lstUnassignedPeople.RowSource?
0
 
LVL 39

Expert Comment

by:thenelson
ID: 13951863
Another aside:

Yes Access sucks

but Cobol sucked supremely

and machine language is the black hole of suckiness.

I guess i'm showing my age.
0
 

Author Comment

by:efinger
ID: 13951931
Hey, would you feel better if I said "accessSucksWhenItComesToInitializingAList"? :-)

Don't get me wrong, VBA/Access is more than decent enough, and the fact that it's 'free' with every installation of Office and therefore immediately relevant to all 10,000 people in my company makes it a home run. It *is* limiting in many ways compared to some , ummm, modern languages I've worked with (java mostly).

To answer your question, the row source for each list is called after a change occurs in a combo box, starting off with the UpdateLists method in my original posting of this thread. The real work occurs in a module outside of the form, as follows:
   
Public Sub SetEmployeeRelatedListBox(aTargetLst As ListBox, aLookUpField As String, anID As Variant)
   
    aTargetLst.RowSourceType = "Table/Query"
    aTargetLst.RowSource = qryEmployeesBySomeID(aLookUpField, QualifyIdForCriteria(anID))
   
End Sub

 which get's a sql string built for it by:

Public Function qryEmployeesBySomeID(aLookUpField As String, anID As Variant) As String
    Dim strSQL As String
    Dim strWhere As String
    Const strOrderBy = "ORDER BY" & BL_SPC & LAST_NAME_FIRST
   
    strSQL = "SELECT [EmployeeID]," & BL_SPC & LAST_NAME_FIRST & " AS displayName " & BL_SPC & "FROM Employee"

    Dim searchType As efSeachTypes
    searchType = NullSearchTypeCheck(efSrchEquals, anID)
    strWhere = BuildWhere(strWhere, aLookUpField, anID, searchType)
   
    If Len(strWhere) > 0 Then strWhere = " WHERE " & strWhere
   
    qryEmployeesBySomeID = strSQL & BL_SPC & strWhere & BL_SPC & strOrderBy & DELIM
End Function

My real question remains that of how to force the ComboBox parameter to be recognized as a ComboBox object and not it's value.

Thanks, Eric

0
 
LVL 2

Accepted Solution

by:
dorbraxton earned 2000 total points
ID: 13952299
OMG

Get rid of the parenthases!!

Change:

UpdateLists (currentCbo)

To:

UpdateLists currentCbo

the parenthasis are causing it to evaluate!
0
 

Author Comment

by:efinger
ID: 13952372
Damn!!!!!!!!!!!!!!!!!!!!!!!

I thought it must be something simple. I'm used to using parens from other languages.

Would the Call statement have also force an evaluation?

Where are the &$%# rules that explain things like this...something so simple you need to OMG when you see it yet all the othe experts here didn't catch that at all.

Thanks, Eric
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question