jumping to a specific record on same form... 1

[Chummly66]

Here is what I have thus far:

1) A form which allows me to enter a record which includes Make, Mode, and ID number. form is called "frmPhoneUpdate".
2) Data is stored in a table called "tblModels". Its PK = "LngModelID"
3) On the form, frmPhoneUpdate", I have it broken into to segments. The top 1/2 has the entry text boxes allowing me to enter the data. The bottom 1/2 has a list box which displays a query, which is merely a query showing all the data from tblModels. Basically, it provides me a listing i can scroll through and see what models are already in the table.

What I am trying to do is if I find a record within that list box that needs editing, I would like to double click it within that list box and have the top 1/2 of the form go right to that record for me. I have done this numerous times with a separate form, but now I am dealing with one form only.

I tried the following under the double-click property for the Listbox, but I keep getting syntax errors. I tried to rework the line I use to go to a record on a separate form, but am not having luck. here is the code:

DoCmd.GoToRecord , , "tblModels.lngModelID = " & Me.List14, , acDialog

Thanks for any help in advance guys!
Richard

 

[ClydeData]

Enter this code in the after update property of your list box

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[lngModelID] = " & Str(Me![List14])
Me.Bookmark = rs.Bookmark


Hope this helps

Jimmy

 

[Chummly66]

Jimmy,

Thanks for the reply.

But I am unclear as to why you would put that code in
the "after update" property in the list box and how that will allow me to double-click on a line item and have the top part of the form jump to that record.

I added your code in (cut & pasted) and as far as I could tell, nothing diferent happened.

thanks,
Richard

 

[HandsOnAccess]

Jimmy's Concept is correct, only this is that you can place that code in List14_DoubleClick Event.

Here two things need to be taken care.

In this Expression
rs.FindFirst "[lngModelID] = " & Str(Me![List14])

First check to which field the List14 is bound to.
You must same field in the above expression

Secondly you must verify this expression
Str(Me![List14])

If the field that is bound to List14 is of Text Data Type, only then you can use Str() function, and for a string type the correct expression is

rs.FindFirst "[lngModelID] = '" & Str(Me![List14]) & "'"

Imp : Notice the Quote

If the Field that is bound to List14 is of Numeric data type then the expression is

rs.FindFirst "[lngModelID] = " & Me![List14])

Hope this helps you...

Regards,

 

[HandsOnAccess]

There are few mistakes in the above post Kindly Ignore the same.

Here is the corrected answer

Jimmy's Concept is correct, only thing is that you can place the same code in List14_DoubleClick Event.

Here two things need to be taken care.

In this Expression
rs.FindFirst "[lngModelID] = " & Str(Me![List14])

First check to which field the List14 is bound to.
You must use the same field in the above expression
i.e., for the above expression to work
List14 must be bound to [lngModelID]

Secondly you must verify this expression
Str(Me![List14])

If the field that is bound to List14 is of Text Data Type, only then you can use Str() function,
and for a string type the correct expression is

rs.FindFirst "[lngModelID] = '" & Str(Me![List14]) & "'"

Imp : Notice the Quote

If the Field that is bound to List14 is of Numeric data type then the expression is

rs.FindFirst "[lngModelID] = " & Me![List14]

Hope this helps you...

Regards,

 

[Chummly66]

Handson,

Thanks for the detailed information.

fyi.. the Listbox, "list14", is not bound to anything. It merely displays data from a query. The query itself has 5 columns: LngModelID, Make, Model, CompID and NokiaID.

So, when I open the frmPhoneUpdate, it automatically does two things: First, is display record #1 in its text fields, and two, in the list box, it displays the results of the query.

I'm going to try and take your info and see if I can get it to work. thanks for all your help, and I'll let you know how it goes

Richard

 

[HandsOnAccess]

By saying "Bound" I actually meant "Bound Column"

In Access When it comes to ListBox and ComboBox "Bound Column" actually refers to the column in the SQL Statement that the control is bound.
It is different from binding it to a Control Source.
The Control Source may be empty,
But Bound Column will always contain a Numeric Value.
Bound Column can never be empty.
By Default the value of Bound Column is 1,

When a List box rowsource query contains multiple fields,
You can choose to bound the value of the listbox to any column.

You can check the bound column value of your list box
under "Data" Tab of the Properties Window.

Regards,

 

[ClydeData]

Richard
as you have LngModelID as the primary key then you may wish to hide it if it is not relevant for the user to see it

In ur SQL have LngModelID as the first column

in the column width property set its width to 0

so have bound column=1

and column widths= 0cm;2.50cm;2.50cm;2.50cm;2.50cm

All Hands Ons comments are very useful


Hope this helps

Jimmy