Form update

[piko1981]

Using Access 2003 and Windows XP

The form displays data about an employee (Their SSN, Name, etc). It also displays what company that employee belongs to in a combobox (Ex A204-04,C204-04), along with how many people are assigned to the company (Assigned to A204-04, Assigned to C204-04)

For example purposes, employees 1,2, and 3 are assigned to companyNum A204-04. If in the first record (Employee 1), I change the company to C204-04, I want to subtract 1 from assigned to A204-04, and add 1 to C204-04.

Right now I am using the On Change property for the combo box to add 1 to the destination company. It adds correctly, however the combo box is then "locked" I can click on it, and the options are listed, but it won't let me choose another one.

Also I'm trying to figure out how to decrement the starting assigned value. I've tried On Enter, On Click, On Got Focus, but for all of these, as soon as the assigned value changes, the combobox is "locked" again.

I can go to the next record (Employee 2), and it will allow the combobox to be used again, until on of the assigned values is changed. Does it have something to do with saving the form? I tried running a macro to save the form when the vaue in assigned changes, but that didn't work either. Any help would be appreciated.
Piko

[Rabbit]

1) Are you storing these totals in your table? It's better if you don't do that.

2) You say you're using code but we can't help you unless you post the code so we can see if anything's wrong with it.

[piko1981]

I know storing totals isn't the best way to do things.... but if all else fails I will explain the situation better to try and emphasize what I'm doing....

Here's the code for when you click on the companyNum combobox
Private Sub companyNum_Enter()
Dim subtractAssign As String
subtractAssign = Left(companyNum, 1)
Select Case subtractAssign
Case "A"
Alpha_Assigned = Alpha_Assigned - 1
Case "C"
Charlie_Assigned = Charlie_Assigned - 1
End Select
End Sub


Secondly the code for after update.... Note.... the record changes before the update happens, so I put the add assigned here

Private Sub companyNum_Change()
Dim subtractAssign As String
Me.Requery
subtractAssign = Left(companyNum, 1)
Select Case subtractAssign
Case "A"
Alpha_Assigned = Alpha_Assigned + 1
Case "C"
Charlie_Assigned = Charlie_Assigned + 1
End Select
End Sub

Let me know what u think

[piko1981]

I've only done the code for Alpha and Charlie so far just for testing purposes, the final would have HHSB, A, B, C

[piko1981]

I also have anoth post..... question about a table which further descrbies why I have the authorized and assigned numbers all in the same table, please read that one as well. Thanks,
Piko

[NeoPa]

You'll see Mary issues similar advice there ;)
(Question about a Table)