Auto fill fields in a form 1

[PlsHlpKat]

I have a Help Desk log form that I created in Access using two separate tables. The first table includes user names and info. The second part of the form comes from a separate table that shows problems/ solutions etc. I would like to have a drop down menu for the users Last name which when selected would trigger the other user information i.e. fist name, phone extension, department etc. to fill in automatically. I have not been able to get this to work right now I have to drop down and select for every field.

Any help would be appriciated!

Kat

 

[jrbarnett]

In the AfterUpdate event of the combobox, use syntax like:

Me.txtFirstname = DLookup ("Firstname", "Users", "Lastname='" & Me!txtLastname & "'&quot

John

 

[PlsHlpKat]

Sorry to open this back up but I have been trying to figure this out on my own.


When I enter:
=[Me].[txtFirstname]=DLookUp "Firstname","Users","Lastname='" & [Me]![txtLastname] & "'&quot

in the AfterUpdate on my Lastname combo box I get the following error:

The expression After Update you entered as the event property setting produced the following error. The object doesn’t contain the Automation object 'Me'.

 

[jrbarnett]

Try and put this in the AfterUpdate event instead:

[Me].[txtFirstname] =DLookUp ("Firstname","Users","Lastname='" & [Me]![txtLastname] & "'&quot

This means: Set the txtFirstname textbox to the result of the DLookup of firstname field in the users table where the Lastname field of the table matches the txtlastname box.

Note that if there are two users with the same surname, it will return the first it finds.

John

 

[PlsHlpKat]

Thanks! But now I am getting

Microsoft Access can't find the macro 'Me].'

Am I doing something wrong?

Kat

 

[ccburn]

Have you tried multiple columns for your last name combo box?

 

[PlsHlpKat]

I'm not sure I know what you mean by multiple columns for your last name combo box? I am sorry I'm very new at this.

Thanks!!!!

 

[AlanKW]

I haven't had any luck using Me inside []'s What happens when you use
Me.txtFirstname

Me represents the current form.

the []'s are more common when referencing other forms or subforms. [Forms]![MyForm]![MySubform] blah blah blah....

 

[jrbarnett]

Try instead:

Me.txtFirstname =DLookUp ("Firstname","Users","Lastname='" & [Me]![txtLastname] & "'&quot

John

 

[PlsHlpKat]

I still get Microsoft Access can not find Macro Me.

Kat

 

[ccburn]

Just set your combo box up to select
lname, fname, phone, department from the USER table
thus you will have 4 columns display when you click the down arrow for the combo box. (You can hide these by reducing the column widths). I suggest you show the first name also since last names could possibly be duplicated. Access sees these columns as lname -->combo.column(0)
fname -->combo.column(1)
phone -->combo.column(2)
dept -->combo.column(3)

On the combo.afterUpdate (in VBA)
put the following..

Me![firstname_field_on_yourform]=combo.column(1)
Me![phone_field_on_yourform]=combo.column(2)
Me![dept_field_on_yourform]=combo.column(3)
etc.

As soon as the combo is changed you will see the new values populate throughout your form for the current record. I use this to speed up data entry.

Hope this helps.
-ccburn

 

[ccburn]

Hello Kat,

Did it work out for you?

ccburn.

 

[PlsHlpKat]

Sorry to just be getting back to you I had to move on to other things and I now found the time to work on this again. I set the lname combo box up to select
lname, fname, phone, department from the USER table. I am still getting an error on:

Private Sub Combo78_AfterUpdate()

Me![firstname_on_Work_Order] = combo.Column(1)

End Sub

It stops on combo and gives me -Compile Error: Variable not defined-

Any idea what I am doing wrong?

Thanks!
Kat

 

[ccburn]

OK, I see..your code should refence the name of the combo box. (looks like you are using 'Combo78').

--
Private Sub Combo78_AfterUpdate()

Me![firstname_on_Work_Order] = Combo78.Column(1)

End Sub
--


Secondly-
Go to the properties sheet for this combo box
Click the 'Format' tab
Make sure the column count is (in your case) =4
so you will have four columns-
lname -->combo.column(0)
fname -->combo.column(1)
phone -->combo.column(2)
dept -->combo.column(3)

now save and try it.

Hope This Helps
-ccburn

 

[PlsHlpKat]

Thank you for your help I've gotten it to work in a round about way now that you have explained how lname --combo.column(0),fname -->combo.column(1),etc. works I have a better understanding. So I just set the Control source for fname = combo88.column(1)etc.

Thanks so much for your help this is my first time building my own access database and you have been a great teacher.

Kat