Field Autofill Problem

[bloukopkoggelmander]

All

I am new to this site and hopefully would have a good time here.

I am new to Microsoft Access and VBA and am currently involved in a project to create a new database for one of our company departments. The problem I am having is as follows( ...I have spent about 2 weeks on this now and is about 5 seconds away from going insane....) :

I have created a new form with a combo box that looks up Last Name and First Name from a employee details table. There are also fields in this table for other details like address,sex,contact number etc. What I am trying to accomplish is to select a first and last name from the combo box on the form and once selected , I want it to autofill all the other text boxes on the form with the relevant personal details of the employee.

I just cannot find any workmethod anywhere which explains how to acomplish this.

I hope anyone will be able to guide a novice like me in the right direction.

Thanks in advance

Bloukopkoggelmander

[PEB]

Hi

Here in the forum there is a lot of posts about this!

You need to use a little VB in the after update event in your combo box

Then type

Me[FirstName]=Me![Combobox].column(2)

This means that your field FirstName will be updated with the value of the column number 3 in your combo box!

:)

[bloukopkoggelmander]

Hi there

Thanks for that man. Will give it a go . Fingers crossed it will work.

Thanks

[ADezii]

All

I am new to this site and hopefully would have a good time here.

I am new to Microsoft Access and VBA and am currently involved in a project to create a new database for one of our company departments. The problem I am having is as follows( ...I have spent about 2 weeks on this now and is about 5 seconds away from going insane....) :

I have created a new form with a combo box that looks up Last Name and First Name from a employee details table. There are also fields in this table for other details like address,sex,contact number etc. What I am trying to accomplish is to select a first and last name from the combo box on the form and once selected , I want it to autofill all the other text boxes on the form with the relevant personal details of the employee.

I just cannot find any workmethod anywhere which explains how to acomplish this.

I hope anyone will be able to guide a novice like me in the right direction.

Thanks in advance

Bloukopkoggelmander

In you are going to AutoFill a Field that is not specifically referenced in the Combo Box based on the First and Last names, then you can use the
DLookup() Function with this basic syntax:

'Looking for an Address based on the First and Last names in tblEmployee and
'shown in the Combo Box

'In the AfterUpdate Event of the Combo Box
txtAddress = DLookup("[Address]", "tblEmployee", "[First]='" & Me![cboName].
Column(0) &"' AND " & "[Last]='" & Me![cboName].Column(1) & "'")

[bloukopkoggelmander]

Hi there

I have had a look at the code example you have written(thank you for the effort).

I have tried changing the reference for the combobox to textbox as the fields that will automatically be populated with data once the username is selected from a combobox will be textboxes. This did not work unfortunately.

Here is a explanation of what I have :

Tablename : Employees
Field name 1 : Employeenumber
Field name 2 : EmployeeFirstName
Field name 3 : EmployeeLastName
Field name 4 : AddressLine1
Field name 5 : AddressLine2
Field name 6 : AddressLine3
Field name 7 : HomeTelephone

On the form I have a ComboBox which dispalys the username(This is done via an expresison which pulls together Filedname 1 & Fieldname 2. ) The users is then suppose to select a name from the dropdown list.

Once the name has been selected, the rest of the Textboxes which corresponds to Fieldnames 1,4,5,6,7 should autopopulate with the data corresponding to the username selected.

Hope this makes more sense of what I want to achieve.

Thanks

[PEB]

So my frien,

In your combobox list add the respective columns that you want to get information about

When they are as columns, in the column property of your combobox modify the number of columns... By default it's 1

If you want that the others columns are invisible in your combobox so assign to them a width = 0

As bound column to your combo box assign the number of co.umn that should be introduced as value in your form field...

And then follow the instructions to reference your respective fields

I repeat:
Imagine you have a Field in your Form named : [EmplyeeName] and this is the second column in your combo box to populate it in the after update event procedure type

Me![EmplyeeName]=Me![Combocvv1].Column(1)

The enumeration of columns begin from 0

:)

[bloukopkoggelmander]

Peb, thanks for your reply.

Appreciate you getting back to me. I have manged to sort this in the end, but I will try your suggestion as well ( I can then have a couple of ways doing it in future.).

Here is what I did :

- I created a new query on the table I want to get the data from.
- I then wrote an expression for this query to join the Lastname and Firstname fields as follows:

- FullName: Trim(Tablename.Fieldname for surname) & " , " & Trim([Fieldname for forename])

- I then also included all the fields in the query that will be autofilling the Textboxes I have created on the form.

- I then changed the RecordSource on the form properties to use the new created query .

- For the full name as above, I used a ComboBox and then set the properties for that ComboBox's control source to use the expression I created as above. I then set the following code for the AfterUpdate as follows :

Private Sub (Your combo box name)_AfterUpdate()
Dim rs As Object

Set rs = Me.Recordset.Clone
Dim Test As String
Test = "[The name of the expresion to combine first and last name] = '" & Me![Your combo box name] & "'"
rs.FindFirst Test
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

- I then created all the nessecary TextBoxes I needed and set each individual TextBox's control source properties to point to the corresponding field in the expression above.(This could be selected from the field names presented to you when you click on the dropdown menu for the source).


Presto!!

Now when I select a username, the rest of the textboxes automatically updates with the user's details.

So to all who have helped me, thank you very much.