Autofilling a textbox and entering data in a textbox.

  • Thread starter Thread starter pawojczak
  • Start date Start date
P

pawojczak

I am trying to have a text box (unit) autofill after I enter data in another
text box (docu nr). Is there any suggestions as to how I can do this?

The docu nr text box contains a a six character code that refers to a unit.
This six character code is the primary key for the unit.
After I type in the docu nr I want the unit to be displayed in the unit text
box.

docu nr format is >AAAAAA-1111-1111
unit format is text

docu nr is stored in a table named tblDocuNr

unit is stored in a table named tblUnitChoices

I have been attempting to write a VBA code in the afterupdate portion of the
docu nr text box on my form, but have not perfected it. Any Help would be
appreciated, thanks in advance.

The AAAAAA portion of docu nr refers to a unit.
 
First, please see:

http://mvps.org/access/tencommandments.htm

Pay special attention to commandment #3. This will save you from massive
headaches later on.

You didn't say, but it looks like you have an updatable query for the form
recordsource. Ant that the two text boxes are bound to fields in the
recordset.

Putting the "AAAAAA" from the document number into the "unit" control is easy:
'-------------------------------------------
Private Sub docu_nr_AfterUpdate()
Me.unit = Left$(Trim(Me.[docu nr]), 6)
End Sub
'-------------------------------------------

But you also *must* make sure it is a valid. So you need some validation
code also:

'-------------------------------------------
Private Sub docu_nr_BeforeUpdate(Cancel As Integer)
Dim docunr As String
Dim L1 As Integer 'length of docu nr
Dim H1 As Integer 'hyphen postion
Dim H2 As Integer 'hyphen postion

'docu nr format is >AAAAAA-1111-1111

'get length - must be 16
L1 = Len(Trim(Me.[docu nr]))
'get 1st hyphen position - must be 7
H1 = InStr(1, Me.[docu nr], "-")
'get 2nd hyphen position - must be 12
H2 = InStr(H1 + 1, Me.[docu nr], "-")

'do checks
If Not (H1 = 7 And H2 = 12 And L1 = 16) Then
' something not right
'change the message box message to what you want
MsgBox "Invalid document number format"
Cancel = True
End If

End Sub
'-------------------------------------------

HTH
 
Back
Top