Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2013
    Posts
    3

    Unanswered: Issue with vba code

    Hi everyone,

    I have almost finished creating a database for work to store customer feedback except I'm having one minor issue. After making some small changes to the main feedback form (where people can log feedback to the database), the code I used to create an automated number has stopped working. I'm not sure what the cause of it is, as all changes have been purely cosmetic and I have not touched the code at all.

    This is my code:
    Private Sub makeCF()
    If IsNull(Company_name.Value) Or IsNull(Product_name.Value) Or Not IsNull(CF_.Value) Then Exit Sub
    strCF = Replace(Me.Company_name.Value, " ", "") & "_" & Me.Product_name.Value & "_" & DatePart("ww", Me.Feedback_date.Value) & Right(Year(Me.Feedback_date.Value), 2)
    Set db = CurrentDb()
    Set qrySeqNum = db.QueryDefs![qrySeqCfNum]
    qrySeqNum.Parameters("productname") = Product_name.Value
    qrySeqNum.Parameters("companyname") = Company_name.Value
    qrySeqNum.Parameters("date") = Feedback_date.Value
    dsSeqNum = qrySeqNum.OpenRecordset()
    cfMax = dsSeqNum!num
    seqNum = Right(cfMax, 1) + 1
    cfMax = Left(cfMax, Len(cfMax) - 1) & seqNum
    CF_.Value = cfMax
    End Sub
    Private Sub Company_Name_AfterUpdate()
    Call makeCF
    End Sub
    Private Sub Feedback_date_AfterUpdate()
    Call makeCF
    End Sub
    Private Sub Product_name_AfterUpdate()
    Call makeCF
    End Sub

    As you can see, it is meant to extract Company name, Product name, 2 digits of the week, 2 digits of the month and 2 digits of the year plus a sequential number and input this into a field called CF# in the feedback table. I have no idea why it has stopped working all of a sudden and why it is not updating the CF# on the form and in the feedback table. Basically, the CF number should look something like this: Company Name_Product Name_0712021

    Can anyone help me out?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so step through the code and examine what actually is happening as opposed to what you think the code is doing.
    its not working
    simply isn't enough information for someone to work out what is wrong.

    my immediate guess would be that
    Company_name Or Product_name are null Or CF_.Value isn't null

    however you also don't have any error trapping on your SQL, so the result could be indeterminate if you don't find an rows

    DO you use option strict or its equivalent to force explicit declaration of variables?

    it could be that unknown to you someone has b(*&*^ered around with the query. however looking at that code I'd put money on it being the first line of the sub MakeCF is failign and exiting the sub with no result

    of course it could be that there is some confusion and you are not setting the acutal control you think you are on completion of the sub

    when posting code here it helps if A the code is idented AND also enclosed in [ c o d e ] and [ / c o d e ] (minus the spaces)
    Code:
    Private Sub makeCF()
    If IsNull(Company_name.Value) Or IsNull(Product_name.Value) Or Not IsNull(CF_.Value) Then Exit Sub
      strCF = Replace(Me.Company_name.Value, " ", "") & "_" & Me.Product_name.Value & "_" & DatePart("ww", Me.Feedback_date.Value) & Right(Year(Me.Feedback_date.Value), 2)
      Set db = CurrentDb()
      Set qrySeqNum = db.QueryDefs![qrySeqCfNum]
      qrySeqNum.Parameters("productname") = Product_name.Value
      qrySeqNum.Parameters("companyname") = Company_name.Value
      qrySeqNum.Parameters("date") = Feedback_date.Value
      dsSeqNum = qrySeqNum.OpenRecordset()
      cfMax = dsSeqNum!num
      seqNum = Right(cfMax, 1) + 1
      cfMax = Left(cfMax, Len(cfMax) - 1) & seqNum
      CF_.Value = cfMax
    End Sub
    Private Sub Company_Name_AfterUpdate()
      Call makeCF
    End Sub
    Private Sub Feedback_date_AfterUpdate()
      Call makeCF
    End Sub
    Private Sub Product_name_AfterUpdate()
      Call makeCF
    End Sub
    It would also be beneficial to include some comments (and beneficial to you when revisiting the code.

    Style wise its arguable whether this would be better as a function rather than a sub returning a value. but thats a stylistic point rather than a hard and fast rule.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2013
    Posts
    3
    Hi Healdem,

    Yes, you were right. It did have something to do with that very first line in the code to do with the If statement and the Company_name.Value and Product_name.Value being null and CF_.Value not null.

    I changed the statement to read as follows:
    Code:
    If IsNull(Company_name.Value) Or IsNull(Product_name.Value) Or Not IsNull(CF_.Value) Then 'Save.
    End If
    But I now have issues with the cfMax = dsSeqNum!num section. I get an error saying 'runtime error 3021 no current record'.

    I'm not very good at coding so I'm not very sure why I'm getting these errors.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you need to put some error trapping artound the SQL, or find a way of working out what to do if no row is found

    bear in mind that for your application its perfectly valoid for no row to be returned fromt he recordset
    so you need a mechanism to recognise that and use an appropraite value
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2013
    Posts
    3
    I considered the use of Nz( ) but am not sure how to wrap it around the cfMax statement. Whenever I use bracketing or the [ ], it throws up an error refusing the use of such brackets

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what makes you think you need touse square brackets for a function such as NZ

    square brackets in JET (Access) SQL have a different meaning they delimit table / column names where people have been foolish enough to use spaces in table / column names

    in any even NZ won't help you as its perfectly legit for your query to return no rows. you need to cater for that by checking to see if any rows were returned (ie is the recordset is empty)

    or redesign your query so that it always returns a value. one way of doing that is to use the SQL function count(), however that assumes you never delete rows

    another woudl be to use the domain function DMAX in your CMAX function as opposed to using a SQL reecordset
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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