Excluding records from the drop down list in a combo box in a subform

[julie67]

I have a subform to add names to events and I want to exclude the names that are no longer active so that they are not inadvertently invited to an event. I went to the Row Source field in the combo box properties and changed the query to include the Active? field, but it is not working. Any suggestions?

Thanks,
Julie

 

[jaydeebetoo]

can you post the sql?

 

[julie67]

Thanks--I just figured it out...I must not have saved the query, but it works now.

However, I can't figure out how to cancel adding a forbidden record. Tbis subform is in a datasheet view, so when I add a command button to Undo the operation, it doesn't come up. For example, I put in a requirement that the combination of the eventid and nameid should be unique. So if you enter a name that is already on the list, you can't cancel the operation. I just get the error and I have to close the whole form. Any suggestions for that?

Thanks,
Julie

 

[jaydeebetoo]

try adding an error handler to the form that gives the user a better message. Something like this (replace 1234 with the error number you get)

on err goto errhandler

ErrHandler:

Select Case Err 'Err is already defined in vba to hold the numeric code for errors

case 1234
msgbox "That eventid with that nameid has already been entered"

End select

 

[julie67]

Where do I enter that code?

 

[ProgramError]

You need to enter it in the code using the vda editor which is activated by pressing 'alt' and 'f11' together.

look for the form you are working on, by clicking the project explorer icon in the menu bar and double clicking on the form name. It open it in a window.

look for the object you are working with by selecting it in the top left and right dropdown lists on the newly opened window.

ie if the object is called ddlist it is invoked by clicking on it... select ddlist in the left dropdown list and click in the right dropdown list
This will take you to the proceedure associated with the control and it will look something like

private function ddlist()
On Error GoTo Err_ddlist_Click
    
   your code!!!!

Exit_ddlist_Click:
    Exit Sub

Err_ddlist_Click:
>> put the error handling routine which jaydeebetoo wrote in here and remove the msgbox line below<<    
    MsgBox Err.Description
    Resume Exit_ddlist_Click
end function

Hope this helps you into the nightmares of VBA programming

Program Error
Why is it, I still think in terms of spectrum BASIC!