Forms: going to existing record based on index entry...

[AccessApprentice]

OK, I'm trying to find a way to get my form to move to the existing record in my database. The key I'm using is one I created called "Item Number" (not the default AutoNumber ID field). When someone types in (or selects) a value in the Item Number combo-box, I'd like for the form to either 1) go to the appropriate record for editing, if it exists, or 2) start a new record. I'VE TRIED ALMOST EVERYTHING...even the infamous:

Me.RecordSetClone.Findfirst "[Item no] = "& Me![Item no]
Me.Bookmark = Me.RecordSetClone.Bookmark

I cannot get that piece of code to work...I wind up generating one error or another, or when it DOES run it doesn't work properly. And I've even tried a few others. I'm using Access2000, for those who might want to know version numbers.

If anyone has a RELIABLE way to get this form to work, I'd appreciate it! THANKS!

 

[BlackKnight]

Hi,

I assume that the item number combobox is unbound.

Private Sub cboItemNo_Click()
Dim rs As New ADODB.Recordset

rs.Open "SELECT * FROM tblItem", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
'I assumed ItemNo is a text field. If not remove
'the single quotes.
rs.Find "ItemNo='" & Me!cboItemNo & "'"
If rs.BOF And rs.EOF Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

Have a good one!
BK

 

[AccessApprentice]

Thanks for your response, BlackKnight.

A couple of things:

The combo box is bound...it is bound to the 'Item Number' field on the database.

The field's name (and the name of the combo box on the form) is actually, 'Item Number'...do I include the space in that name when referencing it, or do I use an underscore (Item_Number)?

Another thing...I'm trying to include this code in the "Lost Focus" (or maybe "After Update&quot event of that combo box...that way, after someone types in a value and tabs away, the form updates. Usually when I do this and go into Codebuilder, it already has a "Private Sub Item_Number OnLostFocus ()" or something like that...in other words, it has already created a SUBroutine...and I found out the other day that I can't put a SUB inside another SUB...that means your code won't go in there. Do you have any ways around this?

THANKS!

 

[JPeters]

If you're trying to have the records change based on the combo box selection, I would recomend making the combo box Unbound, and have it update the Bound field only if it's a new record. Ie: have one combo box unbound ... and have one text box for this field that is bound on your form that's hidden. If Me.NewRecord then Unbound Box = Bound Box... something like that. This way you can use the bookmark technique to call records based on the unbound combobox selection - and it only updates a record if the record is new, so you won't overwrite your Item Number field everytime you change the selection in the combo box.

For your field name being 'Item Number' - I never ever ever put spaces in fields. ANd no, the underscore '_' doesn't replace a space. You actually leave the space in your code.

For your last question, after you go into the event procedure of your combobox... It places that Private Sub() and End Sub in there for you. Your job is to edit inbetween those two. You can add whatever code you want in there. Just think of Private Sub as Access' way of placing a begginning in there for you (incase you forget and you have un-organized code) and the End SUb is their Ending for you (so your code doesn't bleed over into other functions/SUb routines).

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------