Prefilling a combo box with data from another form

[Guest]

I make the user select a value from a combo box. However, if the item is not
available, I have an Add button next to the combo box that executes a macro
that: brings up another form so that the user can add a new record; and then
do a requery so that the new record is available on that original form's
combo box. Works great. But now I would like that new record to be
automatically filled in the combo box. I do not know how to pass the data
from the called form back to the original form. Please tell me the best way
to do this. I would prefer to not use visual basic if I can avoid it.
Thanks.

 

[Guest]

You should not be avoiding VBA if you want to develop Access applications.
You should be learning it.
Macros have their limitations. You are up against one.

 

[Guest]

Since the new record's key added to the table is an autonum, I tried to use
the DMAX function in the macro, but keep getting an error. If I must use VBA
how do I do what I need to do?

 

[Guest]

You can put the value in the first form's combo from the AddNew form after
the requery. Since I don't know your naming, I can only give an example, but
it would be something like:

Forms!FirstFormName!ComboBoxName = Me.ControlWithKeyValue

 

[Guest]

Were does this line go? Thanks.

You can put the value in the first form's combo from the AddNew form after
the requery. Since I don't know your naming, I can only give an example, but
it would be something like:

Forms!FirstFormName!ComboBoxName = Me.ControlWithKeyValue

 

[Guest]

Try putting it in the AddNew form's Unload event.

 

[Guest]

Thanks. That solved my problem. However, the addnew form is not always
called from the first form. How do I check if the first form is loaded?

 

[Guest]

I copied the code for the isloaded function from the Northwind sample
database. Works fine now. Thanks again!

 

[Guest]

If CurrentProject.AllForms("FirtForm").IsLoaded Then
Forms!FirstFormName!ComboBoxName = Me.ControlWithKeyValue
End If

 

[Guest]

I have been trying to work out how to do this and would be really grateful if
you could explain please!
I'm not that concerned about the refinement that has just been added with
help from Klatuu, but I can follow that and add it on if it turns out to be
necessary.
I'm just after the underlying bit for adding a new record to a table so that
it will become accessible in a combo.
Thanks a lot
CW

 

[Guest]

1. Create a form to enter the new record
2. Create a macro:
Macro Name Action
--------------- --------
AddRec : On Click OpenForm (make the record option New)
Requery (the combo box name)
3. Create a label on the form by the combo box.
4. Under the Properties/Event/On Click enter a macro AddRec

Hope this helps.

 

[Guest]

Here is some sample code from one of my applications that does that. For
this to work, you must set the Limit To List property of the combo box to Yes:

Private Sub cboActivity_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If

End Sub

 

[Guest]

Thanks a lot! I'll give that a try.

1. Create a form to enter the new record
2. Create a macro:
Macro Name Action
--------------- --------
AddRec : On Click OpenForm (make the record option New)
Requery (the combo box name)
3. Create a label on the form by the combo box.
4. Under the Properties/Event/On Click enter a macro AddRec

Hope this helps.

 

[Guest]

That's good, I think I can follow it - but how does it get triggered? Do I
need something in one of the Events for the combo, or an additional command
button beside the combo that will fire this up, or ...?
Thanks
CW

Here is some sample code from one of my applications that does that. For
this to work, you must set the Limit To List property of the combo box to Yes:

Private Sub cboActivity_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If

End Sub


--
Dave Hargis, Microsoft Access MVP

I have been trying to work out how to do this and would be really grateful if
you could explain please!
I'm not that concerned about the refinement that has just been added with
help from Klatuu, but I can follow that and add it on if it turns out to be
necessary.
I'm just after the underlying bit for adding a new record to a table so that
it will become accessible in a combo.
Thanks a lot
CW