setting up Equipment data entry form 1

[THWatson]

Using Access 2000

I am having a brain cramp and can't quite get a data entry form, to keep track of equipment, properly designed.

The scenario is this...
1. A piece of equipment (CPU, Monitor, Printer etc.) is purchased. The details of the purchase and information about the piece of equipment is recorded in tblEquipment.
2. The piece of equipment is assigned to a Department. There are 56 departments. These are held in tblDepartments.
3. In the selected Department, the piece of equipment is assigned to a Work Station. Each Department can have up to 6 Work Stations. These are held in tblWorkStations.

What is the best way to design the data entry form for this scenario?

What I have tried is as follows...
I have put both a DeptID and WorkStationID in tblEquipment, and built the form using interdependent combo boxes for entering the appropriate Department and Work Station. The AfterUpdate event for cboDepartment requeries the cboWorkStation. This approach places the proper Department and Work Station data in tblEquipment. HOWEVER the Work Station combo boxes go blank when you cycle from one record to the next. Only the last Work Station entered continues to display.

How can I either (a)fix this so that the correct data continues to display, or (b) redesign the data entry form to get the proper results?

Thanks for a push in the right direction.

Tom

 

[Remou]

I think I am missing some point. Is the Workstation combo not bound to the underlying equipment table?

 

[THWatson]

Remou
Yes, that's the way I have it. But the cboWorkStation is set up to select only those Work Stations that are related to the Department selected in cboDepartments.

As I said in my initial post, the values selected in cboWorkStation are properly set in tblEquipment, but only the cboWorkStation values selected in the last record show in the form.

Tom

 

[Remou]

I asked, because when I set up a small test form, I did not get this problem. Here is the set-up.

[tt]Tables
tblDept
DeptID
Desc

tblEquipment
EquipID
DeptID
WorkID
Desc

tblWorkStation
WorkID
DeptID
Desc

Form
cboDept
Control Source: DeptID
Row Source: tblDept
Bound Column: 1 (DeptID)

cboWorkStation
Control Source: WorkID
Row Source: SELECT [tblWorkStation].[WorkID], [tblWorkStation].[Desc] FROM tblWorkStation WHERE ((([tblWorkStation].[DeptID])=[Forms]![frmEquipment]![cboDept]));
Bound Column: 1[/tt]

Private Sub cboDept_AfterUpdate()
Me.cboWorkStation.Requery
End Sub

 

[THWatson]

Remou
That's exactly the same setup as I have. And that's why I don't quite understand where I am going wrong.

Let me give you an example of what happens in the form...
Record1
Equipment - CPU
Department - Human Resources
Work Station - Director (one of 5 possible Work Stations)

Record2
Equipment - Monitor
Department - Plant Maintenance
Work Station - Supervisor (one of 3 possible Work Stations)

Record3
Equipment - Photocopier
Department - Sales
Work Station - Western (one of 6 possible Work Stations)

When the Work Station "Supervisor" is entered in Record2, the value "Director" disappears from Record1. When the Work Station "Western" is entered in Record3, the value "Supervisor" disappears from Record2...and the value "Director" is still absent from Record1.

Even though all of these values are properly entered in the tblEquipment.

Tom

 

[Remou]

Do you have (?):

Private Sub [b]Form_Current[/b]()
Me.cboWorkStation.Requery
End Sub

 

[THWatson]

Remou
Well, that did the trick. To be honest, I thought I had tried that before, without results...but just added that now and things are working fine.

Thanks so much!!!

Tom

 

[Remou]

I know the feeling, and am glad I could help.