Change input format for a textbox using VBA

Question

I hope I am explaining this clearly:  I have a main form frmMainForm and a subform frmMySubform.

Controls in the subform indicate the desired format for what the user should be entering in the main form:

Me!frmMySubform.Form!txtFirstResponseType = "currency"

Me!frmMySubform.Form!txtSecondResponseType = "date"

The user will enter the responses in the main form:

Me.txtFirstResponse should be in currency format.

Me.txtSecondResponse should be in date format.

How should I go about assuring that the responses are in the correct format? I tried setting the formats during the OnOpen event, but it didn't work:

Select Case Me!MySubform.Form!txtFirstResponseType 

    Case "currency"

    Me.txtFirstResponse .Format = "currency"

    Case "date"

    Me.txtFirstResponse .Format = "date"

End Select

Answer 1

That is not going to work as well as you might hope. Once you set the format of the subform (I assume in datasheet or continuous forms), this is going to be applied to all rows.

This may be one case where an unbound fake grid should be used. That's a lot of work. Make sure it's worth it.

Hi Tom,

This is not a continuous form. So I don't have to worry about changing the format of the controls.

I'm trying to build a way for users to be able to create and control all fill in the blanks on their own library of Word templates. So they will basically create prompts and responses. The responses will be assigned to mergefield names and used as the data source for the merge.

Of course, each prompt that they create should allow them to assign a data type, like date, time, currency, numeric (enter '1' for blue, enter '2' for green) or just plain text.

The table of prompts has to reside on the back end, so in can be centrally maintained and administered.  The table of responses will reside on each user's front end .accdr that resides on their desktop. I know I have not come up with an elegant solution, so any guidance would be appreciated.

My back end prompt table will have one row for each Word template, fields prompt1-prompt40, w/corresponding fields promptType1 - promptType40.

My front end response table will have one record with 40 fields: response1 - response40.

The corresponding Word template will have {mergefield response1} - {mergefield response40}.

It's all I have been able to figure out, since my users only have the runtime accdr, and I want them to be independent and be able to create their own Word templates and field fill ins. This will be a system to replace any {ASK} or {REF} or {FILLIN} fields in their templates.

Answer 2

That is not going to work as well as you might hope. Once you set the format of the subform (I assume in datasheet or continuous forms), this is going to be applied to all rows.

This may be one case where an unbound fake grid should be used. That's a lot of work. Make sure it's worth it.