problem with deleting

[ariel81]

hi,

the delete button of the frmInputBox is unable to delete the data "InputText" and "InputText2" at the frmCalender. Refer to its Dec 2006 & Jan 2007 calender, the datas are unable to be deleted from (1st to 11th Dec '06) and (2nd to 12th Jan '07).

Both months have the same number (11 days) of days unable to be deleted.

Also, those days that were unable to be deleted were also having problems with editing the datas. (refer to attachment)

 

[stopher]

Hi
My suspicion is this is a problem to do with the difference between US and non-US date formatting i.e. mm/dd/yy rather than dd/mm/yy. If you feed the SQL statement a date string in US format all will be well...

So add a new variable

Dim USdate as Date

And replace the SQL line with the following two lines:

USDate = CStr(month(Me!InputDate)) & "/" & CStr(Day(Me!InputDate)) & "/" & CStr(year(Me!InputDate))
sql = "DELETE tblInput.* FROM tblInput WHERE tblInput.inputdate = #" & USDate & "#;"

As an aside, you could create a function to do the conversion so you always have it on hand when you need it.

Interesting application btw.

hth
Stopher

 

[RuralGuy]

Here's a link for your favorites collection. Return Dates in US #mm/dd/yyyy# format

 

[ariel81]

thanks it works.
however when i wanted to edit the data for some dates, it doesn't edit properly.

e.g (for some dates):

original value;

12
9

i edit the data at frmInputBox to:

16
11

then i clicked "ok" at frmInputBox, although the text field at the date (frmCalender) got update the change. but when i clicked at the same date again to pop up the frmInputBox, it (InputText & InputText2) shows me:

12
9

instead of

16
11

seems like i am unable to edit certain dates.

i already edited the code: Private Sub OK_Click( ) for frmInputBox

already changed the original sql statement to:

USdate = CStr(month(Me!InputDate)) & "/" & CStr(Day(Me!InputDate)) & "/" & CStr(year(Me!InputDate))
sql = "SELECT * FROM [tblInput] WHERE (([tblInput].InputDate=#" & USdate & "#));"

did i changed the statement correctly? did i missed out anything?

 

[stopher]

Yes the edit function (Click OK) is a little more complex.

Whoever wrote the code has chosen to reference the form with f!InputDate (rather than me!InputDate as used in the Delete code)

So you need to use:

USDate = CStr(month(f!InputDate)) & "/" & CStr(Day(f!InputDate)) & "/" & CStr(year(f!InputDate))

And everywhere you see f!InputDate thereafter in this section of code, then change it to USDate. I think there's about 3 instances.

I don't have time to test it but it should work.

hth
Stopher