Automatically changing a Lookup field when data is entered

[June7]

The query I provided earlier will show if a sim has been associated with an ASEID. Use it as RecordSource for a report.

It will be done a lot faster if you abandon trying to maintain that Status field and instead calculate the status as I described.

[WayneSteenkamp]

ok. where do we start? we'll give your way ago

[June7]

Follow the instructions I have already given in earlier posts. Like No. 40. Item 6 shows a calc you can use on form to display "Assigned" or "Unassigned". And No. 42 that suggests Conditional Formatting. And No. 36 that shows the query to list all the Sims and calculate their status.

[WayneSteenkamp]

fair enough. Are those the only steps I need to take or is there more?

[June7]

If code still in place for the UPDATE actions can eliminate. Remove the Satus field from table. I don't know of anything else.

[WayneSteenkamp]

Ok, I've done the following:
Deleted the SimStatus in the Sim_Serials table
Deleted the Update Query
Deleted the Subform in the ASE_Units form

created the new select query
I've put the following code in the SimStatus box (control source):
IIf(IsNull(SimSerialNumber), "Unassigned", "Assigned") and that works.

The only problem now is that when I enter a simserialnumber - it only goes into the ASE_Units table and not the Sim_serial table. Need it to go into both tables. Thats why I had a subform
Cause Now if i replace the simserialnumber with another..It just overwrites the previous Sim. So theres no way of tracking the previous, now unassigned sim. it disappears

To add to that..How do i track my 'Cancelled' sims?

[June7]

A SIM either is or is not associated with an ASE_Unit, right? What does 'cancelled' mean?

Deleted which subform? The not visible SIM_Serials_All_Subform?

You state: "The only problem now is that when I enter a simserialnumber - it only goes into the ASE_Units table and not the Sim_serial table. Need it to go into both tables. Thats why I had a subform" Which subform - the not visible SIM_Serials_All_Subform? How did having that subform enable saving the SimSerialNumber into SIM_Serials?

A SIM should only be selected for associating with an ASE_Unit if the SIM is already in the Sim_Serials table. I would use a combobox on ASE_Unit_form to select SIM. Then if SIM not already in list, use the NotInList event of the combobox to add the new SIM http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

If you want a history of all SIMs ever assigned to each ASE_Unit, that is another issue and that requirement was not clear to me. Is that what the SimSerial_RepairTracking table is supposed to document?

The Repair_History subform currently on the ASE_Units_form is a history of repairs to the ASE_Unit, right? There is no field in this history table for the SIM number. Is the SIM number in place during that repair important to know?

[WayneSteenkamp]

Hey
Yes the Sim is associated with an ASE Unit. The cancelled part is for the Sim.This is done manually by the dropbox. It's mainly logged in the SimSerial_RepairTracking. - this is when a Sim is damaged and can't be used again.

The Subform i'm referring to is the hidden Sim_Serials_All_Subform. It copied the SimSerialNumber field and when the record was put through on the ASE form, it copied the SimSerialNumber across to the SIM SERIALS table. That was the only way i could get the SIMSERIALNUMBER into both tables.
The user wants to refrain from entering the Sim twice if possible. If a Sim comes with the ASE unit - We can enter both into the ASE_Units form and it'll copy the sim across into the Sim table.
If the sim is on its own - then the Sim_serial table gets used to enter the Sim.

Any of this makes sense?

The original DB i sent over just basically needs to copy the Sim from the ASE unit form into the Sim Serial table. And the updating of the Sim status.

[WayneSteenkamp]

Hey June.
I know we are looking at doing things your way etc. But i'm really running out of time. And I do appreciate all your help you've given me.

Attached is the older version before all the changes etc.
I'm thinking since this one is so close to working. All it needs is an 'update" in the SimStatus of the Sim_Serials table then i'm done. i.e when a sim is taken out of an ASE unit - it becomes "unassigned" in the Form. Which is perfect. But it doesnt update the Sim table?

Think you could help me complete it today?

[June7]

Did we ever discuss what event should trigger the UPDATE? Is it the entry of a SimSerialNumber typed into the SimSerialNumber textbox? What if user types a SIM that is not already in Sim_Serials - how should that be handled? I think I did say this will require two UPDATE actions, one to change the status of the SIM that was associated and one to change status of the newly entered SIM. I am assuming this form will be used to change the SIM associated with an ASE. Also, if this is an entirely new SIM, it is not an UPDATE that is involved, it is an INSERT action.

So exactly what behaviors will the ASU_Units_form be allowed - will it be used to edit existing records or only add new records?

And nothing is keeping a history of all SIMs ever assigned to an ASE.

From this, can you understand why I find the UPDATE/INSERT approach so uncomfortable? Not impossible, just complicated.

[WayneSteenkamp]

Yeah i know its complicated :/
The ASE_Units form will be used for editing and adding records.

The status did change automatically in the table in the earlier versions i had without updates. Maybe its not working now because I've added so much to it making it a lot more complex. not sure.

[WayneSteenkamp]

Hey. Just a shot in the dark. But that Select query you mentioned before:
SELECT Sim_Serials.SimSerialNumber, IIf(IsNull([ASEID]),"Unassigned","Assigned") AS Status, ASE_Units.ASEID
FROM Sim_Serials LEFT JOIN ASE_Units ON Sim_Serials.SimSerialNumber = ASE_Units.SimSerialNumber;

That would run the "correct" result of the SIM status. Then just run reports off that?
That would solve all our headaches wouldnt it? Though doesnt solve the cancelled status.
It would recognise it as assigned wouldnt it? hmmm

[June7]

Another complication I didn't think of before. What if user enters a Sim that is already associated with another ASE_Unit?


This query handles the Cancelled status by including the SimSerial_RepairTracking table in a nested subquery:

SELECT Sim_Serials.SimSerialNumber, Nz([IsCancelled],IIf(IsNull([ASEID]),"Unassigned","Assigned")) AS Status
FROM (SELECT SimSerial As IsCancelled FROM SimSerial_RepairTracking WHERE Not IsNull([Cancellation Date]) GROUP BY SimSerial) AS Q1 RIGHT JOIN (Sim_Serials LEFT JOIN ASE_Units ON Sim_Serials.SimSerialNumber = ASE_Units.SimSerialNumber) ON Q1.IsCancelled = Sim_Serials.SimSerialNumber;

[WayneSteenkamp]

You cant have more than one Sim attached to a Unit. A error message comes up saying you can't have duplicates etc. So it stops you. S o dont have to worry about that problem

[WayneSteenkamp]

If a sim already exists. An error message will come up saying it cant have duplicates etc. So it stops you. So that complication you dont have to worry about.

The subquery doesnt work.
Displays the Sim number instead of the status "Cancelled"