Increment subform by 1

[Geoff]

I'm trying to increment the date on my subform by one day each time, and am
having difficulty. I've seen this question asked many times in this group so
sorry for repeating it, but I just cannot get it to work and I am tearing my
hair out with this. If I give my table and field names, would someone please
be kind enough to type out the exact code I need, taking into account my
particular table/field names? I'm not being lazy with this-I just cannot get
it to work so I'm obviously missing something that's probably obvious to
someone who's used to writing code.

The code I have seen in several posts is this (to be put in
Form_BeforeInsert on the Subform):

If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Select the record in the main form first."
Else
Me.ID = Nz(DMax("ID", "YourSubFormTable", _
"YourForeignKey = " & Me.Parent.MainID),0) + 1
End If


My main form is linked to a table called 'weeks' and my subform to a query
called 'query-days'. They are linked with the field 'weekstarting' (same
field name in both main form table and subform query). What I want is for
the 'day' field in the first subform record for each main form record to be
the same date as the 'weekstarting' field, and then for 1 to be added each
time.

Any help would be much appreciated. I would imagine it's simply a case of
understanding the code I quoted above properly, but nothing I've tried
works.

Many thanks,
Geoff.

 

[Pavel Romashkin]

Geoff,
Try to put this code in your subform's Before_Insert (or Current) event:

If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Select the record in the main form first."
Else
Me!day = Nz(DMax("day", "query-days", _
"weekstarting = #" & Me.Parent!weekstarting & "#"), _
Me.Parent!weekstarting-1) + 1
End If

Good luck,
Pavel

 

[Geoff]

Geoff,
Try to put this code in your subform's Before_Insert (or Current) event:

If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Select the record in the main form first."
Else
Me!day = Nz(DMax("day", "query-days", _
"weekstarting = #" & Me.Parent!weekstarting & "#"), _
Me.Parent!weekstarting-1) + 1
End If

Good luck,
Pavel

Thanks very much for that Pavel. It wasn't incrementing by one day for each
record at first but now suddenly it is? Well I won't worry about that
because it seems to be working perfectly now, so it must be something odd I
was doing. I need to add some more functionality to it but I hope I'll be
able to manage that.

Thanks again Pavel, you've really helped me a lot.
Geoff.

 

[Geoff]

Thanks very much for that Pavel. It wasn't incrementing by one day for each
record at first but now suddenly it is? Well I won't worry about that
because it seems to be working perfectly now, so it must be something odd I
was doing. I need to add some more functionality to it but I hope I'll be
able to manage that.

Thanks again Pavel, you've really helped me a lot.
Geoff.

Hi again Pavel,

Unfortunately it's not always incrementing, so I wasn't imagining it before.
It puts the same date in all the fields in the subform just sometimes. More
often than not it increments properly but now and then it doesn't, and I
can't see what I'm doing differently in my testing.

Any ideas on what could be causing it please?

Thanks,
Geoff.

 

[Pavel Romashkin]

I think the problem is, the code ran for existing records, too. We
should exclude this possibility.
I will try to change your code and comment it, and you follow the logic:

<<This is Current event of the subform. Perhaps you have some other code
in it?
If Me.Parent.NewRecord Then
' If there is no weekstarting to use, quit.
Cancel = True
MsgBox "Select the record in the main form first."
Else
if Me.NewRecord then
' Only change the Day field if this is a new record in subform.
' Find max of day field in sf for the weekstarting that is selected
' in the main form.
Me!day = Nz(DMax("day", "query-days", _
' If there is a Max value, we simply use it, and will increlent it by
1 at the end.
"weekstarting = #" & Me.Parent!weekstarting & "#"), _
' If this Max is Null, this means no records yet exist for the weekstarting
' chosen in main form, so we simply use it but subtract one, becuase
' we add one to the whole expression in the end.
Me.Parent!weekstarting-1) _
' And we add 1 to increment the Max if it was found to get the nexd Day.
+ 1
end if
End If

Pavel

 

[Geoff]

I think the problem is, the code ran for existing records, too. We
should exclude this possibility.
I will try to change your code and comment it, and you follow the logic:

<<This is Current event of the subform. Perhaps you have some other code
in it?
If Me.Parent.NewRecord Then
' If there is no weekstarting to use, quit.
Cancel = True
MsgBox "Select the record in the main form first."
Else
if Me.NewRecord then
' Only change the Day field if this is a new record in subform.
' Find max of day field in sf for the weekstarting that is selected
' in the main form.
Me!day = Nz(DMax("day", "query-days", _
' If there is a Max value, we simply use it, and will increlent it by
1 at the end.
"weekstarting = #" & Me.Parent!weekstarting & "#"), _
' If this Max is Null, this means no records yet exist for the weekstarting
' chosen in main form, so we simply use it but subtract one, becuase
' we add one to the whole expression in the end.
Me.Parent!weekstarting-1) _
' And we add 1 to increment the Max if it was found to get the nexd Day.
+ 1
end if
End If

Hi Pavel,

Thanks for your reply. I've tried the code and read your comments, and it
all makes good sense to me when I read it in plain English. However, it
still doesn't work unfortunately. It works more often than not, but when it
doesn't work it now puts the date after 'weekstarting' in every row instead
of the same day.

The only thing I think I forgot to mention before is that it won't work at
all if I put the code in the 'on current' event, but it works (apart from
this one problem) when I put it in the 'before insert' box. Could that
somehow be the cause of the problem?

Thanks for your time so far Pavel, I very much appreciate it.

Geoff.

 

[Geoff]

Pavel,

Just to add to my last message...I told you that it now sometimes puts the
day after 'weekstarting' in the 'day' field with your new code, rather than
the same as 'weekstarting'. Well it's actually doing both, depending on how
it feels it seems. So more often than not it works fine but sometimes it
puts the same as 'weekstarting' in each row and sometimes the day after.
Remember that I've had to put the code in the 'before insert' box for the
subform though because that's the only way it'll work at all.

One possible clue is that it seems to only go wrong on the first and
sometimes second week of each month. It did that for about 15 test months
where I just created new record after new record to try and see any pattern
to this. However just once in about 15 months it went wrong for the 2nd week
of a month and not the first! I really can't see any other pattern to this
though. I may not understand the code very well but I'm quite into computers
otherwise and this sort of unpredictable error doesn't make much sense.

Anyway, I just wanted to update you on that in case it helps.

Cheers Pavel,
Geoff.

 

[Geoff]

Hi again Pavel,

I've fixed it! I wanted to let you know as soon as I sorted it out so
hopefully you won't go to the trouble of trying to see what was wrong
yourself.

I've actually changed the code that enters the date automatically
completely. It uses an invisible text box that I've put on the subform for
other reasons (for calculations I'm doing on the main form), which just
counts the number of records in the subform. That gave me the idea of doing
the dates using that too, and to my amazement, it worked the first time! I'm
still using some of your code though so it's still really helped. This is
what I've got now, and it's in the 'before insert' bit of the subform...

If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Select the record in the main form first."
Else
If Me.NewRecord Then
day = Parent!weekstarting + [counter]
End If
End If


That's it, so I'm using my new 'counter' field to work out the date.

Thanks for all your help with this Pavel, I appreciate it very much.

Kind regards,
Geoff.

 

[Pavel Romashkin]

Well, I am glad you got it to work, although having a field that serves
no other purpose that to ba a counter is not my favorite thing to do.
I think the next step in solving the original problem would be my asking
to E-mail me the form in question to look at, anyway.
Good luck,
Pavel

 

[Geoff]

Well, I am glad you got it to work, although having a field that serves
no other purpose that to ba a counter is not my favorite thing to do.
I think the next step in solving the original problem would be my asking
to E-mail me the form in question to look at, anyway.
Good luck,
Pavel

Hi Pavel. Oh no, that field isn't there just to serve as a counter for that
calculation. I put it there because I needed a counter for another
calculation, and that's what made me think of using it for this original
problem too. Maybe I could still have done both of the calculations in a
different way but I'm not that bothered now. The database is only for me and
maybe a few family/friends so it isn't too important.

Cheers,
Geoff.