Update a record without a query?

[fergcu]

Hi,

Don't know if this is possible, or if im being just plain stoopid:

Is there a vb command that will update a record without having to use a query?

My problem is this - I have an HR database which has allows us to add employees that are going to start. We then set their [Activity Status] from "Starting" to "Active". At the moment this is done manually, but what i would like to do is have this automatically change when the [Start Date] = Date().

The code i tried to use (but is obviously wrong) is:

If [Activity Status] = "starting" And [Start Date] < Date Then
[I][COLOR="Red"]Set[/COLOR][/I] [Activity Status] = "Active"
End Sub

Any thoughts what i might change "Set" to, to make this work?

Thanks,

Ferg.

 

[KeithG]

something like below will work if your add your table name

docmd.runsql("
UPDATE [TableName] Set [Activity Status]=”Active” Where [Start Date]=Date()")

 

[The_Doc_Man]

KeithG is right. Here's the behind-the-scenes reason.

In order for VBA to work with data, you must open a recordset. It does not matter whether this is a table or query, it has to be a recordset. VBA will not work on a record in any other context than a recordset. So if you want to update a record, you need a recordset. If you OPEN a recordset directly to a table, it is treated as SELECT * from TABLE; - i.e. an implied query.

Keith's suggestion makes the query SLIGHTLY less implied - but quite dynamic.

 

[fergcu]

ok, thank you both - so to summarise, you cannot use direct vb (and thereby 'bypass' queries) to update a record? If i want to do anyform of updating records, i should use an update query?

Assuming this, then i would use something similar to Keiths sql, however it is still a bit too loose for my liking? how would i do some sort of 'if' statement in the sql? I would like it to only work on records who are 'starting', and also contractors and permanent staff - so would this work?

 UPDATE [TableName] Set [Activity Status]=”Active” Where [Start Date]=Date() And [activity status] = "Starting" And [Type] = "contractor" or [Type] = "permanent"

Many thanks,

ferg.

 

[KeithG]

KeithG is right. Here's the behind-the-scenes reason.

Whoa! The Doc Man agrees with me! That made my night! But anyways you need to add another Activity Status]=”Active” after the type=permanent otherwise you will update every permanent employee. Why are you agianst using queries? You could open the table via a recordset but that would not be efficent.

 

[fergcu]

lol - im glad my question has brought happiness to someone!

i'm definitely not against queries, but unfortunately the database is deployed over a wan - and therefore is not only not 100% stable, but can also be quite slow whn eexecuting update queries. One of the bits of code that i have alters the font colour of the end date if it is in the past and the employee is active - this is done in the form code, and is very rapid to execute. I was hoping that there might be some sneaky way of using this type of code to update a couple of records. Thinking about it, i was being quite naive and stoopid after all!

Anyway, thanks again for your answers guys.

Ferg.

 

[KeithG]

Your welcome, I enjoyed helping you.