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

    Unanswered: Form Problem--urgent help needed!!

    I have a form with several text boxes TART_DATE_TIME, END_DATE_TIME, ITEM_NAME.
    THe user will use this form to make new reservations. He selects an item and enters a start date/time and an end date/time. If the time is taken a message will be displayed "already taken" else the record will be added to the HISTORY table.
    PROBLEM: 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 still be added in the history table. How do I avoid that?
    The HISTORY table is suppose to have the history of successful reservations only.

    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

  2. #2
    Join Date
    Feb 2004
    Location
    Swindon, UK
    Posts
    86
    Set the form to unbound and add the data via code, as follows

    Private Sub EnterRecord_Click()

    Dim wsp As Workspace
    Dim dbs As Database
    Dim rst As Recordset
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim LowMsg, AvgMsg, HighMsg, MsgType

    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

    Set dbs = CurrentDb

    Set wsp = DBEngine.Workspaces(0)
    Set dbs = wsp.OpenDatabase("ServerPath\FileName.mdb")

    Set rst = dbs.OpenRecordset("tbl-TableName", dbOpenTable)
    With rst
    .AddNew
    !StartTime= Text1.Value
    !EndTime = Text2.Value
    !ItemName = Text3.Value
    End With

    Else MsgBox "HI"
    MsgBox " " & varX & " "
    'MsgBox "record not found, so you can add and reservation will be successful"

    End Sub
    "Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done."

  3. #3
    Join Date
    May 2004
    Posts
    55
    Isn't there an easier way to do this? i am not familiar with workspace and some of the other terms you used. Also, where do I put this code??

    Thanks,
    D

  4. #4
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote Originally Posted by dmesh
    Isn't there an easier way to do this? i am not familiar with workspace and some of the other terms you used. Also, where do I put this code??

    Thanks,
    D
    Am I reading right. The person enters a date/time and if that is taken a mesage comes up saying that date/time has been allocated but a new record has been made because the person has entered the date/time.

    Then if so a simple way is to have a couple of unbound text boxes on your form where the date/time is entered and your system for producing a message if that date/time has been allocated is altered so that if refers to the couple of unbound text boxes.

    Are you familiar with SetValue macro action? If not the SetValue places values into text boxes. They are very simple to make.

    If you were to semi automate (so as to try things out) then you would place a macro on a label or button that the person could click and a new record would be created IF the message "already taken" did not come up.

    If I am reading you right then the fundamental problem you have is that yoy are triggereing the message by making a new record. The record needs to be created after the message action has run or not run as the case may be.

    Mike

  5. #5
    Join Date
    May 2004
    Posts
    55
    Quote Originally Posted by Mike375
    Am I reading right. The person enters a date/time and if that is taken a mesage comes up saying that date/time has been allocated but a new record has been made because the person has entered the date/time.

    Then if so a simple way is to have a couple of unbound text boxes on your form where the date/time is entered and your system for producing a message if that date/time has been allocated is altered so that if refers to the couple of unbound text boxes.

    Are you familiar with SetValue macro action? If not the SetValue places values into text boxes. They are very simple to make.

    If you were to semi automate (so as to try things out) then you would place a macro on a label or button that the person could click and a new record would be created IF the message "already taken" did not come up.

    If I am reading you right then the fundamental problem you have is that yoy are triggereing the message by making a new record. The record needs to be created after the message action has run or not run as the case may be.

    Mike
    I understand what you are saying but the problem is that the record source for that form is the table HISTORY. So after the message "already taken" is displayed, the record will be saved in the HISTORY table. The form has a save button and all the above code is ran when the save button is clicked.
    Is this a good approach?

  6. #6
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    But if you enter your date/time on unbound text boxes it does matter.

    So assume you cal alter things so the check on history is doen against the entries on the unbound text boxes then:

    If no message a macro runs and opens a form based on the same table and for a new record and then Setvalue sticks in the appropriate data. If a message that date/time has been allocated then no running of the macro,

    Alternatively you could do it the way your are doing it and if the message box is triggered then it also triggers are delete of the new record.

    You could in fact have a SetValue that runs when the message box opens that places a value in the new record that you don't and a delete query runs and deletes all records with such an entry, say when you close the form.

    By the way, is the message box only triggered for date/time allocated when it is an exact match or do use some bracket on "time", that is, a minimum time either side of the time that is already in the history table.

    Mike

  7. #7
    Join Date
    Nov 2003
    Posts
    1,487
    DoCmd.RunCommand acCmdUndo


Posting Permissions

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