(NOT) Going to a record by selecting from a List or Combo box

[Tim L]

Access 2k/XP

Having used the wizard to create a List or Combo Box which then causes the form to display the record selected I have this code in the On Update event:

Private Sub Combo19_Click()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[JobID] = " & Str(Nz(Me![Combo19], 1))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub


The only problem is that the form does not move to the selected record. I have tried using both List and Combo box, to no avail.

I have used this technique in another database, but there is a difference between the two. In this database the form displays records that have been filtered. I presume that this may be causing the problem.

Can any one give me an idea on how I can get the List box to work correctly?

Tim

 

[Oldsoftboss]

You say you have an after update event but the code you pasted say "Click" ..... Just checking

The code the wizard creates for me is

Sub Combo47_AfterUpdate()
On Error GoTo Err_Combo_AfterUpdate

' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Cust ID] = " & Str(Me![Combo47])
Me.Bookmark = rs.Bookmark

Exit_Combo47_AfterUpdate:
Exit Sub

Err_Combo_AfterUpdate:
MsgBox Err.Description
Resume Exit_Combo47_AfterUpdate

End Sub

Not sure what the NZ is for as the field you are searching is usually the auto number field therefore will never be empty.
Dave

 

[Oldsoftboss]

Second thought. Is the combo box reflecting the records in the filtered record set or gathering its info from all the records.
May be trying to select records that are not there.
You may have to base the combo box on a query that updates after the filter has been applied to show only the filtered records.

Dave

 

[Tim L]

Hi Dave, thanks for taking an interest,

I noticed immediately that the filter that the List Box list was based on included all of the records and took the appropriate steps to limit it, the list in the List Box is correct now.

The 'Click' thing was because I copied the VBA code that was in the Update event into the On Click event so that it was in both of them.

I think that the "Str", "Nz" and ",1" are there because the list that is in the box consists of two pieces of information that are shown, plus in the first column the 'key', which is not displayed but which is used to identify records uniquely, so effectively 3 columns (backed up by the Column Widths property: "0cm; 5cm; 2cm".

Tim

 

[Mile-O]

You don't have to copy the code for another event.

Sub Combo47_AfterUpdate()
Call Combo47_Click()
End Sub

would run the code in the combo's click event

 

[Tim L]

Thanks for that Mile-o-Phile.

Any idea on solving the other problem though?

Tim

 

[jjturner]

I'm using A97 so the syntax looks a bit diff't.

You say the Wiz created the code, and I'm somewhat curious about the 'Str(Nz' syntax.

I know on A97 when using FindFirst method with a String value, it works by encasing the WHERE expression in single quotes:

rs.FindFirst "[Cust ID] = '" & Str(Me![Combo47].Value) & "'"

Also note the reference to the .Value property for the ComboBox - that is also necessary in A97.

HTH,
John

 

[Mile-O]

Shouldn't you dimension rs as a Recordset, rather than an Object?

also, if this is the case

Set rs = Me.RecordsetClone

and not

Set rs = Me.Recordset.Clone

 

[Tim L]

If VBA uses the same convention as other Object Oriented programming then Object is the highest class that can be created and therefore 'recordset' must be a subclass of it (AFAIK).


This is the code from my other db:

Private Sub SelectEquipmentComboBox_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Equipment name] = '" & Me![SelectEquipmentComboBox] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This definitely works, but as I mentioned before the newer db has a list box with 3 columns, of which only 2 are visible. The list box (or drop-down box, it converts between the two without problems) in the older db has only one column/field.

I'm presuming that the bit:

& Str(Nz(Me![Combo19], 1))

is used to compare to the correct part of the 'list' - I've just had another look at the code, and the '1' on my most recent attempt, is a '0', still no luck though.

Tim

 

[jjturner]

Tim,

Shouldn't the ListBox automatically use your bound column as the parameter? And if your bound column is numeric, get rid of the single quote encasement as I suggested earlier. Also get rid of 'Str(Nz' syntax.

On the other hand, you won't be able to reference the bound column if you have the ListBox set to MultiSelect.

HTH,
John

 

[Tim L]

I have had a bit of time to review what is happening in the code.

& Str(Nz(Me![Combo19], 1))

The & is used to concatenate the result returned by the Str function.

The Nz tells the function that it is receiving a number (from Me![Combo19])

The ,1 is also part of the Str function telling the function what to do if the value is null and is not part of the binding as was discussed previously in this thread.

Unfortunately I have still to work out how to get the form to display the current record.

To recap:

I have table of equipment, each of which may have one or more jobs associated with it. The initial form displays a list of the equipment, when one of the equipments is clicked on a new form opens which shows the equipment details and a list of all of the jobs still waiting to be completed. The form can be stepped through all of the (uncompleted) job records. I have a list box on the form, which was placed by using the list box wizard, which should go to the record clicked on. Unfortunately it doesn't.

The code used is listed elsewhere in this thread so I won't duplicate it here.

So, can anyone provide some insight into why listbox record selection isn't working?

Yours,

Tim

 

[Matthew Snook]

Is it possible that having the code in both the AfterUpdate and Click events is causing the problem?

The reason for putting this code into the AfterUpdate event is that you want to go to the record matching the item just selected. If the Click event fires before the AfterUpdate (which would make sense) then the code is executed before the box has accepted the new value; in other words it still contains the old value, probably the ID or pointer to the current record, so when the code executes you go nowhere. The code is therefore working, it is just taking you to the current record because the control never even makes it to the update event.




Matt

 

[Tim L]

Hi Matt,

thanks for the tip.

I have now solved the problem, although it was not exactly caused by what you suggested.

The field used to located a record [JobID] was supposed to be Indexed with No Duplicates. The database was originally created in Access 2.0 and there was no Autonumber field type available, I therefore decided to use Date()+Time() as the default value for the new records, assuming that this would always produce a unique value, which it appeared to do as the Indexed No Duplicates property never complained. Anyway on investigating the field values I discovered that many groups of equal values existed.

As I am now developing the database in Access 97 I changed the values in the JobID field to consecutive numbers (which had 'update cascade' enabled), removed the relationship, renamed the field, created a new autonumber field with the name JobID and hey presto the problem was solved (oh and then after confirming that all of the values were correct I deleted the old field).

I only noticed the duplicate value anomally when I realised that the function was working properly on some of the records and not all of them.

So, the moral of the story is that if you want the 'go to a record' list box/combo box to work you need to ensure that the field it is searching in has unique values.

Anway, thanks to everyone that replied to this post. I hope that by posting the eventual solution that I can assist someone else with a similar problem.

All the best to everyone.

Tim

 

[Tim L]

and finally...

I have realised that in the code:

Str(Nz(Me![Combo19], 1))

the ",1" is the value returned if the value in Combo19 is null. Only just realised that now....

Oh well, better late than never I suppose.

 

[Tim L]

Thanks the clarification Pat.

Unfortunately I can't take credit for the code because it was the default code produced by the 'find a record' combo box wizard. As usual it was the human part of the equation that didn't understand what was going on...

Tim