text box generates auto number, need to save that number in a table

[jdvon]

So I have downloaded this neat expression for a textbox control in a form that generates automatic sequence numbers with the 2-digit year in front of it:

=Format(Now(),"yy") & "CAR" & "-" & Left("000",4-Len(CStr([ID]))) & [ID]

(this is in the control source of the properties.)

so the result is: 07CAR-0002.... 07CAR-0003....

My question is: How do I save the generated numbers in the main table where I save all the relevant information from the form entry? It seems that the generated numbers stay with each form entry within the form but i need to store the number in a table for future reports.

Can anybody help, please?

JDV

[Rabbit]

Assuming that the form's record source is the table, just set the text box's control source to the field.

You may want to lock the field so they can't edit it.

[jwolfley]

I don't get it. You type =Format(Now(),"yy") & "CAR" & "-" & Left("000",4-Len(CStr([ID]))) & [ID] into the control source of the properties of a textbox and you get sequential numbers? I just get an error. Is ID a specified field in the table?

[Rabbit]

From what I can tell, [Id] would have to be a field in the table. It would be [Id] that is generating an autonumber. The expression is merely autopopulating a different field.

Otherwise I don't know how that would generate sequential numbers by itself.

[MMcCarthy]

So I have downloaded this neat expression for a textbox control in a form that generates automatic sequence numbers with the 2-digit year in front of it:

=Format(Now(),"yy") & "CAR" & "-" & Left("000",4-Len(CStr([ID]))) & [ID]

(this is in the control source of the properties.)

so the result is: 07CAR-0002.... 07CAR-0003....

My question is: How do I save the generated numbers in the main table where I save all the relevant information from the form entry? It seems that the generated numbers stay with each form entry within the form but i need to store the number in a table for future reports.

Can anybody help, please?

JDV

If you have a calculated control like this on the form then you can't also have it pointing to the field in the table.

One solution would be to set the control source of the textbox (txtSample) to the field in the table and put this calculation in an event of the form. The On current event would probably be best.

Private Sub Form_Current()
 
  Me.txtSample=Format(Now(),"yy") & "CAR" & "-" & Left("000",4-Len(CStr(Me.ID))) & Me.ID
 
End Sub
 

[NeoPa]

I don't get it. You type =Format(Now(),"yy") & "CAR" & "-" & Left("000",4-Len(CStr([ID]))) & [ID] into the control source of the properties of a textbox and you get sequential numbers? I just get an error. Is ID a specified field in the table?

That's because you're not doing it from within JDVon's form.
The [ID] field is already available in his recordset (IE Yes to your last question).