VB language for module

[Guest_imported]

I am a novice and have created a table with employees last name, first name and ID number. I want to type in thier id number and have the fields for thier names fill in automatically. I have gone to the form and to the properties section for the ID number. In the after update box of the event field I typed in:

Private Sub CCN_AfterUpdate()
Dim varFIRSTNAME, varLASTNAME As Variant
varFIRSTNAME = DLookup("FIRSTNAME", "Employee List", "CCN =[CCN]&quot
varLASTNAME = DLookup("LASTNAME", "Employee List", "CCN =[CCN]&quot
If (Not IsNull(varFIRSTNAME)) Then Me![FIRSTNAME] = varFIRSTNAME
If (Not IsNull(varLASTNAME)) Then Me![LASTNAME] = varLASTNAME

End Sub

When I type in the ID number, it populates the form with the improper information.

 

[lastout]

I don't think DLookup is the right choice since that's mostly intended for getting values from another table, not the same table that the form is using. Try making your ID field a combobox and make sure to set the Auto Expand property to no and limit the number of rows to 1.

Then type the following code behind the AfterUpdate event property for the ID combobox.

Private Sub comboID_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![comboID], 5))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

 

[jimmythegeek]

Another option in this small scenario is to have a multiple column combo box. Hide (width = 0) columns 1 and 2 (0 based). And on the AfterUpdate Event, put this code:


Me.FirstName = Me.comboID.Column(1)
Me.LastName = Me.comboID.Column(2)


Always more than one way to skin a cat, that's what makes Access appealing.
Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[scriverb]

Just to make sure, is your form an UNBOUND form? Have you created it with no Record source for the form? Because if this is NOT the case, the code listed above will cause you problems. It will populate the LastName and FirstName of the current record on the form with the First and Last Names of the Customer ID that you are searching for, thus creating problems with the integrity of your data. ID and last and first names not matched correctly.

If your form is BOUND there are other ways to select and have the form populate with that record. Just respond back as to situation and if need we can provide you with other options.

Bob scriver

 

[Guest_imported]

Here is a solution using ADO. I am using Office XP on a windows 2000 maching with the following references for VBA:
Visual Basic For Applications
Microsoft Access 10.0 Object Library
Microsoft Office 10.0 Object Library
Microsoft ActiveX Data Objects 2.5 Library
Microsoft ActiveX Data Objects Recordset 2.7 Library

You can use older references than those listed and the example should still work. The main thing is making sure the ADO references are there.

Option Compare Database
Option Explicit

Dim rsEmpData As ADODB.Recordset
Dim cnnData As ADODB.Connection
Private Sub Form_Load()
Set cnnData = CurrentProject.Connection
Set rsEmpData = New ADODB.Recordset
rsEmpData.Open "tblEmployeeData", _
cnnData, adOpenDynamic, adLockOptimistic
End Sub

Private Sub Form_Unload(Cancel As Integer)
rsEmpData.Close
End Sub

Private Sub txtIdNumber_KeyUp(KeyCode As Integer, _
Shift As Integer)
txtLastName = ""
txtFirstName = ""
txtLastName.BackColor = 255 'red
txtFirstName.BackColor = 255 'red
'if there is data to check
If txtIdNumber.Text <> &quot;&quot; Then
With rsEmpData
.MoveFirst
.Find &quot;id = &quot; & txtIdNumber.Text
'if the recordset is eof then
'the id was not found
If .EOF = False Then
txtLastName = !lastname
txtFirstName = !firstname
txtLastName.BackColor = 16777215 'white
txtFirstName.BackColor = 16777215 'white
End If
End With
End If
End Sub