Can someone show me how to write this simple function?

[JGKWORK]

Hi,

I have never written vba / functions before, but I would like to learn by seeing how the code would work for a problem I need to solve.

The table is called 'Books' and the column I need to update is called 'processed' (all in one simple database).


Basically i need to write a function that when run, adds the word "complete" to the processed column (for all records) IF the word 'notcomplete' does not exist.

sounds simple enough, but when you've never written VBA before its not!

many thanks.

 

[JESTAR]

So after the last filled cell in this column, you want to stick "COMPLETE" in the next empty cell?

On Sheet1, in cell T1, write
=COUNTA(A:A)
or if it's column F with your data, write
=COUNTA(F:F)

This will count each item in that column so we can reference it and add to the next empty cell. You can hide it if you want. It's referred to below as (1,20). You'll need to change that if you change the cell you write it in.
[tt]
Public Lists as Object
Set Lists = Sheets("Sheet1&quot.Cells

Private btnButton_Click()
Dim Row as Integer
Row = Lists(1, 20) + 1

If Lists(Row, 1) = "NOT COMPLETE" Then
Exit Sub
Else
Lists(Row, 1) = "COMPLETE"
End If
End Sub
[/tt]

I haven't tested this, but hope it helps.

 

[JGKWORK]

Thanks for this - I'm not sure but is this for Excel? I should have said it was for a table in Access - how would it differ?

thanks again.

 

[JESTAR]

Yes for Excel. Sorry, didn't realise you were in Access.

 

[JGKWORK]

Sorry I wasted your time there. I mentioned it was for a database, but I should have been clearer - and i suppose excel is just a simple database!

cheers anyway.

P.S. can anyone show me the code for Access?

 

[Zathras]

I don't know if this is the "accepted" approach do do what you want, but it seems to work:
[blue]

Sub UpdateBooks()
Dim q As QueryDef
Dim sSqlStatement As String
  sSqlStatement = "UPDATE Books SET Books.processed = 'complete' " _
    + &quot; WHERE (Nz([Books].[processed],' ')<>'notcomplete');&quot;
  Set q = CurrentDb.CreateQueryDef(&quot;Temp&quot;, sSqlStatement)
  q.Execute
  CurrentDb.QueryDefs.Delete (&quot;Temp&quot;)
  Set q = Nothing
End Sub

[/color]
You might want to post your request again in one of the Access forums.

 

[JGKWORK]

Thanks - I guess there will be more than one way to write this.

Thanks.