Auto fill only on current record

[SaraMegan]

Hi! Another question from the resident idiot.

I'm working with a form that I'm using to edit employee data. It is built from a query which uses the following fields:

TD# (primary key, employee ID number)
Last (last name of employee)
First (first name)
PositionNo (Unique number classifying all of their job's specs.)

Thanks to RichMorrison, I was able to use a combo box that displays some of the specifications for the position (title, office, supervisor, etc). I put unbound text boxes on the form, and used an AfterUpdate event to show these specifications. The text boxes were enabled and locked so that they couldn't be changed.

Problem is, the information that is put into these fields is displayed for every record. Any idea why this is/how to fix it?

Here is my code:

Private Sub PositionNo_AfterUpdate()
Me!title1 = Me![PositionNo].Column(1)
Me!CostCenter1 = Me![PositionNo].Column(2)
Me!Office1 = Me![PositionNo].Column(3)
Me!Supervisor1 = Me![PositionNo].Column(4)
End Sub

Thanks in advance.

(By the way, I'm not good at code, so you may have to use short, simple words.)

--Sara

 

[Rich]

Why use code at all? just set the control source of the unbound text boxes to the relevant combos
ie.=[PositionNo].Column(1)

 

[SaraMegan]

Thanks, Rich! That's beautiful!

--Sara

 

[RichMorrison]

Sara,

Well it sounds like you want to change the general approach here.

This assumes you have one "person" table and it contains a field PositionNo. Then you have a second "postion" table where PositionNo is the primary key. The position table contains title, office, etc.

In that case you want to change the query so it joins Person.[PositionNo] to Position.[PositionNo]. Then select title, office etc from the position table. Now your query returns all the relevant fields for a person.

If you want to change PositionNo on the form, then you would Requery to see the new title, office, etc.

RichM

 

[SaraMegan]

RichM,

I'm a little confused. I have a tblPositions, with PositionNo as a primary key. I have a tblEmployees, which has an ID number (TDNo) as its primary key and has a PositionNo field that links to tblPositions, as you said.

When I created the query for my combo box, though, I had to leave out the tblEmployees because unfilled positions would not show up as options in my combo box.

I've used Rich's solution, but now I can't change the employee's position number in the form, as it says it creates duplicates in the primary key.

Any thoughts about this?

Thanks in advance.

--Sara

 

[RichMorrison]

You wrote
<<
I'm a little confused. I have a tblPositions, with PositionNo as a primary key. I have a tblEmployees, which has an ID number (TDNo) as its primary key and has a PositionNo field that links to tblPositions, as you said.

When I created the query for my combo box, though, I had to leave out the tblEmployees because unfilled positions would not show up as options in my combo box.
>>

Since I don't remember the original question, let's start from scratch.

What is the purpose of the form you have made ?
What is the record source of the form ?
What is the record source of the combo box ?

It sounds like the form is some kind of entry/update for tblEmployees and the combo box is used to select a possible position for an employee.

===============

In that case, the Record Source of the form should be a query that joins Employees to Positions via PositionNo. So PositionNo is the primary key of tblPosition and also a foreign key stored in tblEmployee. The query returns the fields you want from tblEmployee AND the title, etc from tblPosition.

This means the form contains plain old text boxes for title, office, etc.

================

The combo box would use tblPosition for a RecordSource. The "bound column" of the combo box would whatever column is the primary key of tblPosition, probably column 1. The combo box control itself is the "Control Source" of the PositionNo field in tblEmployee.

When you want to change an employee's position, you just select a new value from the combo box. This changes the PositionNo field in tblEmployee. You create an AfterUpdate event for the combo box that contains a Requery command. That gets the new title, office, etc and displays the values on the form.

=================

Off topic.

What is your "art" ?
Is the name of that town for real ?

RichM

 

[SaraMegan]

RichM,

Thanks for your prompt reply!

Since I'm only a few minutes from leaving, I'll just address the important stuff, and leave the rest until Monday:

On-Topic:

How would I write the code for the requery?

Off-Topic:

I've just graduated college with a theatre degree. I plan to save money (why I'm doing this ******* job...) and then move to Chicago and try to become an actor. I also do some clowning, juggling (but only a little), directing, stage managing, and assorted technical work. I'm starting to put together a one-woman show.

Yes, the name of my town is for real. It's from the Native Americans. I don't know what it means. If you're looking on a map of NH, it's just west of Concord, the state capital.

Anyway, I'm sure I'll post back Monday with more issues. it wouldn't be a Monday without issues.

--Sara

 

[RichMorrison]

You wrote
<<
How would I write the code for the requery?
>>

Go to the Properties window for the combo box. Go to Events and select After Update <Event Procedure>

Access generates an AfterUpdate Sub procedure. You type in "Me.Requery"

RichM

 

[SaraMegan]

I have the combo box based on a separate query - could that be what is causing me trouble?

Problem is, if I use the query I am using for the form, then I have positions without employees. I want these to be an option in the combo box, but the query I'm using for the form doesn't show positions that don't have a corresponding TD#.

The trouble I'm having is that when I try to change the position number (using my second query) it tells me that the record can't be saved because it creates duplicates in the primary key.

Thanks again.

--Sara

 

[RichMorrison]

you wrote
<<
I have the combo box based on a separate query - could that be what is causing me trouble?
>>
Yes. Use one query that links employees to positions. In this query you want a left join of employee to position. That will handle cases where the employee does not yet have a position number.

<<
Problem is, if I use the query I am using for the form, then I have positions without employees. I want these to be an option in the combo box, but the query I'm using for the form doesn't show positions that don't have a corresponding TD#.
>>
The combo box itself should be based on the position table.

<<
The trouble I'm having is that when I try to change the position number (using my second query) it tells me that the record can't be saved because it creates duplicates in the primary key.
>>
Don't quite understand, but if the combo is based on position table and "bound" to PositionNo in employee table then I don't think you will get any duplicate key errors.

Happy Monday,
RichM

 

[SaraMegan]

Okay, I re-worked the whole query, but it wouldn't let me do a left join, saying I had an ambiguous outer join. so I left it as an inner join, and that works fine.

I assigned the combo box to tblPositions.

I'm still getting this error: (Runtime Error 3022)

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

When I test the combo box, I select a position# that I know nobody else has.

If I click "debug" it highlites the me.requery line in my after update.

If you need me to, I can describe the query more in detail, as it uses six tables. Do you think the duplicate in primary key problem could be coming from a different table? Every field in tblPositions (besides the pk) is a foriegn key to another table. Could that be a problem?

Again, thanks. I hope your Monday is going well.

--Sara

 

[RichMorrison]

you wrote
<<
I'm still getting this error: (Runtime Error 3022)

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."
>>

My best guess is this: you have selected tblPosition as the Record Source of the combo box but you have NOT set the Control Source of the combo box to tblEmployee.[PositionNo]

Second guess; in tblEmployee did you index PositionNo ? If yes did you specifiy No Duplicates ? If so, then don't.

it's all ifs and elses,
RichM

 

[SaraMegan]

Thanks, RichM!

It was the second one. I didn't even think of that, but as soon as I read it I felt like a dummy.

Now I can start being productive.

--Sara