Dropdown box help

[Geodon7]

I am having 2 problems with a portion of a form I am creating. The way it is set up is that there is a dropdown with 3 options (contract, project request, and other). If contract is selected, another dropdown appears with projects related to contract, if project request is selected, a dropdown appears with projects related to project requests, and if other is selected, a textbox appears for text to be entered. I've done that using .Visible (I will post the code below).

Private Sub Service_Bound_By_AfterUpdate()
    If Me![Service Bound By] = "Project Request" Then
        Project_Name.Visible = True
        ProjectName.Visible = False
        IfOther.Visible = False
        
    ElseIf Me![Service Bound By] = "Contract" Then
        Project_Name.Visible = False
        ProjectName.Visible = True
        IfOther.Visible = False
    
    ElseIf Me![Service Bound By] = "Other" Then
        Project_Name.Visible = False
        ProjectName.Visible = False
        IfOther.Visible = True
    
    End If

End Sub

Now, the first problem is that if the user makes an original choice, then selects a project, then changes his choice in the first dropdown box, the first project that was selected is still being stored. How do I undo the storage of the first project choice? I was thinking of using .Undo, but I don't think the code I used is correct. Here is what I tried to use:

Private Sub Service_Bound_By_BeforeUpdate(Cancel As Integer)
    If Me![Service Bound By] = "Project Request" Then
        ProjectName.Undo
        IfOther.Undo
    
    ElseIf Me![Service Bound By] = "Contract" Then
        Project_Name.Undo
        IfOther.Undo
    
    ElseIf Me![Service Bound By] = "Other" Then
        Project_Name.Undo
        ProjectName.Undo
    
    End If
End Sub

The next problem is that I have the form set to be continuous, and when one option in the first dropdown is selected on one record, the same is selected for all the others. Is there a way to make it so the selection is only for the record you are on and have it not affect the other records?

Thanks for your help!
G

 

[CautionMP]

I don't use the .Undo method, I would just set the after update procedure to set the ProjectName field to either "" or null;
ProjectName.Value = Null
As far as the Continous form problem goes, the only way to avoid the problem is to bind the dropdown box or put the records in a subform and just use the main form as input to update records.

Hope this helps.

 

[Geodon7]

Caution-

Your first suggestion worked perfectly with the value=null. Thank you! But I'm not sure I understand your second suggestion. How do I bind the dropdown box? Or if I use the other option you mentioned, how would I go about doing that?

Thanks for your help!
G

 

[CautionMP]

To bind the dropdown box you would need to add a field to the underlying table where data from the dropdown box would be held/stored.
The form/subform solution is a neat way to do it but it is a pain to setup, I've done it twice, and now that I think about it I'm not sure I remember how I did it. I remember it took me about 20 tries to make it work. If you feel eperimental check the Help topic for "Bookmarks" in Access.
If you have the ablilty to add a field in the table that would be the easiest solution.

 

[Geodon7]

I added a field to the table, and the dropdown box selection now gets entered into a field called "ServiceBoundBy". Everything else is still basically the same. Am I missing something else? I changed the code so that everything works as it did yesterday, but I still can't get the selection to only apply to a single record. I might have described the problem wrong though.

The problem is not with the first dropdown box, instead it is with the second dropdown box (or textbox) appearing. Whatever selection is chosen in the first dropdown, makes the dropdown/textbox appear for that selection. I want that to only apply to a single record, so that with each record, you will only see the second dropdown/textbox that should be visible for that record.

I hope this is more clear. Any suggestions?

Thanks,
G