Need help with Visual basic problem in Access 2000/XP

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am having trouble using isual Basic in Microsoft Access 2000 and XP
The code belows shows what I am trying to achieve - what I am doing is a rental system for a video store, i have the default value of my "Date Borrowed" field set as Date() and I have a "Date Due Back" field.
The date due back is set at one month after the video is borrowed, unless this falls on a saturday or sunday, in which case it is due back on the following monday.
Hopefully it is clear what I am trying to achieve from the code below as i have not encountered a problem with this as of yet.

What I am having the trouble with is, firstly, when I open the form the Date Due Back value is calculated and inserted into the field automatically, but then on the second, third and subsequent records the field remains blank.
Also, when I reopen the form on another day, the first record I have stored is displayed first, as I have each rental stored by Autonumber, and the form automatically calculates a new Date Due Back, based on the current date - Date() - which overwrites the date that was previously saved and is saved in my table. I then get the problem described above, where the Date Due Back is not calculated for any subsequent records, so these fields remain blank.

What I would like is, firstly, for the Date Due back to be calculated for every field, not just for the first record the form opens, and secondly when the form is reopened at a later date I would like any values previously entered in this field to remain and to not be overwritten.

I have a feeling that some of my problem may be the fact that I have this code under the Form_Load() command, but I think the entire problem may be more complicated than that.


If anyone can help in anyway I would greatly apprciate it as I have no more ideas as to what I can do!! :-(


THE CODE I AM USING IS AS FOLLOWS:-


Private Sub Form_Load()
due = FormatDateTime(DateAdd("M", 1, Now()), vbShortDate)
ddd = Weekday(due)

Select Case ddd
Case 1
DateDueBack.Value = FormatDateTime(DateAdd("D", 1, due), vbShortDate)
Case 7
DateDueBack.Value = FormatDateTime(DateAdd("D", 2, due), vbShortDate)
Case 2 To 6
DateDueBack.Value = FormatDateTime(due, vbShortDate)

End Select



Refresh
End Sub
 
Leigh said:
I am having trouble using isual Basic in Microsoft Access 2000 and XP
The code belows shows what I am trying to achieve - what I am doing is a rental system for a video store, i have the default value of my "Date Borrowed" field set as Date() and I have a "Date Due Back" field.
The date due back is set at one month after the video is borrowed, unless this falls on a saturday or sunday, in which case it is due back on the following monday.
Hopefully it is clear what I am trying to achieve from the code below as i have not encountered a problem with this as of yet.

What I am having the trouble with is, firstly, when I open the form the Date Due Back value is calculated and inserted into the field automatically, but then on the second, third and subsequent records the field remains blank.
Also, when I reopen the form on another day, the first record I have stored is displayed first, as I have each rental stored by Autonumber, and the form automatically calculates a new Date Due Back, based on the current date - Date() - which overwrites the date that was previously saved and is saved in my table. I then get the problem described above, where the Date Due Back is not calculated for any subsequent records, so these fields remain blank.

What I would like is, firstly, for the Date Due back to be calculated for every field, not just for the first record the form opens, and secondly when the form is reopened at a later date I would like any values previously entered in this field to remain and to not be overwritten.

I have a feeling that some of my problem may be the fact that I have this code under the Form_Load() command, but I think the entire problem may be more complicated than that.


If anyone can help in anyway I would greatly apprciate it as I have no more ideas as to what I can do!! :-(


THE CODE I AM USING IS AS FOLLOWS:-


Private Sub Form_Load()
due = FormatDateTime(DateAdd("M", 1, Now()), vbShortDate)
ddd = Weekday(due)

Select Case ddd
Case 1
DateDueBack.Value = FormatDateTime(DateAdd("D", 1, due), vbShortDate)
Case 7
DateDueBack.Value = FormatDateTime(DateAdd("D", 2, due), vbShortDate)
Case 2 To 6
DateDueBack.Value = FormatDateTime(due, vbShortDate)

End Select
Click to expand...


Instead of using Now to calculate Due, you should use the
[Date Borrowed] field.

Note that you should not be formatting the dates in these
calculations, just assign the controls/fields/variables the
date values. You can set the text box's Format property to
display the dates in the desired format. This way you won't
be constantly converting back and forth between date values
and strings that look like a date.

If you form is opened in Single Form view, then you should
place the code in the Current event instead of the Load
event.

If you have the form opened as a Continuous form, then you
need to calculate the Due date in its text box's control
source expression. You can do this by moving the code to a
public function in a standard module:

Public Function CalcDue(DtOut As Date) As Date
Dim DtDue As Date

DtDue = DateAdd("m", 1, DtOut)
Select Case DatePart("w", DtOut)
Case 1
CalcDue= DateAdd("d", 1, DtDue)
Case 7
CalcDue= DateAdd("d", 2, DtDue)
Case 2 To 6
CalcDue=DtDue
End Select
End Sub

Then use =CalcDue([Date Borrowed]) in the DateDueBack text
box.
 
Back
Top