Automatically changing a Lookup field when data is entered

[June7]

Sorry, should have made a cancelled record to test. This is better:



SELECT Sim_Serials.SimSerialNumber, Iif(Not IsNull(Q1.SimSerial), "Cancelled",IIf(IsNull([ASEID]),"Unassigned","Assigned")) AS Status
FROM (SELECT SimSerial 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.SimSerial = Sim_Serials.SimSerialNumber;

Those standard Access error messages are annoying. Should handle the issue to avoid those popups.

[WayneSteenkamp]

Sweet. That seems to work I must just remember that it needs the Cancel date in it else it wont work
so does this query:
SELECT Sim_Serials.SimSerialNumber, Iif(Not IsNull(Q1.SimSerial), "Cancelled",IIf(IsNull([ASEID]),"Unassigned","Assigned")) AS Status
FROM (SELECT SimSerial 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.SimSerial = Sim_Serials.SimSerialNumber;

replace this one or do i still keep it?
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;

And to show my Sim status for each I would need to make copies of these with the criteria in ** i.e *Assigned* to show up all my Assigned Sims?


What error messages are you referring to in particular?

[June7]

Never mind the error message comment. I was thinking about duplication of PK in Sim_Serials. Wires crossed. Hope the query solves issue. Much simpler than code. Certainly demonstrates the advantages of related records and the power of queries.

[WayneSteenkamp]

I've noticed that in the Sim_Serial Table. If a Sim isnt attached to an ASE Unit i.e removed from a Unit in the Ase_units form then the ASEID field is blank. (which it is). The Status should default to "Unassigned" however its staying as "Assigned :/
It's taking it from the Sim Table thats why.

Anyway we can sort that out? I do need the Dropdown box though to have the option to cancel the Sims

Headaches

[WayneSteenkamp]

If I make a text box like the one in Ase_Units_form where is SimNumber is missing then Unassign.
-in this case (in the SIm form) if ASEID is missing then Unassign.
then if can have a drop box with cancel on it?

would that work? or would it clash with anything

Or can we make the drop box change if a ASEID is present? but still be able to overide it by selecting the cancel option?

[June7]

No, don't keep the first query for the Sim status. That was before I was aware of the Cancelled condition.

I am confused by:

"And to show my Sim status for each I would need to make copies of these with the criteria in ** i.e *Assigned* to show up all my Assigned Sims?"

"I've noticed that in the Sim_Serial Table. If a Sim isnt attached to an ASE Unit i.e removed from a Unit in the Ase_units form then the ASEID field is blank. (which it is). The Status should default to "Unassigned" however its staying as "Assigned :/
It's taking it from the Sim Table thats why."

"I do need the Dropdown box though to have the option to cancel the Sims"

If you rely on the query the SimStatus field in Sim_Serials is irrelevant. The cancelled status is determined by an entry into SimSerial_RepairTracking. SIM Status is not 'selected' by the user, status is determined by query of records and calculation.

[WayneSteenkamp]

Yeah I know what you mean. It's fine.
What i want to do it just put a text box in SimSerials form where it looks up whether the sim is assigned/unassigned/cancelled. It looks to see if the ASEID is present - if so "Assigned", if not "Unassigned"

at the moment the code looks like this in the control source: IIf(IsNull(ASEIDlookup), "Unassigned", "Assigned")
how do i add the cancelled to it? Make it look in the Cancelled date field?

EDIT: Got the above working

To add to that - If I delete the SimStatus in the Sim_Serials Table (Since we dont need it anymore), the ASE_Units_form comes up with a message looking for it. I thought it might've been because theres a simstatus on a Sim_Serials_ALL_subform. I delete the field. But it's still looking for it?

[WayneSteenkamp]

Hey To show you what i mean:
If you open up the ASE_Units form and view the 1st ASE 111111
remove the Sim from it and open the sim_serials form. You'll notice the status for the removed Sim is still "Assigned" even if theres no ASE unit attached to it. (but the report query shows it as Unassigned)

What I was trying to do was remove the SimStatus combobox from the SimSerials form and create a textbox with the IIF function mentioned in my previous post. It worked fine. However the ASE_Unit form doesnt show up any cancelled sims. It will just "Assigned" it. It was getting the information from the dropbox before. Now that I've removed it..its not working properly.
I tried an IIF function for the SimStatus field on the ASE_Unit form and coulnt get it to work.
I wanted to remove the SimStatus from the SimSerials table but get errors in the ASE_Unit form as mention before.

Can all this be fixed or best leave it as is?

[June7]

Can use a DLookup in textbox to get the Sim status from the query:

=DLookUp("Status","Sim_Serials_Status","SimSerialN umber='" & [SimSerialNumber] & "'")

I removed the hidden Sim_Serials_All_Subform and don't get the prompt for missing field.

[WayneSteenkamp]

where does the Sim_Serials_Status come from?
also if i remove the Sim_Serials_All_Subform, it will stop populating/copying the simserialnumber across into the Sim table

[June7]

Sim_Serials_Status is name I gave to the status query I suggested to calculate status.

Sim numbers in Sim table are unique values. Adding a new Sim number by entry into the textbox of ASE_Units is an entirely different issue from calculating status. It is why I earlier suggested a combobox to select SIM to associate with ASE_Units. Restrict selection to SIMs listed by combobox. If user enters a new SIM not in list, use the NotInList event to record the new sim in Sim table. http://msdn.microsoft.com/en-us/libr...ffice.11).aspx
If you stick with the textbox, use other code to verify existence of SIM or if it is a new number and new record needed.

Both options would probably use an INSERT action to add the new SIM to table.

[WayneSteenkamp]

hmm. Havent a clue what to do now :/
The whole matter is getting complicated again. Change one thing..then need to change another :/

[June7]

Yes, but has been simplified from needing maybe two UPDATE actions and/or the INSERT, whichever the situation called for, to just maybe the INSERT action. The conventional approach with the NotInList is to open a form so user can add the new item and all its relevant info, such as a new customer. The record is saved, form closes, and the new record would then be available for selection on the first form. Since the info you would be saving is a single value - the SIM number - seems overkill to open form for its input. I would just run code in the NotInList event to confirm the user really wants to add new SIM and if yes, run INSERT to save it. If you stay with the textbox would need code to verify if SIM already exists or is new every time an entry is made in the textbox. A combobox offers some validation of the entry, especially with the AutoExpand property and LimitedToList properties activated and used in conjunction with NotInList event.

[WayneSteenkamp]

what method would you recommend? can it all be completed in 3 hours?

[June7]

In 3 hours, maybe.

With the textbox, try something like this in its BeforeUpdate event (I have not tested):

If IsNull(DLookup("SimSerialNumber", "Sim_Serials", "SimSerialNumber='" & Me.SimSerialNumber & "'")) Then
If MsgBox("This SIM is not in database. Do you want to add?", vbYesNo) = vbYes Then
CurrentDb.Execute "INSERT INTO Sim_Serials VALUES('" & Me.SimSerialNumber & "')"
Else
Cancel = True
Me.SimSerialNumber = Null
End If
End If