Write Code to Create Form Text Box to Display Date and Name

[raychow22]

Hi Forum,

I like to write a VBA code on my text box "File Name 2" in my Form to display the Date (Date of Inspection) and Name (Inspector Name) that is selected within my Form.

Here's the illustration. The code is my attempt to get it to work but it doesn't.

[June7]

What does 'does not work' mean - error message, wrong results, nothing happens?

Are you trying to save the concatenated value to a field in table? Why? Just do the concatenation when needed. The concatenation expression can be in query or textbox ControlSource.

[raychow22]

What does 'does not work' mean - error message, wrong results, nothing happens?

Are you trying to save the concatenated value to a field in table? Why? Just do the concatenation when needed. The concatenation expression can be in query or textbox ControlSource.

Nothing happens. I am trying to concatenate the values in the form so it can be saved into a table. I know I can concatenate these values in expression builder; however, I do not want to use that. I want a code that can display it in the text box and still can edit at the user's discretion.

[ssanfu]

I know I can concatenate these values in expression builder;

No, not in the expression builder, in a query.... the record source for the form. In general, it is a bad idea to save calculated values.
In a query is would look like

FN2: Format([Date of Inspection],"yyyymm-dd") & "-" & [Inspector Name]

Note: should not use spaces in object names. Instead of [Date of Inspection], how about [InspectionDate]??

You have a query for the form record source, the "FileName2" is bound to the query calculated field. You enter the inspector name and the date and the "FileName2" control displays the calculated value. It is always up to date.



Also note that, in the image, the label for the inspector name has an "i" in it that shouldn't be there: "Inspectior Name"...

[June7]

I think the code would have to go in the AfterUpdate events of the date and name controls, not File_Name2. And should be saving to the field not the control. Bind control to the field if you want users to be able to edit the saved concatenated value.

But only if the field is null and values are available from both date and name controls.

If IsNull(Me!fieldname) And IsNull(Me.[Date of Inspection]) And IsNull(Me.[Inspector Name]) Then
Me!fieldname = Me.[Date of Inspection] & "-" & Me.[Inspector Name]
End If

[raychow22]

No, not in the expression builder, in a query.... the record source for the form. In general, it is a bad idea to save calculated values.
In a query is would look like

FN2: Format([Date of Inspection],"yyyymm-dd") & "-" & [Inspector Name]

Note: should not use spaces in object names. Instead of [Date of Inspection], how about [InspectionDate]??

You have a query for the form record source, the "FileName2" is bound to the query calculated field. You enter the inspector name and the date and the "FileName2" control displays the calculated value. It is always up to date.



Also note that, in the image, the label for the inspector name has an "i" in it that shouldn't be there: "Inspectior Name"...

Okay I see. Please look below at what I'm inputting incorrect

[ssanfu]

If you are dead set on storing the calculation, follow June's advice.


Maybe what I suggest is a little advanced right now.

I think it is not necessary to store the calculation. This would mean you do not need the field "File Name2". The "File Name2" would be calculated in a query.
It would also mean you would have to use a query as the record source for a form or report, not the table.

Soooo,

In the query grid, Row named FIELD, instead of "File Name2", you would put

FileName2: Format([Date of Inspection],"yyyymm-dd") & "-" & [Inspector Name]

Nothing in the Criteria row!

Save the query. Run the query to see the result.
Set the form record source to the query.
Then bind "FN2" to the control "File Name2" on the form/report.



If you have a field named "Inspection Type", you need to add brackets -->> [Inspection Type]
This is why you should not use spaces in names.
Also, do not need ".Value"

[Missinglinq]

The problem with using a calculated Field, like

FileName2: Format([Date of Inspection],"yyyymm-dd") & "-" & [Inspector Name]

in a Query, is this stated requirement:

...I want a code that can display it in the text box and still can edit at the user's discretion...

If the Control on the Form is based on that Field, in the Table, the user won't be able to 'edit (that Control) at the user's discretion.'

Given all requirements, I think June7's suggestion in Post #5 is probably the way to go.

Linq ;0)>

[raychow22]

If you are dead set on storing the calculation, follow June's advice.


Maybe what I suggest is a little advanced right now.

I think it is not necessary to store the calculation. This would mean you do not need the field "File Name2". The "File Name2" would be calculated in a query.
It would also mean you would have to use a query as the record source for a form or report, not the table.

Soooo,

In the query grid, Row named FIELD, instead of "File Name2", you would put

FileName2: Format([Date of Inspection],"yyyymm-dd") & "-" & [Inspector Name]

Nothing in the Criteria row!

Save the query. Run the query to see the result.
Set the form record source to the query.
Then bind "FN2" to the control "File Name2" on the form/report.



If you have a field named "Inspection Type", you need to add brackets -->> [Inspection Type]
This is why you should not use spaces in names.
Also, do not need ".Value"

Thanks ssanfu,

Figured it out. Realized it's not necessary for it to be on the table. I can just use query to combine the date and name together like you mentioned. Thanks again!!