Extract partial field name to refer to another field

[oneidalake]

I have a form with 40 radio buttons and 40 date fields
The radio buttons are named Select1,Select2, Select3...
The date fields are named d1,d2,d3...
Each radio button number corresponds to the date field number.

I'm trying to send the value of the date field to a single field called "SelectedDate" when the corresponding radio button is on.

Basically, when I enable Select1, I want to send the value of D1 to SelectedDate. I know if I type in the exact names of the fields it will work, but I'm trying to learn how to do this without having to modify each sub.

Something like this, but of course this only sends the name of the date field to the SelectedDate field.

Note: I'm using 'mid' to extract the number from the radio button and then put a 'd' in front of it to refer to the date field.

ClickedDate = "[d" & Mid(Me.ActiveControl.Name, 7, 2) & "]"
If Me.ActiveControl = True Then
SelectedDate = ClickedDate
Else
SelectedDate = Null
End If

I apologize if this sounds confusing, but I wasn't sure how to describe this.

Thank you!

 

[isladogs]

Suggest you change the names for all your form controls to something meaningful.

Normally this is recommended anyway as its difficult to remember what is the purpose of items like Select1, Select2, d1, d2.

In your case you've proved the point perfectly
If your field names are meaningful and your control names are based on the field names, life is much easier

And if your consistent it becomes easy to do code like you want

e.g. field EndDate, radio button optEndDate, textbox txtEndDate, combobox cboEndDate etc etc

 

[Solo712]

I have a form with 40 radio buttons and 40 date fields
The radio buttons are named Select1,Select2, Select3...
The date fields are named d1,d2,d3...
Each radio button number corresponds to the date field number.

I'm trying to send the value of the date field to a single field called "SelectedDate" when the corresponding radio button is on.

Basically, when I enable Select1, I want to send the value of D1 to SelectedDate. I know if I type in the exact names of the fields it will work, but I'm trying to learn how to do this without having to modify each sub.

Something like this, but of course this only sends the name of the date field to the SelectedDate field.

Note: I'm using 'mid' to extract the number from the radio button and then put a 'd' in front of it to refer to the date field.

ClickedDate = "[d" & Mid(Me.ActiveControl.Name, 7, 2) & "]"
If Me.ActiveControl = True Then
SelectedDate = ClickedDate
Else
SelectedDate = Null
End If

I apologize if this sounds confusing, but I wasn't sure how to describe this.

Thank you!

You could put the buttons inside a frame on the form (eg "frClickedDate") and then extract the value of the selected one in the frame's Click event simply as

SelectedDate = "d" & Cstr(frClickedDate)

How to set up a frame : https://msdn.microsoft.com/en-us/library/aa241724(v=vs.60).aspx

Best,
Jiri

 

[jdraw]

I agree with the other responses so far, but would like to now more about the 40 buttons and 40 Dates
in business terms. What exactly do the buttons/Dates represent?

 

[oneidalake]

Thank you for all the responses.

Jdraw, Its basically a scheduler form. You are presented with date fields on a 'calendar' and you select a date by clicking the radio button next to it. Then an append query runs based on that date.

The user is only going to select one date before the query runs. I was just trying to refer to the corresponding date field since they use the same number (IE: 'Select1' option button clicked would send the value of 'd1' to another text box.)

While I could do this by using

if select1=true then
SelectedDate=d1

I was trying not to write a customized sub for each option button number and date field. I was hoping I could use a 'd' and the number of the option button selected to extract the value the 'd1' date field. I'm close, but I'm only getting the field name 'd1' instead of the value.

ridders, I agree with you about naming, but at this point, I'm just playing around with 'theory' before I actually create a production form. Right now, the words 'select' and 'd' mean more to me than opt and txt would. Also, there's not going to be much more on this form.

solo712, Thanks for the tip on option group frames. I've used them before and I may use it on this. Was just hoping I could find out where my code failed to refer to the date value rather than the field name.

Thanks again all!

 

[Mark_]

If your goal is to have user "Select type of date to fill" and then "Fill date", why use option buttons instead of a combo box? 40 option buttons seems rather... cumbersome?

 

[oneidalake]

Hi Mark

The dates are arranged in different areas of the form, so unfortunately, a combo-box would not work.

If you look at my original question, my code is giving me the field name instead of the field value. Do you know where I went wrong?

Thank you.

 

[Galaxiom]

Maybe this?

ClickedDate = Me.Controls("[d" & Mid(Me.ActiveControl.Name, 7, 2) & "]")

 

[oneidalake]

Galaxiom. That did the trick!

Thank you so much. I appreciate your help.