Automatically changing a Lookup field when data is entered

[WayneSteenkamp]

Hey,
I don't think I'd be able to share the spreadsheet on here in open due to possible sensitive data. unless theres another way of sending it to you. But basically the spreadsheet has the data which are on the ASE Form. The spreadsheet has a few more fields/headings on there which arent needed, so will be ignored and not imported into the DB. Some of the headings need slight adjustment to match those fields on the form. The user will modify the spreadsheet to match the headings of the DB. The data will update the records in the DB. AS i mentioned in a private message I sent to you, the DB will consist of two main forms - the ASE Unit Form and the SimSerials Form. The DB is to record and locate where all the ASE Units are, their history and what Serial Number is attached to it. Every ASE Unit will have a SIm Serial Number and not every SimNumber will have a ASE. Hence there can/will be more Sim Serials than ASE Units recorded. The Spare "Unassigned" SimNumbers will be entered into the Sim_Serials Form. while everything else will be in the ASE Form.
When A SimSerialNumber and a ASE unit is entered in the same record. The SimNumber's status needs to change to "Assigned" automatically in the SImSerial form/table. Any Sims which are damaged/faulty will be entered into the subform of the Sim serial Form as cancelled with the necessary details. The ASE Unit on the ASE form can then be updated with a new sim. (a new sim can be "assigned"in the ASE Form from the "loose/unassigned" ones in the Sim Serials form). If that makes sense.
All this is currently working to as far as my ability goes.
I just need to know how to use a update query and update/populate new records - and having the Status change in the Sim Serial form when a Sim and ASE Unit is entered in the ASE Form. or tables?!?! depends how the query works I guess. Don't know much about the full use of queries yet or the many types going.

[June7]

What is confidential data on the spreadsheet? Send to me personally because I am more trustworthy than anyone else? Make copy and put dummy values in its place or delete if not relevant to issue.

Now that is too much info to weed through. I take it there is no issue with the manual entry and update of individual records. You have that under control? The question at hand is how to update the status on a batch of records at one time?

The project has 4 tables. I gather you need to update the SimStatus field of Sim_Serials table? Since there are not 2000 records (only 9) I presume this is all still dummy data. What criteria would determine which SimSerialNumber records should be updated? I suspect some data in the imported spreadsheet is the trigger.

I can direct you to general guidelines on building queries, including UPDATE and INSERT SELECT and how to write code to run them, but specific wording for your situation escapes me because I don't understand the circumstances requiring update, in spite of your lengthy description.

You can use code (macro or VBA) to run saved Access query object or SQL statements constructed by code. I use only VBA. The code can be in some event, such as a button Click. You already have VBA code in this project so I presume you know how to create an event procedure. Several code options to run action query.

1. example that runs SQL statements
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM tablename"
DoCmd.RunSQL "INSERT INTO tablename(fieldname1, fieldname2) SELECT ID, [Name] FROM tablename2 WHERE some criteria here"
DoCmd.RunSQL "UPDATE tablename SET fieldname1 = value1, fieldname2 = value2 WHERE some criteria here"
DoCmd.SetWarnings True

2. example that runs Access query objects
DoCmd.SetWarnings False
DoCmd.OpenQuery "query name"
DoCmd.SetWarnings True

3. example that runs query and won't trigger warnings so don't need SetWarnings
CurrentDb.Execute "sql statement"

1 and 3 work only on action queries (DELETE, UPDATE, INSERT) not SELECT.

Access Help has more guidelines on building action queries. Also lot of info on SQL structure on web. Here is one: http://www.w3schools.com/sql/sql_update.asp

[WayneSteenkamp]

spreadsheet attached.
Like i said before, not all fields are used from this spreadsheet. just the ones in the ASE form. the fields/headings will be changed on the spreadsheet to match that of the Forms.

yes the data in the DB at the moment is all dummy records..just to see if the forms work.
and if from what i want the forms to do seems to be working ok. Still playing around to see if there are any bugs about.
Yes i want the spreadsheet to be imported in bulk into the main form/table ASE units though updates the SimSErial form/table as well. Just want to see if the Sim status changes when they're both attached (ASE and SIM)

[June7]

The spreadsheet has a field called Location with values like InService, Stock, Netcomm, Unknown, Quarantine. There is also a Location field in ASE_Units table. Is this the field you want to update based on data from spreadsheet?

I set a link to the spreadsheet so I could work with it almost just like a table. Can't add or edit records but can use it as source for queries and reports. So the following query will update the Location field in ASE_Units with value from spreadsheet:

UPDATE [ASE REGISTER] INNER JOIN ASE_Units ON [ASE REGISTER].[ASE#] = ASE_Units.ASEID SET ASE_Units.Location = [ASE REGISTER]![Location];

The IMEI values in spreadsheet do not have hyphens, they do in ASE_Units.

Also advise against use of spaces and special characters in names.

Getting somewhere now?

[WayneSteenkamp]

the spreadsheet that the client is using is going to be modified from this sample one i was given. The location fields will be changed to the ones on the ASE form. But I didnt notice the values of the IMEI in the spreadsheet. Will query which way the client wants it then.

So this code..where do i put it and is it only a link for viewing? Some of the records might need changing in the future if the location/simnumber changes etc.
Is that still possible?

[June7]

Which code are you asking about? How to link to spreadsheet or the UPDATE?

Link to spreadsheet is not code, it is set up by the External Data wizard. Linked spreadsheets can be viewed, not edited from within Access.

The UPDATE can go in an event procedure, perhaps a button click, as described in earlier post. The UPDATE is not to view data, it edits data.

I have the impression this inflow of data by spreadsheet will be a repetitive process, hence the need for coded UPDATE and maybe even INSERT SELECT of new records from the spreadsheet. If this is not the process you want to implement, clarify what you do need. The SiimNumber associated with an ASE_Unit can also be modified by an UPDATE action.

Review Access Help about UPDATE queries.

[WayneSteenkamp]

Right, I'm with you now. Misread what you wrote. Will try and see if I can get it to work

So this UPDATE code...it will enter all the records from the Spreadsheet into my DB table right? I havent got Access on this laptop i'm on now. Will only have access to a computer with it tomorrow. But I want to prepare myself and understand what I should be doing when I have access to this computer.

I read the queries. Gives me a little more insight in that, thanks.
I don't suppose if you have the process set up already ..you could attached it here so i can have a look at what you've done? I just find learning easier that way. I break things down and strip it away to understand whats what.

[June7]

No, UPDATE only modifies records already in the Access table. The UPDATE checks for matching ASE in the spreadsheet and if found updates the Location status.

If you want to actually add records from the spreadsheet to the Access table that is INSERT SELECT sql. Review earlier post discussing this.

I did not write any code. I simply built an Access UPDATE query object and manually executed it as a test. The sql is the statement I posted.

Finally understand something about your request. You want to also modify the status of SimSerialNumber in Sim_Serials when it is assigned to an ASE. Frankly, don't think I would keep this status field in Sim_Serials. You can determine if a SimSerialNumber is assigned by joining tables in a query. The query would 'show all records from Sim_Serials and only those from ASE_Units that are equal'. If a sim number is related to an ASE, it will be apparent in the query because an ASE number will show next to the sim number, if not related then no number. Otherwise, UPDATE query would be required. Problem is you have to modify two records in Sim_Serials - the one that used to be related to the ASE and now is not (unassigned) and the one that now is (assigned). That is two UPDATE actions.

[WayneSteenkamp]

Finally understand something about your request. You want to also modify the status of SimSerialNumber in Sim_Serials when it is assigned to an ASE. Frankly, don't think I would keep this status field in Sim_Serials. You can determine if a SimSerialNumber is assigned by joining tables in a query. The query would 'show all records from Sim_Serials and only those from ASE_Units that are equal'. If a sim number is related to an ASE, it will be apparent in the query because an ASE number will show next to the sim number, if not related then no number. Otherwise, UPDATE query would be required. Problem is you have to modify two records in Sim_Serials - the one that used to be related to the ASE and now is not (unassigned) and the one that now is (assigned). That is two UPDATE actions.

Yes this is more like what i was trying to say.
So it can be done with two update action codes?

Like I said..it works when entering data individually into the form. Just want to know if it will work if a Spreadsheet is imported into the DB with all its required fields in a record

[June7]

Can be done as explained. Practice building and running UPDATE queries. Be sure to use copies of tables.

[WayneSteenkamp]

Hey,

I can't get my head around this importing stuff.
Keep getting errors. they all randoms ones like duplicate fields, Input mask on the IMEINumber etc.
I've tried changing things so they both match but not much luck. Have had times when some imported but not all. Gives me a pasted error table but i can't see anything wrong with them.

The DAtabase and spreadsheet are attached
Can someone help me step by step?

[June7]

Should the spreadsheet completely replace the records in the ASE_Units table? I mean each time you are given this spreadsheet is it a complete set of records? Will all edit changes for ASE_Units data come from the spreadsheet? The Status field is empty in all rows of the spreadsheet. Where will value come from?

Several rows of the spreadsheet have a value in column I. This column does not have corresponding field in ASE_Units table so a test of the import wizard failed. Do you need the values from column I?

I set a link to the table and works very nicely.

The IMEINumber field has an input mask that uses hyphens and also saves the hyphens. The IMEINumber from the spreadsheet does not fit the input mask (no hyphens in the data). Need to resolve this. The input mask can be set to show the hyphens for data entry but not save them. Then if you want hyphens to show on reports will have to set formatting to show them.

[WayneSteenkamp]

Hey,
This spreadsheet would probably be the only sheet that gets used. It's just to transfer the records over and then from then on - record the records manually through the form. So just need it to import once successfully and then wont need it again. Unless something goes wrong.

Since the status is a new thing. they will be updated gradually as far as I'm aware. Same for the dateinstalled. These can be todays date if that can be done?
No i dont need the values in column i

I know theres a problem with the IMEINumber. I'm not sure which method is easier. I noticed you can't put hypens in the spreadsheet. Nor change the format in the spreadsheet to TEXT or Number -same as ACCESS. What do you suggest? Iwould've liked the hyphens but i guess it's not the end of the world.

[June7]

Need to clean up the spreadsheet.

Delete column I.

Normally can put hyphens in if the cells are set as text. Weird, I changed that column to text and all the numbers switched to scientific notation. Remove the input mask for this exercise. Fix it later.

SimSerialNumber field in table is set as Required. Several rows in spreadsheet do not have a SimSerialNumber. Either put a number in cells or set field to Required No. Same goes for Status field.

Find the duplicate ASEID's and fix or remove the Primary Key designation from the table.

I did all but the last and then ran import. All but 7 records came in.

[WayneSteenkamp]

Right I made all the changes and it finally all imported into my required table. Applied the input Mask again for the IMEI Number after importing and the changes were fine.

I'm near complete now
The only issue i have left is when you scroll through the imported records on the ASE_Units_Form. The SimStatus remains unchanged. I.e It's meant to change to "Assigned" if a SerialNumber is with an ASE unit in the same record.

As it stands that method only works when data to manually inputted into the Form. Any ideas?