Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Feb 2002
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code not working (A2000)

    This following code is noy working.
    Am I missing something in the SQL statement.

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strsql As String
    Dim strValue As String
    Set db = CurrentDb
    strsql = "Select * From tblEnquiry Where tblEnquiry.estimateno = " & Forms!frmDetails!EstimateNo & " and tblEnquiry.supp=" & Forms!frmDetails.supp & ""
    'strsql = "Select * From tblEnquiry Where tblEnquiry.estimateno = " & Forms!frmDetails!EstimateNo
    Set rst = db.OpenRecordset(strsql, dbOpenDynaset)
    If rst.RecordCount = 0 Then
    DoCmd.OpenForm "frmenquiry"
    DoCmd.GoToRecord acDataForm, "frmenquiry", acNewRec
    Forms!frmEnquiry!EstimateNo.SetFocus
    Forms!frmEnquiry!EstimateNo = Forms!frmDetails!EstimateNo
    Else
    strValue = Forms!frmDetails!EstimateNo
    DoCmd.OpenForm "frmenquiry", acViewNormal, , "EstimateNo = " & strValue

    End If

    The line that is commented out works fine, the line I am trying to make work isn't.

    strsql = "Select * From tblEnquiry Where tblEnquiry.estimateno = " & Forms!frmDetails!EstimateNo & " and tblEnquiry.supp=" & Forms!frmDetails.supp & ""

    When the form frmEnquiry opens with this line, it does not set the "supp" value to the same as the "supp" value of frmDetails.

    Help is appreciated.

    Dave

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

    Re: Code not working (A2000)

    The SQL statement is OK, but you don't do anything with the additional condition when you open the form frmEnquiry - there you still use EstimateNo only.

    Here is a suggested improvement - I haven't tested it on actual data. I have added an extra variable strFilter that is used in the SQL statement and in the DoCmd.OpenForm instruction.

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strFilter As String
    Dim strSQL As String
    Dim strValue As String
    Set db = CurrentDb
    strFilter = "tblEnquiry.EstimateNo = " & Forms!frmDetails!EstimateNo & " And tblEnquiry.Supp = " & Forms!frmDetails.Supp
    strSQL = "Select * From tblEnquiry Where " & strFilter
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
    If rst.RecordCount = 0 Then
    DoCmd.OpenForm "frmEnquiry"
    DoCmd.GoToRecord acDataForm, "frmEnquiry", acNewRec
    Forms!frmEnquiry!EstimateNo.SetFocus
    Forms!frmEnquiry!EstimateNo = Forms!frmDetails!EstimateNo
    Forms!frmEnquiry!Supp = Forms!frmDetails!Supp
    Else
    DoCmd.OpenForm "frmEnquiry", acViewNormal, , strFilter
    End If

    HTH, Hans

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

    Re: Code not working (A2000)

    Oh, and BTW, I assumed that Supp is numeric. If it is a text field, the last part of strFilter should be

    <pre>" And tblEnquiry.Supp = '" & Forms!frmDetails.Supp & "'"</pre>


    (Forms!frmDetails.Supp is enclosed in single quotes)

  4. #4
    Star Lounger
    Join Date
    Feb 2002
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code not working (A2000)

    Thanks Hans

    The first code is perfect.

    Regards Dave

Posting Permissions

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