Combo Box Problem (1 Viewer)

[Robbyp2001]

Hello folks.

I have a question for one of the gurus on here. Imagine an Access form that contains a Combo box (say Combo20) that has a number of options available for selection. However, option 13 "additional info" is the only one that prompts for further action. The other options do nothing upon selection other populate the box and store the value.

What I'd like to know is, is it possible, upon selection of option 13 only, a pop-up form can be produced and additional info entered, whilst retaining the ID value in the underlying table. This form does not appear for any other option.

There is no room on the main form to embed this additional information permanently, thus the need for a pop-up to appear only upon selection of '13'.

I hope that I've given enough information here and I look forward to the experts coming up with a solution as always!

Rob

 

[Jeanette Cunningham]

On the after update event of the combo you could test for the value of the combo and if it is 13, open the pop up form where the user makes an edit to the data for that record. I assume that you have a textbox on the form that will show the additional info for the appropriate column of the combo.

When user clicks save or close on the pop up, use the unload event of the popup form to requery the combo back on the original form and you probably want to requery the original form as well.

I remember a sample database where the combo was used to add additional info - don't know if it is still available.

 

[Robbyp2001]

Hi Jeanette, Many thanks for your prompt reply. This sounds exactly what I need. Unfortunately I do not know the code required to achieve this 'after update'. If you happen to come across a sample dbase where this function applies, please let me know.

 

[gaccess]

I have a similar arrangement sort of ish
So your control on your main form could be Combo20 (get into the habit of Naming things with relevant terms, it really helps)
I have chosen "3" that is the PK no. in my db for this popup req form

Private Sub Combo20_LostFocus()
If Me.Combo20 = "" Or IsNull(Me.Combo20) Then
MsgBox "Mandatory Field. A Name is Required"
Exit Sub
End If
If Me.Combo20 = "3" Then
DoCmd.OpenForm "frmPopUp", acNormal
End If
End Sub

Then in your popup you can push the value back into your main form like this
OnExit of ControlAdd
Private Sub ControlAdd_Exit(Cancel As Integer)
Forms!frmMain.Controltobeupdated = Forms!frmPopUp.ControlAdd
End Sub
you need to leave the main form open and close your popup after but this could be done by cmdClose after ControlAdd


get back if you have a question

 

[Robbyp2001]

Gaccess

Many thanks for your kind help. I'll try this immediately.

Regards

Rob

 

[gaccess]

I remembered I had this filed away

 

[Robbyp2001]

Thank you so much gaccess. You're a star!

Rob

 

[Pat Hartman]

Personally, I don't like hardcoding this type of process. I would add an additional column to the lookup table with a boolean that indicates when an item requires additional processing. Then rather than checking an actual ID value, I would check the boolean:

If Me.cboLookup.Column(2) = True Then
    ....
End If

rather than

If Me.cboLookup = 13 Then
    ...
End If

I know it is a small point but it makes the code more flexible and allows for future expansion without requiring code changes. I call it defensive programming. Always try to think of things your client might need to change in the future and code for that eventuality if you can. It will save work in the long run and make you look like a star.

 

[Robbyp2001]

Many thanks Pat, I'll look into this suggestion.

Rob