Events Procedure Question..

[Procom Webserve]

Hi guys,

I have a problem with Microsoft Access 2007, in particular a VLB code in the events procedure which I am trying to configure so my query works.

Basically a client has asked me to create a database for them in Access containing 2 lists. One list is a huge list of tropical fish, whilst the other list contains the latin names of these fish. Ideally what they want is to be able to run a query or report and on selection of the Tropical fish, they want the latin name to show up as a result.

I know it has something to do with "row source" and "combo-boxes" and then to add a code into the "VLB" but it doesn't seem to be working.

Here are the names of the lists:

"Common"


Field Name = "COMMON NAME"

"Latin"
Field Name = "LATIN NAME"

The two lists are combined to create "Movements Record"

And here is the Microsoft Visual Basic code im using as my Events Procedure:

 
Private Sub Common_Name_AfterUpdate()
    ' Update the row source of the Latin_Name combo box
    ' when the user makes a selection in the Common_Name
    ' combo box.
    Me.Latin_Name.RowSource = "SELECT LATIN NAME FROM" & _
                               " Latin WHERE Common = " & _
                               Me.Common_Name & _
                               " ORDER BY LATIN NAME"
                            
    Me.Latin_Name = Me.Latin_Name.ItemData(0)
End Sub

Im not even sure this code is correct?

If somebody could point me in the right direction I would be extremely thankful and delighted.

Kind Regards,

Chris Dickinson.

[RuralGuy]

When your FieldNames have embedded spaces you need to enclose the FieldName with brackets "[" and "]". If the value is a string, it is necessary to surround it with single or double quotes:

Me.Latin_Name.RowSource = "SELECT [LATIN NAME] FROM" & _
                           " Latin WHERE Common = '" & _
                           Me.Common_Name & _
                           "' ORDER BY [LATIN NAME]"

[Procom Webserve]

Thanks for your feedback.

I have entered the code and on selection of the common (1st combo box), it says something like 'compile error: Method or data member not found' and opens up the VLB programme again.

I have since decided to tidy my database up and now I have moved both of the sets of data into a new table, called info

Inside the info table is two fields containing the combo boxes and its relevant row source code:

common
Row Source = SELECT [info].[common] FROM info;

latin
Row Source = SELECT [info].[latin] FROM info;

Because I want the record to change in the latin field once the drop down selection is made in the common field, I added the Events Procedure into the common field.

Will somebody be so kind as to provide me with an After Update Events Procedure code so that the above will work?

At the moment I have:

 
Me.info.RowSource = "SELECT [latin] FROM" & _
                           " info WHERE common = '" & _
                           Me.info & _
                           "' ORDER BY [latin]"

However this code isn't working. Any suggestions?

[Procom Webserve]

Ok, slight update here.

It seems ive got the Events Procedure code to work. When I select a choice from the 'common' field, the 'latin' field now matches up and populates with choices in relation to what's been stored in the 'info' table.

However on selection of the common field, the latin field still shows up blank until you select the drop down arrow and then it shows the results. I wanted the results to automatically appear on screen.

How would I do this? Sorry to be a pain.

Chris.

[RuralGuy]

So you are trying to create a form where the user can select a "Common" name and the form shows the "Latin" name, and they can also select a "Latin" name and the form shows the "Common" name. right?

[Procom Webserve]

So you are trying to create a form where the user can select a "Common" name and the form shows the "Latin" name, and they can also select a "Latin" name and the form shows the "Common" name. right?

Yes and no.

Just to select a common name and for the corresponding box on the right to show me the latin name. To be honest I probably dont need a combo box row source for the latin field do I?

I have got it working now, its just ive still got to pull open the latin field box for it to show me the answer produced as opposed to it showing me on screen automatically.

Hope this is of any help.

Thanks,

Chris.

[RuralGuy]

Have you tried replacing the Latin cbo with just a TextBox?

[Procom Webserve]

Yes, just tried that now and to no avail.
Its not a major problem, would be handy though.

[RuralGuy]

Care to post a sample so we can see?

[Procom Webserve]

Here's a view of the form ive created...



If you look at this first image, you'll notice I select a fish called "Achilles Tang". At this point the latin name field generates the corresponding result which is "Acanthurus achilles". However you'll also notice that ive got to open the latin combo box to view the result, as opposed to it appearing on screen automatically.

And here's the property view...



Hope this is of any help? If you need to see any other views let me know.

Regards,

Chris.

[RuralGuy]

Are you resolving the Latin name in the AfterUpdate event of the Common control?

[Procom Webserve]

Are you resolving the Latin name in the AfterUpdate event of the Common control?

Im not sure. Here is the code I have in my AfterUpdate at the moment.

Private Sub common_AfterUpdate()
Me.latin.RowSource = "SELECT [latin] FROM" & _
                           " fish WHERE common = '" & _
                           Me.common & _
                           "' ORDER BY [latin]"
End Sub

[RuralGuy]

The Latin control (a ComboBox) is bound to the Latin field in the RecordSource of the form. Just changing the RowSource of that control does *not* change the bound field. Why not just use a DLookup() in the AfterUpdate event of the Common control and just set the Latin control to the results?
Me.Latin = DLookup("latin","fish","[common] = '" & Me.Common & "'")

[RuralGuy]

Oh...and change the "Latin" control to a TextBox.

[Procom Webserve]

The Latin control (a ComboBox) is bound to the Latin field in the RecordSource of the form. Just changing the RowSource of that control does *not* change the bound field. Why not just use a DLookup() in the AfterUpdate event of the Common control and just set the Latin control to the results?
Me.Latin = DLookup("latin","fish","[common] = '" & Me.Common & "'")

Yes, it works a treat RuralGuy, thank you so much for your help.