select a combbox column for use as a variable

[Jen0dorf]

Hi



I have now thanks to the forum worked out why my tempvars are not working.

I have a form with two combo boxes one selects the parish, the other the church using the code

SELECT tbl_Included_Church.ChurchID, tbl_Included_Church.Church, tbl_Included_Church.fk_ParishID
FROM tbl_Included_Church
WHERE (((tbl_Included_Church.fk_ParishID)=[Forms]![frm_OpeningForm]![CboParish]));

to only show churches which are related tothat parish.

the search button uses this code

Private Sub btnSearch_Click()
TempVars.Add "varParish", Me.cboParish.Value
    TempVars.Add "varChurch", Me.cboChurch.Value
    DoCmd.Close
    DoCmd.OpenForm FormName:="frm_Home"
End Sub

The two combo boxes have two colums, one containg the id, the other the "text Value"

so in my properties I have

column count 2

column width 0cm 3cm

I now realise the variable is being set to column 1 ie the id rather than the value

Would appreciate a fix

thanks

Ian

[ranman256]

You can't CLOSE the form and continue running code. Once the form closes, the code vanishes with it.
do all events ,THEN close the form.

[Jen0dorf]

Hi

Thought I was doing all code before closing.

However I do accept you know much more than I do.

I did alter the code to carry out everything before closing the form but it still sets the variables to the first column in the table which is an id. So the temp var is 1 or 2 or 3 whereas want column 2 to be the variable ie. Allerton Church, St Marys or St Barnabys.

Though about re ordering the table and dragging my column to to the column 1 position but is this good practice ?

cheers

Ian

[John_G]

This will give you the second column:

TempVars.Add "varChurch", Me.cboChurch.Columns(1)

Columns(1) is not a typo - the column references are zero-based (the first column is Column(0).

[Jen0dorf]

Hi

thanks for the info and I'm sure you are correct except that when I use the sugested code

Private Sub btnSearch_Click()
TempVars.Add "varParish", Me.cboParish.Columns(1)
    TempVars.Add "varChurch", Me.cboChurch.Columns(1)
    DoCmd.Close
    DoCmd.OpenForm FormName:="frm_Home"
End Sub

The error is shown compile error

method or data member not found

and the .columns(1) is highlighted.

The cbo box is set to column count 2 and the width is set to 0cm,3cm

Now at a complete loss

cheers

Ian

[Jen0dorf]

Hi

Sorted thanks to everyone, I deleted the s from the end of columns and its all working

cheers

Ian

[John_G]

Oops, sorry - my mistake. Glad you got it working.