Auto Update Table

[yessir]

I am hoping to do some coding to automatically edit entries in a table.

The data is imported to the table via a button on a form and then displayed on the same form.

I am wondering if anyone has an example of how to do this easily.

I am looking to do this in a single table, searching one column at a time for one instance of something and either changing that value or moving it to another field in the table.

I am including what I am going to do for better understanding:

Private Sub cmdAutoFormat_Click()
'Do all the changes outlined as having to be done now

'Units - Change 'Units' to 'pH'
'      - Check if valid

'Results - Check for '<', '>' in this column and place proper symbol in DL
'        - Remove the '<', '>' from the Results column
'        - Check for Non-Numeric values and place those in the Note

'LOQ - Check if contains 'Not Entered' and change to ""

'Analyte - Check for 'Sulfate' and change to 'Sulfates'
'        - Check for 'pH' and change to 'pH(LAB)'
'        - Check if valid

'DuplicateOf - Check if NotNull and ifNotNull then 'Duplicate' = Yes

'ClientID - Check for the values in ClientID = to table 'tblPrefix_Outlets'
'         - If there extract and enter as the 'Location'

'Matrix - Check if valid

'Method - Check if valid

End Sub

Thank in advance
~

 

[ghudson]

I suggest that you create Update queries and run the SQL via VBA after the data has been imported. Here are examples how the update SQL is run in VBA...

DoCmd.RunSQL ("UPDATE YourTable SET YourTable.Units = 'pH' WHERE (((YourTable.Units)='Units')); ")
DoCmd.RunSQL ("UPDATE YourTable SET YourTable.Results = '' WHERE (((YourTable.Results)='<'));")
DoCmd.RunSQL ("UPDATE YourTable SET YourTable.Results = '' WHERE (((YourTable.Results)='>'));")
DoCmd.RunSQL ("UPDATE YourTable SET YourTable.LOQ = '' WHERE (((YourTable.LOQ)='Not Entered'));")

 

[yessir]

Select Into

How Can I insert the results of:

SELECT tblWater_Sample_Temp.Result
FROM tblWater_Sample_Temp
WHERE (((tblWater_Sample_Temp.Result) Like '*-*'));

into:

tblWater_Sample_Temp.Note

 

[ghudson]

Try...

UPDATE tblWater_Sample_Temp SET tblWater_Sample_Temp.result = [tblWater_Sample_Temp]![note]
WHERE (((tblWater_Sample_Temp.result) Like '*-*'));

 

[suepowell]

I would open a recordset from the table, code depends on version of access you are using, and then step through the lines of the table using your logic as laid out in your request to interrogate each field and make the required changes to other fields in that record based upon its value.

Doing it this way means you are making all the changes to 1 record at once, rather than making 1 change to all records, then moving on to the next change. I also feel I have more control over what is happening, but thats probably just a personal opinion.

If you want the code for opening recordsets, let me know what version of access you are using and I will find a sample.

Sue

 

[yessir]

I am using access 2000 but since I have the other stuff working, I'll not play with that right now, but I will be touch ing this again soon, so it would be appreciated.

RIGHT NOT THOUGH...

I want to find all records in a field

tblWater_Sample_Temp.Result

that have

> or < at the beginning and remove that character.

So that:

< 35
> 3

Would become:

35
3

Any Suggestions?

 

[suepowell]

Hi

I have attached a sample database showing how to open the recordset, and remove the first 2 characters from the string. Obviously you would need to test for the characters before removing them, and I think you will want to write back to the same variable. but I'll leave you to play with that.

If you need any more help, let me know

Sue

 

[Rich]

Use an UpdateQuery, it's faster

 

[suepowell]

I must admit I find it easier to understand what I am doing with a recordset, especially if I am having to test a value to see if the field needs to be updated.

Also I have never quite understood how to update a field to a modified version of itself.

Could you supply the expression I would need to use in the update to line if I wanted to test for a character, and then remove it if it was there, as yessir wants to do.

Thanks, this could save me loads of time too.

Sue