Delete records with only Name and Date filled in

[Guest]

Hello -

I have a form that has Name, Date and various other fields in it. The Name
is automatically filled in from the user's logon name and the date is
autofilled too.

Problem: The user enters data, then hits a button taking them to a new
blank record (which isn't really blank because it has the user name and date
in it). When they exit, a record is left in the table because of the
autofills. Is there a way I can delete the record on exit?

Rick B had suggested that I use the before update event or before insert
event, which sounded obvious to me, but when I went back to fix this, I
realized the date and name really have to be visible when each record starts.


Any help will be greatly appreciated!

 

[Rick B]

Just build a select query and pull all records where the name and date are
not null, but the others are. Run it to make sure it pulls what you expect.

Then change it to a delete query and run it.

 

[John Spencer]

Can you use the default value property of the relevant controls to do this
show the values?

It appears that you are calculating the values and assigning them to the
controls.

 

[Guest]

Thanks, Rick. I'll give it a try!
--
Sandy

Just build a select query and pull all records where the name and date are
not null, but the others are. Run it to make sure it pulls what you expect.

Then change it to a delete query and run it.

 

[Guest]

Hi John -

Thanks for your response.

I tried both the default value in the table and then figured the best thing
would probably be the default value of the controls themselves.

Either way, when a new record is produced, if there's a default value, it
puts it into the next record.

 

[John Spencer]

Default values display in the control when a new record is displayed, but
are not assigned to the record field until the record is dirtied by some
action or the record is saved. Do you have code that runs and assigns
values to any fields? If you do, that will dirty the record and the default
values will automatically be assigned to the relevant fields.

 

[Guest]

Hi John -

Thanks for responding. I have the following in Form Load:
DoCmd.ApplyFilter , "Agent='" & fOSUserName & _
"' AND CallDate=" & Date

Also, there is a query attached to the form. The source for Agent is the
Agent column in the query and the source for CallDate is the CallDate column
in the query.

I don't know how that would automatically create a new record, but if I
switch from the form to my table, when I move to a new record for my form, a
row is automatically put in the table with the Agent and CallDate.

 

[Douglas J. Steele]

Dates need to be delimited with # symbols, and need to be in mm/dd/yyyy
format (Okay, the latter isn't strictly true: you can use any unambiguous
format, such as yyyy-mm-dd or dd mmm yyyy. The point is, if your short date
format is dd/mm/yyyy, it won't work for the first 12 days of each month)

Try:

DoCmd.ApplyFilter , "Agent='" & fOSUserName & _
"' AND CallDate=" & Format(Date, "\#mm\/dd\/yyyy\#")

 

[Guest]

Hi Doug -

Thanks for your response. I haven't had any problem with my date
formatting, although that's a good heads-up for the future. I have a default
value of Now() for the textbox.

My BIG problem is that everytime a new "blank" record is created, (acNew),
automatically a row is created in the table and the Agent name and Date are
stuck in. I don't know why this is happening. It gives me the really huge
problem of creating blank records.

Any thoughts on the above?

 

[Douglas J Steele]

I haven't been following the thread, but you mention Rick B's suggestion to
use the BeforeUpdate event or BeforeInsert event. Can you perhaps check
whether any of the other fields have values in them, and cancel the event if
they don't?

 

[Guest]

Hi Douglas -

It's too late to just cancel the event, because the record is already
created. Rick B's delete suggestion works, but not only are my autonumbers
messed up (I know that doesn't really matter, it just bugs me . . .), there's
the problem of an unnecessary trip to the database.

Any other thoughts?

 

[John Spencer]

Do you have any code in the Current event of the form or any code in other
form events that sets the values of any of the controls (or fields) on the
form?

I would expect the CallDate control to have a default value of Date() [or
Now() if you wanted the date and time] and the Agent Control to have the
default value set to fOSUser().

As I expect you know, if you set these as defaults, they will appear in the
form on the new record, but will not be set as values in the record until
something triggers the form to create a new record.

 

[Douglas J Steele]

Unfortunately, there's no way to get around the Autonumber issue.
Autonumbers are intended for one purpose: to provide a (practically
guaranteed) unique value that can be used as a primary key. If the value of
the number matters to you, you probably shouldn't be using an Autonumber.

 

[Guest]

Hi John -

Thanks again for replying.

I do have a Requery in the current event of the form. Would that do it?

I need the requery because I have two comboboxes and one textbox; whatever
the person picks in the first combo narrows the choices in the second combo
and whatever the person chooses in the second combo determines the textbox.

By the way, I've had a very difficult time doing the combo thing and I still
can't get the records to display by navigating previous records.

At any rate, is the requery the culprit?
--
Sandy

Do you have any code in the Current event of the form or any code in other
form events that sets the values of any of the controls (or fields) on the
form?

I would expect the CallDate control to have a default value of Date() [or
Now() if you wanted the date and time] and the Agent Control to have the
default value set to fOSUser().

As I expect you know, if you set these as defaults, they will appear in the
form on the new record, but will not be set as values in the record until
something triggers the form to create a new record.

 

[Guest]

Hi Doug -

It doesn't really matter to me, it just seems neater.