Update Query Not Picking Up Date

[Dave177]

This is slightly weird but is probably due to some scrappy coding:

I've got a form with a multiselect list box. A user would enter details of guests' trip in text boxes and select the names of the guests from the list box, and press the BOOK button with the following code:

Private Sub cmdBook_Click()

   Dim lbxA As ListBox, lbxB As ListBox, SQL As String, DQ As String, itm
   Dim Arrive As Date
   
  
  DoCmd.SetWarnings False
   
   If Me.lstGuests.ItemsSelected.Count = 0 Then
      MsgBox "No Guest(s) Selected!"
      Exit Sub
      ElseIf IsNull(Me.txtArrivalDate) Then
      MsgBox ("Please Enter Arrival Date")
      Exit Sub
        ElseIf IsNull(Me.txtCostPerNight) Then
        MsgBox ("Please Enter Cost Per Night")
        Exit Sub
            ElseIf IsNull(Me.txtNoNights) Then
            MsgBox ("Please Enter Number Of Nights")
            Exit Sub
                ElseIf IsNull(Me.txtActivity) Then
                MsgBox ("Please Enter Activity Code")
                Exit Sub
                
            
        Arrive = Me.txtArrivalDate
        
      ElseIf Me.lstAuthorisers.ItemsSelected.Count = 0 Then
      MsgBox "Please Select an Authoriser"
      Exit Sub
   Else
      For Each itm In Me.lstGuests.ItemsSelected
         SQL = "INSERT INTO tblBookingsHolder " & _
         "(Doc,GuestID,CalderRef,RespCode,Activity,Funding,AuthoriserID,ArrivalDate, " & _
            "NoNights,CostPerNight,File) " & _
               "VALUES ('" & Me!txtDoc & "','" & Me.lstGuests.Column(0, itm) & "', " & _
               "'" & Me!txtCalderRef & "','" & Me!txtResp & "','" & Me!txtActivity & "', " & _
               "'" & Me!txtFunding & "','" & Me!txtAuthoriser & "',#" & Arrive & "#, " & _
               "'" & Me!txtNoNights & "','" & Me!txtCostPerNight & "','" & Me!txtFile & "');"
         DoCmd.RunSQL SQL
      Next
   End If

   MsgBox ("Record Saved")
  
   
   Me.lstGuests.RowSource = Me.lstGuests.RowSource
   Me.lstAuthorisers = Null
   Me.txtArrivalDate = Null
   Me.txtCalderRef = Null
   Me.txtCostPerNight = Null
   Me.txtNoNights = Null
   
   
   
       DoCmd.GoToRecord , , acNewRec
    Me.frmLastDocFile.Requery
    
    Me.txtDoc.DefaultValue = Me.txtDocRef + 1
    Me.txtFile.DefaultValue = """" & Me.txtFileRef & """"
    
    
    DoCmd.SetWarnings True
    
   

End Sub

Not sure why but when I look at the table [tblBookingsHolder] under the arrival date the date recorded is always "30/12/1899", regardless of the data entered in txtArrivalDate

This is driving me up the wall....

Dave

 

[PHV]

your code:
...
Exit Sub
Arrive = Me.txtArrivalDate
...
Obviously, Arrive is NEVER populated ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Golom]

Your indenting is throwing you off.

If Me.lstGuests.ItemsSelected.Count = 0 Then
    MsgBox "No Guest(s) Selected!"
    Exit Sub
ElseIf IsNull(Me.txtArrivalDate) Then
    MsgBox ("Please Enter Arrival Date")
    Exit Sub
ElseIf IsNull(Me.txtCostPerNight) Then
    MsgBox ("Please Enter Cost Per Night")
    Exit Sub
ElseIf IsNull(Me.txtNoNights) Then
    MsgBox ("Please Enter Number Of Nights")
    Exit Sub
ElseIf IsNull(Me.txtActivity) Then
    MsgBox ("Please Enter Activity Code")
    Exit Sub


    [red]Arrive = Me.txtArrivalDate[/red]

ElseIf Me.lstAuthorisers.ItemsSelected.Count = 0 Then
    MsgBox "Please Select an Authoriser"
    Exit Sub
Else
    For Each itm In Me.lstGuests.ItemsSelected
        SQL = "INSERT INTO tblBookingsHolder " & _
              "(Doc,GuestID,CalderRef,RespCode,Activity,Funding,AuthoriserID,ArrivalDate, " & _
              "NoNights,CostPerNight,File) " & _
              "VALUES ('" & Me!txtDoc & "','" & Me.lstGuests.Column(0, itm) & "', " & _
              "'" & Me!txtCalderRef & "','" & Me!txtResp & "','" & Me!txtActivity & "', " & _
              "'" & Me!txtFunding & "','" & Me!txtAuthoriser & "',#" & Arrive & "#, " & _
              "'" & Me!txtNoNights & "','" & Me!txtCostPerNight & "','" & Me!txtFile & "');"
        DoCmd.RunSQL SQL
    Next
End If

The code in [red]red[/red] will never run because it follows an "Exit Sub". Move it before the "For Each ... " statement.

 

[Dave177]

PHV & Golom,
Thank you very much!

Dave