Autofill fields depending on another (1 Viewer)

mbhw99 · May 24, 2012

I realize this is posted quite often in this forum, but I haven't been able to find a solution to my particular problem.

I have 3 combo boxes filled with employee names and an employee ID #'s. They are populated by their own queries, and all the queries pull from tbl eidandname and have a similar structure but in different order:

Queries: "eid Query", "First Name Query", and "Last Name Query"
EID - number
First Name - text
Last Name - text

The EID is the only field guaranteed to not have a duplicate, as you can imagine...the only problem is that not everyone knows someone elses EID, just by name.

How can I autofill these by inputting from any of the combo boxes? I'm using Access '02.

Pat Hartman · May 25, 2012

Search fields should be unbound. The RecordSource for the form can reference the search fields. In the AfterUpdate event of each combo, requery the form:
Me.Requery

The RecordSource query:
Select ..
From ...
Where Eid = Forms!myform!SearchEID OR LastName = Forms!myform!SearchLastName OR FirstName = Forms!myform!SearchFirstName;

The bound column of each combo will always be EID so you will have a unique ID and only retrieve a single record.

MStef · May 25, 2012

You don't need 3 combo boxes. Put a combo box on the EID field.
Look at "DemoComboBoxA2000.mdb" (attachment, zip).
First column in combo is column 0.

mbhw99 · May 25, 2012

Pat Hartman said:
Search fields should be unbound. The RecordSource for the form can reference the search fields.

The form's recordsource is a different and unrelated table.

This method looks like what I'm looking for, if the form recordsource problem can be worked around.

MStef said:
You don't need 3 combo boxes. Put a combo box on the EID field.
Look at "DemoComboBoxA2000.mdb" (attachment, zip).
First column in combo is column 0.

Thank you for your help! I did take a look at your db. I might use this for another db that I manage.

Pat Hartman · May 25, 2012

What's the problem with the form's RecordSource?

mbhw99 · May 29, 2012

My form's recordsource is from "tbl occurance input". tbl eidandname is only for the 3 fields outlined in the pic.

Pat Hartman · May 29, 2012

Is there some reason you can't use a query that joins the two tables as the form's RecordSource?

mbhw99 · May 30, 2012

Very good. Once again a simple solution has escaped me. lol

One more (noob) question. The whole form is saved as 1 record (and as I look in my previous posts, I notice that I did not mention that. My apologies). Since these 3 fields are unbound, will they still be saved with the rest of the data?

Pat Hartman · May 31, 2012

No. If they are unbound, where would they be saved?

Going back to the beginning - search fields should be unbound. That means that your form probably needs two instances of each of the search fields. The first, usually in the header of the form, is unbound and used for searching. The second in the detail section of the form shows the data for the selected record and can be changed.

mbhw99 · Jun 11, 2012

Thanks for clearing that up. I'm having problems with my code, though. Could you please explain what is wrong?

Code:

SELECT Qry occurance input.EID, Qry occurance input.First Name, Qry occurance input.Last Name FROM Qry occurance input ORDER BY Qry occurance input.EID Where EIDS = Forms!frm occurance input!Search EID OR LNS = Forms!frm occurance input!Search Last Name OR FNS = Forms!frm occurance input!Search First Name;

EIDS, LNS, and FNS are the search fields I'm putting in the header...just renamed the field name.

Pat Hartman · Jun 11, 2012

For starters, your column and table names all include embedded spaces. This is poor practice since the names cannot be used by VBA but they can be used in most instances if you enclose them in square brackets.

Names should not include embedded spaces or any special characters such as # and %. You can separate words using CamelCase (MyPreference) or the_underscore (I_dont_like_it).

Autofill fields depending on another (1 Viewer)

mbhw99

Registered User.

Pat Hartman

Super Moderator

MStef

Registered User.

Attachments

mbhw99

Registered User.

Pat Hartman

Super Moderator

mbhw99

Registered User.

Attachments

Pat Hartman

Super Moderator

mbhw99

Registered User.

Pat Hartman

Super Moderator

mbhw99

Registered User.

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread