Automatically changing a Lookup field when data is entered

[WayneSteenkamp]

Hey,



When I enter a UnitId on the form followed by a Serialnumber. I have a button to click on that changes the status to 'Assigned' on my lookup field.
Private Sub Assign_Click()
[Sim_Serials_All_Subform].Form![SimStatus] = "Assigned"
End Sub

Which works quite fine if you do it manually with the button.
However the user wants to import a spreadsheet in - meaning they don't fancy clicking the button 2000 times
So is there a way of losing the button and doing it automatically as soon as the serialnumber is entered?

If any further info is needed..Let me know

Thanks

[June7]

Maybe. Need code in some event. Could be a button Click or AfterUpdate of textbox. The code would run an SQL update action that would change the status field of all records meeting certain criteria.

[WayneSteenkamp]

I used the button code in the Serialnumber field and it worked fine as an afterupdate.
however if you enter raw data into the table directly i.e import from a spreadsheet. The status doesnt change - stays the same as default "Unassigned". when a serialnumber is clearly visible in the field.


ok, what about an if function?
i.e if SerialNumber is Null then "unassign"
else if SerialNumber entered then "Assign"

Not sure how to put that in coding with the brackets etc.

But would that idea work?

[June7]

You mean an IIf in the UPDATE TO row under the SimStatus field of an UPDATE query?

IIf(IsNull([SerialNumber]),"Unassign","Assign")

[WayneSteenkamp]

think so.
Would something like that work?

Not using a query but a subform.

[June7]

If you want to update multiple records, one method is with sql update action. The sql can be an Access saved query or a statement in code. The Access query object can be run manually or executed with code.

The expression I suggested would go in an UPDATE query. Access Help has guidelines on building and running queries.

Another method of changing the value in the subform's records is by use of VBA code that uses RecordsetClone and loops through the records of the clone. I think you will find the UPDATE query much easier.

[WayneSteenkamp]

You mean an IIf in the UPDATE TO row under the SimStatus field of an UPDATE query?

IIf(IsNull([SerialNumber]),"Unassign","Assign")

Right the function works above, however I need my original coding in the field aswell
=[Sim_Serials_All_Subform].[Form]![SimStatus]

that possible?

[June7]

You ran an UPDATE query?

What you put on the form has nothing to do with running the UPDATE query. Can certainly have the original code.

[WayneSteenkamp]

Not sure if I follow :/
sorry.

I don't think i'm running an UPDATE query

Can the 2 codes be put together somehow so both functions run the same time on the field?

[June7]

Now I don't understand what you want or are doing.

You said user wants to import a spreadsheet and then do what? Use the spreadsheet data to update records in a table? This requires an UPDATE query.

[WayneSteenkamp]

Oh right, I'm with you now.
At the moment the form works when data is entered (populates two tables and updates the status when a sim is attached to a unit).
However the User has a large excel sheet and would prefer to import it into the database. and if Data is entered elsewhere i.e in the tables, the tables dont populate nor does the status change.
So wanted to try those two codes first in a field which i think could solve my solution. Don't ask how..bit confusing but if data is entered into the table and the form is brought up, it has the record there but hasn't populated the other table nor changed the status. If i click the button i made earlier which assigns the sim. The status and table is populated. if that makes sense. I know your thinking this is bad coding or something :/

Or I could use a query and see what I can come up with that? :/
Sorry, My knowledge on Access is poor but i'm picking things up as I go along

[June7]

Why is form populating two tables?

Just importing sheet would not affect existing data. Have to do UPDATE query to modify existing records, also maybe INSERT SELECT to append new records using data from the sheet.

Just don't know enough about your data, forms, and work flow to be specific. If you want to provide project, will look at.

[WayneSteenkamp]

It was the only way I knew to populate the tables.
you're welcome to have a look at it by all means. probably rip it apart but least i know it be correct lol
Can't send the DB here can I?

[WayneSteenkamp]

Here's a copy of my DB.
had to strip it down to be able to send it. So took logos out etc.

[June7]

I have your project now. Describe exactly what you want to happen and where and when - which form and fields are involved, how does the spreadsheet impact the existing records. Does the spreadsheet just have data to update records or are there new records that need to be pulled from the spreadsheet as well. What criteria determines which records should be updated. You did not include spreadsheet in the zip so am not able to analyze that data.