Close record (2003 SP2)

[Egg 'n' Bacon]

I'm looking at a basic DB, where I'd like users to be able to click a button (on a continuous form by each record), that would populate a time & date field then close the record (in effect hide it from view).

Trouble is, I'm not sure how best to do it.

[HansV]

Should records be hidden when the date/time field is populated, and displayed if the field is blank?

[Egg 'n' Bacon]

That's exactly right, Hans.

[HansV]

Create a query based on your table (or query) with <code>Is Not Null</code> in the Criteria line for the date/time field.
Save this query and use it as record source for your continuous form.
The On Click code for the command button could look like this (substituting the correct names, of course)

Private Sub cmdSetDate_Click()
Me,DateTimeField = Date ' or Now
Me.Requery
End Sub

Use Date if you want to set the field to the current date (e.g. 06-Jul-2006), or Now if you want to set it to the current date and time (e.g. 06-Jul-2006 12:41).

[Egg &#39;n&#39; Bacon]

Two things Hans; I'm guessing you meant the query criteria should be 'is null' & I'm getting a compile error (because I don't have a clue with VBA).

The date/time field I wish to populate is [closed].

Here's the code;
Private Sub Command32_Click()
On Error GoTo Err_Command32_Click
Me,closed = Now()
Me.Requery

Exit_Command32_Click:
Exit Sub

Err_Command32_Click:
MsgBox Err.Description
Resume Exit_Command32_Click

End Sub

Never mind, I just spotted the comma instead of a full stop (period). Everthing works now.

Many thanks.

[Egg &#39;n&#39; Bacon]

Ah, just come up with a new issue, resulting from using a query as a record source; the data is stored in one main table, which is related to another (reference table). By using a query, I'm now unable to 'add' records, but if I use a sub-form I can't have continuous forms.

Any ideas?

[HansV]

Yes, I meant Is Null. (Sorry, it's all air code)

[HansV]

Just adding the Is Null criteria to a query shouldn't make a difference as to whether you can add records. I'm afraid I don't understand your setup.

[Egg &#39;n&#39; Bacon]

I don't think it's the null, but the second table, that's giving me the problem. However, here's a (very untidy) example, that I've been playing with.

[HansV]

What you want is impossible. There can be multiple records in tblProg for a single record in tblMain, so if you want a continuous form, you shouldn't bring tblProg into it.

[Egg &#39;n&#39; Bacon]

I did wonder about that.

I would like to be able to display the settings from TblProg (duration & temp

[HansV]

There could be a variable number of records in tblProg belonging to a single record in tblMain. How would you want to display them?
If you really need this, switch to a single form based on tblMain only (or a query based on tblMain) with a continuous subform based on tblProg.

[Egg &#39;n&#39; Bacon]

I think I'll go for a seperate 'data entry form', as I'd really like to keep the TblMain records on a continuous form.

Thanks once more.

[HansV]

That's a good workaround.