Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    55

    Unanswered: Query Problem--Need Help!!!!

    I have a form with a save button on it. I have several text boxes on the form like
    START_DATE_TIME, END_DATE_TIME, USER, ITEM. The user picks an item and enters a start date time and an end date time.
    If the time is already taken the user won't be able to make the reservation else it will be a successful reservation and the record will be stored in the table.

    This is what I have for the code:It works fine but it stores the record in the table even when the time selected in unavailable i.e. it displays the message"Already Taken" and then stores it in the table. I would like to display the message and do nothing.


    Private Sub Command18_Click()
    On Error GoTo Err_Command18_Click
    Dim varX As Variant




    varX = DLookup("[ID]", "HISTORY", "([ITEM_NAME] = """ & Forms!HISTORY1!ITEM_NAME & _
    """ AND [START_DATE_TIME] > #" & Forms!HISTORY1!START_DATE_TIME & _
    "# AND [START_DATE_TIME] < #" & Forms!HISTORY1!END_DATE_TIME & "#) " & _
    "OR ([ITEM_NAME] = """ & Forms!HISTORY1!ITEM_NAME & _
    """ AND [END_DATE_TIME] > #" & Forms!HISTORY1!START_DATE_TIME & _
    "# AND [END_DATE_TIME] < #" & Forms!HISTORY1!END_DATE_TIME & "#) " & _
    "OR ([ITEM_NAME] = """ & Forms!HISTORY1!ITEM_NAME & _
    """ AND [START_DATE_TIME] = #" & Forms!HISTORY1!START_DATE_TIME & _
    "# AND [END_DATE_TIME] = #" & Forms!HISTORY1!END_DATE_TIME & "#)")

    'var = DLookup("[ID]", "HISTORY1", "[ITEM_NAME] = "" & Forms!HISTORY1!ITEM_NAME & "" ")
    'If Forms!HISTORY1!START_DATE < Date Then MsgBox "e" Else

    If IsNull(varX) Then DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 Else MsgBox "Already Taken"




    Exit_Command18_Click:



    Exit Sub

    Err_Command18_Click:
    MsgBox Err.Description
    Resume Exit_Command18_Click

    End Sub

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Try putting a "MsgBox varX" before the last If statement.

    Perhaps it's not containing what you expect it to.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    May 2004
    Posts
    55
    Quote Originally Posted by Teddy
    Try putting a "MsgBox varX" before the last If statement.

    Perhaps it's not containing what you expect it to.

    I did. The query is working fine. It will show the appropriate message. But if the reservation is not successful, after the message is displayed it is not suppose to add that record to the HISTORY table. It does that anyway. The HISTORY table had a record of successful reservations only. Am I missing a statement here.

    For example if the user clicks on new reservation and enters a date that is taken he will receive a message "already taken" but the record will be added in the history table. How do I avoid that?

    Private Sub Command18_Click()
    On Error GoTo Err_Command18_Click
    Dim varX As Variant




    varX = DLookup("[ID]", "HISTORY", "([ITEM_NAME] = """ & Forms!HISTORY1!ITEM_NAME & _
    """ AND [START_DATE_TIME] >= #" & Forms!HISTORY1!START_DATE_TIME & _
    "# AND [START_DATE_TIME] < #" & Forms!HISTORY1!END_DATE_TIME & "#) " & _
    "OR ([ITEM_NAME] = """ & Forms!HISTORY1!ITEM_NAME & _
    """ AND [END_DATE_TIME] > #" & Forms!HISTORY1!START_DATE_TIME & _
    "# AND [END_DATE_TIME] <= #" & Forms!HISTORY1!END_DATE_TIME & "#) " & _
    "OR ([ITEM_NAME] = """ & Forms!HISTORY1!ITEM_NAME & _
    """ AND [START_DATE_TIME] = #" & Forms!HISTORY1!START_DATE_TIME & _
    "# AND [END_DATE_TIME] = #" & Forms!HISTORY1!END_DATE_TIME & "#)")

    'var = DLookup("[ID]", "HISTORY1", "[ITEM_NAME] = "" & Forms!HISTORY1!ITEM_NAME & "" ")
    'If Forms!HISTORY1!START_DATE < Date Then MsgBox "e" Else

    If IsNull(varX) Then DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 Else MsgBox "HI"
    MsgBox " " & varX & " "
    'MsgBox "record not found, so you can add and reservation will be successful"







    Exit Sub

    Exit_Command18_Click:



    Exit Sub

    Err_Command18_Click:
    MsgBox Err.Description
    Resume Exit_Command18_Click

    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •