Hey there-

I am trying to follow the Beginning Access 2002 VBA code for creating a drop down record selector with forms. I have Access 2000, so there might be some problems there. Anyway, I wrote the code below and have been getting the Invalid Use of Property Error on the line with the [Event Procedure]. Any help would be greatly appreciated as to why this isn't working. Thanks in advance!!!
__________________________________________________ ____________
Option Compare Database
Option Explicit

Dim WithEvents mcboRecSel As ComboBox
Dim mtxtRecID As TextBox
Dim mfrm As Form

Public Function init(lfrm As Form, lcboRecSel As ComboBox, ltxtRecID As TextBox)
  Set mfrm = lfrm
  Set mtxtRecID = ltxtRecID
  Set mcboRecSel.AfterUpdate = "[Event Procedure]"
End Function


Sarah W
NGIT

Well first, the DIM statements should go AFTER the PUBLIC statement. As for the error line

Set mcboRecSel.AfterUpdate = "[Event Procedure]"

I don't know what you're trying to do there. That statement makes no sense to me as written. Can you please explain?


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

The DIM statements CAN go before the PUBLIC statement if this is to be used globably. But like Greg, I'm unsure of your statement "Set mcboRecSel.AfterUpdate = "[Event Procedure]""

Beth M

Having played with WithEvents in Access 97, it is interesting that it is gradually becoming more mainstream. If I recall correctly (not having seen the book) the portion of the book with which you are having an issue was written by John Colby who got his foot in the door in WithEvents with examples by Shamil Salakhetdinov. John is subscribed to a free Access mailing list that you can subscribe to at:

www.databaseadvisors.com

Given that the place I routinely work has only had Access 97 and is in the process of upgrading to Access 2003 over the next few weeks, I hope to be using WithEvents more in the future. For some example code and demos on WithEvents programming in Access 97 you may wish to go to:

http://smsconsulting.spb.ru/shamil_s/

There are some specific examples of hooking a control by assigning [Event Procedure] to a reference to a control, for example at:

http://smsconsulting.spb.ru/shamil_s...s/deepcnpt.htm

Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]

So sorry about the confusion!! I'm so new I don't even know what all to tell people to help!! But I am learning! Thank you for all your help!!! See below for all of the code. I have a form, with tab controls with subforms on then. I want to be able to select a product from the drop down list and have all the tab control/sub forms be related to that one product.

Thanks!!!
__________________________________________________ ________________
MyRecordSelector Class
Option Compare Database
Option Explicit

Dim WithEvents mcboRecSel As ComboBox
Dim mtxtRecID As TextBox
Dim mfrm As Form

Public Function init(lfrm As Form, lcboRecSel As ComboBox, ltxtRecID As TextBox)
  Set mfrm = lfrm
  Set mtxtRecID = ltxtRecID
  Set mcboRecSel.AfterUpdate = "[Event Procedure]"
End Function

Private Sub Class_Terminate()
  'clean up pointers to form and control objects
  Set mcboRecSel = Nothing
  Set mtxtRecID = Nothing
  Set mfrm = Nothing
End Sub

Sub mcboRecSel_AfterUpdate()
Dim strSQL As String
  'Build A SQL Statement
  strSQL = mtxtRecID.ControlSource & " = " & mcboRecSel
  With mfrm
    'Find the record that matches the control.
    .RecordsetClone.FindFirst strSQL
    'Set the forms bookmark to the Recrodset clones bookmark
    '("Find" the Record)
    .Bookmark = .RecordsetClone.Bookmark
  End With
End Sub
__________________________________________________ _________________
Within the form itself
Option Compare Database
Dim fMyRecordSelector As MyRecordSelector

Private Sub Form_Close()
  Set fMyRecordSelector = Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
  Set fMyRecordSelector = New MyRecordSelector
  fMyRecordSelector.init Me, cboRecSel, txtRecID
End Sub

Sarah W
NGIT

Still not too sure what the
bit is supposed to do - what is it that you're thinking it should do?

But anyway - forget coding it yourself if you're having problems.
The way I see it - you want a combo box that when you select something from it, it goes to the record of your selection, with all the subforms and the like also going to it. If that's so - then just let the wizards do your work for you.

Just make sure you have the wizards turned on, then put a combo box on your form and choose "Find a record on my form based on the value I selected in my combo box", then follow the rest of the prompts.

HTH

Steven


I am a loud man with a very large hat. This means I am in charge

Thanks for all the help and responses. I finally figured things out and got it to work. However, now requirements have changed and I need to have 2 combo boxes on a form, with one combo box driving the information in the second. So if I select a division from the first combo, I want the second combo to come up with a list of the available things to select for that division. How would I go about doing this?

Any help would be appreciated. Thanks again for all your help.

Jurgenw - I made it to that wedsite and the information there was helpful. Thanks!! I can see that what I was trying to do was possible!

Sarah W


Sarah W
NGIT

Go to http://rogersaccesslibrary.com/ and click on his Access97/2000 samples. There is a 'cascadingcombos' example available for download.

Regards,

Beth M

Thanks Beth for the website!! It had exactly what I needed!! AWESOME!!! Have a great day!

Sarah W
NGIT