RecordID select from List Box

[RichCraig]

Please someone, help relieve the headache I've got with this problem!
Ok. I have three fields [ID],[Area],[Equipment]
The problem is that there are many areas with the same equipment.

Im listing all three in a list box on a form and would like to be able to click on the list to move to that RecordID so I can Edit,Delete or add new. I've used bookmarks, but they only seem to remember the one I've selected and Not moving to the record.

Could someone enlighten me as to how Im suppose to be go about this?

Ive tried

    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Equipment] = '" & Me![List0] & "'"
    Me.Bookmark = rs.Bookmark

but this won't search the autonumber field for ID.

Many Thanks

Richard

 

[Rolliee]

Richard,

You are telling ACCESS to look for something in the 'equipment' field. Look in the ID field.

SRCHstr = "ID = ' " & me.something & " ' "

rs.findfirst SRCHstr

I know that is your problem. I am not sure I have given you
the exact code. I beiieve it is right.

rollie@bwsys.net

 

[RichCraig]

Hi Rollie

thanks for the reply. Ive got

Dim rs As Object
    Set rs = Me.Recordset.Clone
    SRCHstr = "Rec_ID = ' " & Me.Rec_ID & " ' "
    rs.findfirst SRCHstr
    Me.Bookmark = rs.Bookmark

but Im just getting "Update or CancelUpdate without AddNew or Edit". This has been doing my head all day.
Will Me.Rec_ID just take the previous ID or will it accept the recordID I click on?

Thanks again

 

[judgehopkins]

Rich, this may give you some ideas:

http://www.tek-tips.com/gviewthread.cfm/lev2/4/lev3/27/pid/702/qid/538080

Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.

 

[Rolliee]

It takes the one your form is pointing to.

rollie@bwsys.net

 

[RichCraig]

THanks judge, that cleared some things up.

It all nearly works. At the moment I can select a record, but I don't think its going to it as it seems to select the record I choose as well as the first record in the table and changes the Equipment of the first record to match the one I've selected. Im guessing I need to stick some sort of GoTo in?

Private Sub List0_Click()

    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.findfirst "[Equipment] = '" & Me![List0] & _
    "' AND [Rec ID] = " & Me![Rec ID] & _
    " AND [Area] = '" & Me![Area] & "'"
    
    Me.Bookmark = rs.Bookmark

    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdRefresh
End Sub

 

[judgehopkins]

http://www.mvps.org/access/forms/frm0005.htm

Have the form move to the record selected in List/Combo box
Author(s)
Dev Ashish


(Q) I would be like to be able to have my form automatically go to a particular record when I click a row in a list box or select a particular value in a combo box. How do I do this?

(A) If you have the ID column as the bound column in your listbox/combobox, you can put the following code in the AfterUpate event of the control:

CAUTION: Make sure you have read about the Bookmark Bug.

'****** Code Start *********
'Move to the record selected in the control Me.RecordsetClone.Findfirst "[ID] = " & Me![ComboOrListboxName] Me.Bookmark = Me.RecordSetClone.Bookmark
'******* Code End *********



Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.

 

[judgehopkins]

BTW, I think the Bookmark Bug has been squashed since that code was written.

Judge Hopkins

http://www.psc.state.mo.us/adjudication.asp

There are only two rules for success: (1) Never tell everything you know.

 

[RichCraig]

Thanks again judge, but does this code just search a single column list? Im displaying 3 columns at the mo to show [area] [equipment] [Rec ID].

I keep having the problem that the first record's equipment changes to the selected equipment, does anyone have any ideas why? All this so users can add/mod/delete equipment or areas without looking at nasty tables, Groan!

 

[RichCraig]

Judge Hopkins, Rolliee!
Thanks for all your help, but Im afraid you can't help me from being me. Fixed it after all by not using a control source and leaving the list unbound.