Automatically changing a Lookup field when data is entered

[WayneSteenkamp]

this in the SimSerialNumber textbox or the SimStatus in the ASE_Units form?

[WayneSteenkamp]

I put it in the SimSerialNumber.
It gets to a point where it asks if you want to add the new sim.

get an error from here. "destination fields not the same"
DEBUG
CurrentDb.Execute "INSERT INTO Sim_Serials VALUES('" & Me.SimSerialNumber & "')"

Not sure which name to change

[June7]

Is SimStatus field still in Sim_Serials? Either remove it or modify code to:
CurrentDb.Execute "INSERT INTO Sim_Serials(SimSerialNumber) VALUES('" & Me.SimSerialNumber & "')"

[WayneSteenkamp]

Ignore the spreadsheet. That was imported in the tables ages ago. Dont need the sheet anymore. All the data imported from the spreadsheet gets updated within the database.
Basically what we're trying to do now is divert from using the Simstatus in the SImSerials Table. Using Queries instead.
The SimStatus in the ASE_Units form and the Sim Serials form seems to be running its outputs from the sim table still.
So basically i want the Sim status to run from the Queries.

[WayneSteenkamp]

Is SimStatus field still in Sim_Serials? Either remove it or modify code to:
CurrentDb.Execute "INSERT INTO Sim_Serials(SimSerialNumber) VALUES('" & Me.SimSerialNumber & "')"

I tried removing it and got an error in the ASE form. Couldnt find the reason


Edit: Ignore above. Thought you meant in the Sim table

[WayneSteenkamp]

The insert code works well. Only problem is if you want to modify the SimSerialNumber i.e replace it or remove it. The sims stay as Assigned

Edit: Actually it does work. Keep looking at the Sim Table and not the query from the reports
Only wierd thing is if you delete the SimNumber first, it wants to ADD the BLANK field

[June7]

Try this:

If Me.SimSerialNumber & "" <> "" Then
If IsNull(DLookup("SimSerialNumber", "Sim_Serials", "SimSerialNumber='" & Me.SimSerialNumber & "'")) Then
...

[WayneSteenkamp]

Try this:

If Me.SimSerialNumber & "" <> "" Then
If IsNull(DLookup("SimSerialNumber", "Sim_Serials", "SimSerialNumber='" & Me.SimSerialNumber & "'")) Then
...

That code seems to add the simserialnumber but doesnt bring up the message box anymore. Seems to have solved the remove problem

[WayneSteenkamp]

Nope. another problem:
when i want to add a SimNumber to a ASE that had a previous Sim removed. Got this error:
The macro or function set to the before update or valiadation rule property for this field is preventing microsoft access from saving the data in the field

Debug:
Me.SimSerialNumber = Null

[June7]

Use Undo instead. This all works for me.

If Me.SimSerialNumber & "" <> "" Then
    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.Undo
        End If
    End If
End If

[WayneSteenkamp]

Think thats worked.
I still want a simstatus to come up showing the status if possible including the cancelled parts.
I need to refer to the query again somehow dont i?

And then I think we may be done. untill i have a good check with the system

[WayneSteenkamp]

Use Undo instead. This all works for me.

If Me.SimSerialNumber & "" <> "" Then
    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.Undo
        End If
    End If
End If

Nope, Its not copying the Sim into the Sim Serials table anymore.
tried adding this code to it but didnt work either:
CurrentDb.Execute "INSERT INTO Sim_Serials(SimSerialNumber) VALUES('" & Me.SimSerialNumber & "')"


Edit:
Sortof working. need to open and close the ASE form twice before the message box comes up asking to save the new number. "/

[WayneSteenkamp]

I've put it back in the After update and works fine.

Able to help me with the Status field now?
I want it to show all 3 status including cancelled.

[June7]

See Post # 69.

[WayneSteenkamp]

yeah i tried that. got a "#Error"
Have I got a field name wrong? I changed the queryname to the name i have mine.

Ive made a new textbox for it on the ASE_form