Not in List Multiple Combo Box with the same row source (1 Viewer)

andy1968

Registered User.
Local time
Today, 09:53
Joined
May 9, 2018
Messages
131
I have a form with 3 combo boxes that all have the same row source, say frmCombo.


I would like to use a Not In List event to open a new form to enter new data, say frmDataEntry.


Is there a way for the frmDataEntry on closing to go back to the combo box on frmCombo that was the source of the call?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:53
Joined
Aug 30, 2003
Messages
36,118
If the not in list event is opening the form, I'd expect focus to go back to the combo that called it. Is that not happening? What's your code?
 

missinglinq

AWF VIP
Local time
Today, 12:53
Joined
Jun 20, 2003
Messages
6,423
If the not in list event is opening the form, I'd expect focus to go back to the combo that called it. Is that not happening? What's your code?
I have to agree with all of the above...we really do need to see your code.

What version of Access are you running?

And I have to ask...why do you have three Comboboxes with the identical RowSource? This seems most odd to me...but maybe it's just me!

Linq ;0)>
 

andy1968

Registered User.
Local time
Today, 09:53
Joined
May 9, 2018
Messages
131
So, the complication is that I want to use the same form for multiple combo boxes on this form, as well as other forms.


Here is the code I have for using on multiple forms:


Private Sub cmdClose_Click()
Me.Refresh
DoCmd.RunCommand acCmdSaveRecord
If IsNull(NameOfPerson) Then
'do nothing
ElseIf CurrentProject.AllForms("frmToDoListEntry").IsLoaded Then
Forms![frmToDoListEntry].Who = Me.NameOfPerson
Forms![frmToDoListEntry].Who.Requery
Forms![frmToDoListEntry].Who.SetFocus

ElseIf CurrentProject.AllForms("frmPeopleContractEnter").IsLoaded Then
Forms![frmPeopleContractEnter].txtName = Me.NameOfPerson
Forms![frmPeopleContractEnter].txtName.Requery
Forms![frmPeopleContractEnter].txtName.SetFocus

End If
DoCmd.Close acForm, "frmEnterNewPeople"

End Sub


This works great with one control on each form, but I would like to have it work on several different controls from the same form.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:53
Joined
Aug 30, 2003
Messages
36,118
You've sort of created your own method there. The "typical" not in list code wouldn't include form references. To continue with your method, you'd probably have to pass the combo name to the form in OpenArgs, and then use that instead of your fixed reference. My template code for calling a form from a not in list event is:

Code:
  Dim mbrResponse        As Integer
  Dim strMsg             As String
  Dim strForm            As String

  strForm = "frmPartVendorMaint"
  strMsg = NewData & " isn't in the Vendor Table.   Add a new Vendor?"
  mbrResponse = MsgBox(strMsg, vbYesNo + vbQuestion, "Invalid Vendor")
  Select Case mbrResponse
    Case vbYes
      DoCmd.OpenForm strForm, DataMode:=acFormAdd, WindowMode:=acDialog, OpenArgs:=NewData
      'new data is the information entered into the combobox

      ' Stop here and while the popup form is completed
      If IsLoaded(strForm) Then
        Response = acDataErrAdded       'add the data to the vendor combobox
        DoCmd.close acForm, strForm     'close the popup form
      Else
        Response = acDataErrContinue      'error occured ... don't add the data
      End If
    Case vbNo
      Response = acDataErrContinue     'user declined to add the data
  End Select

The form would hide itself rather than close itself when you're done, and this code would take over at that point. I've had a couple of glasses of wine if that's not clear. :p
 

andy1968

Registered User.
Local time
Today, 09:53
Joined
May 9, 2018
Messages
131
Thanks Paul.


The code I have is for closing the input form after the new data is entered and going back to the form that called the Not_In_List event.


I like your idea about passing the combo box name.



I've got it to work.


Attached is a piece of my database for reference.
 

Attachments

  • pass multiple combo.accdb
    732 KB · Views: 25

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:53
Joined
Aug 30, 2003
Messages
36,118
No problem, glad you got it to work.
 

mike60smart

Registered User.
Local time
Today, 16:53
Joined
Aug 6, 2017
Messages
1,899
Hi Andy

I looked quickly at your sample Db and have the following observations:-

1. Most would recommend that each table has its own Autonumber Primary Key vice your Autonumber using a Text Data Type.

2. Would warn against using Lookup fields at table level.
 

andy1968

Registered User.
Local time
Today, 09:53
Joined
May 9, 2018
Messages
131
Thanks Mike.


1. Not sure I understand this point. Some tables I get using an autonumber for the key field. Why is this always the best method? Do you just force other field to not allow duplicates?



2. I like using the look up at the table level - minimizes the work when making forms. What is the downside?
 

andy1968

Registered User.
Local time
Today, 09:53
Joined
May 9, 2018
Messages
131
Thanks Mike.


Point 2 sounds worth investigating. Many downsides I did not even know about.
 

Users who are viewing this thread

Top Bottom