Textbox Control Source As Field Input

[Jester0001]

I have created a form [frmSampleLogIn] that will be used to enter records into a table [tblSampleLogIn]. Two of the fields in the table are:

[AccessionNo] AutoNumber Format 0000
[DateEntered] Text; Default Value =Format(Date(),"yymmdd")

What I am trying to do is to have a Text Box on the form that will display a combination of the two fields, and then record this value into the table when a new record is created. For example:

Say the next number for the [AccessionNo] autonumber field is 0045, and the value of the [DateEntered] field is 120229 (based upon the format), then I would like to have the Text Box on the form [frmSampleLogIn] to return something similar to: "0045120229". Then, to have that value stored in another field in the same table [tblSampleLogIn].

However, there is another small formatting change that needs to take place before the value is displayed & stored. I have created a query for such a format:

LabID: Format(Right("0000" & [DateEntered],4),"0000") &  "-" & Format(Right("000" & [AccessionNo],3),"000")

Thus, with the above values for the [AccessionNo] and [DateEntered] fields, the end result is the query returning "0229-045" into the LabID field.

I have found a topic that talks a little bit about what I am trying to do.

Being new to Access, I tried to put the query/formula:

LabID: Format(Right("0000" & [DateEntered],4),"0000") &  "-" & Format(Right("000" & [AccessionNo],3),"000")

directly into the Control Source of the textbox, hoping that it would return the combined values, and then insert them into a field in the table when a new record is created.



I think that I am on the wrong track with what I want to achieve. Any insight or suggestions are welcome. Thanks!

[June7]

What you want to do is create a custom unique id that is dependent on other fields in the table. This is usually a bad idea. For one thing, this value can be calculated whenever needed, and for another, saving dependent data is risky.

Results of calculation in textbox are not automatically saved. Only data entered in controls bound to fields is saved directly to table. Doing what you want requires VBA code.

Saving dates as text in a nonstandard structure will cause you issues in future with manipulating those values.

[Jester0001]

June7,

Thank you for your insight. I am a little confused as to how this would be a bad idea. This new field would not be the pk for the table. Since this value can be calculated, do you suggest that I create a calculated field? If so, how can I get this information to display in a textbox on the form whenever a new record is created?

I was able to create a new record and populate the textbox with VBA code using a button:

Private Sub btnNewRecord_Click()

'Creates a new record
DoCmd.GoToRecord , , acNewRec

'Generates a LabID in the format YYMMDD-0000
Dim lDate As String

lDate = Format(Date, "yymmdd")

[LabID].Value = (lDate & "-" & Format(Right((DMax("[AccessionNo]", "[tblSampleLogIn]") + 1), 4), "0000"))

End Sub

However, the data in the [LabID] Text Box is in the format YYMMDD-0000. I would like for it to be stored in the table in this format, but to be displayed in Text Box in the format MMDD-000 (the right 3 zeros).

I tried using the Format Property in the Property Sheet of the Text Box, but I have been unsuccessful in getting the display format correct.

[June7]

Bad because dependent data can become out of sync with the data they are based on.

Textbox on form would be bound to the field with calculation.

Can't use that format pattern on a text value. You can use the two original fields to display that constructed value (assuming DateEntered is a Date/Time field).

Format(DateEntered,"MMDD") & "-" & Format(Right(AccessionNo,3),"000")

[Jester0001]

June 7,

I took your suggestion, and created a calculated field [LabID] in the [tblSampleLogIn]. However, I was hoping to use this to set up a relationship with another table [tblMycoData], and then add a subform [subMycoData] on my form [frmSampleLogIn].

As you probably already know, Access 2010 won't allow me to create a relationship with a calculated field. Thus, I created an [AccessionNo] field on the [tblMycoData], and related the two tables using [AccessionNo]. I got the form and subform created and linked properly.

Thank you for you suggestion and insight. Now I face another challenge, which will require another topic.