Auto generated field value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

I want Access to automatically fills a combobox with a field code value from
its list that
matches the characters as I type them in. Then to find the last available
value and by pressing the Up Arrow key to get this value +1 (otherwise I
should have a duplicate).
My code is in the LL00000 format and (thanks to Steve Schapel) in order to
work it out I did this.

An SQL query as the row source of the combobox:
SELECT Items.ItemCode
FROM Items
ORDER BY Items.ItemCode DESC;

The code on the Key Down event of the combobox:
Private Sub cboItemCode_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeyUp Then
Me![cboItemCode] = Left([cboItemCode], Len([cboItemCode]) - 3) &
Format(Val(Right([cboItemCode], 3)) + 1, "000")
End If
End Sub

This doesn’t work properly because when I move to a new record and I type a
couple of characters the combobox writes the last entered value but when I
press the Up Arrow I get an the error “Invalid use of Null†in other words
the value of the combobox is null.
When I move to any but the new record the combo, as I type, gives me the
last value but when I press Up Arrow I get the previous stored value +1 and
the cursor jumps to the last field of the record.
Do you know how the erratic function can be resolved?

Thank you,

GL
 
GL,

Well, originally when we discussed this, you were talking about clicking
a button to increment the Code, and this would be ok because as soon as
the focus moves to the command button, the entry of the combobox's value
is complete.

One approach that may work here is to change the Row Source of your
combobox, so the numbers are already incremented, and that means you
wouldn't need to use any key-press or button. Try it like this...

SELECT Left([ItemCode],Len([ItemCode])-3) &
Format(Val(Right([ItemCode],3))+1,"000")
FROM Items
ORDER BY Items.ItemCode DESC;
 
Back
Top