Refrence combo column in a Query??

Where is this query?  
Is it an SQL string in code?
Is is a predefined query on the Queries tab of the DB window?
What is the content of the whole query?  (Could you post it here?)

Brian

It is a Query in the Query tab of the DB.
here is the sql code: (I have simplified it but with the same error)

SELECT Transaction.Location, forms![frmtransaction]![combo4].[column](2) AS Expr1
FROM [Transaction];

what kind of data is in column 2?

This looks perfect.  (I like

SELECT Transaction.Location, forms!frmtransaction!combo4.column(2) AS Expr1
FROM [Transaction];

better, aesthetically...)

What happens when you run this?

(Are you keeping in mind that the 1st column of a query is .Column(0)?)

Brian

Drek -

Data in Column 2 is Text


Brian

This is what I get:

Undefined Function 'forms!frmTransaction!combo4.column' in Expression


Re aesthetics - I agree. I tried it every which way... just sent you that one...

You're right!  You could knock me down with a feather!

Here's what I did to get it to work:

In a module:

___________________________

Option Compare Database
Option Explicit

Public Function GetColumn2() As Long
GetColumn2 = Forms!frmTransaction!combo4.Column(2)
End Function
___________________________


In the query:

___________________________

SELECT *
FROM NOTE_DTL
WHERE GN_ID = GetColumn2();
___________________________


Of course, you will need to supply the names of your particular elements...

Brian

rrr333222 -

Try this:

Have a Text Box, say X with its Control Source =[combo4].[column](2)
 
The query will be:
---
SELECT Transaction.Location, forms![frmtransaction]![X] AS Expr1
FROM [Transaction];
---

Note: Expr1 will be a repeated value for every record of Transaction.

hnasr

I had forgotten this, but you can't get to an individual column of a list or combo box from a query. You can only do it through DAO/ADO, through code. I'd go with something like creating a function or filling a text box with the value from the combo and then referring to that in the query, like Brian suggests.

Brian -

the idea of using a Function is good. But when I try going into the form for the first time I get this error:

Run-time Error -2147417848 (80010108)
Method 'Column' of object of '_combobox' failed

Where do you get that?
Is the form giving the error?

when I try to open the form. I think it's because the function somehow gets called before the form is loaded with the combo4 ??

oh, you see, the form is based on a query that uses the function in it..

A bit of a circular reference then hmmm?

Add this to a module:

____________________________

Function IsRunning(FrmNam As String) As Integer
   
    On Error GoTo IsRunning_Err
   
    Dim i As Integer
    For i = 0 To Forms.Count - 1
        If Forms(i).Name = FrmNam Then
            IsRunning = (Forms(i).CurrentView <> 0)         ' 0 = Design Mode.
            Exit Function                                   ' 1 = Form view.
        End If                                              ' 2 = Datasheet view.
    Next i
   
IsRunning_Res:
    Exit Function

IsRunning_Err:
    msg = Err.Number & ", " & Err.Description & " in Function 'IsRunning(),'" & vbCr
    msg = msg & "module: Global Variables."
    MsgBox msg
    Resume IsRunning_Res

End Function
____________________________

Then modify the earlier function:
____________________________

Public Function GetColumn2(dmy As Variant) As Long

     If Not IsRunning("") Then
          GetColumn2 = 0
     Else
          GetColumn2 = Forms!frmTransaction!combo4.Column(2)
     End If

End Function
____________________________


That will allow the form to open.

(Of course, the field that you are trying to fill will be 0...)

Brian

rrr333222 -

1) I expect the questioner to comment on every comment.  So experts can think of better ways.

2) please mention the name of whom you are commenting to so we can follow the answer.

3) when I answer I spend time on that for the sake of sharing knowllege. My comments works well with me on my environment.  Your comment will show if it works in your environment.

Brewdog-

I mentioned the text box solution, but you did not mention this,like hnasr suggests.

Brian -

Nice idea. But after using the IsRunning Function I still seem to have the same problem. It seems that the form is allready running when the Function is being called by the underlying Query.

Hnasr -

Sorry for not commenting.
But I had actualy tried this (text box=combo) before posting this question, and while this had allways worked for me in the past, here for some reason, it was not. It may have something to do with the underlying query, or the refresh of the form after they change the drop down (combo). I thought it might be too confusing to try to fix this a particular way, and I was happy just to get this working, any which way.

I think that you need to rethink your approach.  If you have a circular reference, as it seems you do, the solution is flawed.

Is it that you want to use the combo to modify the form's contents once the form is open?

If so, you need to give the form a recordSource that does not rely on the combo, then use the combo only to change the already open form.

Thoughts?

Brian

Brian -

the form is setup as a header/detail.

In the Header I have the 2 Combos, and they should be empty when I first come into the screen. But even if not, when I make a change to combo1 then combo2 should (and does) become empty (I do this in code).
The Detail of the form is based on a Query whose criteria is based on both these combos (and the column(2) of combo2).

So in truth I don't think this is a circular refrence.

or am I missing something? (It wouldn't be the first time!)

The form cannot open with the detail dependent on something in the header.  Those combos don't have any datat till after the detail's data gets examined.

This is my guess:

When the form opens, it processes the RecordSource first, prior to processing the individual controls, (header, footer or detail), presuming that having the data available at the time the controls are processed is the most reasonable approach.

So when it looks into the combo during the processing of the RecordSource, there's nothing to see, and it's an error.  The combo has not yet been established.  Every control is a child window, and they all have their specific methods.  At the time of processing the control, the child window that is the control is established, then the methods for the new window are loaded into memory, much as a DLL function.

Since the function, '.Column' is not yet loaded, (properties are loaded just as functions are), you get "Method 'Column' of object of '_combobox' failed," because the 'method' has not yet been established when the RecordSource is being processed.

Brian

Brian -
" Those combos don't have any datat till after the detail's data gets examined. "
No. These combos are not dependant on anything other that a straight sql in its properties. The header has no problem coming up without the deail being populated. This part works.

Now the solution of putting a textbox with the source pointing to the combo4.column(2) would seem to be the perfect solution, but that does not work.

So I tried putting a line behind the On Change Even in Combo4, and in  the code I put this line:
LocAreaDesc = me.combo4.column(2).

And this did not work. Examining why turned out to be an eye opener for me. Since the combo4 looks like this:

col0   col1         col2
1       Kitchen     New
32     Kitchen     Old
81     Kitchen      Blue
99     Bath          Green
12     BedRoom   Large
13     BedRoom   Small

col0 is hidden and is the ID
Col1 is what shows up/displays
Col2 is the missing piece that I need.

Problem -
When I change from, say, the first Kitchen (ID=1) to the second Kitchen (ID=32) the Column 2 DOES NOT CHANGE!

so I think here lies the problem.

What do u think?

In this whole discussion I miss the use of a double reference to a combobox. When I want to retrieve a value from a column in the list of a combobox I use Combo.Column(0,combo.ListIndex) for the first column of the active entry and , Combo.Column(1,combo.ListIndex) for the first entry and the second column, etc.

To see how it works just use the toggle breakpoint in VBA with the immediate window. (Normally I place the toggle on the "AfterUpdate" event) And use ?Combo.Column(1,combo.ListIndex) in the immediate window to check the results.

nico5038,

would you please convert your proposed answer to a comment, and let rrr333222 choose the comment that best suits as the answer, when such a comment is presented?

That is our custom in the Access topic area.

Brian

I tried using the two parameter approach, but this make not difference. In fact the combo does not seem to move since the value in the displayed column is still the same.

Sorry rrr333222 and BrianWren, It's my first time around here.

My solution is working oke "under" the form, but I use the AfterUpdate event, and not the on-change !

rrr333222 I've tried to workout your problem in the Northwind sample database by placing a combobox (Combo45)and an unbound field on the customers form.

The combobox's data/row source was set to 'Alphabetical List of Products' and the format/columncount on 3.

The unbound field was set to:
'=[Combo45].[column](1)'

Nothing else was set and by changing the combobox the unbound field is updated automatically.

(I'm using Access2000 maybe that's acting different?)

I use the same "header" / detail construction but place the details in a subform beneath the combo's.
After changing one of the combo's I just construct a string with the 'qualified' SELECT and place it in the subforms rowsource and it will automatically refresh.
(just referencing to the combo.column to get the specific values)

Can you post the SQL for the two combos, and for the form, (and label which is which, and the names of the objects)?

nico5038 -

sorry i was away for a few days.

for some reason =[forms]![formname]!control.column(n) does not work for me in this form. It eludes me why, as I have done this many a time before.

so i switched to having it update it in code in the change event.

This seems to work now.

BrianWren -

There was a problem with the SQL. I was not giving it the right ID

Got some problems with forms myself, the only way to get it working was to rebuild the form. Specially copied forms cause problems with me.
Tried the sub-form solution ?

Nico5038 -
I didn't try the sub-form as I have it working in the form/header-detail.
I never got the =forms!frmTransaction!combo4.column(2) working (thought I have had this work in this type of setup in another application), but since the On Even Change works, I'm not going to touch it...



Brian -

Since no one gave me the 1-2-3 punch solution i was hoping for, and who knows if there even is one, maybe a problem in my Access or something else. But a lot of good ideas were trown around here, and I want to thank everone for that. Tops among that were your helpfull comments.
So I'm going to give you the Answer - I have to give to someone :)

thanks again

Thanks!

I would have said thanks earlier, But we had a baby on the 16th, (went to the hospital the 15th), and I just flat been busy!

Anyway, thanks again!