help with code (desperate)

[jfuller]

Ok, i am desperatly seeking a solution for this problem i have been trying to solve for the last 3 weeks.
I have a data entry form, by which a user will select a created date, their initials fro ma list box and enter a number into field. From these three pieces of info, i want to auto generate a number in the following format
DQC-R-MMDDYYYY-Initials-###. Where DQC-R is a constant that will always apear, the date is the day chosen by the user, the intials are selected from the list box, and hte ### is entered into the numbers field.
DQC-R-05262011-JF-01 for instance if i chose today, my initlas and the number 01. So far, through trial and error. i have come up with the following code in the after update event for the ### field.

Private Sub RecordNumber_AfterUpdate()
Dim RecordNumber As String
RecordID = "DQC-R-" & [Record Inventory]![CreatedDate] & "mmddyyyy" & Me.Technician & "-" & Me.RecordNubmer
End Sub

To me this looks like it should work, however when i test it out, nothing happens.
What am i doing wrong??

Record Inventory is the name of the table, created date is the field in which the date gets entered. Technician is the list box of initals and record number is the number entered by the user.

 

[boblarson]

Is [Record Inventory] a form? If so you need to refer to it like:

Forms![Record Inventory]![CreatedDate]

Also, if you put "mmddyyyy" concatenated in there then it will just put that explicit text. I think you are wanting to format so it would be


Me.RecordID = "DQC-R-" & Format(Forms![Record Inventory]![CreatedDate], "mmddyyyy") & Me.Technician & "-" & Me.RecordNubmer

 

[jfuller]

Thank you Bob, for the quick response. Indeed, from what i know that looks like it should do it, but sadly something is not working. And i have no idea what that could be. I know this is possible because there is another database here with code that does this.

 

[boblarson]

but sadly something is not working.

When you say it isn't working, what does that mean? Does an error appear? Does it put something in but not the whole thing? Just what? How do you define "not working?"

 

[jfuller]

The issue is that nothing appears. I enter the last piece of information in the field with the after update event, i click to the next field, but the number is not genereated. All i have is an empty field?

 

[boblarson]

Looks like you can get rid of this

Dim RecordNumber As String


which may be overriding the Me.RecordNumber part.

 

[jfuller]

I did try removing that statement, but still nothing. Any other ideas?

 

[boblarson]

Is the control really

RecordNubmer

or is it

RecordNumber

and is it a control that is bound to that field and is there anything in that field?

And is RecordID the name of a control on the form?

 

[jfuller]

My access speak is not where it should be i gather. I will explain the best i can. the record number is a control on my form that is bound to a table i have called Record Inventory (I am creating or attempting to create an access application for my department to manage our physical records, as in papers/documents). The control record ID is on the form itself, i want it to be generated after a user has entered those three required peices of information into other fields. I chose to put the code in the record number after update event because it follows the format of the number i am trying to create.

My ultimate goal is to genrerate a unique "serial number" for each and every document created, using the criteria i mentioned above.

I hope that makes it a little clearer.

 

[boblarson]

A couple of clarifying questions...

1. Record Number is a control on your form. Who fills that in?

2. RecordID is the name of the control (different control than Record Number right?) or the name of the field or both?

In your code you have RecordNubmer so is the control really spelled that way or should it be RecordNumber?