How to select record in continuous subform by a radio button

[FL0XN0X]

Dear,

I have got a form where the subform is a continuous form. The form holds a command button that needs to execute a query using the ID of the selected record in the continuous subform. It is only allowed to select only 1 record of the subform. How can I accomplish this (I was thinking of a radio button, but need some guidance here).


Thank you in advance.
Kind regards,
Bart

[CJ_London]

your query criteria would be something like

ID=forms!mainformname!nameofsubformcontrol!form!ID

can run the query from a button click event, not sure why you are thinking of a radio button

[FL0XN0X]

Radio button because only 1 record and corresponding id is allowed to be used in the suery I want to execute

[June7]

UNBOUND radio button would not restrict selection to single record any more than clicking command button.

Even if multiple records are 'selected', only the record with focus will be referenced in the example code. The record with focus would be the first or last record in the selection.

If you want to prevent selection of multiple records have to set RecordSelectors to No.

What query do you want to execute?

[FL0XN0X]

In the subform continous form, you can provide a range of date (eg start 1-1-2018 stop 1-4-2018 which has id=1, second record will have another date range with start and stop date and Id=2, and so on) the query I want to run can only update one selected date range which the user must Choose. Thus if I want to select date range 2, somehow I must be able to pass the corresponding id 2 to the query

[June7]

User chooses date range from a combobox? Update what table? Why do you need to use an UPDATE action?

Post your SQL statement. And your code - macro or VBA?

I have used the form DblClick event with a label with instruction: "Double click in left margin of a record to view lab data.". This means the RecordSelectors property has to be set to Yes.

[FL0XN0X]

June7, here is the SQL that needs to run after clking the command button:

The only thing that is relevant and which is EXACTLY my question, I need to adjust the 'Where statement' in the SQL to match ONLY the selected record from the continuous subform. Thus, my question remains, how can I select ONE record (ONLY one is allowed, therefore I was thinking of radio button) from the continuous subform and pass the corresponding ID to be used in the Where statement of the SQL statement that needs to be run ?

SQL:
INSERT INTO [Bezetting per dag] ( KalenderId, KindgegevensId, OpvangCodesId )
SELECT Kalender.ID, Klantenbestand.Id, Switch(Kalender.Dag="maandag",[Opvang weekprofiel].[Opvang maandag],Kalender.Dag="dinsdag",[Opvang weekprofiel].[Opvang dinsdag],Kalender.Dag="woensdag",[Opvang weekprofiel].[Opvang woensdag],Kalender.Dag="donderdag",[Opvang weekprofiel].[Opvang donderdag],Kalender.Dag="vrijdag",[Opvang weekprofiel].[Opvang vrijdag],Kalender.Dag="zaterdag",[Opvang weekprofiel].[Opvang zaterdag],Kalender.Dag="zondag",[Opvang weekprofiel].[Opvang zondag]) AS OpvangCode
FROM Klantenbestand INNER JOIN (Kalender INNER JOIN [Opvang weekprofiel] ON (Kalender.Datum>= [Opvang weekprofiel].[Startdatum weekprofiel]) AND (Kalender.Datum<= [Opvang weekprofiel].[Stopdatum weekprofiel])) ON Klantenbestand.Id=[Opvang weekprofiel].[Kind gegevens Id]
WHERE [Opvang weekprofiel].Id=32;

[ArviLaanemets]

The button OnClick must create a query string, like:

SQLStr = 
"INSERT INTO [Bezetting per dag] ( KalenderId, KindgegevensId, OpvangCodesId )
 SELECT Kalender.ID, Klantenbestand.Id, Switch(Kalender.Dag="maandag",[Opvang weekprofiel].[Opvang maandag],Kalender.Dag="dinsdag",[Opvang weekprofiel].[Opvang dinsdag],Kalender.Dag="woensdag",[Opvang weekprofiel].[Opvang woensdag],Kalender.Dag="donderdag",[Opvang weekprofiel].[Opvang donderdag],Kalender.Dag="vrijdag",[Opvang weekprofiel].[Opvang vrijdag],Kalender.Dag="zaterdag",[Opvang weekprofiel].[Opvang zaterdag],Kalender.Dag="zondag",[Opvang weekprofiel].[Opvang zondag]) AS OpvangCode
 FROM Klantenbestand INNER JOIN (Kalender INNER JOIN [Opvang weekprofiel] ON (Kalender.Datum>= [Opvang weekprofiel].[Startdatum weekprofiel]) AND (Kalender.Datum<= [Opvang weekprofiel].[Stopdatum weekprofiel])) ON Klantenbestand.Id=[Opvang weekprofiel].[Kind gegevens Id]
WHERE [Opvang weekprofiel].Id= " & IDValue

where IDValue is read either from same form as [Id] when the button is on subform, or it is the value of active row Id of subform when the button is on parent form. User selects a row on subform (clicks on any available field in row, or navigates to it using subform navigation buttons), and then clicks on report button. After the SQL string is created, the same OnClick event runs the query defined by SQL string.

[FL0XN0X]

ArviLaanemets,

Indeed, button OnClick must create the query mentioned above. Now I was thinking of providing a Command button for each record in the Continuous Subform which has the On Click event with the above SQL string to be executed, thus only 'challenge' left is to have the IDValue populated with the corresponding ID field of the record where the command button is pressed.

Eg. : In the screenshot, you'll see the continuous Subform. Thus, when I click the Command42 next to the first record, the SQL statement must be executed with the IDValue = 31. When I click Command42 from the second record, the SQL statement must be executed with IDValue=32 ...

Can you please tell me how I can accomplish this?

[ArviLaanemets]

There is no way you have a button for every row in continuous form! Look at form in design view - there is only a single row of controls. What you see in form view is a display (i.e. a picture) generated by Access. Really there is a single row of real data - the one of active row. You can have a button in header of continuous form, and clicking on it creates a report for active row in continuous form.

Btw, inserting a button like in your post means, that there is really a single button displayed in form view for every row. This may even work, as clicking on button selects a row (probably), but such design clutters your form. Place the button into form header (the OnClick event code remains same), and place dates into single row instead - the number of rows displayed will be twice as much. An additional click for printing a report is not much of work (or when you have to print tens or hundreds of such reports in row, then you have to consider a better report design )

[FL0XN0X]

Hi,

I know that in design view, there is only one row. Anyway, I solved my issue by inserting following code in the sql Where statement:

INSERT INTO [Bezetting per dag] ( KalenderId, KindgegevensId, OpvangCodesId )
SELECT Kalender.ID, Klantenbestand.Id, Switch(Kalender.Dag="maandag",[Opvang weekprofiel].[Opvang maandag],Kalender.Dag="dinsdag",[Opvang weekprofiel].[Opvang dinsdag],Kalender.Dag="woensdag",[Opvang weekprofiel].[Opvang woensdag],Kalender.Dag="donderdag",[Opvang weekprofiel].[Opvang donderdag],Kalender.Dag="vrijdag",[Opvang weekprofiel].[Opvang vrijdag],Kalender.Dag="zaterdag",[Opvang weekprofiel].[Opvang zaterdag],Kalender.Dag="zondag",[Opvang weekprofiel].[Opvang zondag]) AS OpvangCode
FROM Klantenbestand INNER JOIN (Kalender INNER JOIN [Opvang weekprofiel] ON (Kalender.Datum>= [Opvang weekprofiel].[Startdatum weekprofiel]) AND (Kalender.Datum<= [Opvang weekprofiel].[Stopdatum weekprofiel])) ON Klantenbestand.Id=[Opvang weekprofiel].[Kind gegevens Id]
WHERE [Opvang weekprofiel].Id=Forms![Voeg kind toe].Form![Opvang weekprofiel Subform]![Id];

As a result, there is indeed a command button per record. Reducing the number of buttons by moving the button to the header sections, brings me back to my first question: how to I pass the ID of the Selected Record to the Query: I want the Query to be executed using 1 ID= the ID of the selected record, thus, selecting 1 record, ohw is it forced? I thought by using a radio command button, but till now, nobody seems to understand what I want to accomplish (can be my fault by 'not explaining it too well'). Bottom line: If someone can tell me how I can select 1 record out of a list of records in a continuous form and pass the ID of the selected record to an SQL statement, then I can use another approach

[ArviLaanemets]

When any formula asks for control/field value from continuous form, the value from current/selected record is passed. This means, that when you move the button from row to header, you don't need to change anything.

The only exception is when a bound control is referring to field value in same row (this is how bound combos display different texts for different rows of continuous form).

[CJ_London]

If someone can tell me how I can select 1 record out of a list of records in a continuous form and pass the ID of the selected record to an SQL statement, then I can use another approach

the question has been answered many times in this thread.

how do you select a record? by clicking on it and making it the current record.

how do you pass the id of the selected record? After 11 posts you have adopted the answer in post #2 which should work. So what is it that you are trying to do differently?

[June7]

Are you using a dynamic parameterized query (query object sitting in the Navigation pane)? The method you show in post 11 is how to pass id to query.

Whether button is in Detail or Header section, the behavior will be the same. Clicking button in row does simultaneously select record and pass id from that record.

What is your code to execute the query?