Getting a date to repeat itself in the same form (1 Viewer)

[robwhiting]

Ive set up a database form which is one to many, ie, 1 venue with up to 30 leaflets against the specific venue....the problem is we have to put a date against each entry, which is becoming time consuming. So after putting the first date in i want the date to be put in automatically as per first date....any idea on how to do this....see picture attached;-
The date shown is the 18/01/2022
regards
robert

 

[pbaldy]

Try this:

Forms: Carry current value of a control to new records

in the after update event of the control.

 

[Eugene-LS]

Dim dOldDate As Date

'Remember the value to transfer to the new record
    dOldDate = Me!StartDate
    Me.Recordset.AddNew 'Creating new record in form
'Set the value from the previous record
    Me!StartDate = dOldDate

 

[Pat Hartman]

1 venue with up to 30 leaflets

Sounds like a design issue. Once you have more than one of something, you have many and many requires a separate table. Hope you aren't using columns to hold the leaflets.

Rather than update the dates RBAR, I would run an update query. Add an unbound date field to the main form. Then run an update query that applies that date to all the child rows.

 

[pbaldy]

Sounds like a design issue. Once you have more than one of something, you have many and many requires a separate table. Hope you aren't using columns to hold the leaflets.

You must not have looked at the picture.

 

[Gasman]

Damn,
I answered this on the crosspost https://www.accessforums.net/showthread.php?t=85528 without realising.

 

[Pat Hartman]

You must not have looked at the picture.

I looked at the picture. The leaflets look like separate controls on the form. I don't see any evidence that they are a subform. They could be and the outline and scroll bars are hidden but that isn't what it looks like. It looks like there will be 30 * 4 columns in one table row and that is why the leaflets are limited to 30. Otherwise, there would be too many columns.

 

[pbaldy]

Perhaps I was misled by "Ive set up a database form which is one to many"

 

[June7]

And why save month delivered in another field? This can be extracted from DateDelivered.

 

[Pat Hartman]

We can only tell if we can see the schema Maybe Rob can post the database.

 

[robwhiting]

Here's the database

 

[June7]

There is a subform.

Did you try either suggestion? I recommend pbaldy's.

Again, why have user select month when it can be calculated from DateDelivered?

 

[robwhiting]

There is a subform.

Did you try either suggestion? I recommend pbaldy's.

Again, why have user select month when it can be calculated from DateDelivered?

Your right about the Month, it can be calculated from the date.
Im a bit of a novice with Access, I have found the After update event against the date delivered, however I'm not sure what code to type in to make it work.
i did attach the database as requested on my last reply

 

[pbaldy]

I'm not sure what code to type in to make it work.

Adapting the code from the link:

Private Sub DateDelivered_AfterUpdate()
  Const cQuote = """"  'Thats two quotes
  Me.DateDelivered.DefaultValue = cQuote & Me.DateDelivered.Value & cQuote
End Sub

 

[robwhiting]

Adapting the code from the link:

Private Sub DateDelivered_AfterUpdate()
  Const cQuote = """"  'Thats two quotes
  Me.DateDelivered.DefaultValue = cQuote & Me.DateDelivered.Value & cQuote
End Sub

Many thanks, i'll give it a go
Again thanks for your assistance

 

[Pat Hartman]

Now that we can see that there is a proper relationship, my initial suggestion of an update query still stands. Use an unbound control on the main form to hold the date you want to apply to all the child records.

The "default" solution isn't appropriate in this situation. It would require "touching" each subform record and the OP doesn't want to do that.

 

[June7]

Why would the DefaultValue solution require 'touching' each subform record? This is intended to work for data entry of new records going forward, not edit existing records. Apparently data is already in existing records by normal data entry.

 

[Pat Hartman]

This doesn't sound like a new data entry situation. The records already exist if the idea is to record when the delivery happened.. The OP just wants to add date to existing records. So the default won't even activate since the records are not new.

I don't think the date belongs in the detail records anyway. If all the flyers are being delivered at once, the date belongs in the parent record.

If this is a "to be delivered" date, then maybe the default makes sense if most will be delivered on one date but others will be delivered on other dates.

 

[robwhiting]

Adapting the code from the link:

Private Sub DateDelivered_AfterUpdate()
  Const cQuote = """"  'Thats two quotes
  Me.DateDelivered.DefaultValue = cQuote & Me.DateDelivered.Value & cQuote
End Sub

Sorry, Ive only just tried it and it works a treat, thanks very much for your assistance, much obliged.

 

[Pat Hartman]

So, you ARE populating the date on data entry? BEFORE the delivery is made?
WHY is the date in the child table and not in the parent table if it is the same for all rows?