Help with opening form, from combo box 1

[ramrags]

I'm trying to open a form from a combo box. I have a form with just a unbound combo box. in the after update event I have the code

Private Sub cmbNEWPAT_AfterUpdate()
Dim Criteria As String

If Not IsNull(Me!cmbNEWPAT) Then
Criteria = "RegistrationID = '" & Me!cmbNEWPAT.Column(1) & "'"
DoCmd.OpenForm "frmCODE"
Forms!frmCODE.Filter = Criteria
Forms!frmCODE.FilterOn = True

End If

End Sub

When I try to run this it comes back with an error Runtime error '2001' you canceled the Previous operation…. When it goes in to debug the criteria is correct. I'm stumped can anyone help? Thanks, Tom

 

[RoyVidar]

Hi!

Think you need to supply your open command with the criteria:

[tt]docmd.openform "frmCODE",,,Criteria[/tt]

The line "Forms!frmCODE.Filter = Criteria" can be removed, and i think the line "Forms!frmCODE.FilterOn = True" is assumed when opening the form this way (try to remove that too).

If you also need a "message box" like behavior of the frmCODE, then you could also add acDialog;

[tt]docmd.openform "frmCODE",,,Criteria,,acDialog[/tt]

HTH Roy-Vidar

 

[ramrags]

Thank you for the response but when i tried to open the form this way it comes back with the error that the open form action was canceled this is making me crazy!!!! Tom

 

[RoyVidar]

Sorry, just couple of thoughts:

Is there any code in the "frmCODE" on open event, which might cancel the opening?

The RegistrationID is a valid field in the "frmCODE"'s recordsource?

I just have to ask this too, if not appliccable, just disregard.

I've seen similar when using table level lookups (comboboxes at table level) where the ID value is located in the combos.column(0), and the value shown to users, the description, is located in combos.column(1). Might this be the issue?, have you tried what happens if you try just:

[tt]Criteria = "RegistrationID = '" & Me!cmbNEWPAT & "'"[/tt]

for text criteria or

[tt]Criteria = "RegistrationID = " & Me!cmbNEWPAT [/tt]

for numeric?

I see from yuor threads that you solved a similar challenge in april, is there anything from then you might apply?

If that's not it, lets hope someone else can shed some light.

Roy-Vidar

 

[ramrags]

You did it, my trouble was that the RegistrationID is a number field. I looked at this for hours but did'nt think of that. thanks for the help and it's working now. Tom