How to reference a combo box on one form from another form.

[poporacer]

I have two forms, frmEdit and frmAddInmate. On frmEdit there are 2 comboboxes. The first combo box (cmbLogNum) is a list of incidents and from this combo box, the second combo box (cmbCDC2) is populated with the related data to the first combo box.
This is what I need:
I want that when the Not In List event is triggered for the cmbCDC2 a new form opens (frmAddInmate) to add data. I want the data that was entered in cmbCDC2 from the first form to be entered in the combo box on the second form (cmbCDCadd) to make data entry easier. Then I need that when I exit this form and return to the first form, the first form is updated (requeried?)with the new data and then the new data that was entered is selected cmbCDC2 and the whole form updated.

What happens:
I can get the second form to open but the data from cmbCDC2 does not transfer into combo box on frmAddInmate. I have tried several ways but it doesn't seem to work...It looks like the data that triggered the NotInList Event isn't stored in cmbCDC2. After that is done, how do I sychronize all the new data in the first form for data entry?

here is a portion of the code I have on the open event of frmAddInmate:
lblLogNum.Caption = Forms![frmEdit]![cmbLogNum]
***The above works
Me.cmbCDCadd = Forms![frmEdit]![cmbCDC2]
***this doesn't work

Thanks a million!

 

[CyberLynx]

When you open your second Form from within the NotInList event make sure you open it as Dialog. For example:

DoCmd.OpenForm "frmAddInmate", acNormal, , , acFormAdd, acDialog

Because the Form (frmAddInmate) is opened in Dialog mode, the code after the Domd.OpenForm statement will not fire until the Dialog Form is closed. We'll use this feature to our advantage.

But first....The purpose here is to obviously add an Inmate to the Combo Box in frmEdit so I personally don't understand why you would need the Combo Box in the frmAddInmate Form. I can understand auto filling a InmateName Text Box but not a Combo. I can't see the Database so I'm sure you have reasons for this.

I will show how to fill the Combo Box on the Second Form from the populated Combo Box in the first Form then place the selected Combo Data from the first Form into the Text area of the Combo Box in the second Form.

In the OnOpen Event of the second Form:

Private Sub Form_Open(Cancel As Integer)
[COLOR="DarkGreen"]   'Make sure the Form named frmdit is indeed open. The
   'IsLoaded Function does this. You should be familiar with
   'this Function by now. If not, then you can find it in this
   'Forum.[/COLOR]
   IF IsLoaded("frmEdit") = True Then
      Me.cmbCDCadd.RowSource = Forms("frmEdit").cmbCDC2.RecordSource
      Me.cmbCDCadd = Forms("frmEdit").cmbCDC2
   End If
End Sub

If there are more than one Columns within your cboCDC2 combo Box then you will need to reference the column number of the Item Selected. So, the line:

Me.cmbCDCadd = Forms("frmEdit").cmbCDC2

May look need to be:

Me.cmbCDCadd = Forms("frmEdit").cmbCDC2.Column(0)

It all depends in which list column the data you want to reference resides in.

If this is to merely fill a Inmate Name Text Box in the second Form (frmAddInmate) then you would have this within the OnOpen event of the second Form (frmAddInmate):

Private Sub Form_Open(Cancel As Integer)
[COLOR="DarkGreen"]   'Make sure the Form named frmdit is indeed open. The
   'IsLoaded Function does this. You should be familiar with
   'this Function by now. If not, then you can find it in this
   'Forum.[/COLOR]
   IF IsLoaded("frmEdit") = True Then
      Me.InmateName = Forms("frmEdit").cmbCDC2
   End If
End Sub

Now when the Form frmAddInmate is closed....the whole idea would be to update the cmbCDC2 Combo Box with the newly entered data when we return to the first Form (frmEdit).

Because we opened the second Form in Dialog mode, we can safely place this line of code beneath the DoCmd.OpenForm..... code line within the NotInList event since we know it wont fire until the frmAddInmate Form opened closes:

Me.cboCDC2.Requery

Done Deal.

.

 

[poporacer]

clarification

I tried your suggestion but it didn't work. I tried variations but is still didn't work. I don't think I was clear in what I was doing, hopefully this makes it a little clearer. The combobox on frmAddInmate (cmbCDCAdd) has a row source of all inmates previously involved in an incident. (therefore changing the Rowsource will not list all "available" inmates) this is what I would like to happen. In the frmEdit you want to add an inmate to an incident. I will have an add button but would also like to add it through the NotInList event. So when you type a number that is not in the list it opens frmAddInmate (maybe this is where the misunderstanding stems from...this form does not add an inmate to the database it adds an inmate to an incident). When frmAddInmate opens, I pull the Log number from frmEdit...the code for this works and the next code should be similiar I would assume except that I am putting the info into a combo box. So when the form opens cmbCDCadd already is populated with all the inmates that have previously been involved in incidents. So now if the inmate is in the combo box row (and previously inputted) you just click on the add button. If the inmate has not been previously inputted, then the NotInList event for this combobox will fire and then I can add a "NEW" inmate. (this should be fairly simple after I get past this hurdle). I am attaching the DB to hopefully see what I am talking about.
So to recap.
1. Select an Incident
2. Type in a CDC number that is not in the combo box
3. When frmAddInmate opens the Incident number (this works) and the CDC number (this does not) should be in the form.

I am sure I have probably made this as clear as mud, but hopefully you are tracking.

 

[WayneRyan]

Popo,

You have to handle the new value in the combo first, then you can continue. I don't think
your combo's contents are valid until you EXIT the "NotInList" event properly. Then, you
can open your form and use the combo.

Use the Search Facility here and look for "acDataErrAdded" for examples.

Also, you have a misplaced bracket:

Me.cmbCDCadd = Forms![frmEdit]![cmbCDC2].Column(1) <-- should not be at the end

Wayne

 

[poporacer]

A little clearer

From the results I was getting I assumed that the data was not really there...I wasn't sure at what point it was available. Using the acDataErrAdded would probably work. I changed the bracket and added the acDataErrAdded. The combobox still did not have the data...grrrrrr. When I looked in the debug window it still listed the value of the combo box as null...I think you might have to exit entirely out of the NotInList event for the changes to take place?????
I will keep plugging away.

Thanks

 

[CyberLynx]

Hmmm..... Ok, now I see.

It's like Wayne has mentioned. The ComboBox basically contains nothing for as long as it's Not In List and the Field saved. Referencing this ComboBox is fruitless, but there is something that does hold the entered data and that is the NewData variable within the NotInList parameter set.

Oddly enough, you are referencing this Variable when you open the frmAddInmate Form by way of placing it into the OpenArgs property of the OpenForm Function located within the NotInList event....but you don't utilize it anywhere within the frmAddInmate Form.

Well....utilize it. It contains the data you seek to place.

Instead of referencing the cmbCDC2 ComboBox located in the frmEdit Form, reference the OpenArgs property of the frmAddInmates Form since that's where you placed the entered CDC number anyways

In other words, replace any line with Forms![frmEdit]![cmbCDC2] with Me.OpenArgs. If you do this within the frmAddInmate Form's OnOpen event, you will find the the supplied data will be entered into the CDC Number ComboBox of that Form.

lblLogNum.Caption = Forms![frmEdit]![cmbLogNum]
Me.cmbCDCadd = [B]Me.OpenArgs[/B]
flgNil = False

You still have other issues with this DataBase but I will not et into that here.

.

 

[poporacer]

Thanks

That worked...so what other issues do you see? I would like to correct them before I get too far into this project.
Thanks in advance