Problem deleting record via SQL

[Andrew]

Hi,

I have been able with help from this site to identify a Date-Time on a
record in datasheet view on a subform. The user selects from a list by
clicking into the cell and pressing a button. I can get a variable to show
the result (eg 18022006 1:00:00PM).

I am then able to insert a record into another table using the following code:

DoCmd.RunSQL "INSERT INTO Bookings (Booking_Number,[Food Time]) VALUES ('" &
Current_Max_Booking + 1 & "','" & Selected_Food_Time & "') "

My problem is I can't delete the record from the table that is the source
table for the subform. I am usingf the following code:

DoCmd.RunSQL "DELETE * FROM Tbl_Times_Available WHERE [Food Time] = #" &
Selected_Food_Time & "#;"

I have tried it with both the form containing the subform open and close
thinking it may not be able to delete the record with the table being
accessed via the form.

On both occaisions I get a message that 0 records will be deleted. It isn't
an error it just can't find the record.

I'm also wondering if someone can guide me with a quick and easy bit of code
to provide a message box with the contents of each 'Food Time' record within
the table (Tbl_Times_Available). Something like

While not EOF
msgbox ([Tbl_Times_Available].[Food Time])
wend

 

[Tim Ferguson]

DoCmd.RunSQL "DELETE * FROM Tbl_Times_Available WHERE [Food Time] = #" &
Selected_Food_Time & "#;"

See your previous thread. You need to use Jet-Safe date formats, and DMY is
not one of them.

<grump>

By the way, further experimentation shows that RunSQL does NOT carry out
the behind-the-scenes reformatting (compare when you type dates into the
query designer grid). This has to be a design fault: either the DoCmd
object is going to mimic the UI or it isn't....

</grump>

Tim F