1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

MS Access Event Procedure help!

Discussion in 'Business Applications' started by Gram123, Jan 13, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Hi.
    I have an event procedure on a database I used in the past, which basically allows controls on a "Browse" form to work, so the user can a partial value in one (or more) of 4 fields and it will return a list of records that contain the partial values.

    I want to reuse the event procedure for a new database, so I imported it and edited it, substituting the field and form names for those relevant to the new database.

    The original event procedure looked like this:
    Option Compare Database
    Option Explicit

    Private Sub Form_Load()

    ' On loading the frmBrowse form, do not show List box or Record Count label
    Me.lstDetails.Visible = False
    Me.lblRecordCount.Visible = False
    Me.lblResults.Visible = False
    End Sub
    Private Sub lstDetails_DblClick(Cancel As Integer)

    'Open Data Entry form at specified record when it is double-clicked in the List Box
    DoCmd.OpenForm "Data Entry", acNormal, , "[Reg Number]= '" & lstDetails & "'"

    End Sub

    Private Sub MOB_Number_AfterUpdate()
    'After updating MOB Control, run UpdateListDetails code
    UpdateListDetails
    End Sub

    Private Sub Reg_Number_AfterUpdate()
    'After updating Reg Control, run UpdateListDetails code
    UpdateListDetails
    End Sub

    Private Sub Colour_AfterUpdate()
    'After updating Colour control, run UpdateListDetails code
    UpdateListDetails
    End Sub

    Private Sub Description_AfterUpdate()
    'After updating Description control, run UpdateListDetails code
    UpdateListDetails
    End Sub

    Private Sub UpdateListDetails()
    'If either the MOB, Reg, Colour or Description controls contain values, do the code that follows:
    If Not (Me.[MOB Number].Value = "" And Me.[Reg Number].Value = "" And Me.Colour.Value = "" And Me.Description.Value = "") Then

    'Make the List Box and Record Count visible, re-run the query
    Me.lstDetails.Visible = True
    Me.lstDetails.Requery
    Me.lblRecordCount.Visible = True
    Me.lblResults.Visible = True

    'As the records are counted from 0, subtract 1 from the total so that count is correct
    Me.lblRecordCount.Caption = Me.lstDetails.ListCount - 1

    'If no records are returned, hide List box and Record Count
    If Me.lstDetails.ListCount = 0 Then
    Me.lstDetails.Visible = False
    Me.lblRecordCount.Visible = False
    Me.lblResults.Visible = False

    'Show message stating no records fit the criteria
    MsgBox Title:="Warning for " & Me.Caption, Prompt:="No matching records!"
    Me.[MOB Number].Value = ""
    Me.[Reg Number].Value = ""
    Me.Colour.Value = ""
    Me.Description.Value = ""
    End If
    Else

    'If the MOB, Reg, Colour and Description controls contain no values, hide List Box and Record Count
    Me.lstDetails.Visible = False
    Me.lblRecordCount.Visible = False
    Me.lblResults.Visible = False

    End If
    End Sub


    --

    This is the new version:

    Option Compare Database
    Option Explicit

    Private Sub Form_Load()
    ' On loading the Browse form, do not show List box or Record Count label
    Me.lstDetails.Visible = False
    Me.lblRecordCount.Visible = False
    Me.lblResults.Visible = False
    End Sub
    Private Sub lstDetails_DblClick(Cancel As Integer)

    'Open Data Entry form at specified record when it is double-clicked in the List Box
    DoCmd.OpenForm "VehicleDetails+Work", acNormal, , "[ChassisNumber]= '" & lstDetails & "'"

    End Sub

    Private Sub ChassisNumber_AfterUpdate()
    'After updating Chassis Control, run UpdateListDetails code
    UpdateListDetails
    End Sub

    Private Sub RegNumber_AfterUpdate()
    'After updating Reg control, run UpdateListDetails code
    UpdateListDetails
    End Sub

    Private Sub Model_AfterUpdate()
    'After updating Model control, run UpdateListDetails code
    UpdateListDetails
    End Sub

    Private Sub AgreementNumber_AfterUpdate()
    'After updating Agreement Number control, run UpdateListDetails code
    UpdateListDetails
    End Sub

    Private Sub UpdateListDetails()
    'If either the Chassis, Reg, Model or AgreementNumber controls contain values, do the code that follows
    If Not (Me.[ChassisNumber].Value = "" And Me.[RegNumber].Value = "" And Me.[Model].Value = "" And Me.[AgreementNumber].Value = "") Then

    'Make the List Box and Record Count visible, re-run the query
    Me.lstDetails.Visible = True
    Me.lstDetails.Requery
    Me.lblRecordCount.Visible = True
    Me.lblResults.Visible = True
    'As the records are counted from 0, subtract 1 from the total so that count is correct
    Me.lblRecordCount.Caption = Me.lstDetails.ListCount - 1

    'If no records are returned, hide List box and Record Count
    If Me.lstDetails.ListCount = 0 Then
    Me.lstDetails.Visible = False
    Me.lblRecordCount.Visible = False
    Me.lblResults.Visible = False

    'Show message stating no records fit the criteria
    MsgBox Title:="Warning for " & Me.Caption, Prompt:="No matching records!"
    Me.[ChassisNumber].Value = ""
    Me.[RegNumber].Value = ""
    Me.[Model].Value = ""
    Me.[AgreementNumber].Value = ""
    End If
    Else

    'If the controls contain no values, hide List Box and Record Count
    Me.lstDetails.Visible = False
    Me.lblRecordCount.Visible = False
    Me.lblResults.Visible = False

    End If
    End Sub


    The On Load event on the Browse form, the After Update events on the controls and the On Dbl Click event on the list are all in place.
    However, no matter what I enter in the controls, it doesn't populate the list, even when I enter a value exactly as I know exists in the Vehicles table / VehicleDetails+Work form.

    Any ideas how I can fix this? My first instinct is saying "don't copy existing event procedures to a new database!".

    Gram
     
  2. qucho

    qucho

    Joined:
    Jan 9, 2006
    Messages:
    26
    that code is never been executed to begin with. want proof? set a break point on the begining, it will never be reached.

    Try using the 'compile' menu option . i think is in the debug menu.

    You have a 'else' close to the end which is not inside of any 'IF' block, i think that what you need to do there is delete the 'End If' right before the 'Else'.

    This comparison will ONLY evaluate to TRUE if ALL the values are not empty. And you said the user not all values were required.
    Code:
    If Not (Me.[MOB Number].Value = "" And Me.[Reg Number].Value = "" And Me.Colour.Value = "" And Me.Description.Value = "") Then
    Always avoid the use of 'Not', it only makes your code hard to read. I suggest to change this line to:
    Code:
    If (Me.[MOB Number].Value <> "") Or (Me.[Reg Number].Value <> "") Or (Me.Colour.Value <> "") Or ( Me.Description.Value <> "") Then
    Also, if you dont have the exact same name for those objects, that code will not be executed since there is no object with that name.

    for starters the Form_load() one seems bogus, if your form is called 'browse' then the Sub should be called Form_browse_Load()

    my suggestion when copying code from another project is to create the sub directly in your new project and then only to copy the lines of code inside your old Sub, never copy subs with fron the begining to end when it comes to event related code.
     
  3. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    Not true. My guess is that you haven't assigned the procedure to the form's Load event in the form's event properterties.

    Open the form's Properties window, then under the Event tab, locate the On Load event property. Type [Event Procedure]. Click on the ellipsis (...) after the text box. It should open the form's module to the sub you pasted.

    If the event property hasn't been declared, the procedure will not run.

    I didn't read the code though, just wanted to correct that.

    chris.
     
  4. qucho

    qucho

    Joined:
    Jan 9, 2006
    Messages:
    26
    cristobal03,

    You are correct in that the form property needs to be set to "[Event Procedure]" for the code to run, yet... the name of the procedure still HAS to match that of the form, or no code will be executed. BOTH are needed.
     
  5. cristobal03

    cristobal03

    Joined:
    Aug 5, 2005
    Messages:
    3,086
    I don't know how many forms I have that run a module Form_Load. OP is using Access, which expects the procedure name to be exactly that. To what language are you referring and/or what version of Access?

    Just now, trying to declare a procedure Form_Form1_Load() places the routine in the general portion of the form module, which means it cannot handle events. To do so, it'd have to be a public function in a standard module, and it must be called by name in the form's load event property, a la: =Form_Form1_Load() (instead of [Event Procedure]).

    So yeah. Could you post an example of what you mean please?

    chris.
     
  6. qucho

    qucho

    Joined:
    Jan 9, 2006
    Messages:
    26
    cristobal,

    I must appologize to you and all our readers. you are totally right. I screwed up. :(

    What i said seems to aply to any other object in the form, and should have the object name nad the event in the Sub's name, but NOT the form.

    As you stated the name for the sub Form_Open() is the right one.
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/433712

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice