Showing hidden combo boxes

[BDawg04]

When my form opens only certain combo boxes show. I have set the others to show only after I click on the preceeding box. My problem is when there is data in one of the combo boxes that is hidden, it won't show. I have attached a screen shot. The shot is when the record is first opened. It is only showing four names under the 'Memo Sent To'. There is actually a name in the 5th box that is hidden. I was wondering how to make that 5th box (or any other boxes with information) show automatically when it is required. Thanks for any help you can provide.

 

[wiltshire]

you could have some code attached to the form so that, on change of the record, or initial load, it looks at each combo box and only makes it visible if the value is not null. You would also need to have settings for making it invisible if the value is null. That way, when you move between records, it always checks all combo boxes, cos each record will have different number of boxes.

The settings would be in form design using the 'on load' and 'on current' setttings to point to a macro or VB code.

Hope this helps, it's worked for me on some of my databases

 

[BDawg04]

Thanks for the help. I'm still confused though. This is the code I put in on the 'On Load' event.

Private Sub Form_Open(Cancel As Integer)

If Me![Memo Sent To5] = Not Null Then Me![Memo Sent To5].Visible = True
Else: Me![Memo Sent To5].Visible = False
End If



The Cancel as Integer in the () just showed up. I'm not really sure why that showed up. Same thing goes for the colon after 'Else'. I'm sure I have done something wrong here but I am confused about this. There is some other code in this event related to that Combo box. I intially have all combo boxes 5-15 set to not visible. I added the above code and I am getting some errors. I just don't know what to do to fix it.

 

[wiltshire]

sorry - I can do most things with databases, but never learned VB so I do it using macros, hopefully someone else out there can help with the VB code !! Good luck

 

[BDawg04]

Thanks anyway.

 

[ghudson]

If Not IsNull([Memo Sent To1]) Then [Memo Sent To1].Visible = True
    ElseIf Not IsNull([Memo Sent To2]) Then [Memo Sent To2].Visible = True
    ElseIf Not IsNull([Memo Sent To3]) Then [Memo Sent To3].Visible = True
    ElseIf Not IsNull([Memo Sent To4]) Then [Memo Sent To4].Visible = True
    ElseIf Not IsNull([Memo Sent To5]) Then [Memo Sent To5].Visible = True
Else
     'do something else?
End If

A case statement would be cleaner.

 

[BDawg04]

Thanks for the input. However it's telling me that there is a 'Else without If' when I try to run it. I guess I am overlooking something small but I tried to put this in the onOpen event and I can't get it to work. If you have any further help I would appreciate it.

 

[mresann]

ghudson used the If function incorrectly in his example. His format should have been as follows:

If Not IsNull([Memo Sent To1]) Then
   [Memo Sent To1].Visible = True
ElseIf Not IsNull([Memo Sent To2]) Then
   [Memo Sent To2].Visible = True
ElseIf Not IsNull([Memo Sent To3]) Then
   [Memo Sent To3].Visible = True
ElseIf Not IsNull([Memo Sent To4]) Then
   [Memo Sent To4].Visible = True
ElseIf Not IsNull([Memo Sent To5]) Then
   [Memo Sent To5].Visible = True
Else
    'do something else?
End If

Of course, the Select Case format would be cleaner and lead to less ambiguity.

 

[Rich]

Do you have a combo for each person that you send a Memo to, if so you have a design problem with your db?

 

[BDawg04]

The form view is attached to my first post. The design view is attached to this post. I have 15 combo boxes on the form for 'Memo Sent To'. There is also a box for their job title next to it. The records store everything right. I just can't get the right number of combo boxes to display for each record. I'm assuming that since the records are being stored right that everything is ok. I tried to add the code from the other post but nothing has worked so far. The second set of If statements did not produce any errors like the first set, but it didn't display the right number of combo boxes either. I may have put it in the wrong event or messed up something else. Either way I'm confused on how to get this thing working. I've been looking at this for a while now and haven't gotten anywhere. Sorry for all the conusion but I'm still fairly new to Access and some of these things just don't make a lot of sense to me. Thanks for your help.

 

[ghudson]

Try the OnCurrent event.