Solved: Access 2010 MVL field Errors

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.

rekeene

Thread Starter
Joined
Dec 2, 2014
Messages
49
Good day,

I'm seeking assistance with glitches in my MultiValue Lookup Boxes:

Scenario No. 1:

The MVL field is sorted in alphabetical order, last name first.

Example of names in MVL:
1. Michael, Christopher
2. Michael, Jones

When I search the MVL field for Michael, Jones it returns the record for Michael, Christopher. Even if I manually scroll the MVL field and click on Michael, Jones, I’m still presented with Michael, Christopher’s record. It appears the MVL field is only searching/looking at the first occurrence of Michael.


Scenario No. 2:

The MVL field cannot locate records with an apostrophe in the name. For example: Jn’Marie. Do I need to omit the apostrophe?

Thanks for your assistance.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
I assume that this is a "Find" Combo for locating records.
The first problem is due to the data structure, as you have guessed it is only using the first column of I assume 2 columns.
Does the table/query that the form is based on have an Indexed Key Field?
if so include the key field in the Combo Row Source as the first Column and hide it by setting it's width to 0cm.

The second problem is very old and goes back to the first versions of Access and is caused by the VBA code that does the matching of the records, the single apostrophe confuses the VBA which itself normally uses apostrophes in it's code.
You need to change them in the code to the double apostrophe, the Inverted Comma "
 

rekeene

Thread Starter
Joined
Dec 2, 2014
Messages
49
Hi OPB,

I was able to fix find combo box glitch. Not successful on the second as I don't know where to make the adjustments to the code. I created the search/find field using the wizard. Thanks.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
To fix the second problem it will need some changes.
In the Combo's Property List under "Event" what does it say in the "After Update" event, does it say "[Event Procedure]" or "[Embedded Macro]"?
If it says [Embedded Macro] it will need to be changed to an [Event Procedure] using the drop down arrow that appears when you click on the [Embedded Macro].
Once it says [Event Procedure] you can click on the 3 small dots on the right hand side and it will open the Visual Basic Editor at the correct procedure, which will be blank.
I can supply the code to place in there if you can tell me the exact name of the fields that you are searching on.
 

rekeene

Thread Starter
Joined
Dec 2, 2014
Messages
49
The name of the search field is "Client Name" created in the Expression Builder, using the First and Last name fields. Thanks much!
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Is that the actual names of the fields?
First Name
and
last Name?

With Visual Basic you have to be precise.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Did you include the Key Indexed field in the combo as I suggested, if so what is it's name?
 

OBP

Joined
Mar 8, 2005
Messages
19,895
The VBA code is as follows

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ChildID] = " & Me![Combo name goes here] '
If Not rs.EOF Then Me.Bookmark = rs.Bookmark


Note that where I have "Combo name goes here" you need to change that to the name of your combo, which I forgot to ask for.
 

rekeene

Thread Starter
Joined
Dec 2, 2014
Messages
49
Hi,

This is the code I'm using:

Private Sub ClientSearch_AfterUpdate()
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ChildID] = " & Me![ClientSearch] '
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

However, I'm receiving the attached error.
 

Attachments

OBP

Joined
Mar 8, 2005
Messages
19,895
That suggests that your first Column in the Combo is not actually ChildID.
Is ChildID the first column?
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Are you able to post a copy of your database with some dummy data (personal data is not allowed on the Forum) in it for me to work with?
It would need to be Zipped.
 
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

Members online

Top