[AccessD] Query expression refers to column(1) on a form

Thanks Stuart, this method may come in handy.

/gustav

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Stuart McLachlan
Sendt: 1. oktober 2014 03:15
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] Query expression refers to column(1) on a form

I've never been able to do it either.  I generally use a Static function which I set before opening the query/report:

Static Function BatchName(Optional s As String) As String 
    Dim Store As String 
    If Not IsMissing(s) Then
        Store = s
    End If
    Batchname = Store
End Function

Then put GetbatchName() in the query

And in the onClick which invoked the query or whatever:

...
BatchName  cboBuildType.Column(1) & " " & Format(cboMonth.Column(2), "yyyymmdd")

Docmd.OpenReport "rptMyRreport"
...

On 30 Sep 2014 at 17:30, David McAfee wrote:

> So this is weird, I know I've done this before, I've just been away 
> from Access for too long (I think).
> 
> I assumed that I can create an expression in a query that would refer 
> to a column of a combo box on a form as such:
> 
> Expr1: Forms![frmBuild]![cboBuildType].Column(1)
> 
> I get an error when I try to run the query: Undefined Function 'Expr1:
> Forms![frmBuild]![cboBuildType].Column' in experession
> 
> (notice the column number is missing)
> 
> When I enter  ? Forms![frmBuild]![cboBuildType].Column(1) in the 
> immediate window, it displays the value as expected
> 
> I made a work around to get this going by creating a public function 
> and calling it as such:
> 
> Expr2: GetBatchName()
> 
> 
> Public Function GetBatchName()
>     GetBatchName = Forms![frmBuild]![cboBuildType].Column(1) & " " & 
> Format(Forms![frmBuild]![cboMonth].Column(2), "yyyymmdd") End Function
> 
> I know, I know, there isn't any error handling, just testing it out...
> 
> Anyway, what am I forgetting?
> 
> It's been a  while.
> 
> D