Simple-update table with date when criteria on form is changedd

[kensaffer]

Frustrating but the solution is simple (I Think).
Running Access 2000 on Sql Server and using an ADP.
Have a drop down on form, call it type - with 5 types, and when the type changes I want to insert into the table the current date into the specific typedate field. The TypeDate is not on the form. Let's say the types are male,female,aunt,uncle and pet. So I would have a MaleDate, a FemaleDate,AuntDate, etc. When one changes form male to female then the FemaleDate would be populate with current date.
I tried a macro, didn't work, a stored procedure and it didnt work. perhaps the syntax is also hurting as well. CAnanyone help with this 'simple' solution ???

 

[PaulBricker]

You don't say whether you want the inserted date to go in an existing record or in a new record. That would make a difference, but what I would do to start is add a second column to your Drop Down that had the TypeDate in it. You don't have to show the value if you don't want, but it would make it available to use in a SP when someone selected a value from the Drop Down.
Then I would create an Update style SP, if it's an existing record, and use that to add the current date to the field represented by the Drop Down. If it's a new record, then you can try an Append type query.
If this doesn't seem to work, it would help to know what you've used for a macro or SP attempt.

Paul

 

[kensaffer]

Paul, Thank you for taking the time to respond. This would be an insert to an existing record in the table. I like what you mention about having a non-appearing col but that seems a bit more complicated that this should be. The stored Proced is, one is....

Alter PROCEDURE CharityCareDateP As
UPDATE dbo.accountstbl
SET CharityCareDate = getdate ()

If I run it from the SP view, it runs ok. I want to have this run as part of the code when the drop down changes and I cannot get it to run. I've used
DoCmd.OpenStoredProcedure (charitycaredatep)
and different variations even created a macro that would run the stored procedure to no success.

 

[PaulBricker]

This is how I filled the arguments for the DoCmd.OpenStoredProcedure method

DoCmd.OpenStoredProcedure "StoredProcedure1", acViewNormal

It wouldn't accept () around the table name. It might make a difference for you. Also, set the second argument to acViewNormal
I'm testing this in the Northwind.adp for Access 2003. It shouldn't make a difference but what version are you using.

Paul

 

[kensaffer]

Paul, I'm using Access 2000. Isn't there a way to code this so that I would check
if me.type = 'charity care' then
charitycaredate = date

[I have a table field called accountstbl.charitycaredate]

code so that I can put the charitycaredate into the table by accountstbl.charitycaredate = date or something like that ?

Using your code I get a run time error 7874 with message box
Access cant find object charitycaredatep
Here's code :
DoCmd.OpenStoredProcedure "charitycaredatep", acViewNormal
Here's Procedure :
Alter PROCEDURE CharityCareDateP As
UPDATE dbo.accountstbl
SET CharityCareDate = getdate ()

 

[PaulBricker]

I'm at home where I have A2000 and run the code above using the Click Event for a button. I got a confirmation that the procedure had run and that no records had been returned, but then I got an error message that said Access couldn't fine "StoredProcedure3". But when I looked at the table, the date value had been changed. I can't seem to shake that error message. I haven't tried this yet and probably won't for a couple hours, but have you tried putting the SP into an sql statement and running it that way using the DoCmd method

DoCmd.RunSQL "Alter PROCEDURE CharityCareDateP As UPDATE dbo.accountstbl SET CharityCareDate = getdate ()"

It might be worth a try. I will post back when I've had a chance to try it out.

Paul

 

[PaulBricker]

I tried running just a simple Update string, and that seemed to do it

DoCmd.RunSQL "Update accountstbl SET CharityCareDate = getdate ()"

Try that inside your If statement and see how it works.

Paul

 

[PaulBricker]

Just as a word of caution, please make sure you test this stuff on sample data. I still think you will need to filter the Update statement to apply to a specific record so you aren't updating all the records with the getdate() value.

Paul

 

[kensaffer]

Paul,
Thank you very much fo rthe help, it works! I don't' know how you found the correct code. You are right in that it does update all the rcds. Could I impose on your knowlege on how to only update the current rcd .
I added to the code you sent

Assign the current account # to a string varible...
Caccount = Me.Account

If Me.FinalStatus = "Charity Care" Then
DoCmd.RunSQL "Update accountstbl SET CharityCareDate = getdate () where accountstbl.account = Caccount "
End If

That code give me a run time error of 2757 with message
There was a problem accesssin a property or a method of the OLE object.
I'm doing this on the 'ON Change" event procedure of the form for the FinalStatus object.

 

[PaulBricker]

Assuming that the account value is the primary key, then you just need to adjust the syntax a little to handle the variable
Change the line to this

Caccount = Me.Account

If Me.FinalStatus = "Charity Care" Then

  DoCmd.RunSQL "Update accountstbl SET CharityCareDate = getdate () where accountstbl.account = '" & Caccount &"'"
End If

Try it and see if that runs better.

Paul

 

[kensaffer]

Paul,
I tried it (along with several permutations and while it didnt give me an error it also didnt update the date field in the table. Any thoughts....

 

[kensaffer]

I think what may be happening is that the accountstbl has not yet been updated with the new record so when I try to match on the account it's not there. The form has not been closed or the user has not moved from that specific form.
Does that make sense ?
Having said all that , how would I do the update when I need to be on the current record ?

 

[PaulBricker]

Well, the first thing I would do is add a line to the code, above the DoCmd.RunSQL line, to debug the sql string and make sure the value being passed to Caccount is correct.

Debug.Print "Update accountstbl SET CharityCareDate = getdate () where accountstbl.account = '" & Caccount &"'"

This should return a string in the Immediate (debug) Window that you can check for accuracy. If the value being passed to the variable isn't correct, then we need to figure out why.
Try this first, and post back with the results.

Paul

 

[kensaffer]

As you suggested I used the statement and this is what was returned: I put in aaabbbccc for account #

Update accountstbl SET CharityCareDate = getdate () where accountstbl.account = 'aaabbbccc'

I still think it has to do with the fact that accountstbl doesn't have that account at the time when the code is executed, it hasn't been saved yet.

 

[PaulBricker]

My impression is we have a form, we open the form to a specific record, we decide to change the Type and in the Change Event, we run some code to modify another field in the table.
If, when you added the debug line, it printed to the debug window, the Change Event is running so the code should be executing.
Is this a new or existing record we are trying to modify?? Why wouldn't accountstbl have the record?

Paul

 

[kensaffer]

When the form opens we are either adding a new record or could be updating an existing record.If it's new then the accountstbl won't have it until we leave the form(so it seems). Whenever the Type field changes we want to insert into the accountstbl the current date. This way we can track the date, if needed, when the Type changed.
Does that make sense ? Sometimes the explanation isn't clear even when I try to explain.
Thanks again for spending time with me on this.

 

[PaulBricker]

OK, so we should be able to save the record using this command

DoCmd.RunCommand accmdSaveRecord

before we try to execute the Update command. Try adding that line to the code before your If statement and see how that goes.


Paul

 

[kensaffer]

Paul,
I tried what you wrote and now I'm getting "write Conflict messages" and Im asked to save or drop or copy to clipboard. This is happening before I finish entering the data on the form. The Type is not the last field on the form.

 

[PaulBricker]

If the other fields are required Fields, then you will have a problem trying to save the Record first.
So, can I assume that the code works if the record you are working in is an existing record? If that's the case, I'm not sure what will help. You are faced with a design issue at this point. You could think about something like this:
If the fields are required AT THE TABLE LEVEL, then you could set those to No, and then in the On Exit Event of each remaining field on the form, test to see if there is a value in the field when the user moves to the next field. If it's blank, you could throw up a message that tells them they can't leave the field blank, send them back to the textbox to fill in a value. It would probably eliminate the need to test at the table level, but that isn't always the cleanest way to handle this type of situation. I'm not necessarily recommending this method as much as trying to help you look at alternatives for getting this done. Let me know what you think and we'll go from there.

Paul

 

[kensaffer]

None of the fields are required at the table level, they are optional at this point. If I'm working on an existing record and usingthe code you provided - it doesn't work. I need to save the record to have it work.BUT I did find what seems to be working, using the code you provided I added it
to the Form's properties of AfterUpdate. That seems to be ok, where th euser leaves the form to go to another form and it picks up the date. It may not be my chosen way to have it work (I'd prefer to use some code) but it does seem to work. Unless you have other thoughts or any warnings for me, I'll use this method.
Thank you very much for your help, I really appreciate it.