Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2009
    Posts
    35

    Unanswered: Need some help with VB problem

    Hi, I am fairly new to using access and was after a few pointers. I am currently devising a form which I want to be able to dispaly a command button which will (when pressed) output a query TOTAL. I have found it is simple enough to output a query to another form, but I want the text box?? to output the data directly to the form (which has the command button on). If that makes sense.

    I have managed to find a similar database from 97 which has a vb statement inc. I have copied the code and made it relevant to my form. When I press this button I get the error "Type mismatch" on screen. Here isd the statement :

    Private Sub cmdSB_Click()
    On Error GoTo Err_cmdSB_Click

    Dim stDocName As String
    Dim rst As Recordset
    Set rst = CurrentDb.OpenRecordset("SELECT count(error2) FROM TCLP_PASS1 WHERE error2='SB';")
    Debug.Print rst
    Me!txtSB = rst!Count
    stDocName = "TCLP -SB"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

    Exit_cmdSB_Click:
    Exit Sub

    Err_cmdSB_Click:
    MsgBox Err.Description
    Resume Exit_cmdSB_Click

    End Sub

    Where TCLP -SB is the query it is connected too and txtSB is the data field.
    As I stress I am fairly new and I assumed thast this would be a fairly common procedurebut seems to not be. Is their a simpler way to accomplish this?????

    Any help would be much appreciated.

    Ste

  2. #2
    Join Date
    Sep 2005
    Posts
    19
    Me!txtSB = rst!Count
    I'm just guessing here but I think you may have a problem with

    Me!txtSB = rst!Count

    Your sql string inside the openrecordset command doesn't have anything called "Count". Yes you use the count function but the recordset probably is returning something like countoferror2. Try using an alias instead:

    Set rst = CurrentDb.OpenRecordset("SELECT count(error2) as myalias FROM TCLP_PASS1

    and then use

    Me!txtSB = rst!myalias

    again, I'm not confident that this is your problem but I could see how it would cause problems

  3. #3
    Join Date
    Feb 2009
    Posts
    35
    Thanks for the reply. I have updated the script and added "as data1" where u have stated "as alais" and put this on the end of the rst!count (to rst!data1). This has returned a the same message "type mismatch".

    The error seems to be on the line Set rst = CurrentDb.OpenRecordset...... somewhere along there!!

  4. #4
    Join Date
    Sep 2005
    Posts
    19
    Have you tried copying your select statement into a new query just to make sure it's returning what you want?

  5. #5
    Join Date
    Feb 2009
    Posts
    35
    yes, I have just created a new query called 'data' and added the sql statement to it. This is still returning "type mismatch".

    I have been googling the error and was wondering whether there is a conflict between the DAO and ADO library?? Here I have attached the link :

    You receive a "Type mismatch" error when you run the OpenRecordset method

    The field has never returned a value in the text box. I feel it is something quite obvious? Is there an alternative way to resolve this problem?

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    As an alternative solution, if you're only running this query once, why not use a dcount()? Something like:
    Code:
    Me.txtSB = dcount("error2","TCLP_PASS1","error2 = 'SB'")
    If you're not running off a backend many times and worried about performance, why not?
    Me.Geek = True

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    declare your objects explicitly
    using currentdb you are de facto in DAO so make sure your recordset is DAO.
    dim rs as DAO.recordset
    attempting to open an ADO recordset on a DAO database object is ...unfortunate.
    the default recordset object is determined by the order of the references in your application - you could adjust the order, but it is 1000% better to declare explicitly.

    also:
    rs!something is the data in a field named something
    rs.count is the number of records in the recordset...
    LATER - no it isn't: rs.recordcount is the number of records in the recordset ! sorry about that.
    ...which is typically 1 until you issue a rs.movelast to fully populate the recordset.

    also, make you mind up: decide between:
    SELECT Count() as myalias
    rs!myalias
    or
    rs.count
    or maybe look into
    myCount = dcount("error2", "TCLP_PASS1", "error2='SB'")

    dcount() is considered "slow" so you shouldn't use it in a loop. for a one-off count it is the easiest and will be faster and will take less resources than messing with a recordset.

    izy

    oooops: sorry Nick - we were typing at the same time: at least we said the same thing
    Last edited by izyrider; 02-12-09 at 13:30.
    currently using SS 2008R2

  8. #8
    Join Date
    May 2005
    Posts
    1,191
    No problem Izy, your answer was more complete anyways
    Me.Geek = True

  9. #9
    Join Date
    Feb 2009
    Posts
    35
    I managed to get it goin. I used the
    Me.txtSB = dcount("error2","TCLP_PASS1","error2 = 'SB'")
    statement and it worked.. Thanks for the help

Posting Permissions

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