Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2013
    Posts
    99

    Unanswered: Performing a Dcount In a MS Access Web Database

    I have the following macro in a after update event on an object, I need to convert the current database to a Web Database but Dcount does not appear to be an option.

    Is there any other way of getting around the following issue

    Thank you
    Attached Thumbnails Attached Thumbnails Untitled.bmp  

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    dcount is a macro, effectivley its a SQL statement
    so you can get round your problem by executing a SQL statement

    strSQL = "SELECT sum(acolumn) as NoViolations from ParkingViolationstbl where VIN = '" & VIN & "'"
    ..you will need to reaplce acolumn with a valid column in your db
    im not certain what your vin=0 expression i tryign to achieve. Im expecting you to supply the VIN so you may need to tweak that

    then open the recordset (look at the help file, doens't matter if you use ADO or DAO they both do the same thing in their own way. supposedly ADO is the newer preferred route but DAO still hasn't been phased out after 15 years so I guess its hear to stay
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2013
    Posts
    99

    further help required

    Thanks for the reply Healdem

    You are correct when the VIN is entered into the Feild it performed the Dcount and checked if any records existed in the table on an after update event, if record did not exist a message box and if it did then it would load the form with all the entry's for that car.

    Sorry but this is a little over my head, so where do I put the SQL statement ?

    I understand what it is performing, would this then be followed by an If statement for example

    If novilolation = 0 then
    messagebox "Vehicle does not exist"

    -----

    Also I have read the help files on Recordset Object, but my knowledge is limited can you suggest any other referance.

    As I understand this will enable the user to manipulate data in a database at the record level, why would I require this when all I need the else if to display the records of an existing vehicle.

    Thanks for you time

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I don't know how your application is structured
    Ive never used Access as a web database, so I don't know the requirements for that.

    At some point you currently fire the macro, however you fire off that macro there will be an event that you can use to replicate that macro. truth to be told you can usually do more and more elegantly than using a macro. As a result I tend not to use macro's in any context except for things like dcomd.


    what you could do is write this as a function that retuirns the number of rows found for the spoecified VIN number. eg:-
    Code:
    public function GetNoViolations(VIN as string) as integer
    'this function searches the table to find the number of parking violations that exist for the specified VIN No
    GetNoViolations = 0 'set up our default return value
    'ok so how do we validate the VIN No.
    'tricky as there are different formats, different lengths and so on
    'so lets leave it as is there something in the VIN no 
    if isNull(VIN) = vbtrue or len(VIN) =0 then
      exit sub
    endif
    dim strSQL as string 'this will hold our SQL statement
    'from http://msdn.microsoft.com/en-us/library/office/ff820966.aspx
    Dim dbs As DAO.Database
    Dim rs As DAO.Recordset
    Set dbs = CurrentDb
    
    'strSQL = "SELECT sum(acolumn) as NoViolations from ParkingViolationstbl where VIN = '" & VIN & "'"
    
    'Open a dynaset-type Recordset using a saved query
    Set rs = dbs.OpenRecordset(strSQL, dbOpenDynaset)
    
    if rs.BOF = true and rs.EOF = true then 'we didn't find anything
      GetNoViolations = 0
    else
      GetNoViolations = rs!NoViolations
    endif
    end function
    rs.close 'strictly speakign its not neccessary to close the recordset and db but its no bad thing, and its a good habit to get into especially with more fussy languages/environments than Access
    dbs.close
    then in your application code call that function
    Code:
    if GetNoViolations(MyVinNo) =0 then
      msgbox("Vehicle does not exist in system","Record not found",vbinformation)
    else
      docmd.openform "myformname", acformview, "VIN = '" & myVINNo & "'"
    endif
    as to where that code goes, haven't got a scooby. but it will be where ever you have that macro thatere should be a similar event hook.
    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
  •