Combo Question

[merkava]

Hello,

I know that this has probably been asked a million times but....

In VB, I would have done the following, but this is not possible in Access

MyDB.Open CurrentProject.Path & "\" & CurrentProject.Name
rsDB.CursorLocation = adUseClient
rsDB.CursorType = adOpenDynamic
rsDB.Open strSQL, MyDB
If Not rsDB.EOF Then
While Not rsDB.EOF
Combo1.additem rsDB("fldFirstName")
rsDB.MoveNext
Wend

Wend
End If
rsDB.Close

Any suggestions

Best regards,
Aaron

 

[RuralGuy]

Why is that prefereble to just using a query as the RowSource of the ComboBox? I'll admit I do not quite understand what you code accomplishes as I've not used ADO yet. Strickly a DAO boy here.

 

[merkava]

Hi,

To be perfectly honest, I would rather use a query. The problem is that I don't know how to send a parameter to a query.

This would probably be the best way.

Any Ideas?

Cheers,
Aaron

 

[RuralGuy]

What parameter? Is there a parameter in the code you posted? It just looks like:
SELECT fldFirstName FROM SomeTableName
...to me.

 

[merkava]

Re: Passing variables to queries formerly Combo Question

Hi RG,

The Select is put together at runtime and put into the variable stSQL. In VB I know how to populate a combobox but VBA is different.

Since I am using Access, it would be best to call a query and pass it the variable, but as I have mentioned I don't know how to do that either.

Aaron

 

[RuralGuy]

If you can already put the SELECT together in a string variable then just assign that as the RowSource of the ComboBox and that should be it. Am I missing something here?

 

[LPurvis]

Not to confuse the issue...
But I'm confused by the statement that
"In VB, I would have done the following, but this is not possible in Access"
But then follows some Access VBA code (i.e. use of CurrentProject object).

Are you using a version of Access prior to 2002?
Since then combo's have supported the AddItem method.
(Your combo needs to be in Value List mode of course though).

And Access is more than capable of not only executing your suggested ADO code - but indeed binding to the results.
If your Access version is 2002 or later - although AddItem was introduced it became almost immediately less relevent as also introduced was list control binding to recordsets.
So you could open your recordset and assign it directly as the source of your combobox.

However, as Allan rightly says, the substantial beauty of Access lies in its data-centric orientation.
If you have a local query - it's trivial to bind to that using the default RowSource property.
If that query needs to be parameterised - then the triviality of that depends upon from what the parameter is based.
Is it using another form control to limit the results (bog standard cascading combo controls) or some other external value?


Oh - and FWIW
CurrentProject.Path & "\" & CurrentProject.Name
is identical to
CurrentProject.FullName

Oh.. and Jet doesn't support Dynamic cursor types.
You'll be getting a Static recordset back (because you've requested a client side cursor).

Cheers.

 

[merkava]

I am using access 2003 but part of the problem is that it will be run on pre 2002.

I do not know how to bind recordset and assign it directly as the source of my combo or list combobox.

Cheers,
Aaron

 

[RuralGuy]

Have you tried using the ComboBox wizard to create a new control for you?

 

[merkava]

Yes I did, I use this for queries that don't need parameters that chage or to link them to tables. My problem here is to send parameters at runtime

 

[LPurvis]

You've not given us any detail about what the query or parameters are as yet though.
In your original code the recordset opens a query held in the variable strSQL.
What SQL is that defined as? What parameters are there?

If you're building the SQL up in code then you could, naturally, substitute in the parameter values into the SQL in code before assigning that as the rowsource property.

If you have a saved query definition and want to provide parameters to that - then if they're form values they can be inserted via expressions.
Or executed into a recordset (as you were doing).
The AddItem method you mentioned would work.
Binding to a list control is very simple.

Set Me.cboControl.Recordset = rst

In your earlier example your recordset was clientside (which is correct - what you'd want for binding) but you'd need the control's Control Source Type to be in Table/Query mode and not Value.

Cheers.

 

[LPurvis]

Incidentally, coming back to an earlier point by Allan, - there's nothing here (thus far) that can't be achieved using DAO instead of ADO.
(i.e. a DAO recordset can be bound just as easily - even more so I suppose as there's no concern of the type or location of recordset cursor).

 

[merkava]

Let make start at the beginng, I have confused the situation. Here is what I want to do

When my form is loaded I have a combo box that is getting its content from a table called tblTeams.

Keyfield => fldTeamId

What the user sees and choses => fldTeamDescription

When the user choses a team I want a second combo box or list box to be filled by using the following SQL statement.

SELECT tblAgent.fldAgentId, tblAgent.fldFirstName, tblAgent.fldLastname
FROM tblAgent
WHERE (((tblAgent.fldTeamID)=?(this will come from the first combo box)) AND ((tblAgent.fldPositionId)<>?(this will be provided by a different route)));

As far as I understand there are at least two ways to do this write the code in the form or create a stored procedure and pass it the two values that I wish.

I hope that this clears things up.

Best regards,
Aaron

 

[wiklendt]

i think things have been confused by over-complexing. to me, it sounds like merkava just wants a select query with a parameter OR a Not-In-List event?


this is the code i use for not in list (when a value you want in the combobox is not available but SHOULD be entered):

Private Sub LysateBox_NotInList(NewData As String, Response As Integer)

On Error GoTo Err_LysateBox_NotInList

Dim intAnswer As Integer
Dim strSQL As String

intAnswer = MsgBox("The lysate storage box " & Chr(34) & NewData & _
    Chr(34) & " is not currently listed." & vbCrLf & _
    "Would you like to add it to the list now?" _
    , vbQuestion + vbYesNo, "ICU Cycling Study")
    
If intAnswer = vbYes Then

    strSQL = "INSERT INTO tblCHROMpatches_boxLysates([LysateBoxNumber])" & _
             "VALUES ('" & NewData & "');"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    MsgBox "New lysate storage box added.", vbInformation, "ICU Cycling Study"
    Response = acDataErrAdded

Else
    MsgBox "Please choose an existing lysate storage box." _
            , vbInformation, "ICU Cycling Study"
    Response = acDataErrContinue
End If

Exit_LysateBox_NotInList:
    Exit Sub

Err_LysateBox_NotInList:
    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
    Resume Exit_LysateBox_NotInList

End Sub

as for using a combobox as a parameter in a query, you have a record source in the combobox. you query then has the 'criteria' as

Forms!frmMain.cboYourCombo

... but of course, no one has said where the paramter is being implemented, or what it is or how it will be used...

if you want to build and SQL statement with a paramter, that can be a little more involved, or not, depending on the complexity of your query... could be as simple as:

strSQL = "SELECT * FROM tblTable WHERE PrimaryKeyID = " & Me.cboControl

 

[wiklendt]

ooops, i just read merkava's last post - sounds like a cascading combo situation.

you source for the second combo would be a query with the parameter like the second example i gave in my previous post.

ie.

Forms!frmMain.cboYourCombo1

 

[LPurvis]

The general gist of this thread that I've understood has been cascading combo...
But it's not been voiced exactly.
However the requirement has seemed to be the use of an existing stored query definition (which accepts parameters).

As I asked - it depends if these parameters are form based control expression or just parameters based on some other values which must be passed into the query upon execution.

For what it's worth - if the control being filtered and the referenced control(s) are on the same form then there's no need to separate or fully expand the control reference.
i.e. you can have a fixed permanent rowsource property.
(It would generally be a foreign key that was referenced though of course ;-)

SELECT * FROM tblTable WHERE ForeignKeyID = Form!FirstControlName

This can then be used in your query and the second control just requeried after the first is updated.
If they're not on the same form then you do need the full expression.

SELECT * FROM tblTable WHERE ForeignKeyID = Forms!FirstFormName!FirstControlName

That's not to say you can't implement this as wiklendt suggests with a SQL built string in code. That's very common. It's just not necessary.

(As an aside, in the example referring to "= Form!FirstControlName" the "Form" can be optional however than then requires that the control name doesn't coincide with the name of a field in the query that you're building, The expression service isn't then invoked as the field name will be assumed.)

Cheers.