How to increment pure alpha field

  • Thread starter Thread starter cafe
  • Start date Start date
C

cafe

Not to asnswer your question, but: you can never get into a subform without
a parent record in the main form. So your check on Me.Parent.Newrecord,
whilst well intentioned, is not required.

HTH,
TC
 
I've inherited a database that uses a main form for parents and a subform
for children. Each child subform has a case number from the parent main
form and a separate alpha identifier starting with A. I'm having a problem
incrementing the alpha identifier. I've tried setting the default value to
A and adding one with the following code called from the Before Insert event
of the subform.

Dim CurrentAlpha As String
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Enter a parent record first."
Else
CurrentAlpha = txtID.Value
CurrentAlpha = Chr$(Asc(CurrentAlpha) + 1)
txtID.Value = CurrentAlpha
End If

but I just keeping getting B for the succeeding identifiers. Can someone
help me?

Thanks in advance.
 
Liz, it's probably best to look up the maximum used value directly in the
subform's table. This works regardless of how the subform is sorted or
filtered.

Dim strWhere As String
Dim intChar As Integer
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Enter a parent record first."
Else
strWhere = "[CaseNumber] = " & Me.Parent.[CaseNumber]
intChar = Asc(Nz(DMax("ID", "MyTable", strWhere), "@"))
Me.txtID.Value = Chr(intChar + 1)
End If

Note: If CaseNumber (the foreign key field) is a Text type field, you need
extra quotes:
strWhere = "[CaseNumber] = """ & Me.Parent.[CaseNumber] & """"
 
Actually, this information is not correct.

If the main form is dirty, the record will be saved before focus moves to
the subform. But that doesn't prevent a user moving the main form to a new
record, entering nothing, and then entering something in the subform. This
is a common trap for newbies.

The best solution is to open the subform's table in design view, select the
foreign key field (the one that links to the main form's table), and set its
Required property to Yes. This will prevent orphaned records.

The check in the subform's Form_BeforeInsert is still a good interface,
since the user gets notified of the problem before they fill in the subform
details, rather than after they have gone to that trouble.
 
Thanks to all for helping me out!

--
Liz

Allen Browne said:
Liz, it's probably best to look up the maximum used value directly in the
subform's table. This works regardless of how the subform is sorted or
filtered.

Dim strWhere As String
Dim intChar As Integer
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Enter a parent record first."
Else
strWhere = "[CaseNumber] = " & Me.Parent.[CaseNumber]
intChar = Asc(Nz(DMax("ID", "MyTable", strWhere), "@"))
Me.txtID.Value = Chr(intChar + 1)
End If

Note: If CaseNumber (the foreign key field) is a Text type field, you need
extra quotes:
strWhere = "[CaseNumber] = """ & Me.Parent.[CaseNumber] & """"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Liz Malcolm said:
I've inherited a database that uses a main form for parents and a subform
for children. Each child subform has a case number from the parent main
form and a separate alpha identifier starting with A. I'm having a problem
incrementing the alpha identifier. I've tried setting the default value to
A and adding one with the following code called from the Before Insert event
of the subform.

Dim CurrentAlpha As String
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Enter a parent record first."
Else
CurrentAlpha = txtID.Value
CurrentAlpha = Chr$(Asc(CurrentAlpha) + 1)
txtID.Value = CurrentAlpha
End If

but I just keeping getting B for the succeeding identifiers. Can someone
help me?
Click to expand...
Click to expand...
 
Back
Top