Form Help - autocomplete?

[Diqs]

Hello everyone, I just have a quick question here and I hope someone can
point me in the right direction.

I have a table created for faculty, which contains the following 3 fields:
EmployeeID (Primary Key, unique for each employee), lastname, and firstname.

Is it possible to create a form that does the following? In the form, the
three fields mentioned above appear. The lastname and firstname fields in the
form of drop down boxes (Combo boxes) and the EmployeeID field has no options
of selection. Is it possible that, if you select someones last name.... that
access can look through the database of faculty for that last name and when
selected... can automatically fill out the firstname field and employee
number?

So far, the only luck i've been able to have is creating combo boxes and
when the form is created..... you can select from everyone's firstname and
lastname..... but thats about it :\

Any help is greatly appreciated.

Thank you and god bless.

Sadiq Motani

 

[Klatuu]

I would do this a bit differently.
I would not use combo boxes for the bound controls for the 3 fields, I would
use text boxes.
But, I would use an unbound combo box to do the search.

The problems with the method you are attempting are that when you change the
value of the last name combo, it is changing the value in the current record
which is probably not what you want to do, There can be multiple people with
the same last name, so just typing in the last name may not get the record
you want, and the only unique field is the employeeid is not a good field for
a user to try to understand. But, with my method, you can use the combo to
find exactly the right person and make that record the current record on your
form.

First, create a query for the combo's row source:
SELECT EmployeeID, lastname & ", " & first name AS FullName FROM tblEmployee;

Now, set the following properties for the combo:
Column Count 3
Bound Column 1
Column Widths 0"; 3" (The 0" hides the employee number, the 3" can be
whatever is necessary to display the last name, first name
Limit To List Yes

Now you use the combo's After Update event to search for the employee's
record and make it the current record:

With Me.RecordsetClone
.FindFirst "[EmployeeID] = " & Me.MyCombo
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

This line:
.FindFirst "[EmployeeID] = " & Me.MyCombo
Assumes EmployeeID is a numeric field. If it is text, it should be:
.FindFirst "[EmployeeID] = """ & Me.MyCombo & """"

 

[Diqs]

Thanks for the quick reply.

1. When I create the query for the combo's row source, i get this error. The
combo box is created for the lastname field
"Syntax error (missing operator) in query expression 'lastname & " , " &
first name'
Here's a link with a a screenshot of the error:
http://www.zone.at/memberpages/member/filebase_new/S/sadio/error_1.jpg

Now, I'm not sure if this code is supposed to be written when designing the
table or when designing the form itself (not sure if it makes a difference
though)!

2. When building the after update event, i am presented with 3 options:
Macrobuilder, CodeBuilder, and ExpressionBuilder. Which one do I use when
typing in the code?

I hope these aren't ' too dumb' questions, but this aspect of access I am
not too familiar with.

Thanks for all the help once again, really appreciate it.

I would do this a bit differently.
I would not use combo boxes for the bound controls for the 3 fields, I would
use text boxes.
But, I would use an unbound combo box to do the search.

The problems with the method you are attempting are that when you change the
value of the last name combo, it is changing the value in the current record
which is probably not what you want to do, There can be multiple people with
the same last name, so just typing in the last name may not get the record
you want, and the only unique field is the employeeid is not a good field for
a user to try to understand. But, with my method, you can use the combo to
find exactly the right person and make that record the current record on your
form.

First, create a query for the combo's row source:
SELECT EmployeeID, lastname & ", " & first name AS FullName FROM tblEmployee;

Now, set the following properties for the combo:
Column Count 3
Bound Column 1
Column Widths 0"; 3" (The 0" hides the employee number, the 3" can be
whatever is necessary to display the last name, first name
Limit To List Yes

Now you use the combo's After Update event to search for the employee's
record and make it the current record:

With Me.RecordsetClone
.FindFirst "[EmployeeID] = " & Me.MyCombo
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

This line:
.FindFirst "[EmployeeID] = " & Me.MyCombo
Assumes EmployeeID is a numeric field. If it is text, it should be:
.FindFirst "[EmployeeID] = """ & Me.MyCombo & """"




--
Dave Hargis, Microsoft Access MVP

Hello everyone, I just have a quick question here and I hope someone can
point me in the right direction.

I have a table created for faculty, which contains the following 3 fields:
EmployeeID (Primary Key, unique for each employee), lastname, and firstname.

Is it possible to create a form that does the following? In the form, the
three fields mentioned above appear. The lastname and firstname fields in the
form of drop down boxes (Combo boxes) and the EmployeeID field has no options
of selection. Is it possible that, if you select someones last name.... that
access can look through the database of faculty for that last name and when
selected... can automatically fill out the firstname field and employee
number?

So far, the only luck i've been able to have is creating combo boxes and
when the form is created..... you can select from everyone's firstname and
lastname..... but thats about it :\

Any help is greatly appreciated.

Thank you and god bless.

Sadiq Motani

 

[Klatuu]

See comments in line below
--
Dave Hargis, Microsoft Access MVP

Thanks for the quick reply.

1. When I create the query for the combo's row source, i get this error. The
combo box is created for the lastname field
"Syntax error (missing operator) in query expression 'lastname & " , " &
first name'
Here's a link with a a screenshot of the error:
http://www.zone.at/memberpages/member/filebase_new/S/sadio/error_1.jpg

That is not correct. You do not use a lookup field. Lookup fields should
really never be used. The field should carry it's own value. What I posted
was for creating a combo box on your form.

Now, I'm not sure if this code is supposed to be written when designing the
table or when designing the form itself (not sure if it makes a difference
though)!

It makes a very big difference. Do not do this in the table.

2. When building the after update event, i am presented with 3 options:
Macrobuilder, CodeBuilder, and ExpressionBuilder. Which one do I use when
typing in the code?
CodeBuilder

I hope these aren't ' too dumb' questions, but this aspect of access I am
not too familiar with.

Thanks for all the help once again, really appreciate it.

I would do this a bit differently.
I would not use combo boxes for the bound controls for the 3 fields, I would
use text boxes.
But, I would use an unbound combo box to do the search.

The problems with the method you are attempting are that when you change the
value of the last name combo, it is changing the value in the current record
which is probably not what you want to do, There can be multiple people with
the same last name, so just typing in the last name may not get the record
you want, and the only unique field is the employeeid is not a good field for
a user to try to understand. But, with my method, you can use the combo to
find exactly the right person and make that record the current record on your
form.

First, create a query for the combo's row source:
SELECT EmployeeID, lastname & ", " & first name AS FullName FROM tblEmployee;

Now, set the following properties for the combo:
Column Count 3
Bound Column 1
Column Widths 0"; 3" (The 0" hides the employee number, the 3" can be
whatever is necessary to display the last name, first name
Limit To List Yes

Now you use the combo's After Update event to search for the employee's
record and make it the current record:

With Me.RecordsetClone
.FindFirst "[EmployeeID] = " & Me.MyCombo
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

This line:
.FindFirst "[EmployeeID] = " & Me.MyCombo
Assumes EmployeeID is a numeric field. If it is text, it should be:
.FindFirst "[EmployeeID] = """ & Me.MyCombo & """"




--
Dave Hargis, Microsoft Access MVP

Hello everyone, I just have a quick question here and I hope someone can
point me in the right direction.

I have a table created for faculty, which contains the following 3 fields:
EmployeeID (Primary Key, unique for each employee), lastname, and firstname.

Is it possible to create a form that does the following? In the form, the
three fields mentioned above appear. The lastname and firstname fields in the
form of drop down boxes (Combo boxes) and the EmployeeID field has no options
of selection. Is it possible that, if you select someones last name.... that
access can look through the database of faculty for that last name and when
selected... can automatically fill out the firstname field and employee
number?

So far, the only luck i've been able to have is creating combo boxes and
when the form is created..... you can select from everyone's firstname and
lastname..... but thats about it :\

Any help is greatly appreciated.

Thank you and god bless.

Sadiq Motani

 

[Diqs]

Okay I tried your suggestions.

1. I am still getting that syntax error: "Syntax error (missing operator)
in query expression 'lastname & " , " & first name'

- When I ignore this error and attempt to use the form and try to select a
last name... from the combo box.... I am still getting that error as well.
- This occured even after the fact that I had created a form, and selected
the properties (in form design view) for the combo box for last name

I have a funny feeling I'm doing something that is very obviously wrong..
but that I am oblivious too!

- Sadiq

 

[Klatuu]

Your table should not have any lookup fields.
In your table design, firstname has a space in it, so it is actually first
name. You either need to change the field name in table design or put
brackets around it.

The query should look like this:
SELECT EmployeeID, lastname & ", " & [first name] AS FullName FROM
tblEmployee;

Not to worry, we will get it sorted out.

 

[Diqs]

Okay so some good news.

I was able to input the query and when I tried to run it, it ran without any
problems, so that's great!! The fact that it ran without any problems, I
assume all is okay on that front?

Now, when I use the codebuilder for the "after event" for the combo box for
last name...... i can paste the code in but when I run the form i get this
message.
Screenshot: http://zone.at/memberpages/member/filebase_new/S/sadio/error2.jpg

Of course, I do not see the record source property box anywhere when I click
the property sheet. :\

 

[Klatuu]

That is because the name of the table is different that what is in my code.
I did not know your table name, so I made one up. Just change the table name
from tblEmployee to Employee in the code.

 

[Diqs]

Okay I made the changes in your previous response and this is what has
happened when I run the form.

1. I click on the last-name field, which is a combo box. A promp appears
asking me to enter the first-name.

2. When I enter in a first name, a drop down list appears with names in the
database that are similiar/the same as the name I typed in.

3. If I select ANY name from that list, i get this error... and it goes to
the codebuilder page (Visual Basic):

Compile Error: Method or data member not found.... at which point it goes to
the code screen and the following is highlighted in yellow:
Private Sub lastname_AfterUpdate()