We help IT Professionals succeed at work.

Simple VBA to update form field

Patrick O'Dea
on
See attached.

Open form frmAppointments
Click on any vacant (white) timeslot to book an appointment.
In the yellow bit (!) key in a name of a NEW customer - do NOT use the drop down.

You will now be told that this is a new customer and continue with details.

Question :  Can you add a simple bit of code that sets the field  [customerjoin] to todays date for NEW customers only.

Please advise where you have added the code.
I tried something but it all got complicated!

The bottom line is that I need to now the date that any new customer is added. Appointments2007.zip
Comment
Watch Question

You can use default value in a form (or table).
Set it to =Now()
Retired IT Professional
Commented:
Select the field in the form.
Properties
Default value =Now()

It has to be the same format as the field in the corresponding table.
Hamed NasrRetired IT Professional
Commented:
Table or form? Depends.
If default is needed on any form field appears then go for table.

Author

Commented:
Thanks als315 and hnasr,

Could you perhaps try your suggestion out in the actual database.
It should work as you say but it is give me wierd errors about "one to many relationships" etc.

Perhaps you could see if you get the same error.

I have inherited this code from elsewhere ..so there could be a few quirky things in there.
Hamed NasrRetired IT Professional

Commented:
I tried putting =Date() but refused and asked to be the same as table's field.
Check field it is Date/Time no format, and =Now() was accepted!

Author

Commented:

Has anybody actually opened the database and set the default to now().

This needs to be tested with a NEW customer.

I get bizarre one-to-many errors when I try to set the default value.
Is this message about tblAssets?
Your tables tblAssets and tblAppointments have connected in data scheme (ApptAssetID and AssetID). So AssetID must persist in table tblAssets when you like to add data to table tblAppointments.
But it is other question :)
Hamed NasrRetired IT Professional

Commented:
"Has anybody actually opened the database and set the default to now()"
Yes, I did.
The database has an Autoexec macro. Open while pressing Shift key opens the database and shows the objects.

Author

Commented:
hnasr,

Perhaps you could look at this.
My customerjoin field is set to now()

However, it leads to other errors when I try to add a customer.
These errors go away when I remove the default value now().

(I haven't been drinking...)
Appointments2007.zip
Hamed NasrRetired IT Professional

Commented:
Just increase the field width.
Hamed NasrRetired IT Professional

Commented:
"These errors go away when I remove the default value now()."

Ok tell me the steps you follow to replicate the errors!

Author

Commented:
Click frmAppointments
CLick any white space in the grid of available appointments.
Type a value of "zzzabc123" in the name field (yellow field)..

This will then show a dialogue box with a message "zzzabc132 is not in the customer database.....".

So far so good.
Click "Yes" to add customer.

Then you get a bizarre error message

"THE CURRENT FIELD MUST MATCH THE JOIN KEY 'CUSTOMERNAME' IN THE TABLE THAT SERVES AS THE ONE SIDE OF THE 'ONE TO MANY RELATIONSHIP.
ENTER A RECORD IN THE ONE SIDE WITH THE DESIRED KEY VALUE AND THEN MAKE AN ENTRY WITH THE DESIRED JOIN KEY IN THE MANY-ONLY TABLE"

Author

Commented:
Oops the previous comment ID:32998540 was intended for hnasr (or anybody else).  

It explains how to duplicate the error which I believe is caused (bizarrely) by allocating a default value to the "Customer Join" date.
Appointments2007.zip

Author

Commented:
I am closing this query .... I will revisit the issue in a few days.  

Thanks again.