Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2004
    Posts
    492

    Unanswered: Only allow one active record

    Hi all - need some access advice if someone can help out.

    I have a table which amounts to employee assignments. There is an end-date column, and an employee is only allowed to have one active assignment at a time.

    Its pretty simple - there are 2 combo boxes for the assignment, and an end-date field. What is the best event to use that a person cannot move to the next empty box to enter a record if there is still an "active" record?

    I'll admit I dont really know VBA at all, so I still need to figure out how to code a query to go and check and return an error. I am ok with the query itself, its just actually getting it in VBA.
    Oracle OCPI (Certified Practicing Idiot)

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Are you entering data directly into a table or through a form?
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Jan 2004
    Posts
    492
    It will be through a form. I will be running a query and counting how many records return - if > 0 then I want to raise an error and not let a new record be created, otherwise continue.
    Oracle OCPI (Certified Practicing Idiot)

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    Off hand...I'ld say try using the Form's BeforeUpdate event. If not all fields are filled then Cancel (Cancel = True) the update and set Focus on the field that requires entry.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  5. #5
    Join Date
    Jan 2004
    Posts
    492
    Ok great - i'll try that and see how it works. Thanks!
    Oracle OCPI (Certified Practicing Idiot)

  6. #6
    Join Date
    Jan 2004
    Posts
    492
    Ok well I admit I have no clue how to do this - set up the query fine and it executes as expected. I created 3 parameters for an employee_id, and 2 date fields inside of that query. The query will return one field, and it will just be a count of records.

    From what I've read on the web, I need a querydef thing, but I cannot seem to get more than small snippets of code - none of which really goes into depth of what to do once you call the query. Does anyone have a working link or piece of code to call the query, pass in the parameters, and then use the value returned as validation?

    Any help is appreciated - I will be glad to help you with anything Oracle related in the future if need be
    Oracle OCPI (Certified Practicing Idiot)

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Counting records

    If you are opening a form to edit/add new records ('assignment' form), on the form which has the button you click to open the 'assignment' form, you can try putting in something like the following (this is ADO and the syntax may not be totally correct and others might have some better suggestions):

    Private cmdAddRecord_click()
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select SomeIDField from EmployeeAssignmentTable where EmployeeID = " & me!ComboEmployeeID & "
    'NOTE: you would have a combo box called ComboEmployeeID where they would select the EmployeeID before clicking the cmdAddRecord button
    rs.open strSQL,currentproject.connection,adopenkeyset,adop enreadonly
    if rs.eof and rs.bof then 'i.e. there are no records
    rs.close
    set rs = nothing
    docmd.openform "EmployeeAssignmentRecordForm",,"EmployeeID = " & ComboEmployeeID & ""
    Forms!EmployeeAssignmentRecordForm!AllowAdditions = true
    Forms!EmployeeAssignmentRecordForm!EmployeeID = Forms!CalledFromForm!ComboEmployeeID 'Note: The form which has the button is called CalledFromForm - this sets the EmployeeID value to the EmployeeID combo box selected on that form
    else
    rs.close
    set rs = nothing
    docmd.openform "EmployeeAssignmentRecordForm",,"EmployeeID = " & ComboEmployeeID & ""
    Forms!EmployeeAssignmentRecordForm!AllowAdditions = false
    end if

    Basically what it's doing is checking to see if the selected EmployeeID has records in the 'assignment' table when you click the button to open the 'assignment' form. If there are no records in that table (rs.eof and rs.bof) then open the form and set the allowadditions to true, otherwise open the form and set the allowadditions to false. This code may need a little tweeking as it's been about 3-4 months since I've worked in MSAccess. I also may be off-base on what you're trying to do but this was what I could think of (and I can't remember if you can set the allowadditions to true or false after you open the form). Like I said though, someone else might have a better suggestion.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Jan 2004
    Posts
    492
    Cool - let me play around and I'll see what I can come up with. I appreciate the code sample very much!
    Oracle OCPI (Certified Practicing Idiot)

  9. #9
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Hi ss659,

    This might help. Quick, small and simple but not sure if the best solution. What I did was create a form with some fields. EndDate is a date field and UserName is a text field that comes right after the EndDate field. This code will not let you enter the Name field if there is a Date in the EndDate field, displays a MessageBox to let you know that, then moves back to the EndDate field. As such, if the EndDate field is blank it will let you enter and update the Name field. See if you like it. Use it or toss it.

    Code:
    Private Sub UserName_Enter()
    
    If Me.EndDate <> "" Then
        MsgBox "Finish this assignment first", vbOKOnly, "Go complete assignment!"
        Me.EndDate.SetFocus
        End If
    End Sub
    Have a nice one,
    BUD

  10. #10
    Join Date
    Jan 2004
    Posts
    492
    Well let me post a quick diagram of what I'm trying to accomplish - not sure if your idea will work, but thanks a bunch for the ideas.

    Code:
    Assignment   Start Date    End Date
    -------------------------------------------------------
    CLERK        01-JAN-1985   01-JAN-2004
    ACCOUNTANT   02-JAN-2005  
    CEO          04-JUL-2006   <= Want to prevent someone from doing this
                                  b/c last assignment was not end-dated
    Ok so this is a detail form for one specific user. You pick an assignment, and the start date defaults to today, but may be changed. As you can see, I want to prevent a user from entering the 3rd record, because the 2nd record is still active. Now if they go and end-date the 2nd record to 03-JUL-2006 or before, then it should allow the creation of a new record because there is no overlapping.

    I have a query I stole from an Oracle form that returns a count - anything greater than 0 means there is an overlap, and I need to return an error at that point. The query works great and will definitely provide the correct validation - the issue is I'm not familiar at all with VBA, and most of the code I always see referenced has you hard-coding the query right into the procedure. This seems extremely messy to me and I would like to avoid it if possible.

    I will work on this tomorrow with the samples provided and see - this is sort of a side-project for me, so it takes longer than it normally would.
    Oracle OCPI (Certified Practicing Idiot)

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Not allowing records to be added

    I know this is kind of late but if you're still looking for a solution you could try this:

    Public Function CanAdd() as boolean
    Dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from MyTable where EndDate = """
    rs.open strSQL,currentproject.connection,adopenkeyset,adlo ckreadonly
    if rs.eof and rs.bof then
    CanAdd = true
    else
    CanAdd = false
    end if
    rs.close
    set rs = nothing

    then on your form when the user is trying to close/go to another record:
    if CanAdd = false then
    msgbox "You cannot go to another record until you fill in the EndDate on such a such record."
    docmd.cancelevent
    end if

    I'm guessing from your last post that if there are any records in the table without an EndDate then you don't want the user to add another record.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Ok so this is a detail form for one specific user. You pick an assignment, and the start date defaults to today, but may be changed
    I take this to mean only one user ever enters records on this form. Is this an accurate reading of your statement? If so, is this form the only one writing data to the table, or are other users with other forms doing the same thing? And if this is so, is there a field to ID the user?

    I think I've got a simple solution, depending on your answers.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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