MS Access Event Procedure help!

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 
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.
 
Joined
Aug 5, 2005
Messages
3,086
Originally posted by qucho:

for starters the Form_load() one seems bogus, if your form is called 'browse' then the Sub should be called Form_browse_Load()
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.
 
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.
 
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.
 
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top