Access 2007, refer to a combo in another combo on the subform

[Laursen.Aarup]

Hi
I just can't get the reference to work in my subform:
Mainform = frmMain
subform = frmIOList

In one combo (txtOutstation) on the subform i select a range which is to be
used in another combo (txtModule) on the subform in the property; RowSource
like this:

SELECT lu_IOChannels.IOModule FROM lu_IOChannels WHERE
(((lu_IOChannels.OS)=[Forms]![frmMain]![frmIOList].[Form]![txtOutstation]));

I've tryed all kind of combination but it just won't work. It keeps on
opening a promt for the filter value (txtOutstation)

Where's the fault?

kind regrads Jan

 

[Maurice]

Well depending on the sourcetype you might have to adjust the query just a
bit like:

"SELECT lu_IOChannels.IOModule FROM lu_IOChannels WHERE
lu_IOChannels.OS = '" &
[Forms]![frmMain]![frmIOList].[Form]![txtOutstation] & "'"

note: after the first = sign you'll find a single and a double quote and
right at the end you'll find a double, single and a double quote again.

In this case the txtOustation would be datatype string. If the txtOutstation
is a number you should change it to:

"SELECT lu_IOChannels.IOModule FROM lu_IOChannels WHERE
lu_IOChannels.OS= " & [Forms]![frmMain]![frmIOList].[Form]![txtOutstation]

hth

 

[Laursen.Aarup]

Hi Maurice

Thanx for the quick respond. Unfortunately this does'nt end my problem,
access returns an error message = syntax error?!

This is the line in I use in the controls (txt(Module) property RowSource:


SELECT lu_IOChannels.IOModule FROM lu_IOChannels WHERE lu_IOChannels.OS = '"
& [Forms]![frmMain]![frmIOList].[Form]![txtOutstation] & "'";

Ins'nt it the same as you propose?

kind regards Jan

Well depending on the sourcetype you might have to adjust the query just a
bit like:

"SELECT lu_IOChannels.IOModule FROM lu_IOChannels WHERE
lu_IOChannels.OS = '" &
[Forms]![frmMain]![frmIOList].[Form]![txtOutstation] & "'"

note: after the first = sign you'll find a single and a double quote and
right at the end you'll find a double, single and a double quote again.

In this case the txtOustation would be datatype string. If the txtOutstation
is a number you should change it to:

"SELECT lu_IOChannels.IOModule FROM lu_IOChannels WHERE
lu_IOChannels.OS= " & [Forms]![frmMain]![frmIOList].[Form]![txtOutstation]

hth
--
Maurice Ausum

Hi
I just can't get the reference to work in my subform:
Mainform = frmMain
subform = frmIOList

In one combo (txtOutstation) on the subform i select a range which is to be
used in another combo (txtModule) on the subform in the property; RowSource
like this:

SELECT lu_IOChannels.IOModule FROM lu_IOChannels WHERE
(((lu_IOChannels.OS)=[Forms]![frmMain]![frmIOList].[Form]![txtOutstation]));

I've tryed all kind of combination but it just won't work. It keeps on
opening a promt for the filter value (txtOutstation)

Where's the fault?

kind regrads Jan

 

[Maurice]

If it's a rowsource why not make a statement out of it by clicking the three
dots next to the rowsource and build the statement in the query editor. Once
done the statement will be saved as the correct statement in the rowsource.

So try this (assuming you are using a combobox):

- behind the rowsource property click the three dots
- Build the query select statement with the querybuilder
- in the criteria of the specified field place the parameter (use the
function builder to guide you through this)
- close the query and the selectstatement will be in the rowsource property

- You can test the outcome of the query in the queryeditor by providing a
'hard' value for your parameter.

Note: the statement i presented assumes the parameter is of a stringtype
(=text) if the txtOustation is a number use the other one.

see if that helps.


--
Maurice Ausum

Hi Maurice

Thanx for the quick respond. Unfortunately this does'nt end my problem,
access returns an error message = syntax error?!

This is the line in I use in the controls (txt(Module) property RowSource:


SELECT lu_IOChannels.IOModule FROM lu_IOChannels WHERE lu_IOChannels.OS = '"
& [Forms]![frmMain]![frmIOList].[Form]![txtOutstation] & "'";

Ins'nt it the same as you propose?

kind regards Jan

Well depending on the sourcetype you might have to adjust the query just a
bit like:

"SELECT lu_IOChannels.IOModule FROM lu_IOChannels WHERE
lu_IOChannels.OS = '" &
[Forms]![frmMain]![frmIOList].[Form]![txtOutstation] & "'"

note: after the first = sign you'll find a single and a double quote and
right at the end you'll find a double, single and a double quote again.

In this case the txtOustation would be datatype string. If the txtOutstation
is a number you should change it to:

"SELECT lu_IOChannels.IOModule FROM lu_IOChannels WHERE
lu_IOChannels.OS= " & [Forms]![frmMain]![frmIOList].[Form]![txtOutstation]

hth
--
Maurice Ausum

Hi
I just can't get the reference to work in my subform:
Mainform = frmMain
subform = frmIOList

In one combo (txtOutstation) on the subform i select a range which is to be
used in another combo (txtModule) on the subform in the property; RowSource
like this:

SELECT lu_IOChannels.IOModule FROM lu_IOChannels WHERE
(((lu_IOChannels.OS)=[Forms]![frmMain]![frmIOList].[Form]![txtOutstation]));

I've tryed all kind of combination but it just won't work. It keeps on
opening a promt for the filter value (txtOutstation)

Where's the fault?

kind regrads Jan