Results 1 to 4 of 4

Thread: DoCmd Problem

  1. #1
    Join Date
    Nov 2003
    Posts
    55

    Unanswered: DoCmd Problem

    i'm using the DoCmd.RunSQL to run a query. I set up my query on lines 8-9. My problem is on line 11. When the DoCmd.RunSQL is executed I'm prompted to enter in a parameter. I have already given it a value with strPLNum(j). I have other queries similar to this one and I do not have that problem.

    What am I doing wrong? Can anyone help me.

    Thanks,

    Animaul

    1 For i = 1 To (rst.RecordCount)
    2 strNum = Trim(Str(i))
    3 strNum = "[FIND NO] = '" & strNum & "'"
    4 MsgBox strNum
    5 With rst
    6 .FindFirst strNum
    7 If .NoMatch = False Then
    8 strBuffer = "UPDATE [PL-03] SET [PL-03].[NHA] = " &
    9 strPLNum(j) & " WHERE [FIND NO] = " + Str(i) + ";"
    10 MsgBox strBuffer
    11 DoCmd.RunSQL strBuffer
    12 ElseIf .NoMatch = True Then
    13 j = 1
    14 End If
    15 End With
    16Next

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    A couple of things.

    I don't know what the strPLNum() or Str() are defined as, but they look like a string arrays (based on prepended Str), and you haven't delimited the returned value with single quotes. See your line 3 where you did.

    If you did put the single quotes in, and you are still being asked to provide a paramater, Access prob doesn't recognize one of the columns in your SQL statement.

    BTW, you've included j as an ordinal long, but I don't see where this is being used. Is there ever a case when strPLNum(j) is null?
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Re: DoCmd Problem

    Originally posted by Animaul
    i'm using the DoCmd.RunSQL to run a query. I set up my query on lines 8-9. My problem is on line 11. When the DoCmd.RunSQL is executed I'm prompted to enter in a parameter. I have already given it a value with strPLNum(j). I have other queries similar to this one and I do not have that problem.

    What am I doing wrong? Can anyone help me.

    Thanks,

    Animaul

    1 For i = 1 To (rst.RecordCount)
    2 strNum = Trim(Str(i))
    3 strNum = "[FIND NO] = '" & strNum & "'"
    4 MsgBox strNum
    5 With rst
    6 .FindFirst strNum
    7 If .NoMatch = False Then
    8 strBuffer = "UPDATE [PL-03] SET [PL-03].[NHA] = " &
    9 strPLNum(j) & " WHERE [FIND NO] = " + Str(i) + ";"
    10 MsgBox strBuffer
    11 DoCmd.RunSQL strBuffer
    12 ElseIf .NoMatch = True Then
    13 j = 1
    14 End If
    15 End With
    16Next
    What value is [j] initialized? here it would be 0. Also [j] would not be incremented?

  4. #4
    Join Date
    Nov 2003
    Posts
    55
    Thanks. I had missedc the quoted like you had mentioned.

    Animaul

Posting Permissions

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