Auto Fill a Text Box When No Data Present 1

[rus77d]

I have a form called After Action Report that is linked to a table name After Action Report. On the form is a text box named Case Number.

Is is possible when there is no data present to use code to fill the text box with the Current year with a "-" followed by the sequece of records for that year, And then to reset with every new year after that.

I know little about using code and need extensive help. Thank you for any help you can provide.

Ex. 2006-1, (New Record)2006-2, (New Year and New Record) 2007-1

 

[fneily]

To clarify for me and/or others:
Do you have a form or a report? Sometimes I think you're talking report, then you throw in the word form. Confusing.
Then you state "when there is no data present" but then say "followed by the sequece of records". Well, if there is no data, how do you have records??
Can you be a little more clear?

 

[rus77d]

For clarification; The Table, Forms, and Report are each called After Action Report. This is a single table that is not tied to any other tables for data.

I would like to have the report print out the years and sequence for that year in the Text Box Case Number. There are many other Text box for data entries. But I only want this to work if the Text Box Case Number has no data in it for that record.

 

[MajP]

First read up on naming conventions. These are very bad names and will only lead to trouble. NO Spaces, No objects with the same name. I renamed your objects
frmAfterAction
tblAfterAction (the table)
txtBxCaseNumber (the text box)
txtCaseNumber (the field)

make a public function

Public Function getCaseNumber() As String
  getCaseNumber = Year(Date) & "-" & (DCount("txtCaseNumber", "tblAfterAction", "left([txtCaseNumber],4) = year(date())") + 1)
End Function

in txtBxCaseNumber set the default value to
=getCaseNumber()

Remember you can not sort on this field, (2006-10 comes before 2006-2), if you delete records this could get out of sequence. I would not use as a primary.

 

[MajP]

Now I am confused. I was thinking you wanted to default the forms value.

 

[rus77d]

Thank you very much for your help. There are several other applications that are smiliar that I would be able to use as well!

 

[rus77d]

I did, however, if the individual will placed an assigned case number than he can still edit over it, the default value works well and suits it purpose.

 

[rus77d]

Argh, Sorry Majp, I thought it was working, but after tooling around with it found out that was not what I was looking for.

I am sorry for any confustion and I thought I was being clear. I will re-explain.

I would like to have that value inserted if there is not value entered into that text box. I would like to have it appear on the report as well. This is not a primary key, rather a way to link other After Actions together by way of supplementals.

There are two ways case numbers can be assigned, one when the individual has the pre assigned number, and one when there is no assigned number. I would like to use the format of the Year and Seqence where there is no assigned Case Number.

Once again I apologize for the misunderstanding and fustration.

 

[MajP]

From what you say maybe I was not confused after all. Well it seems what I suggested is close, but you said it did not exactly do what you want. The report is irrelevant to the discussion because this approach will save a value into the field, Case Number, so you can show it on a report or anywhere else. My question is what needs to be modified on this approach. One thing I was thinking was that if you had records for 2006 as

Case Number
2006-1
xyz1234
ttt345
yyyn456
is the next record (2006-2 or 2006-5)
Is it the second of that format or the fifth for the year?

The example code used the first assumption, but this can be modified easily to do the second.
On the form you could instead of using the default value, run it on the before update so if they leave it blank it will fill it in.

 

[rus77d]

The first worked great, but I would like to be able to have the formart to show the second example you just stated. In your example it would show the 5th of the year.

 

[MajP]

To do that you would need some way to figure out how many records belong to a specific year. Unless you already have a field that you can query to do that you will need to add a field.
You could add a field called intYearEntered. Make the default value equal. You do not have to have this on any form, basically it is a tag.
= Year(date())

Then
Public Function getCaseNumber() As String
getCaseNumber = Year(Date) & "-" & (DCount("txtCaseNumber", "tblAfterAction", "intYearEntered = year(date())") + 1)
End Function

There is an assumption that case numbers are not deleted after they are entered. So if you have ten records, and the last is 2006-10, but you delete 4 the next produced would be 2006-7. Throughing everything out of order. This can be worked around using DMAX and one more generated field.

 

[rus77d]

Two Stars. Thank you very much MajP works Great!