Filter a form using VBA

[SallyJenkins]

I hope I've posted this issue in the correct forum.

‘ Get the ID from the list box
sContactId = Me.lstHusbLast.Column(2)
‘ Convert ID to integer
iContactID = Int(sContactId)
DoCmd.OpenForm "ClientServicesInput", , , WhereCondition:="Clientid =" & iContactID

Please see attached file with screen shots.

 

[CJ_London]

As far as I can see you just meet ro remove the WHERE CONDTION text i.e.

DoCmd.OpenForm "ClientServicesInput", , , "Clientid =" & iContactID

Not sure why you are converting text to numeric - I would expect it to be numeric anyway so the following should also work

DoCmd.OpenForm "ClientServicesInput", , , "Clientid =" & lstHusbLast.Column(2)

 

[namliam]

Remember that listboxes are 0 based arrays, thus by entering 2 you are actually picking up column number three.

 

[gbnz]

I have had issues in the WHERE Clause that if I don't encase the key's name in brackets [] it may not work. So I always encase

[FONT=Calibri][SIZE=3]DoCmd.OpenForm "ClientServicesInput", , , "[Clientid]=" & iContactID[/SIZE][/FONT]

 

[SallyJenkins]

Hurray!!! It worked. Here's the resulting statement that worked:

DoCmd.OpenForm "ClientServicesInput", , , "[Clientid] = " & iContactID

Thanks a bunch 1 and all.

(BTW, yes, I know that array's are '0' based)

 

[SallyJenkins]

Dang! it broke again. I have no idea why it seemed to work once and not after I restarted the computer.

 

[gbnz]

Dang! it broke again. I have no idea why it seemed to work once and not after I restarted the computer.

Hmm interesting...I have had projects work for a while and then stop working...is there anyway you can zip up the DB without data and attach. I will take a look at it if you would like.