Code overwiting existing records

[hardyd44]

Hi,

I found while testing this database before letting my users at it, after a couple of months work off and on.

The problem:

This data base is a holiday and absence control database and the problem is when I have entered a absence in a form using the code below to generate the records for each days absence all works as expected - however if the databse is closed and reopened and more absences are entered the first record is overtyped with the first record of a new absence - hope that makes sense, this is obvously not acceptable :-(

Guys (and Girls!) can you get me out of the mire once again and have a quick look and tell me how to stop this happening.

The Code:
rivate Sub cmdOK_Click()

'sets various values

StartD = txtStartDate.Value
EndD = txtEndDate.Value
DepotOrDept = txtDepot.Value
FullName = txtName.Value
Absencetype = txtType.Value
DateAuthorised = TxtEntereddate.Value
' AuthorisedBy = txtAuthorisedBy.Value

'Checks all fields are completed
If IsNull(txtEndDate.Value) Then
MsgBox "All Fields Must be completed- Please reselect"
Exit Sub
End If
If IsNull(txtStartDate.Value) Then
MsgBox "All Fields Must be completed- Please reselect"
Exit Sub
End If
If IsNull(txtName.Value) Then
MsgBox "All Fields Must be completed- Please reselect"
Exit Sub
End If
If IsNull(txtType.Value) Then
MsgBox "All Fields Must be completed- Please reselect"
Exit Sub
End If
' If IsNull(txtAuthorisedBy.Value) Then
' MsgBox "All Fields Must be completed- Please reselect"
' Exit Sub
' End If
If Calendar5.Value < txtStartDate.Value Then
MsgBox ("Finish Date must be later Than Start Date - Please reselect")
Calendar5.Visible = True
Exit Sub
End If

'ask user if all details are correct
If MsgBox("Are all Details correct ?", _
vbQuestion + vbYesNo, "Data Check") = vbNo Then
Exit Sub
End If

'generates absences
For counter = StartD To EndD Step 1
txtStartDate.Value = counter


'checks for weekends
If Weekday(counter, vbMonday) = 6 Then
counter = counter
ElseIf Weekday(counter, vbMonday) = 7 Then
counter = counter
'checks for Bank Holidays from Tbl_bank holidays'
ElseIf counter = DLookup("date", "[Tbl_bank holidays]", "Date = #" & counter & "#") Then
counter = counter

Else
'Found no excluded dates and creates record(s)

txtDateOff.Value = counter
txtDepot.Value = DepotOrDept
txtName.Value = FullName
txtType.Value = Absencetype
TxtEntereddate.Value = Now()
' txtAuthorisedBy.Value = AuthorisedBy
DoCmd.GoToRecord , , acNewRec
End If
Next counter

txtStartDate.Value = ""
txtEndDate.Value = ""
txtDepot.Value = ""
txtName.Value = ""
txtType.Value = ""
txtAuthorisedBy.Value = ""
'refresh various tables fed from Inputed_absences
DoCmd.RunMacro "AutoExec"
MsgBox "Absences added, click OK to continue!", vbOKOnly
On Error GoTo Error_Exit
Error_Exit:
Exit Sub


End Sub

NB the txtauthorised.value is commented out as this was only added to the first record of a new absence set and not all like the rest of the values are, but that I can live with for now

Thanks

Dean

 

[hardyd44]

Sorry !!

Guys,

sorry should have looked around a bit more before posting - have changed the form to data entry= yes, this seems to have cured the problem, however now getting blank record instead - this does not seem to cause any problems but is a bit untidy, any suggestions???

also still have the problem of the authorisedby text anly appearing on the first record when uncommented

so a much smaller problem - but still there

Ta

Dean

 

[hardyd44]

thank you

Pat - thanks for your help, I just changed the table to make the fields required. I will be honest the rest just went over my head - you are right I do tend to treat VB as macro's - but I am still learning (this DB is my first using VB).
However I am still for some reason it still only enters the authorisedby.value in the first record created and not the rest that are created at the same time. If I change the table to required = yes, the script fails.

Any clues?

in words of less than one syalble if possible

Thanks again

Dean