Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Apr 2003
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    exiting counter (access 97/office 98)

    I am having trouble exiting the loop so the user can go back and correct the incorrect PCA in the table. I am also having trouble holding the variable to direct the user which PCA is incorrect. Any ideas?

    Dim dbs As Database
    Dim rst As Recordset
    Dim mbatch_seq As Integer
    Dim cancel As Boolean
    Dim cpca As Integer


    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("rents")

    cancel = False
    mbatch_seq = 0
    PROJCOST = cpca

    Do While Not rst.EOF

    mbatch_seq = mbatch_seq + 1

    With rst
    .Edit

    THIS SECTION CHECKS THAT THE PCA HAS BEEN ENTERED.
    If IsNull(Forms!frmrents!PROJCOST) Then
    MsgBox "A valid PCA must be supplied! & cpca ", vbOKOnly + vbExclamation, "Please correct it."
    cancel = True
    End If

    'THIS SECTION CHECKS THE FFY - PCA COMBO AGAINST THE PCA TABLE FOR VALIDITY
    If (IsNull(DLookup("[FFY_PCA]& [ORG_CODE]", "PCA_TABLE", "[FFY_PCA]& [ORG_CODE] ='" & Me!TXTFC & Me!TXTFY & Me!PROJCOST & "'"))) Then
    MsgBox ("THIS IS NOT A VALID PCA FOR THE FISCAL YEAR AND/OR ORG - PLEASE CORRECT! & cpca "), vbExclamation, "PCA/FISCAL YEAR COMBINATION ERROR!"
    cancel = True
    Me!PROJCOST.Undo
    End If
    .Update
    .MoveNext
    End With
    Loop


    If cancel = True Then
    MsgBox ("A correct PCA must be supplied")
    End If




    DoCmd.OpenForm "frmpmtdate", acNormal, "", "", , acNormal

    End Sub

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

    Re: exiting counter (access 97/office 98)

    I'm confused by your code. You lfoop through the records of a recordset (rst), and initiate an edit of each record, but you don't set any field of the recordset or even refer to a field of the recordset between .Edit and .Update. You do refer to controls on a form (or forms) but there is no (direct) relation with the recordset.

    So I guess you left something out.

    Note: even if the "rents" table is the Record Source of the form, looping through the records of "rents" has no relation to the form.

  3. #3
    Lounger
    Join Date
    Apr 2003
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: exiting counter (access 97/office 98)

    Rents is the table and frmrents is the form. I have a command button on the frmrents that when clicked looks at the table rents to verify PCA's. I thought I needed to use edit and update when using rst.eof. I wasn't sure how to loop through records in a table in access without the recordset. The control source for the form (frmrents) is "Rents" and the field is PROJCOST . I can verify this information on a On Lost Focus or On Exit event after the user keys in the PROJCOST, but seem to be at a lost as to how to do it when the table is already completely populated. Perhaps I need a redirection?

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

    Re: exiting counter (access 97/office 98)

    The usual place to validate the value entered by the user is in the BeforeUpdate event of a control or of the form as a whole. This event has a Cancel argument that can be set to True to cancel the update. I don't see why you would want to loop through the records of "rents", probably you don't need it at all.

    Code could look like this:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    'THIS SECTION CHECKS THAT THE PCA HAS BEEN ENTERED.
    If IsNull(PROJCOST) Then
    MsgBox "A valid PCA must be supplied!", vbExclamation, "Please correct it."
    PROJCOST.SetFocus
    Cancel = True
    Exit Sub
    End If

    'THIS SECTION CHECKS THE FFY - PCA COMBO AGAINST THE PCA TABLE FOR VALIDITY
    If (IsNull(DLookup("[FFY_PCA]& [ORG_CODE]", "PCA_TABLE", "[FFY_PCA]& [ORG_CODE] ='" & _
    Me!TXTFC & Me!TXTFY & Me!PROJCOST & "'"))) Then
    MsgBox ("THIS IS NOT A VALID PCA FOR THE FISCAL YEAR AND/OR ORG - PLEASE CORRECT!"), _
    vbExclamation, "PCA/FISCAL YEAR COMBINATION ERROR!"
    Cancel = True
    PROJCOST.SetFocus
    End If
    End Sub

  5. #5
    Lounger
    Join Date
    Apr 2003
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: exiting counter (access 97/office 98)

    This code works if the user is inputing the PCA, but when the PCA is already there I need to loop through 126 records to check if the PCA is correct.

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

    Re: exiting counter (access 97/office 98)

    I don't understand. The code you posted at the beginning of this thread does nothing whatsoever with the records of the recordset. Can you explain why you need to loop through the records and what you want to check?

  7. #7
    Lounger
    Join Date
    Apr 2003
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: exiting counter (access 97/office 98)

    I have a table with PCA's in it and another table with PCA's in it. One is updated daily. The updated daily table verifies the PCAs in the static table. If a PCA is no longer valid a messagebox should come up and state... Incorrect PCA 67543. Please correct it. The user is not inputting the PCA's in the table they already exist. There are about 130 PCAs to be checked so I figured I would need to use a recordset and loop.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: exiting counter (access 97/office 98)

    I'm afraid your explanation didn't clear things up for me at all . What are PCAs and why do you have them in two different tables when good design requires only a single location? If the user isn't entering them, why do they need checking? If you fill in some of the background information, perhaps someone will be able to give you more help.
    Charlotte

  9. #9
    Lounger
    Join Date
    Apr 2003
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: exiting counter (access 97/office 98)

    Let me try another approach. I have a form bound to a table. The user can see what is in the table on the form. On the form I have a command button that I would like to click. On the click I have an event procedure that I would like to run. The event procedure would verify whether a fields input is valid. I am attempting to do this in a recordset statement. I can refill the field using ![fieldname] = "12345". However I am having a great deal of trouble verifying the field. If I am on a record I can verify that record but I can not seem to figure out how to get the event to move to the next record and check that record. I am using an if statement in side a recordset. It looks something like this:
    With rst

    'THIS SECTION CHECKS THAT THE PCA HAS BEEN ENTERED and IS A VALID PCA FOR THE FFY.
    If IsNull(PROJCOST) Then
    MsgBox "There is a field without a PCA. Please supply a valid PCA! ", vbOKOnly + vbExclamation, "Please correct it."
    Exit Do
    ElseIf IsNull(DLookup("[FFY_PCA]", "PCA_TABLE", "[FFY_PCA] ='" & "20" & "02" & Me!PROJCOST & "'")) Then
    MsgBox ("THIS IS NOT A VALID PCA FOR THE FISCAL YEAR AND/OR ORG - PLEASE CORRECT! " & cpca), vbExclamation, "PCA/FISCAL YEAR COMBINATION ERROR!"
    Exit Do
    End If
    End With

    Loop

    I have attempted to use the movefirst, movenext, edit and update but with no resolution. Any ideas on how I can get this code to work when clicking on a command button and using an event procedure? <img src=/S/brainwash.gif border=0 alt=brainwash width=15 height=15>

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

    Re: exiting counter (access 97/office 98)

    I am sorry to keep on repeating the same thing over and over again, but there is nothing in your sample code that has anything to do with a recordset, so it wouldn't make sense to use MoveFirst, MoveLast, MovePrevious, MoveNext, AddNew, Edit, Update or any other method of a recordset.

    The DLookup will check the entire table PCA_TABLE at once for a match "[FFY_PCA] ='" & "20" & "02" & Me!PROJCOST & "'", so it seems to me that there is no need to loop through the records of that table, unless there is something that you haven't told us.

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: exiting counter (access 97/office 98)

    It seems like we're not communicating very well. Can you explain what you mean by "verifying" the input? If what you're doing is testing the value they entered to see if it exists in a different table, your statement IsNull(DLookup("[FFY_PCA]", "PCA_TABLE", "[FFY_PCA] ='" & "20" & "02" & Me!PROJCOST & "'")) is all you need for the validation. You don't need to use a recordset or loop through anything because the dlookup has checked the PCA_TABLE to see if the value "2002" plus whatever is in the current record in the PROJCOST control exists anywhere in that table. If it does, the value is valid, if not, not. You would ordinarily check this in the beforeupdate event of the form, not from a command button. I suspect that what you're trying to do is wait until the user clicks a button and then check a bunch of records, not just a single record, because otherwise your questions about looping through a recordset make no sense.

    The point that Hans and I are trying to make is that trying to do it this way is going about it backwards. Instead of waiting until they have already created a bunch of records and then testing to see if all of them are valid, check each record before you allow the user to save it. Then there is no need for a command button because the form will force the user to enter a valid value or not allow them to save the record. One of the basic truisms of database design is that it's easier, safer and more efficient to prevent the user from saving bad/invalid/incomplete records in the first place than it is to try and fix them after the fact.

    If the user/client is asking for this "batch validation" approach, don't agree to it. Bad designs don't get any better because someone insists they want it that way. Anyone who designs a database application has a responsibility to protect the user from his own ignorance.
    Charlotte

Posting Permissions

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