Can't get listbox link to form right

[BarryMK]

I'm trying to open a form from the second of two cascading listboxes and have tried umpteen combinations of link criteria and filters. My latest try is below but I'm getting Run time error 2465 can't find the field ServiceName referred to in the expression.


Private Sub lstServices_AfterUpdate()

DoCmd.OpenForm "frmNewRecUnsol", acNormal, , [ServiceName] = Me.lstServices.Column(2), acFormEdit


End Sub

 

[Bat17]

DoCmd.OpenForm "frmNewRecUnsol", acNormal, , "[ServiceName] = '" & Me.lstServices.Column(2) &"'", acFormEdit

 

[BarryMK]

Thanks that's just opening the form on a filtered blank record but not displaying any records containing the link and I've been there before! I tried removing the acEdit at the end of the string but to no avail. If I don't respond again for a while it's due to being away from the office for the next few days. Cheers

 

[Bat17]

I would print the string to a msg box tempoarily so you can check whats being passed.

Peter

 

[BarryMK]

Hi Peter

Sorry I'm not sure how to get the string to display in a MsgBox and can't find the method searching the forum.
Barry

 

[Rich]

Are you sure the form you're opening has a field called ServiceName?

 

[BarryMK]

Hi Rch Absolutely that's what's baffling me.

 

[BarryMK]

Ive just created a form from the underlying query that has only the PK and the field ServiceName on it, named it frmNewRecUnsol and it still doesn't work so it's not any other code on the original form that's causing the problem.

 

[Rich]

And Me.lstServices.Column(2) is the third column in your list box?

 

[BarryMK]

Rich I've tried 1,2,&even 3 in the columns same result. It just occurred to me I might not have made clear that I want to open the data form on a new blank record with just the ServiceName field filled in from the listbox.

 

[Rich]

Is ServiceName a newly entered record?

 

[seth_belgium]

I would print the string to a msg box tempoarily so you can check whats being passed.

Peter

I'd still try to do this, so you can be sure that the value returned by "Me.lstServices.Column(2)" is the right one.

Try this: Msgbox Me.lstServices.Column(2)
It should return you the value of the third column in your list (I have tested this). Also remember that (0) = column 1, (1) = column 2, and so on.

But if I understand your question correctly, you'd like to open the form, go to a new record and set the value for the field "ServiceName" to a value that you have selected in the list "lstServices". Try this:

DoCmd.OpenForm "frmNewRecUnsol"
DoCmd.RunCommand accmdRecordsGoToNew
[Forms]![frmNewRecUnsol]![ServiceName].value = Me.lstServices.Column(2)

Lemme know if it worked!

Greetz,

Seth

 

[Bat17]

You can't filter to a blank record! If you actualy trying to open the form for a new record to fill in with one field filled in to match the list box then use code in the onopen event of the new form to grab the name and stick it in for you. If the form is not just used used from the first form then you may want to pass the value using OpenArgs when you call the form

HTH

Peter

 

[Bat17]

Ooops, I should have read the second part of Seths reply before posting

a slight variation,
DoCmd.OpenForm "frmNewRecUnsol", , , , acFormAdd
[Forms]![frmNewRecUnsol]![ServiceName].value = Me.lstServices.Column(2)

Peter

 

[BarryMK]

Hi Guys sorry I had an unexpected day off yesterday.

First the MsgBox showed that I should be referencing Column 1 not 2 so that's now cleared up. I'll remember to use this function in future to check my code.

Seth's code
DoCmd.OpenForm "frmNewRecUnsol"
DoCmd.RunCommand acCmdRecordsGoToNew
[Forms]![frmNewRecUnsol]![ServiceName].Value = Me.lstServices.Column(1)
Brings Run time error 2046 Command or action ReordsGoToNew isn't available now.

Peter's code brings
DoCmd.OpenForm "frmNewRecUnsol", , , , acFormAdd
[Forms]![frmNewRecUnsol]![ServiceName].Value = Me.lstServices.Column(1)
Brings Run time error 3326 This Recordset is not updatable

 

[Bat17]

Hmm, I assume that the form opens OK at a blank record.
Try
DoCmd.OpenForm "frmNewRecUnsol", , , , acFormAdd
[Forms]![frmNewRecUnsol]![ServiceName = Me.lstServices.Column(1)

Peter

 

[BarryMK]

Same error I'm afraid. It's breaking at this point
[Forms]![frmNewRecUnsol]![ServiceName] = Me.lstServices.Column(1)

 

[Bat17]

seems an odd error for what we are doing.
try hardcoding the entry to see if we can pin down the error.

[Forms]![frmNewRecUnsol]![ServiceName] = "XYZ"

I assume that you can enter data into the form by hand OK?

Peter

 

[BarryMK]

I changed the underlying query which wasn't helping matters and thanks to your help guys the field is now filling in as required. However, and there's always a however isn't there? The record won't save so it looks as if I've got my relationships screwed up. I'll try and sort it myself as it's an area I'm not strong on and I need the learning curve. If you don't mind I may very well come back to ask you to point out my error if I can't crack it on my own. I suspect it's something very basic. I'll let you know how I get on. Cheers Barry