Set record source of a subform combo filter to null when adding new record

[Richie27]

Hi all,

I have a form with a subform.

The subform has combo box filters which I use to filter records on the subform.

However, I want to clear them when the user clicks add new record.

Thus far, I have come up with the following code - but it keeps saying "Object does not support this property or method"

Here is my code

Private Sub addNewRecord_Click()

On Error GoTo Err_addNewRecord_Click

Me![su_file subform].Form.cboBox.RecordSource = " "

DoCmd.GoToRecord , , acNewRec


Exit_addNewRecord_Click:
Exit Sub

Err_addNewRecord_Click:
MsgBox Err.Description

Resume Exit_addNewRecord_Click

End Sub

[pbaldy]

A combo doesn't have that property. I suspect you want the .Value property.

[R_Badger]

try:

Private Sub addNewRecord_Click()

On Error GoTo Err_addNewRecord_Click

Me![su_file subform].Form.cboBox.RowSource = " "

DoCmd.GoToRecord , , acNewRec


Exit_addNewRecord_Click:
Exit Sub

Err_addNewRecord_Click:
MsgBox Err.Description

Resume Exit_addNewRecord_Click

End Sub

I might suggest however you may want to just hide it instead as if the user click on it whilst in that state it will display an error as " " is an invalid rowsource.

[Richie27]

I used the .Value property and it worked, sort of - but when you click on it the values from the records, previous to clicking add new records are still there and very much selectable. I guess the user won't know they are there unless they click on them - which they might - but then they might get confused.

[R_Badger]

Well if you just dont want the user to be able to select them, this nulls the current value and then locks the control preventing further user input:

Private Sub addNewRecord_Click()  On Error GoTo Err_addNewRecord_Click  Me![su_file subform].Form.cboBox.value = null
Me![su_file subform].Form.cboBox.locked = true  DoCmd.GoToRecord , , acNewRec   Exit_addNewRecord_Click: Exit Sub  Err_addNewRecord_Click: MsgBox Err.Description  Resume Exit_addNewRecord_Click  End Sub

[Richie27]

Hey thanks r_badger - that works but kind of kills my combobox completely.

Ah well, sure will just hide it and hope they won't notice! ;-)

[R_Badger]

Check the other post I made above, it nulls the current value and then prevents input

[Richie27]

Ahhhh........the control is completely locked and it doesn't work anymore. I can see the value but can't select...........grrrrrrrr.......

sorry a bit of premature panicking there. I realised it's simply a matter of unlocking in the property sheet.

[R_Badger]

That's the idea that the control is completely locked........

What exactly are you trying to achieve/prevent?

[Richie27]

Lol. Sorry R badger, I just got a little emotional there.

I just want to clear it momentarily when someone goes to add a new record. I don't want to see the previous values and I don't want to be able to select them - but I may want to select them after that if I decide to go scrolling through my records.

Ah, cheers. I'll just set the values to " " and hope they won't notice - I don't think it's the end of the world really.

Thanks.

[Richie27]

Thanks for the lesson though - I may well use this code again. cheers!

[R_Badger]

There is a way round your problem, goto the forms afterinsert event and then add

Me![su_file subform].Form.cboBox.locked=false

[Richie27]

There is a way round your problem, goto the forms afterinsert event and then add

Me![su_file subform].Form.cboBox.locked=false

Ah ha, I thought there may be something like that!

The sun was shining outside yesterday and I was anxious to get out the door!

Please excuse my overly emotional outburst.

Thanks R_Badger - you are a helpful soul indeed!

Rich.

[R_Badger]

I know your pain, I was suffering the same yesterday and funnily enough this morning too!

[Richie27]

Lol. Me too.

Though I'd imagine the weather yonder is generally better than the wesht of Ireland!

It really is a treasured occasion here - and one which you must take full advantage of!

Fine summer weather may only last as long as the day that's in it.

Better stop procrastinating now as I have to present my wonderful Access project later.

Really, couldn't have done it half so quickly without the help of people like you on this forum.

I haven't met so many helpful people since I did the camino in Spain a few years ago.

Ahhh.......I perhaps talk to much, no? ;-)