VBA Code to selete todays date

[tweety]

Hi



I have a form with a subform, and would like to made life easier for the end user, so they select a customer (shop name) from the drop down list, once a name is selected todays date is entered into the subform (date of sale) and then set the mouse cursor in th product. i have attached a screenshot of the form.

the only way i can think of is to write vba code "on change" in the drop down list box properties

thanks in advance

[JoeM]

the only way i can think of is to write vba code "on change" in the drop down list box properties

That's exactly how I would approach it.

[ssanfu]

Like Joe says, I have used the change event.
Mostly, I use the after update event of the shop name combo box.

If this is for a new record, you could set the default property of the "Date of Sale" field to "Date()". Every time a new record is created in the table for the "Date of Sale" field, the current date is entered automatically.

Everyone has their own preferences Take your pick....

[alcapps]

ssanfu is correct..

I usually use after update ..

then you can use the following vba..

me.dateofsale = date()
me.(subform name of control).form.control("ProductControlName").setfoc us


give that a shot should cover both of the items you are trying to do.

[tweety]

hi thanks for the reply
i have tried the default property to now(), date() and today() and they all give the attached error message: index or primary key cannot contain null value

the only way to remove that message is by selecting the date from the date picker, even after the date is set using the date(), now() and today()

any ideas???

thanks

[tweety]

what vba do i use??

[alcapps]

what vba do i use??

you can use the default value..
you might be assigning it without a = sign.
=Date()

in vba the event is
before insert
me.datefeieldname = date()

if vba removes the () after date don't worry. It will work.

[tweety]

just so we on the same page this code goes under the dropdown list box (shop name), before insert??

what i like to do is select a shop, then access puts in the date in the subform (date of sale, todays date), then creates a new saleID number (this field is primary key already) and puts the cursor in the product field

thanks

[tweety]

hi i just noted that once a press the button to create a new record, until the date is reselected the key to saleID is not formed, what iam i doing wrong??

Thanks in advance

[alcapps]

just so we on the same page this code goes under the dropdown list box (shop name), before insert??

what i like to do is select a shop, then access puts in the date in the subform (date of sale, todays date), then creates a new saleID number (this field is primary key already) and puts the cursor in the product field

thanks

before insert event is from the subform form properities.
that is why you use me.[ID field name] = me.parent.saleid

parent refers back to the main form from your sub form.

hope this helps

[ssanfu]

Addressing sub forms is tricky. See http://access.mvps.org/access/forms/frm0031.htm

So you have a main form, a sub-form and a sub-sub-form?
From the main form (shop name combo box), try

Private Sub cboShopName_Afterupdate()

    Me!Subform1.Form!ControlName = DATE()
   ' other lines

End Sub

where "Subform1" is the name of the sub-form CONTROL
and "ControlName" is the name of the control (DateOfSale??)

If you still have problems, tell us:
the name of the main form
the name of the combo box for the shop name
the name of the sub-form control
the name of the control

[tweety]

thanks man

the coding Me!Subform1.Form!ControlName = DATE() works fine!

iam going to run some test then apply it to a another form

thanks

[alcapps]

you might consider in the sub form going to the control and in the default adding =Date() instead.
This is cleaner and it prefills the date when you add a new record. Just a suggestion.

[tweety]

thanks for that
i did set that up in the form, to default to todays date, but the problem with that is i need to reselect the date so that the primary ID gets created, the only work around i can think of was get access to add the date again via vba, which works fine now, ill still doing so tests, and if it work properly then ill apply the same method to another form i have

thanks anyway