pass record_id to row source of combobox (1 Viewer)

[megatronixs]

Hi all,

I have a subform with a combobox. the combobox will need to get me a selection of a participant that has her/his name in one of the records.
For example in record 3 I have 3 participants "John Potter, Amelia Blake, Peter Cetera"
If the form is showing the data for record id 3, then clicking on the combobox would give me the choice as from above, if on another record nr, then those ones from that record.

I tried the below, but without luck:

SELECT [tbl_invitation_participant_name].[participant_name] FROM tbl_invitation_participant_name WHERE record_id = Me.record_id;

this is the SQL that is on the row source of the combobox.

Greetings.

 

[George Moore]

Hi

In this scenario I always have a hidden column (width set to zero) containing the GUID of each record. I then use an "OnClick" event with the control to compile a SQL string along the lines of :-

Dim s as string

s="SELECT * FROM [TableName] WHERE "
s = s & "[GUID]=" & Me.ControlName.Column(0)

 

[George Moore]

By the way using a sub form you will need syntax along the lines of

me.controls("SubFormName").form.controls("ComboName").Column(0)

 

[Solo712]

Hi all,

I have a subform with a combobox. the combobox will need to get me a selection of a participant that has her/his name in one of the records.
For example in record 3 I have 3 participants "John Potter, Amelia Blake, Peter Cetera"
If the form is showing the data for record id 3, then clicking on the combobox would give me the choice as from above, if on another record nr, then those ones from that record.

I tried the below, but without luck:

SELECT [tbl_invitation_participant_name].[participant_name] FROM tbl_invitation_participant_name WHERE record_id = Me.record_id;

this is the SQL that is on the row source of the combobox.

Greetings.

Try :

SELECT [tbl_invitation_participant_name].[participant_name] FROM tbl_invitation_participant_name WHERE record_id = Me.Parent.record_id;

Best,
Jiri

 

[Orthodox Dave]

Hi

I then use an "OnClick" event with the control to compile a SQL string along the lines of ...

I would make it a GotFocus event rather than a Click event. The Click event will fire when you first click the Combo, and will fire again when you select the list item, whereas the GotFocus only fires on the first click.

 

[Cronk]

You don't need to change the rowsource of the control. Just put the sql on the combo's rowsource property, and the combo's contents will change as the focus shifts to another record.

If any event is to be used, it would be the form's onCurrent event.

 

[Mark_]

For example in record 3 I have 3 participants "John Potter, Amelia Blake, Peter Cetera"

How are you attaching these three participants to the record?

Are they fields in the record? Are they in a multi-value field? Or are they child records?

 

[megatronixs]

Hi all,

I was absent some time, so I will get back to this and give feedback.

Greetings.