Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Continuous sub form limitations (2000)

    Hi,
    I have a continuous sub-form that records the movement of material. When its in and out of the office.
    The fields consist of MvtDateOut,MvtDetails, MvtDateBack, MvtDetailsBack, in order.
    If material is sent out, data is recorded in the first two fields, and upon its return, recorded in the last two. I need these field to be filled in.


    Currently I have this:
    Me.AllowAdditions = False
    On the On Change event of the form.

    And this:
    If IsNull([MvtDateBack]) Or IsNull([MvtDateOut]) Or IsNull([MvtDetails]) Or IsNull([Mvtdetailsback]) Then
    MsgBox "Please record all the details", vbExclamation & vbOKOnly, "Error!"
    Else
    Me.AllowAdditions = True
    Me.MvtDetails.SetFocus
    End If
    End Sub
    On the after update event on the last field.

    I'm trying to limit people from recording new material movement, however this doesn't seem to work.
    I need AllowAdditions to equal true when a person first registers a new record so that they can record the location of the material. But I also need to limit people recording a new movement unless the previous movement cycle has been completed.

    I suspect this is a simple error, but you know what they say about woods and trees................

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Continuous sub form limitations (2000)

    I have never heard of an On Change event of a form.

    You should set the Required property of the MvtDateOut and MvtDetails fields to Yes. These two fields should always be filled in.

    Do you want to prevent the users from adding a new record as long as there is a record in which MvtDateBack and/or MvtDetailsBack is blank?

    By the way, why do you use a continuous form? From your description, I would guess that the users only need to see the last record, not all records.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Continuous sub form limitations (2000)

    I do need to prevent a user from entering a further record if MvtDateBack and MvtDetailsBack are blank.
    The On Change event is on MvtDetails as opposed to the form, it escapes me as to why I have put it on this. I'm guessing that it is because it is the first field entered.
    Also, the continuous form is needed as the material is of a sensitive nature, so people need to view the previous movement history.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Continuous sub form limitations (2000)

    I presume this is checking for Nulls in the two fields MvtDateOut and MvtDetails. Should this be in the other fields, MvtDateback and MvtDetailsback. Which are the last fields to be filled.
    Also this doesn't resolve the issue of when a new record is added, as far as my limited knowledge of coding goes, AllowAdditions will only be set to true if these fields are entered. When a new record is entered, no data exists until movement is recorded, but the user won't be able to add any data as AllowAdditions is false................i think. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Continuous sub form limitations (2000)

    Edited by HansV to correct errors in code

    Put the following procedure in the form module:

    Private Sub HandleAdditions()
    On Error GoTo ErrHandler

    If Me.Dirty Then RunCommand acCmdSaveRecord
    Me.AllowAdditions = (DCount("*", "tblSomething", "MvtDateBack Is Null OR MvtDetailsBack Is Null") = 0)
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    End Sub

    where tblSomething is the name of the table. Call this procedure in the On Current event of the form and in the After Update event of MvtDateBack and MvtDetailsBack:

    Private Sub Form_Current()
    HandleAdditions
    End Sub

    Private Sub MvtDateBack_AfterUpdate()
    HandleAdditions
    End Sub

    Private Sub MvtDetailsBack_AfterUpdate()
    HandleAdditions
    End Sub

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Continuous sub form limitations (2000)

    Sorry, I pasted the wrong field names into the code. I have corrected my reply. The code should take care of everything - it works fine in a test for me (with different field names, obviously)

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Continuous sub form limitations (2000)

    Long code fragment moved to attachment by HansV

    The add button is held on the main form, this opens a form where the user selects the material type, and the quantity, and then they click on an 'OK' control button with the following coding:

    This then records the entries selected in frmInterviewsSub1. So if 4 copies were selected, 4 entries would be displayed. The movement details are then linked to each copy and recorded in frmInterviewsSub1Sub2.

    I think this makes sense.

    Would this make a difference to the Sub as its still not displaying an entry to record movement?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Continuous sub form limitations (2000)

    I don't have the slightest idea what this has to do with the original question in this thread, sorry.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Continuous sub form limitations (2000)

    Sorry.
    I did'nt know whether the reason why the coding wasn't working was to do with the way that I had my forms set up.
    Obviously if the coding works on your form, then there must be an issue with mine, and I did'nt know if this was to do with the method that the form was opened.

Posting Permissions

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