I want a value automatically entered for my OnClose Event Procedur

[Rachel]

I have a form that runs an append query on close. However I need to have
Date( ) automatically entered as the parameter on the query. Could someone
show me how to add that to the following statement?

Currently it looks like this-

Private Sub Form_Close()

On Error GoTo Err_Handler

DoCmd.SetWarnings True
DoCmd.OpenQuery "qryUpdateEnrolledPatients"

Exit_Point:
DoCmd.SetWarnings True
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub


Thanks-
Rachel

 

[Rick B]

If I understand your post correctly, you don't need to do anything to your
code, you need to change your query. Just modify the query to include the
Date() items where you need it. I assume you are updating a "last changed"
field or something similar? If so, just modify that query to include the
"Date()" item in the "update to:" part of your query.

If that does not help, please specify a bit more as to what you are trying
to do.

Thanks!

 

[Rachel]

The query is actuallly an append query. The parameter that I am entering is a
date that selects records based on their enrollment date. So I would want to
add a line of code that would enter Date() as the parameter value without the
user needing to enter it.

Thanks

 

[Rick B]

No code needed. Just put it in your query.

In the "Criteria:" under your enrollment date field in your query, put...

=Date()

 

[Rick B]

Hey, I'm curious. You're not taking all the records added today and copying
them to another tale are you? If so, why? This would often indicate a
non-normalized application. Typically, in a normalized relational database,
you don't move records around from one table to another.

 

[Rachel]

I have to move the records to a different table because eventually we will
have to destroy all the data in the original table because these individuals
will not have agreed to participate, and therefore we will not be allowed to
keep their data.

 

[Rick B]

Why not just add an "accepted" date field. You can run a delete query to
delete all the records from a certain date range where the "accepted" field
is null.

Typically, it would not be recommended to move them like you are doing. It
is not considered "normalized" database design.