Listbox autonumber confusion 1

[trueharted]

In Access I have a form with a list box that the user picks a client from and then presses a button to pull up the client form filtering the form for the client they chose. Okay, so here's my problem. It works great until I use an autonumber field as the bound field for the list box and filter. On a number field it works fine, but on an autonumber field I get error 2501 - action was canceled. Is there a syntax problem here? Here's my code:

Private Sub btnShowRecord_Click()
'Find the selected record, then close the dialog box.

'Find the selected record.
DoCmd.OpenForm "frmClients", , , "[ClientID]='" & List0 & "' "

'Close the dialog box.
DoCmd.Close acForm, "frmGoToRecordDialog"

End Sub

 

[Rolliee]

Don't use an autonumber. Your users should not even know it exists. If you need a new number, creat it!

rollie@bwsys.net

 

[trueharted]

Rolliee,

I haven't figured out how to create my own auto incrementing number yet. Can you explain it to me simply?

 

[Rolliee]

Sure. Create a field and your own ADD button. (the wizard will help here) The after you have added a new record, make it's number equal the autonumber. It will have to be a type long.

Do it like this

me.MyID = me.ID ' where ID is the autonumber. Now you can work with this one and not get your feet tangled in it.

rollie@bwsys.net

 

[Laois]

It should work fine with an autonumber. I mean that's why you use an autonumber, to make each record unique and so you can easily find records. If you use the button wizard to create your btnShowRecord button on your frmGoToRecordDialog form, it will generate the following code which works perfectly.

Private Sub btnShowRecord_Click()
On Error GoTo Err_btnShowRecord_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmClients"

stLinkCriteria = "[ClientID]=" & Me![List0]

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btnShowRecord_Click:
Exit Sub

Err_btnShowRecord_Click:
MsgBox Err.Description
Resume Exit_btnShowRecord_Click

End Sub

Why would you want to create another field to duplicate what is in the autonumber field. An autonumber field is just a Long number type except that Access increments it for you.

Dermot

 

[danvlas]

It will work if you drop the single quotes:

DoCmd.OpenForm "frmClients", , , "[ClientID]=" & List0


Dan

 

[trueharted]

Rolliee,

Thank you for your help but I'm going to go with danvlas' idea. It's much quicker.


Danvlas,

You are my hero! Here's a star.