Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code Hangs (A2k)

    The following code which I wrote is to check (once again) for duplicates in tblPeriod :-

    '===========================23-07-2003
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Set db = CurrentDb
    Dim strEstCheck As String
    strEstCheck = "select * from tblPeriod where tblPeriod.Job=" & _
    Forms!frmCourtesyCarPlanner!txtJobSelect & " and tblPeriod.supp=" & _
    Forms!frmCourtesyCarPlanner!txtSuppSelect & ""
    Set rst = db.OpenRecordset(strEstCheck, dbOpenDynaset)
    If rst.RecordCount > 0 Then
    MsgBox "Estimate No Duplicated", vbCritical, ""
    End If
    '===========================

    Thus not allowing a duplicate for EstimateNo & Supp
    It is part of the code attached and when run, hangs the form.
    I'm sure the code is not at fault, but maybe I have inserted it into the wrong palce of the whole code.
    See full procedure attached.
    Attached Files Attached Files

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

    Re: Code Hangs (A2k)

    Hi Dave,

    If you find that there is a duplicate, you should exit the procedure. As it is now, the user gets a warning, but the procedure continues. Insert Exit Sub after the MsgBox, before the End If.

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Hangs (A2k)

    Hans

    Yes I had realised from the msgbox nothing would happen, but, I'm not getting the msgbox yet.
    My intention up to this point was if I get the msgbox, then I know the code is working and would add possibly an undo statement or cancel event.

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

    Re: Code Hangs (A2k)

    Dave,

    1. Perhaps it's better to use DCount, just like in the other checks:

    Dim intEstCount As Integer
    Dim strEstCheck As String
    strEstCheck = "Job=" & Forms!frmCourtesyCarPlanner!txtJobSelect & _
    " And Supp=" & Forms!frmCourtesyCarPlanner!txtSuppSelect
    intEstCount = DCount("*", "tblPeriod", strEstCheck)
    If intEstCount > 0 Then
    MsgBox "Estimate No Duplicated", vbCritical
    Exit Sub
    End If

    2. I can't remember any more whether Job and Supp are numeric or text fields. As you know, text fields should be surrounded with quotes. So for instance if Job is a text field, you should have

    strEstCheck = "Job=" & Chr(34) & Forms!frmCourtesyCarPlanner!txtJobSelect & Chr(34) & _
    ...

    and similar if Supp is a text field.'

    3. If you still have problems: what happens if you single-step the code? Where do things go wrong?

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Hangs (A2k)

    Hans
    As you thought, Text vs Numeric
    I had a type mismatch error and checked tblPeriod.
    Job was text, but Supp was numeric.

    Changing supp to text solved the problem though.
    I think if you remember back, this was declared in the forms module.

    Thanks again.

Posting Permissions

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