Check records for data

[fudgematico]

I have an update query that adds a date to a field which works perfectly but I am wondering if I can get it to check if there is a date there already, if so add another field and put the date in that? Here is my code currently:

Private Sub Command33_Click()
Dim t1 As Date
t1 = Date
CurrentDb.Execute "update tblTest set tblTest.Date2 = #" & t1 & "# WHERE ID = " & Forms!frmSwitchBoard.txtID
End Sub

Thanks in advance

 

[plog]

This sounds possible, but so is cutting off your leg and replacing it with a wood peg. Generally once set up, you don't add fields to a database, you especially don't create a way to automatically add fields. This sounds like your structure is bad.

Can you post the structure of the table you have and a more in-depth explanation of how that table works?

 

[Mihail]

Use the RecordsAffected after the Execute method. If this will return 0 (zero) then no records have been updated. So, run an Append query.

 

[fudgematico]

Thanks for the quick reply's

Here what I am trying to do

I have several records I have in a table and I have quantities for each one, what I want to happen is when I press a button it takes one away from the quantity field and also stamps it with a date.

I already have the code to make it stamp with a date and to take away the quantity.

But say I have 5 quantity and I take one away which equals 4 it then puts the date in the date field, I then want to take another one away to make 3 I want it to recognise it already has a date and put the date somewhere else.

It doesn't need to be in a field it just needs to be some place VBA can automatically put it

 

[JHB]

I think you need a "child" table where the date is stored.

It doesn't need to be in a field it just needs to be some place VBA can automatically put it

If you are thinking of a variable or Array, then it is lost when you finish database.

 

[Mihail]

I think that JHB is sure that you need a child table