Referencing Forms (1 Viewer)

[Judy]

Can someone tell me how a form referenced that is not the form that has the focus?

I have a form (first form) with a combo box object where the user selects data to enter from the selection list. The double-click event of the combo box object on the form opens another form (second form) where the user can add an item to the selection list if it doesn't exist. The save button on this form saves the record (of course), then makes the original form's combo box item the same as the form:
Forms![frmFirst]![RecordID] = Me![RecordID]

My problem is that I need to create another form (third form) with the exact same combo box object and allow the same functionality. On the second form's save button, how can I reference which form's combo box was clicked (first or third form)?

If (some condition)....
Forms![????]![RecordID] = Me![RecordID]

Thanks,

Judy

 

[ColinEssex]

Hi Judy

Do you just need to add an item to a comboBox list if it's not in the list.?

Col

 

[Judy]

Yep, that's all I'm trying to do. Am I making this more difficult than it should be?

Thanks!
Judy

 

[ColinEssex]

Hi Judy

The first thing to do is set the LimitToList property to true. That will trap the fact you are trying to enter something not in the list.

Then in the OnNotInList event paste in this code-

Private Sub
ComboBoxName_NotInList(NewData As String, Response As Integer)
Dim rec As Recordset

If MsgBox("Entry is not in list. Ok to add it now?", vbOKCancel, "Adding Data") = vbOK Then
Set rec = CurrentDb.OpenRecordset("YourTableName")

rec.AddNew
rec!Yourfieldname = NewData
rec.Update
Set rec = Nothing

Response = acDataErrAdded
Else
Response = acDataErrContinue
ComboBoxName.Undo
End If
End Sub

That should do it for you. Post back if you need more help

Col

 

[lorenzoaj]

I was looking for something like this. One problem I had though is that after I click yes to add I get a data type mismatch in the following line.

Set rec = CurrentDb.OpenRecordset("tblUnit")

What does that mean?

Thanks

AJ

 

[ColinEssex]

Check that you are not trying to add data that is not of the correct type in the table.

e.g.

Text to a number field
Text or number to a date field etc etc etc

Col

 

[Judy]

I'm getting the same data type mismatch error as lorenzoaj. I believe this is because the text entered into the combo box is text and the primary key is an auto number field so it looks like it's trying to update the auto number field to the text in the cbo box. The cbo box is based on a 2 field table, ID and name. When name is typed into the cbo, how do I update the auto field number using this procedure?

Thanks!!

Judy