Review Code plz

[Geordie2008]

Afternoon All,

i have the following code (highlighted below in Blue) within my "Form" properties. The aim of the code is as follows:

I would like to open the form and populate it with the values as they appear currently in the form.

E.g.

Mandy Smith, Employee, (Record_Date: 01/01/2008)

when the user closes the form I want to change the Record_Date value to =now()

so that when I next open the form I get:

Mandy Smith, Employee, (Record_Date: 01/04/2008)

So when the form closes I want to populate the Record_Date value with =now() and until that point I want it populated with whatever was last populated in the "Record_Date" field of the table.

Private Sub Form_Close()

Me.Hid_DAT = txtNow

End Sub


txt.now is an unbound text box with the following default value "=Now()" and I set Hid_DAT to equal this when it closes. Hid_DAT is bound to the Record_Date field within the table... (so I expected it to populate this field...

This doesn't work and bombs out on:

Me.Hid_DAT = txtNow

I assume I have the syntax wrong as when I hover over it, it says:

Me.Hid_DAT = txtNow = False

What syntax do I require in order to make it update the field "Me.Hid_DAT" with the data in the field "txt.Now"?

Many thanks,
Mandy

 

[namliam]

Hi Mandy

You dont need the unbound box... but if you want to know you probably are missing the Me. in front of it....

The easier way of doing this is just to add the now into the code. Instead of the unbound box.
Me.Hid_DAT = now()

Note that Now() is a timestamp, so you dont only get the date, but also the time.
If you need the date, use the function Date()

Greets

 

[Geordie2008]

Hi Namliam,

I tried both ways, and still I get errors.... although I do agree that I probably dont need the unbound date...

I think the problem is that the code is testing if the relationship is True or false, it is returning false as Me.Hid_DAT =now() is not true.

How do I make the code set the value of Me.Hid_DAT to be =now()

I tried the obvious:

Set.Me.Hid_DAT =now()

didn't work....

Thanks,
Mandy

 

[Geordie2008]

Hi All,

Can anyone help with this please?

I have tried several different things...

I have tried changing the event from on close to On change.... no luck, although no error! It did not make the change before passing it back to the original table....

I need to update the value in the field and then save it into the table...

How can I do this please?

Thanks,
Mandy

 

[Mike375]

Try this

Forms![Formname]!Hid_DAT = Now()

 

[Geordie2008]

It gives me the following error message when I use the code in the Form_Close event...

"Run-Time errer '2448' You cant assign a value to this object"

Is this because the field is bound to a table?

Tnx,
Mandy

 

[Mike375]

That is like your field won't accept a date. Is it a number field. Also, you are running the Forms![Formname]!Hid_DAT = Now() before close form action.

This Forms![Formname]!Hid_DAT = Now() is just the full naming.

If your Hid_DAT is not a number field and you are closing the form are the set value and still have problems, then try a SetValue macro and see how that goes.

If you have not made a macro before then just go the the macro tab and then New. On the action there will be a drop down list appear. Select SetValue. At the bottom of the screen you will Item and Expression.

In item type [Forms]![YourFormname]![Hid_DAT] and for Expression type the Now()

The macro name is entered on the line in the Click event (or whichever event you are using)...in other words you don't click the 3 dots for code builder.

 

[Geordie2008]

No joy....

Can anone online help me plz?

I have a field in a form that is bound to a field in a table....

The field in the form is called Hid_DAT and this is bound to the Record_Date field in the table

On opening the form the Record_Date field displays the date when the field was last updated. currently this date is when the data was input into the table for the first time. No amendments have been made. So it reads (for example) 01/01/2008

When the form closes (on close event of the form properties) I want the value of Record_Date to be =now()

Is there anyone out there.... (please!) who knows how to populate this field with this value so that I the next time I open the form the Record_Date displays 01/04/2008 and so on and so forth.....

I tried the on Close event and tried to populate the form before the values were passed back to the table.

then I tried populating the table directly....

Sub Form_Close()
If Fields.ID = 1783 Then
Set Tbl_MAIN_Staff_Details.Record_Date = Now()
End If
End Sub

All of which have failled utterly......

I tried this in a seperate database with only 1 form and 1 field and I still cant get it to populate..... as soon as the value is not bound to the table it is happy and works fine, but I need to bind it to the table to populate the value...

If anyone can help me with this I would be so grateful,
Thanks,
Mandy

 

[Mike375]

Is the field Record_Date part of the query that supports the form.

Somewhere you are likley to either have some type of name problem. The process of setting the value of a field to Now(), Time(), date() is basic and extremely common.

The first thing I would do is change

Forms![Formname]!Hid_DAT = Now()

to

Forms![Formname]!Record_Date = Now()

and try that.

If that does not work then try it with a SetValue macro.

Do this separate from closing the form. Worry about that later. In other words the code or macro will just insert the value of Now(). If Record_Date is not visible on the form then make it visible so you can see what happens.

If Now() goes in then close the form manually and reopen at the same record.

 

[Geordie2008]

The first two did not work... the third however.....

when I created a button and attached the following code:

Private Sub Command182_Click()

Forms![frmStaffDetails_ComboBox]!Record_Date = ""
Forms![frmStaffDetails_ComboBox]!Record_Date = Now()

End Sub

It works! So I set it to null first and then to now and all is fine... tried the null then value logic in the On-Close event and it did not work however....

Now that I have got it to work when I click a button... do I set the click the button up as and On-Close macro?

Is there an event that occurs just before the form closes that I could use maybe?

Thanks for this, is great to now that I can at-least change the values..... now I just need to populate them in the table....!

So you have any next step suggestions?
thanks for all of your help!
Mandy

 

[Mike375]

Just make sure you insertion of Now() is done before the closing action of the form and there should be no problem. One of the reasons for removing the close X on the form and closing with code or macro is do different actions before the form is closed. If you have the close action first then any following code or macro actions which reference the form (such as you have with Record_Date) will not work. You will get msgs like cant assign, can't find or whatever.

You should not need to set the value to null. The setvalue rubs out what is there.

Try a little experiment.

Open your form and where you can see the field Record_Date (and with the make null part removed) and every few seconds click your button. You should see the entry in Record_Date change by a few seconds at each click.

 

[Geordie2008]

Thanks Mike,

How do I go about inserting now() before closing the form? I have tried several of the event proceedures (on-dirty, on-change) etc... none have worked.

is there any code I can use to do this?

Also - you make a good point... how do I stop a user from clicking x to close the form (thats how I have been doing it! Could that be a cause of this problem?)

I have removed the nulls. It still works.

With regards to the experiment....When I try clicking multi-times... the value does not update.... it only seems to update when I go into the code and then come out again....

I tried the following:

Forms![frmStaffDetails_ComboBox]!Record_Date = Now()
Forms![frmStaffDetails_ComboBox]!Record_Date.Update

and got complie errors on the update syntax....

Why would it not be updating the value of the field within the field itself if that makes sense?

Its almost 8:20 over here and Im gonna have to leave by 9 (otherwise security will be asking Qs!)

Thanks,
Mandy

 

[Geordie2008]

Ti quite strange... when I resize the window the values in the REcord_Date field update....

I have checked, and I have no code in ANY of teh events except "on-Open"

Could anything else be causing this?
Mandy

 

[Mike375]

Put this after your insert Now()

DoCmd.Close acForm, "FormName"

So when you click on your button it will insert the value of Now() and then close the form.

With regards to the experiment....When I try clicking multi-times... the value does not update.... it only seems to update when I go into the code and then come out again....

It should update with each click. Unless it is different with a macro but that does seem logical as the two actions are the same. I will try one myself with code.

The update should occur because each time you click you set the value of the field with the value of Now() which of course continually changing.

 

[Mike375]

I just did it with code and it updates.

Forms![2ProspectT]!Ti = Now()

Only difference to you was I had it behind a label but that should make no difference

But one would not expect it to be different to macro which is

[Forms]![2ProspectT]![Ti]
Now()

 

[Geordie2008]

Sis you get my earlier post that the Record_Date does update, but not when I click, but when I re-size the window? So I drag it to make it smaller or larger?

"Ti quite strange... when I resize the window the values in the REcord_Date field update...."

Oh and so I will call the button something like "Save & Close" and then how should I hide the X (to stop people like me exiting in this way!)

Thanks,
Mandy

 

[Mike375]

Do Now() and then close action.

I just posted that I tried the code and the field updated every few seconds as I clicked.

"Ti quite strange... when I resize the window the values in the REcord_Date field update...."

No idea.

 

[Geordie2008]

No worries, I guess as I immediately close the form anyway it will make no long-term difference!!

Thanks so much for your help!

I have to present a proto-type of this tomorrow at 10am to some much more senior bods than myself and there was no way I would have made this deadline without your help!

Have a great day...
Mandy

 

[Geordie2008]

Oh.. do you have any brief advice on how to disable the X?

No worries if not... I do a search on the forums tomorrow otherwise!

Thanks,
Mandy

 

[Mike375]

In form design and Properties and Format and Control Box. make it No and you won't see anything.

Or you can leave Control Box and set Close as No and the X will be greyed out. However, it does not look as nice and in addition it has the look that you are stopping someone from using the X

If you have Access 2007 (which I don't) I am assuming it is the same.